如何在 excel 中将 subtotal 与 if 一起使用?
这个公式简直就是神奇。
如果要在 Excel 中使用 IF(条件)编写 SUBTOTAL 公式,则需要使用多个函数来执行此操作。但在此之前,让我们先了解一下此示例中的数据。
在上面的示例中,您有三列:
- 姓名
- 年龄板
- 性别
当您从“年龄板块”列中筛选板块时,它会在单元格 F1 中显示女性数量。所以这意味着我们有一个公式可以显示过滤值的数量,但有条件。
我们有公式:
=SUMPRODUCT((C2:C41=E1)*(SUBTOTAL(3,OFFSET(C2,ROW(C2:C41)-MIN(ROW(C2:C41)),0))))
了解 SUBTOTAL IF 公式
此公式使用五个函数:SUMPRODUCT、SUBTOTAL、OFFSET、ROW 和 MAX。因此,要理解这个公式,我们需要将其分为几个部分。
1.线(C2:C41)-2.0)
公式的这一部分使用 MIN 和 ROW 函数。
- 在 ROW 中,我们引用了“性别”列,它返回一个行号数组。
- 之后,MIN 获取该数组或行号并返回最小行号。这就是为什么我们在公式的这一部分有 2。
2.线路(C2:C41)
在这一部分中,我们只有 ROW 函数,它返回行号数组。
3. 偏移量(C2,线(C2:C41)-MIN(线(C2:C41)),0)
现在我们有了 OFFSET 函数。它可以帮助您使用单元格引用作为起点创建对范围的引用。在引用参数中,我们引用了单元格 C2,这是性别范围开始的第一个单元格。
在lines参数中,我们有上面前两部分讨论的公式的这一部分。之后,在 cols 参数中,我们使用了 0。这样,OFFSET 返回“性别”列中所有值的数组。
4. 小计(3,偏移量(C2,行(C2:C41)-MIN(行(C2:C41)),0))
我们使用了 SUBTOTAL 中 OFFSET 返回的数组。而在function_num中我们使用了3,它告诉SUBTOTAL使用COUNTA函数进行计算。
当您在“Age Slab”列上使用过滤器时,公式的 SUBTOTAL 部分将返回一个包含 0 和 1 的数组。
在此表中,我们有 1 表示与我们应用过滤器的值等效的值。请参阅下面的示例:
5. (C2:C41=E1)
公式的这一部分通过测试条件返回一个数组。在这种情况下,我们测试范围值是否为“Female”,并在数组中返回 TRUE 和 FALSE。
在此表中,“女性”值为 TRUE,其他值为 FALSE。
7.最后一部分
最后,我们在 SUMPRODUCT 中有两个表。我们在这些数组之间还有一个星号运算符。
当我们将两个表相乘时,我们得到一个包含 0 和 1 的表。在此表中,一 (1) 代表性别中的“女性”值,21-30 代表“年龄层”。
最后,SUMPRODUCT 使用该数组返回总和。此总和是当您在“年龄范围”列中筛选 21-30 时,性别列中值为“女性”的单元格数。