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. 小计函数基本上被视为Microsoft Excel中的内置函数，分类在功能：数学或三角函数下。
2. 小计将不仅总结特定定义范围内的数字，还将通过对其执行各种算术操作来帮助。
3. Microsoft Excel中的SUBTOTAL函数非常灵活。我们可以使用它来进行平均值、求和以及计算一堆单元格等操作。

SUBTOTAL函数的定义

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

Microsoft Excel中的SUBTOTAL公式

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

公式：

=SUBTOTAL (function_num,ref1,.....)

其中，

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表格中可见的单元格

请记住，给定功能编号为101到111的Subtotal公式将忽略所有隐藏的单元格：被筛选掉的和手动隐藏的。因此，在使用Excel的隐藏功能从视图中删除不相关数据时，我们还可以使用功能101-111来排除隐藏行中的值。

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

假设在我们的Microsoft Excel Subtotal公式中提供的范围包含其他Subtotal公式，那么在这种情况下嵌套的小计将被忽略，因此同一数字只计算一次。

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

如何在Microsoft Excel中使用SUBTOTAL函数

现在让我们看看在Microsoft Excel中如何使用SUBTOTAL函数。

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

在下面的示例中，给定表格包含季度销售数据，其中个别季度在列E中，销售数据在列F中。

SUM FUNCTION 主要应用于相应的单元格”F16″=SUM(F4:F15)。

它将以以下值返回结果： 1184 。

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

如果我们只想要Q4数据的正确SUM值，那么我们需要使用SUBTOTAL函数。

在应用SUBTOTAL函数之前，我们还需对标题行应用筛选器。

因此，让我们分别计算过滤数据的Q1或Q2或Q3或Q4销售数据：

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

而特定的SUBTOTAL函数将忽略被筛选隐藏的行并计算出正确的结果。

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

Q4

Q3

Q2

Q1

# 示例2：Excel表格中手动隐藏行的SUBTOTAL函数

在相应的Microsoft Excel表格中，通过使用行格式选项将第15、16、19和20行隐藏，只需将这些行进行高亮显示，然后使用鼠标右键点击，然后我们将分别选择”隐藏”选项。

而且在所选电子表格的C23单元格中使用了子总计函数，以便计算可见单元格的总和。

此后，我们需要将109作为SUBTOTAL函数的function_num参数，以便对数据进行汇总。

即， SUBTOTAL(109, C14:C21)

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

此后，它将仅在可见行中找到数值的总和；SUBTOTAL函数也会忽略隐藏的行。

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

在此示例中，给定季度销售数字范围中的所有选定单元格都是可见的，并且没有筛选或隐藏行，因此它将包括所有值。

如下面的示例中所示，我们可以看到可以轻松应用多个小计函数。

所以在特定的单元格C14、C15和C16中，小计函数被高效地应用了。

现在，为了计算SUM值，将使用9或109的相应函数号码，在一个小计函数中计算指定范围的总和，即： SUBTOTAL(9, C4:C11)。

一旦我们应用这个公式，它将给出单元格C4:C11中所有值的总和，前提是没有进行筛选，并且指定范围内的行已被隐藏。

现在，为了计算出最小值，通过在小计函数中使用功能编号为5或105的相应函数，将找到指定范围中的最小值。在特定的单元格C15中，有效地应用了一个小计函数，即 =SUBTOTAL (5, C4:C11):

它将在给定的指定范围内查找最小值，该范围是从单元格 C4:C11：

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

然后将分别从C4:C11中的指定范围找出最大值。

Microsoft Excel子总计不起作用：常见错误

如果我们各自的子总计公式返回错误，很可能是以下原因之一：

1. #VALUE!： 特定的function_num参数不是介于1-11之间的整数；或者任何包含3D引用的ref参数。
2. #DIV/0！ 如果指定的摘要函数执行了除以零的操作，就会发生这种情况。
3. #NAME?： 子总计函数的名称拼写错误 – 需要分别修复此错误。

重要注意事项： 需要注意的是，如果我们对SUBTOTAL函数不够熟悉，可以使用内置的SUBTOTAL功能，让公式自动插入。

Microsoft Excel中个人需要记住的重要事项是什么

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

1. 个人可以有效地应用SUBTOTAL函数来查找已过滤值的总和。
2. SUBTOTAL函数主要用于忽略隐藏行中的值。
3. 此外，在任何情况下，函数_ num参数值不能超过11或小于1。同样，它不应大于111或小于101；如果大于上述值，则会出现错误：#VALUE!错误。
4. SUBTOTAL函数仅适用于数值。
5. 如果SUBTOTAL范围中通常存在子总计，那么它们将被忽略，这意味着如果输入范围的其他SUBTOTAL公式中已经存在子总计公式，则该值将被SUBTOTAL函数忽略，避免出现重复计数错误。
6. 除此之外，SUBTOTAL函数还可以高效地用于动态汇总数据。
7. 在计算过程中，SUBTOTAL函数将忽略空白单元格和包含非数值的单元格。