延續前二篇:
本篇要來練習如何互通陣列公式、SUMIF、SUMPRODUCT等函數的使用。
在下圖左的資料清單裡,如果要計算所以A*B的總和,先把每一組A*B算出後(C欄),再予以加總。但是,如果使用陣列公式,則公式顯的簡捷。
公式:{=SUM(A3:A8*B3:B8)}
這是陣列公式,所以輸入完成要按 Ctrl+Shfit+Enter 鍵,Excel 會自動加上「{}」。
除了使用陣列公式,也可以使用 SUMPRODUCT 函數執行陣列運算。
先選取儲存格A2:B8,按 Ctrl+Shift+F3 鍵,勾選「頂端列」,定義名稱:甲、乙。
公式:=SUMPRODUCT(甲,乙)
公式:=SUMPRODUCT(甲*乙)
以上二者結果相同,而陣列公式可以這樣寫:
公式:{=SUM(甲*乙)}
看看不同的例子,下圖中要計算「性別為男」者的數量總和。
先選取儲存格A2:B10,按 Ctrl+Shift+F3 鍵,勾選「頂端列」,定義名稱:性別、數量。
陣列公式這樣設計:
公式:{=SUM(IF(性別="男",數量,))}
在 IF 函數的第 False 參數是空白的,其結果會傳回 FALSE,運算過程中會被視為0。
或是陣列公式改成這樣:
公式:{=SUM(IF(性別="男",數量,0))}
使用 SUMIF 函數:
公式:=SUMIF(性別,"男",數量)
使用 SUMPRODUCT 函數:
公式:=SUMPRODUCT((性別="男")*數量)
再換另一種表格呈現方式,下圖中要計算「已繳交」者的金額總和。
陣列公式的寫法:
公式:{=SUM(IF(性別="男",數量,))}
公式:{=SUM(IF(性別="男",數量,0))}
使用 SUMIF 函數:
公式:=SUMIF(性別,"男",數量)
使用 SUMPRODUCT 函數:
公式:=SUMPRODUCT((性別="男")*數量)
再來進化,下圖中要計算「丙+丁」為正數者的總和。
選取儲存格A1:B9,按 Ctrl+Shift+F3 鍵,勾選「頂端列」,定義名稱:丙、丁。
陣列公式寫法:
公式:{=SUM(IF(丙+丁>0,丙+丁,0))}
在陣列公式中直接判斷「丙陣列+陣列」是否大於 0,如果成立,則傳回「丙+丁」的陣列;否則傳回 0。
公式:{=SUM(IF(丙+丁>0,丙+丁,))}
SUMPRODUCT 函數這樣表示:
公式:=SUMPRODUCT((丙+丁>0)*(丙+丁))
在下圖的例子中,要計算「男生已繳交」者的金額總和。
選取儲存格A1:D13,按 Ctrl+Shift+F3 鍵,勾選「頂端列」,定義名稱:學生、性別、金額、繳交。
陣列公式這樣設計:
公式:{=SUM(IF((性別="男")*(繳交="V"),數量,))}
使用 SUMIFS 函數:
公式:=SUMIFS(金額,性別,"男",繳交,"V")
使用 SUMPRODUCT 函數:
公式:=SUMPRODUCT((性別="男")*(繳交="V")*數量)
在下圖的例子中,要計算「一年級女生未繳交」者的金額總和。
選取儲存格A1:E13,按 Ctrl+Shift+F3 鍵,勾選「頂端列」,定義名稱:學生、年級、性別、金額、繳交。
陣列公式這樣設計:
公式:{=SUM(IF((年級="一")*(性別="女")*(繳交<>"V"),數量,))}
使用 SUMIFS 函數:
公式:=SUMIFS(數量,年級,"一",性別,"女",繳交,"<>V")
使用 SUMPRODUCT 函數:
公式:=SUMPRODUCT((年級="一")*(性別="女")*(繳交<>"V")*數量)
最後這個例子,要建立各年級中不同性別的「應收、已繳」金額。
選取儲存格A1:E13,按 Ctrl+Shift+F3 鍵,勾選「頂端列」,定義名稱:學生、年級、性別、金額、繳交。
(1) 計算應收金額表
儲存格H2: =SUMPRODUCT((年級=H$1)*(性別=$G2)*金額)
儲存格H2: =SUMIFS(金額,性別,$G2,年級,H$1)
(1) 計算已繳金額表
儲存格H9: =SUMPRODUCT((年級=H$9)*(性別=$G10)*(繳交="V")*金額)
儲存格H9: =SUMIFS(金額,性別,$G2,年級,H$1,繳交,"V")
留言列表