手把手教你Excel数据处理！

公众号后台回复“图书“，了解更多号主新书内容

作者：胖里

来源：胖里的日常

今天还是数据分析的学习，如果你觉得文章太长太没意思，欢迎拉到底部直接看大纲总结，一秒学会（学不会我也不负责，让你不看全文）。

数据分析指根据某一分析目的，采用适当的工具及方法对数据进行处理和分析，从中提取有价值的信息，形成有效结论的过程。流程大致如下。

可见分析固然重要，但是没数据何来分析可言？即使有了数据，很多时候数据可能并不那么称心如意，比如含有很多冗余数据、或缺失不少数据，此时就需要先对数据进行处理，否则无法进行后续分析。

最近在读《谁说菜鸟不会数据分析》，此书应该是数据分析入门基础书，我看得挺基础的，学得也挺仔细，今天借着所学，来聊聊数据分析的前篇 -- 数据处理。

数据处理指根据数据分析目的，对收集到的数据进行处理、加工，使数据具备准确性、一致性和有效性，形成适合用于后续分析的数据格式。常用的数据处理方法主要有：数据清洗、数据合并、数据拆分、数据计算、数据转换。

一、数据清洗

如之前所言，拿到的数据表中会存在一些数据重复、数据缺失的情况，此时就需要进行数据清洗，日常中常见的数据清洗方法主要有：重复数据处理、缺失数据处理、空格数据处理。

1. 重复数据处理

对于表中的重复数据，可采用函数法、高级筛选法、条件格式法、数据透视表、删除重复项等方法进行查看和删除，这些方法有的只能用于查看重复值但不方便删除，有的能直接删除但不体现重复值是什么，还有的既能体现重复值还能实现重复值的删除。下面就这些方法分别进行介绍。

a. 函数法（查看+删除）

采用COUNTIF(计数范围，计数条件)函数进行计数，通过数据来表示重复与否。其中计数条件可以是数字、表达式或文本，甚至可以是通配符。

如图，共有9条记录，使用COUNTIF()函数，以A列作为计数范围，计当前记录的数量，记为“重复标记1”，通过此标记可以得到哪些是重复记录及其重复次数。“重复标记2”是以当前记录及其之前记录为计数范围，记录当前记录是否重复及重复次数，通过此标记可以得到大于1的记录都是重复记录，等于1的记录即为去重之后的记录。

因此使用COUNTIF()函数，不仅能进行重复记录的查看，也能很容易获取到去重后的数据。

b. 高级筛选法（删除）

高级筛选法是指直接使用Excel菜单中自带的高级筛选功能进行重复值去除，操作过程很简单，如下图所示，直接“选择不重复的记录”即可对重复值进行去除，得到不重复的记录集合，因此此法只适用于重复记录的删除，并不适合具体重复记录的查看。

删除重复值后的结果如下。

c. 条件格式法（查看）

所谓的条件格式法就是直接采用Excel菜单中自带的条件格式进行重复值的显示，此法只适合凸显出重复值。

操作后结果如下，可以看出效果跟函数法的重复标记1大致相同，但感觉不如函数法，无法很好地看出重复值及重复次数。

d. 数据透视表法（查看+删除）

此法是利用数据透视表实现重复数据的查看和删除的，在菜单栏中选择插入数据透视表，可以放置在新工作表，也可以指定位置放在现有工作表中。

将姓名字段拖拽至行和值处，即可实现数据记录及其重复次数的展示，计数项中大于1的即为重复数据，行标签中的记录即为去重后的数据记录。这种方法可同时实现重复记录重复次数的统计和数据去重。

e. 删除重复项（删除）

如果你并不关注某些记录具体重复了多少次，只想知道有多少记录重复了，或者只想直截了当地获得去重后的数据，那直接点个“删除重复项”就OK了，它会提示你删除了多少重复值，剩了多少唯一值。

2. 缺失数据处理

对于表中的缺失数据，一般可采取下列方法进行缺失值填充，包括：使用样本统计量的值进行填充；使用模型计算的值进行填充；直接将包括缺失值的记录删除；忽略数据缺失，不作处理、之后需要进行相应分析时再进行处理。

提及缺失数据的填充，就不得不提查找和替换了，这两个是大家日常常用的功能，也都有其对应的快捷键：“Ctrl+F”，“Ctrl+H”，此处不多余讲了。

必须要提的是在书中学到了一个巨好用的批量填充方式，举例说明。

胖里有个个人品牌，主要生产牛仔裤、棉服、毛衣，在不同的电商平台开店，12月不同商品在不同平台销量情况如图所示。

现在想利用这些数据进行某些分析，表中存在合并的单元格，需要进行单元格拆分，处理为合格的一维表。

首先选定A2:A13，点击菜单栏的“合并后居中”，取消单元格合并，得到如下结果。

这下名称列有很多空值待填充，可以选定A2:A13，快捷键“F5”开启“定位”，“定位条件：选定“空值”，即可定位所有空值，然后键盘先后输入“=”、“↑”，“Ctrl+Enter”，即可获得批量填充后的结果。这个操作我是第一次接触，许是孤陋寡闻了，但是强烈推荐！以后批量填充就可以直接用这种方法，特别是在数据量非常大的时候！

3. 空格数据处理

对于空格数据的处理，一般可以直接使用查找替换，将空格去除，但可能遇到需要保留文本中间空格的情况，比如“Jack Lee”，如果直接用查找替换，势必会将中间的空格也去掉，此时就可以选择TRIM(text)函数进行空格的去除，此函数除了单词间的单个空格外，会清除其他所有空格。也就意味着如果单词间有两个空格，使用此函数后，单词间也便只剩一个空格了。

二、数据合并

数据合并主要包括以下两种情况：字段合并和字段匹配。

其一是表中一些数据可能分属不同字段，而分析时需要这些字段的某种组合，比如年、月、日分属不同字段，分析时需要年-月-日这一字段的信息，此时就需要进行数据合并，此处称为字段合并。

其二是数据分别储存在不同表中，需要进行数据合并，也就是SQL中类似join的操作，此处称为字段匹配。

1. 字段合并

关于字段合并，可以理解为拼接，可以采用CONCAT()函数进行字段的连接，函数参数为需要进行拼接的文本项，如果其中涉及到字符（串），需要使用英文的双引号，如CONCAT(A1,",",B1)。除了CONCAT()函数，当需要拼接的字段较少时也可以使用&连接符进行拼接，如=A1&","&B1，效果与CONCAT()函数一致。

2. 字段匹配

提到不同表中字段的匹配，可以采用VLOOKUP()函数进行字段匹配，举例说明。

班主任有一张班级学生信息表，其中包括学生学号、姓名、性别等字段，语文老师有一张学生语文成绩表，其中包括学生学号、姓名、语文成绩（各科老师都有各科的学生成绩表），现在班主任找各科老师要来各科成绩表，想对每个人各科成绩进行整理汇总（以语文成绩为例）。

此时可以使用VLOOKUP()函数进行按列查找,VLOOKUP()函数参数一指要查找的值，参数二指查找范围，参数三指返回查找范围的第几列，参数四指匹配方式，还想了解更多的可以自行百度。

当然，我举得例子过于简单，直接copy过来也百分百匹配，此处举例只为说明此函数用于字段匹配的应用。

三、数据拆分

有数据合并就不免存在数据拆分，数据拆分也叫数据抽取。比如某字段存储着用户的身份证信息，可以通过拆分得到用户的省份、出生日期、性别等信息。再比如某表中有上万条数据，而分析时可能需要随机抽取某些数据作为样本进行分析。

按以上所述，数据拆分可分为字段拆分和随机抽样。

1. 字段拆分

如果想对某一字段进行拆分操作，首先可以选择菜单栏--数据--分列，利用分列功能按照想要的形式进行划分，下图附两张操作过程图，由于是自主分列，所以选择“固定宽度”，手动进行列的划分，得到想要的数据。

其次也可以采用LEFT()、RIGHT()、MID()函数进行某一字段的划分，其实也就是实现文本的提取，前两个函数有两个参数，最后一个函数有三个参数，具体用法可以直接在Excel中操作试试，也可自行百度。

2. 随机抽样

随机抽样是指抽取部分记录，书中介绍的是利用随机函数+VLOOKUP函数进行随机抽样。指先使用RAND()或RANDBETWEEN()函数对记录序号进行随机生成，然后通过VLOOKUP()函数进行数据匹配。

注意，在生成随机序号时可能存在重复的序号，可以先对序号进行去重，得到所需要数量且不重复的随机序号后再进行数据匹配。

四、数据计算

数据计算包括简单计算和复杂计算。

简单计算指一些字段或字段间的加减乘除，直接使用+-*/即可计算得到。

复杂计算指一些需要利用函数进行的计算，比如简单的求和函数SUM()，求均值函数AVERAGE()，用于日期间隔求取的DATEDIF()函数等。还要学会利用类似IF()这种判断函数进行一些数据分组打标。书中还介绍了使用VLOOKUP()函数进行数据分组的操作，感兴趣的可以读读。

五、数据转换

数据转换主要包括行列转换、数据类型转换、二维表转一维表。

1. 行列转换

行列转换简单些，可以直接选择需要转换的数据范围，复制，选择性粘贴，转置，即可完成行列转换。

2. 数据类型转换

数据类型的转换基本涉及数值转文本，文本转数值，数值型的日期转日期。这其中可以通过如VALUE()、TEXT()函数进行数值和文本的转换，也可以通过之前介绍的菜单栏中的分列，在分列过程中通过列类型的选择进行数据类型的转换（虽选择分列，但实际还是当前列）。

3. 二维表转一维表

有如下左边一张二维表，想转为右边一维表。

不同的工具有不同的转化方法，此处介绍的是Excel下的转化方式，由于我自己用的是mac，所以不能使用书上介绍的Windows版本的转换方法（想学的自己看书，哈哈哈），这边介绍下ios版本的转化方法，主要借助于OFFSET()函数。

OFFSET()函数是一个引用函数，可以引用某一个单元格或者区域，其参数包括参考系、上下偏移行数、左右偏移列数，要返回的引用区域的行数，要返回的引用区域的列数。

上图给出了每列借助OFFSET()、ROW()、INT()、MOD()等函数进行二维表转一维表的操作示例，每个字段的第一行数据可以通过上述公式求得，下拉即可得到所有的值。

好啦，以上就是常用数据处理方法的介绍，个人感觉还是比较实用的，感兴趣的同学也可以自己去读一读书。

奉上大纲总结。

◆ ◆ ◆  ◆ ◆麟哥新书已经在当当上架了，我写了本书：《拿下Offer-数据分析师求职面试指南》，目前当当正在举行活动，大家可以用相当于原价5折的预购价格购买，还是非常划算的：

数据森麟公众号的交流群已经建立，许多小伙伴已经加入其中，感谢大家的支持。大家可以在群里交流关于数据分析&数据挖掘的相关内容，还没有加入的小伙伴可以扫描下方管理员二维码，进群前一定要关注公众号奥，关注后让管理员帮忙拉进群，期待大家的加入。
管理员二维码：
猜你喜欢
● 卧槽！原来爬取B站弹幕这么简单● 厉害了！麟哥新书登顶京东销量排行榜！● 笑死人不偿命的知乎沙雕问题排行榜
● 用Python扒出B站那些“惊为天人”的阿婆主！● 你相信逛B站也能学编程吗