標籤：

[Excel]用公式刪除重複項

03-17

太長不看版:

  1. Excel可以用數據>刪除重複項 把一組數據裡面的重複單元格刪掉, 但是這種操作要依靠手動, 不能自動更新, 所以如果你的數據來源發生更新的時候要自己重新做一遍, 容易出錯
  2. 要想能夠自動完成數據>刪除重複項 的功能, 用下面這個公式就可以了. 假設需要刪除重複項的這組單元格是A2:A11, 刪除重複項以後的唯一值列表放在C列, 在C2放置這個公式, CTRL+SHIFT+ENTER完成輸入, 之後把單元格向下填充

=IFERROR(INDEX($A$2:$A$11,MATCH(0,COUNTIF($C$1:C1,$A$2:$A$11),0)),"")

搞定.

我們都知道可以用數據>刪除重複項獲得一個唯一元素列表。很多時候這個操作已經夠我們用了，但是其實呢，就像篩選可以用公式實現一樣，刪除重複項這個操作也一樣可以用公式搞定。

方法就是把index, match還有countif連起來用，當然iferror也要用上。

內容來源：How to Get Unique Items from a List in Excel Using Formulas

比如A2:A11是這麼一組數據：1，1，1，2，2，3，4，5，5，6

然後做一個刪除重複項就成了C2：C7的這一些東西：1，2，3，4，5，6

公式長成這樣：=IFERROR(INDEX($A$2:$A$11,MATCH(0,COUNTIF($C$1:C1,$A$2:$A$11),0)),"")

上面的公式可以拆成下面幾塊：

  • countif($C$1:C1,$A$2:$A$11)，這個記作countif(...)
  • match(0, countif(...),0)，這個記作 match(...)
  • index($A$2:$A$11, match(...)), 這個記作 index(...)
  • match找不到的時候會報錯，所以最外面再套一個iferror(index(...),"")

上面這個公式是一個數組公式，完成輸入以後要按ctrl+shift+enter，而不是enter。

1. countif

這個地方最繞的應該還是countif. 我盡量說明一下.

首先如果是=countif($A$2:$A$11,$A$2:$A$11) 如果在excel裡面用數組形式輸入這個公式, 返回的結果是 3. 這個3指的是$A$2:$A$11 1這個值出現了3次. 如果我們把A2:A11的數據重新排列一下順序: 2, 1, 3, 4, 1, 2, 5, 1, 5, 6, 這個公式就會返回2, 因為2這個值出現了2次. 如果我們在公式外面再套一個其他的公式, 然後用公式> 公式求值去看一下計算過程的話就會看到:

對=sum(countif($A$2:$A$11,$A$2:$A$11))作公式求值的第一次求值結果

可以看到對=countif($A$2:$A$11,$A$2:$A$11)求值的結果是:{2;3;1;1;3;2;2;3;2;1} , 也就是這個數組裡面, 2出現了2次,1出現了3次,3出現了1次,4出現了1次,1出現了3次,2出現了2次,5出現了2次,1出現了3次,5出現了2次,6出現了1次.

這就是當我們把criteria設置成一個範圍內的單元格的時候會產生的結果.

2. match和index

接下來看看第二層, match. 還是藉助公式求值這個工具, 這次我們在C2這個單元格輸入

=MATCH(0,COUNTIF($C$1:C1,$A$2:$A$11),0)

記得按CTRL+SHIFT+ENTER! 返回結果是1

然後在C2這裡做一個公式求值, 求值結果是: =MATCH(0,{0;0;0;0;0;0;0;0;0;0},0)

從這個公式來說, MATCH返回1是沒問題的, 畢竟這個數組第一個就是0. 但是現在還是看不出來和我們向問題有什麼關聯. 但是接下來把index再套上去就不一樣了, 這個時候返回的結果是2. 如果我們把A2:A11裡面所有的2都替換成Feb呢? C2這個時候也會變成Feb. 這是肯定的, 畢竟=index($A$2:$A$11,1) 肯定就是返回這寫個單元格里的第一個嘛.

現在還不太看得出來為什麼這麼干就能獲得唯一值列表, 所以讓我們把C2往下填充, 哎, 居然真的就成了唯一值了!

讓我們對C7這個單元格做一下公式求值吧:countif(...) 求值以後變成了{1;1;1;1;1;1;1;1;1;0} . 這個時候只有最後一個元素是0, 自然match的時候就會返回最後一個元素的位置, 接下來用index再把位置轉換成元素, 也就是6.

下面那一堆#N/A 通過iferror就可以幹掉了.

所以最關鍵的地方還是在countif,拿一系列的單元格來做countif的criteria可以說是一個奇技淫巧了, 在沒有寫這篇專欄文章以前我真完全沒有想到還可以這麼干, 下面我畫一個圖解嘗試解釋一下吧.

比如在上面這個數據表裡面, B列有三個數據:Ben, Jane, Tom,那麼現在下面這個公式返回的是幾呢?

=countif(B:B,A1)

用A1的值(Ben)作為標準去數B列, B列就只有一個Ben, 那當然就是返回1了啦.

同樣的, =countif(B:B, A2)呢?B列有一個Jane, 所以也是1.

在上面的兩個例子里, 被搜索的範圍是沒有改變的, 都是B列的Ben, Jane, Tom著三個單元格, 但是搜索的標準變了. 那麼如果我們把這兩次搜索合併成一次搜索是否可行呢? 當然是可以的:

=countif(B:B,A1:A2)

按ctrl+shift+enter轉換為數組公式, 這個時候返回的值實際上就是{1;1} 了, 這個結果的意思就是第一次搜索(用A1作標準), 找到了1個, 第二次搜索(用A2作標準), 也找到了1個. 這個就是拿一組數據做countif的criteria時候發生的事情.

那麼回到我們的公式

=INDEX($A$2:$A$11,MATCH(0,COUNTIF($C$1:C1,$A$2:$A$11),0))

這裡面很關鍵的一點, 就是我們被搜索範圍的設定: 從當前列第一個單元格開始($C$1)到當前單元格上一個格子(對C2單元格, 我們設置成了C1, 這個相對關係在向下填充的時候被保留了).

對於C2單元格來說,它上面什麼都沒有,所以countif(...) 返回的肯定是一堆{0;0....;0},所以呢這個時候=match(0,countif(...),0)跟著返回的就是1, 那再交給index就會把用作criteria的這堆單元格的第一個拿來填到C2裡面去.

接下來到C3單元格, 對它來說, 上面已經有了A2:A11這組單元格裡面的第一個, 這樣一來再去作countif(...) 的時候自然就不會全是0了, A2:A11裡面所有值和A2相同的單元格, 對應的countif結果都會變成1. 對於前面提到的Feb, 1, 3, ...這組數據, 到C3的時候countif(...)返回的結果就會是{1;0;0;...} ,那match(...) 返回的結果也就成了2, 再用index就可以獲得排在第二的單元格的值1

至於C4單元格,前面兩個單元格都已經分別是Feb和1了,那countif的結果肯定會有更多的1,至於下一個0就只會出現在既不是Feb也不是1的單元格, 把這個位置用match拿出來, 再用index轉成對應的值, 也就是3. 下面的都是以此類推.

這個想法還真是聽精妙的, 而且讓我覺得對Excel的功能有了新的認識


推薦閱讀：

TAG:MicrosoftExcel | Excel公式 | Excel技巧 |