Excel SUBTOTAL 函數教學
SUBTOTAL 函數 是 Excel 中非常實用的數學統計函數，用來計算篩選數據時的一些統計值（例如總和、平均值、最大值等）。它的特點是可以在對數據進行篩選或隱藏的情況下，依然準確計算顯示的數據結果。
SUBTOTAL 函數語法
SUBTOTAL(function_num, range1, [range2], ...)
- function_num（必填）：一個介於 1 和 11 或 101 和 111 之間的數字，用來指定統計函數的類型。這個參數決定了使用哪個統計方法來計算。
1：AVERAGE（平均值）2：COUNT（計數）3：COUNTA（計算非空值）4：MAX（最大值）5：MIN（最小值）6：PRODUCT（乘積）7：STDEV（標準差，基於樣本）8：STDEVP（標準差，基於全體）9：SUM（總和）10：VAR（方差，基於樣本）11：VARP（方差，基於全體）
- range1（必填）：要進行計算的數據範圍。
- range2, …（選填）：額外的數據範圍，如果需要進行多範圍計算時使用。
SUBTOTAL 函數的運作原理
SUBTOTAL 函數是根據不同的 function_num 來選擇不同的統計方法來對範圍進行計算。它的最大特點是，當你篩選數據時，SUBTOTAL 只計算 可見行 的數據，不會將被隱藏或過濾掉的數據納入計算。
例如，當你有一個數據表，篩選某些數據後，SUBTOTAL 會計算篩選後可見的數據，而不是所有的數據。
範例
範例 1：計算範圍 A2:A10 的總和
=SUBTOTAL(9, A2:A10)
結果會是範圍 A2 到 A10 的總和，這裡的 9 代表總和（SUM）。
範例 2：計算範圍 B2:B10 的平均值
=SUBTOTAL(1, B2:B10)
結果會是範圍 B2 到 B10 的平均值，這裡的 1 代表平均值（AVERAGE）。
範例 3：計算範圍 C2:C10 的最大值
=SUBTOTAL(4, C2:C10)
結果會是範圍 C2 到 C10 的最大值，這裡的 4 代表最大值（MAX）。
範例 4：計算範圍 D2:D10 的計數（計算非空單元格）
=SUBTOTAL(3, D2:D10)
結果會是範圍 D2 到 D10 中非空白單元格的數量，這裡的 3 代表計算非空白單元格（COUNTA）。
篩選數據時的行為
如果對數據進行篩選（例如使用 Excel 的「篩選」功能），SUBTOTAL 函數會只計算 可見行 的數據。這意味著隱藏的數據或被篩選掉的數據將不會被納入計算。
如何使用 SUBTOTAL 函數
- 在 Excel 中，選擇一個空白的單元格。
- 輸入公式
=SUBTOTAL(function_num, range1)，其中function_num是你想使用的統計方法編號，range1是你要計算的數據範圍。 - 按 Enter 鍵，Excel 會返回你所選擇統計方法的計算結果。
注意事項
- 篩選和隱藏行：
SUBTOTAL函數的最大特點之一是，它只計算篩選或顯示的數據行。隱藏或篩選掉的行不會影響計算結果。 - 函數編號：函數編號從 1 到 11，或者從 101 到 111，後者的區別是會忽略隱藏行。比如，
1代表平均值，而101也代表平均值，但是會忽略隱藏的行。1：AVERAGE（計算所有行的平均值）101：AVERAGE（只計算顯示行的平均值）
常見錯誤
- #DIV/0! 錯誤
如果你在使用SUBTOTAL計算平均值或其他運算時，範圍內的數字為零或範圍內無有效數字，則會返回#DIV/0!錯誤。 - #REF! 錯誤
如果參數range或function_num無效，或者範圍包含無效的引用，則會返回#REF!錯誤。
相關函數
- SUM 函數：
SUM函數用來計算範圍的總和，但是它不會忽略被隱藏的行。與SUBTOTAL不同，SUM是對所有數據進行計算。 - AVERAGE 函數：
AVERAGE函數用來計算範圍的平均值，但它也不會忽略被隱藏的行。 - COUNT 函數：
COUNT函數計算數字單元格的數量，不會對篩選數據或隱藏行進行區分。
結論
- SUBTOTAL 函數 是 Excel 中非常實用的統計函數，特別是在處理篩選或分組數據時，它可以根據不同的統計方法（如總和、平均值、最大值等）對可見數據進行計算。
- 它能夠根據需要計算篩選後的數據，這使它在動態數據分析中非常有用。