如何在 Microsoft Excel 中使用 SUBTOTAL 函数
摘要:要使用 SUBTOTAL 函数,请使用以下语法创建公式:SUBTOTAL(function_number, reference 1, reference 2,...)。您还可以通过选择行,然后单击“数据”选项卡来使用小计功能。在大纲下拉菜单中,选择“小计”。
在 Microsoft Excel 中获取相关项目组的小计很容易。事实上,您有两种方法可以做到这一点。您可以使用 SUBTOTAL 函数或 Subtotal 功能,以效果最好的为准。我们会告诉你怎么做。
什么是 Excel 中的小计?
如前所述,Excel 中的 SUBTOTAL 允许您对列表中的相同或相关项目进行分组,并使用函数来计算值。例如,您可以使用它来按月计算销售额或按学生计算平均成绩。您还可以按产品添加库存或计算下周到期的账单数量。
通过创建公式或使用小计功能使用 SUBTOTAL 函数,只需几步即可获得所需的计算结果。
使用 Excel 小计函数
您可以使用 Excel SUBTOTAL 函数创建一个公式,该函数可以灵活地包含或排除您隐藏的行。
公式的语法是 SUBTOTAL(function_number, reference 1, reference 2,...)
,其中前两个参数是必需的。您可以根据需要为其余参数使用其他单元格引用或命名范围。
function_number
参数允许您使用相应的编号插入 11 个函数之一。前 11 个包含隐藏行,而后 11 个不包含隐藏行。因此,只需插入所需功能的编号以及您希望如何处理隐藏行。
Function | Include Hidden Rows | Exclude Hidden Rows |
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 |
作为 SUBTOTAL 函数的示例,我们将使用以下公式对单元格范围 B2 到 B4 中的销售额求和:
=SUBTOTAL(9,B2:B4)
对于下一个示例,我们隐藏了第 4 行和第 5 行。使用第一个公式,我们可以使用数字 9 作为第一个参数来获得我们的总和,以包括隐藏数据。
=SUBTOTAL(9,B2:B6)
现在,我们将从总数中排除那些隐藏的行,使用数字 109 作为我们的第一个参数。
=SUBTOTAL(109,B2:B6)
获得小计后,您可以再次使用 SUBTOTAL 函数在底部进行总计。或者,您可以使用 SUM 函数添加小计。
再举一个例子,我们将使用 average 函数通过以下公式包含隐藏的第 3 行和第 4 行:
=SUBTOTAL(1,C2:C6)
接下来,我们将使用此公式排除隐藏行:
=SUBTOTAL(101,C2:C6)
SUBTOTAL 函数的注意事项
在 Excel 中使用 SUBTOTAL 函数时需要注意以下几点:
- 该函数适用于列或垂直范围,不适用于行或水平范围。
- 如果您在引用参数区域中嵌套了小计,Excel 会在小计结果中忽略它们。
- 当您使用过滤器时,过滤掉的数据将被排除在小计结果之外。
使用小计功能
在 Excel 中使用 SUBTOTAL 函数的另一种方法是使用小计功能。这样,您可以自动添加计算并同时对项目进行分组。 Excel 使用 SUBTOTAL 函数为您完成此操作。让我们看几个例子。
使用按月对销售额进行小计的功能,我们可以按月对数据进行分组,然后在底部对每个组求和并得出总计。
选择要分组和小计的所有行。转到“数据”选项卡,然后在“大纲”下拉菜单中选择“小计”。
当“小计”框打开时,选择您希望如何添加小计。
- At Each Change In:选择要用于分组的列。在这里,我们选择了月份,以便在工作表中的月份发生变化时创建一个新组。
- 使用函数:选择您要使用的函数,例如求和、平均值、最小值、最大值或其他选项。对于我们的示例,我们选择了 Sum。
- 将小计添加到:选中要计算小计的位置的复选框。对于我们的示例,我们选择了销售。
(可选)根据需要选中底部的框以获取其他项目。单击“确定”。
然后您会看到您的数据更新为分组 和 小计行并在底部创建总计。使用加号、减号和数字按钮折叠或展开组以便于查看。
再举一个例子,我们将使用学生成绩。我们将使用小计和分组功能来显示每个学生的平均成绩。这是过程。
选择行,转到数据,然后在大纲下拉菜单中选择“小计”。
在小计框中,我们将在更改下拉列表中选择学生,在函数列表中选择平均值。然后我们将选中等级框。单击“确定”。
我们现在将行按学生分组,每个学生的平均成绩和底部的总体平均成绩。同样,您可以使用左侧的按钮折叠和展开组。
如果您决定在使用小计功能后取消对行的分组,行将恢复正常。但是,那些包含 Excel 使用的 SUBTOTAL 函数的文件将保留供您继续使用,或者如果您愿意,只需将其删除。
如果您计划使用多个组,小计功能可能会变得复杂。但是,如果您对此功能感兴趣,这些基本示例应该可以帮助您入门。
需要注意的一件事是,您不能使用此功能添加 Excel 表格小计。如果您的数据在表格中,您可以按前面所述插入 SUBTOTAL 的 Excel 公式,或者将表格转换为单元格区域以使用该功能。如果您选择后者,您将失去表格功能。
既然您知道如何在 Excel 中插入小计,请查看如何删除重复行。
Mastering Excel Functions | ||
Functions | AVERAGE · CONCATENATE · COUNT · COUNTIF · DATEDIF · FILTER · FREQUENCY · FV · HYPERLINK · IF · IFS · IMAGE · INDEX · IS · LEN · MATCH · MEDIAN · RAND · ROUND · RRI · SORT · SQRT · SUBSTITUTE · SUBTOTAL · SUM · SUMIF · TODAY · TRIM · TRUNC · VLOOKUP · WEEKDAY · XLOOKUP · YEAR | |
Types | Basic · Budgeting · Data Entry · Logical · Text · Time and Date | |
Explained | Copying Formulas · Evaluating Formulas · Finding Functions · Fixing Formula Errors · Functions vs Formulas · Comparing Lookup Functions · Locking Formulas · Structuring Formulas · Translating Formulas |