就是．梅酒

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

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


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

下載範例檔：





A、設定格式化條件

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


陣列範圍是 A:A ，條件是 A2 ，表示在整個A欄中符合A2的有幾個。在這裡我想要知道哪些姓名的個數有「大於1」的，那就是有重複的姓名囉！因此，
就填在A2欄位中的 格式 > 設定格式化的條件。對話框中選擇 公式為 將公式打上去，在選擇 格式，當條件符合的時候，就套用該格式。如此，就可以很直接地看出來哪幾個姓名是重複的了。


B、進階篩選

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

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


C、陣列公式

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

我先在 下拉式選單的 插入 > 名稱 > 定義 ，去把我A欄中的姓名範圍用OFFSET定義出來，叫namels。
好處是，有這名稱namels(name list)之後，我在公式裡面要打這個範圍時只要寫namels，Excel就會代入OFFSET這個範圍了，才不會讓公式看起來很亂要Debug不容易。

一步一步來，先不用陣列公式。
在D2輸入
看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這函數囉！在這裡我先不打算把姓名的結果顯示出來，而是顯示數字，即，沒有重複姓名的列數。


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


我們在E欄E2輸入

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

注意，最外面的大括號{}不是用輸入的，而是在整個公式打完之後按Ctrl+Shift+Enter產
生的。這就是陣列公式。
除了外面包個SMALL之外，不一樣的還有這個
，原本的只有
這樣而已。

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


此時，一定感到很奇怪，出現的是上圖這個樣子。在這地方我也搞了很久，百思不得其解。明明每一列都是一樣的，而且都有陣列公式的{}大括號啊！怎麼不行？
後來我知道了，用滑鼠去把這個區域框選起來再按Ctrl+Shift+Enter就可以啦！就變成E欄那樣的結果啦！
最後一步就是加入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]淺談陣列公式-高低標的計算一文。