数据治理：让你效率迅速提升的10个Excel数据清理技巧

根据IDC调查显示，数据科学家有80%的时间都花在了数据的整理和准备上！没有大量的数据治理和清洗工作，再华丽的图表，再炫酷的大屏，都是空中楼阁。

拼写错误的单词、难以去除的尾随空格、不需要的前缀、不正确的大小写和非打印字符给人一种不好的第一印象。导致数据脏乱差的因素还不止这些……

数据清理是根据数据分析目的，将收集到的数据，用适当的处理方法进行加工、整理，形成适合数据分析的要求样式，它是数据分析前必不可少的工作，并且在整个数据分析工作量中占据了大部分比例。数据清理包括数据抽取、数据清洗、数据合并、数据计算、数据分组等操作。

说到数据处理或数据分析，大家肯定都耳熟能详的认为其必须具有如下技能：熟练的掌握SQL、Hive等，R、Python等至少精通其中一种，但是大家往往忽略了一个最基本的工具那就是“Excle”。

1
使用Excle清理数据的基本步骤

步骤1：从外部数据源导入数据。

步骤2：在单独的工作簿中创建原始数据的备份副本，通常一个 Excel 中会包含多个工作表：原始数据表、中间表、分析结果表等。记住：不论数据有多脏，别在原始(元)数据上直接改动。一旦在元数据上直接清洗，即便清洗出现问题，也有可能无法察觉；如果元数据有增加，可能很多清洗动作又得重做一遍。

步骤3：确保数据采用行和列的表格格式：每一列中的数据类似、所有列和行可见并且该区域中无空白行。

步骤4：先执行不需要列操作的任务，例如拼写检查或使用“查找和替换”对话框。例如：干掉Excel中的空格。

步骤5：接下来，执行列操作的任务，进行数据清理。

2
使用Excle清理数据的10个技巧

技巧1：删除重复行

导入数据时，重复行是一个常见问题。最好先“筛选唯一值”，确认结果是所需结果，然后再删除重复值。

技巧2：查找和替换文本

元数据中可能会有不少字符错误，如半角句号“.”被输入成了全角句号“。”，或是在收集用户邮箱时遇到把 @ 输入成 # 的情况。可能还需要删除常见的前导字符串（例如后跟冒号和空格的标签）或后缀（例如已过时或不必要的字符串结尾处的附加说明短语），如何批量替换呢？若要执行此操作，可查找文本的实例，然后将其替换为无文本或其他文本。可使用Vlookup、Replace、Substitute函数进行字符串的查找和替换。

Vlookup：按列查找的方式从指定数据表区域的最左列查找特定数据，它能够返回查找区域中与找到单元格位于相同行不同列的单元格内容 。

Replace：将一个字符串中的部分字符用另一个字符串替换。

Substitute：将字符串中的部分字符串以新字符串替换。

技巧3：数据截取

可使用Excel的数据截取函数进行数据截取，数据截取类函数主要功能为从文本中提取需要的字符串，主要包括left、right、mid函数。

Left：从一个文本字符串的第一个字符开始，返回指定个数的字符

Right：从一个文本字符串的最后一个字符开始返回指定个数的字符

Mid：从文本字符串中指定的起始位置起，返回指定长度的字符

技巧4：更改文本大小写

有时文本格式混乱，尤其是文本大小写方面。使用三种 Case 函数中的一种或多种，可将文本转换为小写字母（如电子邮件地址）、大写字母（如产品代码）或首字母大写（如姓名或书名）。

技巧5：删除文本中的空格和非打印字符

有时文本值包含前导空格、尾随空格或多个嵌入空格字符（Unicode 字符集值 32 和160），或非打印字符（Unicode 字符集值 0 到 31、127、129、141、143、144 和 157）。执行排序、筛选或搜索操作时，这些字符有时会导致意外结果。例如，在外部数据源中，用户可能会无意添加额外的空格字符，从而导致打字错误，或者从外部源导入的文本数据可能包含嵌入在文本中的非打印字符。由于这些字符不容易引起注意，因此意外结果可能很难理解。若要删除这些不需要的字符，可组合使用 TRIM、CLEAN 和SUBSTITUTE 函数。

技巧6：修复数字和数字符号

主要有两个数字问题可能需要你进行数据清理：无意中将数字导入为文本，以及需要根据你组织的标准更改负号。

技巧7：修复日期和时间

由于存在许多不同的日期格式，并且这些格式可能混杂有编号部件代码或其他包含斜杠标记或连字符的字符串，因此日期和时间通常需要进行转换和重新设置格式。

技巧8：合并和拆分列

有合必有分，有时从数据库中导出的数据会以csv 或是txt 的格式存储，并以逗号将各列进行分隔。这种情况下可使用分列操作将各列数据分开。例如，可能需要将包含全名的列拆分为名字和姓氏。或者可能需要将包含地址字段的列拆分为单独的街道、城市、地区和邮政编码列。反之亦可。可能需要将名字和姓氏列合并为一个全名列，或者将单独的地址列合并为一列。其他可能需要合并为一列或拆分为多列的常见值包括产品代码、文件路径和 Internet 协议 (IP) 地址。可使用concatenate函数将多个文本字符串合并成一个。

技巧9：转换和重新排列行和列

Excel中的大多数分析和格式设置功能都假设数据存在于单个平面二维表中。有时可能需要将行转换为列、将列转换为行。有时候，数据甚至不是表格格式结构，需要使用一种方法将数据从非表格格式转换为表格格式。

技巧10：通过联接或匹配协调表格数据

有时，数据库管理员会使用 Excel 查找并更正两个或多个表联接时的匹配错误。这可能涉及协调不同工作表中的两个表，例如，查看两个表中的所有记录，或比较两个表并查找不匹配的行。可使用Excel的VLOOKUP将一个表格的数据匹配到另一个表中。

3
事实上，Excle的能力超乎想象

下面这幅画来自日本77岁老人堀内辰男，是他参加EXCEL自动图形艺术大赛的获奖作品。

是的，你没有看错，这幅画使用EXCEL设计制作的！

堀内辰男，日本长野人，退休后想到了在电脑上用Excel来作画的金点子，他通过坚持不懈地学习，不仅掌握了基本技能，还成为一名数字艺术家。

Excel最牛逼的地方在于它不是小李飞刀也不是轩辕剑——需要练个10年8年才能用，它只是一把菜刀，老百姓可以用来切菜，高手可以用来刮胡子，绝世高手拿着直接从南天门一直砍到蓬莱东路。

——知乎用户雨声敲敲

Excel的使用门槛极低，简单5分钟就能学会它的基础操作。但要是想学会并精通它的所有功能却不是简单的事情。如果你能掌握一定的方法与技巧工作效率一定大幅度提高。

注明：本文转自公众号谈数据！

(加入数据工匠俱乐部知识星球获取更多资讯)

联系我们

扫描二维码关注我们

微信：DaasCai

邮箱：[e-mail]

QQ：2286075659

热门文章

我们的使命：普及数据管理知识、发展数据管理工程师行业、改变中国企业数据管理现状、提高企业数据资产管理能力、推动企业走进大数据时代。

我们的愿景：凝聚行业力量、打造数据工程师全链条平台，培养不同层级数据工程师人才、构建数据工程师生态圈。

我们的价值观：分享数据管理知识，持续提升数据管理和运营能力。

了解更多精彩内容

长按，识别二维码，关注我们吧！

数据工程师

微信号：sjgcs

构建数据工程师生态圈