為什麼我已經用了 UNIQUE，名單裡卻還是看到好幾個 M014？又為什麼 SORT 明明寫了 -1，排出來的還是由小到大？
SORT、UNIQUE 的基本寫法在 SORT 與 UNIQUE 入門 講過了，會員排行這類組合用法在 SORT 與 UNIQUE 實戰。這篇只講會讓結果怪怪的地方，一節一個。
名單裡還是好幾個 M014
會員名單有人手打、有人從別的系統貼過來，A2:A7 長這樣，第三欄是用 LEN 算出的字數：
| 列 | 內容 | 字數 |
|---|---|---|
| 2 | M014 | 4 |
| 3 | m014 | 4 |
| 4 | M014（結尾多一個空白） | 5 |
| 5 | M021 | 4 |
| 6 | M021（開頭多一個空白） | 5 |
| 7 | M021 | 4 |
直接丟給 UNIQUE：
直接去重
=UNIQUE(A2:A7)會得到 5 列，不是 4 列。旁邊的字數是為了讓你看出差別，另外用 LEN 加上去的：
| 內容 | 字數 |
|---|---|
| M014 | 4 |
| m014 | 4 |
| M014（結尾多一個空白） | 5 |
| M021 | 4 |
| M021（開頭多一個空白） | 5 |
多一個空白的 M014，對 UNIQUE 來說就是另一個值，只是眼睛看不出來，才會覺得「怎麼去不掉」。要看出來的話，旁邊放一欄 LEN 比對字數最快。
第 3 列的小寫 m014 也沒有被併掉。UNIQUE 比對文字時是分大小寫的，M014 和 m014 算兩個不同的值，兩個都留下來。
先處理空白，用 TRIM 把頭尾空白拿掉，再去重。TRIM 要處理整塊範圍，外面得包 ARRAYFORMULA：
先去除頭尾空白，再去重
=UNIQUE(ARRAYFORMULA(TRIM(A2:A7)))來看看結果：
| 內容 |
|---|
| M014 |
| m014 |
| M021 |
空白處理掉了，可是小寫的 m014 還在。會員編號大小寫不同，多半本來就該算同一位，這時要在去重之前先統一大小寫，用 UPPER（或 LOWER）：
去頭尾空白、統一成大寫，再去重
=UNIQUE(ARRAYFORMULA(UPPER(TRIM(A2:A7))))這次只剩 M014、M021 兩列。不包 ARRAYFORMULA 的話，UPPER(TRIM(...)) 會得到 #VALUE!。我自己的習慣是，從別的系統貼進來的名單，進 UNIQUE 之前都先過一次 TRIM，編號類的再加 UPPER。文字清洗的其他函式在 文字清理函式 有介紹。
TRIM 拿不掉全形空格
中文資料常有全形空格。假設 A2:A4 是三筆 M033，其中第 3 列結尾是全形空格，LEN 是 5：
| 列 | 內容 | 字數 |
|---|---|---|
| 2 | M033 | 4 |
| 3 | M033（結尾多一個全形空格） | 5 |
| 4 | M033 | 4 |
TRIM 只處理半形空格，全形空格它不會動。所以上一節的寫法在這邊仍然是 2 列：M033 和結尾多一個全形空格的 M033。要先用 SUBSTITUTE 把全形空格換成半形，再交給 TRIM：
全形空格換成半形空格，再去頭尾空白，再去重
=UNIQUE(ARRAYFORMULA(TRIM(SUBSTITUTE(A2:A4, " ", " "))))SUBSTITUTE 的第二個參數是一個全形空格，打的時候用中文輸入法的空白鍵。這次只剩 1 列：M033。
SORT 寫 -1 還是遞增
SORT 只認 TRUE 和 FALSE，-1 會被當成 TRUE，入門篇講過了。這邊補上其他數字的情況，用入門篇那張訂單，A2:C11，杯數在第 3 欄：
| 寫法 | 杯數欄的結果 |
|---|---|
SORT(A2:C11, 3, TRUE) | 5、8、9、12、12、17、21、29、37、37 |
SORT(A2:C11, 3, -1) | 5、8、9、12、12、17、21、29、37、37 |
SORT(A2:C11, 3, 1) | 5、8、9、12、12、17、21、29、37、37 |
SORT(A2:C11, 3, 0) | 37、37、29、21、17、12、12、9、8、5 |
規則是只有 0 算 FALSE，其他數字都是 TRUE，所以 -1 是遞增，0 才是遞減。不會報錯，只是順序不對，資料本來就亂的話很難第一眼看出來。我習慣一律寫 TRUE 和 FALSE，看公式的人也不用猜。Excel 的 SORT 則是用 1（遞增）和 -1（遞減），兩邊寫法不同，公式搬家時這個參數要改。
中文排序依筆畫，想要固定順序
中文照筆畫由少到多排，原因入門篇講過了。拿五間門市看看，A2:B6：
| 列 | 門市 | 杯數 |
|---|---|---|
| 2 | 信義 | 12 |
| 3 | 中和 | 33 |
| 4 | 板橋 | 21 |
| 5 | 新店 | 8 |
| 6 | 永和 | 15 |
依門市名稱排序
=SORT(A2:B6, 1, TRUE)來看看結果，各門市第一個字的筆畫是「中」4、「永」5、「板」8、「信」9、「新」13：
| 門市 | 杯數 |
|---|---|
| 中和 | 33 |
| 永和 | 15 |
| 板橋 | 21 |
| 信義 | 12 |
| 新店 | 8 |
這個順序和注音、拼音都對不上。
如果報表想要固定的順序，例如店長習慣的「信義、板橋、中和、永和、新店」，我會讓 SORT 去排一欄「名次」。MATCH 找出每間門市在清單裡的位置，當作第二個參數：
依自訂的門市順序排序
=SORT(
A2:B6,
ARRAYFORMULA(MATCH(A2:A6, {"信義"; "板橋"; "中和"; "永和"; "新店"}, 0)),
TRUE
)排序欄可以是範圍外的另一欄，列數要和範圍一樣，這邊 MATCH 算出 5 個位置，剛好對上 5 列。來看看結果：
| 門市 | 杯數 |
|---|---|
| 信義 | 12 |
| 板橋 | 21 |
| 中和 | 33 |
| 永和 | 15 |
| 新店 | 8 |
要小心的是，門市清單裡漏寫的名字，MATCH 會找不到，所以新增門市時這份清單也要補。
數字、文字、TRUE 混在一起
匯出的資料有時候會夾著文字形式的數字，儲存格靠左對齊就是線索。A2:A5 是 37、文字的 8、112、21：
| 列 | 內容 |
|---|---|
| 2 | 37 |
| 3 | 8（文字） |
| 4 | 112 |
| 5 | 21 |
遞增排序
=SORT(A2:A5, 1, TRUE)結果是 21、37、112，最後才是文字的 8。數字永遠排在文字前面，文字的 8 不會跟著大小排進去。修法是先用 VALUE 把文字轉成數字：
先把文字轉成數字，再排序
=SORT(ARRAYFORMULA(VALUE(A2:A5)), 1, TRUE)來看看結果：8、21、37、112。
資料型別更多種的時候，遞增是數字在前、文字在中間、TRUE 和 FALSE 在最後，遞減整個倒過來。拿 A2:A5 是 37、「青茶」、TRUE、8 來說：
| 公式 | 結果 |
|---|---|
| 8、37、青茶、TRUE | |
| TRUE、青茶、37、8 |
SORTN 並列的時候少一筆
SORTN 排序後只留前 n 列，但前幾名有並列的時候，要不要一起留，由第三個參數「顯示並列模式」決定。
7 筆訂單的杯數放在 A2:A8：29、37、21、29、17、29、21。排好是 37、29、29、29、21、21、17，第 2 到第 4 名都是 29。取前 3 名：
| 模式 | 作法 | 結果 |
|---|---|---|
| 0 | 剛好 n 列，並列的後面直接截掉 | 37、29、29 |
| 1 | n 列之後，把和第 n 列並列的也一起留 | 37、29、29、29 |
| 2 | 先去掉重複的列，再取 n 列 | 37、29、21 |
| 3 | 取名次前 n 名，並列的全部留下 | 37、29、29、29、21、21 |
模式沒填就是 0，所以第 4 名那個 29 預設會被截掉。做排行榜的時候，名次三個人並列卻只出現其中兩個，通常就是這個原因。我自己在排行榜上會選模式 1 或 3，看想不想連第 4 名的並列也算進去。
指定排序欄時，模式 2 依排序鍵去重。若資料有品項和杯數，卻只指定杯數排序，相同杯數即使品項不同，也只留一列；指定多個排序欄時，則看整組排序鍵。需要整列完全相同才去重，先用 UNIQUE，再用 SORTN 模式 0 取前 n 列，例如三欄訂單可寫 。完整多欄例子見 SORTN 取前 N 名。
結果被擋成 #REF!
溢出被擋會變 #REF!，原因和處理方式入門篇講過了。這邊只補一個容易忽略的情況：它常常是資料變多之後才冒出來。
例如 F2 放 ，名單有 4 位，結果占 F2:F5，F6 有個隨手打的「x」，一開始沒事。後來多一位新會員，範圍變成 A2:A8，結果需要 F2:F6，撞到 F6，F2 就變成 #REF!。清掉 F6 就恢復。所以放結果的位置，下面要留足夠的空間。
範圍框太大，尾巴多一格空白
為了讓新資料自動算進去，有人會把範圍多框幾列，例如 A2:A9，實際只有 A2:A6 有資料，裡面是 M014、M021、M014、M033、M045。
範圍多框了空白列
=UNIQUE(A2:A9)結果是 5 列，最後一列是空白，因為空白儲存格也被當成一個不重複的值：
| 列 | 內容 |
|---|---|
F2 | M014 |
F3 | M021 |
F4 | M033 |
F5 | M045 |
F6 | （空白） |
這一格空白會讓後面的 COUNTIF、排行榜多出一列沒名字的資料。用 FILTER 先把空白篩掉：
先篩掉空白，再去重
=UNIQUE(FILTER(A2:A9, A2:A9<>""))來看看結果，剩下 F2:F5 四位會員。FILTER 的用法在 FILTER 入門 有介紹。
練習
找出錯在哪
我想把 A2:C11 的訂單依杯數由大到小排序，寫了 ，結果卻是由小到大。請改成正確的寫法。
遇到結果怪怪的時候，我通常先查三件事：範圍裡有沒有看不見的空白、排序欄寫的是不是 TRUE／FALSE，還有結果下面有沒有東西擋住。
我是喜特先生，Mr. Sheet，我們下個教學見！
接下來可以
相關文章
查看所有文章SORT 與 UNIQUE：自動排序、自動去除重複
用 SORT 依一欄或多欄排序，用 UNIQUE 去掉重複的列，再把兩個函式套在一起，做出一份會跟著原始資料自動更新的清單。
文字清理函式參考：TRIM、CLEAN、SUBSTITUTE、PROPER 與看不見的空格
整理 TRIM、CLEAN、SUBSTITUTE、REPLACE、UPPER、LOWER、PROPER 的語法與限制，包含 TRIM 清不掉全形空格與 CHAR(160)、CLEAN 只清 ASCII 0 到 31、SUBSTITUTE 第 4 參數只換第 N 次，以及用 UNICODE 找出看不見的字元。
留言討論
共 0 則留言