excel中如何运用新建格式规则比较两个表格中的数值_数据分析基础-用Excel就可以？！...

Excel对每个打工人来说都应该不陌生，但你知道吗，数据分析师都脱离不开Excel。虽然现在机器学习满地走，Excel依旧是无可争议的第一工具。它是日常工作中最常用的工具，如果不考虑性能和数据量，它可以应付绝大部分的分析工作。所以，很多数据分析师的面试中也要考察Excel处理数据的技能。

那么开始用excel进行数据分析基础工作吧！

数据分析步骤：

明确问题 理解数据 数据清洗 数据分析或构建模型 数据可视化

1）明确问题 例如，数据分析师的招聘需求？薪酬范围？所在城市？可以通过搜集获得相关原始数据，数据的获得后面通过学习python可以进一步深入了解如何爬取数据。

2）理解数据 excel表中有行号、列号，行号+列号可确定一个单元格。我们通过观察数据的字段，了解到数据的具体信息。数据的格式可以通过右键点击单元格获得，可以看到excel中有非常多种数据类型，包括货币、数值、日期、时间、文本等等。

excel数据格式

然而，这些格式总体来说可以归为三类：【字符串】【 数值】【 逻辑】。

常规格式：表示所有数据类型。

3）数据清洗 数据清洗是为了将数据变为业务所需要的数据，好为后面的数据分析作准备！

• 清洗数据的步骤：

清洗数据的步骤

1/选择子集：暂不需要的列可以隐藏起来，可以通过【格式】选择恢复隐藏。

2/列名重命名：根据需求修改列名。

3/删除重复项：使用【数据】选项卡中的【删除重复项】删除重复的数值。注意：需要首先全选表格数据，再选择目标要删除重复数据的列。

4/缺失值处理：选中一整列，该列最好具备唯一标示性，观察excel右下角的【计数】；为了检验其他列是否有数据缺失的情况，选中其他某列，观察excel右下角的【计数】，两列计数的差额就是被检查列缺失的数据个数。可以通过【查找和选择】定位空值的地方，然后填充缺失的数据，（可以用contr+enter实现一次性多格输入）。

5/一致化处理：

【分列】——运用分列，我们可以将一列中的数据分为2列，以方便后面数据处理。

【截取字符串的函数】——通过使用left 、right 、mid函数，我们可以截取字符串里面任意的内容：

截取字符串的函数

函数套函数，往往能更好实现我们想要的数据处理，如Find函数用来对原始数据中某个字符串进行定位，以确定其位置。为了定位k所在的地方，可以使用=LEFT(要查找的字符串，find("k"，要查找的字符串））定位我们想要截取的字符。

【常用函数】——excel函数功能中有平均值、求和等，可以直接在方格中编辑函数=f（x）来设定函数。

注意：在字符串长度不一的情况下，应该使用mid函数+find函数+len函数的方法。

【筛选】——使用筛选功能，按条件查找数据，可以查找出错误的数据，如#value！。筛选出错误的数据后，我们进一步查看出现错误的原因，然后修订，使得#value！的错误数据回归正常，从而完成一致性处理的过程。

在excel中，空白单元格会被当为0处理，所以使用公式计算平均值、加总计算的时候要注意将字符串类型的数字转换为数字类型。

字符串格式的数据，导致无法计算出正确结果而显示#DIV/0！，需要调整数据格式

小tips：字符串类型的数据显示在方格的左边，而数字类型的数据显示在方格的右边！

【数据透视表】——可以起到分组汇总的作用，可以关联不同的excel表格。例如：

数据透视表使用方法

使用数据透视表排序

4）数据分析

• 首先思考需要解决什么业务问题？

比如对于如下这张表格：

我们可以思考：

1/在哪些城市找到数据分析师工作的机会比较大呢？

2/对于工作年限的需求特点？

运用数据透视表，把城市字段拖入【行】，工作年限拖入【列】，加总值选择计数项：城市，我们就可以获得按照城市加总的数据分析师职位总数量，也就是每个城市有多少数据分析师的岗位需求：可以看到排名顺序是北上深广，说明北京的数据分析岗位需求是最大的，总计有1060个岗位。

右键点击总计，选择【数据显示方式】为【列汇总的百分比】输出结果

右键点击总计，选择【数据显示方式】为【行汇总的百分比】输出结果

我们可以右键在总计一列选择【数据显示方式】为【列汇总的百分比】，这样我们可以获得各个城市岗位占总岗位的百分比，从而看出各个城市的岗位需求相对程度。同样我们也可以在总计一列选择【数据显示方式】为【行汇总的百分比】，这样反应的就是对工作年限的要求的百分比数。

3/研究平均薪酬？

我们可以使用excel里的描述统计功能！

excel中的描述统计功能

在【输入区域】选中平均薪酬一列，我们就可以得到描述统计的输出结果。

同样我们可以利用数据透视表研究不同工作经验年限的平均薪酬：

看来数据分析的收入还是比较可以的，并且随着工作经验的增长有很大的提升。

• 日期数据如何处理？

我们经常会在工作中接触到日期数据，而日期数据往往会出现一些杂乱无章的格式：

例：处理乱的日期数据

首先，日期数据可以采用分列处理，选中日期列后，前两步骤都直接点击下一步：

但此处我们要选择列数据格式为【日期】，获得如下结果：

可以看到大部分格式都整齐的变为了日期类型，但仍有一些错误。我们再选中日期列，使用【开始】中的选择数据格式选择框，将格式选择为我们想要的格式，如短日期、长日期等，就能得到统一的结果了。

• vlookup函数

1/ VLOOKUP函数语法：

=VLOOKUP(查找值,查找区域,返回查找区域第N列,查找模式)

vlookup函数的使用方法

一般情况下我们都是使用精确查找。

但vlookup有个缺点，就是当查找区域存在重复值时，只会返回第一个数值。如何解决？

方法：把查找区域内容标识唯一化，设置一个辅助列，辅助列是唯一化的没有重复值的内容，故可以通过查找辅助列找到所需数值。

2/ 用vlookup函数分组：

如上图，我们需要填充第一个表里的“消费分组”，在第二个表中我们设定分组的阈值0、500、1000。然后对阈值定义，小于500元的是低消费，500-1000的是中消费，大于1000的是高消费，在表一里的消费分组中填写函数=VLOOKUP(B2,$E$1:$G$4,2,1),注意使用vlookup函数进行分组功能时我们应该选择近似查找。

5）数据可视化

通过上面的学习，我们总结一下目前的知识：

后面一步就是数据可视化的学习啦！好事多磨，我们下一篇文章再探讨如何可视化 ;-) 。

---

今日练习：

今天我们来分析在文章《数据分析基础：描述统计分析》中的case：

电商购物数据案例 表1-购物数据

电商购物数据案例 表2-婴儿信息

一、数据清洗

1）选择子集

观察表1、表2，将不需要的列隐藏。此步略。

2）列名重命名

两个数据集中，列名均为中文，并且含义清晰，因此我们不做任何处理。

3）删除重复值

表1:我们使用【删除重复项】功能会发现，用户id、商品编号都有上千个重复值，但考虑到实际场景，一个用户多次购买同种商品属于正常情况，所以无需删除。我们保留原表。

表2:未发现重复值。

4）缺失值处理

表1：用户id、商品编号、商品二级分类、商品一级分类、购买数量、购买时间都有29971个数值，而商品属性只有29827个数值，说明商品属性有数据是空值。处理：把表1的A-E列复制出来到新文件，❶使用【定位】功能定位空值 ❷在格子中输入vlookup公式，查找对应商品编号，返回商品属性，精确查找，再按control+enter。这样处理可以填充对应商品属性，如果确实没有同样已经填充过商品属性的商品就会显示为0。

表2: 有953个数据，没有缺失值。不作处理。

5）一致化处理

表1: 观察表1，我们发现目前的字段格式都是“常规”。❶把购买数量一列处理为数值格式❷把购买时间一列处理为日期格式。（我们发现设置日期格式时不能通过【开始】直接选择数据格式，此时就要通过【分列】来处理为日期格式，如下图：）

处理结果

表2: 同样的方法我们把日期数据进行处理为统一的日期格式：

我们看到“性别”一列是用0或1代表的性别，统计学中一般用1表示男，0表示女，但为了后续我们做可视化时方便，我们此处把这一列一致化处理显示为“男”或“女”。❶我们在后面新建一列“性别” ❷ 在该列输入=IF(C2=0,"女",IF(C2=1,"男","未知")) ❸把原来的性别列隐藏起来，我们得到结果：

而且可以发现表2信息不完全，有26个性别未知的婴儿用户。

6）数据排序

表1: 对购买数量进行降序排列，一次性购买单个商品最多的用户是2288344467，购买了10000件。

表2: ❶我们用vlookup函数把“购买日期”从表1中按照“用户id”贴过来，此处要注意，vlookup返回粘贴的数值是找到对应用户id的第一个数值，所以我们要在表2输入公式前首先把表1按照“购买时间”升序排序，再输入vlookup公式，这样粘贴到表2中的准确来说是首次购买的时间 ❷ 用year函数计算首次购买时候的年龄 ❸年龄出现负数，说明有的商品在出生前就已经购买了，我们可以把这类数据一致化为“出生前” ❹按照“购买年龄”排序，我们获得如下结果：

我们发现最大年龄的居然是28岁，显然不是婴儿年龄范围。

7）异常值处理

表1:无异常值需要处理

表2:在上一步我们发现最大年龄是28岁，显然不符合实际情况，故删除该行数据。

二、数据分析

表1是购买行为中产生的数据，表2是用户特征数据（婴儿用户的id、出生日期、性别）。相关字段为【用户id】,说明两个表可以通过用户id相结合分析。

1）研究总体时间内 最畅销/滞销的商品是什么？

运用我们学到的excel技能实现：❶使用【数据透视表】功能，在数据透视表“行”中拖入“商品一级分类”，计数项中拖入计数：购买数量 ❷降序排列，得到最畅销的商品排序信息：

可见最畅销的商品是一级分类为28的商品，总共出售28545件，占比达到37.44%。其次是50014815、50008168的商品。

总体时间内（2012年7月-2015年2月），最畅销的商品一级分类是28、50014815、50008168，并且该三大品类商品覆盖总体销量88%。商品一级分类是122650008的商品销量最低，应该具体考察下这些低销量的商品的利润情况以及其他情况，考虑产品下架或迭代。

排名前三的一级分类相对应的排名前三的二级分类销量情况：❶在数据透视表“行”中拖入“商品二级分类”❷在筛选器中选择“前10项”，再输入具体的你想显示的项目数量，此处我选择显示前3项，即一级分类对应的排名前3的二级分类。

可以看到二级分类相对应的排名前3的分类销量情况，28大类商品中排名前3的二级分类分别是50011993、50012788、50003700。但彼此差距不大（如果二级分类是品牌，说明该大类商品中没有形成垄断竞争，偏向是完全竞争市场。）50014815大类商品中前3名分别是50018831、50006520、50012564，其中50018831占据绝对优势，达到12657件（如果二级分类是品牌，说明该大类商品市场中已出现领头羊甚至是接近寡头垄断的品牌，即便认为单笔10000件的那条购物数据为异常数据剔除，50018831总量仍有2657件，较第二名50006520的610件有绝对的优势），50008168的前3名是50007016、50013636、50006602 ，彼此差距不大。

2）分时间段的销量情况？

❶在数据透视表里将“购买时间”拖入“行”，将“购买数量”拖入“值“，按照季度分类查看购买情况：

❷我们使用右键中的【值字段设置】，显示出购买数量的环比增长率和同比增长率：

❸我们使用右键中的【值字段设置】，显示出购买数量占总体购买量比率的情况，具体看看各个月的情况可以发现（2012、2015年数据非全年，故隐藏不作分析），2013年全年最高销量发生在12月，共售出4458件商品；2014年全年最高销量发生在11月，共售出13044件商品。

❹我们把月度数据描绘一个趋势图如下：

通过以上各个季度、月份销量、同比环比增长的趋势的数据分析，我们发现每年年初（1月、2月）的销量往往都是处于全年低位的，而从1-4季度销量呈上升趋势，从1季度-4季度，销量呈现一个增长状态，下半年的两个季度所占较大的比重。2013年最高销量发生在12月，2014年发生在 11月， 可能的原因在于下半年的各种促销活动较多，如双11、双12、圣诞节，而上半年活动就相对比较少了。2014年全年度较2013年全年度销量总量增长85.16%。要实现更好的销售，我们可以把宣介力度、促销活动放在下半年。

我们进一步看看每个月的数据，月均环比增长率的平均值为24%，最大值是2014年11月出现的292.89% ，该月同比2013年增长率高达413.95%。进一步查看2014年11月的销量情况：

可见2014年11月出现单笔大额订单，客户2288344467一次性购买了10000件50014815，2014年全年度较2013年全年度销量总量增长19428件，增幅85.16%，如果认为该笔订单是异常的订单，剔除该笔订单，则2014年全年度较2013年全年度销量总量增长9428件，增幅41.33%。

3）复购率最高/最低的商品？同一个客户重复购买同一个商品最多次的是复购率最高的商品，从而可以分析出复购率最高/最低的商品是什么品类，有什么特点。

❶我们先对【购买数量】字段做一个描述统计，发现购买数量的众数为1，有26335次的购买记录只购买了1件商品，占比达到86.8%。

❷我们用数据透视表，“行”拖入用户ID；“值”拖入用户ID、选择计数项；“列”拖入商品一级分类：

整体复购率很低，最高频次仅为4次，是用户814316568购买的大类为28的商品，经查询，该用户2013年3月-2013年10月购买了4次。此前我们已经知道有26335次的购买记录只购买了1件商品，占比达到86.8%，说明整体而言用户的重复购买欲望是很少的，如果希望增加销量，应该从打包一次性销售更多件商品的思路着手。

4）研究不同年龄段的用户（婴幼儿）购买行为特点？这需要结合两个表的数据，可以按照年龄给客户分组，再筛选出按照组别最畅销/滞销的商品总售出数量和商品分类、特性。可以看到不同年龄段、不同性别用户对商品的偏好。

❶首先根据之前我们数据清洗时候的步骤，使用vlookup把表1中的购买时间粘贴到表2。我们看一下我们表2现有数据中用户的年龄分布和性别分布：

婴儿用户数据年龄分布

表2有购买行为的用户中，主要年龄为0-1岁，两者相加达到52%；对购买年龄做统计描述，众数为1岁（253个），峰度达到24，用户年龄集中度极高，2岁-3岁的用户达到26%，3岁以上的用户随着年龄增长逐渐减少。有5%用户属于“未雨绸缪”型，在出生前（家长）就已经为其购买。

用户性别分布

表2有购买行为的用户中，女性占比51%，男性用户为46%，有3%用户性别不详，总体而言男女比例差异不大。

❷并表——我们把需要研究的字段按照vlookup粘贴到表2。注意：由于有的用户是多次购买，所以要粘贴“购买数量”时，我们先应该在表1中使用数据透视表，按照用户ID加总每位客户的购买总数，再根据这个数据透视表把“购买数量”粘贴到表2。

❸我们用vlookup的【分组】功能，将表2的婴幼儿用户分成不同年龄组，先在表2中设定好阈值：

然后新增列，命名为“年龄区间”，该段使用vlookup模糊匹配，匹配后的结果如下图所示：

❹如此我们可以使用数据透视表分析婴幼儿的购买数据了。

各个年龄段的购买总量（可以行、列均进行排序，使用值字段设置的排序功能）：

按照年龄区间的购买总量分布

可以看到0-1岁的用户购买总量为854件，占总购买量的55%，0-1岁是我们购买的主力军。同时2-3岁的用户购买量占25%，购买力也很强，这与我们用户年龄分布比例也相一致。

按照大类商品的购买总量分布

可以看到婴幼儿用户中，最受欢迎的大类商品是50014815，占总购买量的32%，其次是50008168（29%），再次是28。

但是在各个龄段中，最受欢迎商品略有不同：

使用【数据条】功能显示购买量

0-1岁，最受欢迎的大类商品是50014815；2-3岁包括以上年龄，最受欢迎的大类商品是50008168。

5）不同性别婴幼儿的购买偏好？

为了比较两性不同的购买特征，现在我们使用数据透视表，加入性别字段，会是什么结果呢？

男女性存在购买商品的较大差异。一、我们发现女性购买商品总量是934件，而男性仅有559件，购买量仅为女性购买量的59.9%，可见女性是购买的主力军。二、男女性对商品的偏好不同，女性中，最受欢迎的大类商品是50014815、其次为50008168、28，而男性中，最受欢迎的大类商品是50008168、其次为28、50008168。

总结

1. 熟悉数据分析的步骤很重要，这其中穿插了数据分析的思路。
2. 熟悉数据清洗的方法和操作，尤其是处理日期数据，注意细节。
3. 熟悉常用函数的用法，学会在函数中嵌套函数以达到查找数据的目的，重视vlookup函数。
4. 使用数据透视表分析数据。