摘要： 有时需要在Excel表格中动态地反映筛选后数值的变化情况，如下图中F32单元格中的合计值会随着不同的筛选情况而变化。我们知道，用SUBTOTAL函数可以求出筛选后可见单... 有时需要在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各单元格中的数值的数组。
在Excel中如何进行无并列地排名
我们在Excel中用RANK函数进行排名时，相同的数值其排名是相同的，即出现并列的情况。如果需要在排名时不考虑并列，可以用下面的公式，假如数据在A2:A20中，在B2:B20中进行排名，在B2中输入公式并向下拖： =RANK(A2,$A$2:$A$21) COUNTIF($A$2:A2,A2)-1 说明：对于并列的情况
③ 用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使用TEXT函数转换文本
TEXT函数可将数值转换为文本，并可使用户通过使用特殊格式字符串来指定显示格式。需要以可读性更高的格式显示数字或需要合并数字、文本或符号时，此函数很有用。例如，假设单元格A1含有数字23.5。若要将数字格式设置为人民币金额，可以使用以下公式： =TEXT(A1,"￥0.00") 在本例中，Exc
