Excel 中的文本数字,必须按数值的规则排序?是不是有点强人所难

文本格式的数字排序 , 向来是个大麻烦 , 11会排在2前面 。 因为文本的排序规则就是依次把每一个字符拿出来比大小 , 第一位的1比2小 , 所以11就排在前面了 。
当然是有解的 , 比如之前分享过的Excel–世纪难题“按月份排序” , 终于解决了 。
今天我要一次性给大家再讲几种方法 。
案例:
将下图1制作成数据透视表 , 汇总每层楼的各项物资总数 , 楼层必须顺序排序 。
效果如下图2所示 。
Excel 中的文本数字,必须按数值的规则排序?是不是有点强人所难
文章图片
Excel 中的文本数字,必须按数值的规则排序?是不是有点强人所难
文章图片
解决方案:
先来看一下 , 如果什么都不设置 , 直接做数据透视表的结果 。
1.选中数据表的任意单元格-->选择菜单栏的“插入”-->“数据透视表”
Excel 中的文本数字,必须按数值的规则排序?是不是有点强人所难
文章图片
2.在弹出的对话框中选择“现有工作表”及所需上传至的位置-->点击“确定”
Excel 中的文本数字,必须按数值的规则排序?是不是有点强人所难
文章图片
3.在右侧的数据透视表字段区域 , 将“楼层”和“物资”拖到行区域 , “数量”拖到值区域 。
Excel 中的文本数字,必须按数值的规则排序?是不是有点强人所难
文章图片
Excel 中的文本数字,必须按数值的规则排序?是不是有点强人所难
文章图片
接下来进入正题:怎么解决?如果不介意更改原始数据表的内容的话 , 可以考虑将A列中的数字提取出来 , 变成数值 , 这样就容易排序了 。
4.在D2单元格中输入A2单元格的楼层7
Excel 中的文本数字,必须按数值的规则排序?是不是有点强人所难
文章图片
5.选中D2:D30区域-->按Ctrl+E
Excel 中的文本数字,必须按数值的规则排序?是不是有点强人所难
文章图片
Excel 中的文本数字,必须按数值的规则排序?是不是有点强人所难
文章图片
6.将D列的数据剪切粘贴到A列对应的区域 。
Excel 中的文本数字,必须按数值的规则排序?是不是有点强人所难
文章图片
现在再做出的数据透视表就是按月份顺序排序的 。
Excel 中的文本数字,必须按数值的规则排序?是不是有点强人所难
文章图片
如果想用公式提取月份数值也可以 。
7.将数据表复制粘贴到空白区域 , 删除楼层数据 。
Excel 中的文本数字,必须按数值的规则排序?是不是有点强人所难
文章图片
8.在E2单元格中输入以下公式-->下拉复制公式:
=LEFT(A2,LEN(A2)-1)
公式释义:
left函数的作用是从文本字符串的第一个字符开始返回指定个数的字符;
A2是要查找的文本字符串;
LEN(A2)-1:返回的长度是字符串的总长度-1 , 即扣除最后一个字符“楼”
Excel 中的文本数字,必须按数值的规则排序?是不是有点强人所难
文章图片
Excel 中的文本数字,必须按数值的规则排序?是不是有点强人所难
文章图片
现在用这个新的区域做数据透表 , 但是你会惊讶地发现数字还是以文本的方式排序的 。
这是因为left函数提取出来的结果仍然是文本 , 还要将其转换成数值才行 。
Excel 中的文本数字,必须按数值的规则排序?是不是有点强人所难
文章图片
9.在原公式的基础上 , 增加“*1” , 修改后的公式如下:
=LEFT(A2,LEN(A2)-1)*1
*1就能将文本数字变成数值格式 。
Excel 中的文本数字,必须按数值的规则排序?是不是有点强人所难
文章图片
Excel 中的文本数字,必须按数值的规则排序?是不是有点强人所难