excel 方程的解釋？

12-27

大家好，
一個有關excel的問題：
我想知道如何計算出某一列中不重複項的個數（重複數據只計一次）？
在網上找到了這個回答：
」假設數據在A2:A21，則統計數據個數（重複數據只計算1次）的公式為：=SUMPRODUCT(1/COUNTIF(A2:A21,A2:A21))「
請問該函數如何理解？ countif 括弧中重複的兩次代表什麼？之前的 1/ 代表什麼？為什麼用sumproduct?
同時請問，還有沒有其他公式可以實現同樣的結果？
最後，可否給出一個公式以計算所有重複項的個數（不分類呈現，比如不通過該方式：1有5個，2有3個。而只給出所有重複項的總個數，比如上例中，重複項共8個。）
非常感謝！

這個公式很巧妙，先利用COUNTIF()統計每個單元格內容出現的次數N，然後取倒數，這樣每個單元格就變成了1/N，由於1/N必然出現N次，那麼相加後就是去重後的數量。

COUNTIF(A2:A21,A2:A21))意思是對A2到A21分別用A2到A21的內容做一次重複內容統計，結果是一個數組（N1, N2, N3…Nm），每個值是本單元格內容的重複次數。

首先先解釋一下公式。

這個公式的確是非常巧妙，我來詳細地解釋一下。

說明
COUNTIF 函數對區域中滿足單個指定條件的單元格進行計數。

語法
COUNTIF(range, criteria)
參數
range 必需。要對其進行計數的一個或多個單元格，其中包括數字或名稱、數組或包含數字的引用。空值和文本值將被忽略。
criteria 必需。用於定義將對哪些單元格進行計數的數字、表達式、單元格引用或文本字元串。例如，條件可以表示為 32、">32" 、B4、"蘋果" 或 "32"。

根據以上微軟官方的說明，range就是區域，criteria就是條件（被統計的單元格）。

於是，我們可以把COUNTIF(A2:A21,A2:A21)拆開成20個公式來理解。

COUNTIF(A2:A21,A2) 在區域A2:A21中對和A2一樣的單元格進行計數。
COUNTIF(A2:A21,A3) 在區域A2:A21中對和A3一樣的單元格進行計數。
……
COUNTIF(A2:A21,A21) 在區域A2:A21中對和A21一樣的單元格進行計數。

為了直觀一點，我們把這20個公式的結果逐一計算出來如下：

糾正：圖中SUMPRODUCT(C2:C16)應作SUMPRODUCT(E2:E21)

可以看到在A1:A21中只有1,2,3,4,5這5個數字，顯然『不重複項的個數』等於5。

COUNTIF(A2:A21,A2)統計出了 3 出現的次數是 5 次

COUNTIF(A2:A21,A4)也統計出了 3 出現的次數是 5 次

COUNTIF(A2:A21,A11)也統計出了 3 出現的次數是 5 次

COUNTIF(A2:A21,A16)也統計出了 3 出現的次數是 5 次
COUNTIF(A2:A21,A19)也統計出了 3 出現的次數是 5 次
對他們都取倒數，得到的結果都是0.2，再全部相加，得到的結果必然是1。

對於其他數字也是一樣，得到的結果必然都是1。

把它們全部相加的結果一定是等於5的。

所以COUNTIF(A2:A21,A2:A21)實際上就是這20個公式的簡寫。

關於題主的問題：

最後，可否給出一個公式以計算所有重複項的個數（不分類呈現，比如不通過該方式：1有5個，2有3個。而只給出所有重複項的總個數，比如上例中，重複項共8個。）

我沒有想出只用公式來解決這個問題，只能給出一個用VBA寫的宏來解，不過也不是很難，思路就是把不止出現了一次單元格個數記錄下來。

考慮到題主理解的延續性，這段代碼用的也是COUNTIF。如果題主能看懂之前的解釋，那麼下面這段代碼應該不難理解。

Sub macro1() "除去只出現一次的單元格就是重複的單元格 For Each Rng In Worksheets("Sheet1").Range("A2:A21") If WorksheetFunction.CountIf(Range("A2:A21"), Rng) &<&> 1 Then Range("A23").Value = Range("A23").Value + 1 End If Next End Sub

這段代碼是為了理解方便，肯定不是執行效率最高的，題主有興趣可以想想別的方法。

@shotgun 說的很好了，sumproduct也可以換成sum的數組形式（ctrl+shift+enter），這樣可能更好理解

這種巧妙的用法對我這種常用不到的人常學常忘。
折中一下：使用countif函數，統計重複條數，再使用篩選得到想要的

雖然我也喜歡用VBA，但Excel早幫我們寫好了函數。

以Excel 2010為例

選中需要計算的數組，滑鼠點最上面一行的數據-&>刪除重複項-&>確定，然後看最後一列的行值是幾就好了