延續前二篇：
本篇要來練習如何互通陣列公式、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")
留言列表