星期五, 4月 06, 2007

MS Excel排除(篩選)掉相同資料

Major John @ 星期五, 4月 06, 2007
很多時候自己用excel處理資料時,常常會碰到一個欄位中有重複的資料,如果我想要知道這一欄裡面有幾筆不一樣的資料時,要是一個一個看、一個一個比較的話,不只會令人抓狂,更慘的是可能會遺漏掉。

像這欄一堆姓名有些重複的,我想要剃除掉重複的,每個人名都只列出一個就好。這就是這篇文章的目的了!(自己在try excel功能時一邊在看電視,臨時抓了幾個人名進去,沒有別的意思!)


有關「陣列公式」的用法,看了一堆文章自己實做了好幾次都還抓不到頭緒,在『[EXCEL]排除重複的資料』中我終於悟出究竟那個環節搞錯了!這也是本文的一個大重點,因為寫這篇就是我自己的筆記啦,免得哪天需要用到時忘了!

下載範例檔:





A、設定格式化條件

先說,上圖我加入了條件,讓有重複的姓名底色變成藍色的。只用到一個很簡單的函數,COUNTIF

COUNTIF(陣列,條件)
在陣列中符合條件的個數

陣列範圍是 A:A ,條件是 A2 ,表示在整個A欄中符合A2的有幾個。在這裡我想要知道哪些姓名的個數有「大於1」的,那就是有重複的姓名囉!因此,
=COUNTIF(A:A,A2)>1

就填在A2欄位中的 格式 > 設定格式化的條件。對話框中選擇 公式為 將公式打上去,在選擇 格式,當條件符合的時候,就套用該格式。如此,就可以很直接地看出來哪幾個姓名是重複的了。


B、進階篩選

然而,要是成千上萬筆資料,不就要刪到手軟?!而且眼花加上手滑,很難不出錯。
Excel有個很可愛的功能可以自動幫你篩選。「進階篩選」
下拉式選單中的 資料 > 篩選 > 進階篩選 是也~

資料範圍為A:A,記得要勾選「不選重複的紀錄」,「將篩選結果複製到其他地方」我們指定在C1。確定,Excel就會把資料複製到C欄了,沒有重複的資料。


C、陣列公式

上面這個方法挺好的,快速又方便。如果A欄的資料更動過,上述的步驟就要重作一遍。如果要自動化呢?當我們填好A欄的資料後,在D欄自動剃除掉重複的資料呢?這就要用函數啦!這是最好玩的。

我先在 下拉式選單的 插入 > 名稱 > 定義 ,去把我A欄中的姓名範圍用OFFSET定義出來,叫namels。
=OFFSET(Sheet1!$A$2,,,COUNTA(Sheet1!$A:$A)-1,1)

好處是,有這名稱namels(name list)之後,我在公式裡面要打這個範圍時只要寫namels,Excel就會代入OFFSET這個範圍了,才不會讓公式看起來很亂要Debug不容易。

一步一步來,先不用陣列公式。
在D2輸入
=IF(MATCH(A2,namels,0)=ROW()-1,ROW()-1,"")
看MATCH(A2,namels,0),在namels範圍中(即我們在A欄列出來的這些姓名)找和A2(陳水扁)相同的在第幾列。
外面再包個IF來判斷,如果MATCH的列數和ROW()-1相等時就顯示ROW()-1,如果不是就顯示空格("")。ROW()後面減1是因為資料都從第二列開始,把第二列當作第一列,因為namels設定的範圍OFFSET從A2第二列開始,所以A2是namels的第一列!


在D欄顯示出來的數字,就是該列沒有重複的資料,重複的資料都變成空白的了!沒有9, 14, 15, 16, 17, 18等,這幾列都是重複過前列的資料了!

這樣太分散了也不是我要的,我要的是可以像C欄一樣把沒有重複的資料都排在一起,不要有空格跳格,這樣還蠻醜的。這就要用到『陣列公式』和SMALL這函數囉!在這裡我先不打算把姓名的結果顯示出來,而是顯示數字,即,沒有重複姓名的列數。

=SMALL(陣列,數字)
SMALL這函數在找 陣列 中 第幾小的數字

如在D欄這陣列,第1小的數字是SAMLL(D:D,1),同理,第9小的數字是10,SMALL(D:D,9),因為沒有9這數字。SMALL可以造成一個排序的結果。由小排到大。我們在數字的地方可以ROW()函數來替換讓他自動以1,2,3,4,...增加。


我們在E欄E2輸入

{=
SMALL(IF(MATCH($A$2:$A$21,namels,0)=ROW()-1,ROW()-1,""),ROW()-1)}

再複製E2的資料,從E2到E21。

注意,最外面的大括號{}不是用輸入的,而是在整個公式打完之後按Ctrl+Shift+Enter
生的。這就是陣列公式。
除了外面包個SMALL之外,不一樣的還有這個
MATCH($A$2:$A$21,namels,0)
,原本的只有
MATCH(A2,namels,0)
這樣而已。

本來指定單一儲存格A2,用了陣列公式就要指定一個範圍,讓陣列公式知道去跑怎樣的迴圈,從A2到A21。注意,這個範圍可是不能用OFFSET定義的哦!我自己在這邊耗了很久的時間,因為我想,這個指定的範圍A2~A21不就正是namels一樣的嘛?所以我就寫成 MATCH(namels,namels,0) ,結果當然是跑不出來,這是錯誤的!
第二個重點是,陣列公式的維度要和你指定的範圍(A2~A21)一樣,也就是20列!出了20列以外的欄位,不會顯示正確的結果哦!


此時,一定感到很奇怪,出現的是上圖這個樣子。在這地方我也搞了很久,百思不得其解。明明每一列都是一樣的,而且都有陣列公式的{}大括號啊!怎麼不行?
後來我知道了,用滑鼠去把這個區域框選起來再按Ctrl+Shift+Enter就可以啦!就變成E欄那樣的結果啦!
最後一步就是加入INDEX這函數。

INDEX(陣列,列,欄)
顯示陣列中第幾欄地幾列的值

在這裡,陣列當然是namels囉,列的話就是我們那一長串的SMALL包出來的值。欄可以省略。在K欄K2~K21輸入

=INDEX(namels,SMALL(IF(MATCH($A$2:$A$21,namels,0)=ROW()-1,ROW()-1,""),ROW()-1))

記得這也是陣列公式哦!


下載範例檔:remove repeating data.xls



補充一下。會用絕對位置$A$2,是因為要將這個欄位的資料複製從第2列到第21列,不要讓複製的時候,位置的指定跟著變化,所以要用絕對位置。

陣列公式的奧義,可以參考一下[EXCEL]淺談陣列公式-高低標的計算一文。

3 則留言:

  1. 您的方法相當簡易,但請問一下,只能針對一欄資料嗎??有無方法可針對一整頁面的檔案挑重複資料??

    回覆刪除
  2. 看來沒辦法用比較簡單的方式針對一整頁的資料來篩選咧。
    用巨集才能做到。

    回覆刪除
  3. B2儲存格:
    {=INDEX(namels,MATCH(0,COUNTIF($B$1:B1,namels),0))}

    回覆刪除