在介紹主題 SUMIFS 函數之前,先回顧 "系列十二:SUMIF 函數"。
SUMIF 函數語法:=SUMIF(range,criteria,[sum_range])。
從語法得知:一個 IF 就有一對 <range,criteria>,因此,SUMIFS 函數有一個以上的 IF,也就有一對以上的 <range,criteria>。
由於 SUMIFS 函數的 IF 個數可 "按需增加",所以將 sum_range 作為第一個參數,以便有更多對的 <range,criteria> 可向後延伸。
SUMIFS 函數語法:
=SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], …)
其中所有條件(criteria)都必須同時成立。
陣列語意:
{=SUMIFS(加總範圍,查找範圍1,來源陣列1,[查找範圍2,來源陣列2],...)}。
一、SUMIFS 函數陣列作法:
如下圖所示,給定資料表 A1:D14,加總範圍 C2:C14。
1. 查找範圍1 A2:A14,來源陣列1位址 A20:A21 即 {"Fax";"Phone"},
查找範圍2 B2:B14,來源陣列2位址 B20:B21 即 {"Brown";"Jones"}:
選取 F20:F21 (存放結果陣列),
輸入
=SUMIFS(C2:C14,A2:A14,A20:A21,B2:B14,B20:B21),
按下 CTRL+SHIFT+ENTER。
業務員 Brown 的 Fax 總銷售單位 26,
業務員 Jones 的 Phone 總銷售單位 75。
2. Brown 的 Fax 及 Jones 的 Phone 總銷售單位總和:
選取 F23 (存放單格結果陣列),
輸入
=SUM(SUMIFS(C2:C14,A2:A14,A20:A21,B2:B14,B20:B21)),
按下 CTRL+SHIFT+ENTER。
業務員 Brown 的 Fax 與 Jones的 Phone 的總銷售單位 101。
二、SUMPRODUCT 函數作法:
1. 條件範圍 A20:A21 及 B20:B21:
選取 F25:F26,輸入
=SUMPRODUCT(($A$2:$A$14=A20)*1,($B$2:$B$14=B20)*1,$C$2:$C$14),
(參考 "系列4":TRUE*1 =1;FALSE *1 = 0)
按下 CTRL+ENTER。
(關於 CTRL+ENTER ,請參考 "系列五:COUNTIF 函數")
業務員 Brown 的 Fax 總銷售單位 26,
業務員 Jones 的 Phone 總銷售單位 75。
2. 條件範圍 A20:A21 及 B20:B21:
選取 F28,輸入
=SUMPRODUCT(($A$2:$A$14=A20)*1,($B$2:$B$14=B20)*1,$C$2:$C$14)
+SUMPRODUCT(($A$2:$A$14=A21)*1,($B$2:$B$14=B21)*1,$C$2:$C$14)
按下 ENTER。
業務員 Brown 的 Fax 與 Jones的 Phone 的總銷售單位 101。
結論:
1. 此篇是由兩個元素個數相同的單欄陣列作為 SUMIFS 函數的來源陣列,運算結果也是一個單欄陣列,與來源陣列元素對應且元素個數也相同。(請參考 "系列一:入門篇" 及 "系列二:四則運算")
2. 除了利用 SUMIF 函數的 "多格陣列" 作法外,此處亦可利用系列三、四介紹過的 "單格陣列函數":SUM 函數,個別計算如下所示:
i. 業務員 Brown 的 Fax 總銷售單位:
選取 G25 (存放單格陣列函數),
輸入
=SUM(($A$2:$A$14=A20)*($B$2:$B$14=B20)*$C$2:$C$14)
按下 CTRL+SHIFT+ENTER。
ii. 業務員 Jones 的 Phone 總銷售單位:
拖曳 G25 的 "填滿空點" 至 G26。
原因可參考 "系列三:SUM 函數之一",或敬待 "單格陣列函數" 主題。此用法亦較 SUMPRODUCT 函數更簡潔。
3. 承 2. 也可利用 IF 函數的 "多格陣列" 作法,搭配 SUM 函數的 "單格陣列" 作法,個別計算如下所示:
i. 業務員 Brown 的 Fax 總銷售單位:
選取 H25 (存放單格陣列函數),
輸入
=SUM(IF(($A$2:$A$14=A20)*($B$2:$B$14=B20),$C$2:$C$14))
按下 CTRL+SHIFT+ENTER。
ii. 業務員 Jones 的 Phone 總銷售單位:
拖曳 H25 的 "填滿空點" 至 H26。
(敬請期待 IF 函數的 "多格陣列" 作法介紹。)
4. 承 2. 也可利用 IF 函數的 "多格陣列" 作法,搭配 SUM 函數的 "單格陣列" 作法,個別計算如下所示:
i. 業務員 Brown 的 Fax 總銷售單位:
選取 I25 (存放單格陣列函數),
輸入
=SUM(IF(IF($A$2:$A$14=A20,$B$2:$B$14)=B20,$C$2:$C$14))
(內含兩層 IF 的 SUM 陣列公式)
按下 CTRL+SHIFT+ENTER。
ii. 業務員 Jones 的 Phone 總銷售單位:
拖曳 I25 的 "填滿空點" 至 I26。
(敬請期待 IF 函數的 "多格陣列" 作法介紹。)
5. COUNTIFS 函數的陣列用法相同於 SUMIFS 函數的陣列用法。
留言列表