网站搜索

关于 Excel 中溢出您需要了解的一切

Excel 2019 或更早版本不支持溢出和动态数组公式。

您应该首先了解的定义

在向您展示实际溢出的示例之前，先介绍一下本指南中一些相关 Excel 术语的定义：

  • 溢出：当 Excel 公式返回一组值（也称为数组）时。
  • 数组：单元格范围内的一组数据。例如，如果单元格 A1 到 A20 包含数据，则 A1:A20 是范围，该范围内的数据是数组。
  • 函数：预定义的公式，用于在公式中使用时执行计算。
  • 公式：函数、单元格引用、值、运算符和名称的组合，当在等号 (=) 后一起使用时，将返回结果
  • 动态数组公式：能够返回数组的公式。
  • 溢出数组公式：当前返回溢出数组的公式。

溢出的例子

首先，让我们看一个 Excel 中溢出的简单示例。此 Excel 电子表格包含六个测验团队及其第一周到第五周的分数。

因为

=B2:B7

是一个动态数组公式，将其输入到单元格 H2 中会返回一个覆盖 H2 到 H7 的溢出数组。请注意，当我选择单元格 H2 时，Excel 在这些单元格周围放置一个临时边框，以提醒我它是一个溢出数组。

如果我在单元格 H3 到 H7 中键入值，这会干扰并破坏 Excel 刚刚创建的溢出数组，从而导致 #SPILL！错误。我会更多地谈论#SPILL！很快就会出现错误。

然而，现在我想通过使用 OFFSET 函数更实际地利用这种类型的结果。在本例中，我希望 Excel 根据我在单元格 I1 中输入的数字告诉我每个团队在给定一周内的得分。

为此，我将在单元格 I2 中输入

=OFFSET(A2,0,I1,6,1)

因为我希望 Excel 从单元格 A2 开始，停留在同一行，移动我在单元格 I1 中键入的列数，并返回向下 6 行、跨 1 列的结果。由于 Excel 返回一个高度为 6 行的数组，即使我只在单元格 I2 中输入公式，结果也会从单元格 I1 溢出到 I7。

为了整理我的数据检索表，我还想将团队名称添加到 H 列。为此，我将输入

=A2:A7

到单元格 H2 中，这也会产生溢出数组。我还应用了一些格式来提高电子表格的可读性。

使用上述动态数组公式的好处之一是，如果数据发生更改或重新排列，溢出的数组将相应调整。

在最后一个示例中，我将使用 XLOOKUP 函数生成溢出数组。我的目标是显示某个团队在整个五周期间的一组结果，具体团队取决于单元格 I1 中的值。首先，我将输入动态数组公式

=B1:F1

到单元格 H3 中，以在单元格 H3 到 L3 中生成周数的溢出数组。

现在，我将在单元格 I1 中创建一个下拉列表，其中包含单元格 A2 到 A7 中列出的团队名称。为此，我将单击“数据”>“数据验证”，在“允许”字段中选择“列表”，然后选择单元格 A2 到 A7 来定义列表的来源，然后单击“确定”。

最后，我可以使用 XLOOKUP 函数返回我选择的团队在五周内的得分。在单元格 H3 中，我将键入：

=XLOOKUP(I1,A2:A7,B2:F7)

因为我希望 Excel 获取单元格 I1 中的团队名称，将其与 A2 到 A7 范围内的团队名称匹配，并从 B 列到 F 列返回相应的数组。由于结果有五列宽，因此显示为将数组溢出到单元格 H3 到 L3。在本例中，我从单元格 I1 的下拉列表中选择了 Quizpicable Me 团队。

通常导致数组溢出的其他函数包括 FILTER、SORT、UNIQUE 和 RANDARRAY。

引用溢出范围

要引用溢出范围，请在动态数组公式中的范围后面使用溢出范围运算符 (#)。使用与上面相同的示例，我想根据单元格 H3 到 L3 中的溢出数组显示给定团队的总得分。

所以，输入

=SUM(H3#)

写入单元格 L4 告诉 Excel 对从 H3 开始的溢出范围内的数组求和。

修复常见#SPILL！错误

某些情况会导致 Excel 返回#SPILL！错误。如果您的电子表格中发生这种情况，请注意以下事项，以及您可以采取哪些措施来解决该问题：

The spilled array crosses over a merged cell.

选择合并的单元格，然后单击“开始”选项卡的“对齐”组中的“合并并居中”以取消合并细胞。

Something is blocking the cells where the spilled array is trying to go.

删除或移动阻止溢出数组的单元格中的数据。

The range in the dynamic array formula extends beyond the worksheet's edges.

检查动态数组公式以确保单元格引用准确。

The dynamic array formula is used within a formatted Excel table.

将动态数组公式移动到格式化表格之外的位置。或者，通过选择表格中的一个单元格，然后单击“表格设计”选项卡的“工具”组中的“转换为范围”，将格式化的 Excel 表格转换为未格式化的范围。

The dynamic array formula has caused Excel to run out of memory.

在动态数组公式中引用较小的范围。

The spilled range is not known, as the dynamic array formula contains a volatile function.

动态数组公式不适用于未知长度的溢出数组。不幸的是，解决这个问题的唯一方法是避免使用创建可变长度数组的动态数组公式。

如果动态数组公式引用整列，则可以使用 TRIMRANGE 函数告诉 Excel 折扣空行，实质上是修剪数据以仅包含必要的单元格。

相关文章