首頁  >  文章  >  專題  >  實用Excel技巧分享:怎麼忽略隱藏列進行求和?

實用Excel技巧分享:怎麼忽略隱藏列進行求和?

青灯夜游
青灯夜游轉載
2022-05-27 10:42:1510893瀏覽

在之前的文章《實用Excel技巧分享:「條件格式」和「函數公式」配合使用》中,我們透過2個實例了解了「條件格式」和「函數公式」如果配合使用。而今天我們來聊聊資料求和,介紹一下忽略隱藏列進行求和的方法,快來學習學習!

實用Excel技巧分享:怎麼忽略隱藏列進行求和?

在平常工作中,我們常常會用到求和,這種問題對大家來說是再簡單不過的,使用SUM函數就可以解決:

實用Excel技巧分享:怎麼忽略隱藏列進行求和?

有時候可能會隱藏幾行數據,這時候求和就要用到SUBTOTAL這個函數了。在沒有隱藏的時候,SUBTOTAL函數結果與SUM函數計算的結果一致,如下:

實用Excel技巧分享:怎麼忽略隱藏列進行求和?

#一旦我們將其中的某幾行(如第3、6、9 、12行)資料隱藏起來,結果就發生了變化,如下:

實用Excel技巧分享:怎麼忽略隱藏列進行求和?

#注意SUBTOTAL函數的第一個參數使用109就是表示忽略隱藏行的求和。

關於SUBTOTAL這個函數,之前發表過教程,有興趣的朋友可以去看看歷史文章。

今天我們要討論的問題不是忽略隱藏行如何求和,而是忽略隱藏列如何求和。首先要明確一點,SUBTOTAL這個函數是做不到這一點的,在函數的幫助裡說的很清楚:

實用Excel技巧分享:怎麼忽略隱藏列進行求和?

不但是SUBTOTAL函數做不到,就目前來說, Excel還沒有可以忽略隱藏列進行求和的函數。那麼對於這種需求該如何處理呢?這要用到一個比較新鮮的函數-CELL函數來做輔助才行。

相信見過這個函數的朋友不多,會用這個函數的就更少,我們就先來簡單了解一下CELL函數是用來做什麼的。在儲存格輸入=c就會看到這個函數的身影,選擇函數後,會出現一個簡單的解釋:

實用Excel技巧分享:怎麼忽略隱藏列進行求和?

在這句話中,可以大致了解到CELL函數可以得到一個單元格的格式、位置等資訊。雙擊這個函數,會出現一些選項:

實用Excel技巧分享:怎麼忽略隱藏列進行求和?

可以看到,函數有兩個參數,info_type和reference。第一個參數info_type,訊息類型,一共有12種,各種類型具體意義可以透過函數幫助了解:

實用Excel技巧分享:怎麼忽略隱藏列進行求和?

對於這些資訊類型有興趣的朋友可以自己看看,它們都非常容易理解。今天重點要用到的是最後一個訊息類型"width",簡單來說就是列寬。

有些朋友可能已經想到了,如果列被隱藏的話其列寬就是0,到底是不是這樣,我們可以來試試看。公式的第一參數選擇"width",第二參數設為B1,表示要得到B1單元格的列寬(其實就是B列的列寬)。在B16儲存格輸入公式:=CELL(“width”,B1),然後將公式向右拉:

實用Excel技巧分享:怎麼忽略隱藏列進行求和?

結果全部是8。我們可以試試看調整個別列的寬度,再看看是否有變化:

實用Excel技巧分享:怎麼忽略隱藏列進行求和?

當我們調整了寬度以後,結果還是8,難道是公式有問題嗎?

其實不是的,原因是CELL函數有點小脾氣,當單元格的格式改變以後(列寬就是一種格式)必須重新計算才能更新結果。重新計算有兩種方法,一是按F9功能鍵,二是雙擊任一儲存格後回車。再來看看就發現結果已經更新了:

實用Excel技巧分享:怎麼忽略隱藏列進行求和?

數字的大小的確與單元格的寬與窄對應。說到這裡如何忽略隱藏的列求和,答案已經呼之欲出了:使用CELL函數得到列寬,再用SUMIF函數實作求和。 I2儲存格輸入公式為:=SUMIF($B$16:$G$16,">0",B2:G2),然後將公式向下拉。

1實用Excel技巧分享:怎麼忽略隱藏列進行求和?

在沒有隱藏的時候,就是全部求和,現在我們隱藏幾列看看效果:

1實用Excel技巧分享:怎麼忽略隱藏列進行求和?

#隱藏後記得要按F9或雙擊一下哦。

問題到這裡似乎該結束了,可是總有些夥伴不太樂意用輔助列(輔助行),就想用數組公式來實現,例如:=SUMPRODUCT((CELL("width",B1 :G1>0)*B2:G2)

想法似乎很有道理,但是這樣做是不行的,因為如果參數reference 是某一單元格區域,則函數CELL 只將該資訊傳回給該區域左上角的單元格。也就是說,雖然寫了B1:G1這樣一個區域,但是得到的只是B1的列寬。

是不是覺得cell這個函數的脾氣挺怪的~~~

其實這個函數還有很多有趣的用法,如果你想知道的話,在下面留言吧!

#相關學習推薦:excel教學

#

以上是實用Excel技巧分享:怎麼忽略隱藏列進行求和?的詳細內容。更多資訊請關注PHP中文網其他相關文章!

陳述:
本文轉載於:itblw.com。如有侵權,請聯絡admin@php.cn刪除