文字加数字组合的排序技巧

 

Excel基础学习园地

公众号“Excel基础学习园地”是一个免费发布Excel基础知识、函数应用、操作技巧、学习方法等资讯的公众号，请点击上方“Excel基础学习园地”添加关注，方便我们每天向您推送精彩资讯。

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列进行升序排序，这样就达到了我们想要的效果。