如何在 excel 中将 subtotal 与 if 一起使用?

这个公式简直就是神奇。

如果要在 Excel 中使用 IF(条件)编写 SUBTOTAL 公式,则需要使用多个函数来执行此操作。但在此之前,让我们先了解一下此示例中的数据。

带 if 的小计

在上面的示例中,您有三列:

  • 姓名
  • 年龄板
  • 性别

当您从“年龄板块”列中筛选板块时,它会在单元格 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

在此表中,“女性”值为 TRUE,其他值为 FALSE。

7.最后一部分

最后,我们在 SUMPRODUCT 中有两个表。我们在这些数组之间还有一个星号运算符。

两幅作品的总和

当我们将两个表相乘时,我们得到一个包含 0 和 1 的表。在此表中,一 (1) 代表性别中的“女性”值,21-30 代表“年龄层”。

包含 0 和 1 的单个数组

最后,SUMPRODUCT 使用该数组返回总和。此总和是当您在“年龄范围”列中筛选 21-30 时,性别列中值为“女性”的单元格数。

sumproduct-通过使用数组返回总和

获取 Excel 文件

下载

添加评论

您的电子邮箱地址不会被公开。 必填项已用 * 标注