您的位置: 首页 > EXCEL技巧 > Excel函数 >

Excel中筛选后的条件求和公式

时间:2013-12-10 整理:docExcel.net

有时需要在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条“广州”的记录。

Excel中筛选后的条件求和公式 问题:Excel中筛选后的条件求和公式
回答:...动态地反映筛选后各城市的合计值,即进行筛选后的条件求和,仅仅使用SUBTOTAL函数无法实现。虽然用多条件求和公式可以得到计算结果,但我们的目的是要随着不同的筛选操作,能够动态实时地反映数据的变化情况,因而此处...
筛选求和汇总 问题:筛选求和汇总
回答:如图: 将公式 =SUMIF($C$4:C17,G4,$D$4:$D$17) 粘贴至E4单元格,如图: 将公式 =IF(F4="","",SUMIF($B$4:$B$17,F4,D4:D17)) 粘贴至I4单元格,如图: 选中H4:I4单元格,对准右下角,光标成“+”状时双击,效果如图: OK 建议以后上图...
怎么把表格求和里面的负数隐藏掉 问题:怎么把表格求和里面的负数隐藏掉
回答:在目标单元格内输入如下公式:=SUMIF(求和区域,"=0",求和区域)其中求和区域为所有求和的单元格区域,如A2:A100等。原公式格式为=SUMIF(条件区域,条件,求和区域)其中,条件区域为对应条件需要筛选的条件区域,求和区域为...
怎样对区域中最大的几个数求和 问题:怎样对区域中最大的几个数求和
回答:我们可以用SUM和LARGE函数来对区域中最大几个数值求和。例如在A1:A20中包含一些数值,可以使用下列公式: =SUM(LARGE(A1:A20,{1,2,3})) 这个公式对A1:A20区域中最大的3个数求和,要说明的是,此处公式仅仅对最大的3个数求和,如果...
实例讲解Excel2013隔行求和以及公式推导 问题:实例讲解Excel2013隔行求和以及公式推导
回答:本篇教程主要介绍Excel2013表格隔行求和的方法,以及公式推导,办公人员在做汇总时,经常需要奇数月和偶数月分开统计。这也是常说的excel隔行求和的典型应用,下面就从一个简单例子来具体分析。 ①先来看看隔行求和之奇...
《Excel表格自动求和全攻略》单个公式求和 问题:《Excel表格自动求和全攻略》单个公式求和
回答:...强大,我们可以利用Excel完成各种公式的运算,比如Excel求和,求积,求差,求平均值等等,无论多么复杂的数据在Excel表格中都可以快速完成运算。本次Excel办公网就来为大家来演示在Excel2007中求和的详细步骤。 Excel2007中单个...
相关推荐: