有網友問到:在 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))

arrow
arrow
    全站熱搜

    vincent 發表在 痞客邦 留言(3) 人氣()