在隐藏行和筛选数据中包含或排除隐藏值
当 Excel 工作表包含隐藏行、筛选数据或分组数据时，请使用 Excel SUBTOTAL 函数。 SUBTOTAL 函数可以在计算中包含或排除隐藏值。除了计算数据组的总计外，Excel 还可以计算数据的平均值、最大值、最小值、标准差和方差。下面介绍如何在 Excel 中插入小计。
注意：本文中的说明适用于 Excel for Microsoft 365、Excel 2019 和 Excel 2016。
SUBTOTAL 函数的语法使用 Excel 中的 SUBTOTAL 函数以不同的方式汇总工作表中的值。当您的工作表包含要包含在计算中的隐藏行时，它特别有用。
SUBTOTAL 函数的语法为：SUBTOTAL(function_num,ref1,ref2,…)
function_num 参数是必需的，它指定用于小计的数学运算类型。 SUBTOTAL 函数可以添加数字、计算所选数字的平均值、查找某个范围内的最大值和最小值、计算所选范围内的值的数量等等。
注意：SUBTOTAL 函数会忽略不包含数据的单元格和包含非数字值的单元格。
此参数是一个数字，取决于您是要在结果中包含隐藏行还是从结果中排除隐藏行。这些行可以手动隐藏或通过过滤器隐藏。
function_num 参数包括：
Function Task
function_num
function_num
(includes hidden values)
(excludes hidden values)
AVERAGE
1
101
COUNT
2
102
COUNTA
3
103
MAX
4
104
MIN
5
105
PRODUCT
6
106
STDEV
7
107
STDEVP
8
108
SUM
9
109
VAR
10
110
VARP
11
111
注意：当使用“隐藏”命令隐藏行时，function_num 引用参数 1 到 11 仅包含隐藏行中的值。使用“筛选”命令时，SUBTOTAL 计算不包括隐藏的筛选结果。
ref1 参数是必需的。这些单元格用于计算所选 function_num 参数的结果。该参数可以是一个值、单个单元格或单元格范围。
ref2,... 参数是可选的。这些是计算中包含的附加单元格。
对隐藏行使用 SUBTOTAL 函数Excel 函数可以手动输入或借助“函数参数”对话框输入。为了说明如何使用编辑栏手动输入函数，以下示例使用 COUNT function_num 参数来计算可见行以及可见行和隐藏行中的值的数量。
要使用 SUBTOTAL 函数计算工作表中的行数：
从包含多行数据的工作表开始。
选择将包含可见行数的单元格。
在功能栏中输入=SUBTOTAL。当您键入时，Excel 会建议一个函数。双击SUBTOTAL函数。
提示：要使用“函数参数”对话框输入 SUBTOTAL 函数，请转至公式并选择数学与三角函数 > SUBTOTAL 。
在出现的下拉菜单中，双击102 – COUNT function_num 参数。
键入逗号 (,)。
在工作表中，选择要包含在公式中的单元格。
按 Enter 键查看您在步骤 2 中选择的单元格中的结果。
选择将包含可见行和隐藏行计数的单元格。
在功能栏中输入=SUBTOTAL。当您键入时，Excel 会建议一个函数。双击SUBTOTAL函数。
在出现的下拉菜单中，双击 2 – COUNT function_num 参数，然后键入逗号 (,)。
在工作表中，选择要包含在公式中的单元格，然后按 Enter。
隐藏多行数据。在此示例中，仅隐藏销售额低于 100,000 美元的行。
将 SUBTOTAL 函数与筛选后的数据结合使用对已筛选数据使用 SUBTOTAL 函数会忽略已被筛选器删除的行中的数据。每次过滤条件更改时，该函数都会重新计算以显示可见行的小计。
要使用 SUBTOTAL 函数在过滤数据时查看计算结果的差异：
创建小计公式。例如，创建公式来确定已筛选数据的小计和平均值。
注意：对于可见行或隐藏行使用 function_num 参数并不重要。两个参数在过滤数据中提供相同的结果。
选择数据集中的任意单元格。
转到首页，然后选择排序和过滤 > 过滤。
使用下拉箭头过滤工作表数据。
请注意每次选择不同的过滤条件时值如何变化。
对分组数据使用 SUBTOTAL 函数对数据进行分组时，有一种方法可以将 SUBTOTAL 函数应用于每个单独的组，然后计算整个数据集的总计。
选择数据集中的任意单元格。
选择数据>小计打开小计对话框。
选择每次更改时下拉箭头，然后选择将计算每个小计的分组。
选择使用函数下拉箭头并选择 function_num。
在添加小计至列表中，选择将应用公式的列。
选择确定。
为每个数据组插入小计，并在数据集的底部插入总计。
要更改 function_num，请突出显示数据集中的任意单元格，然后选择数据> 小计。然后，在小计对话框中进行选择。