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

小马哥的牛棚

发布于 2020-08-20 02:35:05

1.5K0

文章被收录于专栏：小马哥的牛棚小马哥的牛棚

根据 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 Says https://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 Approach https://www.coursera.org/specializations/pwc-analytics
Top ten ways to clean your data https://support.office.com/en-us/article/top-ten-ways-to-clean-your-data-2844b620-677c-47a7-ac3e-c2e157d1db19

引用和工具：