Excel是一个功能强大的分析工具，它能够自动化生成报表，并且可以制作简单的数据可视化图表。只要简单更换下日期或点击筛选器，就可以自动更新门店的营业数据，是不是觉得这样非常的方便呢？下面就跟三金一起来制作吧！
只需改变一个日期，报表可自动计算所需要的当周数据
只需选择不同平台，报表可自动生成所对应的平台数据
无需逐一手动填写，报表可自动汇总并对比上周数据值
无需手动绘制图标，报表可根据周数据自动绘制迷你图
无需手动计算检验，当周数据不佳时报表即可自动标注
本文主要用到的函数为if，sumifs以及index/match的混合使用，我先将这几个函数的基本语法贴上（已了解的同学可自行跳过）
函数语法
IF函数的基本语法如下：
IF(logical_test, value_if_true, value_if_false)
其中：
logical_test：要测试的条件或表达式。
value_if_true：如果logical_test为真（即满足条件），则返回的值。
value_if_false：如果logical_test为假（即不满足条件），则返回的值。
SUMIFS函数的语法如下：
SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)
其中：
sum_range：要求和的单元格范围。
criteria_range1：第一个条件所在的单元格范围。
criteria1：第一个条件。
[criteria_range2, criteria2]：可选的第二个条件及其对应的单元格范围和条件。可以继续添加更多的条件和对应的单元格范围和条件。
Excel中的INDEX和MATCH函数是两个常用的查找和引用函数。
INDEX函数：
功能：返回指定行和列的单元格中的值。
语法：INDEX(array, row_num, column_num)
array：要从中返回值的区域或数组。
row_num：要返回的行号。
column_num：要返回的列号。
MATCH函数：
功能：返回指定项在数组中的相对位置。
语法：MATCH(lookup_value, lookup_array, [match_type])
lookup_value：要查找的值。
lookup_array：要在其中查找lookup_value的范围或数组。
[match_type]：可选参数，指定匹配类型。它可以是1、0或-1。
1表示小于或等于lookup_value的最大值。
0表示与lookup_value完全匹配。
-1表示大于或等于lookup_value的最小值。
这两个函数经常一起使用，以实现更复杂的查找和引用功能。
好啦，下面让我们正式进入自动化报表的制作过程吧！
设置“平台”下拉选项框
如图所示，通过点击单元格下拉按钮就可以显示出“全部/美团/饿了么”三个选项
操作方法如下：
1、选中需要设置下拉选项的单元格，然后点击工具栏里面的“数据验证”；
2、在弹出来的窗口设置里，将“允许”设置为“序列”，在“来源”里手动输入“全部,美团,饿了么”，最后点击“确定”按钮即可。
输出结果指标
我们把所有的日期都引用同一个单元格的数据，这样的好处就是只需要改变一个日期数值，其余日期所对应的数值会关联变化。
如图所示，将A14=A13+1再把A14往下拉（千万不要直接把A13往下拉），同时将日期单位格格式调整为“星期”，就可得到当天为星期几。
那么，当天的GMV是怎么自动生成呢？这里我们分为三步走：
第一步，我们先计算2020/8/10至2020/8/16的当天GMV，用以下公式：
=SUMIFS('拌客源数据1-8月'!J:J,'拌客源数据1-8月'!A:A,$A13)
第二步，替换公式的内容，使得数据可以根据“报表字段”的变化而变化，这里要用到index和match函数
=INDEX('拌客源数据1-8月'!$A:$X,0,MATCH(C$12,'拌客源数据1-8月'!$A$1:$X$1,0))
接着替换掉第一步公式中的GMV：
=SUMIFS(INDEX('拌客源数据1-8月'!$A:$X,0,MATCH(C$12,'拌客源数据1-8月'!$A$1:$X$1,0)),'拌客源数据1-8月'!$A:$A,$A13)
第三步，再通过if函数，可以让我们通过平台筛选器来让报表自行计算不同平台的数值，则最终公式为
=IF($H$5="全部",SUMIF('拌客源数据1-8月'!$A:$A,$A13,INDEX('拌客源数据1-8月'!$A:$X,0,MATCH(C$12,'拌客源数据1-8月'!$1:$1,0))),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))
通过这个公式进行拖拉，可以自行计算出“商家实收”，“有效订单”、“无效订单”等字段的数据结果。“到手率”和“客单价”可以通过这两个公式得出：
到手率=商家实收÷GMV；
客单价=GMV÷有效订单
最后，利用sum函数对“GMV”，“商家实收”，“有效订单”，“无效订单”自动求汇总。
注意这里“到手率”和“客单价”是直接下拉即可。
输出过程指标
首先计算出2020/8/10这天的曝光人数，同理运用index和match函数让公式可以灵活套用。
=IF($H$5="全部",SUMIF('拌客源数据1-8月'!$A:$A,$A24,INDEX('拌客源数据1-8月'!$A:$X,0,MATCH(C$23,'拌客源数据1-8月'!$1:$1,0))),SUMIFS(INDEX('拌客源数据1-8月'!$A:$X,0,MATCH(C$23,'拌客源数据1-8月'!$1:$1,0)),'拌客源数据1-8月'!$A:$A,$A24,'拌客源数据1-8月'!$H:$H,$H$5))
以上公式可以计算出“曝光人数”、“进店人数”、“下单人数”，直接通过拖拽就可以自动计算出指标结果。
进店转化率、下单转化率根据这几个等式得出：
进店转化率=进店人数÷曝光人数；下单转化率=下单人数÷进店人数；
营销占比=cpc总费用÷GMV
我们需要先写出“cpc总费用”的公式，再除以我们已经得到了的GMV。这里我们可以直接灵活引用上述的公式，最后的营销占比公式为：
=IF($H$5="全部",SUMIFS('拌客源数据1-8月'!$T:$T,'拌客源数据1-8月'!A:A,">="&A24,'拌客源数据1-8月'!A:A,"<="&A30),SUMIFS('拌客源数据1-8月'!T:T,'拌客源数据1-8月'!H:H,H5,'拌客源数据1-8月'!A:A,">="&A24,'拌客源数据1-8月'!A:A,"<="&A30))/C20
总计一栏的“曝光人数”、“进店人数”、“下单人数”和之前一样，也是可以直接用sum求和函数直接拖拽计算的。“进店转化率”和“下单转化率”也是下拉填充即可。
制作Excel迷你图
在周累计数据里会涉及到制作趋势曲线图，这个我们可以直接使用Excel的“迷你图”功能完成。
首先，点击需要生成“迷你图”的目标单元格，在“插入”功能栏里面找到“迷你图”，再点击“折线”。
然后在“数据范围”里选择所需要的数据，再点击“确定”按钮，最后个性化设置折线即可。
计算周环比
环比，是表示连续2个统计周期内的量的变化比。如计算周环比，公式为：周环比=当周的数据/上周的数据-1
其中“有效订单”、“商家实收”、“到手率”的本周数据，可以直接引用下方的结果指标。例如，“有效订单”的本周数据，就可以使用公式：
=F20
计算“有效订单”和“商家实收”的周环比
“有效订单”的周环比=本周“有效订单”/上周“有效订单”-1
本周“有效订单”的数值已经计算出，那么我们只要计算出上一周的数据，就可以得到周环比数据。
上一周的数据也不用单独计算，灵活运用之前的公式稍微修改下即可，比如将（日期数值-7），“有效订单”的周环比的公式如下：
=A9/IF(H5="全部",SUMIFS('拌客源数据1-8月'!P:P,'拌客源数据1-8月'!A:A,">="&(A13-7),'拌客源数据1-8月'!A:A,"<="&(A13-1)),SUMIFS('拌客源数据1-8月'!P:P,'拌客源数据1-8月'!A:A,">="&(A13-7),'拌客源数据1-8月'!A:A,"<="&(A13-1),'拌客源数据1-8月'!H:H,H5))-1
用同样的公式，也可以计算得出“商家实收”的周环比数据：
=C9/IF(H5="全部",SUMIFS('拌客源数据1-8月'!K:K,'拌客源数据1-8月'!A:A,">="&(A13-7),'拌客源数据1-8月'!A:A,"<="&(A13-1)),SUMIFS('拌客源数据1-8月'!K:K,'拌客源数据1-8月'!A:A,">="&(A13-7),'拌客源数据1-8月'!A:A,"<="&(A13-1),'拌客源数据1-8月'!H:H,H5))-1
“到手率”周环比的计算公式为：
“到手率”周环比=本周“到手率”/上周“到手率”-1
上周“到手率”的公式为：上周“到手率”=上周“商家实收”/上周GMV。
所以，“到手率”周环比的计算公式可以优化为：
“到手率”周环比=本周“到手率”/(上周“商家实收”/上周GMV)-1
计算公式如下：
=E9/(IF(H5="全部",SUMIFS('拌客源数据1-8月'!K:K,'拌客源数据1-8月'!A:A,">="&(A13-7),'拌客源数据1-8月'!A:A,"<="&(A19-7)),SUMIFS('拌客源数据1-8月'!K:K,'拌客源数据1-8月'!A:A,">="&(A13-7),'拌客源数据1-8月'!A:A,"<="&(A19-7),'拌客源数据1-8月'!H:H,H5))/IF(H5="全部",SUMIFS('拌客源数据1-8月'!J:J,'拌客源数据1-8月'!A:A,">="&(A13-7),'拌客源数据1-8月'!A:A,"<="&(A19-7)),SUMIFS('拌客源数据1-8月'!J:J,'拌客源数据1-8月'!A:A,">="&(A13-7),'拌客源数据1-8月'!A:A,"<="&(A19-7),'拌客源数据1-8月'!H:H,H5)))-1
设定目标和业务进度条
“业务目标”的设置，在实际应用场景中，需要根据“平台”的不同，而显示出不同的“目标”数值。
假设我们把“业务目标”定为：平台为“全部”，则为200000；平台为“美团”，则为100000；平台为“饿了么”，则为50000。
通过if函数我们可以写出：
=IF(H5="全部",200000,IF(H5="美团",100000,50000))
业务进度是截止至目前日期，整个月的GMV除以目标。
这里我们可以结合上述公式和date函数，就可以得到：
=IF(H5="全部",SUMIFS('拌客源数据1-8月'!J:J,'拌客源数据1-8月'!A:A,">="&DATE(YEAR(A13),MONTH(A13),1),'拌客源数据1-8月'!A:A,"<="&A19),SUMIFS('拌客源数据1-8月'!J:J,'拌客源数据1-8月'!A:A,">="&DATE(YEAR(A13),MONTH(A13),1),'拌客源数据1-8月'!A:A,"<="&A19,'拌客源数据1-8月'!H:H,H5))/H8
数值有了，但进度条要怎么显示出来呢？
这个并不难，首先我们选中“业务进度”这个计算出来的数值，然后点击菜单栏里面的“条件格式”→“新建格式规则”→“编辑规则”，再根据自己的喜好进行个性化的设置。
至此，周报表的基础数据设置完成。
最后的排版优化
1、数据标题加粗，调整颜色，对齐方式等；
2、环比的数值如果是正数，显示“绿色向上”箭头；如果是负数，则显示“红色向下”箭头：这个依然是菜单栏里面的“条件格式”→“新建格式规则”，先选中“基于各自值设置所有单元格的格式”，然后在“编辑规则”处设置即可（字体颜色同理）
3、将GMV小于当周GMV日平均值的行用“下划线”标出：先选中“结果指标”区域，依然是通过“新建格式规则”，在条件设置中，填入公式=$C13<AVERAGE($C$13:$C$19)，再个性化设置格式，例如“加粗”、“加下划线”等。
4、取消“网格线显示”：最后为了视觉美观，可以在菜单栏点击“视图”，取消勾选“网格线”即可。
至此，整张动态数据报表完成设置。
你学会（废）了吗？
大家对于本篇Excel的自动化办公应用有什么问题，欢迎在评论区讨论交流
如果希望使用本文所用到的案例数据，可以在评论区留言回复“Excel”
