Excel数据透视表反向求和技术
时间:2023-01-25 08:04:19
之前一篇文章中我们给出了一个公式,能够求出一列数值中从最后一个数值开始向上数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
![](/images/zang.png)
![](/images/jiucuo.png)
猜你喜欢
微软商城Office365降价了 特价299元还送229元耳机
![](https://img.aspxhome.com/file/2023/5/15475_0s.jpg)
word图片设置在哪_怎么设置图片尺寸大小
Word如何一键去除超链接 Word一键去除超链接方法
![](https://img.aspxhome.com/file/2023/8/22598_0s.jpg)
excel表格怎么快速插入多列单元格?
![](https://img.aspxhome.com/file/2023/8/40088_0s.jpg)
excel单元格中格式太多该怎么解决?
![](https://img.aspxhome.com/file/2023/7/a141607_0s.jpg)
word中图片怎么设置边缘透明度?word羽化效果的使用方法
![](https://img.aspxhome.com/file/2023/2/27462_0s.jpg)
Excel表格如何冻结窗口?冻结窗口的类型和方法
![](https://img.aspxhome.com/file/2023/5/41465_0s.jpg)
word怎么竖向选择文字?
![](https://img.aspxhome.com/file/2023/5/34665_0s.gif)
word打不开怎么办?修复损坏的Word文档的三种方法
![](https://img.aspxhome.com/file/2023/7/28897_0s.jpg)
如何在Office 2019中更改视图?
Word2010中如何使用"悬停时显示文档工具"提示功
![](https://img.aspxhome.com/file/2023/0/21490_0s.jpg)
Win10显卡驱动怎么更新?Win10显卡驱动更新方法介绍
![](https://img.aspxhome.com/file/2023/3/52323_0s.png)
excel怎么加批注
![](https://img.aspxhome.com/file/2023/5/37185_0s.png)
word2013设置每行字数的方法
![](https://img.aspxhome.com/file/2023/9/25359_0s.jpg)
excel表格设置数据有效性时怎么却掉表头?
![](https://img.aspxhome.com/file/2023/9/40509_0s.jpg)
word2007怎么添加上下标
![](https://img.aspxhome.com/file/2023/8/18098_0s.jpg)
Word 2013 DIY文字水印的方法
![](https://img.aspxhome.com/file/2023/1/20171_0s.jpg)
联想win10系统改win7要怎么设置BIOS?
![](https://img.aspxhome.com/file/2023/2/51562_0s.png)
word2013橡皮擦如何使用
Win10ipv4没有网络访问权限怎么办?Win10ipv4没有网络访问权限的解决方法
![](https://img.aspxhome.com/file/2023/8/52748_0s.jpg)