2008.08_EXCEL公式：不重複個數

解析 "不重複個數" 公式的原理和意義
1] 計算"不重複個數" 的公式原理
公式:
=SUMPRODUCT(1/COUNTIF(A1:A5,A1:A5))
以下做個說明，敬請參考 & 指正
假設, 有 5 筆資料
AA, BB, BB, CC, CC, CC
則套用公式中 1/COUNTIF(A1:A5,A1:A5) ，每個會變成
1/1, 1/2, 1/2, 1/3, 1/3, 1/3
意思就是, 每一種有幾個, 就拆成幾分 (合起來是 1 )，去除了重複值。
最後再全部 SUMPRODUCT 起來就是不重複的資料個數了。

2] 解決資料中, 如果有 "空白格" 的方法
如果資料中, 含有空白格
公式　:
=SUMPRODUCT(1/COUNTIF(A1:A8,A1:A8))
公式返回 #DIV/0!
解決 "空白格" 方法為 :
公式中加多 IF(range<>"",…….
變成 :
=SUMPRODUCT(IF(A1:A8<>"",1/COUNTIF(A1:A8,A1:A8)))
原理說明 :
假設, A1:A8 有 6 筆資料 及 最後2筆資料, 為"空白格"
{"AA";"BB";"BB";"CC";"CC";"CC";"　";"　"}
COUNTIF(A1:A8,A1:A8) 每個會變成
{1;2;2;3;3;3;0;0}
跟著 1/COUNTIF(A2:A8,A2:A8) 會變成 :
{1/1; 1/2; 1/2; 1/3; 1/3; 1/3; 1/0; 1/0}
/>>
{1;0.5;0.5;0.333333333333333;0.333333333333333;0.333333333333333;#DIV/0!;#DIV/0!}
留意 #DIV/0!. 不重複的資料個數, 祇有3個, 如何能忽略誤值 ?

公式 :
=SUMPRODUCT(IF(A1:A8<>"",1/COUNTIF(A1:A8,A1:A8)))
=SUMPRODUCT(IF({TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;FALSE;FALSE},{1;0.5;0.5;0.333333333333333;0.333333333333333;0.333333333333333;#DIV/0!;#DIV/0!}))
=SUMPRODUCT({1;0.5;0.5;0.333333333333333;0.333333333333333;0.333333333333333;FALSE;FALSE})

因為 SUMPRODUCT 能忽略邏輯值 (FALSE = 0),　

最後, 返回結果 :

=3

3] 把 "不重複個數" 的公式, 變成 "重複個數" 的公式

"不重複個數" 的公式為 :

=SUMPRODUCT(1/COUNTIF(A1:A10,A1:A10))

公式改一改, 就會變成, "重複個數" 的公式 :

=SUMPRODUCT(1-1/COUNTIF(A1:A10,A1:A10))

來源網址