专栏首页小马哥的牛棚让你效率迅速提升 5 倍的 10 个 Excel 数据清洗技巧

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

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

图引用自 Forbes 文章

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

图引用自 Forbes 文章

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

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

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

第一部分:养成好习惯

#1 不在原始数据上改动

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

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

#2 为工作表命名

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

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

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

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

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

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

#4 干掉多余的空格

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

  1. 一个个手动删除。如果只有三五个空格,这可能是最快的方式。
  2. 查找替换。可选中待清洗的数据,在查找中输入2个空格,在替换中输入1个空格,那么所有2个空格都会被替换成1个空格。这种方式需要多次点击替换,直至提示找不到需要查找的数据为止。
  3. 使用 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

引用和工具:

  • 文章头图引用自Discountsglobal.com
  • 文中 GIF 图全部为原创,截图工具 GIPHY CAPTURE
  • GIF 图压缩工具 www.iloveimg.com

本文分享自微信公众号 - 小马哥的牛棚(MLB-Umpire),作者:MarshallQIU

原文出处及转载信息见文内详细说明,如有侵权,请联系 yunjia_community@tencent.com 删除。

原始发表时间:2018-08-15

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

我来说两句

0 条评论
登录 后参与评论

相关文章

  • 如何在 Tableau 中对列进行高亮颜色操作?

    在做数据分析时,如果数据量比较大,可以考虑使用颜色对重点关注的数据进行高亮操作,显眼的颜色可以帮助我们快速了解数据和发现问题。比如一个数据表可能会有十几到几十列...

    小马哥的牛棚
  • 数据清洗(data cleaning)的重要性

    之前经常和临床试验数据打交道,无论是来自手动录入的数据还是取自数据库的数据,在完成数据获取这一步后,感觉有80%甚至90%的时间和精力会用在做数据清洗(data...

    陆勤_数据人网
  • 大数据如何破解腐败密码?厉害了

    戳上图,订阅2018年《方圆》杂志 美国最高法院大法官刘易斯·布兰迪斯(Louis Brandeis)曾写道:“阳光是最好的防腐剂。”在全球共同推动下,大数据所...

    企鹅号小编
  • 峰会演讲嘉宾李御玺:数据分析人才培养之道

    谢谢主持人,谢教授、各位专家,大家好! 现在我给大家介绍一下数据分析人才的知识结构,事实上这两天的论坛,这两天的演讲,要做数据分析的人他应该具备哪一方面的知识和...

    机器学习AI算法工程
  • MapReduce极简教程

    一个有趣的例子 你想数出一摞牌中有多少张黑桃。直观方式是一张一张检查并且数出有多少张是黑桃? ? MapReduce方法则是: 给在座的所有玩家中分配这摞牌 ...

    架构师小秘圈
  • 如何判断一个数据分布是否是正太分布?

    在数据分析过程中,得到一组数据,在分析之前,通常需要判断数据是否符合正态分布与否,再决定下一步分析方法。那么,如何判断数据是否属于正太分布呢?

    小末快跑
  • 政府开放数据(Open Data) 大数据产业的机遇与挑战

    下一代创新研究mGovLabChina 移动政务实验室聚焦新一代信息技术发展所催生的知识社会以人为本、用户参与的下一代创新(创新2.0),及其引发的产业、政府、...

    小莹莹
  • Web开发在过去20多年时间里如何改变了我

    web在过去20年时间里改变得相当快。越来越多的逻辑从服务器端移动到了客户端。不但需要在客户端编写更复杂的JavaScript代码,而且最近几年还发生了一些奇特...

    用户1289394
  • 深度剖析“开放政府数据”

    导读 开发一款全国人民都能使用的“找公厕”APP的核心障碍是什么?是开发者缺少关于公厕的数据。这就涉及到开放政府数据的问题。政府数据既能够推动经济增长乃至经济增...

    小莹莹

扫码关注云+社区

领取腾讯云代金券