Excel周报制作

学习视频：

【课程2.0】Excel基础操作|大厂周报制作|常用函数_哔哩哔哩_bilibili

作品介绍：

        做一个联动性非常强的Excel周报，如图所示，每个数据之间都有关联，改动起来非常方便，对基本函数也有了很深入的认知，总而言之收获非常多。

数据介绍

        可以看出是一张线上饮食门店营业情况的表

        GMV：营业额，扣除满减、红包等之前的商家本该进账的钱

        商家实收：商家实际收到的钱

        门店曝光量：被用户看到

        门店访问量：用户进入店铺

        量/人数的区别：相当于PV和UV，前者是用户每次访问都会被记录，后者只记录不同的用户，不会记录同一用户的多次访问，相当于一个去重操作

        cpc总费用：曝光总花费，相当于打广告花了多少钱

数据处理

        1.拿到数据首先复制一份，不要在原数据上直接操作

        2.可以在数据部分点击筛选按钮，就有一个下拉菜单，能很清楚看到数据有没有空值、异常值等，比如这里人数出现比量更多的情况，不过周报用不上，所以没处理

        3.这里还教了数据透视表和切片器，比较简单

常用函数

SUM求和

        正常使用sum求和就框选需要求和的区域就行，这里就涉及到多区域求和，区域与区域间用"，"分隔就行

SUMIF单条件求和

        sumif函数（条件所在区域，条件，求和值所在列），这个跟后面sumifs参数位置要区别开，比如这里要求2020/07/01这一天的GMV，第一个参数就是日期所在列，第二个参数就是2020/07/01所在单元格，第三个参数就是GMV所在列

        这里再注意一下$这个锁定符号就行，就是我们快速填充时，需要哪一行或列不变就锁定

SUMIFS多条件求和

        只看美团GMV列，sumifs函数（求和数据范围，条件范围1，条件1，条件范围2，条件2），例如：这里第一个参数是GMV所在列，第二个参数是日期所在列，第三个参数是2020/07/31所在单元格，第四个参数是平台名称也就是"平台i"这一列，第五个参数是"美团"

        

环比与同比

        环比小范围，同年不同月；同比大范围，同月不同年

例如：

年

        2020年环比（年环比与年同比一样）

        =（2020-2019）/ 2019

        =2020 / 2019 - 1

月

        2020年7月环比 = 2020年7月 / 2020年6月 - 1

        2020年7月同比 = 2020年7月 / 2019年7月 - 1

日

        2020年7月1日环比 = 2020年7月1日 / 2020年6月30日 - 1

        2020年7月1日的月同比 = 2020年7月 / 2019年7月 - 1

        2020年7月1日的周环比 = 2020年7月1日 / 2020年6月24日 - 1

        这里来看日环比这一列，根据公式就是7月31日GMV / 7月30日GMV - 1，7月31日GMV已经算出就是前一个单元格C30，那前一天的日期直接用7月31日减一即可

DATE(YEAR(),MONTH(),DAY())

        year()：取出日期中的年份

        month()：取出日期中的月份

        day()：取出日期中的天数

        由于下一个要计算日同比，我们就需要求出上个月这一天的GMV，如何求上个月的这一天呢？这里视频给的是DATE函数对年、月、日组合，其中给月份减一就可以得到，但是存在一个问题，就是7月31日给出的计算结果是7月1日，很明显是不对的，我们需要6月30日

        这里问了GPT给出的答案是可以的

        这样我们就可以求出上个月GMV

        这里算的是日对于月的同比，将刚刚的内容复制到日期地方就行

        这里先看美团GMV这一列，求的是月份了，不是某个日期，那么我们就要对月份的每一天GMV求和，这里用了>=和<=，注意符号要写在""之间，并且在符号后要加上&

       

        接下来我们要求月环比，那就要从上个月1号求到上个月最后一天，第一天就是将day改为1就行，最后一天由于每个月份不相同，所以灵活写成下个月第一天减一即可

        所以这里的月环比就求出来了

SUM和SUBTOTAL的区别

        sum就不用说了，主要是subtotal，第一个参数9是代表sum求和，我们输入时会有提示，不同数字代表不同函数，第二个参数就是要操作的区域了，还有一个区别就是，subtotal会跟随原数据进行筛选而改变

        

IF函数

        if函数比较简单，就是（判断条件，满足条件的结果，不满足条件的结果）

IF嵌套

        if嵌套就是在if中继续用if，理清楚逻辑也很简单

VLOOKUP函数

        vlookup函数第一个参数填写查找的参照物，也就是这里的门店ID，第二个参数是参照物和需要查找的数据所在区域（注意：参照数据所在列必须是所选区域的第一列），第三个参数是需要查找数据所在的列数，例如：我这里框选的区域是D和E两列，D列是门店ID，E列是门店名称，那么我们就应该填2，最后一个参数代表精确查找（0）

通配符

        *：代表不定数量字符

        ?：代表一个字符

        使用vlookup查找a的返回值发现报错，查看是因为左表中a后面跟了个空格，所以我们修改在I96后面加上&"*"，此时表中也会有别的a后面跟不同字符的数据，但是返回值只会为1，因为vlookup只会返回查找到的第一个值

        VLOOKUP函数也可以对数据透视表使用

INDEX和MATCH函数

        这两个函数结合使用，index第一个参数是整个表，第二个参数确定行，第三个参数确定列，而这里2、3参数都是用match函数实现的，行：确定需要找的数据在哪一行，跟平台门店名称是一行的，所以match第一个参数就是对应的平台点名称，第二个参数在这一列找行数。列：确定需要找的数据在哪一列，跟门店ID是一列的，所以match第一个参数就是对应的列字段，第二个参数在这一行找列数

        同样也是要注意锁定的问题

        这样的好处就是可以使用快速填充对后面的列填充，都能自动识别

        

完成报表

结果指标和过程指标

        1.这里的日期只自己填写结果指标中的2020/8/10，剩余的日期和星期全部都引用这一个日期（+1和将数据类型改为周几），这样调整日期别的也会自动改变

        2.由于我们添加了一个数据验证，对平台区分全部、美团、饿了么三种情况，所以我们后续操作时都得加此判断条件

        3.GMV列如图，由于我们希望后面的商家实收等都可以也自动填充，所以第一列GMV就写的复杂了点

        拆开来看（这里的@符号是报错溢出的解决方法）

        日期列：我们只要找列就行，所以index中行的位置为0，列就是按照"日期"来匹配列数

        求和列：我们是要对GMV列求和，那么同样按照"GMV"来匹配列数即可

        平台列：同上

        

        回到这张图片来看，如果数据验证为"全部"，那么sumif中第一个参数是时间列（通过index和match找日期列），第二个参数是对应的日期，第三个数据就是GMV列。

        sumifs里面判断的就是美团和饿了么，注意第一个参数是GMV列，后面的才是条件（一个是日期条件，一个是平台条件）

        

        3.那么求出之后我们就可以进行快速填充到下面和后面，但是这里的到手率和客单价原表中是没有的，需要自己计算

        到手率 = 商家实收 / GMV

        客单价 = GMV / 有效订单

        4.过程指标中曝光人数就按照做过的GMV处理就行，只要将GMV改为曝光人数即可，剩余的快速填充即可

        5.同样进店转化率、下单转化率、营销占比是需要自己计算的

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

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

        营销占比 = cpc总费用 / GMV（相当于支出除以收入）

        6.总计的求法：快捷键Alt + =可以快速对上面的数据求和，后面的快速填充即可

        注意：到手率、客单价等需要自己计算的，填充完再从其总计上面一个单元格向下填充一下，为了让其应用到上面的公式，就相当于变成：总商家实收 / 总GMV

周累计

        这里放了三个指标，都是我们计算过的，直接引用就行了，再在插入部分找到插入迷你图即可

目标和业务进度

        1.目标：是自己定的，用个if函数

        2.业务进度：整个GMV / 进度

        求整个GMV要知道的就是开始时间和结束时间，这里的开始时间就是本月1号，而结束时间就是本周最后一天（也就是8月16日），其他的没有什么变化

       

周环比

        1.有效订单的周环比：本周有效订单 / 上周有效订单 - 1

        这里只需要在之前算过的有效订单基础上将起始日期修改就行日期都-7即可

        2.商家实收同上

        3.到手率的周环比不能一样计算，因为 本周到手率 / （上周商家实收 / 上周GMV）- 1

        本周到手率已经算出，上周的两个也是只需要修改始末日期即可

        4.字体颜色和图案设置是在开始部分的条件格式中新建规则