提示:文章写完后,目录可以自动生成,如何生成可参考右边的帮助文档
文章目录
概述
在掌握excel的基础前提下,学会部分函数,根据附件数据运用并实操完成一份大厂周报
一、学习重要函数
1.条件判函数if
功能:IF函数判断是否满足某个条件,如果满足返回一个值,如果不满足则返回另一个值。
语法:=IF(条件判断, 结果为真返回值, 结果为假返回值)1.多条件求和函数sumifs:
功能:对一组给定条件的指定单元格求和。
语法:=SUMIFS(求和范围,条件1范围,条件1,条件2范围,条件2……条件N范围,条件N)。
2.多条件求和函数sumifs:
功能:对一组给定条件的指定单元格求和。
语法:=SUMIFS(求和范围,条件1范围,条件1,条件2范围,条件2……条件N范围,条件N)。
3.函数match:
功能:在单元格区域中搜索指定项,然后返回该项在单元格区域中的相对位置。
语法:MATCH(lookup_value, lookup_array, [match_type])。
二、搭建周报
1.观察处理源数据并录入部分指标数据
用如下部分数据作为(示例):
1.1观察数据的列标题
1.2按周报所需指标建立框架
1.3部分指标数据处理后录入
根据框架,将数据指标按平台区分为:全部,美团,饿了么,我可以用数据验证做到这一步。
gmv我们可以初步构思为=if(平台=全部,sumifs(gmv列,日期列,日期),sumifs(gmv列,日期列,日期,平台列,平台))
=IF('大厂周报-练习版'!H5="全部",SUMIFS('拌客源数据1-8月'!J:J,'拌客源数据1-8月'!A:A,$A13),SUMIFS('拌客源数据1-8月'!J:J,'拌客源数据1-8月'!A:A,$A13,'拌客源数据1-8月'!G:G,$H$5))
这样我们可以按照这个步骤逐步录入我们所需指标的数据。
但是这样在不同的指标下需要逐个的录入,在处理多指标周报时这样过于繁琐,接下来我们可以用到index函数及match函数简化这一操作。
> =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))
这样我们按照将可替换的gmv列,时期列,平台列用index函数与match函数表示,这样随着指标的变化录入的数据自动与之对应的变动。
得到如下(示例)
2.计算并录入余下部分数据
2.1数据公式:
经以上处理后,部分指标因不存在于源数据列标题中无法被正常录入,因此要对它们用特定的公式计算并录入。
-到手率=商家实收/GMV
-客单价=GMV/有效订单
-进店转化率=进店人数/曝光人数
-下单转化率=下单人数/进店人数
-营销占比=cpc总费用/GMV
2.2处理数据并录入
根据2.1公式,根据所求指标数据能够得出除营销占比以外,因为cpc总费用指标不在制作的周报里,我们额外对其进行处理
=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("cpc总费用",'拌客源数据1-8月'!$A$1:$X$1,0))),SUMIFS(INDEX('拌客源数据1-8月'!$A:$X,0,MATCH("cpc总费用",'拌客源数据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))/$C13
我们额外处理总计后得到如下示例
三.周报的可视化
1.标题和小看板初构
将周累计与周环比按以下指标数据突显
2.小看板数据处理与可视化
根据周环比=本周数据/上周数据-1
以曝光人数周环比做示范:
=A9/IF($H$5="全部",SUMIFS(INDEX('拌客源数据1-8月'!$A:$X,0,MATCH($A$8,'拌客源数据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($A$8,'拌客源数据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))-1
业务进度:
=IF($H$5="全部",SUMIFS(INDEX('拌客源数据1-8月'!$A:$X,0,MATCH("GMV",'拌客源数据1-8月'!$A$1:$X$1,0)),INDEX('拌客源数据1-8月'!$A:$X,0,MATCH("日期",'拌客源数据1-8月'!$A$1:$X$1,0)),">="&DATE(YEAR($A$13),MONTH($A$13),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("GMV",'拌客源数据1-8月'!$A$1:$X$1,0)),INDEX('拌客源数据1-8月'!$A:$X,0,MATCH("日期",'拌客源数据1-8月'!$A$1:$X$1,0)),">="&DATE(YEAR($A$13),MONTH($A$13),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))/$H$8
如法炮制得到其他指标的周环比后,将周累计指标数据用迷你折线图可视化后得到如下:
3.加强可视化并美化周报
-将过程指标表与结果指标表数据加上条件格式,将gmv小于平均值的数据行的指标数据加上下划线
-给周环比加上条件格式可视化
-将业务进度加上条件格式渐变填充可视化
如下:
-最后对边框进行处理并修改一点细节得到最终的周报:
总结
以上就是《数分1:Excel的学习与运用,实操完成一份大厂周报》,本文仅仅简单介绍了excel的使用,及大厂周报的实操制作。
具体操作可参考:
百度网盘链接:https://pan.baidu.com/s/1kCA_D9oxWnmfrTiAwLNmlQ
提取码:ii10