如下樣表:
在不同的日期段,項目參與人不同,但參與人可以參與多個日期段,所有,參與人一列中有許多重複人員。
要求:計算參與人數。
韓老師有幾個不同的公式,都可以得到正確結果。
一、SUMPRODUCT+COUNTIF
公式:
=SUMPRODUCT(1/COUNTIF(B2:B26,B2:B26))
解析:COUNTIF(B2:B26,B2:B26):是每一個姓名出現的次數組成的數組,
如下圖,是用此公式得出的每個姓名出現的次數。
1/COUNTIF(B2:B26,B2:B26)得到如下數組:
SUMPRODUCT將上述數組元素求和,即是不重複姓名的個數。
二、SUM+COUNTIF
公式:
{=SUM(1/COUNTIF(B2:B26,B2:B26))}
原理和第一個公式相同,只不過用數組運算方式,所以用
Excel109 | SUM+COUNTIF統計不重複值的個數
三、COUNT+FREQUENCY
=COUNT(1/FREQUENCY(MATCH(B2:B26,B2:B26,0),ROW(1:25)))
(MATCH(B2:B26,B2:B26,0)返回值如圖四F列所示;
FREQUENCY(MATCH(B2:B26,B2:B26,0),ROW(1:25))返回圖四F列出現的頻率,FREQUENCY只返回相同值中第一個值出現的頻率。
四、SUM+FREQUENCY
=SUM(--(FREQUENCY(MATCH(B2:B26,B2:B26,0),MATCH(B2:B26,B2:B26,0))<>0))
FREQUENCY(MATCH(B2:B26,B2:B26,0),MATCH(B2:B26,B2:B26,0)的返回值如圖四H列所示,再與0比較,<>0的返回TURE,否則返回FLASE,再用減負運算(--),將TURE或FLASE轉為1或0,再求和。
五、SUMPRODUCT+FREQUENCY
=SUMPRODUCT(--(FREQUENCY(MATCH(B2:B26,B2:B26,0),MATCH(B2:B26,B2:B26,0))<>0))
原來同公式四。
六、FREQUENCY+COUNT
=FREQUENCY(COUNTIF(OFFSET(B2,,,ROW(1:25)),B2:B26),1)
OFFSET函數已經講過了,大家自己下載案例練習,來分析一下這個公式哦。
你還有哪些公式?留言哦
閱讀更多 韓老師講office 的文章