按动态区域求和是日常工作中经常会遇到的一类问题(制作动态图表也经常会遇到),具体演示如动画所示:
简单来说,就是根据选择的开始日期和结束日期,对数据源里符合条件的数据进行汇总。
(案例选自【老菜鸟的班】一道课后作业,数据源下载地址:https://pan.baidu.com/s/1BkW5y0WkIIkvg5sdXPcHfA)
这个题目发出以后,同学们一共给出了五种常用的公式,以下为大家进行分享!
SUMPRODUCT
第一个公式是:=SUMPRODUCT(($A$2:$A$29>=$J$1)*($A$2:$A$29<=$J$2)*B2:B29)
这是最为常用的条件求和公式的套路了:SUMPRODUCT(第一组条件*第二组条件*求和数据)
其中第一组条件为:$A$2:$A$29>=$J$1,表示日期列中大于等于开始日期的数据;
其中第二组条件为:$A$2:$A$29<=$J$2,表示日期列中小于等于结束日期的数据;
求和数据就是B2:B29这个区域。
关于SUMPRODUCT的详细介绍可以参阅:sumproduct函数最通俗易通的讲解,不容错过!
SUM数组公式
第二个公式是:=SUM(($A$2:$A$29>=$J$1)*($A$2:$A$29<=$J$2)*B2:B29)
沿用公式一的思路,通常都可以将SUMPRODUCT函数替换为SUM函数,不过要以数组公式的输入方式来完成,也就是同时按着Ctrl、shift和回车键完成输入,公式两端自动添加大括号。
原理不再赘述,有关于数组公式的基础知识,可以参阅:Excel数组公式入门
SUMIFS
第三个公式是:=SUMIFS(B2:B29,$A$2:$A$29,">="&$J$1,$A$2:$A$29,"<="&$J$2)
通过前面两个公式可以了解到,本例实际上可以视为条件求和,既然是条件求和,当然少不了SUMIF函数,不过本例的条件不是一个而是两个,所以要用SUMIFS函数来完成。
公式结构:SUMIFS(实际求和区域,条件区域1,条件1,条件区域2,条件2)
B2:B29是实际求和的区域;$A$2:$A$29是条件区域,本例中的两个条件区域都是A列;">="&$J$1是条件1,也就是大于等于开始日期,注意这种条件的写法,符号要加引号,同时使用&连接单元格;<="&$J$2是条件2,表示小于等于结束日期。
关于SUMIF函数在公众号之前发了很多篇文章,有兴趣的朋友可以参考本文介绍的方法来进行搜索:守着一座金山,却在到处寻找!今天告诉你怎么在公众号挖宝……
SUM+OFFSET
第四个公式是:=SUM(OFFSET($A$1,MATCH($J$1,$A$2:$A$29,0),COLUMN(A1),$J$2-$J$1+1,1))
之前的三个公式其实都是利用了条件求和的套路,并不是按照动态区域的思路来处理的,通常遇到有关于动态区域,少不了OFFSET这个引用函数,对于大多数朋友来说,OFFSET函数显得难以理解,简单来说一下OFFSET吧。
OFFSET(起始位置,行偏移量,列偏移量,区域高度,区域宽度),这个函数一共五个参数,每个参数用起来都是非常灵活多变,这也是OFFSET函数难于掌握的一个原因。
就本例而言,我们需要使用OFFSET函数来确定一个动态区域,首先确定这个区域的起点,用$A$1来作为起点的话,实际要求和的位置需要根据开始日期进行调整,也就是行偏移量,这时就用到了MATCH这个专门定位的函数,MATCH($J$1,$A$2:$A$29,0)这部分就是用MATCH函数来确定开始日期在A列当中的第几个位置,例如,开始日期是3月13日时,就位于日期中的第六个位置,那么行偏移量就是6,表示A1向下6行。
列偏移量用的是COLUMN(A1)来确定,因为数据源中各区域的位置与结果中的位置一样,只需要随着公式右拉发生变化即可。
(注:实际上将基点$A$1改为A$1的话,列偏移这个参数是可以省略的,这是利用把基点混合引用实现了调整列的位置。不过这样写的话,公式对于新手来说更加难以理解。)
最后是区域的高度和宽度,$J$2-$J$1+1用这个作为高度,也就是结束日期-开始日期+1,具体的天数作为高度。
宽度当然就是1了。
用OFFSET指定了一个区域之后,再外面加个SUM完成求和,就是这个公式的来龙去脉。
不过仅靠这么解释,还是有些朋友听不明白,那么可以通过这篇文章先去了解一下OFFSET函数的基本知识:offset函数的使用方法
SUM+INDEX
第五个公式是:=SUM(INDEX(B2:B29,MATCH($J$1,$A2:$A29,0)):INDEX(B2:B29,MATCH($J$2,$A2:$A29,0)))
这个公式的用法就比较稀罕了,利用了INDEX的一个鲜为人知的特性,公式的套路倒是很常见,就是INDEX+MATCH这对经典组合,可以通过本文来来了解:index+match函数组合在excel中的应用
那么稀罕这何处呢?
只能引用这个函数说明中的一句话来解释:
函数 INDEX 的结果为一个引用,且在其他公式中也被解释为引用。根据公式的需要,函数 INDEX 的返回值可以作为引用或是数值。例如,公式 CELL("width",INDEX(A1:B2,1,2)) 等价于公式 CELL("width",B1)。CELL 函数将函数 INDEX 的返回值作为单元格引用。而在另一方面,公式 2*INDEX(A1:B2,1,2) 将函数 INDEX 的返回值解释为 B1 单元格中的数字。
对于这段话,涉及到很多基础概念,如果你无法理解的话,赶快补课:公式函数入门基础知识1:公式和函数
小结
就这个问题本身来说,使用条件求和的思路无疑是最佳解决方案,不过这个例子也是动态区域的典型案例,OFFSET函数和INDIRECT函数做构造动态区域方面有无可取代的地位(使用INDIRECT函数解决本例也是可以的),另外一个亮点就是INDEX的出现,本例中并没有显示出INDEX的优势,不过如果将问题再进一步复杂化,求和区域的确定增加两个条件的话:
其他几个公式都要做很大的改动才行,公式的复杂性也会增加,而INDXE依然如旧,有兴趣的朋友可以自己去测试一下。
最后这个复杂的区域求和问题,也是INDEX函数高级应用这节课的一个内容,就不在本文罗嗦了。
联系客服