编按：

哈喽，大家好！今天向大家分享一个销售统计表模板。该模板支持动态查询功能，并且在查询的时候，相应数据会变色，如此，查询结果一目了然。统计模板将使用sum、and、column、match、offset函数并结合条件格式和数据验证。赶紧来看看吧！

今天要和大家分享的是一个可以用颜色指示查询区域和结果的动态查询销售数据的统计模板。何为动态查询呢，效果如动图所示：

要做这个模板，需要两部分工作，公式和条件格式。

公式用来实现销售数据汇总，条件格式用来改变单元格颜色突出求和的数字区域。

但是在这之前，先要设置三个数据验证，分别是查询区域、开始月和结束月，以下分别说明。

1.查询区域的设置

这是数据验证最基本的用法之一，在【允许】栏选择序列，【来源】里选择对应的单元格区域即可，操作步骤见动图演示。

2.开始月的设置

与前一项不同，开始月设置为只能输入1到12之间的整数，并且设置提示信息，操作步骤见动图演示。

3.结束月的设置

与开始月的设置方法基本一致，只是需要将最小值设置为开始月所在的单元格，操作步骤见动图演示。

完成以上三个设置之后，首先来制作销量合计的计算公式。

要实现按照查询区域、开始月和结束月这三个条件进行合计的公式思路不是唯一的，这次我们使用比较常用的SUM-OFFSET函数组合，公式为：

=SUM(OFFSET(A1,MATCH(B16,A2:A14,0),B17,1,B18-B17+1))

这个功能的关键是OFFSET，在以前的教程中介绍过，OFFSET有五个参数，分别是起点、行偏移量、列偏移量、区域高度（行数）和区域宽度（列数）。不清楚这个函数的同学，可以学习这篇教程《Excel进阶之路必学函数：动态统计之王——OFFSET（上篇）》

在本例中，我们以A1作为起始位置，行偏移量用MATCH(B16,A2:A14,0)来确定，也就是要查找的区域所在的行，列偏移量直接使用开始月份所对应的数字，区域高度为1，因为都是针对单个区域进行统计，所以区域宽度就是结束月-开始月+1，这里面就是一些简单的数字问题了。

简单验证一下，公式结果是正确的。

最后一步就是利用条件格式突出显示要统计的单元格。

设置条件格式，大致需要三步，首先就是新建规则；

依次点击【开始】-【条件格式】-【新建规则】

然后设置公式：

在编辑格式规则中，选中【使用公式确定要设置格式的单元格】，输入预先编辑好的公式，再点【格式】进行设置。

案例中用的公式为：

=AND($A2=$B$16,COLUMN(A2)>=$B$17,COLUMN(A2)<=$B$18)

（稍后会说明这个公式的含义）

设置格式就很简单了，和平时设置单元格格式的方法是一样的，包含数字格式、字体、边框以及填充色，本例中只是设置了填充色，选择一种反差比较大的颜色效果会更好。

点两次确定退出条件格式的设置界面。

最后一步就是设置条件格式的生效范围（如果是先选择了数据区域再设置条件格式的话，这一步就无需进行了）。

打开管理规则，可以看到已经设置完成的规则，以及每个规则的应用范围。

调整规则的生效范围就能看到突出显示的效果了，操作步骤如图所示。

以上就是设置条件格式的步骤，最后简单说一下这个公式的意思。

本例公式使用了AND，里面有三个参数，也就是三个条件，只有当三个条件同时成立时，才会按照设置的格式去显示。

在公式=AND($A2=$B$16,COLUMN(A2)>=$B$17,COLUMN(A2)<=$B$18)中，条件1是$A2=$B$16。A2是数据源中的区域，B16是查询条件中的区域，这个条件就是判定查询条件的区域和数据源中的区域是否一致。

重点是$在其中的作用，由于格式的应用区域是$B$2:$M$14，而各销售区域名称只在A列存在，因此要在列号前加$。

确定了哪一行要突出显示后，还需要根据起始月份和终止月份来确定这一行中的哪几列符合条件。

于是条件2和条件3就分别用列号与这两个月份值作比较。

条件2：COLUMN(A2)>=$B$17

条件3：COLUMN(A2)<=$B$18

总结：今天分享的案例是一个综合性非常强的应用，涉及到数据验证的一些知识点，动态区域求和的公式套路，以及条件格式的应用。教程内容难度适中，所用到的知识点都非常实用，希望大家能够多加练习。灵活利用Excel的这些功能，可以设计出各种带查询功能的统计表，大大提高工作效率。

本文配套的练习课件请加入QQ群：1043683754下载。

做Excel高手，快速提升工作效率，部落窝教育《一周Excel直通车》视频和《Excel极速贯通班》直播课全心为你！

扫下方二维码关注公众号，可随时随地学习Excel：

相关推荐：

Column函数的应用《会用Column吗？ 它让公式不那么笨。》

用条件格式制图《新同事用条件格式制作的图表，竟然比我的还好看？》

OFFSET函数的应用《Excel进阶之路必学函数：动态统计之王——OFFSET（上篇）》

MATCH函数的应用《MATCH：函数哲学家，找巨人做伴。新出道必学！》