在 Excel 中，SubTotal函數用于計算分類匯總，它至少要有兩個參數，其中一個為函數序號，另一個為命名區域或引用；函數序號共支持11 個函數，并且這 11 個函數被設置兩種編號，分別為 1-11 和 101-111，其中 1-11 包含隱藏值，101-111 忽略隱藏值；命名區域或引用最多支持 254 個。

SubTotal函數可與 OffSet、SumProduct、If、Sum、Row 等函數組合使用，其中 SubTotal + OffSet + SumProduct + Row 用于篩選態下求乘積之和，即不包含篩選外的值；Sum + If + OffSet + SubTotal 用于求指定條件的和。

一、Excel SubTotal函數語法

1、表達式：SUBTOTAL(Function_Num, Ref1, [Ref2], ...)

中文表達式：SUBTOTAL(函數序號, 命名區域或引用1, [命名區域或引用2], ...)

2、說明：

A、Function_Num 用于指定要分類匯總的函數，一共有 11 個函數，每個函數有兩個序號，其中 1-11 表示包含隱藏值，101-111 表示忽略隱藏值，具體如下：

B、至少要有一個命名區域或引用，最多只能有 254 個命名區域或引用。如果指定有三維引用，SUBTOTAL函數將返回引用錯誤值 #REF!。

C、如果參數 ref 中有嵌套分類匯總，它們將被忽略，以避免重復計算；不在篩選結果中的行也被忽略。

D、參數 Function_Num 為 1-11 包含隱藏值是指通過“隱藏”選項（即手動隱藏）所隱藏行的值；參數 Function_Num 為 101-111 忽略隱藏值是指忽略通過“隱藏”選項所隱藏行的值。

E、參數 Function_Num 為 101-111 時，當分類匯總水平區域時，隱藏某一列不影響分類匯總；但隱藏垂直區域中的某一行就會對分類匯總產生影響。

二、Excel SubTotal函數的使用方法及實例

（一）求和匯總

1、假如要匯總服裝銷量之和。選中 E9 單元格，輸入公式 =Subtatol(9,e2:e9)，按回車，返回求和結果 5028；操作過程步驟，如圖1所示：

圖1

2、公式 =Subtatol(9,e2:e9) 中 9 表示求和，e2:e9 為求和區域。

（二）忽略嵌套分類匯總

1、已按“分類”進行了分類匯總，現在再用SubTotal函數分類匯總。雙擊 D12 單元格，把公式 =SUBTOTAL(3,D2:D11) 復制到 D12，按回車，返回統計結果 7；操作過程步驟，如圖2所示：

圖2

2、公式 =SUBTOTAL(3,D2:D11) 中 3 表示 CountA函數，即統計不為空的單元格數，返回結果為 7，說明并沒有包含嵌套的分類匯總單元格，因為 D2 至 D11 一共有 10 個非空單元格，有三個是嵌套分類匯總結果。

（三）忽略不包含在篩選結果中的行

1、選中 E9 單元格，把公式 =SUBTOTAL(9,E2:E8) 復制到 E9，按回車，返回求和結果 5028；框選 E2:E8 單元格，選擇“數據”選項卡，單擊“篩選”，則 E2 單元格添加上“篩選”圖標，單擊該圖標，在彈出的菜單中依次選擇“數字篩選”→ 大于，打開“自定義自動篩選方式”窗口，在“大于”右邊輸入 600，單擊“確定”，則篩選出銷量大于 600 的服裝，E9 中的值自動變為 4500，說明忽略了不包含在篩選結果中的行；操作過程步驟，如圖3所示：

圖3

（四）包含隱藏行與忽略隱藏行

1、選中 E9 單元格，把公式 =SUBTOTAL(2,E2:E8) 復制到 E9，按回車，返回統計有數值單元格個數為 7；選中第二至第四行，右鍵第二行行號 2，在彈出的菜單中選擇“隱藏”，則所選中的三行被隱藏，E9 中的數值仍是 7；雙擊 E9，把公式中的 2 改為 102，按回車，返回統計結果 4；操作過程步驟，如圖4所示：

圖4

2、公式 =SUBTOTAL(2,E2:E8) 中的 2 表示 Count函數，當隱藏三行后，統計結果仍為 7，說明參數 Function_Num 為 1-11 時包含隱藏行；當把公式中的 2 改為 102 后，統計結果變為 4，說明參數 Function_Num 為 101-111 時忽略隱藏行。

（五）隱藏行或列對分類匯總結果的影響

1、選中 E10 單元格，把公式 =SUBTOTAL(109,C2:E9) 復制到 E10，按回車，返回求和結果 14091；選中第二行和第三行，右鍵第二行行號 2，在彈出的菜單中選擇“隱藏”，則第二三行被隱藏；E10 中的值變為 12014；按 Ctrl + Z 取消隱藏行，右鍵第四列列號 D，在彈出的菜單中選擇“隱藏”，則第四列被隱藏，E10 中的值仍為 14091；操作過程步驟，如圖5所示：

圖5

2、當隱藏行時，公式 =SUBTOTAL(109,C2:E9) 忽略了被隱藏的行；而隱藏列時，公式 =SUBTOTAL(109,C2:E9) 把隱藏的列也計入求和。

（六）同時引用多個區域

1、假如要求服裝表中任意兩種服裝（如“襯衫”和“褲子”）的平均銷量。選中 E10 單元格，把公式 =SUBTOTAL(1,C2:C9,E2:E9) 復制到 E10，按回車，返回 560.5；操作過程步驟，如圖6所示：

圖6

2、公式 =SUBTOTAL(1,C2:C9,E2:E9) 中 1 表示求平均值，C2:C9 和 E2:E9 為求平均值區域。

三、Excel SubTotal函數擴展使用實例

（一）求和到當前行

1、雙擊 G2 單元格，把公式 =SUBTOTAL(9,F$2:F2) 復制到 G2，按回車，返回求和結果 329；再次選中 G2 單元格，把鼠標移到 G2 右下角的單元格填充柄上，按住左鍵，往下拖，拖到 G3，求和到 G2，再拖到 G4，求和到 G4；操作過程步驟，如圖7所示：

圖7

2、公式 =SUBTOTAL(9,F$2:F2) 中的 F$2:F2 表示對單元格 F2 的引用，$2 表示對行的絕對引用，當往下拖時，F$2 不變，F2 會變為 F3、F4、……。

（二）在篩選狀態下求乘積之和用 SumProduct + SumTotal + OffSet 組合

1、假如要求篩選狀態下營業額。選中 F1 單元格，把公式 =SUMPRODUCT((D2:D8)*(E2:E8)*SUBTOTAL(3,OFFSET(E1,ROW(1:7),))) 復制到F1，按回車，返回求和結果 478295.3；框選 E1:E8，選擇“數據”選項卡，單擊“篩選”，則 E1 添加上“篩選”圖標，單擊該圖標，在彈出的菜單中選擇“數字篩選”→ 大于，打開“自定義自動篩選方式”窗口，在“大于”右邊輸入 700，單擊“確定”，則篩選出大于 700 的服裝，F1 中的值隨即變為 371917.6，說明被隱藏的第二、三、五行沒有被計入求乘積之和；操作過程步驟，如圖8所示：

圖8

2、公式說明：

A、公式 =SUMPRODUCT((D2:D8)*(E2:E8)*SUBTOTAL(3,OFFSET(E1,ROW(1:7),))) 執行過程如圖9所示：

圖9

公式執行過程說明：選中 F1，按住 Alt，按一次 M，按一次 V，打開“求值”窗口，按回車求值，每按一次回車求一次值。

B、ROW(1:7) 用于返回 1 到 7 的數組，即 {1;2;3;4;5;6;7}；則 OFFSET(E1,ROW(1:7),) 變為 OFFSET(E1,{1;2;3;4;5;6;7},)，執行時，以 E1 為基準，第一次從數組中取出 1 作為參數 Rows，由于 OffSet 省略了參數 Cols、返回單元格的高度和寬度，因此返回 E1 下一行的單元格 E2 的值；第二次從數組中取出 2 作為參數 Rows，返回 E1 下二行的單元格 E3 的值，其它的以此類推；最后返回數組{#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!}，#VALUE! 代表 E2:E7 中的數值，它可以用公式 =SUM(N(OFFSET(E1,ROW(1:7),))) 驗證（輸入公式后需按 Ctrl + Shift + 回車），演示如圖10所示：

圖10

C、則 SUBTOTAL(3,OFFSET(E1,ROW(1:7),)) 變為 SUBTOTAL(3,{#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!})，執行時，依次從數組中取出每一個值，如果它被隱藏（即不是篩選結果），返回 0，否則返回 1，最后返回 {0;0;1;0;1;1;1}，符合第二、三、五行篩選時被隱藏，注意這里的范圍為 E2:E8，所以 E2 是第二行，因此數組第一個元素為 0。

D、D2:D8 以數組形式返回 D2 到 D8 中的數值，E2:E8 以數組形式返回 E2 到 E8 中的數值，然后把兩個數組對應元素相乘，最后返回數組 {28590.1;42187.2;177606;35600.4;62484;34609.6;97218}。

E、則公式變為 =SUMPRODUCT({28590.1;42187.2;177606;35600.4;62484;34609.6;97218}*{0;0;1;0;1;1;1})，再把兩個數組對應元素相乘，然后求每個乘積之和。

（三）用 Sum + If + SubTotal + OffSet函數按條件求不包含隱藏值的和

1、假如要求“男裝”的銷量之和。雙擊 E9 單元格，把公式 =SUM(IF(C2:C8="男裝",SUBTOTAL(109,OFFSET(E1,ROW(1:7),)))) 復制到 E9，按 Ctrl + Shift + 回車，返回求和結果 1879；右鍵第三行行號 3，在彈出的菜單中選擇“隱藏”，則第三行（分類為男裝）被隱藏，E9中的值隨即變 982，說明被隱藏的行沒有參數求和；操作過程步驟，如圖11所示：

圖11

2、公式說明（以隱藏第 3 行后為例）：=SUM(IF(C2:C8="男裝",SUBTOTAL(109,OFFSET(E1,ROW(1:7),))))

A、OFFSET(E1,ROW(1:7),) 在上例中已經分析過，最后返回 {#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!}。

B、SUBTOTAL(109,OFFSET(E1,ROW(1:7),)) 變為 SUBTOTAL(109,{#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!})，接著，如果數組中的值被隱藏返回 0，否則返回具體數值，最后返回 {329;0;528;638;762;892;982}。

C、C2:C8="男裝" 為 If 的條件，意思是：如果 C2 至 C8 中的文本等于“男裝”，返回 True，否則返回 False；例如 C2 為“女裝”，因此返回 False，C3 為“男裝”，因此返回 True；最后返回數組 {FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;TRUE}。

D、則公式變為 =SUM(IF({FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;TRUE},{329;0;528;638;762;892;982}))，再把兩個數組對應元素相乘（相乘時，True 被轉為 1，FALSe 被轉為 0），則公式變為 =SUM({FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;982})，最后求和返回 982。

提示：If + SubTotal 組合按條件求和不能這樣寫公式：=SubTotal(109,If(C2:C8="男裝"),C2:C8,))。