Excel数据处理（缺失值/重复值/异常值/拆分）

原创虾壳可乐可乐的数据分析之路

6月12日给大家讲解了一下数据获取的东东（时隔略久，忘记的请点击数据获取回顾），时隔一个月，接着我们的用Excel进行数据分析系列的第二篇：数据处理。文末有获取本篇实例数据的方法。

目录：

一、缺失值

1、筛选

2、定位空值

3、缺失值的处理

4、实例

二、重复值

1、countif函数

2、条件格式

3、数据透视表

三、异常值

1、异常值的判断

2、实例

四、字段拆分

1、分列

2、文本函数

3、实例

正文开始：

爬取了某招聘网站关于数据分析的职位的信息进行数据处理的实例讲解

原始字段：

岗位：岗位名称
地址：地市+区
薪资：薪资+X年经验+学历
薪资2：薪资
公司：公司名称
公司概况：公司所属行业+规模+人数

一、缺失值

缺失值即数据值为空，或为NULL等，寻找缺失值有很多方法，这里提供筛选和定位空值两个思路。

1、筛选

我们发现学历一栏里是有空值的，寻找空值的方法很多，这里提供两个方法，一个是直接筛选，在Excel里对于数据量较少的情况下筛选空值是很有效的一个方法，数据——筛选里可以找到，筛选的快捷键是“ctrl+L”.

2、定位空值

开始——查找——定位条件里选择定位空值，可以筛选出所有空值。

3、缺失值的处理

对于寻找到的缺失值我们该如何处理呢，这得看实际的数据和业务需求了，一般来说可以有以下3种处理方式，直接删除、保留和寻找替代值。

直接删除：直接删除的优点是删除以后整个数据集都变得完美了，都是有完整记录的数据，缺点是缺少了部分样本可能导致整体结果的偏差。对于有大量缺失值的在衡量利弊的情况下建议就直接删除了吧，缺失了大量关键数据的样本集统计起来也没有什么意义。
保留：保留缺失值，优点是保证了样本的完整，缺点是你得知道为什么要保留，保留它的意义是什么，是什么原因导致了值的缺失，是系统的原因还是人为的原因，这种保留建立在缺失单个数据的情况下，且缺失值是有明确意义的。
寻找替代值：如用均值、众数、中位数等代替缺失值，优点是简单且有依据，缺点是可能会使缺失值失去其本身的含义。对于寻找替代值的除了统计学中常用的描述数据的值以外，还可以人为地去赋予缺失值一个具体的值。

4、实例

具体到本例中，学历为空的缺失值我们如果直接删除，会发现在年限一栏里就少了应届毕业生这个变量了，所以不能直接删除。保留的话，按照常识，就算是应届毕业生也应该有相应的学历，是什么应届，高中？大专？本科？硕士？所以保留也不行。那要就寻找替代值了，我们发现学历里的变量有大专、本科、硕士、不限，这些是类别变量，如果取众数来替代空值的话，那应届毕业生的学历应该填本科，但我们通过分析薪资和年限发现，填本科好像不太对，学历本科，年限一年以下的薪资在4K-8K之间，而应届毕业生的薪资在10-15K，说明这个应届毕业生的学历要比本科高比硕士低，依据常识推断此处空值可填本科双学位。

可以直接筛选出来填，也可以定位空值填，此处以定位空值批量填写为例，定位好空值后直接在单元格内输入“本科双学位”，此时先不要急着回车，批量填写时要“ctrl+回车”。

二、重复值

获取数据源的时候可能因为各种原因会导致获取到完全重复的数据，对于这样的数据我们没必要进行重复统计，因此需要找出重复值并删除，这里也提供3种寻找重复值的思路：countif函数、条件格式和数据透视表。

1、countif函数

还记得countif函数吗，按条件统计个数，模板：countif(区域，条件)，这里countif（I:I,I2），统计I2单元格在I列里出现的次数，以此类推，结果为1的是出现了1次，为2是出现了2次。这样就可以统计重复出现的公司了，对于公司等招聘条件都重复的可以删除。

2、条件格式

开始——条件格式——突出显示单元格的规则——重复值，将重复值直接以红色底色显示出来。

3、数据透视表

数据透视表可以直观地统计出每个变量出现的次数，行标签是公司，以公司进行计数统计。

对于重复值的处理，就两个字：删除。

三、异常值

1、异常值的判断

对异常值的判断除了依靠统计学常识以外就是对业务的理解。如果某个类别变量出现的频率非常少，或者某数值型变量相对业务来说太异常的可以判断为异常值。对异常值的处理就直接删除好了。

2、实例

在本例中，我们对薪资下限升序排列，发现了一个薪资区间在1-1K的，但因为深圳的基本工资为2200元，所以对于薪资上限小于2K的值我们都判定为异常。

四、字段拆分

对于原始数据有些字段不是我们想象中格式，因此要对这些字段做一些计算和处理，计算这里就不细说了，用函数搞定即可，这里主要讲解一下字段拆分的操作。

对于原始字段里的地址一栏，我们想要将地市和区域分开，将一个字段分割成两个字段，这里介绍两种方式：分列和函数。

1、分列

之前讲到过分列的功能，数据——分列，观察数据发现，地市和区域之间以符号 “ · ” 区分，所以我们也用该符号进行分列的标志，可以得到地市和区域分开的数据。

2、文本函数

可以使用left、right以及find函数来实现字段分列的功能。观察发现，地市全部为两个字符，那么地市一栏我们就可以用left函数取前两个字符即可得到。

区域字段理想情况下应该用right函数取后3位字符，但观察发现，有的区域是三个字符，有的是两个字符，那就不能直接用right函数取后3位了，应该取的是总字符个数减3个字符（没明白的再好好琢磨一下），RIGHT(B2,LEN(B2)-3)。

3、实例

这里我们还要对薪资一栏进行处理，我们想要把原始字段里的区间变量转换成薪资下限和薪资上限，为什么要做这样一个处理呢？我们在学Excel使用技巧的时候发现，其实把几个字段合并起来是非常容易的，但想要把一个字段拆分成几个我们想要的字段其实是很困难的，有规律的还好我们用分列+公式也能解决，规律不明显的就没法处理了。所以在录入Excel表的时候，也建议小伙伴们本着最简化的原则去录入，一个单元格里能少放就不要多放，比如地址：深圳市福田区上梅林XX大厦，你就把它分成三个单元格录入最好，深圳市，福田区，上梅林XX大厦，这也是给统计的人以方便，人家想合并几秒就能合并，想拆分还得写上一大堆公式，还不一定能拆分出来否。

好，我们先来看看分列能不能完成，分割符号是-，最后分列完是BC列显示的，数据+单位的形式（13K），我们在做Excel数据表统计的时候数值通常是不带单位的，因为你带上个单位这个单元格的值就变成了文本形式，没法做数值统计，所以我们还要把K这个单位去掉，这很简单了，我们用LEFT(B2,LEN(B2)-1)公式，

这是先分列再公式，可能有人会觉得繁琐，接下来，我们直接上公式。=LEFT(A2,FIND("k",A2)-1)，高效，就看你对公式的掌握了。先find找k是第几个值，find后的结果是数到k，可能是3也可能是2，，然后left左取3-1（2-1）。

后台回复“数据处理”可获取本实例的数据，实操练习效果更佳哦。

关于数据处理这部分就告一段落，接下来在 用Excel进行数据分析系列 中会写一些分析的方法、技巧，感谢各位的持续关注。

任何疑问，欢迎加我个人微信号：data_cola 交流讨论

提供入门级数据分析的学习路线规划，分享从Excel到统计学的干货。数据分析是一项技能，希望人人都能分析数据。

如果你觉得有用，请点击右下角在看哟~

Excel数据处理（缺失值/重复值/异常值/拆分）

发送给作者