职场表格易错点解析：数据格式不规范怎么办？

👆点击“博文视点Broadview”，获取更多书讯

数据格式不规范的可能性有多种多样，但高频发生的错误主要有日期格式和数字格式错误，或者是单元格中存在多余空格，导致无法精确统计和计算（见图1）。

图1

不管是财务人员还是领导，看到如图1所示的表格时一定处在崩溃的边缘。想要计算总额，输入函数后一看，怎么数据对不上？想直接定位查看某位员工的详细数据，结果 Excel 提示“查无此人”（见图2）。

图2

领导看了直摇头叹息：好好的一个 Excel 表格，【运算】和【查找】功能统统用不上，那这份表格的意义是什么？

先别慌，尽管前期录入时没有掌握规范，但我们还有力挽狂澜的办法。

01

查找替换法

在Excel 中，只有连接符为“/”或“-”的日期才是被认可的日期格式。

例如， “2020.10.1”等格式，尽管符合我们的填写习惯，却不符合 Excel 的日期规范， 因而无法被识别。

而在单元格中手动添加单位或者空格，都可能使单元格内容无法被 Excel正确识别。在错误发生之后，我们如何能够快速替换表格中的错误符号及删除多余空格呢？

Excel 的替换功能可以快速实现删除和内容替换。

以删除“报销金额”列的单位为例，单击【开始】 选项卡【编辑】组中的【查找和选择】命令，在弹出的下拉菜单中选择【替换】命令（见图3）。弹出【查找和替换】对话框，在【查找内容】框 中输入要查找的内容“元”，在【替换为】框中不输 入任何内容，单击【全部替换】按钮，即可清除表格中所有“元”字（见图4）。

图3

图4

如果需要将表格中日期列的“.”替换为可识别的日期连接符，则在【查找内容】 框中分别输入错误的符号“.”和“\”，【替换为】框中输入“-”或者“/”符号， 再单击【全部替换】按钮即可完成。

02

函数法

除了查找和替换，通过函数也能够快速完成数据格式的转换和内容修改，解决更多复杂的错误问题。

TEXT函数——将单元格值转换为特定格式的文本，在《从零到一学Excel》一书的 3.3 节中有讲解。 

第 1 个参数选择需要转换的单元格；第 2 个参数设置为想要转换的单元格格式。如：=TEXT(D2,0)（见图5）。

图5

VALUE函数——将代表数值的文本字符串转换成数值。

VALUE 函数只有一个参数，输入函数后，选择需要转换为“数字”格式的单元格，按【Enter】键。但是被转换的单元格内容必须为纯数字，否则将会报错，如：=VALUE(D2) （见图6）。

图6

SUBSTITUTE函数——将字符串中的部分字符串以新字符替换，即用新内 容 B 替换原字符串或单元格中的 A，本函数共包含 4 个参数（见表1）。

表1

举个例子，将单元格中的“起”替换为“周”。当第 4 个参数没有任何数值时，则替换原单元格中所有“起”字（见图7）。

图7

REPLACE函数——将字符串中的部分字符用另一个字符串替换，即用 B 替换原字符串或单元格中，从第 n 位到第 n+m-1 位的内容（见表2）。

表2

当第 2 个参数为 6、第 3 个参数为 1 时，则表示将 E11 单元格中的第 6 个 字符“起”替换为“周”。当第 3 个参数为 7 时，则表示将 E11 单元格中的第 6 个字符至第 12 个字符的内容均替换为“周”（见图8）。 

图8

掌握更多 Excel 函数之后，还可以通过嵌套不同的函数，轻松完成表格数据的整理。不过，磨刀不误砍柴工，提前规避错误才是提高效率的最优解。

03

Power Query 数据清洗

有时候，已经尽力将数据按照规范进行整理了，但不管是运算还是统计，总是无法得出正确的结果。

这可能是因为表格中存在隐藏的非打印字符，可以通过 Power Query 可以一步完成整理。

Power Query 是内置在 Excel 2016 专业增强版及以上版本中的 Power BI 组件之一，能够高效地完成很多数据清洗的步骤，以及快速实现二维表和一维表 的转换。 

单击【数据】选项卡，就可以看到【获取和转换数据】组，可以通过【来自 表格 / 区域】等相应的命令将 Excel 表格、文本，甚至是网站的数据导入 Power  Query 进行清洗整理（见图9）。

图9

加载数据进入 Power Query 后，单击【转换】选项卡中的【格式】命令，在下拉菜单中选择【修整】/【清除】命令，可以一键清除所选数据区域的空格和非打印字符（见图10）。 

图10

关于 Power Query 的界面和操作，在《从零到一学Excel》一书的后续章节会继续介绍。如果已经安装的版本中没有这个功能，建议更新版本，或者是前往微软Power BI 官网下载 Power BI Desktop 软件。

本文节选自《从零到一学Excel》一书，更多相关内容欢迎阅读本书。

▊《从零到一学Excel》

张博，邓丽 著，一周进步编辑部 编

• 从零开始学Excel的教科书
• 助力Excel菜鸟到高手秘籍
• 800万人共同学习的Excel高效操作法
• 赠视频课、超值模板、免费素材

本书旨在帮助从未系统学习过Excel 的人快速掌握Excel 的操作技巧。为了满足初学者的学习需求，本书从基础内容讲起，并逐渐延伸到函数、数据透视表、可视化图表，以及数据分析。为了使本书内容更加实用，我们对Excel的知识点进行了精心筛选，只挑选了常用或容易出错的知识点进行讲解。

除了知识点介绍，本书每章都配有视频课程，并附赠200 多套模板，以及专属的读者交流群。衷心希望本书能够成为你在Excel 软件学习上的“领路人”。

（京东满100减50，快快扫码抢购吧！）

如果喜欢本文欢迎 在看丨留言丨分享至朋友圈 三连
 热文推荐  
万物皆可NFT，元宇宙中的NFT到底是什么？
以前没得选，现在，我想做CTO！
烫烫屯屯锟斤拷��年薪80W，在大厂呆了10年的我，被裁得心服口服
▼点击阅读原文，了解本书详情~