學不完．教不停．用不盡

接續上一篇文章：Excel-計算符合多條件的個數(COUNTIFS,SUMPRODUCT)

在多條件的運算中，有些要執行邏輯 AND 運算，有些要執行邏輯 OR 運算。以下的例子中，是要求取符合多個被選取的星期幾者的小計。（參考下圖）

 

【公式設計與解析】

選取A欄至D欄中的資料，按 Ctrl+Shift+F3 鍵，勾選「頂端列」，定義名稱：人員、日期、星期、數量。

 

1. 利用 SUMIF 函數加總多個結果

儲存格F5：=SUMIFS(數量,人員,F$4,星期,"星期一")+SUMIFS(數量,人員,F$4,星期,
"星期三")+SUMIFS(數量,人員,F$4,星期,"星期五")

如果使用三個 SUMIF 函數，分別求取固定條件：「星期一、星期三、星期三」的結果，但是該結果沒有彈性，條件更改時，要修改參數。

 

2. 利用陣列公式加總多個結果

{=SUM(IF(人員=F$4,IF(星期={"星期一","星期三","星期五"},數量),""))}

這是陣列公式，輸入完成要按 Ctrl+Shift+Enter 鍵，Excel 會自動加上「｛｝」。

如果利用陣列公式，可簡化公式長度。其中公式「SUM(IF(…」和函數「SUMIF」的觀念相同。

 

3. 利用 SUMPRODUCT 函數加總多個結果

儲存格F5：=SUMPRODUCT((WEEKDAY(日期,2)=($F$2:$L$2="V")*COLUMN
($A:$G))*(人員=F$4)*數量)

如果利用 SUMPRODUCT 函數，則可以達到較為彈性的方式來計算每個人員選取不同星期幾的小計。

條件：WEEKDAY(日期,2)=($F$2:$L$2="V")*COLUMN($A:$G)

WEEKDAY(日期,2)：傳回數字 1～7 代表星期一 ~星期日。

其傳回順序，恰好對應圖中的儲存格F1:L1。

COLUMN($A:$G)：代表 1~7 的陣列。

($F$2:$L$2="V")*COLUMN($A:$G)：在陣列中會傳回 $F$2:$L$2="V" 條件成立者對應的COUMN 數。

 

【延伸練習】

如果要將第2式改為像第3式是只計算有勾選的項目，該如何處理公式？

參考答案：

儲存格F5：{=SUM(IF(人員=F$4,IF(WEEKDAY(日期,2)=($F$2:$L$2="V")*COLUMN
($A:$G),數量),""))}

這是陣列公式，輸入完成要按 Ctrl+Shift+Enter 鍵，Excel 會自動加上「｛｝」。