佩奇的数据分析学习之excel(三)

佩奇的数据分析学习之excel(三)

---

前言

文章内容来源于博主对B站UP主：”戴戴戴师兄“ 的学习笔记
戴戴戴师兄视频链接
练习数据获取来源：戴戴戴师兄
链接: https://pan.baidu.com/s/1uXVMupx4y1rw0amWjhyJgQ?pwd=1919

由于excel的功能多切杂乱，博客中仅学习一些数据分常用的功能
数据分析的目的：数据分析是指用适当的统计分析方法对收集来的大量数据进行分析，提取有用信息和形成结论而对数据加以详细研究和概括总结的过程。在实用中，数据分析可以帮助人们做出判断，以便采取适当行动。

所用的数据为外卖门店的一些信息
有以下字段：
日期；品牌ID ；品牌名称；门店ID；门店名称；城市；平台；平台i；平台门店名称；GMV；商家实收；门店曝光量；门店访问量；门店下单量；无效订单；有效订单；曝光人数；进店人数；下单人数；cpc（cpc指每产生一次点击所花费的成本）总费用；cpc曝光量；cpc访问量；商户补贴；平台补贴

---

一、excel是什么？

什么是excel，excel本质是一款电子表格软件，该软件是由一系列和行组成的，形成一个个网格，一个网格就是一个单元格，单元格可以存储文本，数字，公式等元素

二、什么是周报

上一期我们学习了常用的excel函数

经过前两期的学习，我们可以利用之前的知识来制作周报了

什么是周报呢
工作周报，简称周报，是每周一次的工作报告。相关概念有工作日报、工作月报、周例会、工作总结、工作计划等。
工作周报的内容包括工作计划、工作总结、上级回复等，适用于公司、部门、团队、社会团体、项目组等各种组织，是最常见的组织管理基本方法之一。

三、周报的制作

这次我们的目的就是利用源数据制作一份周报
我们打开大厂周报练习版

里面是我们这次制作周报所要计算的目标，大部分在之前的学习中已经学习过怎么使用函数计算了
我们做的是8月第二周的报表，所以数据时间我们就填写为8月第二周
2020年8月的第二周开始时间是2020-08-10
所以我们在日期下的第一个单元格内写上2020-08-10，下一行的单元格可以引用上一个单元格的内容直接加1得到日期

接下来我们来填写这一天是周几，按照日期一样的方法写出10号到16号再设置单元格格式

选择日期这一栏的星期类型，excel会将你的日期全部转化成星期几的格式，点击确定得到结果

同理我们可以将过程指标的日期和星期字段都写上
接下来我们要计算几个字段
结果指标：GMV,商家实收，到手率，有效订单，无效订单，客单价

过程指标：曝光人数，进店人数，进店转化率，下单人数，下单转化率，营销占比

我们接下来填写平台字段，目的是让他能够下拉，并且根据选择的不同，呈现不同平台的数据
我们点开菜单栏数据选项下的数据验证，创建一个数据验证
选择允许序列，下面的来源写上全部,美团,饿了么，就可以得到一个下拉列表

我们按照顺序先来算GMV，这里有两种情况，一种是平台等于全部的时候，一种是平台是饿了么或者美团的时候。
当平台是全部的时候我们只需要根据日期来求和，使用sumif函数
当平台是饿了么或者美团的时候需要根据日期和平台来求和，要使用sumifs函数。

所以函数应该为：

=IF($H$5=“全部”,SUMIF(‘拌客源数据1-8月’!A:A,‘大厂周报-练习版’!A13,‘拌客源数据1-8月’!J:J),SUMIFS(‘拌客源数据1-8月’!J:J,‘大厂周报-练习版’!A:A,‘大厂周报-练习版’!A13,‘拌客源数据1-8月’!H:H,‘大厂周报-练习版’!H$5))

下拉之后可以可以将剩下的几天也计算出来
商家实收也是和GMV一样的计算原理，自己动手试试吧

=IF($H$5="全部",SUMIF{(}^{\prime }拌客源数据1-8{月}^{\prime }!A:A{,}^{\prime }大厂周报-练习{版}^{\prime }!A13{,}^{\prime }拌客源数据1-8{月}^{\prime }!K:K),SUMIFS{(}^{\prime }拌客源数据1-8{月}^{\prime }!K:K{,}^{\prime }拌客源数据1-8{月}^{\prime }!A:A{,}^{\prime }大厂周报-练习{版}^{\prime }!A13{,}^{\prime }拌客源数据1-8{月}^{\prime }!H:H{,}^{\prime }大厂周报-练习{版}^{\prime }!$H$5))

我们还可以使用index和match进行优化
比如将GMV这列的取用函数变成
INDEX(‘拌客源数据1-8月’!$A:$X,0,MATCH($A$12{,}^{\prime }拌客源数据1-8{月}^{\prime }!$A$1:$X$1,0))
同理的我们把计算GMV函数中可替换的都用index和match替换

=IF($H$5="全部",SUMIF{(}^{\prime }大厂周报-完成{版}^{\prime }!,$A13,INDEX(‘拌客源数据1-8月’!$A:$X,0,MATCH(C$12{,}^{\prime }拌客源数据1-8{月}^{\prime }!$A$1:$X$1,0))),SUMIFS(INDEX{(}^{\prime }拌客源数据1-8{月}^{\prime }!$A:$X,0,MATCH(C$12{,}^{\prime }拌客源数据1-8{月}^{\prime }!$A$1:$X$1,0)),INDEX{(}^{\prime }拌客源数据1-8{月}^{\prime }!$A:$X,0,MATCH($A$12{,}^{\prime }拌客源数据1-8{月}^{\prime }!$A$1:$X$1,0)),$A13,INDEX(‘拌客源数据1-8月’!$A:$X,0,MATCH(“平台i”,‘拌客源数据1-8月’!$A$1:$X$1,0)),‘大厂周报-完成版’!H5))

以此为基础我们继续推导，有效订单和无效订单的求和函数怎么写
有效订单和无效订单都是源数据里有的字段，和GMV相似的求和条件，只是将列名改为有效订单

有效订单：
=IF($H$5="全部",SUMIF(INDEX{(}^{\prime }拌客源数据1-8{月}^{\prime }!$A:$X,0,MATCH($A$12{,}^{\prime }拌客源数据1-8{月}^{\prime }!$A$1:$X$1,0)),$A13,INDEX(‘拌客源数据1-8月’!$A:$X,0,MATCH(F$12{,}^{\prime }拌客源数据1-8{月}^{\prime }!$A$1:$X$1,0))),SUMIFS(INDEX{(}^{\prime }拌客源数据1-8{月}^{\prime }!$A:$X,0,MATCH(F$12{,}^{\prime }拌客源数据1-8{月}^{\prime }!$A$1:$X$1,0)),INDEX{(}^{\prime }拌客源数据1-8{月}^{\prime }!$A:$X,0,MATCH($A$12{,}^{\prime }拌客源数据1-8{月}^{\prime }!$A$1:$X$1,0)),$A13,INDEX(‘拌客源数据1-8月’!$A:$X,0,MATCH(“平台i”,‘拌客源数据1-8月’!$A$1:$X$1,0)),$H$5))

无效订单

=IF($H$5="全部",SUMIF(INDEX{(}^{\prime }拌客源数据1-8{月}^{\prime }!$A:$X,0,MATCH($A$12{,}^{\prime }拌客源数据1-8{月}^{\prime }!$A$1:$X$1,0)),$A13,INDEX(‘拌客源数据1-8月’!$A:$X,0,MATCH(G$12{,}^{\prime }拌客源数据1-8{月}^{\prime }!$A$1:$X$1,0))),SUMIFS(INDEX{(}^{\prime }拌客源数据1-8{月}^{\prime }!$A:$X,0,MATCH(G$12{,}^{\prime }拌客源数据1-8{月}^{\prime }!$A$1:$X$1,0)),INDEX{(}^{\prime }拌客源数据1-8{月}^{\prime }!$A:$X,0,MATCH($A$12{,}^{\prime }拌客源数据1-8{月}^{\prime }!$A$1:$X$1,0)),$A13,INDEX(‘拌客源数据1-8月’!$A:$X,0,MATCH(“平台i”,‘拌客源数据1-8月’!$A$1:$X$1,0)),$H$5))

曝光人数
=IF($H$5="全部",SUMIF(INDEX{(}^{\prime }拌客源数据1-8{月}^{\prime }!$A:$X,0,MATCH($A$12{,}^{\prime }拌客源数据1-8{月}^{\prime }!$A$1:$X$1,0)),$A13,INDEX(‘拌客源数据1-8月’!$A:$X,0,MATCH(C$24{,}^{\prime }拌客源数据1-8{月}^{\prime }!$A$1:$X$1,0))),SUMIFS(INDEX{(}^{\prime }拌客源数据1-8{月}^{\prime }!$A:$X,0,MATCH(C$24{,}^{\prime }拌客源数据1-8{月}^{\prime }!$A$1:$X$1,0)),INDEX{(}^{\prime }拌客源数据1-8{月}^{\prime }!$A:$X,0,MATCH($A$12{,}^{\prime }拌客源数据1-8{月}^{\prime }!$A$1:$X$1,0)),$A13,INDEX(‘拌客源数据1-8月’!$A:$X,0,MATCH(“平台i”,‘拌客源数据1-8月’!$A$1:$X$1,0)),$H$5))

进店人数
=IF($H$5="全部",SUMIF(INDEX{(}^{\prime }拌客源数据1-8{月}^{\prime }!$A:$X,0,MATCH($A$12{,}^{\prime }拌客源数据1-8{月}^{\prime }!$A$1:$X$1,0)),$A13,INDEX(‘拌客源数据1-8月’!$A:$X,0,MATCH(D$24{,}^{\prime }拌客源数据1-8{月}^{\prime }!$A$1:$X$1,0))),SUMIFS(INDEX{(}^{\prime }拌客源数据1-8{月}^{\prime }!$A:$X,0,MATCH(D$24{,}^{\prime }拌客源数据1-8{月}^{\prime }!$A$1:$X$1,0)),INDEX{(}^{\prime }拌客源数据1-8{月}^{\prime }!$A:$X,0,MATCH($A$12{,}^{\prime }拌客源数据1-8{月}^{\prime }!$A$1:$X$1,0)),$A13,INDEX(‘拌客源数据1-8月’!$A:$X,0,MATCH(“平台i”,‘拌客源数据1-8月’!$A$1:$X$1,0)),$H$5))

下单人数：
=IF($H$5="全部",SUMIF(INDEX{(}^{\prime }拌客源数据1-8{月}^{\prime }!$A:$X,0,MATCH($A$12{,}^{\prime }拌客源数据1-8{月}^{\prime }!$A$1:$X$1,0)),$A13,INDEX(‘拌客源数据1-8月’!$A:$X,0,MATCH(F$24{,}^{\prime }拌客源数据1-8{月}^{\prime }!$A$1:$X$1,0))),SUMIFS(INDEX{(}^{\prime }拌客源数据1-8{月}^{\prime }!$A:$X,0,MATCH(F$24{,}^{\prime }拌客源数据1-8{月}^{\prime }!$A$1:$X$1,0)),INDEX{(}^{\prime }拌客源数据1-8{月}^{\prime }!$A:$X,0,MATCH($A$12{,}^{\prime }拌客源数据1-8{月}^{\prime }!$A$1:$X$1,0)),$A13,INDEX(‘拌客源数据1-8月’!$A:$X,0,MATCH(“平台i”,‘拌客源数据1-8月’!$A$1:$X$1,0)),$H$5))

以上函数的计算都是一样的，只要会其中的原理写出一个就能拉到别的单元格

接下来计算到手率，到搜率=商家实收/GMV，客单价=GMV/有效订单，
进店转化率=进店人数/曝光人数，下单转化率=下单人数/进店人数
非常好写的函数大家自己写一下就可以
我们再来求营销占比，营销占比=cpc费用/GMV
cpc费用我们还没有求而GMV我们已经求了，所以现在的任务是求CPC费用，大致的求和方法和求GMV费用一样

=IF($H$5="全部",SUMIF(INDEX{(}^{\prime }拌客源数据1-8{月}^{\prime }!$A:$X,0,MATCH($A$12{,}^{\prime }拌客源数据1-8{月}^{\prime }!$A$1:$X$1,0)),$A13,INDEX(‘拌客源数据1-8月’!$A:$X,0,MATCH(“cpc总费用”,‘拌客源数据1-8月’!$A$1:$X$1,0))),SUMIFS(INDEX{(}^{\prime }拌客源数据1-8{月}^{\prime }!$A:$X,0,MATCH("cpc总费用"{,}^{\prime }拌客源数据1-8{月}^{\prime }!$A$1:$X$1,0)),INDEX{(}^{\prime }拌客源数据1-8{月}^{\prime }!$A:$X,0,MATCH($A$12{,}^{\prime }拌客源数据1-8{月}^{\prime }!$A$1:$X$1,0)),$A13,INDEX(‘拌客源数据1-8月’!$A:$X,0,MATCH(“平台i”,‘拌客源数据1-8月’!$A$1:$X$1,0)),$H$5))
最后在函数的末尾除上我们已经求过的GMV就可以得到营销占比了

之后我们再计算总和，使用sum函数就可以轻松求和，注意一下求和的关系即可
但营销占比的总和是比较难算的，因为我们还需要知道一周内CPC总费用的和，这是我们没有计算过的
我们需要把时间的判断条件从判断某一天变成判断一个范围的值

=IF($H$5="全部",SUMIFS(INDEX{(}^{\prime }拌客源数据1-8{月}^{\prime }!$A:$X,0,MATCH("cpc总费用"{,}^{\prime }拌客源数据1-8{月}^{\prime }!$A$1:$X$1,0)),INDEX{(}^{\prime }拌客源数据1-8{月}^{\prime }!$A:$X,0,MATCH($A$12{,}^{\prime }拌客源数据1-8{月}^{\prime }!$A$1:$XKaTeX parse error: Expected 'EOF', got '&' at position 11: 1,0)),">="&̲A$13,INDEX{(}^{\prime }拌客源数据1-8{月}^{\prime }!$A:$X,0,MATCH($A$12{,}^{\prime }拌客源数据1-8{月}^{\prime }!$A$1:$XKaTeX parse error: Expected 'EOF', got '&' at position 11: 1,0)),"<="&̲A$19),SUMIFS(INDEX{(}^{\prime }拌客源数据1-8{月}^{\prime }!$A:$X,0,MATCH("cpc总费用"{,}^{\prime }拌客源数据1-8{月}^{\prime }!$A$1:$X$1,0)),INDEX{(}^{\prime }拌客源数据1-8{月}^{\prime }!$A:$X,0,MATCH($A$12{,}^{\prime }拌客源数据1-8{月}^{\prime }!$A$1:$XKaTeX parse error: Expected 'EOF', got '&' at position 11: 1,0)),">="&̲A$13,INDEX{(}^{\prime }拌客源数据1-8{月}^{\prime }!$A:$X,0,MATCH($A$12{,}^{\prime }拌客源数据1-8{月}^{\prime }!$A$1:$XKaTeX parse error: Expected 'EOF', got '&' at position 11: 1,0)),"<="&̲A$19,INDEX{(}^{\prime }拌客源数据1-8{月}^{\prime }!$A:$X,0,MATCH("平台i"{,}^{\prime }拌客源数据1-8{月}^{\prime }!$A$1:$X$1,0)),$H$5))/$C20

我们计算了总和之后，就可以完成周累计的数据，调用相应的数据即可

下面是迷你图的制作，我们要在曝光人数等字段后面插入一个迷你图
选择我们要制作折线图的数据区域，选择菜单栏下的插入，再选择迷你图里的折线

这样就插入成功了，动手插入其他的折线图
重点的是接下来要算的周环比，周环比=本周数据/上周数据-1
本周的数据我们已经计算出来了，上周的数据应该如何计算呢，在上一节的内容里我们知道日期其实是个数字，想要计算上一周的时间我们直接减去7就可以了
具体的函数如下
=A9/IF($H$5="全部",SUMIFS(INDEX{(}^{\prime }拌客源数据1-8{月}^{\prime }!$A:$X,0,MATCH($A$8{,}^{\prime }拌客源数据1-8{月}^{\prime }!$A$1:$X$1,0)),INDEX{(}^{\prime }拌客源数据1-8{月}^{\prime }!$A:$X,0,MATCH("日期"{,}^{\prime }拌客源数据1-8{月}^{\prime }!$A$1:$XKaTeX parse error: Expected 'EOF', got '&' at position 11: 1,0)),">="&̲(A$13-7),INDEX{(}^{\prime }拌客源数据1-8{月}^{\prime }!$A:$X,0,MATCH("日期"{,}^{\prime }拌客源数据1-8{月}^{\prime }!$A$1:$XKaTeX parse error: Expected 'EOF', got '&' at position 11: 1,0)),"<="&̲(A$19-7)),SUMIFS(INDEX{(}^{\prime }拌客源数据1-8{月}^{\prime }!$A:$X,0,MATCH($A$8{,}^{\prime }拌客源数据1-8{月}^{\prime }!$A$1:$X$1,0)),INDEX{(}^{\prime }拌客源数据1-8{月}^{\prime }!$A:$X,0,MATCH("日期"{,}^{\prime }拌客源数据1-8{月}^{\prime }!$A$1:$XKaTeX parse error: Expected 'EOF', got '&' at position 11: 1,0)),">="&̲(A$13-7),INDEX{(}^{\prime }拌客源数据1-8{月}^{\prime }!$A:$X,0,MATCH("日期"{,}^{\prime }拌客源数据1-8{月}^{\prime }!$A$1:$XKaTeX parse error: Expected 'EOF', got '&' at position 11: 1,0)),"<="&̲(A$19-7),INDEX{(}^{\prime }拌客源数据1-8{月}^{\prime }!$A:$X,0,MATCH("平台i"{,}^{\prime }拌客源数据1-8{月}^{\prime }!$A$1:$X$1,0)),$H$5))-1
以此类推，我们用同意的方法计算出商家实收和到搜率的周环比

接下来要算我们的业务进度，业务进度=本月到目前为止的GMV的和/目标GMV
现在是什么时间我们已经知道了，月初的时间我们需要用DATE函数分别取日期字段下的单元格内的年和月，并将日的值设置为了来求
具体的函数如下
=IF($H$5="全部",SUMIFS(INDEX{(}^{\prime }拌客源数据1-8{月}^{\prime }!$A:$X,0,MATCH("GMV"{,}^{\prime }拌客源数据1-8{月}^{\prime }!$A$1:$X$1,0)),INDEX{(}^{\prime }拌客源数据1-8{月}^{\prime }!$A:$X,0,MATCH("日期"{,}^{\prime }拌客源数据1-8{月}^{\prime }!$A$1:$XKaTeX parse error: Expected 'EOF', got '&' at position 11: 1,0)),">="&̲DATE(YEAR(A$13),MONTH($A$13),1),INDEX{(}^{\prime }拌客源数据1-8{月}^{\prime }!$A:$X,0,MATCH("日期"{,}^{\prime }拌客源数据1-8{月}^{\prime }!$A$1:$XKaTeX parse error: Expected 'EOF', got '&' at position 11: 1,0)),"<="&̲A$19),SUMIFS(INDEX{(}^{\prime }拌客源数据1-8{月}^{\prime }!$A:$X,0,MATCH("GMV"{,}^{\prime }拌客源数据1-8{月}^{\prime }!$A$1:$X$1,0)),INDEX{(}^{\prime }拌客源数据1-8{月}^{\prime }!$A:$X,0,MATCH("日期"{,}^{\prime }拌客源数据1-8{月}^{\prime }!$A$1:$XKaTeX parse error: Expected 'EOF', got '&' at position 11: 1,0)),">="&̲DATE(YEAR(A$13),MONTH($A$13),1),INDEX{(}^{\prime }拌客源数据1-8{月}^{\prime }!$A:$X,0,MATCH("日期"{,}^{\prime }拌客源数据1-8{月}^{\prime }!$A$1:$XKaTeX parse error: Expected 'EOF', got '&' at position 11: 1,0)),"<="&̲A$19,INDEX{(}^{\prime }拌客源数据1-8{月}^{\prime }!$A:$X,0,MATCH("平台i"{,}^{\prime }拌客源数据1-8{月}^{\prime }!$A$1:$X$1,0)),$H$5))/$H$8

最后的阶段我们来进行美化
按照下图所示来给业务进度制作一个进度条

除此之外还可以新建别的规则，比如单元格内数据大于0颜色为绿色，小于0颜色为红色

还可以让他大于0时有向上的图标，小于0时是向下的图标

最后再做亿点点的美化得到最终结果

到此我们凭借这自己的双手制作了一张数据周报