2016年11月29日 星期二

Excel-計算符合條件者的不重覆數量(SUMPRDUCT,COUNTIF)

網友問到一個 Excel 公式運算的問題:如下圖,如何求得在『配編』欄位中各個年月的配編數(幾種不一樣的類別)?
在下圖中,年月是10501者,有 1, 2, 3, 4 共四種配編類別,該如何求得?
Excel-計算符合條件者的不重覆數量(SUMPRDUCT,COUNTIF)

【公式設計與解析】
儲存格G2:
=SUMPRODUCT(($A$2:$A$26=F2)*(1/COUNTIF($C$2:$C$26,$C$2:$C$26)))
(1) $A$2:$A$26=F2
在 SUMPRODUCT 函數中設定條件判斷儲存格A2:A26中和儲存格F2相同者。
(2) COUNTIF($C$2:$C$26,$C$2:$C$26)
在 SUMPRODUCT 函數中,計算儲存格C2:C26中每個數的重覆個數。例如:『1, 1, 1, 2, 2, 3, 3, 4, 4』傳回『3, 3, 3, 2, 2, 2, 2, 2, 2』
(3) 1/COUNTIF($C$2:$C$26,$C$2:$C$26)
將第(2)的傳回值取倒數,例如『3, 3, 3, 2, 2, 2, 2, 2, 2』傳回『1/3, 1/3, 1/3, 1/2, 1/2, 1/2, 1/2, 1/2, 1/2』,當在 SUMPRODUCT 函數加總時會得到結果『4』。
可另外參考以下二篇的說明:

沒有留言:

張貼留言

檢視其他文章

好康東東