本文是專門為新書打造的「拓展閱讀系列」之一，我們希望做到實(shí)體書與線上學(xué)習(xí)的有效結(jié)合，通過二維碼打通實(shí)體書與線上資源，構(gòu)建完善的Excel知識體系。
Excel排序功能非常強(qiáng)大，可它對數(shù)據(jù)表的格式有著嚴(yán)格的要求。如果我們的表格有不規(guī)范的地方，很容易掉入Excel的“坑”中。
那么Excel排序中會遇到哪些“坑”？又該如何解決？
— 01 —
首行如何不參與排序
整理Excel表格時，經(jīng)常遇到需要排序的場景?？墒桥判虻臅r候，卻發(fā)現(xiàn)第一行表頭也參與了排序，使得小白雷哥一頭霧水。
表頭參與了排序
如何才能讓第一行的表頭不參與排序？
首先要搞明白為何點(diǎn)擊排序時，標(biāo)題也參與了排序？
這是由于排序的數(shù)據(jù)包含了標(biāo)題。
如果數(shù)據(jù)中包含標(biāo)題，我們需要告訴Excel，標(biāo)題是否參與排序。
點(diǎn)擊【數(shù)據(jù)】——【排序】，勾選【數(shù)據(jù)包含標(biāo)題】。在排序時，數(shù)據(jù)自動排除第一行。同理，如果數(shù)據(jù)中不包含標(biāo)題，請告知聰明的Excel大大，取消勾選【數(shù)據(jù)包含標(biāo)題】。
例，當(dāng)勾選【數(shù)據(jù)包含標(biāo)題】時，對D列進(jìn)行升序排列（注：漢字排序規(guī)則是按拼音進(jìn)行的）。結(jié)果如下：
當(dāng)不勾選【數(shù)據(jù)包含標(biāo)題】時，對D列進(jìn)行升序排列。結(jié)果如下：
通過例子發(fā)現(xiàn)：在排序前，請告知Excel你的表格是否含有標(biāo)題。
— 02 —
排序時發(fā)現(xiàn)有空行
在工作中，由于小白雷哥整理數(shù)據(jù)時疏忽，發(fā)現(xiàn)有一些空行。可是在排序時，發(fā)現(xiàn)這些空行把表的完整性破壞了。
如下圖，如果按D列進(jìn)行升序排列，數(shù)據(jù)只是在第一行到第七行進(jìn)行排序。后面的數(shù)據(jù)無法參與排序。如果想要進(jìn)行排序，必須立馬快速找出所有的空行并刪除，這樣才可以把數(shù)據(jù)都參與排序。
如何快速找出所有的空行?
方法：按【Ctrl】+【G】進(jìn)行快速定位。調(diào)出快速定位的窗口后，點(diǎn)擊定位條件，選擇【空值】，然后點(diǎn)擊【確定】。
可以發(fā)現(xiàn)所有的空行都已經(jīng)被快速選中，然后右擊鼠標(biāo)，刪去空行即可。
— 03 —
合并單元格排序
合并單元格如何排序?
如圖，需要對每個地區(qū)的產(chǎn)品單價進(jìn)行排序。當(dāng)我們鼠標(biāo)單擊C列的單元格，然后進(jìn)行排序時，會有錯誤提示：若要執(zhí)行此操作，所有合并單元格大小相同。遇到這種情況如何進(jìn)行排序呢？
思路：首先分析下“若要執(zhí)行此操作，所有合并單元格需大小相同”，這句話表達(dá)的意思是說“北京”“成都”“大連”等是由三個單元格合并而來的，而其他的并不是由合并單元格來的。所以出現(xiàn)了單元格大小不同的情況。
因此排序時，只能對A列以外的數(shù)據(jù)進(jìn)行排序。
為了不出現(xiàn)北京區(qū)域的數(shù)據(jù)跑到其他區(qū)域，需要把表數(shù)據(jù)分為三個塊：北京，成都和大連。
這種排序也被稱為“組內(nèi)排序”
因此
若增加一個輔助列，每一個區(qū)域的數(shù)字大小是一個數(shù)量級，比如北京的輔助列數(shù)字大小為10000+，成都的輔助列數(shù)字大小為20000+，大連的輔助列數(shù)字大小為30000+。
那么
無論如何排序，每一個區(qū)域的產(chǎn)品都是連在一起的。
這樣就保證了在合并單元格的情況下進(jìn)行組內(nèi)排序。
需要借助輔助列和函數(shù)COUNTA函數(shù)。
COUNTA函數(shù)是計算區(qū)域中非空單元格的個數(shù)。如圖在輔助列輸入公式=COUNTA（$A$2:A2）*10∧4+D2，并向下復(fù)制填充。
這樣公式在向下復(fù)制填充的過程，COUNTA（$A$2:A2）引用的單元格區(qū)域逐漸擴(kuò)大，每跨過一個合并單元格，結(jié)果就會增加1，因此整個公式就構(gòu)造出了一組不同數(shù)量級的數(shù)值。
最后，選擇數(shù)據(jù)區(qū)域（框選B-F列的數(shù)據(jù)），進(jìn)行排序即可順利實(shí)現(xiàn)組內(nèi)排序。排序結(jié)束后，刪去輔助列的數(shù)據(jù)即可。
含有合并單元格的數(shù)據(jù)，無法直接進(jìn)行排序。需要借助輔助列的數(shù)據(jù)進(jìn)行排序。
— 04 —
不聽話的文本、數(shù)字混合排序
Excel對數(shù)值的排序依據(jù)是數(shù)值的大小、對文本的排序依據(jù)是文本首字母，但是對文本與數(shù)字組合形式，排序的規(guī)則卻比較復(fù)雜。
如下圖A列編碼是由字母和數(shù)字組合而成，現(xiàn)在我們對A列進(jìn)行升序排序，發(fā)現(xiàn)排序后的結(jié)果并沒有按照我們想象的「先按字母升序，然后按照數(shù)字大小升序」。
可以看到，順序仍然是亂的。
錯誤的文本排序
而我們想要的排序結(jié)果是這樣的
正確的文本排序
上面的排序沒能實(shí)現(xiàn)預(yù)期是因?yàn)椋鹤帜负蛿?shù)字組合之后，他們就變成了文本，那么排序的規(guī)則是：一個字符一個字符進(jìn)行排序。
因此直接對A列進(jìn)行排序的過程是這樣的：
先對第一個字符（也就是字母進(jìn)行排序）
↓
再對第二個字符進(jìn)行排序
↓
第二個字符顯然的結(jié)果是
A7>A16
↓
因此出現(xiàn)“錯誤”的排序
↓
然后對第三個、第四個字符進(jìn)行排序……
因此如果數(shù)字的位數(shù)不一樣，排序就會出錯。
我們可以通過構(gòu)造0占位符，使數(shù)字的位數(shù)一致。
如圖所示在C2單元格中寫入公式=LEFT(A2,1)&TEXT(RIGHT(A2,LEN(A2)-1),'000')，構(gòu)建輔助列。
簡單解釋這個公式：
LEFT(A2,1)：是提取原編碼中左端的字母；
RIGHT(A2,LEN(A2)-1)：是提取原編碼中的數(shù)字；TEXT(RIGHT(A2,LEN(A2)-1),'000')：是提取出來的數(shù)字變?yōu)槿粩?shù)的顯示形式，不足的位數(shù)用0補(bǔ)齊。
然后對C列進(jìn)行升序排序，這樣就達(dá)到了我們想要的效果。
你學(xué)會了么？
相關(guān)文章閱讀
2、一學(xué)就會，高效實(shí)用的9個Excel行列技巧
3、震驚！Excel也會騙人，數(shù)值精度及位數(shù)取舍函數(shù)全解析
·END·
安偉星
Excel發(fā)燒友
Office認(rèn)證大師
簡書推薦作者
創(chuàng)作讓你感受到呼吸心跳的Excel體驗(yàn)
讓你茅塞頓開的思路與方法論！
精進(jìn)Excel
