如何在Microsoft Excel中使用SUBTOTAL函数

要使用 SUBTOTAL 函数,请使用以下语法创建一个公式:SUBTOTAL(function_number, reference 1, reference 2,...)。您还可以通过选择行,然后单击“数据”选项卡来使用小计功能。在大纲下拉菜单中,选择“小计”。

在 Microsoft Excel 中获取相关项目组的小计很容易。事实上,你有两种方法可以做到这一点。您可以使用 SUBTOTAL 功能或 Subtotal 功能,以效果最好的为准。我们会告诉你怎么做。

Excel中的小计是什么?

如前所述,Excel 中的 SUBTOTAL 允许您将列表中相同或相关的项目分组,并使用函数来计算值。例如,您可以使用它按月计算销售额或按学生计算平均成绩。您还可以按产品添加库存或计算下周到期的账单数量。

通过创建公式或使用小计功能使用小计功能,您只需几个步骤即可获得所需的计算。

使用 Excel 小计功能

您可以使用 Excel SUBTOTAL 函数创建一个公式,该函数可以灵活地包含或排除您隐藏的行。

公式的语法SUBTOTAL(function_number, reference 1, reference 2,...)是需要前两个参数的地方。您可以根据需要对其余参数使用其他单元格引用或命名范围

function_number参数允许您使用其对应的编号插入 11 个函数之一。前 11 个包括隐藏行,而后 11 个不包括它们。因此,只需插入所需功能的编号以及您希望如何处理隐藏行。

功能 包括隐藏行 排除隐藏行
平均 1 101
数数 2 102
康塔 3 103
最大限度 4 104
最小 5 105
产品 6 106
标准差 7 107
STDEVP 8 108
9 109
曾是 10 110
最后的 11 111

作为 SUBTOTAL 函数的一个示例,我们将使用以下公式对单元格区域 B2 到 B4 中的销售总额求和:

=SUBTOTAL(9,B2:B4)

如何在Microsoft Excel中使用SUBTOTAL函数

对于下一个示例,我们隐藏了第 4 行和第 5 行。使用第一个公式,我们可以使用数字 9 作为第一个参数来获得总和,以包含隐藏的数据。

=小计(9,B2:B6)

如何在Microsoft Excel中使用SUBTOTAL函数

现在,我们将从总数中排除那些隐藏的行,使用数字 109 作为我们的第一个参数。

=小计(109,B2:B6)

如何在Microsoft Excel中使用SUBTOTAL函数

有了小计后,您可以再次使用 SUBTOTAL 函数来计算底部的总计。或者,您可以使用SUM 函数添加小计。

再举一个例子,我们将使用average 函数通过以下公式包含隐藏的第 3 行和第 4 行:

=SUBTOTAL(9,B2:B6)

如何在Microsoft Excel中使用SUBTOTAL函数

接下来,我们将使用以下公式排除隐藏行:

=SUBTOTAL(1,C2:C6)

如何在Microsoft Excel中使用SUBTOTAL函数

SUBTOTAL 函数的注释

在 Excel 中使用 SUBTOTAL 函数时,请注意以下几点:

  • 该函数适用于列或垂直范围,而不是行或水平范围。
  • 如果在引用参数区域中有嵌套小计,Excel 会在小计结果中忽略这些。
  • 当您使用过滤器时,过滤掉的数据将从小计结果中排除。

使用小计功能

在 Excel 中使用 SUBTOTAL 函数的另一种方法是使用 Subtotal 功能。这样,您可以同时自动添加计算 分组项目。Excel 使用 SUBTOTAL 函数为您完成此操作。让我们看几个例子。

相关: 如何使用 Microsoft Excel 中的函数计算工作日

使用按月销售的小计功能,我们可以对每月的数据进行分组,并将每个组与底部的总计相加。

选择要分组和小计的所有行。转到“数据”选项卡并在“大纲”下拉菜单中选择“小计”。

如何在Microsoft Excel中使用SUBTOTAL函数

当小计框打开时,选择添加小计的方式。

  • 在每次更改时选择要用于分组的列。在这里,我们选择了月份,以便在工作表中的月份更改时创建一个新组。
  • 使用函数选择要使用的函数,例如求和、平均值、最小值、最大值或其他选项。对于我们的示例,我们选择了 Sum。
  • 将小计添加到选中要计算小计的位置的框。对于我们的示例,我们选择了销售。

可以根据需要选中底部的框以获取其他项目。点击“确定”。

如何在Microsoft Excel中使用SUBTOTAL函数

然后,您将看到数据更新以对行进行分组小计,并在底部创建总计。使用加号、减号和数字按钮折叠或展开组以便于查看。

如何在Microsoft Excel中使用SUBTOTAL函数

作为另一个例子,我们将使用学生成绩。我们将使用小计和分组功能来显示每个学生的平均成绩。这是过程。

选择行,转到数据,然后在大纲下拉菜单中选择“小计”。

在小计框中,我们将在更改下拉列表中选择学生,在函数列表中选择平均值。然后,我们将选中“成绩”框。点击“确定”。

如何在Microsoft Excel中使用SUBTOTAL函数

我们现在将我们的行按学生分组,每个行的平均成绩和底部的总体平均值。同样,您可以使用左侧的按钮来折叠和展开组。

如何在Microsoft Excel中使用SUBTOTAL函数

如果您决定在使用小计功能后取消对行的分组,这些行将恢复正常。但是,包含 Excel 使用的 SUBTOTAL 函数的那些保留供您继续使用,或者如果您愿意,可以直接删除。

如何在Microsoft Excel中使用SUBTOTAL函数

如果您计划使用多个组,小计功能可能会变得复杂。但是,如果您对此功能感兴趣,这些基本示例应该可以帮助您入门。

需要注意的一点是,您不能使用此功能添加 Excel 表格小计。如果您的数据在表格中,您可以插入 Excel 公式,如前所述,或将表格转换为单元格区域以使用该功能。如果您选择后者,您将失去表格功能。

既然您知道如何在 Excel 中插入小计,请查看如何删除重复行

THE END