周报内容分布:标题、小看板、结果指标、过程指标
周报的美化最后再做
目录
一、日期计算:日期+1,等于后一天日期。
二、所有数据都用引用,整个表的数据才会进行联动。
所有日期都引用第一个日期。
三、搭建结果指标框架
过程指标:与流量漏斗和转化率相关,店铺的承接能力。
影响进店转化率关键因素--店铺在美团等app上的评分情况,以及首图式样。
影响下单转化率关键因素--菜品、评价、满减、价格,对经营情况直观的体现。
四、制作数据验证(类似筛选器,本质是条件验证)
要让单元格变成一个下拉单元格(美团 饿了么 中间用英文逗号隔开)
五、把单元格放进函数,构建结果指标
逻辑:
(1)当平台为全部时候,不需要判断平台,只需要对应日期进行求和就好;
(2)当平台为美团/饿了么,需要同时判断日期和平台进行求和。
=IF(H5="全部",SUMIF('拌客源数据1-8月'!A:A,'大厂周报-练习版'!A13,'拌客源数据1-8月'!J:J),SUMIFS('拌客源数据1-8月'!J:J,'拌客源数据1-8月'!A:A,'大厂周报-练习版'!A13,'拌客源数据1-8月'!H:H,'大厂周报-练习版'!H5))
写好函数后,要改绝对引用和相对引用。
=IF($H$5="全部",SUMIF('拌客源数据1-8月'!$A:$A,'大厂周报-练习版'!$A13,'拌客源数据1-8月'!$J:$J),SUMIFS('拌客源数据1-8月'!$J:$J,'拌客源数据1-8月'!$A:$A,'大厂周报-练习版'!$A13,'拌客源数据1-8月'!$H:$H,'大厂周报-练习版'!$H$5))
------问题:上述函数被限定在当表头是GMV时,进行计算。如果表头是其他,如何自动引用?
-----解决:用index和match去识别表头所在位置,并返回数值。把上面函数的 平台列、日期列和GMV列全部替换掉。
(1)首先替换日期列
=INDEX('拌客源数据1-8月'!$A:$X,0,MATCH('大厂周报-练习版'!$A$12,'拌客源数据1-8月'!$1:$1,0))
(2)替换求和列
=INDEX('拌客源数据1-8月'!$A:$X,0,MATCH('大厂周报-练习版'!C$12,'拌客源数据1-8月'!$1:$1,0))
(3)替换平台列
=INDEX('拌客源数据1-8月'!$A:$X,0,MATCH("平台I",'拌客源数据1-8月'!$1:$1,0))
(4)替换上述函数
=IF($H$5="全部",SUMIF('拌客源数据1-8月'!$A:$A,'大厂周报-练习版'!$A13,'拌客源数据1-8月'!$J:$J),SUMIFS('拌客源数据1-8月'!$J:$J,'拌客源数据1-8月'!$A:$A,'大厂周报-练习版'!$A13,'拌客源数据1-8月'!$H:$H,'大厂周报-练习版'!$H$5))
找函数日期列:'拌客源数据1-8月'!$A:$A 和 '拌客源数据1-8月'!$A:$A
找求和列:sumif的和在最后,替换【'拌客源数据1-8月'!$J:$J】
sumifs的和在第一个,替换【'拌客源数据1-8月'!$J:$J】
找平台列:【'拌客源数据1-8月'!$H:$H】
替换上述函数后:
=IF($H$5="全部",SUMIF(INDEX('拌客源数据1-8月'!$A:$X,0,MATCH('大厂周报-练习版'!$A$12,'拌客源数据1-8月'!$A$1:$X$1,0)),'大厂周报-练习版'!$A13,INDEX('拌客源数据1-8月'!$A:$X,0,MATCH('大厂周报-练习版'!C$12,'拌客源数据1-8月'!$A$1:$X$1,0))),SUMIFS(INDEX('拌客源数据1-8月'!$A:$X,0,MATCH('大厂周报-练习版'!C$12,'拌客源数据1-8月'!$A$1:$X$1,0)),INDEX('拌客源数据1-8月'!$A:$X,0,MATCH('大厂周报-练习版'!$A$12,'拌客源数据1-8月'!$A$1:$X$1,0)),'大厂周报-练习版'!$A13,INDEX('拌客源数据1-8月'!$A:$X,0,MATCH("平台I",'拌客源数据1-8月'!$A$1:$X$1,0)),'大厂周报-练习版'!$H$5))
六、把单元格放进函数,构建过程指标
把列名修改,C12改成C24
=IF($H$5="全部",SUMIF(INDEX('拌客源数据1-8月'!$A:$X,0,MATCH('大厂周报-练习版'!$A$12,'拌客源数据1-8月'!$A$1:$X$1,0)),'大厂周报-练习版'!$A13,INDEX('拌客源数据1-8月'!$A:$X,0,MATCH('大厂周报-练习版'!C$24,'拌客源数据1-8月'!$A$1:$X$1,0))),SUMIFS(INDEX('拌客源数据1-8月'!$A:$X,0,MATCH('大厂周报-练习版'!C$24,'拌客源数据1-8月'!$A$1:$X$1,0)),INDEX('拌客源数据1-8月'!$A:$X,0,MATCH('大厂周报-练习版'!$A$12,'拌客源数据1-8月'!$A$1:$X$1,0)),'大厂周报-练习版'!$A13,INDEX('拌客源数据1-8月'!$A:$X,0,MATCH("平台I",'拌客源数据1-8月'!$A$1:$X$1,0)),'大厂周报-练习版'!$H$5))
数据分析师要有信心,既要相信可以搞定数据,又要相信可以搞出来。
上周cpc总费用如何算:
修改当天cpc计算,把只算一天cpc,改成大于等于本周第一天 +小于等于本周最后一天即可。
七、调整格式
7.1 调整小数位数
7.2 插入迷你图
选取数据-点击“插入迷你图”
7.3 到手率计算
7.4 业务目标
=IF(H5="全部",200000,IF(H5="美团",150000,50000))
7.5 业务进度
=截止目前的GMV/目标
本月第一天:=DATE(YEAR(A13),MONTH(A13),1)
上周GMV:
IF($H$5="全部",SUMIFS(INDEX('拌客源数据1-8月'!$A:$X,0,MATCH(C$12,'拌客源数据1-8月'!$A$1:$X$1,0)),INDEX('拌客源数据1-8月'!$A:$X,0,MATCH("日期",'拌客源数据1-8月'!$A$1:$X$1,0)),">="&($A$13-7),INDEX('拌客源数据1-8月'!$A:$X,0,MATCH("日期",'拌客源数据1-8月'!$A$1:$X$1,0)),"<="&($A$19-7)),SUMIFS(INDEX('拌客源数据1-8月'!$A:$X,0,MATCH(C$12,'拌客源数据1-8月'!$A$1:$X$1,0)),INDEX('拌客源数据1-8月'!$A:$X,0,MATCH("日期",'拌客源数据1-8月'!$A$1:$X$1,0)),">="&($A$13-7),INDEX('拌客源数据1-8月'!$A:$X,0,MATCH("日期",'拌客源数据1-8月'!$A$1:$X$1,0)),"<="&($A$19-7),INDEX('拌客源数据1-8月'!$A:$X,0,MATCH("平台i",'拌客源数据1-8月'!$A$1:$X$1,0)),$H$5)))
截止目前的GMV:
把上周的GMV时间进行修改
A13-7 改成本月第一天
A19-7 改成 今天,即表格中的A19
=IF($H$5="全部",SUMIFS(INDEX('拌客源数据1-8月'!$A:$X,0,MATCH(C$12,'拌客源数据1-8月'!$A$1:$X$1,0)),INDEX('拌客源数据1-8月'!$A:$X,0,MATCH("日期",'拌客源数据1-8月'!$A$1:$X$1,0)),">="&DATE(YEAR(A13),MONTH(A13),1),INDEX('拌客源数据1-8月'!$A:$X,0,MATCH("日期",'拌客源数据1-8月'!$A$1:$X$1,0)),"<="&$A$19),SUMIFS(INDEX('拌客源数据1-8月'!$A:$X,0,MATCH(C$12,'拌客源数据1-8月'!$A$1:$X$1,0)),INDEX('拌客源数据1-8月'!$A:$X,0,MATCH("日期",'拌客源数据1-8月'!$A$1:$X$1,0)),">="&DATE(YEAR(A13),MONTH(A13),1),INDEX('拌客源数据1-8月'!$A:$X,0,MATCH("日期",'拌客源数据1-8月'!$A$1:$X$1,0)),"<="&$A$19,INDEX('拌客源数据1-8月'!$A:$X,0,MATCH("平台i",'拌客源数据1-8月'!$A$1:$X$1,0)),$H$5))
7.6 表格美化
7.6.1 业务进度 加进度条:
开始-条件格式-新建规则-数据条
数据条结果展示:
结果展示:
7.6.2 表格中添加箭头
(1)修改数字字体颜色
开始--条件格式--新建规则
(2)小于0的数字加上箭头
开始--条件格式--新建规则
设置完之后点击格式刷即可。
如果规则建立之后还要修改的话,可以点击“开始--条件格式--管理规则”:
7.6.3 一周中GMV低于平均值的用下划线标注出来
开始--条件格式--新建规则
呈现结果:
极大增强报表的可视化程度!!!
7.6.4 其他细节美化
(1)去掉表格网格线
(2)文字美化等