在實際工作中經常需要計算不重複的值個數,比如:
【例】下圖所示的客戶消費明細表中,要求計算客戶的總人數。
公式我們都知道是:
=SUMPRODUCT((1/COUNTIF(B2:B10,B2:B10)))
但用了這麼多年,又有多少人明白這個公式的含義呢?為什麼要用1/ ?
在Excel中根據條件統計數量,我們知道可以用用Countif函數可以完成。比如,統計上表中顧客A的出現次數:=COUNTIF(B2:B10,B2),B2:B9數據統計區域,B2為條件。即統計B2這個值在B2:B9區域被出現的次數。
但是,如果把上面公式中Countif 的第2個參數換成一個區域,結果會怎麼樣呢?比如
=COUNTIF(B2:B9,B2:B10)
結果會統計出表中所有行客戶的出現次數。選中公式部分按F9可以查看計算結果。
={3;2;2;3;1;2;2;1;3}
這代表了,顧客A出現了3次;顧客A出現了3次2;顧客B出現了2次;顧客D出現了3次…….為了更明白的顯示,請看下圖標註:
因此,可以明白為什麼用“1/”了嗎?為了讓重複的客戶都只計算1次,不重複公式巧妙的用了1/ 的方法:
=1/COUNTIF(B2:B9,B2:B10)
結果是A的出現次數變成了 1/3,B的出現次數變成了1/2….
={1/3;1/2;1/2;1/3;1;1/2;1/2;1;1/3}
如果把A的所有出現次數(3)加在一起正好是1,同理B的所有出現次數加在一起也是1,其他同理。也就是說,1/後求和,每個客戶的求和結果都是1。
而SUMPRODUCT就是統計求和的。因此最終的公式也出來了:
=SUMPRODUCT((1/COUNTIF(B2:B10,B2:B10)))
注:這里為什麼用sumproduct不用sum,因為countif第2個參數是區域(1組數),所以該公式為數組運算,sum不能直持數組運算,而sumproduct函數可以。