EXCEL中計算不重複單元格的個數

computer 2010-02-26 16:23:15 閱讀316 評論0   字型大小：大中小 訂閱

=SUM(1/COUNTIF($A$1:$A$6,$A$1:$A$6))

數組公式

= SUM(1/COUNTIF(區域,區域))

個公式是計算區域中不重值的個數的經典公式。

準備

區域A1:A10的數據分別是：公式、計算、可以、公式、公式、複製、這個、重複、可以、不可以

B1寫入公式：

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

它是一個數組公式，同時按 hift+Ctrl+Enter 三鍵結束。

觀察

1、在編輯欄，像下面一樣選取函數Countif()部分，然後按F9：

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

你看到的結果會是：

=SUM(1/{3;1;2;3;3;1;1;1;2;1})

如果這時你按下Esc鍵，公式會還原為原來的狀態。

2、在結果的基礎上繼續選取，=SUM({1/{3;1;2;3;3;1;1;1;2;1})或者在原公式上選取=SUM(1/COUNTIF(A1:A10,A1:A10))，按F9，結果為：

=SUM({0.333333333333333;1;0.5;0.333333333333333;0.333333333333333;1;1;1;0.5;1})

3、選取全部公式按下F9或者直接按三鍵，你看到的都是同一結果：7。

分析

1、Countif(A1:A10,A1:A10)是數組計算，運算過程相當於：

Countif(A1:A10,A1)

Countif(A1:A10,A2)

……

Countif(A1:A10,A10)

結果如上面所示，是一個數組，表示區域中等於本單元格數據的個數。

2、用1除以個數，是個非常妙的想法，要結合前後計算才能領會好它的作用。把上一步的結果整理一下，用分數代替小數：

=SUM({1/3;1/3;1/3;1/2;1/2;1;1;1;1;1})

區域中有三個重複的數「公式」，那麼這一步就有三個1/3，有兩個「可以」，那麼這一步的結果就有兩個1/2，如果有5個重複的數，那麼這一步的結果就有5個1/5，如此類推。

3、這一步幾乎不用說了。Sum()進行的是加法運算，3個1/3相加，結果為1,2個1/2相加，結果也是1。如果區域中有8個重複的數，那麼一定會有8個1/8相加，結果也是1。

重複的數經過三步運算，到最後結果都是1。

把所有結果加起來，就是區域中不重複的個數。

QUOTE:

以上公式解釋源自山版http://club.excelhome.net/viewthread.php?tid=159374&extra=&page=1#368625

PS：excelhome裡面的高手，這個方法確實可行。在輸完公式的時候，記得不要按回車，而應當按ctrl+shift+enter這三個組合鍵，不然結果出來會是小數。

推薦閱讀：