1. 场景背景
在财务报销或项目费用管理中，数据录入往往遵循一种特定的结构：“费用名称”与“具体金额”分行存储。
例如，当单元格标记为“差旅补贴”时，其正下方的单元格才是具体的报销金额。我们需要快速提取所有“差旅补贴”对应的下方金额并求和。
传统的SUMIF只能对同一行或固定列求和，无法处理这种“当前行是条件，下一行是数值”的错位关系。此时，OFFSET结合数组公式是最佳解决方案。
2. 核心公式
=SUM(IF(D1:D20="差旅补贴", OFFSET(D1:D20, 1, 0)))重要提示：在旧版 Excel 中，输入完公式后必须按
Ctrl + Shift + Enter组合键（数组公式），新版 Excel (365/2021) 可直接回车。
公式逻辑拆解
D1:D20="差旅补贴"：检查 D 列区域内哪些单元格是“差旅补贴”。OFFSET(D1:D20, 1, 0)：- 以
D1:D20为基准区域。 1表示向下偏移1 行。0表示列偏移为 0（即保持在 D 列）。- 结果：原本指向 D1 的引用变成了 D2，D2 变成了 D3，以此类推。
- 以
IF(..., ...)：如果某行是“差旅补贴”，则取它下方偏移后的数值；否则忽略。SUM(...)：将提取出的所有数值相加。
3. 示例数据演示
假设数据都在D 列，结构如下：
下面是去除了第三列（即“说明”列）后的内容：
| 行号 | D 列内容 (费用/金额) |
|---|---|
| 1 | 办公采购 |
| 2 | 500 |
| 3 | 差旅补贴 |
| 4 | 800 |
| 5 | 客户招待 |
| 6 | 1200 |
| 7 | 差旅补贴 |
| 8 | 450 |
| 9 | 设备维修 |
| 10 | 300 |
| 11 | 差旅补贴 |
| 12 | 600 |
| … | … |
计算结果
在上述数据中，共有 3 处“差旅补贴”，其下方的金额分别为：800、450、600。
应用公式后：
800+450+600=1850 800 + 450 + 600 = \mathbf{1850}800+450+600=1850
4. 为什么这个公式比辅助列更好？
- 动态性强：不需要在 E 列写
IF(D1="差旅补贴", D2, 0)这种辅助公式再求和。 - 结构紧凑：直接在一单元格内完成“查找定位” + “偏移取值” + “求和”全过程。
- 适应性强：即使数据行数增加，只需调整范围（如
D1:D100），逻辑依然成立。
5. 注意事项
- 数据连续性：此方法假设“标识”和“金额”严格相邻（标识在上，金额在下）。如果中间有空行，逻辑会错位。
- 数组输入：如果是 Excel 2019 或更早版本，务必记住按
Ctrl+Shift+Enter，否则可能返回#VALUE!错误或只计算第一行。 - 边界处理：确保选取的范围（如 D1:D20）最后一行不是“差旅补贴”，否则
OFFSET会引用到范围外的空白单元格（虽通常不影响求和，但保持严谨更好）。
6. LET + FILTER 优化版本
对于新版 Excel（有些Excel版本不支持，请自行验证），可以利用LET + FILTER实现更清晰、可读、性能更好的计算，同时动态锚定“总计”行，无需固定最后一行：
=LET( totalRow, MATCH("总计", A:A, 0), dataRange, A1:INDEX(A:A, totalRow-1), nextRowValues, A2:INDEX(A:A, totalRow), SUM(FILTER(nextRowValues, dataRange="交通费")) )特点与优势：
- 动态定位“总计”行，无需手动调整范围。
dataRange用于匹配条件，nextRowValues与其行数完全一致，保证 FILTER 不报错。- 逻辑清晰：先筛选“交通费”对应下一行，再求和。
- 适合大数据量表格，性能优于 OFFSET 方案。
7. LET + OFFSET 兼容版本（兼容旧思路）
如果希望保留原 OFFSET 逻辑，同时增强可维护性，也可以使用 LET 进行优化：
=LET( totalRow, MATCH("总计", A:A, 0), dataRange, A1:INDEX(A:A, totalRow-1), offsetValues, OFFSET(dataRange, 1, 0), SUM(IF(dataRange="交通费", offsetValues)) )特点与优势：
- 保留了原 OFFSET + IF 的数组逻辑，容易理解。
- 动态定位“总计”行，无需手动固定范围。
- 适合旧版 Excel，注意输入后按Ctrl+Shift+Enter（数组公式）。
- 对小表格或兼容旧版系统仍然有效，但 OFFSET 是挥发性函数，大表格可能稍慢。
💡总结：
- FILTER 版本：现代 Excel、逻辑直观、性能好，推荐使用。
- OFFSET 版本：兼容旧版、易于理解，适合保留传统逻辑或旧版 Excel 使用。
（END）
