对于多工作表的财务模型而言，这不仅仅是操作体验的提升，而是真正扩展了可能性的边界--无需辅助列，无需 Ctrl+Shift+Enter，也无需预先知道输出结果需要多少行。
微软从 2018 年起向 Excel 365 订阅用户推出动态数组功能（预览版），2020 年正式上线，Excel 2021 版本同样包含此功能。截至 2026 年 6 月，所有主流 Excel 安装版本均已支持--但 2021 之前的旧版本不支持动态数组，这一点比大多数教程所承认的更为关键。
溢出区域的实际运作机制
在某个单元格中输入 =UNIQUE('GL'!C:C)，Excel 会对公式求值，统计不重复的值，然后从该单元格开始向下写入所有结果。顶部单元格显示公式本身，下方每个单元格显示灰色的"幽灵值"--它们同属一个数组，均为只读。
溢出区域是动态的。在 GL 工作表中新增 3 个成本中心，UNIQUE 的输出在下次计算时会自动扩展；删除 2 个则自动收缩。无需维护命名区域，无需硬编码行数。
根据微软 Dynamic array formulas and spilled array behavior 官方文档的定义："当公式能够向相邻单元格返回多个值时，称为溢出。能够返回多个结果的公式称为动态数组公式。"
对财务模型的意义在于：一个从数据源工作表动态拉取成本中心、产品线或业务实体列表的独立模型工作表，可以始终保持同步，无需任何手动维护。
# 运算符：大多数教程略过的关键
#（溢出区域运算符）用于引用动态数组公式当前的完整输出，并随其动态调整。微软在 Spilled range operator (#) 文档中对此有专门说明。
假设成本中心名称通过 =UNIQUE('GL'!C2:C) 从 Summary!B2 开始溢出。今天有 14 个成本中心，下个季度组织架构调整后可能变为 17 个。在任意位置引用完整列表，只需写 Summary!B2#--# 符号告诉 Excel 使用整个溢出区域，而非仅仅是 B2 单元格。
用 SUMIFS 将实际值与该动态列表进行匹配：
=SUMIFS(
'GL'!E:E,
'GL'!C:C, Summary!B2#,
'GL'!A:A, ">=" & Assumptions!$B$3,
'GL'!A:A, "<=" & Assumptions!$B$4
)
这将按成本中心返回一组实际值--今天是 14 个值，组织架构调整后自动变为 17 个--尺寸自动匹配 UNIQUE 的输出。条件数组无需硬编码范围，成本中心列表变更后也无需更新公式。
这与旧模式有着本质区别。过去使用 SUMIFS 时，条件范围往往是固定的 $B$2:$B$15，一旦有人新增科目，这个范围就会过时。
6 个动态数组函数及其替代对象
| 函数 | 功能 | 替代的旧方式 |
|---|---|---|
| FILTER | 返回满足条件的行/列 | 高级筛选、手工嵌套 IFERROR(INDEX(MATCH)) |
| SORT | 按一列或多列排序 | 辅助排序列、数据透视表变通方案 |
| SORTBY | 按独立辅助数组排序 | 基于排名的辅助列 |
| UNIQUE | 从区域中提取不重复列表 | 删除重复项 + 手动刷新 |
| SEQUENCE | 生成数字序列 | ROW(INDIRECT("1:"&n)) 等取巧写法 |
| RANDARRAY | 生成随机数数组 | 分散的 RAND() 单元格 |
XLOOKUP 在查找值为数组时同样返回数组，可作为多条件嵌套 MATCH/INDEX 的更简洁替代方案。
值得在模型中落地的 FP&A 应用场景
动态差异分析报告
FILTER 是这类场景的核心函数。提取损益表中所有超过重要性阈值的差异行：
=FILTER(
CHOOSE({1,2,3,4}, 'P&L'!B:B, 'P&L'!C:C, 'P&L'!D:D, 'P&L'!E:E),
ABS('P&L'!E:E) >= Assumptions!$C$2
)
其中 E 列为预算与实际的差异，Assumptions!$C$2 为你设定的阈值（例如 50 万元）。当季度收入为 4200 万元、毛利率为 38.5% 时，单个科目波动超过 50 万元的条目往往是董事会重点追问的对象--这个公式让它们自动浮出水面，每月实际数据到位后列表即时刷新。
无需硬编码的预测表头
SEQUENCE 可以彻底替代手动录入月份标签或维护表头行这种重复劳动：
=TEXT(SEQUENCE(1, 12, DATE(Assumptions!$B$1, 1, 1), 30), "mmm-yy")
从假设年份开始，自动生成 12 个月度表头。修改 B1 中的年份，12 个标签全部联动更新。对于每季度复用的董事会汇报材料，仅这一项每个周期就能节省约 5 分钟的清理时间。
在杠杆收购（LBO）或现金流折现（DCF）模型中，用于预测年份的 SEQUENCE 写法如下：
=SUMIFS(
'Revenue'!C:C,
'Revenue'!A:A, SEQUENCE(1, 5, Assumptions!$B$5, 1),
'Revenue'!B:B, "Recurring"
)
其中 B5 为第一个预测年份，SEQUENCE 自动生成年份数组 {2025, 2026, 2027, 2028, 2029}，一个公式返回 5 年的收入汇总。在 14.2 倍 EBITDA 进入倍数下，第 5 年的终值测算精度至关重要--而这一切的前提，是公式确实拉取了正确年份的数据。
动态实体或成本中心列表
UNIQUE 与 FILTER 组合使用，可以替代手动维护主数据列表的工作：
=SORT(UNIQUE(FILTER('GL'!C:C, 'GL'!B:B = Dashboard!$B$1)))
这将提取所选主体下按字母排序的唯一成本中心列表。在下游引用时用 C2# 替代硬编码区域，列表随 GL 数据增长自动保持最新。
对于银团贷款 DCF 场景中从共享总账导出文件拉取数据的情形，这种写法可以消除整类"列表对不上"的错误。
兼容性：无人提醒你的那些坑
动态数组在 Excel 2019 及更早版本中不可用。当 Excel 365 打开旧版文件时，会在曾经使用隐式交叉引用的公式前自动加上 @，因此你可能在旧模型中看到 =@VLOOKUP(...)--这是 Excel 为保持向下兼容所做的处理，并非错误。
麻烦的是反方向的情况。你用 FILTER 或 UNIQUE 构建的模型，在 Excel 2019 上只会显示 #NAME?，没有任何优雅降级，也没有任何警告--公式直接失效。
如果模型需要发送给银团成员、有限合伙人（LP）或收购方的尽职调查团队，务必事先测试。对于关键单元格，一种合理的容错写法是：
=IFERROR(FILTER('P&L'!B:D, 'P&L'!D:D<>0), "Dynamic arrays not supported")
至少可以做到优雅失败。对于确实需要在旧版 Excel 上运行的模型，请坚持使用 Ctrl+Shift+Enter 输入的传统数组公式，或改用 SUMIFS 加硬编码区域的方案。
溢出区域在表格（Table）内无法正常工作
对于习惯用 Ctrl+T 创建输入表格的分析师，有一个具体限制需要注意：当动态数组函数的输出区域与 Excel 表格（Table）的边界产生交叉时，大多数动态数组函数将无法使用。FILTER、UNIQUE 和 SEQUENCE 在这种情况下均会返回 #SPILL!。
实践中建议的分工方式：用 Table 存放输入数据（Table1[Revenue] 这类结构化引用语法值得保留），将动态数组公式放置在 Table 之外或相邻的普通单元格中。单独划出一个不带 Table 格式的输出区域，可以从根本上规避这个问题。
第一次遇到这个情况时往往会浪费 20 分钟--公式看起来完全正确，报错信息也毫无帮助。
关于 XLOOKUP 作为动态数组函数的说明
当查找值为数组时，XLOOKUP 返回数组结果。将一个区域作为查找值传入，它便返回对应区域的结果：
=XLOOKUP(
'Returns Analysis'!B3:B10,
'Assumptions'!$C$2:$C$50,
'Assumptions'!$D$2:$D$50,
"N/A"
)
这一个公式替代了 8 个独立的 VLOOKUP 或 INDEX/MATCH 公式。当查找范围从第 3:10 行变更为第 3:15 行时，溢出区域自动适配。
截至 2026 年 6 月，XLOOKUP 已在 Excel 365、Excel 2021 以及 Excel 网页版中可用，但不支持 Excel 2019 及更早版本。
常见问题解答
动态数组公式和传统的 Ctrl+Shift+Enter 数组公式有什么区别？
传统的 CSE 数组公式（Ctrl+Shift+Enter）需要提前确定输出范围大小，并手动选取目标区域后才能输入；动态数组公式只需在单个单元格中输入，Excel 自动判断并填充所需的溢出区域，无需预设范围。
#SPILL! 错误最常见的触发原因是什么？
最常见的原因有两类：一是溢出路径上存在非空单元格（哪怕是不可见的空格），二是动态数组公式的输出区域与 Excel 表格（Table）区域发生交叉。前者清空障碍单元格即可解决，后者需将公式移至 Table 范围之外。
在旧版本 Excel 中打开含动态数组公式的文件会怎样？
FILTER、UNIQUE、SEQUENCE 等函数在 Excel 2019 及更早版本中会直接返回 #NAME? 错误，没有降级处理。在 Excel 365 打开旧版文件时，原有隐式交叉引用公式会被自动加上 @ 前缀（如 =@VLOOKUP(...)），这是兼容性标记而非错误。
# 溢出区域运算符能跨工作表使用吗？
可以。Summary!B2# 这样的跨表溢出区域引用完全有效，下游公式会自动追踪源动态数组的完整输出，无论其大小如何变化。
动态数组公式是否支持 Google Sheets？
Google Sheets 通过 ARRAYFORMULA 函数支持类似的数组扩展行为，但语法和功能与 Excel 的动态数组并不完全对等。FILTER、UNIQUE 等函数在 Google Sheets 中已原生支持，无需 ARRAYFORMULA 包裹，但 # 运算符和部分边界行为存在差异。
当溢出区域层层嵌套--UNIQUE 输出喂入 SUMIFS、再汇总至摘要工作表--所形成的依赖链，其审计难度远高于传统的单元格引用。一旦汇总层出现问题，跨工作表追溯三层动态公式的链路往往相当耗时。
如果你发现自己花更多时间在排查公式而非分析数据上，ModelMonkey 可追踪跨工作表的公式依赖关系并自动定位问题所在。查看月度方案和一次性积分包--同时支持 Google Sheets 和 Excel。