Excel里的动态求和，竟然难倒了80%的人？

关键字：动态求和；函数；Excel技巧

栏目：函数

全文934字，预计2分钟读完

哈喽，小伙伴们，你们好呀~

美好的一周又开始啦，每天学点Excel，早点下班不是梦！

前段时间，读者群中有位小伙伴提出想用公式计算月份累计求和，问题如下图所示：

说到求和，可能大家都觉得很简单，不就是求和？

但这个问题，委实是难倒了一部分人。

小伙伴想要实现的效果图示如下：

接着我们来讲解一下是如何实现这种动态区域求和的。

在J4单元格中输入公式：

=SUM(INDIRECT("$E$7:"&ADDRESS(7,MATCH(SUBSTITUTE(J3,"累计",""),E6:P6,0)+4)))

回车确定即可。

别看公式那么长一串，实际计算的结果和=SUM(E7:G7)相等。（前提是J4单元格的值等于“3月累计”）

为了让大家更快地理解这个函数，我们把公式进行了拆分标注解释。

以=SUM(E7:G7)公式为例，我们知道变量在“E7:G7”这个区域上，所以我们开始对公式中的区域下手了，分别使用SUBSTITUTE、MATCH、ADDRESS、INDIRECT四大函数来变身动态区域，因为SUM函数的区域需要根据下图J3单元格的内容动态变化进行求和，所以我们需要通过①②③④的嵌套函数公式让区域变成根据条件动态更新，具体参考下图右下角函数语法解释理解。

下面我们分段拆解一下嵌套函数中每个函数在公式中所起的用途，如果有不理解函数语法意思的小伙伴记得看上图解释。

SUBSTITUTE替换文本函数是将J3单元格中“累计”文本替换为空，这样我们替换完成后的值可以在第6行中的月份字段进行匹配。

如果这里不想使用SUBSTITUTE函数来替换，可以使用之前跟大家分享过的自定义单元格格式的方法。

输入月份后，设置自定义单元格格式，自定义类型中输入“@”月份””确定即可。

这样显示的是带累计的内容，实际内容编辑栏只有月份，这个方法在公式提取单元格内容和批量给数据添加单位时经常用到。

MATCH函数查找目标值所在区域中的位置，而我们需要查找的目标值就是SUBSTITUTE函数替换后的月份值即下图的R5单元格中的内容“3月”返回查找区域E6:P6中所在的位置。

ADDRESS函数是返回单元格地址，这里我们只简单的用到两个参数，已知第一参数是数据对应的第7行，第二参数返回列数因为我们的数据是从E列开始，所以要加上前4列最后加上MATCH值所在位置就是我们需要查找的单元格位置$G$7。

最后就是用INDIRECT返回引用区域，因为区域中的起始位置$E$7单元格是固定不变的，所以我们可以对E7单元格进行绝对引用固定，后面&就是R7单元格中的$G$7。当我们更改J3单元格中的内容时对应的R8单元格中值就会动态求和显示。

最后我们只需要将刚刚拆分的公式全部嵌套替换组合成一个公式就可以实现用公式动态根据条件去查找的效果了。

怎么样，你学会了吗？