前面写了三篇文章,对动态求和的实现做了拆分,分别讲了关键的三个函数的用法。本文对动态求和的实现做总结,帮助大家理清动态求和的实现逻辑。

先看回原始数据

原始数据

原始的数据位于B1:E23 区域,即共有23行。通常当表格中的数据有增加时,都是会把记录增加到末尾。如上图,如果有20210801的数据,那将会写到第24行上。

按照当前共23的数据,理论上,如果我们要求最后7天的销量,只要一个简单的求和公式就可以实现:

=sum(c17:c23)

但是当数据增加了的时候,这条公式就需要变成:


=sum(c18:c24)

=sum(c19:c25)

=sum(c20:c26)

...

可以看出,sum内部的区域是需要动态变化的,公式也就需要每天更新,这是一种很机械和低效的做法。

但是我们可以换种思路,因为数据一直都在增加,我们要统计的始终都是最后的7条数据。准确地说,我们需要的是C列最后7行数据的和。这个是固定不变的。这个思路可以用offset函数实现(offset函数的使用可以参考这里:Excel offset 函数怎么用),函数如下:

=sum(offset(c23,0,0,-7,1))

这个函数与

=sum(c17:c23)

是等价的。

当最后一行变成了24行的时候,函数就需要改为

=sum(offset(c24,0,0,-7,1))

然后是


=sum(offset(c25,0,0,-7,1))

=sum(offset(c26,0,0,-7,1))

=sum(offset(c27,0,0,-7,1))

...

可以看到,改变的部分变少了,只有offset函数里面的第1个参数。而且这个参数看起来是比较容易取到的。因为它总是表示C列最后一个非空行所在的位置

到这里,变化的部分进一步减少了,我们只需要得到C列最后一个非空行是多少就可以了,因为C列是不变的(我们需要统计的数据总是在C列)。而统计非空行的函数大家就熟悉了,我前面也写了文章有讲过,那就是COUNTA函数(看清楚最后有个“A”的,不是COUNT函数,这两个函数的作用是不同的)。也就是用count(c:c) 就能得到最后一行是多少。

那么,我们知道C列最后一行是C+COUNTA(C:C),怎么得到如C23 这种格式的单元格地址呢?

这就需要用到address函数了,顾名思义,address函数就是用来得到address的(英文单词address的中文含义就是“地址”)。address函数的用法在前面已经写了文章来详细讲了Excel 如何进行动态求和(一)。我们用以下的公式就能得到C23

=address(counta(c:c),3,4)

而当C列的数据填充到24行时,上面的公式得到的就是C24;

当C列的数据填充到25行时,上面的公式得到的就是C25;

当C列的数据填充到26行时,上面的公式得到的就是C26;

...

公式的内容完全没有变化,但是得到的结果却变了,而且是随着C列数据的增加而变化的。我们的需求已经初步实现了!

但是,address函数返回的结果是一个文本,而offset函数的第一个参数要的不是文本,是单元格引用!所以单有address函数还不行,还需要indirect函数来把这个文本转成单元格引用:

=indirect(address(counta(c:c),3,4))

这个indirect函数返回的结果就能放到offset函数的第1个参数来用了。它的具体用法可以看这篇文章:Excel 如何实现动态求和(二)

所以组合起来,这个动态求和的公式就是:

=sum(offset(indirect(address(counta(c:c),3,4)),0,0,-7,1))

可能有些朋友还不是很清楚,没关系,只是因为不熟练所以一时记不住而已,可以回到文章开头再看一遍,慢慢理解。也可以评论或私信我,我给大家答疑。

举报/反馈

学不可以已

813获赞 1561粉丝
为学,求知,传承,提升
关注
0
0
收藏
分享