戴师兄数据分析学习day03——周报制作

提要

本次周报制作旨在对之前的内容做一个总结和练习，所使用的Excel源数据由b站戴戴戴师兄提供

介绍

本次周报主要是对数据中本周以及上周的业务数据做一个可视化数据展示，主要有周累计、周环比、结果指标、过程指标几个大块；
可以通过切换不同平台，查看不同平台间的数据情况；也就是具体数据可以随着平台的切换而变化，实现数据的联动。

实现重点

1 平台的切换效果实现

选中单元格，通过添加数据验证来实现数据下拉选择的效果。

2 日期联动更改

只有一个日期是需要手动写的，其他日期都以不同方式引用该单元格，这样需要修改日期时，只需要修改一个日期数据就可以联动修改其他日期数据，能大大节省时间和精力。

3 显示星期

对日期数据所在单元格修改单元格格式即可

4 根据平台、日期、列标题统计数据

使用多层函数嵌套实现该功能

逻辑描述如下

if(平台=全部,sumifs(GMV,日期列,当前日期), sumifs(GMV,日期列,当前日期,平台列,当前选中平台))

实际实现如下

=IF($H$5="全部",SUMIFS(INDEX('拌客源数据1-8月'!$A:$X,0,MATCH(C$12,'拌客源数据1-8月'!$1:$1,0)),INDEX('拌客源数据1-8月'!$A:$X,0,MATCH($A$12,'拌客源数据1-8月'!$1:$1,0)),$A13),SUMIFS(INDEX('拌客源数据1-8月'!$A:$X,0,MATCH(C$12,'拌客源数据1-8月'!$1:$1,0)),INDEX('拌客源数据1-8月'!$A:$X,0,MATCH($A$12,'拌客源数据1-8月'!$1:$1,0)),$A13,INDEX('拌客源数据1-8月'!$A:$X,0,MATCH("平台i",'拌客源数据1-8月'!$1:$1,0)),$H$5))

5 过程指标部分介绍以及实现

营销占比需要嵌套多层函数得出

=IF($H$5="全部",SUMIFS(INDEX('拌客源数据1-8月'!$A:$X,0,MATCH("cpc总费用",'拌客源数据1-8月'!$1:$1,0)),INDEX('拌客源数据1-8月'!$A:$X,0,MATCH($A$24,'拌客源数据1-8月'!$1:$1,0)),$A25),SUMIFS(INDEX('拌客源数据1-8月'!$A:$X,0,MATCH("cpc总费用",'拌客源数据1-8月'!$1:$1,0)),INDEX('拌客源数据1-8月'!$A:$X,0,MATCH($A$24,'拌客源数据1-8月'!$1:$1,0)),$A25,INDEX('拌客源数据1-8月'!$A:$X,0,MATCH("平台i",'拌客源数据1-8月'!$1:$1,0)),$H$5))/C13

总计中的营销占比——需要统计一周内的cpc总费用，利用 “>=”&日期 进行日期判断

=IF($H$5="全部",SUMIFS(INDEX('拌客源数据1-8月'!$A:$X,0,MATCH("cpc总费用",'拌客源数据1-8月'!$1:$1,0)),INDEX('拌客源数据1-8月'!$A:$X,0,MATCH($A$24,'拌客源数据1-8月'!$1:$1,0)),">="&$A$25,INDEX('拌客源数据1-8月'!$A:$X,0,MATCH($A$24,'拌客源数据1-8月'!$1:$1,0)),"<="&$A$31),SUMIFS(INDEX('拌客源数据1-8月'!$A:$X,0,MATCH("cpc总费用",'拌客源数据1-8月'!$1:$1,0)),INDEX('拌客源数据1-8月'!$A:$X,0,MATCH($A$24,'拌客源数据1-8月'!$1:$1,0)),">="&$A$25,INDEX('拌客源数据1-8月'!$A:$X,0,MATCH($A$24,'拌客源数据1-8月'!$1:$1,0)),"<="&$A$31,INDEX('拌客源数据1-8月'!$A:$X,0,MATCH("平台i",'拌客源数据1-8月'!$1:$1,0)),$H$5))/C20

6 求和快捷键 【Alt + =】

7 迷你图

创建迷你图

更改迷你图样式

8 周环比

计算公式

到手率周环比实现

9 业务进度

业务进度=本月至今为止的GMV/业务目标

=IF($H$5="全部",SUMIFS(INDEX('拌客源数据1-8月'!$A:$X,0,MATCH("GMV",'拌客源数据1-8月'!$1:$1,0)),INDEX('拌客源数据1-8月'!$A:$X,0,MATCH($A$24,'拌客源数据1-8月'!$1:$1,0)),">="&(DATE(YEAR($A$25),MONTH($A$25),1)),INDEX('拌客源数据1-8月'!$A:$X,0,MATCH($A$24,'拌客源数据1-8月'!$1:$1,0)),"<="&($A$19)),SUMIFS(INDEX('拌客源数据1-8月'!$A:$X,0,MATCH("GMV",'拌客源数据1-8月'!$1:$1,0)),INDEX('拌客源数据1-8月'!$A:$X,0,MATCH($A$24,'拌客源数据1-8月'!$1:$1,0)),">="&(DATE(YEAR($A$25),MONTH($A$25),1)),INDEX('拌客源数据1-8月'!$A:$X,0,MATCH($A$24,'拌客源数据1-8月'!$1:$1,0)),"<="&($A$19),INDEX('拌客源数据1-8月'!$A:$X,0,MATCH("平台i",'拌客源数据1-8月'!$1:$1,0)),$H$5))/$H$8

10 美化图表

可以通过对单元格设置条件格式来美化单元格

i.进度条

ii.图标

iii.对符合公式的数据设置格式

作品概览

总结

这一次的周报制作充分使用到了上一次学习到的内容，尤其是使我对SUMIFS()、INDEX()、MATCH() 等常用函数的理解和使用更加深入，在Excel中函数的嵌套使用是一大重点，可以解决很多繁琐的、手动计算容易出错的工作；
同时了解到了很多Excel其他功能的使用，可以说是又开辟了一些Excel的知识版图。而且还知道了周报是如何制作的、应该包含哪些指标、这些指标代表的含义等等业务上的知识，可以说是获益匪浅。