歡度春節之後，利用寒假開學前，來讓學校同仁，再次有機會親近 Excel，看看能否利用試算表工具來讓工作效能提升，所以製作了研習的範例。本篇是關於「陣列+SUM+IF、SUMIF、SUMIFS、SUMPRODUCT」的綜合應用。
延續上一篇文章：Excel-陣列公式初探
在使用陣列在執行加總計算時，利用 SUM+IF、SUMIF、SUMIFS、SUMPRODUCT等公式可以得到相同結果，可謂異曲同工，以下希望能融會貫通，使用時如魚得水。
下載練習檔案：點我下載
1. 計算「A*B」的總和
儲存格E3：{=SUM(A2:A7*B2:B7)}
這是陣列公式，輸入完成要按 Ctrl+Shift+Enter 鍵，Excel 會自動加上「{、}」。
以下陣列公式做法相同，所以步驟說明加以省略。
陣列公式相當於執行：
{=SUM(A2:A7*B2:B7)}=SUM({4*9;7*2;8*4;6*5;2*1;8*5})
{=SUM(A2:A7*B2:B7)}=SUM({36;14;32;30;2;40})
2. 計算「甲*乙」的總和
選取儲存格A1:B7，按 Ctrl+Shift+F3 鍵，勾選「頂端列」，定義名稱：甲、乙。
「甲」代表儲存格A1:A7；「乙」代表儲存格B1:B7。
(1) 儲存格E2：{=SUM(甲*乙)}
{=SUM(甲*乙)}=SUM({8;7;2;3;2;8}*{3;8;6;5;6;4})
{=SUM(甲*乙)}=SUM({24;56;12;15;12;32})
(2) 儲存格E2：=SUMPRODUCT(甲,乙)
公式=SUMPRODUCT({8;7;2;3;2;8},{3;8;6;5;6;4})
(3) 儲存格E2：=SUMPRODUCT(甲*乙)
公式=SUMPRODUCT({8;7;2;3;2;8}*{3;8;6;5;6;4})
公式=SUMPRODUCT({8*3;7*8;2*6;3*5;2*6;8*4})（執行相乘）
公式=SUMPRODUCT({24;56;12;15;12;32})
公式=24+56+12+15+12+32=151（執行相加）
3. 計算「性別為男」者的數量總和
選取儲存格A1:B9，按 Ctrl+Shift+F3 鍵，勾選「頂端列」，定義名稱：性別、數量。
(1) 儲存格D2：{=SUM(IF(性別="男",數量,))}
性別="男"：{TRUE;TRUE;FALSE;FALSE;FALSE;TRUE;TRUE;FALSE}
數量：{75;61;55;99;36;58;56;42},))
在運算過程中 TRUE 會轉換為 1；FALSE 會轉換為 0。
=SUM(IF({1;1;0;0;0;1;1;0},{75;61;55;99;36;58;56;42},))
=SUM({75;61;0;0;0;58;56;0})
(2) 儲存格D2：{=SUM(IF(性別="男",數量,0))}
(3) 儲存格D2：=SUMIF(性別,"男",數量)
性別：{"男";"男";"女";"女";"女";"男";"男";"女"}
數量：{75;61;55;99;36;58;56;42})
取出「性別=男」者對應的數值予以加總：75+61+58+56=250
(4) 儲存格D2：=SUMPRODUCT((性別="男")*數量)
=SUMPRODUCT({1;1;0;0;0;1;1;0}*{75;61;55;99;36;58;56;42})
=SUMPRODUCT({75;61;0;0;0;58;56;0})
=75+61+58+56=250
4. 計算「已繳交」者的金額總和
選取儲存格B1:C9，按 Ctrl+Shift+F3 鍵，勾選「頂端列」，定義名稱：金額、繳交。
(1) 儲存格E2：{=SUM(IF(繳交="V",金額,))}
(2) 儲存格E2：{=SUM(IF(繳交="V",金額,0))}
(3) 儲存格E2：=SUMIF(繳交,"V",金額)
(4) 儲存格E2：=SUMPRODUCT((繳交="V")*金額)
5. 計算「丙+丁」為正數者的總和
選取儲存格A1:B9，按 Ctrl+Shift+F3 鍵，勾選「頂端列」，定義名稱：丙、丁。
(1) 儲存格E2：{=SUM(IF(丙+丁>0,丙+丁,0))}
公式=SUM(IF({-20;61;-7;-16;5;29;46;-48}>0,{-20;61;-7;-16;5;29;46;-48},0))
公式=SUM(IF({FALSE;TRUE;FALSE;FALSE;TRUE;TRUE;TRUE;FALSE},丙+丁,0))
公式=SUM(IF({0;1;0;0;1;1;1;0},{-20;61;-7;-16;5;29;46;-48},0))
公式=SUM({0;61;0;0;5;29;46;0})=141
(2) 儲存格E2：{=SUM(IF(丙*丁>0,丙+丁,))}
(3) 儲存格E2：=SUMPRODUCT((丙+丁>0)*(丙+丁))
=SUMPRODUCT(({-20;61;-7;-16;5;29;46;-48}>0)*(丙+丁))
=SUMPRODUCT(({FALSE;TRUE;FALSE;FALSE;TRUE;TRUE;TRUE;FALSE})*(丙+丁))
=SUMPRODUCT(({0;1;0;0;1;1;1;0})*({-20;61;-7;-16;5;29;46;-48}))
=SUMPRODUCT({0;61;0;0;5;29;46;0})
6. 計算「男生已繳交」者的金額總和
選取儲存格B1:D13，按 Ctrl+Shift+F3 鍵，勾選「頂端列」，定義名稱：性別、金額、繳交。
(1) 儲存格F2：{=SUM(IF((性別="男")*(繳交="V"),數量,))}
(2) 儲存格F2：=SUMIFS(金額,性別,"男",繳交,"V")
SUMIF 語法：SUMIFS(加總範圍, 條件1範圍, 條件1, [條件2範圍, 條件2], ...)
在此，不管參數中有幾個條件，都會執行邏輯 AND 運算。
符合：條件1 AND 條件2 AND 條件3 AND …
將對應的加總範圍予以加總。
(3) 儲存格F2：=SUMPRODUCT((性別="男")*(繳交="V")*數量)
7. 計算「一年級女生未繳交」者的金額總和
選取儲存格B1:E14，按 Ctrl+Shift+F3 鍵，勾選「頂端列」，定義名稱：年級、性別、金額、繳交。
(1) 儲存格G2：{=SUM(IF((年級="一")*(性別="女")*(繳交<>"V"),數量,))}
(2) 儲存格G2：=SUMIFS(數量,年級,"一",性別,"女",繳交,"<>V")
(3) 儲存格G2：=SUMPRODUCT((年級="一")*(性別="女")*(繳交<>"V")*數量)
8. 轉換摘要表格
選取儲存格B1:E14，按 Ctrl+Shift+F3 鍵，勾選「頂端列」，定義名稱：年級、性別、金額、繳交。
(1) 儲存格H2：=SUMPRODUCT((年級=H$1)*(性別=$G2)*金額)
(2) 儲存格H9：=SUMPRODUCT((年級=H$1)*(性別=$G2)*(繳交="V")*金額)
【自行練習】
【延伸學習】
留言列表