Excel对每个打工人来说都应该不陌生,但你知道吗,数据分析师都脱离不开Excel。虽然现在机器学习满地走,Excel依旧是无可争议的第一工具。它是日常工作中最常用的工具,如果不考虑性能和数据量,它可以应付绝大部分的分析工作。所以,很多数据分析师的面试中也要考察Excel处理数据的技能。
那么开始用excel进行数据分析基础工作吧!
数据分析步骤:
明确问题 理解数据 数据清洗 数据分析或构建模型 数据可视化
1)明确问题 例如,数据分析师的招聘需求?薪酬范围?所在城市?可以通过搜集获得相关原始数据,数据的获得后面通过学习python可以进一步深入了解如何爬取数据。
2)理解数据 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处理,所以使用公式计算平均值、加总计算的时候要注意将字符串类型的数字转换为数字类型。
小tips:字符串类型的数据显示在方格的左边,而数字类型的数据显示在方格的右边!
【数据透视表】——可以起到分组汇总的作用,可以关联不同的excel表格。例如:
4)数据分析
- 首先思考需要解决什么业务问题?
比如对于如下这张表格:
我们可以思考:
1/在哪些城市找到数据分析师工作的机会比较大呢?
2/对于工作年限的需求特点?
运用数据透视表,把城市字段拖入【行】,工作年限拖入【列】,加总值选择计数项:城市,我们就可以获得按照城市加总的数据分析师职位总数量,也就是每个城市有多少数据分析师的岗位需求:可以看到排名顺序是北上深广,说明北京的数据分析岗位需求是最大的,总计有1060个岗位。
我们可以右键在总计一列选择【数据显示方式】为【列汇总的百分比】,这样我们可以获得各个城市岗位占总岗位的百分比,从而看出各个城市的岗位需求相对程度。同样我们也可以在总计一列选择【数据显示方式】为【行汇总的百分比】,这样反应的就是对工作年限的要求的百分比数。
3/研究平均薪酬?
我们可以使用excel里的描述统计功能!
在【输入区域】选中平均薪酬一列,我们就可以得到描述统计的输出结果。
同样我们可以利用数据透视表研究不同工作经验年限的平均薪酬:
看来数据分析的收入还是比较可以的,并且随着工作经验的增长有很大的提升。
- 日期数据如何处理?
我们经常会在工作中接触到日期数据,而日期数据往往会出现一些杂乱无章的格式:
首先,日期数据可以采用分列处理,选中日期列后,前两步骤都直接点击下一步:
但此处我们要选择列数据格式为【日期】,获得如下结果:
可以看到大部分格式都整齐的变为了日期类型,但仍有一些错误。我们再选中日期列,使用【开始】中的选择数据格式选择框,将格式选择为我们想要的格式,如短日期、长日期等,就能得到统一的结果了。
- vlookup函数
1/ VLOOKUP函数语法:
=VLOOKUP(查找值,查找区域,返回查找区域第N列,查找模式)
一般情况下我们都是使用精确查找。
但vlookup有个缺点,就是当查找区域存在重复值时,只会返回第一个数值。如何解决?
方法:把查找区域内容标识唯一化,设置一个辅助列,辅助列是唯一化的没有重复值的内容,故可以通过查找辅助列找到所需数值。
2/ 用vlookup函数分组:
如上图,我们需要填充第一个表里的“消费分组”,在第二个表中我们设定分组的阈值0、500、1000。然后对阈值定义,小于500元的是低消费,500-1000的是中消费,大于1000的是高消费,在表一里的消费分组中填写函数=VLOOKUP(B2,$E$1:$G$4,2,1),注意使用vlookup函数进行分组功能时我们应该选择近似查找。
5)数据可视化
通过上面的学习,我们总结一下目前的知识:
后面一步就是数据可视化的学习啦!好事多磨,我们下一篇文章再探讨如何可视化 ;-) 。
今日练习:
今天我们来分析在文章《数据分析基础:描述统计分析》中的case:
一、数据清洗
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。
总结
- 熟悉数据分析的步骤很重要,这其中穿插了数据分析的思路。
- 熟悉数据清洗的方法和操作,尤其是处理日期数据,注意细节。
- 熟悉常用函数的用法,学会在函数中嵌套函数以达到查找数据的目的,重视vlookup函数。
- 使用数据透视表分析数据。