1.2 商务数据的清洗

在挖掘数据的过程中，海量的原始数据可能会存在不完整、不一致或有异常等情况，严重时甚至会影响数据分析的最终结果。因此，在数据分析前对采集到的数据进行数据清洗尤为重要。

数据清洗是指发现并纠正数据文件中可识别错误的最后一道程序，是对数据的完整性、一致性和准确性进行重新审查和校验的过程。数据清洗主要是对多余或重复的数据进行筛选清除，将缺失的数据补充完整，对错误的数据进行纠正或删除。

1.2.1 清洗缺失数据

在采集数据的过程中，缺失数据常常表示为空值或错误标识符（#DIV/0!）。一般情况下，Excel中出现错误标识符大多是公式使用不当造成的，可以利用Excel的定位功能查找到数据表中的空值和错误标识符。

清洗缺失数据的方法一般有以下4种。

（1）用样本统计数据代替缺失数据。

（2）用统计模型计算出来的数据代替缺失数据。

（3）删除有缺失数据的记录。

（4）保留有缺失数据的记录。

下面将采用“用样本统计数据代替缺失数据”的方法来清洗缺失数据，具体操作如下。

（1）启动Excel 2016，打开素材文件“店铺人均销售额统计表.xlsx”工作簿（素材参见：素材文件\第1章\店铺人均销售额统计表.xlsx），如图1-10所示。

（2）由表可知，第8行支付金额的缺失导致平均销售金额为0，因此需要对缺失的数据进行清洗，这里使用平均值代替缺失数据。在【开始】/【编辑】组中单击“查找和选择”按钮，在打开的下拉列表中选择“定位条件”选项，如图1-11所示。

（3）打开“定位条件”对话框，单击选中“空值”单选项，然后单击“确定”按钮，如图1-12所示。

（4）返回工作表后，Excel将自动定位至D8单元格（如果工作表中有多个空值单元格，那么使用“定位条件”功能后，Excel将自动选择工作表中所有的空值单元格），然后在编辑栏中输入公式“=(D7+D9)/2”，如图1-13所示，表示计算该笔订单前后两笔订单支付金额的平均值，并按【Enter】键得出计算结果。

（5）此时，D8单元格中的数据将不再为0，而是“412.5”。选择D2:D14和F2:F14单元格区域，在【开始】/【数字】组中，单击“数字格式”列表框右侧的下拉按钮 ，在打开的下拉列表中选择“货币”选项，如图1-14所示。

（6）所选单元格区域的数据将以货币格式显示，并且自动保留小数点后两位，最终效果如图1-15所示（效果参见：效果文件\第1章\店铺人均销售额统计表.xlsx）。

1.2.2 清洗重复数据

重复数据一般可分为实体重复和字段重复两种。其中，实体重复是指所有字段完全重复；字段重复是指某一个或多个不该重复的字段重复。为了保证数据的一致性，在获取数据后，需要对重复数据进行处理。

1.查找重复数据

在清洗重复数据前，应该先查找重复数据，一般可采用数据透视表法、函数法、高级筛选法和条件格式法。

（1）数据透视表法

打开要清洗的数据，插入数据透视表，在“数据透视表字段”任务窗格中拖曳相应的字段到“行”列表框、“列”列表框和“值”列表框中，完成数据透视表的创建。通过数据透视表，可以统计出各数据出现的次数，出现两次及两次以上的数据就属于重复数据，图1-16所示为利用数据透视表查找重复数据的效果。需要注意的是，“值”列表框中的字段要设置为“计数”汇总方式才能查找到重复数据。

（2）函数法

利用Excel提供的统计函数COUNTIF，可以对指定区域中符合指定条件的单元格进行计数，并以此识别重复数据。

COUNTIF函数的语法结构为：COUNTIF(range,criteria)。其中，range表示计算非空单元格数目的区域，criteria表示以数字、表达式或文本形式定义的条件。在I2单元格中输入公式“=COUNTIF($A$2:A2,A2)”，按【Enter】键得出计算结果，商品名称“T恤”无重复。将公式向下填充，查询区域将以A2单元格为起始单元格，结束单元格是不断增加的动态区域，即公式每向下填充一行，查询区域就向下增加一个单元格，如图1-17所示，当“辅助列”中出现大于“1”的数值时就表示对应的商品名称有重复。

（3）高级筛选法

利用Excel提供的“高级筛选”功能可以快速查找并删除大量重复数据。打开带有重复数据的工作簿，在【数据】/【排序和筛选】组中单击“高级筛选”按钮 ，打开“高级筛选”对话框，在其中设置筛选结果的存放位置、参与筛选的数据区域和筛选条件等参数后，单击选中“选择不重复的记录”复选框，然后单击“确定”按钮，便可在查找重复数据的同时自动删除重复数据，如图1-18所示。

（4）条件格式法

利用Excel处理数据时，如果要突出显示数据区域中的重复数据，则可通过“突出显示单元格规则”选项来实现。在工作表中选择要突出显示重复数据的单元格区域，在【开始】/【样式】组中单击“条件格式”按钮 ，在打开的下拉列表中选择“突出显示单元格规则”选项，在打开的子列表中选择“重复值”选项，如图1-19所示，打开“重复值”对话框，保持默认设置并单击“确定”按钮后，所选区域中的重复数据会以“浅红填充色深红色文本”样式显示，最终效果如图1-20所示。

2.删除重复数据

上述4种方法，只有高级筛选法可以删除重复数据，其他3种方法都只是查找出重复数据，不能同时删除重复数据。下面将介绍删除重复数据的具体操作。

删除重复数据的操作很简单，只需要选择数据区域中的任意一个单元格，在【数据】/【数据工具】组中单击“删除重复项”按钮 ，如图1-21所示，在打开的“删除重复值”对话框中单击“确定”按钮。

1.2.3 清洗错误数据

除了缺失数据和重复数据外，其他的数据不规范现象还有很多，如错误数据。错误数据可能是人工录入错误导致的，也可能是被调查者输入的信息不符合要求导致的。因此，为了尽可能地保证数据的准确性，需要对错误数据进行处理。

1. 清洗人工录入的错误数据

利用Excel提供的“条件格式”功能可以快速查找出人工录入的错误数据。假设某一表格中只能输入数字“0”和“1”，除此之外的数字为错误数据。下面将对工作表中除数字“0”和“1”以外的错误数据进行清洗，具体操作如下。

（1）打开“人工录入的数据.xlsx”工作簿（素材参见：素材文件\第1章\人工录入的数据.xlsx），在“Sheet1”工作表中选择B3:H6单元格区域，如图1-22所示。

（2）在【开始】/【样式】组中单击“条件格式”按钮 ，在打开的下拉列表中选择“新建规则”选项，如图1-23所示。

（3）打开“新建格式规则”对话框，在“选择规则类型”栏中选择“使用公式确定要设置格式的单元格”选项，在“编辑规则说明”栏的“为符合此公式的值设置格式”文本框中输入“=OR(B3=1,B3=0)=FALSE”（表示同时不等于0和1两个数字的数据为错误数据），然后单击“格式”按钮，如图1-24所示。

（4）打开“设置单元格格式”对话框，单击“字体”选项卡，在“颜色”下拉列表中选择“标准色”栏中的“红色”选项，如图1-25所示，然后单击“确定”按钮。

（5）返回“新建格式规划”对话框，单击“确定”按钮，返回“Sheet1”工作表，所选单元格区域中不符合规则的数据将呈红色显示，最终效果如图1-26所示（效果参见：效果文件\第1章\人工录入的数据.xlsx）。

2.清洗被调查者输入的错误数据

在进行问卷调查时，如果其中的多项选择题最多可选3项，而被调查者却选择了4项及以上，那么就可以综合利用Excel提供的COUNTIF函数和IF函数来判断数据的正确性。下面将以客户满意度调查中的多项选择题为例来介绍这类错误数据的清洗方法，具体操作如下。

（1）打开“被调查者输入的数据.xlsx”工作簿（素材参见：素材文件\第1章\被调查者输入的数据.xlsx），在“Sheet1”工作表中选择I3单元格，在编辑栏中输入公式“=IF(COUNTIF(B3:H3,"<>0")>3,"错误","正确")”，如图1-27所示。

（2）按【Enter】键得出计算结果，然后重新选择I3单元格，将鼠标指针移至该单元格的右下角，当鼠标指针变成 形状后向下拖曳，如图1-28所示，直至将公式填充至I21单元格后再释放鼠标。

（3）I3单元格中的公式将快速复制到I4:I21单元格区域中，并显示图1-29所示的计算结果（效果参见：效果文件\第1章\被调查者输入的数据.xlsx）。由计算结果可知，显示为“错误”的单元格就是被调查者输入的错误数据。