Excel动态数组公式和溢出的数组行为详解

Excel动态数组公式和溢出的数组行为详解

Excel中的动态数组公式和溢出行为是提高工作效率的重要工具。本文将详细介绍什么是溢出、动态数组公式的特点以及如何使用这些公式，帮助用户更好地处理数据和复杂计算。

返回一组值（也称为数组）的 Excel 公式将这些值返回到相邻单元格。 这种行为称为“溢出”。
可以返回可变大小的数组的公式称为动态数组公式。 当前返回成功溢出的数组的公式可以称为溢出的数组公式。
下面是一些说明，可帮助你理解和使用这些类型的公式。

溢出的含义是什么？

注意: 较旧的数组公式（称为旧数组公式）始终返回固定大小的结果 - 它们始终溢出到相同数量的单元格中。 本主题中所述的溢出行为不适用于旧的数组公式。

溢出表示公式已生成多个值, 并且这些值已放置在相邻的单元格中。 例如，**=SORT(D2:D11,1,-1),**按降序对数组进行排序，将返回 10 个行高的相应数组。 但只需在左上角的单元格中输入公式，或者在这种情况下输入 F2，它将自动向下溢出到单元格 F11。

要点

• 按 Enter 确认公式时，Excel 将为你动态调整输出区域的大小，并将结果放入该区域内的每个单元格。
• 如果是编写动态数组公式以对数据列表执行操作，则将它放在Excel 表中，然后使用结构化引用来引用数据会很有用。 这是因为在表中添加或删除行时，结构化引用会自动调整。
• Excel 表本身不支持溢出的数组公式，因此应将它们放在表外的网格中。 表格最适合容纳独立数据的行和列。
• 输入溢出数组公式后，在溢出区域中选择任何单元格时，Excel 将在该区域周围放置突出显示的边框。 选择区域外的单元格时，边框将消失。

• 只有溢出区域中的第一个单元格可编辑。 如果在溢出区域中选择另一个单元格，公式将显示在公式栏中，但文本为“幻像”，且无法更改。 如果需要更新公式，则应选择数组区域中的左上角单元格，根据需要进行更改，然后当你按Enter时，Excel 将自动更新剩余的溢出区域。
• 公式重叠 - 如果存在任何阻止输出区域的内容，则无法输入数组公式。 如果发生这种情况，Excel 将返回#溢出!错误，表示存在阻塞。 如果删除了“阻塞”，公式将按预期溢出。 在下面的示例中，公式的输出区域与另一个包含数据的区域重叠，并显示为带有虚线边框的重叠单元格，其值表示不能溢出。 删除阻止的数据，或将其复制到其他位置，公式将按预期溢出。

• 由于兼容性原因，仍然支持通过CTRL+SHIFT+ENTER(CSE) 输入的旧版数组公式，但不应再使用它们。 如果需要，可通过以下方式将旧数组公式转换为动态数组公式：查找数组区域中的第一个单元格，复制公式的文本，删除旧版数组的整个区域，然后在左上角单元格中重新输入公式。 将旧版数组公式升级到动态数组公式之前，应注意两者之间的一些计算差异。
• Excel 对工作簿之间的动态数据提供有限支持，并且仅当这两个工作簿时都处于打开状态时才支持此方案。 如果关闭源工作簿，刷新时，任何链接的动态数组公式都将返回#REF! 错误。