如何使用EXCEL进行数据分析

2.数据集介绍

数据是某电商购买婴儿商品的相关信息，本数据集包含2个EXCEL文件：

表1购买商品：用户购买信息

表2婴儿信息：用户信息

3.字段说明

（1）表1购买商品

ser_id：用户编号，用户唯一编码

auction_id：商品编号，商品唯一编码

cat_id：商品小类编码（商品二级分类）

cat1：商品大类编码（商品一级分类）

property：商品属性（如尺寸、颜色、品牌等）

buy_mount：一次购买的数量

day：购买日期

（主键：ser_id+auction_Id+day）

（2）表2婴儿信息

user_id：用户编号，用户唯一编码

birthday：出生日期

gender：性别（0女性；1男性；2未知的性别）

（主键：user_id）

三、数据分析过程

数据分析一般步骤为：明确问题->选择子集->数据清洗->数据分析->数据可视化。

（一）明确问题

通过熟悉数据集字段，明确你想从数据集中得到答案的问题。参考如下：

1.用户年龄和性别分布情况。

2.以往商品类别的销售量排名。

3.比较每月的销售总额，观察波动规律。

4.获取该电商婴儿商品的发展趋势。

（二）选择子集

拿到原始数据，建议不要直接在原文件开展数据分析，而是复制到新文件再开展后面的工作。根据业务目标，对于无用的字段（列）使用隐藏操作。为方便后续分析，将列名修改为中文名称。

（三）数据清洗

业务部门提供的数据一般不规范，为了保证后续数据分析的正确性，必须做好数据清洗工作。

数据清洗一般针对重复值、缺省值、不规范值、异常值进行标准化处理。具体操作如下：

1.去除重复值

（1）与业务部门确定主键列。

（2）在菜单选项“数据”下点击“删除重复值”，选择“扩展选定区域”，点击“删除重复项”，此时出现一个默认“全选”的对话框，选择“取消全选”，选择列表中的主键列点击“确定”。EXCEL就会将主键的重复行删除。

2.填充缺省值

（1）选定主键列，查看EXCEL下方的计数值N，这个数值代表有N条有效记录。

（2）选择每一列的计数值，小于N的情况，都说明该列存在缺省值即空值。

（3）查找空值，并填充内容。

选定存在空值的列，通过“开始”下的“查找和选择”，点击定位条件，选择“空值”，点击“确定“，此时EXCEL选定该列的所有空值格。

输入单元格内容或公式，按下CTRL+ENTER，便可实现批量修改空值。

如果只有少量空值，手工修改也可以。

3.处理不规范值

不规范值是指一切不便于进行下一步分析的数据，比如不规范的日期格式，常见的还有一个字段包含了多个信息，需要从一个单元格的字符串中截取部分数据出来，形成一个新的独立的单元格。

以下就针对日期和数据截取做详细的处理说明：

(1）日期规范化

一般人员习惯使用右键->设置单元格格式->日期来统一日期格式，但实际效果并不如意。此时使用”分列“功能，会有意想不到的效果。

下图为分列过程图：

下图分别为日期原始数据、经设置单元格格式后的结果、经分列后的结果：

(2）数据截取

本案例的商品属性中包含了商品属性编号和属性值，通过冒号”:“和分号”;“分割。我们通过以下常用函数进行数据截取：

FIND(":",E2) 表示找到单元格E2中，":"的位置。如E2=“21458:76412435;1633959:77875535;”，FIND的结果就是6。EXCEL的字符串与一般程序语言不同，字符串的位置是从1数起。

LEFT(E2,FIND(":",E2)-1) 表示从左边开始，截取“FIND(":",E2)-1”长度。也就是截取E2“:”前的5个字符，即“21458”。

MID(E2,FIND(":",E2)+1,FIND(";",E2)-FIND(":",E2)-1) 表示从第一个参数"FIND(":",E2)+1" 的位置开始截取，截取长度为第二个参数"FIND(";",E2)-FIND(":",E2)-1"，也就是截取E2从“:“到”;“之间的字符，即”76412435“。

这里的难点在于如何定位要截取的位置，通过特殊的字符，推算计算公式，即可结合LEFT、RIGHT、MID、FIND等截取到合适数据。

4.异常值取舍

通过筛选和排序，根据业务经验判断数值是否异常，根据业务的需要，按修改、删除、保留三个方向处理。

比如通过出生日期的排序（降序），得到一个1984年出生的异常值，需要与业务部门确认处理方法。

（此处也可通过描述统计的中位数Tukey's test来识别异常值）

通过以上的步骤完成繁琐的数据清洗工作。

（四）数据分析

通过数据清洗，数据已经标准化，可以展开真正的数据分析了。EXCEL的数据分析主要依靠数据透视表功能。

数据透视表的工作原理：分组->统计->组合结果。

路径：插入->数据透视表

在数据透视表字段框中，选择合适的字段放入行中，将需要统计的字段放入计算位置，结合案例如下：

1.通过表2的婴儿出生日期和性别，得到用户年龄和性别分布情况。

（1）用户年龄分布

表2只给出婴儿的出生日期，可以通过日期推算年龄，这一步也可放在数据清洗步骤做。

因该表统计时间为2015年3月1日，通过公式=IF(B2<DATE(2015,3,1),(DATE(2015,3,1)-B2)/365,0)得到宝宝当时的年龄，通过VLOOKUP函数得到年龄组（这里应用了VLOOKUP的模糊匹配实现分组）：

（2）用户性别分布

2.通过表1商品销售信息中商品大类和商品小类，得到商品类别的销售量排名。

分析结果显示，商品大类的销售量集中在”28“、”50014815“、”50008168“；631个商品小类中，”50018831“的销售量占总销量的16%。

3.比较每月的销售总量，观察波动规律。

从每月的销售量可以看出，下半年的销售量差不多是上半年的两倍，特别是双十一所在的十一月份。

4.通过表1，获取该电商婴儿商品的发展趋势。

从近三年的销售量分析，该电商母婴商品的销售量持续上涨。

注意：2015年度只统计了1月和2月的前5天的销售量

（五）数据可视化

四、结论

通过数据集的数据分析，得到以下结论：

1、用户男女比例均衡，主要集中于1-3岁的年龄段；

2、商品大类”28“、”50014815“、”50008168“的销售量占总销售量的88%；商品小类”50018831“的销售量占总销量的16%（统计样本中有661种商品小类）。

3、根据每月的销售情况，可得出结果：下半年的销售成绩比上半年好，特别是11月份（双十一）。

4、根据近三年该电商母婴商品的销售量，可以看到该电商母婴商品的销售量是持续增长的。根据2015年1月份的销售量，保守估计2015年的销售总量至少将达到2014年的两倍。