Excel中筛选后的条件求和公式 图解教程
时间:2022-04-22 14:15:16
有时需要在Excel表格中动态地反映筛选后数值的变化情况,如下图中F32单元格中的合计值会随着不同的筛选情况而变化。我们知道,用SUBTOTAL函数可以求出筛选后可见单元格的数值和或平均值等,因为SUBTOTAL函数会忽略不包括在筛选结果中的行。例如下图是筛选“商品名称=袜子”、“类别=男”时的结果,其中F32单元格中的公式为:
=SUBTOTAL(9,F10:F31)
但如果此时还需要动态地反映筛选后各城市的合计值,即进行筛选后的条件求和,仅仅使用SUBTOTAL函数无法实现。虽然用多条件求和公式可以得到计算结果,但我们的目的是要随着不同的筛选操作,能够动态实时地反映数据的变化情况,因而此处不宜使用多条件求和公式。关于多条件求和公式,可以参考本站《用公式进行多条件求和》一文。
要在筛选后进行条件求和,可以使用下面的几个公式。以下图为例,假如数据在A10:F31区域中。
1.统计筛选后“广州”的销售数量:
在B2单元格中输入公式:
=SUMPRODUCT(SUBTOTAL(9,OFFSET($F$10,(ROW($F$10:$F$31)-MIN(ROW($F$10:$F$31))),)),–($B$10:$B$31=A2))
说明:
① ROW($F$10:$F$31)-MIN(ROW($F$10:$F$31))返回一个包含22个数值的数组{0;1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17;18;19;20;21}。
② 用OFFSET函数返回包含F10:F31各单元格中的数值的数组。
③ 用SUBTOTAL函数返回包含筛选后可见单元格数值的数组,不可见单元格对应数组中的数值为0。如本例中返回包含22个数值的数组{359;535;0;0;0;297;0;308;0;0;0;1021;0;0;0;0;0;506;392;0;0;0}。
④ –($B$10:$B$31=A2)返回一个包含数值1和0的数组,其中值为“广州”的单元格对应数组中的数值为1。本例中返回包含22个数值的数组{1;0;0;1;0;1;0;0;0;0;1;1;0;1;0;1;0;1;0;1;0;1}。
⑤ 最后用SUMPRODUCT函数返回上述两个数组的乘积和,得到所需结果。
另一个类似的数组公式为:
=SUM(SUBTOTAL(9,OFFSET($F$9,ROW($1:$22),))*($B$10:$B$31=A2))
该公式为数组公式,输入完毕后按Ctrl+Shift+Enter结束。
2.统计筛选后“广州”的记录数:
只需将上述公式中SUBTOTAL函数的参数“9”改为“3”即可。如在C2单元格中输入公式:
=SUMPRODUCT(SUBTOTAL(3,OFFSET($F$10,(ROW($F$10:$F$31)-MIN(ROW($F$10:$F$31))),)),–($B$10:$B$31=A2))
或数组公式:
=SUM(SUBTOTAL(3,OFFSET($F$9,ROW($1:$22),))*($B$10:$B$31=A2))
都可以返回数值4,表示筛选后有4条“广州”的记录
![](/images/zang.png)
![](/images/jiucuo.png)
猜你喜欢
excel怎么设置合同到期提醒
![](https://img.aspxhome.com/file/2023/3/55143_0s.gif)
我们也可以把图形改为平面图形
如何设置word 2013文档的段落缩进
![](https://img.aspxhome.com/file/2023/0/32850_0s.jpg)
excel中计算平均值的方法
Excel表格中怎么突出凸显查询到的数据行
Win10专业版安装Steam后提示无法联网更新怎么办?
![](https://img.aspxhome.com/file/2023/3/50343_0s.jpg)
Win10系统定位服务如何关闭?Win10 1909定位服务关闭方法
![](https://img.aspxhome.com/file/2023/0/46570_0s.png)
WORD如何批量删除错号和对号?word通过替换批量删除对号错号的方法
![](https://img.aspxhome.com/file/2023/4/22814_0s.jpg)
如何导出win10系统的日历备注等数据?
![](https://img.aspxhome.com/file/2023/5/46895_0s.jpg)
Word怎么调下划线和字间距?Word设置文字与下划线的间距教程
![](https://img.aspxhome.com/file/2023/6/31166_0s.jpg)
excel表格怎么设置表格线粗细
Word2003文档打印全接触
Word如何美化文档
excel向下填充快捷键使用基础教程
![](https://img.aspxhome.com/file/2023/1/a140621_0s.png)
在Word中画横线的简单技巧
![](https://img.aspxhome.com/file/2023/4/29884_0s.jpg)
iOS 14.5 Apple Music 新功能:新增“城市排行榜”和艺人详情页面动态效果
![](https://img.aspxhome.com/file/2023/5/45945_0s.jpg)
excel表格如何设置冻结某一行或几行窗口
excel 怎么去设计表格 excel数据输入之表格的设计图解教程
Win10更新完以后开机后黑屏怎么办
![](https://img.aspxhome.com/file/2023/4/51214_0s.png)
word 如何输入摄氏温度单位符号℃?
![](https://img.aspxhome.com/file/2023/0/35110_0s.png)