如何用 Excel 計算資料串裡面的不同值之個數？
Count Distinct in Excel
今天朋友問了我一個 Excel 的問題，如果有一串資料，想要計算裡面的不同值共有幾個，要怎麼作？這個問題在資料庫很簡單，就是 select + count + group by 就作完了，但在 excel 嘛，還是拜請 google 大神，結果第一篇就得到這個答案。
=SUMPRODUCT((A1:A10<>"")/COUNTIF(A1:A10,A1:A10&""))
A1:A10 當然是 Range，可以自己替換，但這一串到底在作什麼，我試著用我的理解解釋看看。
假設有一串資料 A1 到 A10 共 10 個儲存格，共有 4 個 A、3 個 B、2 個 C、1 個空白，裡面不同的值就是 ABC 共 3 個，所以要得到的答案就是3，我們用上面的公式跑出來的結果如下：
判斷公式作啥，當然從括號裡面來，首先是 (A1:A10<>"")，這句判斷 Range 裡的每一個值是否不等於空字串，換句話說就是儲存格內有值為 true (1)，儲存格內沒有值就是 false (0) 囉。
那除號 (/) 後面的 COUNTIF(A1:A10,A1:A10&"") 又是什麼意思，它計算每一個值出現的次數，第二個參數之所以 AND (&) 空字串 ("")，是希望計次時用字串處理，這樣遇到空字串也計次得到，不會產生 0 的結果。
所以我們得到了一個全部是 true 跟 false 的資料陣列，以及一個全是次數的資料陣列，接下來相除 (/)，得到：
以 A 來說出現了 4 次，結果經過了上面的計算，得到的就是 1/4，這樣最後我們再用 SUMPRODUCT 加起來，就剛好是一個完整的 1，相同值因此不會被重覆計算而達到我們要的目的了。
至於最後一步為什麼要用 SUMPRODUCT 而不用 SUM 呢，這部份我也不瞭解，不過用 SUM 是不行的，所以我猜應該是因為 SUM 要的是數值的型別的關係吧。
最後的最後，附上一個不錯的討論串，是這個方法的一些延伸用法
http://www.pcreview.co.uk/forums/thread-1780806.php
今天朋友問了我一個 Excel 的問題，如果有一串資料，想要計算裡面的不同值共有幾個，要怎麼作？這個問題在資料庫很簡單，就是 select + count + group by 就作完了，但在 excel 嘛，還是拜請 google 大神，結果第一篇就得到這個答案。
=SUMPRODUCT((A1:A10<>"")/COUNTIF(A1:A10,A1:A10&""))
A1:A10 當然是 Range，可以自己替換，但這一串到底在作什麼，我試著用我的理解解釋看看。
假設有一串資料 A1 到 A10 共 10 個儲存格，共有 4 個 A、3 個 B、2 個 C、1 個空白，裡面不同的值就是 ABC 共 3 個，所以要得到的答案就是3，我們用上面的公式跑出來的結果如下：
判斷公式作啥，當然從括號裡面來，首先是 (A1:A10<>"")，這句判斷 Range 裡的每一個值是否不等於空字串，換句話說就是儲存格內有值為 true (1)，儲存格內沒有值就是 false (0) 囉。
那除號 (/) 後面的 COUNTIF(A1:A10,A1:A10&"") 又是什麼意思，它計算每一個值出現的次數，第二個參數之所以 AND (&) 空字串 ("")，是希望計次時用字串處理，這樣遇到空字串也計次得到，不會產生 0 的結果。
所以我們得到了一個全部是 true 跟 false 的資料陣列，以及一個全是次數的資料陣列，接下來相除 (/)，得到：
以 A 來說出現了 4 次，結果經過了上面的計算，得到的就是 1/4，這樣最後我們再用 SUMPRODUCT 加起來，就剛好是一個完整的 1，相同值因此不會被重覆計算而達到我們要的目的了。
至於最後一步為什麼要用 SUMPRODUCT 而不用 SUM 呢，這部份我也不瞭解，不過用 SUM 是不行的，所以我猜應該是因為 SUM 要的是數值的型別的關係吧。
最後的最後，附上一個不錯的討論串，是這個方法的一些延伸用法
http://www.pcreview.co.uk/forums/thread-1780806.php
留言