Excel 跨表多条件动态汇总-夜雨聆风

Excel 跨表多条件动态汇总

场景引入

有50个工作表，每个工作表的格式都是一模一样的，具体要求是：

“把所有属于『北方公司』的项目的『7月销售额』全部加总。”

在各个项目表中，D3 单元格写着它所属的区域（比如“北方公司”），而 K9 单元格则是“7月销售”的数据。

核心公式

在汇总表的 K9 单元格中输入以下公式（向右/向下拖拽即可动态汇总）：

=SUMPRODUCT(N(T(INDIRECT("'"&表名列表&"'!D3"))="北方公司") * N(INDIRECT("'"&表名列表&"'!" & CELL("address", K9))))

(注：需将所有工作表名称列出，并定义为名称区域 表名列表)

公式原理解析

该公式的底层逻辑是：跨表生成条件数组 × 跨表生成数据数组 = 汇总值。

INDIRECT("'"&表名列表&"'!D3")

作用：跨表抓取所有分表的 D3 单元格，形成内存数组。

N(T(...)="北方公司")

作用：判断抓取的值是否为“北方公司”。T() 确保处理文本，N() 将 TRUE/FALSE 转为 1/0。
结果：生成一个条件数组，如 {1, 0, 1...}。

CELL("address", K9)

作用：获取当前单元格的相对地址 "K9"，确保公式拖拽时能动态变化，而不是只能取 K9 的值。

N(INDIRECT("'"&表名列表&"'!" & CELL(...)))

作用：跨表抓取所有分表对应位置（如 K9）的数据，形成数值数组，如 {1000, 5000, 2000...}。

SUMPRODUCT

作用：将【条件数组】与【数值数组】对应相乘并求和。
结果：(1×1000) + (0×5000) + (1×2000) = 3000。

进阶应用场景

1.跨表多条件汇总（AND 逻辑）如需增加条件（如 D4=”在建”），只需再乘一个条件数组：

=SUMPRODUCT(N(...)="北方公司") * N(INDIRECT(...)="在建") * N(...目标数据))

2.跨表多单元格汇总（区域求和）如果不是汇总单个 K9，而是要汇总每个表的 K9:M9 这三个月的数据之和，不能直接把公式里的 K9 改成 K9:M9，因为 CELL("address") 只能获取左上角单个单元格地址。解法：直接将目标区域拼接进 INDIRECT 中，并利用 SUM 嵌套（需按 Ctrl+Shift+Enter 作为数组公式录入，或在较新版本 Excel 中直接回车）：

=SUMPRODUCT((T(INDIRECT("'"&表名列表&"'!D3"))="北方公司") * SUMIF(INDIRECT("'"&表名列表&"'!K9:M9"), "<>0"))

3.规避空表名引发的 #REF! 错误若 表名列表 中包含空值，需用 IFERROR 包裹 INDIRECT 容错：

... * IFERROR(N(INDIRECT(...)), 0)

4.双向动态匹配查找将 CELL("address", K9) 替换为 ADDRESS(MATCH(...), MATCH(...))，即可无视各分表行列顺序差异，实现精准提取汇总。

⚠️ 性能预警

INDIRECT 会导致表格极度卡顿？INDIRECT 是 Excel 中的易失性函数 (Volatile Function)。这意味着只要您在表格中输入任何数据（哪怕是修改一个毫不相关的单元格），包含 INDIRECT 的公式都会被强制重新计算一次。特别是涉及多个单元格汇总时，矩阵计算量呈指数级增长， Excel 每次敲击回车都可能卡死。

如果遇到了性能瓶颈，或需要汇总大片区域（多行多列），请避免使用纯函数方案。