Excel SUBTOTAL 函數用法：計算篩選後的總計與平均值

SUBTOTAL 函數可以對資料範圍進行加總、平均、計數等運算，最大的特色是會自動忽略被篩選掉的資料，以及範圍內其他的 SUBTOTAL 計算結果，避免重複加總。

Excel SUBTOTAL 函數語法

=SUBTOTAL(函數代碼, 參照1, [參照2], ...)

參數	必填	說明
函數代碼	是	指定要使用的計算方式代碼，1 到 11 會包含手動隱藏列，101 到 111 會忽略手動隱藏列。
參照1	是	要進行計算的第一個儲存格範圍。
參照2	選填	要進行計算的第二個儲存格範圍，最多可達 254 個。

回傳：依據指定的函數代碼，回傳計算後的數值。

適用版本：所有 Excel 版本皆支援。

SUBTOTAL 函數範例

範例 1：基本加總

計算業務員的總銷售額，並確保篩選時結果會跟著變動。

業務員	銷售額
王小明	5000
陳怡君	8000
林家豪	3000

在 C2 輸入公式：

=SUBTOTAL(9, B2:B4)

結果：16000。使用代碼 9 代表執行 SUM 加總計算。公式會將 B2 到 B4 的銷售額加總，若後續對資料進行篩選，結果會自動隨之變動。

範例 2：計算平均值並忽略隱藏列

計算學生的平均成績，且不計算被手動隱藏的資料列。

學生	成績
張建國	85
李雅婷	92
吳柏毅	78

在 C2 輸入公式：

=SUBTOTAL(101, B2:B4)

結果：85。使用代碼 101 代表執行 AVERAGE 平均值計算，且會忽略手動隱藏的資料列。目前資料皆未隱藏，因此計算 B2 到 B4 的平均分數為 85。

範例 3：統計非空白儲存格數量

統計有打卡紀錄的人數。

姓名	打卡時間
王小明	08:50
陳怡君
林家豪	08:55
張建國	09:05

在 C2 輸入公式：

=SUBTOTAL(3, B2:B5)

結果：3。使用代碼 3 代表執行 COUNTA 計算，會統計範圍內非空白儲存格的數量。B2 到 B5 中有 3 個儲存格有打卡時間紀錄，回傳 3。

使用 SUBTOTAL 的技巧與注意事項

• SUBTOTAL 函數會自動忽略參照範圍內的其他 SUBTOTAL 函數計算結果，這在處理包含多個小計與總計的報表時，可避免資料被重複計算。
• 無論使用 1 到 11 或 101 到 111 的代碼，SUBTOTAL 函數都會忽略因為「篩選」功能而被隱藏的資料列。
• 代碼 101 到 111 的設計專門用來處理「手動隱藏」的資料列（按滑鼠右鍵選擇隱藏），若要計算包含手動隱藏列的資料，請使用 1 到 11 的代碼。
• SUBTOTAL 函數主要設計用於垂直欄位的資料計算，不適用於水平列的隱藏資料計算。若隱藏欄，計算結果不會受影響。

SUBTOTAL 常見錯誤與解決方法

錯誤	原因	解決方法
#VALUE!	函數代碼不在 1 到 11 或 101 到 111 的範圍內。	確認並修正第一個參數，輸入正確的函數代碼（例如 9 代表加總）。
#DIV/0!	指定了平均值（代碼 1 或 101）計算，但參照範圍內沒有數值資料，或所有數值都被篩選隱藏。	確保參照範圍內包含至少一個有效的數值，或清除篩選條件以顯示資料。

Google 試算表可以用 SUBTOTAL 嗎？

Google 試算表完全支援 SUBTOTAL 函數，函數代碼與運作邏輯與 Excel 一致。

下載 SUBTOTAL 範例檔

上面的範例都整理在這個檔案裡，每個範例一個工作表，黃色格子是公式，可以直接改資料看結果變化：SUBTOTAL 範例.xlsx（範例公式都在試算表軟體實際計算過）。

常見問題