Microsoft Excel SUBTOTAL 函数及公式示例

在 Microsoft Excel 中， SUBTOTAL 函数的含义是什么？

Microsoft Excel 中的 SUBTOTAL 函数用于返回列表或数据库中的小计。在此上下文中，“小计”不仅仅用于汇总单元格范围内的数字，还可以用于其他工作。

与其他主要用于执行特定任务的 Microsoft Excel 函数不同，Excel 中的 SUBTOTAL 函数非常通用——它可以执行各种算术和逻辑运算，包括以下各项：

1. 计算相应单元格的数量。
2. 轻松计算平均值。
3. 在给定的 Excel 工作表中查找最大值或最小值。

此外，SUBTOTAL 函数在所有 Microsoft Excel 版本中都可用，包括：

• Microsoft Excel 2016 版本。
• Microsoft Excel 2013 版本。
• Microsoft Excel 2010 版本。
• Microsoft Excel 2007 及更早版本。

Microsoft Excel 中 SUBTOTAL 函数的基本要点是什么？

与 Microsoft Excel 中的 SUBTOTAL 函数相关的几个关键要点如下：

1. SUBTOTAL 函数是 Microsoft Excel 的一个内置函数，属于数学或三角函数类别。
2. SUBTOTAL 不仅仅汇总特定单元格范围内的数字，它还可以通过执行各种算术运算来提供帮助。
3. Microsoft Excel 中的 SUBTOTAL 函数非常通用。我们可以使用它来计算平均值、求和、计数单元格以及进行其他高效的计算。

SUBTOTAL 函数的定义

Excel 中的 SUBTOTAL 函数主要用于在数据库或列表中返回小计，即找出给定特定单元格范围的小计。

Microsoft Excel 中的 SUBTOTAL 公式

可用于 SUBTOTAL 函数的公式如下：

公式

其中，

1. Function_num 或操作代码： SUBTOTAL 公式主要指示应使用哪个函数来计算给定列表中的小计。

此外，function_num 参数根据下面提到的两种标准或方法应用。

1. 包含隐藏值：当 function_num 参数在 1-11 范围内时，使用此值，SUBTOTAL 函数将包含隐藏值。
2. 忽略隐藏值：当 function_num 参数在 101-111 范围内时，SUBTOTAL 函数将排除或忽略隐藏值。它将仅查找可见行中的值，并在计算中忽略隐藏值。

1. ref1, [ref2]：它们被称为包含需要为小计执行计算的值的特定单元格的一个或多个引用。

使用 Microsoft Excel 中的 SUBTOTAL 函数的三个主要原因是什么？

与传统的 Microsoft Excel 函数相比，SUBTOTAL 函数最终提供了以下优点：

1. 计算已筛选的行中的值

Microsoft Excel SUBTOTAL 函数会忽略已筛选掉的行中的值，我们可以使用它来创建动态数据摘要，其中小计值会根据筛选条件自动重新计算。

• 例如：如果我们想筛选表格以仅显示东部地区的销售额，SUBTOTAL 公式将自动调整，以便有效地从总计中排除所有其他地区。

重要提示。由于两组函数编号（范围从 1-11 和 101-111）都会忽略已筛选的单元格，因此我们可以有效地使用 Subtotal 9 或 Subtotal 109 公式。

2. 计算 Excel 工作表中仅可见的单元格

请记住，带有 function_num 101 到 111 的 SUBTOTAL 公式将忽略所有隐藏的单元格：手动筛选掉和手动隐藏的。因此，当我们使用 Excel 的隐藏功能从视图中删除不相关数据时，在这种情况下，我们也可以使用函数 101-111 来排除隐藏行中的值，以进行小计计算。

3) 忽略嵌套 SUBTOTAL 公式中的所有值

假设提供给我们 Microsoft Excel SUBTOTAL 公式范围内的任何其他 SUBTOTAL 公式，在这种情况下，这些嵌套的小计将被忽略，因此相同的数字将只计算一次。

在下面的屏幕截图中，Grand Average 公式：SUBTOTAL(1, C2:C10)，将忽略单元格 C3 和 C10 中 SUBTOTAL 公式的结果，就像如果我们使用两个单独的范围的 Average 公式AVERAGE(C2:C5, C7:C9)一样。

如何在 Microsoft Excel 中使用 SUBTOTAL 函数？

现在让我们看看 SUBTOTAL 函数在 Microsoft Excel 中是如何工作的。

# 示例 1：使用 Excel 自动筛选器或 Excel 中由筛选器隐藏的行隐藏的值的 SUBTOTAL 公式

下表包含季度销售数据，其中个人季度在 E 列，销售数据在 F 列。

SUM 函数主要应用于单元格“F16”=SUM(F4:F15)。

它将分别返回或结果为1184。

现在，如果行被筛选器隐藏（下图），SUM 函数将返回相同的值 1184，而不是 Q4 数据的 194。

如果我们想要 Q4 数据的正确 SUM 值，那么在这种情况下，我们需要使用 SUBTOTAL 函数。

现在，在应用数据工具栏下方的 SUBTOTAL 函数之前，我们还将为标题行应用筛选。

因此，让我们分别计算已筛选数据的 Q1、Q2、Q3 或 Q4 销售数据。

我们将使用 109 作为 SUBTOTAL 函数中的 function_num 参数来汇总数据。即，=SUBTOTAL(109, C17:C28)

然后，该 SUBTOTAL 函数将忽略被筛选器隐藏的行并计算正确的结果。

这是会根据筛选条件动态变化的小计值。它将仅查找可见行中的值之和，并且 SUBTOTAL 函数也将忽略隐藏行。

Q4

Q3

Q2

Q1

# 示例 2：用于手动隐藏行的 SUBTOTAL 函数（Excel 工作表）

在相应的 Microsoft Excel 工作表中，通过突出显示这些行、右键单击鼠标，然后选择“隐藏”选项，已通过行格式设置隐藏了第 15、16、19 和 20 行。

并且在所选电子表格的 C23 单元格中使用了一个 subtotal 函数，用于计算可见单元格的总和。

之后，我们需要使用109作为 SUBTOTAL 函数中的 function_num 参数来汇总数据。

即，=SUBTOTAL(109, C14:C21)

在这里，相应的 SUBTOTAL 函数将忽略手动隐藏的行并计算正确的结果。

之后，它将仅查找可见行中的值之和；SUBTOTAL 函数也将忽略隐藏行。

# 示例 3：Excel 工作表中用于正常值的 SUBTOTAL 函数

在此，给定季度销售数字范围内的所有选定单元格都可见，并且没有任何单元格被筛选，也没有隐藏行，因此它将包含所有值。

如下例所示，我们可以看到多个 SUBTOTAL 函数已轻松应用。

因此，在单元格 C14、C15 和 C16 中，SUBTOTAL 函数已有效应用。

现在，为了计算 SUM 值，在 subtotal 函数中使用了函数编号 9 或 109，该函数将计算指定范围的总和，即：=SUBTOTAL(9, C4:C11)。

应用公式后，它将给出 C4:C11 单元格中所有值的总和，前提是该指定范围内没有筛选，也没有隐藏行。

现在，为了计算 MIN 值，在 subtotal 函数中使用了函数编号 5 或 105，用于查找指定范围内的最小值。在单元格 C15 中，有效应用了 SUBTOTAL 函数，即：=SUBTOTAL(5, C4:C11)：

它将查找给定指定范围（从单元格 C4:C11）内的最小值：

为了计算 MAX 值，可以在 subtotal 函数中使用函数编号 4 或 104 来查找指定范围内的最大值。在单元格 C16 中，应用了一个 SUBTOTAL 函数，即=SUBTOTAL(4, C4:C11)：

它将分别查找指定范围 C4:C11 内的最大值。

Microsoft Excel Subtotal 不工作：常见错误

如果我们的 subtotal 公式返回错误，很可能是由于以下原因之一：

1. #VALUE!：function_num 参数不是 1-11 范围内的整数；或任何 ref 参数包含 3D 引用。
2. #DIV/0!：如果指定的汇总函数除以零，则主要发生此错误。
3. #NAME?：Subtotal 函数的名称拼写错误——这个简单的错误需要相应地修复。

重要提示：请注意，如果您对 SUBTOTAL 函数还不熟悉，可以使用内置的 SUBTOTAL 功能，让 Excel 自动插入公式。

在使用 Microsoft Excel 的 SUBTOTAL 函数时，个人需要记住哪些重要事项？

在使用 Microsoft Excel 中的 SUBTOTAL 函数时，个人需要记住的重要事项如下：

1. 个人可以有效地应用 SUBTOTAL 函数来查找已筛选值的总和。
2. SUBTOTAL 函数主要用于忽略隐藏行中的值。
3. 此外，function_num 参数值在任何情况下都不得超过 11 或小于 1。同样，它不应大于 111 或小于 101；如果它大于前面提到的值，那么在这种情况下它将给出错误：#VALUE! 错误。
4. SUBTOTAL 函数主要仅适用于数值。
5. 如果 SUBTOTAL 范围中存在任何小计，则它们都会被忽略，这意味着如果其他 SUBTOTAL 公式中的任何现有 SUBTOTAL 公式，则该特定值将在计算过程中被 subtotal 函数忽略，以避免重复计数并防止重复计数错误。
6. 此外，SUBTOTAL 函数还可以有效地用于动态汇总数据。
7. 在计算过程中，SUBTOTAL 函数将有效忽略空白单元格以及包含非数值的单元格。