利用Excel进行简单的数据清洗

按照百度百科，数据清洗(Data cleaning)是指对数据进行重新审查和校验的过程，目的在于删除重复信息、纠正存在的错误，并提供数据一致性。其原理是利用有关技术如数理统计、数据挖掘或预定义的清理规则将脏数据转化为满足数据质量要求的数据。本文中尝试用Excel进行简单的数据清洗。
数据清洗从名字上也看的出就是把“脏”的“洗掉”，指发现并纠正数据文件中可识别的错误的最后一道程序，包括检查数据一致性，处理无效值和缺失值等。因为数据库中的数据是面向某一主题的数据的集合，这些数据从多个业务系统中抽取而来而且包含历史数据，这样就避免不了有的数据是错误数据、有的数据相互之间有冲突，这些错误的或有冲突的数据显然是我们不想要的，称为“脏数据”。我们要按照一定的规则把“脏数据”“洗掉”，这就是数据清洗。而数据清洗的任务是过滤那些不符合要求的数据，将过滤的结果交给业务主管部门，确认是否过滤掉还是由业务单位修正之后再进行抽取。不符合要求的数据主要是有不完整的数据、错误的数据、重复的数据三大类。数据清洗工作也对应地分为三个方面：
一、记录完整性检查
主要是一些应该有的信息缺失，造成记录不完整。
检查方法：在字段值符合该字段格式要求的前提下，检查是否为空即可，通常利用counta()函数即可实现，如下图：

解决方法：典型情况下，具体字段的值是无法从其它字段推出的，因此解决字段数据缺失的唯一办法是要求提供完整的数据源，即补充缺失的数据。
二、字段规范性检查
主要是检查字段数字格式是否符合该字段要求，如日期格式是否规范，文本字符数是否正确等。
检查方法：根据具体字段格式要求不同，检查方法也各异，需要具体情况具体分析。下图是简单的文本字符数检查：

解决方法：此项错误有两类，一类是和上图中例子一样，无法纠正数据格式，解决起来只能要求更正源数据；另一类是系统性地格式错误，经过简单处理可以纠正，如文本格式的日期、数字等。
根据我所在单位工作实际，经常会遇到两种情况：一是导出数据中日期看起来和日期格式一模一样，但实际是文本格式，因而Excel无法对其日期进行正确计算。这种情况可以通过替换+设置数字格式的方法来纠正，先将“假日期”所在列中日期格式的关键符号如“-”或“/”批量替换为自身，如将“-”替换为“-”，完成后再将该列设置为所需的日期格式，即可纠正日期格式。第二种情况是数量、金额等数字统一是文本格式，这种情况可通过选择性粘贴来纠正。

三、重复性检查
我认为包括两种情况：
（一）主关键字重复
即不同记录的主关键字重复，或通俗地说不同行的特征列取值重复，如不同员工的工号重复等。
检查方法：利用countif()函数，针对单列进行检查，如图：

解决方法：核查清楚，去伪存真。
（二）整条记录重复
整条记录重复，或者说Excel中不同行的各列单元格数据完全相同，可以理解为冗余数据。
检查方法：高级筛选固然能查重复，但会破坏原数据，故不采用。在功能区“数据”选项卡中，有“删除重复项”这一功能，根据提示操作即可删除重复的记录。