【Excel周报制作】|如何利用Excel做一份强大、优美的周报呢？

最新推荐文章于 2025-03-30 22:23:27 发布

了不起的旺仔鱼

最新推荐文章于 2025-03-30 22:23:27 发布

阅读量3.4k

点赞数 15

文章标签：数据分析 excel

本文链接：https://blog.csdn.net/weixin_59782731/article/details/138800169

版权

文章结构：

前言

一、源数据描述

二、目标

三、手把手制作

四、结果展示

前言

做数据分析可能会用到很多工具，Python、R、Java等等。但是，无论如何都无法绕开Excel。虽然涉及到生产的项目都会用编程语言或者SQL开发，但是日常的一些探查和分析都会用到Excel。除此之外在日常工作中，经常需要对过去一周、一个月甚至一年的数据进行整理归纳，从而发现影响数据变化的因素，所以一份直观、优美的周报制作至关重要。在此也要特别鸣谢B站UP主戴师兄，本文也算是我的学习记录，以拌客1-8月的源数据作为示例，一起来看看吧！

一、源数据描述

源数据是从B站戴师兄那里下载的，需要的小伙伴可以去search一下。

拿到数据的第一件事是看一下数据的量级，本数据集大小为561行、24列（不含表头），主要描述了娃小辣、拌客等品牌在2020年1月-8月在不同平台（美团、饿了么）的销售额、GMV、下单量、曝光量、cpc总费用等数据。

二、目标

然后我们就要思考要做一份怎么样的周报？这个周报要反映什么数据？首先，一份合格的周报是要能反映出本周的工作效果，也就“结果指标”，在这里我们考虑GMV、商家实收、有效订单、无效订单四个指标，通过这些指标可以计算出商家的到手率和客单价：到手率=商家实收/GMV，客单价=GMV/有效订单。

那为什么会引起这样的结果呢？这就需要我们考虑一下本周的曝光量怎么样（曝光人数），有多少人点开商家链接（进店人数），又有多少人下单（下单人数）等，这就是我们所说的“过程指标”。通过这些指标我们可以计算出进店转化率、下单转化率、营销占比，这些可以帮助我们更好的分析可以从哪些方面进行改进。

除此之外，我们还需要对本周的结果与之前的做对比，并且希望能够直观的反映出这周的结果是否达标。我采用的方式是计算周累计与周环比（周环比=本周数据/上周数据-1）。

另外，我还希望这个周报表可以对不同平台进行区分，比如饿了么、美团或者全部，所以还需要添加一个筛选器。你也可以对不同商家进行筛选，方法类似。

三、手把手制作

OK，有了明确的目标之后，我们就可以撸起袖子开始干了。这么多数据需要一个一个动手敲吗，或者一列一列进行计算，或者需要很复杂的函数？No!其实在整个制作报表的过程，我们仅仅手敲一个日期，其他都是通过单元格的引用和函数计算得来的。并且我们只是灵活使用了四个函数：IF、SUMIFS、MATCH、INDEX，以及Excel自己本身的自我填充功能。

Step1:首先，手动输入一个日期，例如2020年8月11日，之后的日期对2020/8/11进行引用，然后利用Excel的填充功能，所有日期都如此操作。

因为我们想要这个报表可以展示不同平台的数据，所以在平台那里添加数据验证，之后的数据都来引用这个数据验证：

Step2:点击数据>>数据验证>>序列>>输入“全部，美团，饿了么”（注意：都是英文状态下的符号）

Step3:接下来我们计算GMV 等结果指标。

先用我自己的话表达几个函数的用法：

IF函数：=IF(条件，满足条件时返回的值，不满足条件时返回的值)

IF函数嵌套：=IF(条件1，IF（条件2，同时满足条件1和2返回的值，满足条件1不满足条件2返回的值），IF(条件2，不满足条件1但满足条件2返回的值，条件1和条件2都不满足返回的值))

SUMIFS函数：=SUMIFS(要求和的列，条件1所在的列，条件1的值，条件2所在的列，条件2的值，......)

MATCH函数：用来定位所选元素所在的行（列）的第几个位置，0表示精确搜索

=MATCH（元素，区域，0）

INDEX函数：用来确定某行某列的元素，行为0返回整列，列为0返回整行

=INDEX(区域，行，列)

GMV的计算就是灵活运用了这几个函数的计算，以下是GMV的计算思路：

根据这个思路，写出计算GMV的公式：

=IF($H$5="全部",SUMIFS(INDEX(E12!$A:$X,0,MATCH(C$12,源数据备份!$1:$1,0)),源数据备份!$A:$A,$A13),IF($H$5="美团",SUMIFS(INDEX(源数据备份!$A:$X,0,MATCH(C$12,源数据备份!$1:$1,0)),源数据备份!$A:$A,$A13,源数据备份!$H:$H,"美团"),SUMIFS(INDEX(源数据备份!$A:$X,0,MATCH(C$12,源数据备份!$1:$1,0)),源数据备份!$A:$A,$A13,源数据备份!$H:$H,"饿了么")))

（需要注意的一点是，我们为了利用Excel的自动填充功能，对一些行和列的标号需要固定）

因为我们这个公式中都是对指标进行引用的，而不是直接赋值，所以可以直接拖拽，利用Excel自动填充。大家会发现，咦，"到手率"和"客单价"为啥是错的呢？那是因为我们源数据中没有这两个指标，这两个指标是我们自己计算哒！

Step4：计算过程指标

与Step3很类似，不再过多介绍。

Step5:计算有效订单、商家实收等指标的周环比

周环比=本周数据/上周数据-1，本周数据我们在结果指标或过程指标中都得到了，那上周数据怎么计算呢，很简单，只要我们在SUMIFS函数中日期的条件稍作修改就可以了。以有效订单为例，给出计算上周有效订单的公式：

IF($H$5="全部",SUMIFS(INDEX(源数据备份!$A:$X,0,MATCH(F$12,源数据备份!$1:$1,0)),源数据备份!$A:$A,">="& $A13-7,源数据备份!$A:$A,"<="& $A19-7),IF($H$5="美团",SUMIFS(INDEX(源数据备份!$A:$X,0,MATCH(F$12,源数据备份!$1:$1,0)),源数据备份!$A:$A,">="& $A13-7,源数据备份!$A:$A,"<="& $A19-7,源数据备份!$H:$H,"美团"),SUMIFS(INDEX(源数据备份!$A:$X,0,MATCH(F$12,源数据备份!$1:$1,0)),源数据备份!$A:$A,">="& $A13-7,源数据备份!$A:$A,"<="& $A19-7,源数据备份!$H:$H,"饿了么")))

Step6:迷你图的插入以及符号的插入

迷你图：选中数据>>插入>>折线>>插入位置

符号：选中百分比>>开始>>条件格式>>新建规则>>格式样式选图标集>>修改图标、值