前往小程序,Get更优阅读体验!
立即前往
首页
学习
活动
专区
工具
TVP
发布
社区首页 >专栏 >职场表格易错点解析:数据格式不规范怎么办?

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

作者头像
博文视点Broadview
发布2022-05-27 21:11:38
2.3K0
发布2022-05-27 21:11:38
举报

👆点击“博文视点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,快快扫码抢购吧!)

代码语言:javascript
复制
如果喜欢本文欢迎 在看丨留言丨分享至朋友圈 三连
 热文推荐  
万物皆可NFT,元宇宙中的NFT到底是什么?
以前没得选,现在,我想做CTO!
烫烫屯屯锟斤拷��年薪80W,在大厂呆了10年的我,被裁得心服口服
▼点击阅读原文,了解本书详情~
本文参与 腾讯云自媒体同步曝光计划,分享自微信公众号。
原始发表:2022-05-26,如有侵权请联系 cloudcommunity@tencent.com 删除

本文分享自 博文视点Broadview 微信公众号,前往查看

如有侵权,请联系 cloudcommunity@tencent.com 删除。

本文参与 腾讯云自媒体同步曝光计划  ,欢迎热爱写作的你一起参与!

评论
登录后参与评论
0 条评论
热度
最新
推荐阅读
相关产品与服务
腾讯云 BI
腾讯云 BI(Business Intelligence,BI)提供从数据源接入、数据建模到数据可视化分析全流程的BI能力,帮助经营者快速获取决策数据依据。系统采用敏捷自助式设计,使用者仅需通过简单拖拽即可完成原本复杂的报表开发过程,并支持报表的分享、推送等企业协作场景。
领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档