0 前言

本来想将 Excel 函数的学习和周报放到一篇文章里，我的妈呀，Excel 函数已经很有学头了，因此分成两篇文章，这篇文章核心内容就是学习制作大厂周报。

1 收获

学习完本篇博客，你将会获得如下周报报表，你以为只是简单的 Excel 表格，静态查看数据，那简直是大错特错了，它是可以自动变化的，这可算是见识到了 Excel 的实力了，即使这也只是一些皮毛，但还是觉得自己好厉害，感觉世界真美好，还有这么多东西等着我去探索~

以前觉得 Excel 就是简单的表格，其实原来竟然是我不会用它~

如下👇🏻就是最终要展示的周报：

我感觉 index 和 match 是比较难的，所以会重点多次详细讲解，在学习这里的时候，务必打起精神，务必喝一口咖啡，哈哈哈哈哈哈😆！

2 实操

如上表所示，我们会运用到许多新的概念，这里给出需要的公式，方便后续查找。

2.1 基本公式或者概念

结果指标部分：
· GMV
· 商家实收
· 到手率 = 商家实收/GMV
· 有效订单
· 无效订单
· 客单价 = GMV/有效订单
过程指标部分：
· 曝光人数
· 进店人数
· 进店转化率 = 进店人数/曝光人数
· 下单人数
· 下单转化率 = 下单人数/进店人数
· 营销占比 = cpc总费用/GMV

· 到手率周环比 = 本周到手率/上周到手率 - 1
· 上周到手率 = 上周商家实收/上周GMV
· 到手率周环比 = 本周到手率/(上周商家实收/上周GMV) - 1

2.2 正式操作

(1) 填充表格内容(无需公式部分)

首先，打开大厂周报-练习版

目标：根据大厂周报-完成版进行周报的填充

先将需要展示的指标，在表格中列出，比如曝光人数、进店转化率等等。

找到筛选条件平台，并进行设置

数据验证！这里我们就知道如何制作类似下拉选框的样式了。

这里直接采用 Excel 自带的数据格式，将日期转变成星期。

拖动句柄填充是非常好用的工具，多多使用叭~

填充完毕的结果，如下图所示。

(2) 填写表格(需要公式部分)

填写完基本信息，开始填表格中其他需要计算的部分啦！

思路：平台的选择情况有三种，一是“全部”；二是“美团”；三是“饿了么”，因此我们就会有条件判断，IF H5=“全部”，如果是的，就直接全部求和，如果不是就接着判断，是美团就美团求和，是饿了么就饿了么就和。

注意这里有一个大的判断条件，即判断平台，所以首先需要 IF 函数，判断平台，再进行其他条件的筛选~

审题：GMV，有两个限制体条件，条件一：时间；条件二：平台，因此选择 SUMIFS 函数。
这都是审题思路，结果不重要，重要的是思考的过程。

=IF(H5="全部",SUMIFS('拌客源数据1-8月'!J:J,'拌客源数据1-8月'!A:A,A13),SUMIFS('拌客源数据1-8月'!J:J,'拌客源数据1-8月'!A:A,A13,'拌客源数据1-8月'!H:H,H5))

将上述表达式转换为 INDEX&MATCH 版本叭

=IF(H5="全部",SUMIFS(INDEX('拌客源数据1-8月'!A:X,0,MATCH(C12,'拌客源数据1-8月'!1:1,0)),'拌客源数据1-8月'!A:A,A13),SUMIFS(INDEX('拌客源数据1-8月'!A:X,0,MATCH(C12,'拌客源数据1-8月'!1:1,0)),'拌客源数据1-8月'!A:A,A13,'拌客源数据1-8月'!H:H,H5))

这里主要是把要填写的数据部分进行替换

接着将部分值进行固定，记得 F4 快捷键，可以整体固定下来。

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

有效订单的计算

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

无效订单思路是一样的，因此只需要进行拖拽句柄即可。自动化的 Excel 真的太赞了！！！

到手率 = 商家实收/GMV

=D13/C13

=SUMIFS(INDEX('拌客源数据1-8月'!$A:$X,0,MATCH(F$12,'拌客源数据1-8月'!$1:$1,0)),'拌客源数据1-8月'!$A:$A,$A13,'拌客源数据1-8月'!$H:$H,$H$5)

求曝光人数，复制之前GMV的公式，将 12 行改成 24 行即可，接着进行拖拽，具体修改的部分就需要我们自己去理解，很好理解，曝光人数在 24 行，因此需要修改行。

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

你就说是不是很爽，有了INDEX & MATCH，拖拽即可完成，有效帮助我们减少重复工作！

客单价 = GMV/有效订单

=C13/F13

进店转化率 = 进店人数/曝光人数

如何，知道公式了，现在拿捏否😎？

下单人数计算函数：

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

下单转化率 = 下单人数/进店人数

=F25/D25

· 营销占比 = cpc总费用/GMV
· 这个公式还挺好记忆的，营：经营，cpc投入宣传费用；销：销量 GMV，占比：除

CPC总费用：

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

GMV总费用：

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

营销占比：

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

(3) 总计区域

注意，我这里是美团数据昂。

总计的营销占比 = 一周的cpc总费用/一周的GMV总值

=IF($H$5="全部",SUMIFS(INDEX('拌客源数据1-8月'!$A:$X,0,MATCH("cpc总费用",'拌客源数据1-8月'!$1:$1,0)),'拌客源数据1-8月'!$A:$A,">="&$A13,'拌客源数据1-8月'!$A:$A,"<="&$A19),SUMIFS(INDEX('拌客源数据1-8月'!$A:$X,0,MATCH("cpc总费用",'拌客源数据1-8月'!$1:$1,0)),'拌客源数据1-8月'!$A:$A,">="&$A13,'拌客源数据1-8月'!$A:$A,"<="&$A19,'拌客源数据1-8月'!$H:$H,$H$5))/IF($H$5="全部",SUMIFS(INDEX('拌客源数据1-8月'!$A:$X,0,MATCH(C$12,'拌客源数据1-8月'!$1:$1,0)),'拌客源数据1-8月'!$A:$A,">="&$A13,'拌客源数据1-8月'!$A:$A,"<="&$A19),SUMIFS(INDEX('拌客源数据1-8月'!$A:$X,0,MATCH(C$12,'拌客源数据1-8月'!$1:$1,0)),'拌客源数据1-8月'!$A:$A,">="&$A13,'拌客源数据1-8月'!$A:$A,"<="&$A19,'拌客源数据1-8月'!$H:$H,$H$5))

这里和之前求营销占比的区别在于日期部分的条件限制，之前是日期 =A3， 现在的日期范围是 >=A13 & <= A19。

填写头部信息啦~

目标部分：如果是全部的平台，目标是 200000，美团目标 100000，饿了么目标 50000。

=IF($H$5="全部",200000,IF($H$5="美团",100000,50000))

· 业务进度 = GMV/目标

注意，这里时间有一个很大的区别，业务进度是从本月第一天开始计算的，累计值。

=IF($H$5="全部",SUMIFS(INDEX('拌客源数据1-8月'!$A:$X,0,MATCH(C$12,'拌客源数据1-8月'!$1:$1,0)),'拌客源数据1-8月'!$A:$A,">="&DATE(YEAR($A$13),MONTH($A$13),1),'拌客源数据1-8月'!$A:$A,"<="&$A19),SUMIFS(INDEX('拌客源数据1-8月'!$A:$X,0,MATCH(C$12,'拌客源数据1-8月'!$1:$1,0)),'拌客源数据1-8月'!$A:$A,">="&DATE(YEAR($A$13),MONTH($A$13),1),'拌客源数据1-8月'!$A:$A,"<="&$A19,'拌客源数据1-8月'!$H:$H,$H$5))/$H$8

· 周环比 = 本周/上周 - 1

上周有效订单:

IF($H$5="全部",SUMIFS(INDEX('拌客源数据1-8月'!$A:$X,0,MATCH(A$8,'拌客源数据1-8月'!$1:$1,0)),'拌客源数据1-8月'!$A:$A,">="&($A13-7),'拌客源数据1-8月'!$A:$A,"<="&($A19-7)),SUMIFS(INDEX('拌客源数据1-8月'!$A:$X,0,MATCH(A$8,'拌客源数据1-8月'!$1:$1,0)),'拌客源数据1-8月'!$A:$A,">="&($A13-7),'拌客源数据1-8月'!$A:$A,"<="&($A19-7),'拌客源数据1-8月'!$H:$H,$H$5))

有效订单环比：

=A$9/IF($H$5="全部",SUMIFS(INDEX('拌客源数据1-8月'!$A:$X,0,MATCH(A$8,'拌客源数据1-8月'!$1:$1,0)),'拌客源数据1-8月'!$A:$A,">="&($A13-7),'拌客源数据1-8月'!$A:$A,"<="&($A19-7)),SUMIFS(INDEX('拌客源数据1-8月'!$A:$X,0,MATCH(A$8,'拌客源数据1-8月'!$1:$1,0)),'拌客源数据1-8月'!$A:$A,">="&($A13-7),'拌客源数据1-8月'!$A:$A,"<="&($A19-7),'拌客源数据1-8月'!$H:$H,$H$5))-1

商家实收周环比:

=C$9/IF($H$5="全部",SUMIFS(INDEX('拌客源数据1-8月'!$A:$X,0,MATCH(C$8,'拌客源数据1-8月'!$1:$1,0)),'拌客源数据1-8月'!$A:$A,">="&($A13-7),'拌客源数据1-8月'!$A:$A,"<="&($A19-7)),SUMIFS(INDEX('拌客源数据1-8月'!$A:$X,0,MATCH(C$8,'拌客源数据1-8月'!$1:$1,0)),'拌客源数据1-8月'!$A:$A,">="&($A13-7),'拌客源数据1-8月'!$A:$A,"<="&($A19-7),'拌客源数据1-8月'!$H:$H,$H$5))-1

· 到手率周环比 = 本周到手率/上周到手率 - 1
· 上周到手率 = 上周商家实收/上周GMV
· 到手率周环比 = 本周到手率/(上周商家实收/上周GMV) - 1

=E$9/(IF($H$5="全部",SUMIFS(INDEX('拌客源数据1-8月'!$A:$X,0,MATCH(C$8,'拌客源数据1-8月'!$1:$1,0)),'拌客源数据1-8月'!$A:$A,">="&($A13-7),'拌客源数据1-8月'!$A:$A,"<="&($A19-7)),SUMIFS(INDEX('拌客源数据1-8月'!$A:$X,0,MATCH(C$8,'拌客源数据1-8月'!$1:$1,0)),'拌客源数据1-8月'!$A:$A,">="&($A13-7),'拌客源数据1-8月'!$A:$A,"<="&($A19-7),'拌客源数据1-8月'!$H:$H,$H$5))/IF($H$5="全部",SUMIFS(INDEX('拌客源数据1-8月'!$A:$X,0,MATCH(C$12,'拌客源数据1-8月'!$1:$1,0)),'拌客源数据1-8月'!$A:$A,">="&($A13-7),'拌客源数据1-8月'!$A:$A,"<="&($A19-7)),SUMIFS(INDEX('拌客源数据1-8月'!$A:$X,0,MATCH(C$12,'拌客源数据1-8月'!$1:$1,0)),'拌客源数据1-8月'!$A:$A,">="&($A13-7),'拌客源数据1-8月'!$A:$A,"<="&($A19-7),'拌客源数据1-8月'!$H:$H,$H$5)))-1

(4) 数据整理

在美化表格前，对数据进行统一处理。

（1）将数字格式进行对齐；
（2）百分比的就改写成百分比格式，保留两位小数，非百分比进行四舍五入转化为整数。

这样我们就处理好了我们的数据。

2.3 美化界面

到此，我们就填写完毕，本着先填写再美化的原则，进入美化环节。

原则：美化永远是最后做的事情

按住 ctrl，接着通过鼠标点击操作可以实现多选，可以减少重复操作。
以下主要是加粗部分指标。给一些指标不同的颜色，目的是使得部分更加抓眼球。

绘制迷你折线图，可以在周报报表中很醒目的一眼看到变化趋势，有一个趋势的概念。

进店转化率 和 下单转化率 都是同样的操作~非常 easy 啦

这里有一个小窍门，就是双击格式刷的话，不会进行一次格式刷就消失。

=$C13<AVERAGE($C$13:$C$19)

再新增两个规则

可以自己调试一下，比如我将平台改成饿了么

将平台改成全部：

2.4 small tips

· 永远不要用execel日期格式存储日期，用字符
· >= 也要用英文双引号，且要加入&符号
· index行位置为0返回整列，列为之为0返回整行
· 美化都是最后做的

2.5 作业

学习完所有课程后，点击所有的按钮

3 后记

如果博客对你有帮助的话，记得给我点赞赞哟~

哦莫，今天三月三，必须吃地菜煮鸡蛋了~

妈呀，这博客写了我三天，中间就是有各种各样的问题，真是不容易啊！！！