下载掌阅APP，畅读海量书库
立即打开
在实践中，从调查问卷、数据库或者网页获取的数据可能并不规范，例如包含重复值、缺失值，数字因以文本的格式存储而无法进行计算等。将数据整理成规范的格式，可以提高分析数据的效率，达到事半功倍的效果。本节通过例1.1介绍Excel中常用的数据清洗方法。
图1.12所示是某年NBA全明星赛中的球员数据。
图1.12 某年NBA全明星赛中的球员数据
单击单元格A1，单击“数据”→“删除重复项”，在弹出的对话框中勾选“ID”“First Name”和“Last Name”，如图1.13所示，若存在这3个变量的观测值相同的记录，则认为它们是重复记录。
图1.13 “删除重复项”对话框
单击“删除重复项”对话框中的“确定”，弹出图1.14所示的警告，提示“2找到并删除重复值；保留18唯一值”等，表示表中原本有20条记录，删除了其中2条重复记录。
图1.14 “删除重复项”的警告
图1.12所示的表中有空白单元格，存在数据缺失的记录（缺失值）。通常，需要将缺失值剔除，以保证在整个研究中样本容量的一致性。若不剔除缺失值，会面临在分析不同的变量时样本容量不一致的情况，也可能导致某些统计分析无法实现。
首先框选单元格区域“A1:I19”，然后单击“开始”→“编辑”→“查找和选择”→“定位条件”，弹出“定位条件”对话框，选择“空值”，如图1.15所示，单击“确定”。
图1.15 打开“定位条件”对话框
空白单元格被选中，单击鼠标右键并在弹出的快捷菜单中选择“删除”（或者按Ctrl + −），弹出“删除”对话框，选择“整行”，如图1.16所示，单击“确定”，即可将缺失值所在行剔除。
图1.16 剔除缺失值
删除了存在缺失值的4行，保留了14条记录。
使用UPPER函数或LOWER函数可以分别将英文字母全部转换成大写或者全部转换成小写，使用PROPER函数可以将首字母转换为大写。如图1.17所示，列B中有的球员名字的首字母没有大写，在其右侧插入3个空白列，在单元格C2中录入公式“=PROPER(B2)”，然后框选单元格区域“C2:C15”，单击“开始”→“编辑”→“填充”→“向下”，即可自动完成公式填充。
图1.17 “开始”卡片中的“填充”工具
由于列C中的值根据列B中的值计算而得，若删除列B，会导致列C中的计算无法实现。如图1.18所示，复制单元格区域“C1:C15”，然后单击单元格E1，再单击鼠标右键，在弹出的快捷菜单中选择“选择性粘贴”→“值”，即可复制列C中的值到列E中。此时将列B删除，列E中的值不会受到影响。
图1.18 选择性粘贴
图1.12所示表格中列D中的国家名称，也存在大小写不一致的问题，使用UPPER函数可以将国家名称的所有字母都设置为大写。
如图1.19所示，单元格C4和C13中有几个多余的空格，使用TRIM函数可以删除单元格中多余的空格。若不删除多余的空格，Excel会认为“Mathurin”和“Mathurin”是两个不同的观测值，会造成统计错误。
图1.19 单元格中存在多余空格
图1.20中的列F是球员所在的学校的名称，Duke University和Duke都表示杜克大学，University of CA Los Angeles和UCLA都表示加州大学洛杉矶分校。在录入学校名称数据时，有的用简称，有的用全称，我们需要将形式上不同但实质内容相同的观测值统一。
首先，在单元格G2中录入公式“=UNIQUE(F2:F15)”（见单元格H2），用UNIQUE函数查看学校名称的取值情况。
然后，如图1.20所示，框选单元格区域“F2:F15”，单击“开始”→“查找和选择”→“替换”，在“查找和替换”对话框的“查找内容”文本框中输入Duke University，在“替换为”文本框中输入Duke。单击“全部替换”，提示“完成2处替换”，如图1.21所示。再用同样的操作将University of CA Los Angeles替换为UCLA。
图1.20 使用UNIQUE函数并进行查找和替换
图1.21 查找和替换的结果
列G中的出生日期中的时间是无用数据，需要删除。首先，在列G右侧插入一列，用来存放分列后的内容。然后，选中列G，即需要分列的内容。单击“数据”→“分列”，在弹出的“文本分列向导-第1步，共3步”对话框中选择“分隔符号”，如图1.22所示，单击“下一步”。
图1.22 文本分列的第1步
在“文本分列向导-第2步，共3步”对话框中勾选“制表符”、“空格”和“连续分隔符号视为单个处理”，下方预览区域中出现了一条分列线，将“年月日”与“00:00:00”分开，如图1.23所示，单击“下一步”。
图1.23 文本分列的第2步
根据图1.24进行设置，“目标”是指定分列以后的数据的输出位置。本例使用默认设置，分列后的数据放置于以单元格G1为左上角的单元格区域，并将替换列G中原有的数据。
图1.24 文本分列的第3步
单击图1.24所示对话框中的“完成”，得到图1.25所示的结果，再删除列H即可。
图1.25 文本分列的结果
以文本形式存储的数据无法进行数值运算，需将其转换成数值，如图1.26所示。在列H右侧插入2个空白列，在单元格H16中录入公式“=AVERAGE(H2:H15)”（见单元格H17）计算年龄均值，返回“#DIV/0!”（见单元格H16）。列H中的年龄观测值的左上角有绿色的标记，代表这些单元格中的数据都是以文本形式存储的。这时需要调用VALUE函数，将文本数据转换成数值。
在单元格I2中录入公式“=VALUE(H2)”（见单元格J2），将其中文本转换为数值后方可对其进行计算。用同样的方式转换单元格区域H3:H15中的数据，然后再计算年龄均值。
框选单元格区域“H2:H15”，单击三角形惊叹号，选择“转换为数字”，如图1.27所示，也可以将以文本形式存储的数据转换为数值。
图1.26 计算年龄均值
图1.27 将以文本形式存储的数字转换为数值
列I中的身高观测值如“6-6”的实际含义是6ft（英尺）6in（英寸），需要将其转换为数值。首先利用1.4.6节中介绍的分列，将列I中的数据进行分割，分隔符设置为“−”，如图1.28所示。
图1.28 设置分隔符为“-”
如图1.29所示，在单元格K2中录入公式=I2*30.48+J2*2.54（见单元格L2），将身高调整为以厘米为单位的数据。（1ft≈30.48cm，1in≈2.54cm。）
图1.29 计算以厘米为单位的身高
列J中的体重观测值都含有单位“lbs”（磅），在单元格K2中录入189，单击“数据”→“快速填充”，即可进行批量转换，如图1.30所示。
图1.30 “数据”卡片上的“快速填充”
在列C右侧插入一列，然后在单元格D2中录入Joshua Primo，单击“数据”→“快速填充”，即可将First Name和Last Name合并成全名，如图1.31所示。
图1.31 将First Name和Last Name合并成全名
如图1.32所示，在数据区域下方计算每个变量的最大值、最小值，考查观测值的分布是否在一个合理的区间中（以列J为例在列K中给出函数示例）。对于文本形式的变量，其最大值和最小值都等于0。
对于数值型数据，利用COUNT函数统计包含数值的单元格个数。对于文本数据，利用COUNTA函数统计包含非空单元格的个数。使用COUNTBLANK函数可以统计空白单元格的个数。对每个变量，检查上述统计结果的一致性。
图1.32 异常值和缺失值的识别
如图1.33中的列B所示，性别和专业的观测值都是数值代码，若直接以数值代码绘制图形，则图形中的标注也是数值代码，这不利于理解。利用IF函数将数值代码转换为文本，这样生成的图就能让人一目了然。IF函数的第1项参数是条件表达式，若条件成立，则返回第2项参数值，若条件不成立，则返回第3项参数值详见图1.33中的列D，其返回结果见图1.33中的列C。
图1.33 利用IF函数将数值代码转换为文本
注意 ：IF函数中的第2项和第3项参数值若是文本，需要用半角双引号引起来。
实操技巧
● 框选数据区域，单击“数据”→“删除重复项”，剔除重复值。
● 框选数据区域，单击“开始”→“编辑”→“查找和选择”→“定位条件”，弹出“定位条件”对话框，选择“空值”，单击“确定”，剔除缺失值。
● 使用UPPER函数可以将英文字母转换成大写，使用LOWER函数可以将英文字母转换成小写，使用PROPER函数可以将首字母转换为大写。
● 使用TRIM函数可以删除单元格中多余的空格。
● 单击“数据”→“分列”，将单元格中的内容按指定形式分隔，或者添加分列线，对文本进行分列。
● 使用VALUE函数可以将以文本形式存储的数据转换为数值。
● 使用“数据”卡片下的“快速填充”，可以执行批量转换。
● 计算定量变量的最大值、最小值，可以考查观测值是否分布在一个合理的区间中。
● 使用COUNT函数可以统计包含数值的单元格个数，使用COUNTA函数可以统计包含非空单元格的个数，使用COUNTBLANK函数可以统计空白单元格的个数。
● 使用IF函数可以将数值代码转换为文本，它的第1项参数是条件表达式，若条件成立，则返回第2项参数值，若条件不成立，则返回第3项参数值。
● 使用“选择性粘贴”可以只粘贴单元格的值，去掉公式信息。