2015年11月10日 星期二

Excel-計算數列中合於多條件的個數(AND和OR運算,SUMPRODUCT)

在 Excel 中的 SUMPRODUCT 函數是個十分好用的工具,如果能配合邏輯 AND 和 OR的關念來設計公式,可以將單純的乘積和運算達到多條件的邏輯運算。
參考下圖,有A組和B組二組數列,以下用 6 個不同的運算來介紹 SUMPRODUCT 函數的應用。(關於 SUMPRODUCT 函數的介紹,請自行參考部落格中其他文章。)
Excel-計算數列中合於多條件的個數(AND和OR運算,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>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>0))
參考(2)的說明,而因為在 SUMPRODUCT 函數的運算中,符合『A組<40 0="" 1="" 2="" nbsp="" strong="">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))

沒有留言:

張貼留言

檢視其他文章

好康東東