当前位置:首页 > 办公 > Excel教程 > 正文内容

Excel数据透视表反向求和技术

酷网3周前 (10-06)Excel教程24

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

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

如下所示,在单元格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)),”没有这么多数字”)

公式中,使用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

扫描二维码推送至手机访问。

版权声明:本文章来源于互联网,由八酷网收集发布,如需转载请注明出处。

本文链接:https://www.i8ku.com/2021/39413.html

分享给朋友:

相关文章

电脑Excel文件损坏该怎么办?电脑Excel文件损坏修复详细步骤

电脑Excel文件损坏该怎么办?电脑Excel文件损坏修复详细步骤

  Excel文件是我们平时使用的一个必不可少的工具,我们经常在生活或是工作中用到它,如果我们碰到Excel文件突然损坏了该怎么处理呢?下面请遇到同样问题的小伙伴跟我一起解决Excel...

如何使用Excel 2010做数据分析工具?

如何使用Excel 2010做数据分析工具?

  Office一直是我们最常用的办公软件,我们经常会使用office中自带的Excel表格工具对数据进行一个处理,其实Excel不仅仅可以储存和管理数据,还可以针对数据做一些简单的数...

排名函数(Rank)的使用

排名函数(Rank)的使用

同学们,通过之前的学习我们已经了解了几个简单函数的用法,但学习、工作中我们还会用到其他的函数功能,比如也经常会用到的排名函数,这个函数的名称是Rank。 下面让我们开始了解一下RANK函数。找到“公式选项卡”下的“插入函...

Excel中rank函数怎么用

Excel中rank函数怎么用

你还在为Excel中rank函数怎么用而苦恼吗,今天小编教你Excel中rank函数怎么用,让你告别Excel中rank函数怎么用的烦恼。 经验主要从四方面对Excel函数进行讲解,1.函数的含义,2.函数的语法格式,3...

领导不喜欢看“间断”的excel排名,RANK换成SUMPRODUCT!

领导不喜欢看“间断”的excel排名,RANK换成SUMPRODUCT!

前几天了Excel RANK.EQ(RANK)总排名、SUMPRODUCT分类排名,有朋友说,RANK和RANK.EQ排出来的名次,如果有相同的数值会出现相同的排名,再继续排下去,会出现名次“间断”的情况,比如,两个第三...

Excel怎么输入部分相同的数据?Excel输入部分相同数据

Excel怎么输入部分相同的数据?Excel输入部分相同数据

Excel怎么输入部分相同的数据?Excel输入部分相同数据原标题:Excel怎么输入部分相同的数据?,Excel输入部分相同数据,Excel输入部分相同数据的方法技巧是什么?  1.自动填充  如果数据含有固定内容,但同时又是有规律的数据...

发表评论

访客

◎欢迎参与讨论,请在这里发表您的看法和观点。