单元格排序_“一网打尽”Excel排序过程中遇到的那些“坑”

Excel排序功能非常强大，可它对数据表的格式有着严格的要求。如果我们的表格有不规范的地方，很容易掉入Excel的“坑”中。

那么Excel排序中会遇到哪些“坑”？又该如何解决？

— 01 —

首行如何不参与排序

整理Excel表格时，经常遇到需要排序的场景。可是排序的时候，却发现第一行表头也参与了排序，使得小白雷哥一头雾水。

表头参与了排序

如何才能让第一行的表头不参与排序？

首先要搞明白为何点击排序时，标题也参与了排序？

这是由于排序的数据包含了标题。

如果数据中包含标题，我们需要告诉Excel，标题是否参与排序。

点击【数据】——【排序】，勾选【数据包含标题】。在排序时，数据自动排除第一行。同理，如果数据中不包含标题，请告知聪明的Excel大大，取消勾选【数据包含标题】。

例，当勾选【数据包含标题】时，对D列进行升序排列(注：汉字排序规则是按拼音进行的)。结果如下：

当不勾选【数据包含标题】时，对D列进行升序排列。结果如下：

通过例子发现：在排序前，请告知Excel你的表格是否含有标题。

— 02 —

排序时发现有空行

在工作中，由于小白雷哥整理数据时疏忽，发现有一些空行。可是在排序时，发现这些空行把表的完整性破坏了。

如下图，如果按D列进行升序排列，数据只是在第一行到第七行进行排序。后面的数据无法参与排序。如果想要进行排序，必须立马快速找出所有的空行并删除，这样才可以把数据都参与排序。

如何快速找出所有的空行?

方法：按【Ctrl】+【G】进行快速定位。调出快速定位的窗口后，点击定位条件，选择【空值】，然后点击【确定】。

可以发现所有的空行都已经被快速选中，然后右击鼠标，删去空行即可。

— 03 —

合并单元格排序

合并单元格如何排序?

如图，需要对每个地区的产品单价进行排序。当我们鼠标单击C列的单元格，然后进行排序时，会有错误提示：若要执行此操作，所有合并单元格大小相同。遇到这种情况如何进行排序呢？

思路：首先分析下“若要执行此操作，所有合并单元格需大小相同”，这句话表达的意思是说“北京”“成都”“大连”等是由三个单元格合并而来的，而其他的并不是由合并单元格来的。所以出现了单元格大小不同的情况。

因此排序时，只能对A列以外的数据进行排序。

为了不出现北京区域的数据跑到其他区域，需要把表数据分为三个块：北京，成都和大连。

这种排序也被称为“组内排序”

因此

若增加一个辅助列，每一个区域的数字大小是一个数量级，比如北京的辅助列数字大小为10000+，成都的辅助列数字大小为20000+，大连的辅助列数字大小为30000+。

那么

无论如何排序，每一个区域的产品都是连在一起的。

这样就保证了在合并单元格的情况下进行组内排序。

需要借助辅助列和函数COUNTA函数。

COUNTA函数是计算区域中非空单元格的个数。如图在辅助列输入公式=COUNTA($A$2:A2)*10∧4+D2，并向下复制填充。

这样公式在向下复制填充的过程，COUNTA($A$2:A2)引用的单元格区域逐渐扩大，每跨过一个合并单元格，结果就会增加1，因此整个公式就构造出了一组不同数量级的数值。

最后，选择数据区域(框选B-F列的数据)，进行排序即可顺利实现组内排序。排序结束后，删去辅助列的数据即可。

含有合并单元格的数据，无法直接进行排序。需要借助辅助列的数据进行排序。

— 04 —

不听话的文本、数字混合排序

Excel对数值的排序依据是数值的大小、对文本的排序依据是文本首字母，但是对文本与数字组合形式，排序的规则却比较复杂。

如下图A列编码是由字母和数字组合而成，现在我们对A列进行升序排序，发现排序后的结果并没有按照我们想象的「先按字母升序，然后按照数字大小升序」。

可以看到，顺序仍然是乱的。

错误的文本排序

而我们想要的排序结果是这样的

正确的文本排序

上面的排序没能实现预期是因为：字母和数字组合之后，他们就变成了文本，那么排序的规则是：一个字符一个字符进行排序。

因此直接对A列进行排序的过程是这样的：

先对第一个字符(也就是字母进行排序)

↓

再对第二个字符进行排序

↓

第二个字符显然的结果是

A7>A16

↓

因此出现“错误”的排序

↓

然后对第三个、第四个字符进行排序……

因此如果数字的位数不一样，排序就会出错。

我们可以通过构造0占位符，使数字的位数一致。

如图所示在C2单元格中写入公式=LEFT(A2,1)&TEXT(RIGHT(A2,LEN(A2)-1),"000")，构建辅助列。

简单解释这个公式：

LEFT(A2,1)：是提取原编码中左端的字母；

RIGHT(A2,LEN(A2)-1)：是提取原编码中的数字；TEXT(RIGHT(A2,LEN(A2)-1),"000")：是提取出来的数字变为三位数的显示形式，不足的位数用0补齐。

然后对C列进行升序排序，这样就达到了我们想要的效果。

你学会了么？

相关文章阅读

1、从0到1：混职场，必须掌握的十二个函数

2、一学就会，高效实用的9个Excel行列技巧

3、震惊！Excel也会骗人，数值精度及位数取舍函数全解析

4、神奇的Excel快速填充功能，到底有多牛逼？

--中将名单公布--

上期中奖名单为

九州舜天-熊伟17607174433

获奖的小伙伴请在12月5日之前

私信后台回复  邮寄地址+收件人+联系方式 

没中奖的小伙伴也不要气馁

下周还会给大家进行送书活动~

—— 往期精选 ——

① 热文 | 推荐20款大数据可视化工具—无论是编程老司机or小白，总有一款适合你！

② 热文 | 最全的免费正版图片网址合集

③ 图表 | Excel季度报表这样做，高端大气！老板：第一次见这么洋气的饼图

④ 图表 | 困扰多年的Excel仪表盘，原来是这么做的！貌似并不难嘛