什么是 Excel 动态数组？ONLYOFFICE 实操案例详解

如果您曾耗费大量时间手动将公式逐行复制到数百行单元格中，或是艰难应对那些复杂的“Ctrl+Shift+Enter”数组公式操作，那么 Excel 动态数组将彻底改变您的工作方式。只需输入一个动态数组公式，它便会自动扩展，填充结果所需的所有单元格——无需重复操作，也无需手动更新。本指南将涵盖您需要了解的有关动态数组的一切内容。

Excel 中的动态数组是什么？

动态数组是由单个公式生成的一组数值，它会自动将其结果“溢出”到多个相邻单元格中。

在动态数组出现之前，若要生成多单元格结果，通常需要手动复制公式，或者使用传统的 CSE 数组公式（通过按下 Ctrl+Shift+Enter 键输入）。这类传统公式的输出范围是固定的，无法随着源数据的增减而自动扩展或收缩。

动态数组公式彻底消除了这一限制。您只需输入公式并按下 Enter 键，Excel 就会自动填充其余结果。如果源数据增加，溢出范围会自动扩展；如果数据被移除，溢出范围则会自动收缩。

示例：公式 =SORT(UNIQUE(A2:A100)) 可以在单个单元格中生成一个已排序且去重后的列表——无需使用 CSE 组合键，也无需借助辅助列。

动态数组的工作原理：溢出行为与核心函数

溢出行为

当动态数组公式生成多个结果时，Excel 会将其放置在一个连续的单元格区域中，该区域被称为溢出范围。关于溢出行为，有几点需要了解：

• 只有左上角单元格包含实际的公式，其余单元格仅显示溢出的数值结果。
• 随着源数据的变化，溢出范围会自动更新。
• 如果输出区域内的任何单元格已被占用（即存在遮挡），Excel 将返回 #SPILL! error——清除遮挡物即可解决。
• 可以使用 # 运算符来引用整个溢出范围：例如 =SUM(A2#) 会对 A2 单元格公式所溢出的所有数值求和，且当该溢出范围扩展时，求和范围也会自动调整。
• 动态数组公式在表格中无效，请将其放置在常规网格区域中。

核心动态数组函数

让我们来看看在使用 ONLYOFFICE 电子表格编辑器时最重要的函数：

1. FILTER — 提取符合特定条件的行：

=FILTER(A2:D11, B2:B11="东部")

2. UNIQUE — 返回去重后的列表:

=UNIQUE(A1:A8)

3. SORT — 通过公式对指定区域进行排序:

=SORT(A2:A9)

4. SEQUENCE — 生成一个包含连续数字的网格:

=SEQUENCE(4; 7; 1; 1)

5. RANDARRAY — 用随机数填充指定区域:

=RANDARRAY(5; 3; 1; 100; 1)

将最后一个参数设为 1 即可生成整数。每次刷新工作表时，该公式都会重新计算。

6. XLOOKUP — VLOOKUP 的现代替代方案，能够一次性返回多个列的数据:

=XLOOKUP(G2, A2:A200, B2:D200, "Not found")

结果会自动溢出并填充至相邻列中。如需了解更多关于查找公式的内容，请参阅我们的 Excel LOOKUP 函数指南。理解函数参数的工作原理对于将这些函数进行嵌套组合使用尤为有益。

动态数组的优势与考量

为何值得使用

• 自动扩展 — 输出结果会根据数据规模自动调整，无需人工干预。
• 公式精简 — 一条公式即可替代数十条重复公式，从而降低文件复杂度。
• 可读性强  — 相比于在500条完全相同的公式中逐一排查，审计单条源公式要容易得多。
• 减少错误 — 可避免因手动复制而导致的引用失效或公式细微差异等问题。
• 可组合性 — 函数之间可自然地串联使用，例如：=SORT(UNIQUE(FILTER(A2:A100, B2:B100=”Q1″)))。

实际应用考量

• 布局规划 — 请在公式下方及右侧预留足够的空白区域，以便容纳公式的溢出范围。
• 兼容性 — 这些函数仅支持 Excel 365、Excel 2021 或 ONLYOFFICE 文档；旧版 Excel 无法支持这些功能。
• 大型区域 — 若将 UNIQUE 或 FILTER 函数应用于整列数据，可能会导致重新计算速度变慢；建议将输入范围限制在实际包含数据的行数内。

动态数组及其组合应用的示例

一旦您熟练掌握了各个独立函数，便能体会到动态数组真正的强大之处——即通过串联组合来发挥其作用。这属于超越基础操作的进阶应用场景。

结合 RANDARRAY 与 RANK 函数制作绩效评分卡

场景：您想要模拟一个团队的绩效得分，并自动为每个人分配排名位置。

在单元格区域 A2:A6 中设置员工姓名。

在 B2 单元格中，生成介于 50 到 100 之间的随机分数：

=RANDARRAY(5; 1; 50; 100; 1)

一旦您对生成的分数感到满意，请将其粘贴为“数值”（快捷键 Ctrl+Shift+V → 仅粘贴数值），以防止在后续操作中分数自动重算。

在 C2 单元格中，为每个分数分配对应的排名位置：

=RANK(B2; $B$2:$B$6; 0)

将公式向下拖动至 C6 单元格。其中的“$”符号用于锁定引用范围，确保每个分数都能与这五个分数组成的完整集合进行比较；若缺少该符号，每一行将仅与自身进行排名比较。

最终结果是一个三列式的成绩单，清晰展示每位员工的姓名、得分以及在团队中的排名位置——且每当分数变动时，该排名结果都会自动更新。整个过程无需手动排序，也无需借助辅助列。

应用场景：最适合谁用？

数据分析师：利用 FILTER、UNIQUE 和 SORT 函数，无需辅助列或数据透视表，即可构建自动更新的数据汇总报表。

财务团队：构建能够随新交易数据录入而自动刷新的财务报表；并利用 SEQUENCE 函数来搭建财会周期的结构框架。

项目经理：创建任务追踪表，动态列出当前活跃的项目、唯一的任务负责人或逾期未完成的事项——整个过程无需编写宏代码。

何时应考虑使用动态数组？

• 需要实时数据且无需手动更新的各类报表与仪表盘。
• 用于构建下拉列表或数据汇总时的唯一值清单。
• 需要进行排名或筛选视图展示，但又不希望改动原始数据源的场景。
• 利用 XLOOKUP 函数进行多列查找匹配的场景。

获取 ONLYOFFICE，即刻体验动态数组功能

动态数组是现代电子表格工作中极为实用的升级之一。一条公式即可替代数十条旧式公式，计算结果自动实时更新，让您的文档保持整洁有序且易于审计。

不妨从小处着手：将 VLOOKUP 列替换为 XLOOKUP；在制作下一个下拉列表时尝试使用 UNIQUE 函数；或者利用 FILTER 函数构建动态汇总表，取代传统的静态“复制-粘贴”表格。您对电子表格的思维方式将因此迅速转变。

想要体验完整的动态数组功能？您可以注册免费的 ONLYOFFICE 协作空间账号在线使用；或者下载免费的桌面编辑器，在您的 Windows、Linux 或 macOS 电脑上离线使用：

在线使用   下载桌面应用