数据表格软件Excel的功能其实很强大，只需要使用一点点技巧，就可以实现搭建出动态可视化报表，满足日常的监控工作数据的需求。

下面是某外卖门店的销售周报情况。只要动动手指切换筛选器，就可以自动更新门店的营业数据。

一、数据报表问题

利用提供的外卖门店的营业数据，搭建出下图的外卖门店的“销售数据周报看板”，显示出该门店的销售过程指标。

并且切换【平台】选项后，对应的销售数据自动变化。

二、问题解决方案

（一）明确报表目标

一份灵活性较高的周报，只要改动“平台”、“日期”、“项目指标”等信息时，指标数据能够相应变化。

（二）报表使用到的Excel函数

1、INDEX函数功能

INDEX( 取值范围，第几行，第几列）

INDEX函数的作用是，返回表格或区域中的值或值的引用。

2、MATCH函数功能

MATCH(查找值,查找区域, [查找方式])

MATCH函数的作用是，返回查找内容在查找区域中的相对位置。

3、SUMIFS函数功能

SUMIFS（用来求和的数值区域，条件1判断的所在区域1，条件1，[条件2判断的所在区域2，条件2]，......）

SUMIFS函数的作用是，对满足多个条件的全部区域进行求和。

4、IF函数功能

IF(条件表达式,成立时返回结果，不成立时返回结果)

IF函数的作用是，根据逻辑计算的真假值，从而返回相应的内容。

三、操作步骤

（一）搭建数据报表框架

要搭建出满足相应需求的数据报表，需要填充好好包括“标题”、“平台筛选按钮”、“过程指标”三个区域。

（二）设置日期

所有日期数据，都引用同一个单元格进行计算。

引用同一单元格的好处，就是后期改动这一单元格日期数据时，其他日期都会随之变动。

如图所示，日期都引用单元格A25。

下一天的日期直接用该日期加1即可计算得出。

将单元格格式的数字格式改为“星期”，则能够显示对应的星期。

（三）设置【平台】下拉选项框

【平台】下拉选项框实现结果如下。

点击单元格右侧的倒三角按钮，就能够显示出【全部、美团、饿了么】3个选项提供选择。

效果实现过程如下：

1、选中需要设置下拉选项框的目标单元格，点击【数据】工具栏的有效性按钮；

2、在弹出的【数据有效性】窗口中，将“有效性条件”的【允许】设置为【序列】，【来源】输入序列【全部,美团,饿了么】。最后点击【确定】按钮。

（四）统计过程指标

1、计算2020/8/10至2020/8/16的每一天【曝光人数】，

用以下公式：

=IF($H$5="全部",SUMIFS(拌客源数据1-8月!Q:Q,拌客源数据1-8月!A:A,$A25),SUMIFS(拌客源数据1-8月!Q:Q,拌客源数据1-8月!A:A,$A25,拌客源数据1-8月!H:H,$H$5))

公式解析如下：

2、改动公式的内容，使得数据可以根据“表头标题”的变化而变化，可用INDEX和MATCH实现。

首先写出日期列、求和列、平台列的公式：

接着将日期列、求和列、平台列替换掉第一步公式中的日期、曝光人数、平台，则最终公式为：

3、以上公式可以计算出【曝光人数】、【进店人数】、【下单人数】，将公式复制到对应单元格，则自动计算出指标的结果。

4、计算指标【进店转化率】、【下单转化率】

进店转化率、下单转化率根据这几个等式得出：

进店转化率=进店人数÷曝光人数；

下单转化率=下单人数÷进店人数；

5、计算指标【营销占比】

营销占比根据这等式得出：营销占比=cpc总费用÷GMV

我们需要先写出【cpc总费用】的公式，再除以我们已求出的GMV。

引用上面最终的灵活公式，然后将C$24改成”cpc总费用”即可得出【cpc总费用】的公式，则营销占比公式为：

6、计算各个过程指标的总计

【曝光人数】、【进店人数】、【下单人数】可利用sum求和函数进行加总计算；

【进店转化率】和【下单转化率】是下拉填充；

【营销占比】的计算思路为：一周的营销占比=当周的cpc总费用÷当周的GMV，

操作为：将计算“一天”的cpc总费用的公式，改为计算“一周”的cpc总费用的公式，然后再除以当周的GMV。公式如下：

数据周报完成。

四、文章总结

1、一份灵活性较高的周报，只要改动“平台”、“日期”、“项目指标”等信息时，指标数据能够相应变化。

2、数据报表常用到的函数

（1）INDEX( 取值范围，第几行，第几列）

INDEX函数的作用是，返回表格或区域中的值或值的引用。

（2）MATCH(查找值,查找区域, [查找方式])

MATCH函数的作用是，返回查找内容在查找区域中的相对位置。

（3）SUMIFS（用来求和的数值区域，条件1判断的所在区域1，条件1，[条件2判断的所在区域2，条件2]，......）

SUMIFS函数的作用是，对满足多个条件的全部区域进行求和。

（4）IF(条件表达式,成立时返回结果，不成立时返回结果)

IF函数的作用是，根据逻辑计算的真假值，从而返回相应的内容。

———————————————————

在工作中，学会用Excel把数据直观地表达出来，是我们必须掌握的技能。因为一张好的图表可以做到一图胜千言！

下图是某店铺的销售报表，从图中可以直观看到该店铺在一段时间的销售额、曝光人数、进店转化率等经营业绩趋势。

本号将会陆续更新一系列文章，讲解如何制作反映店铺业绩的自动化数据报表。

历史系列文章：

1.Excel常用函数(1)-sum求和函数

2.Excel常用函数(2)-sumif单条件求和函数

3.Excel常用函数(3)-多条件求和就用sumifs函数

4.Excel常用函数(4)-如何计算上个月的同一天，用EDATE日期函数

5.Excel常用函数(5)-如何计算销售日环比

6.Excel常用函数(6)-如何计算销售日同比

7.Excel常用函数(7)-如何计算某个月最后一天的日期

8.Excel常用函数(8)-使用IF函数判断分类，让你效率翻倍

9.Excel常用函数(9)- IF函数嵌套使用，实现多条件结果分类，使用前先画个图

10.Excel常用函数(10)-三分钟学会vlookup函数，快速匹配出结果

11.Excel常用函数(11)-利用vlookup函数模糊匹配，实现等级快速划分

12.Excel常用函数(12)-利用Excel数据透视表，1分钟汇总大数据结果

13.Excel常用函数(13)-还不会用match函数？亏大了！

14.Excel常用函数(14)-认识INDEX，函数中的精确查找导弹

15.Excel常用函数(15)-INDEX和MATCH函数做搭档，查询起来真方便

16.Excel常用函数(16)-【index和match】函数查找神器，查询起来真方便

17.Excel常用函数(17)-使用【index和match函数】，轻松搞定条件求和

18.Excel数据报表(1)-用Excel搭建门店销售周报，自动统计数据

在理解Excel函数语法规则后，要多多练习，才能够记住哦~

如果希望使用文章中的案例数据，可以在文章评论区回复【excel】，则自动回复文件下载地址。