基于Excel的数据清洗应用研究
来源:用户上传
作者:
摘 要：高质量的数据是数据分析和应用的重要保证，“脏数据”会导致不可靠输出，这种输出导致的结果有可能难以弥补，因此数据清洗尤为重要。该文重点阐述了数据清洗思路及利用Excel软件进行数据清洗的具体方法，为教育和科研工作者提供一些参考。
关键词：Excel 数据清洗 方法
中图分类号：G250.2 文献标识码：A 文章编号：1672-3791（2019）05（c）-0028-02
随着计算机及通信技术的迅猛发展，如今已经进入了全新的数字化及智能化时代。各种信息系统的广泛应用积累了大量的原始数据，分析这些数据的内在规律，预测相关业务量的发展趋势，已经成为各行各业的迫切需求，因此掌握数据分析技术显得尤为重要。而数据清洗又是整个数据分析过程中不可缺少的一个环节，其结果质量直接关系到模型效果和最终结论。在实际操作中，数据清洗通常会占据分析过程的50%～80%的时间。
1 数据清洗的定义
数据清洗，英文名为Data Cleaning或Data Scrubbing，是检测和去除数据集中的噪声数据和无关数据，处理遗漏数据，去除空白数据域和知识背景下的白噪声。也就是将重复、多余的数据筛选清除，将缺失的数据补充完整，将错误的数据纠正或者删除，最后整理成为标准的、干净的、连续的数据，提供给数据分析、数据挖掘等使用。
2 常用的数据清洗方式
实际工作中常用的数据清洗方式有人工清洗、计算机清洗及人工和计算机结合清洗。对于非标准数据或数据量较小的数据源，可使用人工清理的方式，即借助统计调查实际工作经验，通过人工检查的原始方法来发现异常值，但如果数据量较大、变量较多的情况，使用这种方法费时费力，效率相对较低。计算机清洗则是通过SPSS、SAS、Excel和Python等软件，设置相应的函数及校验规则，对缺失值、取值范围，以及数据格式、数据类型、拼写错误等问题进行识别和处理，大数据时代的数据清理更适合采用该种方式。然而计算机程序难免出现编程错误或bug，因此还可以采用人工和计算机相结合清洗的方式，首先通过计算机检测出可疑数据，统计调查人员再根据其业务知识和经验进行人工判断。数据清洗后还需进行质量审核及评估，在审核评估中不断发现问题、解决问题，确保调查数据的完整性和唯一合法性。该文则主要探讨基于Excel的计算机清理方法。
3 数据清洗的方案
数据清洗是发现并纠正数据文件中可识别的错误的最后一道程序，包括检查数据完整性、唯一性、合法性等。数据的完整性，重点检查数据是否有缺失值，例如人的属性中缺少性别、籍贯、年龄等。数据的唯一性，重点检查否有重复样本，例如不同来源的数据出现重复的情况。数据的合法性，则检查数据是否有异常值，例如获取的数据与常识不符，年龄大于150岁。
3.1 缺失值的处理思路
缺失值指的是数据原本是必须存在的，但实际上该变量没有数据，它与空值不同。主要的处理方法有其他信息补全法，例如使用省份证号推算性别、籍贯、出生日期、年龄等。这种方法比较复杂，一般用在补全一些不可缺失的信息时使用。前后数据补全法，例如时间序列数据部分缺失时，可以使用前后数据的均值或整个时间序列的均值进行补全。当缺失数据较多时，可以使用平滑处理进行补全。剔除法，该办法在实在无法补全的情况下使用，剔除不等于删除，可以暂时不用这些数据，在以后的分析中可能还会使用。
3.2 重复值的处理思路
数据中属性值相同的记录被认为是重复记录，通过判断记录间的属性值是否相等来检测记录是否相等，相等的记录合并为一条记录，即合并或者清除。主要方法有按主键去重及按规则去重。
3.3 异常值的处理思路
异常值是指样本中的个别值，其数值明显偏离所属样本的其余观测值。用统计分析的方法识别可能的错误值或异常值，如偏差分析、识别不遵守分布或回归方程的值，也可以用简单规则库（常识性规则、业务特定规则等）检查数据值，或使用不同属性间的约束、外部的数据来检测和清理数据。主要方法有设定强制合法规则、设定字段类型合法规则及设定字段内容合法规则。
4 Excel数据清理的方法
现将数据清理过程中出现的常见问题，如重复项的处理、缺失值的处理、字段分列、去除非打印字符及空格、数据的离散化、数据的有效性等，分别探讨在Excel中处理方法。
4.1 重复项的处理
方法是直接删除重复数据，保留一条记录即可。在Excel中选择数据标签，单击“删除重复项”按钮。打开“删除重复项”对话框，默认情况下所有字段都相等认为是重复项，单击“确定”删除即可。还有一种情况是部分字段相同就认为是重复项，这种情况可以现将重复项挑选出来，再根据统计调查人员的业务经验有选择性地删除。
4.2 缺失值處理
直接剔除法，如果该字段分析价值不大且缺失比例较大，可以采取直接剔除法。前后数据补全法，即用前后数据的平均值来补全，主要应用于时间序列数据。选定缺失值字段，单击开始标签里的查找选择下拉菜单的定位条件命令。打开“定位条件”对话框，选择“空值”选项，定位出所有空缺单元格，输入平均值公式，单击Ctrl+Shift+Enter键确认。用其他表格信息补全，可以利用表格相同字段，通过“Vlookup”函数完成精确查找并补全。
4.3 数据分列
主要用于将一个字段分裂为两个字段，首先在需要分列字段后插入一个空列，然后选择数据标签的“分列”按钮，打开“文本分列向导”对话框，进入文本分列向导，选择该列数据的分隔符号进行分列操作。
4.4 去除非打印字符及空格
通过网络获取的数据有时会回车符，空格等影响数据分析的字符，可以分别利用Clean（），Trim（）函数去除。
4.5 数据的离散化
对于字符数据，可以通过数据透视表观察数据类别的基本情况，然后通过筛选进行类别的重新组合，也可以将字段重新设置成为是否标志。对于数值数据，先设置离散的区间及每个区间对应的字段值，然后利用Vlookup的近似查找完成。
4.6 数据的有效性
数据的有效性是对单元格或单元格区域输入的数据从内容到数量上的限制。在Excel中选择数据标签里的“数据验证”按钮，打开“数据验证”对话框，可以依靠系统检查数据的正确有效性，然后通过“圈释无效数据”功能可以对已录入的数据中不符合条件的数据做圈释标示。
5 结语
数据分析技术广泛应用于医疗、能源、零售、汽车、金融等诸多领域，通过分析有价值的数据，提供决策和建议。但脏数据的普遍存在，导致数据分析过程中可利用的数据有限，因此，数据清洗显得尤为重要，对数据清洗方法的研究也将更加深入。
参考文献
[1] 罗强，何利力，王晓菲.数据仓库中数据清洗技术分析[J].电脑编程技巧与维护，2015（2）：61，76.
[2] 罗艳霞，王庭熙，骆绍晔.数据预处理在图书借阅中的应用[J].莆田学院学报，2016（2）：50-53.
转载注明来源:https://www.xzbu.com/8/view-14954935.htm
