学习笔记：EXCEL外卖商家营业数据周报制作

数据及逻辑公式均来源戴师兄，感谢老师！

这是一份上海市两个火锅类品牌多家门店于20年1-8月在美团和饿了么平台营业的相关数据，主要包括GMV(成交额)、商家实收、门店曝光量、进店量、下单量、CPC(广告)总费用等，共计550+条数据。

进行简单的数据探索，查找无空值，筛选门店按日期查看无重复记录 (发现各店记录非同一时间区间，拓展门店数据横向比较无益，按月统计也无意义)。参考报表思路为：按平台分类统计一周所有品牌门店累计数据及环比数据，并分为结果指标和过程指标进行详细指标统计，其中计算使用的公式具有普范性、灵活性，能大大提升办公效率，虽看似复杂但极具逻辑思维，值得学习考究，举一反三。

 首先周报唯一需要手动输入的是开始日期A13，第二天时间=A13+1以此类推，同时表头日期=周一和周日的日期，第二列星期列只需等于前一列后将单元格格式改为自定义-周三。

 然后开始首次位置查找GMV，用到index+match函数。这里要考虑平台和时间两个条件。平台分为：全部、美团、饿了么三类，用数据验证-序列做一个可筛选表格，用IF嵌套选择；时间需要用match锁定返回时间列，同时考虑到一天会有多条门店记录，故需要SUMIF函数求和计算。

公式框架为：GMV=IF(筛选表格$H$5="全部",SUMIF(INDEX(日期列),日期$A13,INDEX(GMV列)),SUMIFS(INDEX(GMV列),GMVC$12,INDEX(日期列),日期$A13,INDEX(平台列),平台$H$5))

INDEX(日期列)=INDEX(原数据,0,MATCH(日期头$A$12,原数据表头,0) GMV列、平台列只需将前公式内日期头换为相应表头即可。

由于公式是根据表格内容查找数据，所以直接拖拽可得到商家实收、有效无效订单数据，并且修改表头内容可以查看原表其他列汇总数据。到手率=商家实收/GMV；客单价=GMV/有效订单。 

下表类似，其中营销占比=CPC总费用/GMV。

然后求总计，alt+=为SUM快捷键。比率总计再公式下拉，营销占比总计为(1-7)天CPC总费用/GMV总计。需要修改公式中时间条件：将日期$A13替换为">="&$A13，再复制一条INDEX并替换为"<="&$A19，由于两个条件因此将SUMIF替换为SUMIFS，后面两平台也同样将日期修改两次。结果和过程指标表告一段落。

在平台筛选下做目标和业务进度的制作。目标也按平台分三种，用IF嵌套(这里目标应是月目标，可按实际业务修改)；进度为从本月初至本周末的GMV总值/月目标，除了将上面营销占比总计的公式中"CPC费用"改为GMV,">="&$A13也需要改为">="&DATE(YEAR($A$13),MONTH($A$13),1)锁定本月第一天。

进度条在条件格式-新建规则-基于各自...-格式样式-数据条，设置类型-数字、值0-1、外观-渐变填充等。

周累计各项数据指标=该项总计。(这里老师直接用=，我用index+match查找了一下，可以更灵活吧嘻嘻)  迷你折线图在选中下表对应该列7个数据后，插入-迷你图-折线图，勾选标记。

周环比各项数据指标=(本周-上周)/上周=本周/上周-1，很熟练了已经上周公式里把日期">="&$A13替换为">="&($A13-7)、"<="&$A19替换为"<="&$(A19-7)。

其中，唯有到手率独树一帜、鹤立鸡群，出现了长达9行的公式算法！样我们来看看

到手率=本周到手率/上周到手率-1，上周到手率不可以直接改时间获得，需要重新代入公式计算，=上周商家实收/上周GMV，上周商家实收公式可以copy到，上周GMV公式可以将 商家实收$D$12均改为GMV$C$12,一层又一层全部放进去，乍一看自然很长了。数字颜色及箭头标识都在条件格式里编辑，这里就略一下先(;p,下划线为低于周平均GMV的标识。

终于告一段落，最后美化一下就可以啦。每周制作时只需要修改时间和目标的具体数值就可以得到一张新的周报！这里面 对条件查找函数、时间函数、条件格式等出神入化的运用简直妙哉！学到了学到了，再次感谢老师nim。这里也是我整理笔记处女作，之后大概会陆续上传一些之前或新get的数分笔记，独自前行任重而道远，感谢家人朋友陪伴。