這個Excel公式到底是怎麼不重複計數的?

4.8
(84)

在實際工作中經常需要計算不重複的值個數,比如:

【例】下圖所示的客戶消費明細表中,要求計算客戶的總人數。

公式我們都知道是:

=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函數可以。

擡擡小手賞個5星吧?

單擊星星進行評價

平均分 4.8 / 5. 評價計數: 84

還沒有人評價哦~

抱歉喲~如果您覺得不好,可以聯絡我們進行質量提升喲!

期待您的反饋

麻煩告知我們該如何改進,謝謝!

相關文章

網友評論

留下一個答复

請輸入你的評論!
請在這裡輸入你的名字

資料救援軟體

關於 Bitwar Data Recovery

3步驟救回500多種被刪除、格式化或丟失的檔案、照片、視訊、音訊,以及各種狀況丟失的資料檔案。

分享文章

喵喵醬

貓噗知識+致力於分享辦公室的點點滴滴, 包括但不局限於Windows、macOS桌面及筆記型電腦、蘋果手機的操作技巧以及故障解決方案、 OFFICE使用技巧、辦公室閑聊笑話等等知識+。

最新文章

熱門文章

實用工具