學不完．教不停．用不盡

在 Excel 中的 SUMPRODUCT 函數是個十分好用的工具，如果能配合邏輯 AND 和 OR 的關念來設計公式，可以將單純的乘積和運算達到多條件的邏輯運算。

參考下圖，有A組和B組二組數列，以下用 6 個不同的運算來介紹 SUMPRODUCT 函數的應用。(關於 SUMPRODUCT 函數的介紹，請自行參考部落格中其他文章。)

【公式設計與解析】

為了解說方便，先選取儲存格B1:C21，按 Ctrl+Shift+F3 鍵，勾選「頂端列」，定義名稱：A組、B組。

(1) 計算A組中介於60到80的個數

儲存格E2：=SUMPRODUCT((A組>=60)*(A組<=80))

在 SUMPRODUCT 函數中的『*』相當於執行邏輯 AND 的運算，並且在運算(乘法)過程中可以將條件傳回值 TRUE／FALSE 轉換為 1／0。
        

(2) 計算B組中50以下和80以上的個數

儲存格E5：=SUMPRODUCT((B組<50)+(B組>80))

在 SUMPRODUCT 函數中此例的『+』運算(加法)過程中可以將條件傳回值 TRUE／FALSE 轉換為 1／0。(注意：『+』運算並非執行邏輯 OR 運算)
        

(3) 計算A組大於60『且』B組大於70的個數

儲存格E8：=SUMPRODUCT((A組>60)*(B組<=70))
        

(4) 計算A組小於40或B組小於30的個數

儲存格E11：=SUMPRODUCT(--((A組<40)+(B組<30)>0))

參考(2)的說明，而因為在 SUMPRODUCT 函數的運算中，符合『A組<40』條件和『B組<30』條件者可能同時成立，所以會傳回 2 ，因此再將條件判斷是否大於 0 (即包含 1 和 2 者)，並且使用『--』運算，將傳回值 TRUE／FALSE 轉換為 1／0，才能由 SUMPRODUCT 函數計算乘積和。
        

(5) 計算A組介於50到80『且』B組介於60到70的個數

儲存格E14：=SUMPRODUCT((A組>=30)*(A組<=60)*(B組>=40)*(B組<=80))

條件一：(A組>=30)*(A組<=60)；條件二：(B組>=40)*(B組<=80)

公式=SUMPRODUCT(條件一*條件二)

(6) 計算A組介於30到60『或』B組介於40到80的個數

儲存格E17：
=SUMPRODUCT(--((A組>=30)*(A組<=60)+(B組>=40)*(B組<=80)>0))

條件一：(A組>=30)*(A組<=60)；條件二：(B組>=40)*(B組<=80)

公式=SUMPRODUCT(--(條件一*條件二>0))

【思考一下】

當第(5)和第(6)之類的運算條件再增加為2個以上時，你會修改公式？

仿=SUMPRODUCT(((A組>=30)*(A組<=60))*((B組>=40)*(B組<=80)))

　=SUMPRODUCT(條件一*條件二)

改=SUMPRODUCT(條件一*條件二*條件三* … )

仿=SUMPRODUCT(--((A組>=30)*(A組<=60)+(B組>=40)*(B組<=80)>0))

改=SUMPRODUCT(--(條件一+條件二+條件三+ … >0))