excel表格制作教程:反向求和技术

之前的文章中,我们给出了一个公式,能够求出一列数值中从最后一个数值开始向上数5个数值的和,忽略其中的空格。本文给出了一个更简洁的公式,并且可以指定求后面的X个数值之和。

如下图1所示,假设我们要求这列数值后面5个数值之和,即16+2+5+6+1=30,注意,空格不算在内。

图1

如下图2所示,在单元格C2中指定要求和的数字的个数,在单元格C5中输入数组公式:

=IFERROR(SUM(OFFSET($A$1,LARGE(IF($A$1:$A$15>0,ROW($A$1:$A$15)),C2)-1,0,ROWS($A$1:$A$15),1)),”没有这么多数字”)

图2

公式中,使用OFFSET函数确定要求和的单元格区域,其中的关键部分是:

LARGE(IF($A$1:$A$15>0,ROW($A$1:$A$15)),C2)-1

IF函数判断单元格区域A1:A15中的值是否大于0,如果大于0,则返回该单元格所在的行号,否则返回FALSE,即:

LARGE({1;2;FALSE;4;5;6;7;FALSE;9;10;11;FALSE;FALSE;14;15},C2)-1

如果我们要求最后5个(单元格C2中的值)数字之和,那么可以看出,要开始求和的单元格对应的值的所在的行数从大到小排在第5位。也就是说,使用LARGE函数获取数组中第5大的值,即对应着要开始求和的单元格所在的行号,减去1,得到OFFSET函数从单元格A1开始到达开始求和的单元格要偏移的行数。即:

LARGE({1;2;FALSE;4;5;6;7;FALSE;9;10;11;FALSE;FALSE;14;15},5)-1

其中的FALSE对应着空单元格。转换为:

9-1

结果为:

8

代入主公式中得到:

=IFERROR(SUM(OFFSET($A$1,8,0,ROWS($A$1:$A$15),1)),”没有这么多数字”)

转换为:

=IFERROR(SUM(OFFSET($A$1,8,0,15,1)),”没有这么多数字”)

转换为:

=IFERROR(SUM($A$9:$A$23),”没有这么多数字”)

即:

=IFERROR(SUM({16;2;5;0;0;6;1;0;0;0;0;0;0;0;0}),”没有这么多数字”)

得到结果:

30

今天的分享就到这里,非常感谢你的耐心阅读,希望对你有所启发。最后,大家可以通过坚果云分享内容,越分享越快乐

阅读已结束,喜欢的话就点个赞吧
注册坚果云网盘
还有其他问题,可以咨询小坚果咨询小坚果
赞(0)
网站部分内容和图片来源于网络如有侵权请联系我们删除:企业网盘坚果云的干货分享 » excel表格制作教程:反向求和技术
分享到: 更多 (0)
excel表格制作教程:反向求和技术

本文链接:excel表格制作教程:反向求和技术https://content.jianguoyun.com/23544.html

评论 抢沙发

评论前必须登录!

 

坚果云 便捷、安全的网盘产品

免费试用下载客户端