excel周报制作

一、源数据

日期	品牌ID	品牌名称	门店ID	门店名称	城市	平台	平台i	平台门店名称	GMV	下单人数	商家实收	商户补贴	平台补贴	无效订单数	曝光人数	有效订单数	进店人数	cpc单次点击费用	cpc总费用	cpc曝光量	cpc访问量	gmvroi	下单转换率	单均gmv	单均实收	实收roi	无效订单	有效订单	自然曝光量	自然访问量	门店下单量	门店实收	门店曝光量	门店营业额	门店访问量
2023/7/1	6108	拌客（武宁路店）	337460136	拌客干拌麻辣烫(武宁路店)	上海	eleme	饿了么	拌客·干拌麻辣烫(武宁路店)	3824	72	1332	2050	64	2	5373	75	267	1.81	157.66	2008	87	7.59	0.27	50.99	17.75	2.64	2	75	3365	180	72	1331.57	5373	3824.26	267
2023/7/1	4636	蛙小辣火锅杯（总账号）	2001104355	宝山店	上海	eleme	饿了么	蛙小辣·美蛙火锅杯麻辣烫(宝山店)	1169	18	395	660	22	0	902	19	64	1.5	25.5	232	17	11.53	0.28	61.52	20.79	3.9	0	19	670	47	18	395.07	902	1168.84	64
2023/7/1	4636	蛙小辣火锅杯（总账号）	8491999	宝山店	上海	meituan	美团	蛙小辣火锅杯（宝山店）	1008	19	351	553	38	0	794	19	73	1.45	37.57	631	26	9.56	0.26	53.07	18.5	3.33	0	19	163	47	19	351.48	794	1008.28	73
2023/7/2	4636	蛙小辣火锅杯（总账号）	8491999	宝山店	上海	meituan	美团	蛙小辣火锅杯（宝山店）	1023	19	364	559	43	0	792	19	75	1.42	41.11	575	29	9.63	0.25	53.86	19.14	3.42	0	19	217	46	19	363.71	792	1023.39	75

二、实现目标

三、制作过程

（一）数据时间范围

1.获取当前日期所在周的星期一日期：

        B1=TEXT(DATE(YEAR(TODAY()),MONTH(TODAY()),DAY(TODAY())-WEEKDAY(TODAY())+2),"YYYYMMDD")

        D1=B1+6

3.获取当前日期所在月的第几周：

        A2=LEFT($B$1,4)&"年"&INT(MID($B$1,5,2))&"月"&LET(x,ROUNDDOWN((A12-DATE(YEAR(A12),MONTH(A12),1)+WEEKDAY(DATE(YEAR(A12),MONTH(A12),1)+1))/7,0)+1,IFS(x=1,"第一周",x=2,"第二周",x=3,"第三周",x=4,"第四周",x=5,"第五周"))

（二）结果指标

1.指标解释：

        GMV:商品交易总额，一般指订单金额

        商家实收：交易中商家实际收到的款项

        到手率：商家实收/GMV

        有效订单：已接单未取消

        无效订单：接单后一段时间后取消

        客单价：GMV/ 有效订单

2.计算公式

        A12=LEFT($B$1,4)&"-"&INT(MID($B$1,5,2))&"-"&INT(RIGHT($B$1,2))+ROW(A1)-1

        B12=WEEKDAY(A12,1)

        C12=SUMIFS(源数据!J:J,源数据!A:A,A12,源数据!H:H,IF($H$4="全部","*",$H$4))

        D12=SUMIFS(源数据!L:L,源数据!A:A,A12,源数据!H:H,IF($H$4="全部","*",$H$4))

        E12=IFERROR(D12/C12,"---")

        F12=SUMIFS(源数据!AC:AC,源数据!A:A,A12,源数据!H:H,IF($H$4="全部","*",$H$4))

        G12=SUMIFS(源数据!AB:AB,源数据!A:A,A12,源数据!H:H,IF($H$4="全部","*",$H$4))

        H12=IFERROR(C12/F12,"---")

        下拉填充

3.格式设置

        星期：改为日期格式（星期几）

        到手率：改为百分比格式，小数点后保留两位

        客单价：改为数值格式，小数点后保留两位

        "A12:H18"使用公式=$E12>$E$19进行条件格式设置：加粗、下划线、更改字体颜色

（三）过程指标

1.指标解释：

        曝光人数：类似于知名度

        进店人数：顾名思义

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

        下单人数：进店且下单的人

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

        营销占比：CPC费用(用户点击广告，广告主要承担的费用)/GMV

2.计算公式

        A23=LEFT($B$1,4)&"-"&INT(MID($B$1,5,2))&"-"&INT(RIGHT($B$1,2))+ROW(A1)-1

B23=WEEKDAY(A23,1)

        C23=SUMIFS(源数据!P:P,源数据!A:A,A23,源数据!H:H,IF($H$4="全部","*",$H$4))

        D23=SUMIFS(源数据!R:R,源数据!A:A,A23,源数据!H:H,IF($H$4="全部","*",$H$4))

        E23=IFERROR(D23/C23,"---")

        F23=SUMIFS(源数据!K:K,源数据!A:A,A23,源数据!H:H,IF($H$4="全部","*",$H$4))

        G23=IFERROR(F23/D23,"---")

        H23=IFERROR(SUMIFS(源数据!T:T,源数据!A:A,A23,源数据!H:H,IF($H$4="全部","*",$H$4))/C12,"---")

        下拉填充

3.格式设置

        星期：改为日期格式（星期几）

        进店转化率、下单转化率、营销占比：改为百分比格式，小数点后保留两位

（四）周累计、周环比

1.周累计

        ①直接引用过程指标中对应值的总计

        ②插入迷你图，数据区域选择对应值的所在位置，在迷你图工具中勾选“标记”

2.周环比(指标解释:(本周-上周)/上周)

①有效订单、商家实收、到手率直接引用结果指标中对应值的总计

②公式计算

        计算思路：先计算上周的有效订单、商家实收、到手率，在计算周环比

       B8 =LET(x,SUMIFS(源数据!Q:Q,源数据!A:A,">="&A12-7,源数据!A:A,"<"&A12,源数据!H:H,IF($H$4="全部","*",$H$4)),IFERROR((A8-x)/x,"---"))

       D8 =LET(x,SUMIFS(源数据!L:L,源数据!A:A,">="&A12-7,源数据!A:A,"<"&A12,源数据!H:H,IF($H$4="全部","*",$H$4)),IFERROR((C8-x)/x,"---"))

        F8=LET(x,SUMIFS(源数据!L:L,源数据!A:A,">="&A12-7,源数据!A:A,"<"&A12,源数据!H:H,IF($H$4="全部","*",$H$4))/SUMIFS(源数据!J:J,源数据!A:A,">="&A12-7,源数据!A:A,"<"&A12,源数据!H:H,IF($H$4="全部","*",$H$4)),IFERROR((E8-x)/x,"---"))

③格式设置

图标集（上下箭头）的条件格式设置：

        基于各自值设置所有单元格的格式→格式样式→选择图标集

字体颜色的条件格式设置：

        使用公式以0为界点设置两次，大于0字体颜色设为绿色，小于0字体颜色设为红色

（五）业务进度（截至目前整个月的GMV/目标）、平台

业务进度公式计算

        G6=SUMIFS(源数据!J:J,源数据!A:A,">="&DATEVALUE(LEFT($B$1,4)&"-"&INT(MID($B$1,5,2))&"-"&"1"),源数据!A:A,"<="&MAX(IFERROR(DATEVALUE(A12:A18),0)),源数据!H:H,IF($H$4="全部","*",$H$4))/H7

业务进度条件格式设置：数据条，值右对齐

平台：数据→有效性→序列，添加全部、美团、饿了么

补充：计算公式中的*表示通配符，当选择是全部时IF($H$4="全部","*",$H$4)，使用通配符进行条件匹配，它表示任意字符串下都为TRUE。