excel数据清洗_数据清洗excel

数据清洗与加工

目的：获得具备准确性、完整性和一致性符合分析质量的数据。

数据处理第一步：数据清洗

（1）数据去重

方式1：删除重复项功能。适用于有重复项出现的列，并且这样的重复无意义，比如标识列。操作：【数据】选项卡下的【删除重复值】按钮

方式2：排序删除重复项。适用于需要人工判断无用重复项的数据，即将数据内容相同的放在一起，可以辨认出哪些需要删除的。

操作：比如要删除员工姓名列。右击员工姓名的任一单元格--【排序】--【升序】选项，之后可以通过快速找到重复数据判断是否要删除

方式3：条件格式删除重复项。适用于难以判断是否存在重复值的列，比如商品编码。

选中A列--【开始】--【条件格式】--【突出显示单元格规则】--【重复值】，

选中重复值后，可以进行行的删除。

（2）处理空值

步骤1：寻找空值。操作：【开始】--【查找和选择】--【定位条件】--【空值】

步骤2：处理空值。替换空值—可以采用平均值进行替换，也可以采用数据预测回归法进行补充数据（适用于连续时间段内的销量数据有默认值）；删除空值，适用于样本数量较多时；人工经过核查进行填写。

其中：一次性批量补充空值，比如采用平均值替换

操作：定位空值后，保证空值处于选中状态—输入平均值—【ctrl+enter】组合键

（3）检查数据逻辑

方式1：通过函数检查逻辑值。可以在需要判别逻辑是否正确的列旁边添加一列逻辑是否正确的判断列，比如判别性别列的输入值是否正确，可以采用IF函数和OR函数结合进行判断。

方式2：通过条件格式检查逻辑值。【开始】--【条件格式】--【突出单元格规则】选项，可以进行数值、文本检查，而且还可以建立新的规则进行判别，方式较为方便。

（4）检查格式

步骤1:格式检查方法—选中数据，右击设置单元格格式，可以看到当前单元格内容的格式

步骤2：格式修改—日期格式，通过分列可以完成统一。【数据】-【数据工具】-【分列】--【固定宽度】--【日期】下拉框选择【YMD】选项，可以将格式统一。

数据处理第二步：数据加工

（1） 数据计算—简单计算、常用函数计算

（2） 数据转化—为方便分析进行行列的转换、为保证同一列的统计口径相同可以采用查找和替换。

（3） 数据分类—采用vlookup函数可以实现数据分类，首先设置条件区域，目的是告诉函数以什么依据为数据进行分组；其次是通过vlookup函数实现分组，借助相对引用和绝对引用实现数据的快速填充。

（4） 数据重组。

数据拆分：

方式1分隔符拆分.适用于数据列没有统一的字符宽度，但是有固定的分隔符号，比如以空格作为分隔符。

方式2固定宽度拆分.适用于有固定的字符宽度，比如身份证号。

数据合并：

方式1:连接符“&”

方式2：将数据转成文本格式，采用函数text(B3,”0.0%”)表示将B3单元格转换成文本格式，并且显示为带一位小数的百分数。

方式3：concateane函数—将单元格数据连接成文本。

数据抽取：

从1列数据中抽取：采用left、right等文本函数

从多列数据中抽取：一般采用vlookup函数

案例数据分析：

表1购买商品数据分析：

1. 年度销售数据概况分析

2.2013年和2014年上半年和下半年销量对比分析

上半年销售数据对比分析，可以看出2013年上半年整体增幅缓慢，数据变化并不特别明显。而2014年整体呈现明显的上升趋势，增幅较快。从两者对比增加幅度比可以得到2014年销量远高于2013年，其中3月份和5月份作出了较大贡献，因此可以进一步分析增加3月份和5月份销量激增的相关因素。

从下半年销售数据对比分析可以看出，2013年下半年和2014年下半年(11月份除外)整体增幅平稳，2014年下半年整体销量远高于2013年主要在于8月、9月、10月和11月，尤其是11月较2013年11月销量增幅高达416%，因此也可以进行回归分析，探讨驱动销量大幅度增长的相关因子。

表2婴儿出生信息分析：

（1）首先对表2的字段进行清洗，删除gender=2的值；转换出生日期格式；

（2）数据加工。根据表2的字段userid与表1进行匹配，采用vlookup函数得到相应用户购买日期，并采用days函数得到出生日期与购买日期之间的天数之差，并换算成购买时婴儿的年龄，这里只考虑婴儿出生后才进行购买的用户。

(3)数据分析。

婴儿年龄与购买数量，采用VBA将各年龄段婴儿用品购买数量进行统计，得到结果如下：

购买数量和商品种类分析：

采用数据透视表对用户购买数量与商品id二级分类进行分析，其中id号为50008168和50014815的销售量最高，其次是id为28号的商品，针对该部分目标用户，在其浏览网页时可以多放置相关商品的信息，增加互补品的销售量，或者进行打折促销捆绑销售等。当然，影响用户购买行为的因素还有很多，仍需要更具体的探讨分析以完善相关决策。