销售MM用excel 做了张会 自动变色 的动态查询报表,老板都看傻了【excel教程】

编按:哈喽,大家好!今天向大家分享一个销售统计表模板。该模板支持动态查询功能,并且在查询的时候,相应数据会变色,如此,查询结果一目了然。统计模板将使用SUM、AND、COLUMN、MATCH、OFFSET函数并结合条件格式和数据验证。学习更多技巧,请收藏关注部落窝教育excel图文教程。


今天要和大家分享的是一个可以动态查询销售数据的统计模板。何为动态查询呢,效果如动图所示:

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

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

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

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,这里面就是一些简单的数字问题了。

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

最后一步就是利用条件格式突出显示要统计的单元格。学习更多技巧,请收藏关注部落窝教育excel图文教程。

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

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

然后设置公式:

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

案例中用的公式为:

=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的这些功能,可以设计出各种带查询功能的统计表,大大提高工作效率。学习更多技巧,请收藏关注部落窝教育excel图文教程。


****部落窝教育-excel动态查询统计表****

原创:老菜鸟/部落窝教育(未经同意,请勿转载)

更多教程:部落窝教育

(0)

相关推荐