让你效率迅速提升 5 倍的 10个Excel 数据清洗技巧

这是小马哥的第35篇原创文章

根据 Forbes 一次针对数据科学家的调查显示,收集、整理和清洗数据占据了数据科学家 80%的工作时间。

图引用自 Forbes 文章

而 76%的数据科学家认为收集、整理和清洗数据成就感最低。

图引用自 Forbes 文章

别说数据科学家了,运营、市场的同学在面对 Excel 中的大量数据,通宵达旦做分析时也会常常抓耳挠腮、痛不欲生。因为调研问卷或是用户反馈的数据千奇百怪,与脑海中的预期相差十万八千里。

往往上半夜都过完了,数据还没清洗完,更别提要分析了

掌握下面的10个技巧,效率迅速提升 5 倍

第一部分:养成好习惯

#1 不在原始数据上改动

不论数据有多脏,别在原始(元)数据上直接改动。一旦在元数据上直接清洗,即便清洗出现问题,也有可能无法察觉;如果元数据有增加,可能很多清洗动作又得重做一遍。

可以新建一个工作表(Sheet / Tab)用于存储清洗后的数据。

#2 为工作表命名

通常一个 Excel 中会包含多个工作表:原始数据表、中间表、分析结果表等。

为这些工作表命名可以帮助我们回忆表单内容。下个月老板再问起来,打开 Excel 一看工作表名称就能回忆起。

#3 给工作表设置颜色和排序

如果工作表过多,可以通过设置颜色和排序的方式更好的管理。

我的习惯是最左侧的工作表展示分析结果,向右依次为中间表、元数据。如果工作表超过4个,通常会按照结果数据、过程数据、原始数据的分类分别设置不同的颜色,方便区分查找。

第二部分:效率提升 5 倍的技巧

#4 干掉多余的空格

原始数据中如果夹杂着大量的空格,可能会在我们筛选数据或统计时带来一定麻烦。如何去掉多余的空格,仅在字符间保留一个空格?有三种办法:

一个个手动删除。如果只有三五个空格,这可能是最快的方式。

查找替换。可选中待清洗的数据,在查找中输入2个空格,在替换中输入1个空格,那么所有2个空格都会被替换成1个空格。这种方式需要多次点击替换,直至提示找不到需要查找的数据为止。

使用 Trim 函数。Trim 函数可以帮我们移除文本中的所有多余空格,仅保留单词之间的单个空格。

基于第一条习惯#1 不在原始数据上改动的理念,为了避免误操作导致原始数据被改的面目全非,不推荐使用上述①②两种方法去空格。

#5 批量替换多个字符

元数据中可能会有不少字符错误,如半角句号「.」被输入成了全角句号「。」,或是在收集用户邮箱时遇到把 @ 输入成 # 的情况,如何批量替换呢?

查找替换可以完成,但需要直接修改元数据。为了保证元数据不被修改,可使用 Substitute 函数完成批量替换。

下面的例子中我们把用户输入邮箱中的 # 批量替换成正确的 @

#6 挽救变身文字格式的数字们

有些单元格里明明记录了数字,却提示错误:把数字记录成了文字格式(Number Stored as Text)。这种情况下直接修改单元格格式有时会没有效果,很有可能需要一个个点击左上角错误提示按钮,逐个修正错误到手软。

可使用 Value 函数批量调整。

#7 批量改大小写或首字母大写

使用 Upper / Lower / Proper 函数可批量修改将字符修改为全部大写、全部小写或首字母大写。这在整理数据或修改英文名称时格外有用。

#8 多列数据合并成一列

这可能是一个超级常用的操作。比如在 Excel 中一列记录姓,一列记录名字,这时我想有一列显示姓名,该如何操作呢?使用 Concatenate 函数可以合并多列数据。

#9 对文本进行分列操作

有合必有分,有时从数据库中导出的数据会以 csv 或是 txt 的格式存储,并以逗号将各列进行分隔。这种情况下可使用分列操作将各列数据分开。

#10 不规则的数据如何分列

文本分列操作的确十分好用,但有时我们需要取一列的前 3 位或是中间 8 位,比如取电话号码的前3位,身份证号码中间记录年份的 4 位,又该如何操作呢?

可以使用 Right / Left / Mid 函数非常简单高效的完成

终极技能

# 记得存盘

经常点击Control + S 或 Command + S ( MAC 电脑 ) 保存,在绝大多数时间,这个好习惯能帮助你节约几十分钟,并保持好心情。

图引用自 MemeCenter

你遇到过哪些棘手的数据清洗问题?

如何解决的或是还未解决?

欢迎留言——

参考文章:

Cleaning Big Data: Most Time Consuming,Least Enjoyable Data Science Task, Survey Sayshttps://www.forbes.com/sites/gilpress/2016/03/23/data-preparation-most-time-consuming-least-enjoyable-data-science-task-survey-says/#50210fe26f63

Data Analysis and Presentation Skills: the PwC Approachhttps://www.coursera.org/specializations/pwc-analytics

Top ten ways to clean your datahttps://support.office.com/en-us/article/top-ten-ways-to-clean-your-data-2844b620-677c-47a7-ac3e-c2e157d1db19

引用和工具:

我是小马哥,欢迎关注

  • 发表于:
  • 原文链接:https://kuaibao.qq.com/s/20180815G0W6V000?refer=cp_1026
  • 腾讯「云+社区」是腾讯内容开放平台帐号(企鹅号)传播渠道之一,根据《腾讯内容开放平台服务协议》转载发布内容。

扫码关注云+社区

领取腾讯云代金券