Excel 小教室 – 超多功用的 SUBTOTAL 函數,比如包含隱藏列怎麼求和?

今天來講一個大家比較沒注意的 SUBTOTAL 函數,不論是求和用的 SUM、算個數的  COUNT 系列等,大家應該都已經用的滾瓜爛熟了吧,但來一個簡單的問題,當我們的數列有隱藏列時,要怎麼求和,又要怎麼分別得出包括隱藏列的總和以及不包含的總和呢?以包括隱藏列的總和來說,只要用 SUM 就可以,那麼反之?要用什麼?那麼就來學一下 SUBTOTAL 這個超實用且多功用的函數,看懂後就像吃了十年大補丸。

超多功用的 SUBTOTAL 函數使用介紹:

首先在開始之前給大家看一下我的數列範例是 1,3,5,7,9 直到 15,這時我們先將第 4 跟第 5 列隱藏起來。

Excel 小教室 - 超多功用的 SUBTOTAL 函數,比如包含隱藏列怎麼求和? 01 9

這時如果我們用 SUM 來求得 C2:C9 的總和,你會發現,得出的總和是包含隱藏列的內容,獲得 64,那麼我們要怎麼求得不包括隱藏列的總和,往下來看。

Excel 小教室 - 超多功用的 SUBTOTAL 函數,比如包含隱藏列怎麼求和? 02 6

公式為:

=SUBTOTAL(109,C2:C9)

稍稍的來解一下這個函數,SUBTOTAL 並不是一個單一函數,而是個複合式函數,意思是他可以依照你提供的參數不同,結果也會有所不同。

以我們要求和不含隱藏列來說,109 這個參數就是代表 SUM,但不包括隱藏列,後面 C2:C9 當然就是計算範圍。

Excel 小教室 - 超多功用的 SUBTOTAL 函數,比如包含隱藏列怎麼求和? 03 8

如果你將 109 去掉 10,只留下 9,就代表包括隱藏列內容的求和,這是很基本的應用,我們再往下來看。

Excel 小教室 - 超多功用的 SUBTOTAL 函數,比如包含隱藏列怎麼求和? 04 5

如果要求得平均值且不含隱藏列就是用 101,看到這邊有沒有覺得很神奇!只要修改中間的參數,馬上就可以應用在各種不同地方。

往下來跟大家解一下各種參數。

Excel 小教室 - 超多功用的 SUBTOTAL 函數,比如包含隱藏列怎麼求和? 05 3

詳細的用法都可以在 Office 官方網站查看:請點我前往

而這個 SUBTOTAL 複合式的函數,一共有 11 種參數可以用,分別是 1~11,是包括隱藏的值一起計算,而反之的就是忽略隱藏值計算,分別有 101~111,認真算的話就是共 22 種用法,其實這些函數包括 AVERAGE(平均值)、COUNT(計算個數含空格)、COUNTA(計算非空格個數)、MAX(數列中最大值)、MIN(數列中最小值)、PRODUCT(所有數值相乘的結果)、SUM(求和),而另外四個有點難解釋,平常也很少會用上,阿湯就先略過,等有機會再來和大家說明,大家就先將以上幾個最基礎且常用的參數學起來吧。

看到這裡,恭喜你吃了十年大補丸,功力再上一層囉。

Excel 小教室 - 超多功用的 SUBTOTAL 函數,比如包含隱藏列怎麼求和? 06 3

發佈留言

發佈留言必須填寫的電子郵件地址不會公開。 必填欄位標示為 *