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

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

一、Excel SubTotal函數(shù)語法

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

中文表達(dá)式：SUBTOTAL(函數(shù)序號(hào), 命名區(qū)域或引用1, [命名區(qū)域或引用2], ...)

2、說明：

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

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

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

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

E、參數(shù) Function_Num 為 101-111 時(shí)，當(dāng)分類匯總水平區(qū)域時(shí)，隱藏某一列不影響分類匯總；但隱藏垂直區(qū)域中的某一行就會(huì)對(duì)分類匯總產(chǎn)生影響。

二、Excel SubTotal函數(shù)的使用方法及實(shí)例

（一）求和匯總

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

圖1

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

（二）忽略嵌套分類匯總

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

圖2

2、公式 =SUBTOTAL(3,D2:D11) 中 3 表示 CountA函數(shù)，即統(tǒng)計(jì)不為空的單元格數(shù)，返回結(jié)果為 7，說明并沒有包含嵌套的分類匯總單元格，因?yàn)?D2 至 D11 一共有 10 個(gè)非空單元格，有三個(gè)是嵌套分類匯總結(jié)果。

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

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

圖3

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

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

圖4

2、公式 =SUBTOTAL(2,E2:E8) 中的 2 表示 Count函數(shù)，當(dāng)隱藏三行后，統(tǒng)計(jì)結(jié)果仍為 7，說明參數(shù) Function_Num 為 1-11 時(shí)包含隱藏行；當(dāng)把公式中的 2 改為 102 后，統(tǒng)計(jì)結(jié)果變?yōu)?4，說明參數(shù) Function_Num 為 101-111 時(shí)忽略隱藏行。

（五）隱藏行或列對(duì)分類匯總結(jié)果的影響

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

圖5

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

（六）同時(shí)引用多個(gè)區(qū)域

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

圖6

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

三、Excel SubTotal函數(shù)擴(kuò)展使用實(shí)例

（一）求和到當(dāng)前行

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

圖7

2、公式 =SUBTOTAL(9,F$2:F2) 中的 F$2:F2 表示對(duì)單元格 F2 的引用，$2 表示對(duì)行的絕對(duì)引用，當(dāng)往下拖時(shí)，F(xiàn)$2 不變，F(xiàn)2 會(huì)變?yōu)?F3、F4、……。

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

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

圖8

2、公式說明：

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

圖9

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

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

圖10

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

D、D2:D8 以數(shù)組形式返回 D2 到 D8 中的數(shù)值，E2:E8 以數(shù)組形式返回 E2 到 E8 中的數(shù)值，然后把兩個(gè)數(shù)組對(duì)應(yīng)元素相乘，最后返回?cái)?shù)組 {28590.1;42187.2;177606;35600.4;62484;34609.6;97218}。

E、則公式變?yōu)?=SUMPRODUCT({28590.1;42187.2;177606;35600.4;62484;34609.6;97218}*{0;0;1;0;1;1;1})，再把兩個(gè)數(shù)組對(duì)應(yīng)元素相乘，然后求每個(gè)乘積之和。

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

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

圖11

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

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

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

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

D、則公式變?yōu)?=SUM(IF({FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;TRUE},{329;0;528;638;762;892;982}))，再把兩個(gè)數(shù)組對(duì)應(yīng)元素相乘（相乘時(shí)，True 被轉(zhuǎn)為 1，F(xiàn)ALSe 被轉(zhuǎn)為 0），則公式變?yōu)?=SUM({FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;982})，最后求和返回 982。

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