前幾個主題所介紹的 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) 公式又有何差異呢?敬請待續。
留言列表