在Excel里按动态区域求和的五种常用思路，学会三个就能纵横职场！

Excel基础学习园地

公众号“Excel基础学习园地”是一个免费发布Excel基础知识、函数应用、操作技巧、学习方法等资讯的公众号，请点击上方“Excel基础学习园地”添加关注，方便我们每天向您推送精彩资讯。

扫码申请加Excel微信群（免费）

交流心得  解决问题

验证信息：Excel

公众号回复2016，可以获得office2016的下载链接

按动态区域求和是日常工作中经常会遇到的一类问题（制作动态图表也经常会遇到），具体演示如动画所示：

简单来说，就是根据选择的开始日期和结束日期，对数据源里符合条件的数据进行汇总。

案例选自【老菜鸟的班】一道课后作业，数据源下载地址：

https://pan.baidu.com/s/1BkW5y0WkIIkvg5sdXPcHfA

思路1：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这个区域。

思路2：SUM数组公式

第二个公式是：

=SUM(($A$2:$A$29>=$J$1)*($A$2:$A$29<=$J$2)*B2:B29)

沿用公式一的思路，通常都可以将SUMPRODUCT函数替换为SUM函数，不过要以数组公式的输入方式来完成，也就是同时按着Ctrl、shift和回车键完成输入，公式两端自动添加大括号。

思路3：SUMIFS

第三个公式是：

=SUMIFS(B2:B29,$A$2:$A$29,'>='&$J$1,$A$2:$A$29,'<='&$J$2）

>='&$J$1是条件1，也就是大于等于开始日期，注意这种条件的写法，符号要加引号，同时使用&连接单元格；<='&$J$2是条件2，表示小于等于结束日期。

思路4：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完成求和，就是这个公式的来龙去脉。

思路5：SUM+INDEX

第五个公式是：

=SUM(INDEX(B2:B29,MATCH($J$1,$A2:$A29,0)):INDEX(B2:B29,MATCH($J$2,$A2:$A29,0)))

这个公式的用法就比较稀罕了，利用了INDEX的一个鲜为人知的特性：

稀罕在何处呢？

只能引用这个函数说明中的一句话来解释：

函数 INDEX 的结果为一个引用，且在其他公式中也被解释为引用。根据公式的需要，函数 INDEX 的返回值可以作为引用或是数值。

例如，公式 

 CELL('width',INDEX(A1:B2,1,2)) 等价于公式 CELL('width',B1)

CELL 函数将函数 INDEX 的返回值作为单元格引用。

而在另一方面，公式 2*INDEX(A1:B2,1,2) 将函数 INDEX 的返回值解释为 B1 单元格中的数字。 

小结

就这个问题本身来说，使用条件求和的思路无疑是最佳解决方案，不过这个例子也是动态区域的典型案例，OFFSET函数和INDIRECT函数做构造动态区域方面有无可取代的地位（使用INDIRECT函数解决本例也是可以的），另外一个亮点就是INDEX的出现，本例中并没有显示出INDEX的优势，不过如果将问题再进一步复杂化，求和区域的确定增加两个条件的话：

其他几个公式都要做很大的改动才行，公式的复杂性也会增加，而INDXE依然如旧，有兴趣的朋友可以自己去测试一下。

最后这个复杂的区域求和问题，也是INDEX函数高级应用这节课的一个内容，就不在本文罗嗦了。