2016年2月6日 星期六

Excel-計算儲存格範圍中不重覆的數值個數(SUMPRODUCT,COUNTIF)

有網友問到:在 Excel 的工作表中,如何求取一個儲存格範圍中的不重覆數值個數?參考下圖,網友使用了以下的公式:(錯誤結果)
(X) 儲存格G3:{=SUM(COUNTIF(B2:E11,B2:E11))},這是陣列公式。
Excel-計算儲存格範圍中不重覆的數值個數(SUMPRODUCT,COUNTIF)
網友的公式有誤,這是因為在陣列公式中,每個儲存格都會計算和自己相同數值的個數,但是每一個重覆的數值,也都執行了相同的動作,因此結果會傳回重覆計算的結果。
Excel-計算儲存格範圍中不重覆的數值個數(SUMPRODUCT,COUNTIF)
我們來修改網友的公式:
(1) 使用陣列公式
儲存格G3:{=SUM(1/COUNTIF(B2:E11,B2:E11))}
輸入完成要按 Ctrl+Shift+Enter 鍵,Excel 會自動加上「{}」。
你只要將 COUNTIF(B2:E11,B2:E11) 再取『倒數』即可。因為如果有 2 個重覆,則儲存格會傳回 1/2,如果有 3 個重覆,則儲存格會傳回 1/3,如果有 4 個重覆,則儲存格會傳回 1/4,...,不管幾個重覆,最後加總後的結果都會為『1』。
Excel-計算儲存格範圍中不重覆的數值個數(SUMPRODUCT,COUNTIF)

(2) 使用 SUMPRODUCT 函數
如果你對陣列公式的操作不了解,不妨改用 SUMPRODUCT 函數,其運作概念和陣列公式相同。
儲存格G3:SUMPRODUCT(1/COUNTIF(B2:E11,B2:E11))

沒有留言:

張貼留言

檢視其他文章

好康東東