|
点击照片 认识双11 1元抢购 Excel、Word、PPT等活动
编按：
哈喽，大众好！今天向大众分享一个营销统计表模板。该模板支持动态查找功能，并且在查找的时候，相应数据会变色，如此，查找结果一目了然。统计模板将运用SUM、AND、COLUMN、MATCH、OFFSET函数并结合要求格式和数据验证。赶紧来瞧瞧吧！
今天要和大众分享的是一个能够动态查找营销数据的统计模板。何为动态查找呢，效果如动图所示： 要做这个模板，需要两部分工作，公式和要求格式。公式用来实现营销数据汇总，要求格式用来改变单元格颜色明显求和的数字区域。然则在这之前，先要设置三个数据验证，分别是查找区域、起始月和结束月，以下分别说明。温馨提示加入下面QQ群：1043683754下载教程配套的课件练习操作
1.查找区域的设置这是数据验证最基本的用法之一，在【准许】栏选取序列，【源自】里选取对应的单元格区域就可，操作过程见动图演示。 2.起始月的设置与前一项区别，起始月设置为只能输入1到12之间的整数，并且设置提示信息，操作过程见动图演示。 3.结束月的设置与起始月的设置办法基本一致，只是需要将最小值设置为起始月所在的单元格，操作过程见动图演示。 完成以上三个设置之后，首要来制作销量合计的计算公式。要实现根据查找区域、起始月和结束月这三个要求进行合计的公式思路不是独一的，这次咱们运用比较常用的SUM-OFFSET函数组合，公式为：=SUM(OFFSET(A1,MATCH(B16,A2:A14,0),B17,1,B18-B17+1))这个功能的关键是OFFSET，在以前的教程中间商绍过，OFFSET有五个参数，分别是起点、行偏移量、列偏移量、区域高度（行数）和区域宽度（列数）。不清楚这个函数的朋友，能够学习这篇教程《10大经典必学函数之一：以一顶五，这位动态统计之王，绝非浪得虚名！【Excel教程】》在本例中，咱们以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的这些功能，能够设计出各样带查找功能的统计表，大大加强工作效率。  温馨提醒：
请点到名的粉丝们，抽时间来免费领取全套Excel课程学习。
详情请点击链接：宠粉送课第1期：阅读、分享、留言最多的粉丝，送课给你们！ 扫一扫添加老师微X
咨询Excel课程学习
Excel教程关联举荐 怎么用Excel图表表达前后两月数据的增减变化？来瞧瞧这个最佳答案……【1元秒杀】Excel、Word、PPT、PS、摄影、去水印等视频教程瞬间搞定报销表花费汇总！这个Excel求和公式太牛了从多人拼单的订单中提取各顾客的手机号【Excel教程】Excel表格打印到一半没墨了怎么办？摇一摇呀！
让工作提速百倍的「Excel极速贯通班」
↓ 点击阅读原文，可免费试听。
| 
发表于 2024-10-1 16:18:42