前幾個主題所介紹的 VLOOKUP 函數是製作報表中非常重要的查表工具，但更重要的是如何將查找出來的 "資料" 彙整出商業決策所需要的 "資訊"。自此篇起，將介紹一連串統計函數的陣列用法。
本主題是由 "系列七：LOOKUP 函數" 中提到的 "如何以陣列公式統計某業務員的總營業額"。而 "某業務員的總營業額是多少？"，等同 "如果是某業務員，他的營業額總合是多少？"，不難想出統計此問題的函數是 SUMIF 函數或 SUM(IF()) 公式。
在介紹主題 SUMIF 函數之前，先回顧 "系列五：COUNTIF 函數"，
COUNTIF 函數語法：=COUNTIF(range,criteria)
所對應的陣列語意：{=COUNTIF(查找範圍,來源陣列)}。
可對照出
SUMIF 函數語法：=SUMIF(range,criteria,[sum_range])
所對應的陣列語意：{=SUMIF(查找範圍,來源陣列,[加總範圍])}。
其中 SUMIF 函數的
1) 來源陣列：多個 "查找條件" 形成的陣列，
2) 加總對象：加總範圍中，與查找範圍內條件符合者的同列儲存格。
一、SUMIF 函數陣列作法 ：如下圖所示，給定資料表 A1:D14，其中查找範圍 B2:B14，加總範圍 C2:C14
1. 來源陣列位址 A20:A21 即 {"Brown";"Jones"}：
選取 E20:E21 (存放結果陣列)，
輸入 =SUMIF(B2:B14,A20:A21,C2:C14)，
按下 CTRL+SHIFT+ENTER。
業務員 Brown 總銷售單位 46，業務員 Jones 總銷售單位 180。
2. Brown 與 Jones 的總銷售單位：
選取 E24 (存放單格結果陣列)，
輸入 =SUM(SUMIF(B2:B14,A20:A21,C2:C14))，
按下 CTRL+SHIFT+ENTER。
業務員 Brown 與 Jones 的總銷售單位 226。
二、SUMPRODUCT 函數作法：
1. 條件範圍 A20:A21：
選取 E27:E28，輸入
=SUMPRODUCT(($B$2:$B$14=A20)*1,$C$2:$C$14)，
(參考 "系列4"：TRUE*1 =1；FALSE *1 = 0)
按下 CTRL+ENTER。
(關於 CTRL+ENTER ，請參考 "系列五：COUNTIF 函數")
業務員 Brown 總銷售單位 46，業務員 Jones 總銷售單位 180。
2. 條件範圍 A20:A21：
選取 E29，輸入
=SUMPRODUCT((B2:B14=A20)+(B2:B14=A21),C2:C14)，
(注意: 為何沒有乘上 1？ )
按下 ENTER。
業務員 Brown 與 Jones 的總銷售單位 226。
結論：
1. 由 B J 總銷售單位，呼應了 "系列四：SUM 函數之二" 所提到：SUM 函數的陣列用法較 SUMPRODUCT 函數更簡潔。
2. 除了利用 SUMIF 函數的 "多格陣列" 作法外，此處亦可利用系列三、四介紹過的 "單格陣列函數"：SUM 函數，個別計算如下所示：
i. 業務員 Brown 總銷售單位：
選取 F20 (存放單格陣列函數)，
輸入 =SUM(($B$2:$B$14=A20)*$C$2:$C$14)
按下 CTRL+SHIFT+ENTER。
ii. 業務員 Jones 總銷售單位：
拖曳 F20 的 "填滿空點" 至 F21。
原因可參考 "系列三：SUM 函數之一"，或敬待 "單格陣列函數"主題。此用法亦較 SUMPRODUCT 函數更簡潔。
3. 承 2. 也可利用 IF 函數的 "多格陣列" 作法，搭配 SUM 函數的 "單格陣列" 作法，個別計算如下所示：
i. 業務員 Brown 總銷售單位：
選取 G20 (存放單格陣列函數)，
輸入 =SUM(IF($B$2:$B$14=A20,$C$2:$C$14))
按下 CTRL+SHIFT+ENTER。
ii. 業務員 Jones 總銷售單位：
拖曳 G20 的 "填滿空點" 至 G21。
敬請期待 IF 函數的 "多格陣列" 作法介紹。
4. 又若是 "如果是北區某業務員，他的營業額總合是多少？" 有兩個條件：北區、某業務員，那麼應該採用至少兩個 IF 的統計函數：SUMIFS 函數。下一主題登場。
5. SUMIF 函數、SUMIFS 函數與 SUM(IF) 公式又有何差異呢？敬請待續。
留言列表
