太秀了!用 Excel 也能实现和 Python 数据分析一样的功能!
![太秀了!用Excel也能实现和Python数据分析一样的功能!](https://static001.geekbang.org/infoq/93/933bd8a5a7b4b0c1695ec9b74c852443.png)
作者:Cherich_sun
来源:公众号「杰哥的 IT 之旅」ID:Jake_Internet
这是一篇关于如何用 excel 做数据分析的案例。目的是帮助大家,在遇到小型数据样本时,快速利用 excel 做分析。所以本篇文章的重点是分析思路+数据处理+可视化的实现,因为数据来源于网络,所以不做深入解析。
需求说明
分析某电商企业销售趋势,找到影响销售增长的因素。同时分析不同市场产品销售状况,找到不同市场的销售差异。挖掘不同产品的销售状况,找到不同产品的销售差异。对用户群体进行分析,对企业用户的画像和价值进行挖掘。从上述分析中得出结论,并根据分析结果,提出自己对此企业未来发展的一些想法和建议。
数据说明
本项目数据为某电商平台全年每日订单详情数据和用户信息数据,包括两个数据表,销售订单表和用户信息表。
其中销售订单表为每个订单的情况明细,一个订单对应一次销售。这两个表的表结构分别为:
销售订单表结构:
![](https://static001.geekbang.org/infoq/27/2779ab56ebe04ba250a26885fe3100aa.png)
用户信息表结构:
![](https://static001.geekbang.org/infoq/4e/4ee828b4f1b58b2d5a981ef74b5850d5.png)
一、分析思路
本项目主要分为三个维度,分别是用户画像,针对用户群体进行分析,找出平台的主力军特点有哪些;商品维度上,找出商品在不同市场的销售差异等;用户价值维度上,主要根据用户的消费行为,挖掘出其内在的规律。分析的详细思路,如下:
![](https://static001.geekbang.org/infoq/f6/f603058a38bf7a42139600a2dc4a2753.png)
二、数据处理(Excel)
1、数据清洗
最终数据状态:"多一分则肥,少一分则瘦",那么常用的清洗方法主要有以下三种。
① 重复数据处理
函数法 COUNTIF()
重复标记 =COUNTIF(A:A,A2)
第二次重复标记 =COUNTIF(A$2:A2,A2)
两种结果对比如下 :
![](https://static001.geekbang.org/infoq/1d/1d71ce7654b5dcd596510c2c48dd36bc.png)
高级筛选法 选中将要处理的数据区域,点击菜单栏里【数据】——【排序和筛选】里选择【高级】,会弹出高级筛选。如下操作:
![](https://static001.geekbang.org/infoq/a4/a491b0fa8e89f66bcd8c2e8e313e39a5.png)
![](https://static001.geekbang.org/infoq/26/267d9751478b20967be2ca60b7e67677.png)
条件格式 选中将要处理的数据区域,点击菜单栏里【开始】——【条件格式】里选择【突出显示单元格规则】——【重复值】。如下操作:
![](https://static001.geekbang.org/infoq/23/2376dc9ba1a5f842ebd78506b3d283ca.png)
![](https://static001.geekbang.org/infoq/cb/cb179ec7abd50a6ca8a8bdb08631a6c3.png)
数据透视表 数据透视表一样能计算数据重复的频次。而且比 COUNTIF 更加简单易用,只需要拖动。首先,选中第一行标题数据左侧,按 Shift+Ctrl+End/下箭头,选中全部数据后——【插入】——【数据透视表】,如下:
![](https://static001.geekbang.org/infoq/77/77a7d5787162d5c40e0f9f828b5f26ad.png)
我们上面已经学会了各种重复值的处理,那么在实际业务中,通常会删除重复值。选中全部数据——点击——菜单栏里【数据】——【删除重复项】,如下:
![](https://static001.geekbang.org/infoq/1a/1ab4c3a2b04e78c51d43fcece73c101b.png)
② 缺失数据处理
一般可以接受的缺失值的标准是在 10%以下。通常的处理方式如下:
平均值填充;
统计模型计算出来的值;
将缺失值的记录删除掉;
保留,只在做相应的分析中做必要的排除
批量填充 如何把下面的表格的合并单元格拆分开,转化成规范的数据。
![](https://static001.geekbang.org/infoq/04/04824f4001500db719cb4ad31786ae7f.png)
选择要转换的区域——【开始】——【合并后居中】——即取消单元格合并——继续选中要转换的区域——按 Ctrl+G——弹出【定位】——【定位条件】,选择空值——确定——继续在 A3 单元格中输入"=",按上箭头,再按 Ctrl+Enter,自动填充完成。
![](https://static001.geekbang.org/infoq/94/944d6dd6d38cbe2528db31f83d6c8284.png)
![](https://static001.geekbang.org/infoq/80/8056f6aa2e317f3c6446eb9c62a38180.png)
![](https://static001.geekbang.org/infoq/36/36a32ba2a231e98abe22b57a3dd1c1ef.png)
注:批量去除公式:选中数据,粘贴为数值,这样会提升 excel 的整体运行效率。下面,我们需要利用批量填充,处理销售订单表中的产品名称字段,批量删除掉数值,只保留产品名。新建一列空白列,先输入几个正确的产品名称,按 Ctrl+E,快速智能填充。
![](https://static001.geekbang.org/infoq/69/69f6a38e7c1c8bec678f622c2e637c03.png)
查找和替换 快捷键分别是:Ctrl+F;Ctrl+H
③ 空格数据处理
直接替换空格 Ctrl + H
特殊空格,要用 trim()
![](https://static001.geekbang.org/infoq/de/de2e5d9ba4b81beed86118fc075e32f1.png)
2、数据合并
① 字段合并
CONCATENATE()
![](https://static001.geekbang.org/infoq/5d/5d4925708ead03591a73d4ef44873ef7.png)
&
![](https://static001.geekbang.org/infoq/35/356852986f483f1feb32cc5664b4698a.png)
DATE()
![](https://static001.geekbang.org/infoq/34/34b31ef2982e499f96245160f8135de4.png)
② 字段匹配
能够实现的是表里没有的数据,但是其他表有的,通过对应的共同关键字(数据类型必须相同)进行匹配。现在,我们将用户表和订单表进行合并,使得订单表的字段更加丰富,为接下来分析(消费的主力军特征)做准备。想要实现的结果,如下:
![](https://static001.geekbang.org/infoq/0c/0cb73e2bc1f42de54676604cd241fa1a.png)
实现方式 VLOOKUP,语法如下:
VLOOKUP(要查找的值,查找的范围,属于查找范围的第几列(序列号),模糊/精确查找) 通过上面的语法,我们能够成功的获取到性别这一列数据,但是还有几个字段,如果通过复制粘贴的形式,修改序列号的话,效率太慢了。我们有更高效的方式。如下:
![](https://static001.geekbang.org/infoq/db/dbf42d6f417dd4934a0105ad3f1c5fb9.png)
![](https://static001.geekbang.org/infoq/c6/c6c05434f2d39664a8de7c21d69af7ea.png)
如上,序列号的位置通过 COLUMN 函数定位列,记得对行加绝对引用 $。这样的话,直接向右,向下拖拽,就会自动补全字段内容。
3、数据抽取
① 字段拆分
分列 现在我们已经成功合并成了一个大表,接下来,要对付款时间进行分列,拆分成年,月形式,方便后面作可视化。首先选中【单价】列右键——【插入】——同样的方式插入三个空白列——复制一列【付款时间列】,再分别命名列名为年、月——选中【付款时间】列——【数据】——【分列】——【下一步】——【其他】输入"/"——【完成】
![](https://static001.geekbang.org/infoq/8d/8d49e8457964ae71a2dc9656912d18d6.png)
![](https://static001.geekbang.org/infoq/31/319496c055bdb78eec9552df7060c5ec.png)
![](https://static001.geekbang.org/infoq/41/41e1e4a15dff54e22e72cf2e2a39e923.png)
函数(截取字符串):LEFT();RIGHT();MID()
② 随机抽样
RAND():返回 0~1 之间的小数; 如果想返回 60~70 之间的,=INT(RAND()*10+60)
RANDBETWEEN():返回介于指定数字之间的随机数,=RANDBETWEEN(60,70)
4、数据计算
① 简单计算
![](https://static001.geekbang.org/infoq/4c/4c7a429691f58670106540df58f72e91.png)
② 函数计算
日期计算 求年龄函数实现方式:=DATEDIF(D2,TODAY(),"Y")
数据分组 项目的需求是根据年龄对用户打标签(小于 21 岁,标记为"00 后";大于 21 岁并且小于 31,标记为"90 后";大于 31 岁并且小于 41,标记为"80 后";大于 41 岁并且小于 51,标记为"70 后"),有下面两种实现方式。
方式一:
IF() IF(W2<21,"00 后",IF(AND(W2>21,W2<31),"90 后",IF(AND(W2>31,W2<41),"80 后","70 后")))
![](https://static001.geekbang.org/infoq/f3/f3c83db1b9ba3f72115683df147d9fdb.png)
方式二:
VLOOKUP() 这种方式我们仅需要设置阈值和显示标签,值得注意的是要 VLOOKUP 的第三个参数是模糊匹配。
![](https://static001.geekbang.org/infoq/45/45d7f439ff930b9ec7f11f79dd9bf06d.png)
5、数据转换
① 数据表行列转换
实现如下效果,选中要转换的数据——右键,复制——选择空白单元格——【开始】——【粘贴】——【选择性粘贴】——选中【转置】——完成
![](https://static001.geekbang.org/infoq/4d/4d1451de20d5aaecafc8456f1d40678e.png)
![](https://static001.geekbang.org/infoq/e5/e5f967fbd7da7343e9cdf7cbb3fadfb1.png)
② 二维表转为一维表
点击【文件】——【选项】——【自定义功能】——在【不在功能区中的命令】中找到【数据透视表和数据透视图向导】并选中——在右侧的【数据】选项卡下面添加【新建选项卡】并选中它——【添加】——确定,最终,在【数据】里出现了"数据透视表和数据透视图向导",如下:
![](https://static001.geekbang.org/infoq/bd/bdc23ba032545ac56c0c33c6e5aa2360.png)
![](https://static001.geekbang.org/infoq/44/4422633cf96cb6722c281bf7e3d53855.png)
点击【数据透视表和数据透视图向导】——选择【多重合并计算数据区域】——【创建单页字段】——下一步——选定将要操作的区域——下一步——选择【新工作表】——完成——双击【总计】的值——即实现二维表转为一维表,如下:
![](https://static001.geekbang.org/infoq/bf/bfaf58041e0cd824b5be9cfa70358fc7.png)
![](https://static001.geekbang.org/infoq/96/96b2ceefd2f47356e7492d27047d5639.png)
![](https://static001.geekbang.org/infoq/8a/8a4ee66ff30237859c7a9e9dd0a5bcc3.png)
三、可视化
可视化图形大多数比较简单,相信大家都能轻而易举的实现。那么,一些特殊的图形,因为也很重要。
1、用户画像
![](https://static001.geekbang.org/infoq/99/9978ad59b391c49dc77faa10ce0d6488.png)
图表说明:平台的主力军的特征主要是:女性用户;90 后人群单身人群‘’学历主要是中专、博硕;地域集中在天津地区。女生购买偏好饮料、日用品,男性购买偏好是饮料、海鲜产品。
2、产品优势
![](https://static001.geekbang.org/infoq/5f/5f1e2e331731bfaf2d7f755f315596d7.png)
图表说明:整体的销售额处于下降趋势,从 10 月份开始慢慢回升。根据二八法则,可以看出天津、南京、北京占总销售额的 43%,可以考虑作为重点投放市场。最受欢迎的品类是饮料、日用品、肉/家禽。
3、用户价值
![](https://static001.geekbang.org/infoq/6b/6bdfb010ea1e8465f3767429057dad8c.png)
![](https://static001.geekbang.org/infoq/1e/1e6606a50b6bb09c34aca78db56f2cd3.png)
![](https://static001.geekbang.org/infoq/2e/2e8b7ca9f15884f780ca880949a59462.png)
注:因为上面的数据集里用户量有限,做同期群后不明显。所以这里是一个新的用户数据集。
图表说明:通过 RFM 模型已经对用户做好分类,可以针对不同类型用户采取相应的运营策略;重点维护消费频率在 82~100 之间的用户。同期群分析,我们可以看出平台的新用户是逐渐递增的,但是留存率较低。
公众号:杰哥的 IT 之旅,后台回复:「20210226」,即可获取本文完整数据。
如果你觉得这篇文章对你有点用的话,就请为本文留个言,点个赞,或者转发一下,让更多的朋友看到,因为这将是我持续输出更多优质文章的最强动力!
![](https://static001.geekbang.org/infoq/01/019b65e5ec6661e8bbcaec704494797d.jpeg?x-oss-process=image/resize,p_80/auto-orient,1)
版权声明: 本文为 InfoQ 作者【JackTian】的原创文章。
原文链接:【http://xie.infoq.cn/article/f9ad29c38c9f9a0857c723cf3】。文章转载请联系作者。
评论 (1 条评论)