在 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 陣列相加,結果即會符合二個條件的個數。

>

arrow
arrow
    文章標籤
    Excel SUMPRODUCT MOD ROW
    全站熱搜

    vincent 發表在 痞客邦 留言(1) 人氣()