各位小夥伴大家好，我們上節講了Excel常用函數之數學函數（一），本節讓我們來看一下Excel常用函數之數學函數（二），本節我們主要講Sum，sumif，SUMIFS，sumproduct，和SUBTOTAL

一、SUMIFS

1、語法：

表達式：SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)

中文表達式：SUMIFS(求和區域，條件區域1，條件1，[條件區域2，條件2]，...)

2、說明：

A、表達式中，前三個參數是必需的，括号（[]）中的參數是可選的，省略号（...）表示繼續構建[條件區域3，條件3]、[條件區域4，條件4]、...、[條件區域n，條件n]；

B、可以在條件中使用通配符“問号 (?) 和星号 (*)”，問号匹配任意單個字符，星号匹配任意一個或一串字符；如果要找“? 和 *”，需要在它們前面加轉義字符 ~，例如要查找 ?，需要這樣寫 ~?。

C、如果在條件中使用文本條件、含有邏輯或數學符号的條件都必須用雙引号 (") 括起來；例如使用大于号，應該這樣寫：">50" 或 ">"&50。

D、SumIfs 隻對數值求和，文本則忽略，如果選中的求和區域全為文本，則返回 0；如果既有文本又有數值，則隻取數值求和。

3、實例

3.1 單條件求和-統計廣州的總銷量

3.2 多條件求和-統計廣州，T恤的總銷量

3.3 數組條件且與Sum函數結合-統計在廣州和深圳銷售的襯衫銷量之和

（知識點： SumIfs 函數用來分别統計在“廣州”和“深圳”銷售的“襯衫”銷量之和，Sum函數用來把 SumIfs 求出的在“廣州”和“深圳”銷售的“襯衫”銷量之和加起來。）

3.4 多數組條件-統計在廣州和杭州銷售的價格為 59、75或 97 元的服裝銷量之和

（知識點：條件2 {59;75;97} 中數字之間用半角分号（;），如果用半角逗号（,），隻會返回第一條滿足條件的銷量）

3.5 用通配符組合條件-統計産品名稱為四個字、銷售地區含有“州”字、價格大于60元的全部服裝銷量之和

（知識點：由三組“條件區域和條件”組成；第一組（D6:D15,"????"）是從 D6:D15 中找出名稱為四個字的服裝；第二組（E6:E15,"*州"）是從 E6:E15 中找出含有“州”字的服裝；第三組（F6:F15,">60"）是從 F6:F15中找出“價格”大于 60 元的服裝。最後把三組統計出的服裝銷量用 Sum 求和）

二、SUMPRODUCT

對于sumproduct函數，公式參數特别簡單，即=SUMPRODUCT（數組1,數組2,數組3, ……），每個數組之間用逗号隔開，表示數組之間先相乘再求和。

1、基礎用法-求兩列數值的乘積之和

2、單條件求和-求廣州市的所有銷量

3、多條件求和-求廣州市，T恤的所有銷量

4、單條件計數-統計出現廣州市的數量

（知識點：N函數的主要作用為：将不是數值形式的值轉換為數值形式，N函數也被稱為Excel最短函數。）

5、多條件計數-統計出現廣州市，價格大于60的數量

6、條件求乘積-分别統計T恤，襯衫，雪紡的銷售額（價格*銷量）

7、排名-根據銷量排名

8、條件排名- 按照類别對銷量分類排名

9、中國式排名

知識點：利用sumprodunct排名時，在L26單元格輸入公式=SUMPRODUCT(($K$26:$K$35>K26)/COUNTIF($K$26:$K$35,$K$26:$K$35)) 1即可。這個函數比較難理解。對于L26單元格，COUNTIF($K$26:$K$35,$K$26:$K$35)函數表示條件計數，如果有重複值，則返回重複的個數，此處返回的結果是1;1;2;2;1;2;2;1;1;1，而用1/COUNTIF($K$26:$K$35,$K$26:$K$35)表示相同的數字隻統計一次（因為每個重複的數字都被平均了）。返回結果為1;1;0.5;0.5;1;0.5;0.5;1;1;1，然後($K$26:$K$35>K26)/COUNTIF($K$26:$K$35,$K$26:$K$35)的返回結果為0;1;0;0;1;0;0;1;1;0，其中$K$26:$K$35>K26采用的是相對引用，所以向下填充會返回不同的結果。直接決定了計算的相對名次。然後再用sumproduct函數對上面返回結果求和，最後 1對結果修正。

三、SubTotal

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

中文表達式：SubTotal(函數序号, 彙總區域1，[彙總區域2])

2、說明：

A、函數序号分為兩組，一組為 1 到 11，另一組為 101 到 111，它們都對應 Average、Count、CountA、Max、Min、Product、Stdev、Stdevp、Sum、Var、Varp 這 11 個函數，其中序号 1 至 11 不忽略隐藏值，101 到 111 忽略隐藏值，如圖1所示：

B、彙總區域 Ref 參數至少有一個，最多隻能有 254 個。

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

(一）包含隐藏行與不包含隐藏行的實例

1、右鍵第三行行号 3，在彈出的菜單中選擇“隐藏”，則第三行被隐藏；把公式 =SUBTOTAL(9,D2:D6) 複制到 D7 單元格，按回車，返回結果 2977；雙擊 D7 單元格，把公式中的 9 改為 109，按回車，返回結果 2085；操作過程步驟，如圖2所示：

圖2

2、公式說明：公式 =SUBTOTAL(9,D2:D6) 中的 9 代表求和函數 Sum，D2:D6 為求和區域；當為 9 時，求和結果為 2977；當把 9 改為 109（109 也代表求和函數 Sum），求和結果為 2085；說明 9 包含了隐藏的第三行，109 沒有包含隐藏的第三行，即函數序号為 1 到 11 包含隐藏行、101 到 111 不包含隐藏行。

(二）忽略已有分類彙總的實例

1、假如有一個已經按“類别”分類彙總的表格，如圖3所示：

圖3

2、選中 E13 單元格，把公式 =SUBTOTAL(9,E2:E12) 複制到 E13，如圖4所示：

圖4

3、按回車，返回對 E2:E12 的求和結果 5151，如圖5所示：

圖5

4、返回結果與總計相同，說明返回的結果沒有包含對“T恤、襯衫、雪紡和總計”的彙總結果，否則返回結果為 5151 的兩倍。

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

1、把公式 =SUBTOTAL(9,E2:E8) 複制到 E9 單元格，按回車，返回結果 5151；選中 E 列，選擇“數據”選項卡，單擊“篩選”圖标，則 E 加上篩選下拉列表圖标，單擊該圖标，在彈出的菜單中依次選擇“數字篩選”→ 大于，打開“自定義自動篩選方式”窗口，在“大于”右邊輸入 700，單擊“确定”，則篩選出“銷量”大于 700 的服裝，“銷量”小于等于 700 的被隐藏；E9 中的 SubTotal 彙總結果也自動變為 3645，說明“銷量”小于等于 700 的被隐藏的行被剔除彙總結果；雙擊 E9，把公式中的 9 改為 109，按回車，同樣返回 3645；操作過程步驟，如圖6所示：

圖6

2、從操作過程可知，函數序号無論是 1 到 11 還是 101 到 111 都忽略不包含在篩選結果中的行。

（四）對行分類彙總隐藏值對彙總結果的影響實例

1、選中 F2 單元格，把公式 =SUBTOTAL(109,B2:E3) 複制到 F2，按回車，返回結果 3215；右鍵第三行行号 3，在彈出的菜單中選擇“隐藏”，則把第三行隐藏，F2 中分類彙總結果也随之變為 1614，按 Ctrl Z 取消隐藏第三行；右鍵第四列頂部 D，在彈出的菜單中選擇“隐藏”把 D 列隐藏，F2 中的分類彙總結果仍然是 3215；操作過程步驟，如圖7所示：

圖7

2、說明：當隐藏行時，SubTotal函數彙總結果變小，說明被隐藏的第三行被剔除彙總結果；當隐藏列時，SubTotal函數彙總結果不變，說明隐藏列不影響彙總結果；此種情況适用于函數序号為 101 到 111，當函數序号為 1 到 11 是，無論隐藏行還是列，都不會影響彙總結果。

（五）一次引用兩個區域的實例

1、假如要彙總 B 列和 D 列。選中 B10 單元格，把公式 =SUBTOTAL(9,B2:B9,D2:D9) 複制到 B10，按回車，返回結果 10158，操作過程步驟，如圖8所示：

圖8

2、一次彙總多列，如果它們連在一起，引用一次區域即可；隻有它們隔開列時才分開寫，如演示中的 B 列和 D 列。

好了 今天的sumifs，sumproduct，subtotal三個求和函數講到這裡了，剩下的下節課再講~~

更多精彩资讯请关注tft每日頭條，我们将持续为您更新最新资讯!