一、为何选择EXCEL工具
90%以上的数据分析岗位任职要求都有具备EXCEL数据处理能力这一条。相对于SQL、PYTHON、R语言等工具,EXCEL有它显著的优点:第一、EXCEL为常用办公软件,无需额外特殊安装;第二、门槛低,易上手,无论是否有计算机背景,甚至是文科生,都能快熟掌握EXCEL的操作方法。但它也有突出的劣势,当数据量过大,使用EXCEL处理数据的效率明显比其他工具的低得多。在不考虑效率的前提下,EXCEL是数据分析工具中不错的大众选择。以下通过电商母婴购物商品案例,详细介绍如何通过EXCEL完成基本的数据分析操作。
二、数据集内容
1.数据来源
Baby Goods Info Data-数据集-阿里云天池tianchi.aliyun.com2.数据集介绍
数据是某电商购买婴儿商品的相关信息,本数据集包含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年的两倍。