关于Subtotal

Cassidy

好的，我们来全面讲解一下 Excel 中功能强大且非常实用的 **SUBTOTAL 函数**。

与 DATEDIF 不同，SUBTOTAL 是 Excel 的“正式成员”，在函数列表中可以直接找到。它的核心功能是**对可见单元格进行汇总计算**，这意味着它能智能地忽略被手动隐藏或通过筛选过滤掉的单元格。

### 1. 函数语法

```excel

=SUBTOTAL(功能代码, 引用1, [引用2], ...)

```

- **功能代码（必填）**： 一个数字（1-11 或 101-111），用于指定您要使用的汇总函数（如求和、求平均、计数等）。

- **引用1（必填）**： 您想要对其进行计算的第一个单元格区域。

- **[引用2], ...（可选）**： 您想要对其进行计算的其他 2 到 254 个单元格区域。

---

### 2. 功能代码详解（核心）

SUBTOTAL 的精髓在于这两组功能代码：

| 功能代码 | 对应函数 | 功能说明 |

| :--- | :--- | :--- |

| **1** | AVERAGE | 求平均值 |

| **2** | COUNT | 计算数值单元格的个数 |

| **3** | COUNTA | 计算非空单元格的个数 |

| **4** | MAX | 求最大值 |

| **5** | MIN | 求最小值 |

| **6** | PRODUCT | 求乘积 |

| **7** | STDEV | 计算样本标准偏差 |

| **8** | STDEVP | 计算总体标准偏差 |

| **9** | SUM | 求和 |

| **10** | VAR | 计算样本方差 |

| **11** | VARP | 计算总体方差 |

| **101-111** | (同上) | 功能与 1-11 完全相同，但**会忽略任何原因隐藏的行**（包括手动隐藏）。 |

**关键区别：**

- **代码 1-11**： 在筛选数据时，会忽略被筛选掉的行。但如果你**手动隐藏**了某些行，它们仍然会被计算在内。

- **代码 101-111**： 无论是通过**筛选**还是**手动隐藏**的行，都会被忽略。

**最佳实践：** 在绝大多数情况下，**推荐使用 101-111 这组代码**，因为它的行为更一致，能排除所有隐藏行的影响。

---

### 3. 核心特性与优势

#### 特性1：只对可见单元格计算

这是 SUBTOTAL 最主要的价值。当你的数据列表应用了筛选后，使用 SUM 或 AVERAGE 函数会仍然计算所有原始数据，而 SUBTOTAL 只会计算筛选后你能看到的行。

**示例：**

假设 A2:A10 是销售额，你筛选出“部门A”的数据。

- `=SUM(A2:A10)` 会返回所有部门的总和。

- `=SUBTOTAL(9, A2:A10)` 会返回**只有部门A**的总和。

#### 特性2：自动忽略嵌套的 SUBTOTAL

如果一个计算区域里已经包含了 SUBTOTAL 公式，SUBTOTAL 函数会非常智能地**忽略**这些单元格，避免重复计算。而 SUM 函数则会把里面的 SUBTOTAL 结果再加一遍。

#### 特性3：动态适应筛选状态

使用 SUBTOTAL 汇总的表头，在与 Excel 表格（Ctrl+T）和筛选功能结合时，可以动态显示当前可见数据的统计结果，非常适合做动态报表。

---

### 4. 实用示例

假设你有如下数据（A1:B10）：

| 部门 | 销售额 |

| :--- | :--- |

| A | 100 |

| B | 200 |

| A | 150 |

| C | 300 |

| B | 250 |

| A | 120 |

| C | 180 |

| B | 220 |

| **总计** | **1520** |

**场景1：基本筛选汇总**

1. 你对“部门”列进行筛选，只选择“A”。

2. 此时可见的销售额是 100, 150, 120，总和为 370。

3. 在 B11 单元格输入以下公式并观察结果：

- `=SUM(B2:B9)` → 结果依然是 **1520** （错误，计算了全部）

- `=SUBTOTAL(9, B2:B9)` → 结果是 **370** （正确，只计算了可见的A部门）

**场景2：创建动态汇总行**

在一个表格的末尾，你希望始终显示当前筛选状态下的平均值和总和。

- `="可见单元格平均: "&SUBTOTAL(101, B2:B9)`

- `="可见单元格总和: "&SUBTOTAL(109, B2:B9)`

当你筛选不同部门时，这两个值会自动更新。

**场景3：忽略手动隐藏的行**

假设你手动隐藏了第5行（B部门的250）。

- `=SUBTOTAL(9, B2:B9)` → 结果是 **1270** (1520 - 250)，因为它只忽略了筛选行，但没忽略手动隐藏行)。

- `=SUBTOTAL(109, B2:B9)` → 结果是 **1270** (如果之前有筛选，它会忽略所有隐藏行)。在这个场景下，由于是手动隐藏，**109** 才是正确的代码。

---

### 5. 与 SUM， AVERAGE 等的对比总结

| 特性 | SUBTOTAL | SUM/AVERAGE 等 |

| :--- | :--- | :--- |

| **对筛选数据** | ✅ **只计算可见行** | ❌ 计算所有行 |

| **对手动隐藏行** | ✅ (代码101-111) **只计算可见行** | ❌ 计算所有行 |

| **避免重复计算** | ✅ 自动忽略区域内的其他 SUBTOTAL | ❌ 会重复计算 |

| **动态报表** | ✅ **非常适合**，结果随筛选动态变化 | ❌ 不适合，结果固定不变 |

| **计算速度** | 相对稍慢 | 更快 |

### 总结

**什么时候使用 SUBTOTAL？**

- 当你的数据**需要被筛选**，并且你希望汇总行能**实时反映筛选结果**时。

- 当你需要创建一个**动态仪表板或报表**时。

- 当你需要汇总数据，但又不确定某些行是否会被手动隐藏时。

**记住一个简单的原则：** 只要你的数据有可能被筛选或隐藏，需要动态统计可见部分，**SUBTOTAL 就是你的首选工具**。在日常工作中，直接使用 **101-111** 这组代码可以避免绝大多数意外情况。