Microsoft Excel 中的 Subtotal 和 Countif 函数

Microsoft Excel 作为一款功能强大的工具，在数据处理和分析方面提供了广泛的函数。其中，“Subtotal 函数”和“Countif 函数”尤为重要，它们能够帮助用户快速准确地进行各种复杂的计算。

“Subtotal 函数”在 Microsoft Excel 中是一个强大的汇总工具，可以帮助用户轻松计算数据子集中的各种汇总统计信息，如求和、平均值、计数等。结合“Countif 函数”，后者用于统计特定条件在给定数据范围内出现的次数，Microsoft Excel 用户可以更有效地控制数据分析。本文将介绍 Subtotal 和 Countif 函数的基本定义、语法和日常应用示例，并阐述它们如何协同工作以简化数据分析流程。通过实际示例和见解，本教程旨在让用户掌握有效利用这些函数，从而在 Excel 数据分析工作流中获得更深入的洞察，并做出明智的决策。

Microsoft Excel 中的 SUBTOTAL 函数是什么？

Microsoft Excel 中的“SUBTOTAL 函数”是一个功能强大的工具，专门用于对数据范围执行汇总计算。与 SUM 或 AVERAGE 等传统函数不同，SUBTOTAL 主要能够适应数据筛选，并基于特定范围内的可见单元格计算结果。此功能在需要分析数据子集而不受筛选掉或隐藏单元格干扰时非常宝贵。通过结合各种函数编号，如 SUM、AVERAGE、COUNT 等，SUBTOTAL 提供了在给定数据集中生成不同类型的小计或总计的灵活性。无论是管理财务记录、库存清单还是销售数据，SUBTOTAL 都能通过自动化计算来提高效率，并确保海量数据集的准确性。它能够根据数据可见性或筛选条件的更改无缝调整计算，从而简化了复杂的数据分析任务。此外，SUBTOTAL 函数特别适用于对分组数据生成小计，从而为数据分析提供了一种简化的方法，而无需手动调整或单独计算。本质上，SUBTOTAL 函数使 Excel 用户能够轻松高效地执行全面的数据分析，使其成为各行各业专业人士不可或缺的工具。

Microsoft Excel 中 SUBTOTAL 函数的语法

Microsoft Excel 工作表中“SUBTOTAL 函数”的有效语法如下所示：

通常，SUBTOTAL 函数主要接受以下参数，具体如下讨论：

• Function_num：此数字负责确定 SUBTOTAL 函数将执行哪种算术运算（函数）。尽管如此，“function_num”参数可以取 1 到 11 或 101 到 111 之间的任何值。
• Ref1：这是需要对其执行算术运算的单元格范围。尽管如此，它可以作为引用或命名范围提供。
• Ref2：SUBTOTAL 函数中的此参数是需要对其执行算术运算的第二个单元格范围。它也可以输入为引用范围或命名范围。参数“function_num”和“ref1”是必需的，而“ref2”是可选的。SUBTOTAL 函数返回数值输出。

重要提示：应注意，“function_num”参数始终输入为数值，而不是字符串或字符。

如何在 Microsoft Excel 中打开 SUBTOTAL 函数？

现在，为了在 Microsoft Excel 中打开“SUBTOTAL Excel 函数”，我们需要在所需的单元格中输入“=SUBTOTAL”，后面跟着函数的参数。或者，也可以从 Microsoft Excel 的“公式”选项卡中打开 SUBTOTAL 函数。步骤如下：

步骤 1：首先，我们必须选择要输入“SUBTOTAL 公式”的单元格。

步骤 2：现在，从“公式”选项卡中，我们需要单击“数学和三角函数”选项的下拉菜单。然后选择“SUBTOTAL”，如下图所示：

步骤 3：通常，在执行上述步骤后，我们将看到“函数参数”对话框，如下图所示。在此对话框中，我们需要分别输入“function_num”和“ref1”参数的值。当光标位于“ref 1”框内时，“ref2”框将出现在其下方。

之后，我们需要单击“确定”按钮以继续获取结果。SUBTOTAL 函数的结果将显示在步骤 1 中选择的单元格中。

为什么要使用 Microsoft Excel 中的 SUBTOTAL 函数？

在 Microsoft Excel 中有效使用 SUBTOTAL 函数的三个重要原因是：

1. 计算已筛选行中的相应值。

众所周知，Excel 中的 SUBTOTAL 函数通常会忽略已筛选行中的值。通过此功能，还可以创建动态数据摘要，其中小计值会根据筛选条件自动重新计算。

• 例如：这里我们将考虑一种情况，其中我们将筛选表格以仅显示东部地区的销售额。同样，Subtotal 公式会自动调整，以便将所有其他区域从总计中排除。

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

众所周知，带有 function_num 101 至 111 的相应“Subtotal 公式”会忽略所有隐藏单元格。因此，当我们使用 Microsoft Excel 的隐藏功能从视图中删除不相关数据时，我们需要使用 101 至 111 范围内的函数编号来从总计中排除隐藏行中的值。

3. 忽略嵌套 Subtotal 公式中的值。

假设有效地提供给相应 Microsoft Excel Subtotal 公式中的范围包含任何其他 Subtotal 公式。在这种情况下，所有这些嵌套小计都将被忽略，因此相同的数字只会计算一次。

如下图所示，Grand Average 公式 SUBTOTAL (1, C2:C10) 通常会忽略给定单元格 C3 和 C10 中 Subtotal 公式计算出的结果，就像我们使用带有两个单独范围 AVERAGE (C2:C5, C7:C9) 的 Average 公式一样。

# 示例 1：在 Microsoft Excel 中使用 SUBTOTAL 函数

步骤 1：首先，我们需要构建一个包含不同类别数据的表，如下所述。

步骤 2：在此步骤中，我们需要观察上表。通过观察，我们注意到我们有一个产品类别、颜色类别和产品数量。然后，我们必须应用带有函数编号 9 的“SUBTOTAL 公式”。

步骤 3：之后，我们需要应用此公式，结果如下图所示。

步骤 4：在此步骤中，我们需要应用如下的“SUBTOTAL 公式”，函数编号为 109。

步骤 5：现在，我们必须应用 SUBTOTAL 公式，结果如下图所示。

步骤 6：在此步骤中，我们在公式中使用了函数编号 9 和 109 来对两个列执行 SUM 操作。C2:C9 是我们实际进行计算的数据范围。两个公式的总和都是 515。现在，隐藏某些行，然后观察两个公式的结果。

步骤 7：现在，在执行“SUBTOTAL 公式”后，结果通常如下所示：

尽管如此，第 50 行到第 60 行已隐藏，因此函数编号 109 的结果已更改为 260。这是因为它不考虑手动隐藏的数据，而函数编号 9 保持不变，这意味着即使我们手动隐藏它，它也会考虑数据。

步骤 8：现在，我们将对数据应用筛选器，然后仅筛选一种颜色；在此示例中，我们考虑了“黄色”。

步骤 9：在应用“SUBTOTAL 公式”后，结果如下图所示。

然后，我们需要应用如下的 SUBTOTAL 公式，函数编号为 109。

步骤 10：现在，在应用“SUBTOTAL 公式”后，结果如下所示。

现在，如果我们观察上面的截图，我们可以看到两个函数编号都没有考虑已筛选数据的数量。在筛选出数据并隐藏行后，两个公式都将给出相同的结果，即两个公式都不会考虑不可见数据。

步骤 11：应用 SUBTOTAL 公式后，结果如下所示。

步骤 12：应用如下的 SUBTOTAL 公式，函数编号为 109。

步骤 13：现在，应用此公式后，结果显示如下。

Microsoft Excel 中 COUNTIF 函数的含义是什么？

Microsoft Excel 中的“COUNTIF 函数”是一个强大的工具，可用于计算指定范围内满足给定条件或标准的单元格数量。其主要目的是提供一种快速有效的方法，根据特定参数来统计数据，从而使用户能够轻松分析和操作大量信息。

1. COUNTIF Excel 函数主要用于根据测试条件计算给定范围或数组中具有数值和字母值的单元格，并返回准确结果。
2. 使用此函数时，可以在单个数据集中指定多个条件或测试条件。
3. “公式”>“函数库”>“更多函数”>“统计”菜单项通常提供对该函数的访问。
4. 可以通过键入“=C”并从列表中选择，或通过在单元格中键入“=COUNTIF”来双击 Excel 建议函数列表中的 COUNTIF 函数。

Microsoft Excel 中 COUNTIF 函数的语法

COUNTIF 函数的语法非常简单：它主要包含两个主要部分：范围和条件。“范围”是指我们要实际评估的单元格组，而“条件”则建立了要计入计数必须满足的条件。此条件可以采取以下各种形式：数值、逻辑表达式等。

通常，如果我们有一个包含一些数值数据的单元格范围，并且我们希望确定其中有多少单元格的值大于 30，那么将使用 COUNTIF 函数，如下所示：`=COUNTIF(range, ">30")`。尽管如此，COUNTIF 函数的多功能性使其成为财务分析、统计报告和库存管理等广泛应用的宝贵工具。通过提供简单有效的用户定义的条件数据摘要方法，COUNTIF 函数主要使相关用户能够提取有意义的见解。它有助于个人做出明智的决策。

# 示例 2：在 Microsoft Excel 中使用 COUNTIF 函数以及给定值。

为了利用 Microsoft Excel 中的 COUNTIF 函数来计算单元格范围内特定值的出现次数，我们需要遵循以下步骤：

步骤 1：首先，我们需要构建一个表，该表通常显示单元格 B2:B7 中的数值列表。我们需要计算给定单元格范围中与数字“43”匹配的值。

步骤 2：现在，在此步骤中，我们需要将“COUNTIF 函数”应用于给定的单元格范围，公式如下：

“=COUNTIF (B2:B7, 43)。”在此，应用到公式的相应条件是数字“43”。公式检查单元格范围 B2:B7 中与数字“43”匹配的值。该范围仅包含一个满足条件的数字。

步骤 3：因此，COUNTIF 函数返回的结果是“2”。结果分别显示在单元格 B8 中。

# 示例 3：使用 COUTNIF 函数计算小于给定数字的数字。

现在，为了利用 Microsoft Excel 中的 COUNTIF 函数来计算单元格范围内特定值的数量，我们需要遵循以下步骤：

步骤 1：随后的表中提供了单元格 B2:B10 中的数据列表。计算给定单元格范围中小于“70”的值。之后，相应地将COUNTIF 函数主要应用于给定的单元格范围，公式如下：

"=COUNTIF (B2:B10,"<70")"

步骤 2：在此，应用于公式的条件是“<70”。COUNTIF 公式检查与条件（小于 70）匹配的单元格范围。该范围内只有四个值小于 70。因此，此函数返回的结果是“5”。结果有效地显示在单元格 B18 中。

如何在 Microsoft Excel 中轻松使用 SUBTOTAL 函数与 COUNTIF 函数？

在这里，我们将重点介绍如何轻松使用 SUBTOTAL 函数与 COUNTIF 函数来有效计算已筛选数据集的计数。当我们筛选大量数据以仅显示某些数据值时，函数仍会计算隐藏值并将其包含在计算中。

但是，有时我们只想计算可见行或特定选定数据集的数据值。如果我们想根据已筛选的数据集计算满足给定标准的单元格数量。有时，函数将计算整个数据集，为防止这种情况，我们必须利用 SUBTOTAL 函数、COUNTIF 函数和 SUMPRODUCT 函数。

# 示例：1 将 SUBTOTAL 与 COUNTIF 结合使用

现在，让我们以一个需要利用 SUBTOTAL 函数与 COUNTIF 函数在 Microsoft Excel 中使用的样本场景为例。假设我们有一组数据，其中包含不同班级分数和选定学生的性别列表。在这里，我们实际上想计算某个特定班级的女生人数。我们可以选择筛选特定班级，而不是创建新的数据集。但是，函数返回了不正确的结果。因此，我们决定结合使用 SUBTOTAL 和 COUNTIF 函数来高效获得正确的计数。

现在，我们将逐步解释在 Microsoft Excel 中将 SUBTOTAL 与 COUNTIF 函数结合使用的过程。要将此方法应用于我们的工作，我们可以遵循以下步骤。

步骤 1：首先，我们需要筛选我们的初始数据集。为此，我们将选择整个范围，然后转到“数据”选项卡。然后，我们将选择“排序和筛选”部分内的“筛选”。

步骤 2：之后，我们将单击“班级”列旁边的下拉箭头。接下来，我们将勾选A选项框，因为我们只想计算 A 班的单元格。最后，我们将按“应用”选项。

步骤 3：现在，在此步骤中，在筛选完数据集后，我们可以轻松应用公式。为此，我们可以输入以下公式：“=SUMPRODUCT (SUBTOTAL (3, OFFSET (C2:C8, ROW (C2:C8)-ROW (C2), 0,1)),-(C2:C8="girl"))”，输入公式后，我们需要按键盘上的Enter键以有效返回结果。

步骤 4：瞧！我们已在 Microsoft Excel 中成功地将 SUBTOTAL 与 COUNTIF 函数结合使用。

# 示例 2：有效使用 SUBTOTAL 函数与 COUNTIF 函数

现在，在此示例中，我们将展示在 Microsoft Excel 中有效使用 SUBTOTAL 函数与 COUNTIF 函数。

现在，让我们设想一个包含篮球运动员数据的电子表格。此特定数据集通常包含以下信息：姓名、位置和所属联盟。为了实现我们的目标，我们将实现 subtotal 与 countif 函数的组合。

步骤 1：首先，我们需要筛选数据以仅显示来自西部联盟的球员。为此，我们必须选择整个数据集。选择数据后，我们必须导航到 Microsoft Excel 窗口顶部的“数据”选项卡，然后单击“筛选”按钮。这将有效地为每个列标题添加筛选箭头。

步骤 2：接下来，我们将单击“联盟”列标题旁边的筛选箭头，单击它后，会出现一个下拉菜单，其中包含按条件筛选的选项。确保只勾选“西部”旁边的框，然后单击“确定”选项。我们的数据将立即更新，仅显示联盟为西部的行。

步骤 3：通常，为了准确计算位置为“后卫”的可见行数量，我们将使用函数组合，即 SUBTOTAL 和 SUMPRODUCT 函数。以下是我们即将使用的公式：

让我们来分解一下：

1. OFFSET：此函数负责返回一个引用，该引用距离起始单元格和起始范围有指定数量的行和列。
2. ROW (B2:B11)-ROW (B2)：公式的这部分用于调整范围，使其从筛选后的第一个可见行开始。
3. SUBTOTAL (3)：此函数使用 COUNTA 函数 (3) 计算给定范围的小计，并且仅考虑可见单元格。
4. SUMPRODUCT：将数组相乘然后对乘积求和。在这种情况下，它会将小计计数乘以每个相应行是否为“后卫”。
5. (B2:B11="Guard")：这将把 (B2:B11="Guard") 的布尔结果转换为 1 和 0。

步骤 4：当我们将此公式输入到相应的单元格时，它将准确地计算出位置为“后卫”的可见行数，从而在应用了筛选器后为我们提供可靠的计数，即6。

使用 Microsoft Excel 中的 SUBTOTAL 函数与 COUNTIF 函数的各种优点是什么？

在 Microsoft Excel 中使用 SUBTOTAL 函数与 COUNTIF 函数的各种优点如下：

1. 动态计算：Microsoft Excel 中的相应“Subtotal 函数”通常允许我们动态计算已筛选数据的总计，这在与“Countif 函数”结合使用时非常有用。这意味着，当我们筛选或操作数据时，相应的总计计算将进行相应调整，从而有效地提供基于已筛选条件的准确且最新的计数。
2. 提供报告灵活性：通过结合“Subtotal 与 Countif”函数，我们可以轻松地更准确地生成定制报告。无论我们是跟踪特定销售人员的销售额，计算库存中特定项目的出现次数，还是统计给定调查中的回复，这种有效的组合都允许针对我们的特定需求进行灵活的报告。
3. 效率：仅仅使用相应的“Subtotal 与 Countif”函数可以显著提高数据分析任务的效率。与每次更改时手动更新计数或重新计算总计相比，我们可以轻松地依赖 Microsoft Excel 的函数来自动处理这些任务。这将节省我们的时间，并减少我们分析任务中出现错误的可能性。
4. 演示清晰度：在向他人展示汇总数据时，使用“Subtotal 与 Countif”函数通常可以确保数据流的清晰度和透明度。Subtotal 函数主要负责提供清晰指示聚合级别的总计标签，从而使读者更容易理解数据的结构并解释 Countif 函数提供的计数。
5. 有助于加深对数据的理解：通过将数据分解为各种总计，然后将 Countif 应用于每个总计，我们可以轻松地深入了解数据集中的分布和模式。这对于识别趋势、异常值或主要需要进一步调查的区域特别有价值。
6. 可扩展性：无论我们处理的是小数据集还是大数据集，相应的“Subtotal 函数”与 Countif 结合使用都能有效地扩展。

列出了关于 Microsoft Excel 中 SUBTOTAL 与 COUNTIF 函数的各种常见问题。

关于 Microsoft Excel 中 SUBTOTAL 与 COUNTIF 函数的各种常见问题如下：

1. 列出在 Microsoft Excel 中使用 SUBTOTAL 与 COUNTIF 函数的主要目的。

主要目的是对已筛选的数据集执行计算。SUBTOTAL 函数主要计算 SUM、AVERAGE、COUNT 等各种函数，而 Microsoft Excel 中的 COUNTIF 函数则计算满足特定标准的单元格。将它们结合起来可以有效地计算已筛选的数据。

2. 如何使用 SUBTOTAL 与 COUNTIF？

我们可以通过将 COUNTIF 的适当函数编号作为 SUBTOTAL 函数的第一个参数来轻松地将 SUBTOTAL 函数与 COUNTIF 函数结合使用。

3. SUBTOTAL 与 COUNTIF 函数的主要函数编号是什么？

COUNTIF 的函数编号是 109。因此，当我们使用 SUBTOTAL 与 COUNTIF 结合使用时，我们在 SUBTOTAL 函数中使用 109 作为第一个参数。

4. 我们可以在 Microsoft Excel 中将其他函数与 SUBTOTAL 和 COUNTIF 函数一起使用吗？

是的，我们还可以通过仅提供适当的函数编号，以类似的方式将 SUMIF、AVERAGEIF、MAXIF、MINIF 等其他函数与 SUBTOTAL 一起使用。

5. SUBTOTAL 是否考虑隐藏或已筛选的数据？

是的，当然，相应的 SUBTOTAL 函数会忽略已筛选或隐藏的数据，这使得它对于基于已筛选数据进行计算非常有用。

6. 我们可以将 SUBTOTAL 和 COUNTIF 与动态范围一起使用吗？

是的，我们可以轻松地将动态范围（如表或命名范围）与 SUBTOTAL 和 COUNTIF 结合使用，以在数据更改时自动调整计算。

7. 使用 SUBTOTAL 和 COUNTIF 组合是否存在任何限制？

一个限制是 SUBTOTAL 仅适用于可见单元格。如果我们需要在计算中包含隐藏单元格，则需要使用其他方法或函数。