2018年6月19日 星期二

Excel-計算編號為N的倍數且數值大於M的個數(SUMPRODUCT,MOD,ROW)

在 Excel 的工作表中,如下圖的數值清單裡,要如何計算編號為 3 的倍數者,其大於50的個數有幾個,該如何處理?
Excel-計算編號為N的倍數且數值大於M的個數(SUMPRODUCT,MOD,ROW)

【公式設計與解析】
儲存格D2:=SUMPRODUCT((MOD(ROW(B2:B26),3)=1)*(B2:B26>50))
在 SUMPRODUCT 函數裡,使用兩個條件式來運算。
(1) 條件一:MOD(ROW(B2:B26),3)=1
判斷 MOD(ROW(B2:B26),3)= 1 是否成立,傳回 TRUE/FALSE 陣列。
因為 ROW 函數會傳回儲存格的列號,所以 ROW(B2)=2、ROW(B3)=3、ROW(B4)=4、……。
MOD(ROW(B2),3)=2、MOD(ROW(B3),3)=0、MOD(ROW(B4),3)=1、......。
因為編號 A003 在儲存格B4,所以利用 MOD(ROW(B2:B26),3)=1 來判斷編號是否為 3 的倍數。
(2) 條件二:B2:B26>50
判斷儲存格 B2:B26 的內容是否大於 50,傳回 TRUE/FALSE 陣列。
(3) ((MOD(ROW(B2:B26),3)=1)*(B2:B26>50)
其中的「*」運算會將 TRUE/FALSE 陣列轉換為 1/0 陣列。
最後於 SUMPRODUCT 函數中將 1/0 陣列相加,結果即會符合二個條件的個數。
文章標籤

沒有留言:

張貼留言

檢視其他文章

好康東東