文本格式的数值排序一直是个大麻烦,11会先于2。因为文字的排序规则是把每个字符依次拿出来比较大小,第一个1小于2,所以11先来。
当然有解决的办法。比如之前分享的Excel-century谜题“按月排序”终于解决了。
今天我就一次性多告诉你几个方法。
案例:
把下面的图1做成数据透视表,汇总各楼层的材料总量,各楼层必须按顺序排序。
效果如下图2所示。
解决方案:
先来看看吧。不设置任何东西,就做透视表的结果。
1.选择数据表中的任意单元格-选择菜单栏中的“插入”-“透视表”。
2.在弹出的对话框中,选择“现有工作表”和上传到的位置——点击“确定”
3.在右侧的数据透视表字段区域中,将“地板”和“材料”拖到行区域,将“数量”拖到值区域。
接下来说正题:怎么解决?如果你不介意改变原数据表的内容,可以考虑把A列的数字提取出来,变成数值,方便排序。
4.输入D2单元格中A2单元格的第7层。
5.选择D2:D30区域-按Ctrl E。
6.将D列中的数据剪切并粘贴到a列对应的区域。
现在数据透视表按月排序。
如果想用公式提取月份值,也可以。
7.将数据表复制并粘贴到空白区域,并删除楼层数据。
8.在单元格E2-下拉复制公式中输入以下公式:
=LEFT(A2,LEN(A2)-1)
公式解释:
left函数从文本字符串的第一个字符开始返回指定数量的字符;A2是要查找的文本字符串;LEN(A2)-1:返回的长度是字符串的总长度-1,即减去最后一个字符“Lou”。
现在用这个新的区域做数据透表,但是你会惊讶地发现数字还是以文本的方式排序的。
这是因为 left 函数提取出来的结果仍然是文本,还要将其转换成数值才行。
9. 在原公式的基础上,增加“*1”,修改后的公式如下:
=LEFT(A2,LEN(A2)-1)*1
*1 就能将文本数字变成数值格式。
现在再做数据透视表,就能按楼层排序了。
如果不能改动原数据表,那个“楼”字不可省略,那么可以这样做。
10. 将楼层列复制到空白区域 --> 选中该列,选择菜单栏的“数据”-->“删除重复值”
11. 在弹出的对话框中点击“确定”
12. 点击“确定”
13. 将该列升序排序。
14. 排序不尽人意的地方,手工调整。
15. 选择菜单栏的“文件”-->“选项”
16. 在弹出的对话框中选择“高级”--> 在“常规”区域中点击“编辑自定义列表”
17. 选中楼层列表 --> 点击“导入”按钮
18. 点击“确定”
19. 点击“确定”
再一次制作数据透视表。看到没?带中文也按能楼层排序。
20. 选中数据透视表的任意单元格 --> 选择菜单栏的“设计”-->“报表布局”-->“以表格形式显示”
21. 选择“设计”-->“分类汇总”-->“不显示分类汇总”
完美。
很多同学会觉得 Excel 单个案例讲解有些碎片化,初学者未必能完全理解和掌握。不少同学都希望有一套完整的图文教学,从最基础的概念开始,一步步由简入繁、从入门到精通,系统化地讲解 Excel 的各个知识点。
现在终于有了,以下专栏,从最基础的操作和概念讲起,用生动、有趣的案例带大家逐一掌握 Excel 的操作技巧、快捷键大全、函数公式、数据透视表、图表、打印技巧等……学完全本,你也能成为 Excel 高手。