掌握数据处理利器：SUBTOTAL()函数深度剖析

在Excel中，SUBTOTAL函数被誉为“万能函数”，因为它可以实现多种数据汇总操作，包括求和、计数、求平均值、最大值、最小值等。无论是在数据处理、统计分析还是报表制作中，SUBTOTAL函数都能发挥巨大作用。接下来，我们将详细介绍SUBTOTAL函数的各个方面，帮助大家更好地理解和应用这个函数。

SUBTOTAL函数的作用及语法结构

SUBTOTAL函数的主要作用是返回一个数据列表或数据库的分类汇总。它的语法结构很简单：

```excel

=SUBTOTAL(功能代码, 数值区域)

```

这里，“功能代码”决定了SUBTOTAL函数执行的汇总操作类型，而“数值区域”则是你要进行汇总操作的数据范围。

功能代码详解

SUBTOTAL函数的功能代码分为两组：1-11和101-111。这两组代码对应的函数名称相同，但功能有所不同。

1-11：包含手动隐藏的值。也就是说，即使某些数据行或列被隐藏，SUBTOTAL函数仍然会考虑这些值进行计算。

101-111：不包含手动隐藏的值。只对可见单元格进行汇总操作。

常见的功能代码及其对应的函数名称如下：

1：求平均值（AVERAGE）

9：求和（SUM）

2：计数（COUNTA），非空单元格个数

3：计数（COUNT），忽略错误值、文本等，仅计算数值单元格个数

4：求最大值（MAX）

5：求最小值（MIN）

101：忽略隐藏值的求平均值

109：忽略隐藏值的求和

102：忽略隐藏值的计数（COUNTA）

103：忽略隐藏值的计数（COUNT）

104：忽略隐藏值的求最大值

105：忽略隐藏值的求最小值

示例与应用技巧

1. 求和

如果你想要对一个数据区域进行求和，并且希望计算结果包含隐藏的行或列，可以使用功能代码9。如果你只希望对可见单元格求和，可以使用功能代码109。

```excel

=SUBTOTAL(9, D3:D9) // 包含隐藏值求和

=SUBTOTAL(109, D3:D9) // 忽略隐藏值求和

```

2. 计算平均值

同样地，如果你希望计算包含隐藏值的平均值，可以使用功能代码1。如果你只希望对可见单元格求平均值，可以使用功能代码101。

```excel

=SUBTOTAL(1, D3:D9) // 包含隐藏值求平均值

=SUBTOTAL(101, D3:D9) // 忽略隐藏值求平均值

```

3. 保持序号连续性

在数据表中，有时候我们需要在隐藏或筛选数据后保持序号的连续性。这时，可以使用SUBTOTAL函数的计数功能（功能代码103），因为它只对可见非空单元格进行计数。

```excel

=SUBTOTAL(103, B$2:B2)

```

这个公式会在每次隐藏或筛选数据时，自动更新序号，保持其连续性。

4. 统计当前可见值中的最大值

如果你想要找到当前可见单元格中的最大值，可以使用功能代码104。

```excel

=SUBTOTAL(104, D3:D9)

```

这个公式会忽略隐藏的行或列，只计算可见单元格中的最大值。

5. 筛选后计算平均值

在数据筛选后，如果你想要计算当前可见单元格的平均值，可以使用功能代码1或101。但需要注意的是，SUBTOTAL函数在筛选后并不会改变其计算范围，它只是简单地忽略隐藏的行或列。

```excel

=SUBTOTAL(1, D3:D9) // 筛选后计算包含隐藏值的平均值（实际结果与筛选前相同，但可见部分正确）

=SUBTOTAL(101, D3:D9) // 筛选后计算可见单元格的平均值

```

虽然代码1在筛选后计算的结果与筛选前相同（因为它包含隐藏值），但如果你只关心可见单元格的平均值，应该使用代码101。

6. 忽略小计求合计

在财务报表中，经常会有小计和合计的行。如果你使用SUBTOTAL函数来计算合计，并且小计也是用SUBTOTAL函数计算的，那么SUBTOTAL函数会自动忽略由SUBTOTAL函数计算出来的数值。

```excel

=SUBTOTAL(9, C2:C12)

```

这个公式会计算C2:C12