SUBTOTAL 入門：篩選之後只加看得到的列，代碼 9 和 109 差在哪

銷量表套上篩選，只留下「奶茶」那幾列，畫面上看起來是三列，底下的 SUM 卻還是整張表的總和。

想要「篩選之後，只加看得到的列」，要用的是 SUBTOTAL。這篇會把它的 function_code 一次整理好，特別是 9 和 109 這種容易搞混的代碼。

篩選之後 SUM 不會變

我手上有一張七列的銷量表，A1:C8：

=SUM(C2:C8) 是 37 + 8 + 21 + 16 + 12 + 29 + 14 = 137。

接著我點「資料」>「建立篩選器」，在「分類」欄只勾選「奶茶」，畫面上剩下 12、29、14 三列，手算是 55。但 SUM 還是 137，因為被篩掉的列只是看不見，儲存格裡的值還在。

每次篩選完都要拿計算機重加一次，有點太麻煩了吧！這時候就可以把 SUBTOTAL 派上場。

SUBTOTAL

SUBTOTAL 會用你指定的彙總方式（加總、平均、計數⋯⋯）去算範圍，而且會跳過被篩選掉的列。

語法

=SUBTOTAL(函式代碼, 範圍1, [範圍2, ...])

• 函式代碼：用數字指定要做哪一種計算，完整對照在下一節。
• 範圍1：要計算的範圍，官方說明寫的是垂直範圍。
• 範圍2⋯⋯：選填，更多範圍。

範例

要加總「杯數」，函式代碼用 9：

沒套篩選的時候，結果是 137，跟 SUM 一樣。套上「分類 = 奶茶」的篩選後，結果是 55。

我習慣把 SUBTOTAL 放在表格上方（例如 E1），不要放在資料的最後一列。篩選會連同範圍內的列一起收起來，放在範圍裡面的小計，有可能跟著被藏起來，看不到就白算了。

函式代碼對照

代碼有兩組：1 到 11，和對應的 101 到 111。兩組做的計算一樣，差別只有一個，後面會講。

下表的結果，是用上面那七筆杯數算出來的，沒有篩選、沒有隱藏的狀況：

我個人最常用的是 9，其次是 2 或 3 拿來數筆數，4、5 偶爾拿來看最大最小。7 到 8、10 到 11 這幾個統計用的，在日常報表幾乎沒碰過。

篩選與手動隱藏列

「看不到」其實有兩種原因，SUBTOTAL 的兩組代碼就是在處理這個差別：

• 篩選掉的列：兩組代碼都會跳過。
• 手動隱藏的列（在列號上按右鍵，選「隱藏列」）：官方說明寫的是 101 到 111 會略過隱藏的值，1 到 11 則不會。

所以怎麼選？如果你只用篩選，9 跟 109 沒差別。如果同事習慣直接把不要看的列藏起來，又希望合計跟著畫面變，就用 109。我現在寫新表格大多直接用 101 到 111，合計會跟著畫面走；如果你希望手動隱藏的列還是照算，就用 1 到 11。

還有一個限制：官方說明寫的是垂直範圍，只談到「列」，沒有說明隱藏欄的行為。水平排列的資料請不要假設隱藏欄會被略過。Excel 的說明頁有明寫橫向範圍的隱藏欄不影響小計，那是 Excel 的行為。

小計裡面有小計

做分組報表時，常常會在每組下面放一個小計，最後再放總計。

C6 是 =SUBTOTAL(9, C2:C5)，C10 是 =SUBTOTAL(9, C7:C9)，總計 C11 這樣寫：

結果是 137。82 + 55 = 137，兩個小計沒有被重複加進去。

換成 =SUM(C2:C10) 的話，會把明細 137 再加上兩個小計 137，變成 274。範圍裡只要有別的 SUBTOTAL 公式算出來的格子，SUBTOTAL 都會自動略過，所以做分組小計時不用擔心重複計算。

練習

沒有任何列被隱藏的時候，結果是 37。

下次套篩選之前，先在表格上方放一格 SUBTOTAL，之後篩選就不用再拿計算機了。