最近在學校的 Excel 程式單元中，教完 COUNTIF 和 SUMIF 函數後，接著教 SUMPRODUCT 函數，以下是學生的練習題，你也可以來試試：
【準備工作】
選取儲存格A1:D26，按一下 Ctrl+Shift+F3 鍵，勾選「頂端列」，定義名稱：日期、人員、性別、數值。
【輸入公式】
(1) 性別：男/女，求「人員」個數和其數值總和
儲存格G2：=SUMPRODUCT(--(性別=F2))
儲存格H2：=SUMPRODUCT(--(性別=F2),數值)
其中「--」的用意是要將 True/False 陣列經由運算後變成 1/0 陣列，所以以下的做法都可以達到相同的結果：
- =SUMPRODUCT((性別=F2)+0)
- =SUMPRODUCT((性別=F2)*1)
- =SUMPRODUCT((性別=F2)/1)
- =SUMPRODUCT((性別=F2)^1)
複製儲存格G2:H2，貼至儲存格G2:H3
接著，改以 COUNTIF 函數和 SUMIF 函數來練習：
儲存格G2：=COUNTIF(性別,F2)
儲存格H2：=SUMIF(性別,F2,數值)
(2) 人員：甲/乙/丙/丁/戊/己，求「奇數」個數和其數值總和
儲存格G6：=SUMPRODUCT((人員=F6)*(MOD(數值,2)=1))
儲存格H6：=SUMPRODUCT((人員=F6)*(MOD(數值,2)=1)*數值)
其中使用 MOD 函數來求得數值除以 2 的餘數，若是餘 1，則為奇數。(反之為偶數)
複製儲存格G6:H6，貼至儲存格G6:H11
【進階】若改為陣列公式：(輸入公式後要按 Ctrl+Shift+Enter 鍵)
儲存格G6：{=SUM(IF((人員=F6)*(MOD(數值,2)=1),1,FALSE))}
儲存格H6：{=SUM(IF((人員=F6)*(MOD(數值,2)=1),數值,FALSE))}
(3) 日期：一月/二月/三月/四月，求「女生」個數和其數值總和
儲存格G14：=SUMPRODUCT((MONTH(日期)=ROW(1:1))*(性別="女"))
儲存格H14：=SUMPRODUCT((MONTH(日期)=ROW(1:1))*(性別="女"),數值)
透過 MONTH 函數取出日期陣列中的月份成為月份陣列，因為ROW(1:1)=1，往下複製時可以產生 ROW(2:2)=2、ROW(3:3)=3、…。
複製儲存格G14:H14，貼至儲存格G14:H17
【進階】若改為陣列公式：(輸入公式後要按 Ctrl+Shift+Enter 鍵)
儲存格G14：{=SUM(IF((MONTH(日期)=ROW(1:1))*(性別="女"),1,FALSE))}
儲存格H14：{=SUM(IF((MONTH(日期)=ROW(1:1))*(性別="女"),數值,FALSE))}
(4) 日期：星期日/星期一/星期二/星期三/星期四/星期五/星期六，求「人員」個數和其數值總和
儲存格G20：=SUMPRODUCT(--(WEEKDAY(日期,1)=ROW(1:1)))
儲存格H20：=SUMPRODUCT((WEEKDAY(日期,1)=ROW(1:1))*數值)
透過 WEEKDAY 函數取出日期陣列中的星期成為星期陣列，其中參數 1，對照星期日為 1，…，星期六為 7。
複製儲存格G20:H20，貼至儲存格G20:H26
【進階】若改為陣列公式：(輸入公式後要按 Ctrl+Shift+Enter 鍵)
儲存格G20：{=SUM(IF(--(WEEKDAY(日期,1)=ROW(1:1)),1,FALSE))}
儲存格H20：{=SUM(IF(--(WEEKDAY(日期,1)=ROW(1:1)),數值,FALSE))}
留言列表