Excel动态阵列，功能和公式

Excel 365的革命性计算引擎更新让数组公式变得简单易懂，不再只是高级用户的专属技能。本教程讲解Excel动态数组的概念，并展示如何利用它们提高工作表效率，简化设置。

Excel数组公式一直被认为是专家和公式高手的专利。“可以用数组公式解决”这句话，常常会让许多用户立刻反应：“有没有其他方法？”

动态数组的引入是期待已久且非常受欢迎的改变。由于它们能够以简单的方式处理多个值，无需任何技巧和窍门，每个Excel用户都能理解和轻松创建动态数组公式。

- Excel动态数组

• 动态数组可用性
• 动态数组函数
• 动态数组公式示例
• 溢出区域 - 一个公式，多个单元格
• 溢出区域引用（# 符号）
• 隐式交集和@字符
• 动态数组的优势
• 动态数组的局限性
• 动态数组与传统的CSE数组公式
• 向后兼容性
• Excel动态数组公式无效

Excel动态数组

动态数组是可调整大小的数组，它们会自动计算并将值返回到多个单元格中，而这些值都基于单个单元格中输入的公式。

三十多年来，Microsoft Excel经历了许多变化，但有一点始终如一：一个公式，一个单元格。即使使用传统的数组公式，也需要在每个希望显示结果的单元格中输入公式。有了动态数组，这条规则不再适用。现在，任何返回数组值的公式都会自动溢出到相邻单元格中，无需按Ctrl Shift Enter或执行任何其他操作。换句话说，操作动态数组就像操作单个单元格一样简单。

让我们用一个非常基本的例子来说明这个概念。假设您需要将两组数字相乘，例如，计算不同的百分比。

在Excel的非动态版本中，下面的公式仅适用于第一个单元格，除非您在多个单元格中输入它并按Ctrl Shift Enter将其显式地设为数组公式：

=A3:A5*B2:D2

现在，看看在Excel 365中使用相同的公式会发生什么。您只需在一个单元格（在本例中为B3）中输入它，按Enter键……整个区域就会立即填充结果：

用单个公式填充多个单元格称为溢出，填充的单元格区域称为溢出区域。

需要注意的是，最近的更新不仅仅是处理Excel数组的一种新方法。事实上，这是对整个计算引擎的突破性改变。有了动态数组，Excel函数库中添加了一系列新函数，现有函数也开始运行得更快、更高效。最终，新的动态数组应该会完全取代使用Ctrl Shift Enter快捷键输入的旧式数组公式。

Excel动态数组可用性

动态数组于2018年在Microsoft Ignite大会上推出，并于2020年1月发布给Office 365订阅者。目前，它们可在Microsoft 365订阅和Excel 2021中使用。

以下版本支持动态数组：

• Windows版Excel 365
• Mac版Excel 365
• Excel 2021
• Mac版Excel 2021
• iPad版Excel
• iPhone版Excel
• Android平板电脑版Excel
• Android手机版Excel
• 网页版Excel

Excel动态数组函数

作为新功能的一部分，Excel 365中引入了6个新函数，这些函数可以原生处理数组并将数据输出到单元格区域。输出始终是动态的——当源数据发生任何变化时，结果会自动更新。因此，该组的名称是——动态数组函数。

这些新函数可以轻松应对许多传统上被认为难以解决的任务。例如，它们可以删除重复项、提取和计数唯一值、过滤空值、生成随机整数和小数、按升序或降序排序等等。

下面简要介绍每个函数的功能以及深入教程的链接：

• UNIQUE - 从单元格区域中提取唯一项。
• FILTER - 根据您定义的条件过滤数据。
• SORT - 按指定的列对单元格区域进行排序。
• SORTBY - 按另一个区域或数组对单元格区域进行排序。
• RANDARRAY - 生成一个随机数数组。
• SEQUENCE - 生成一个序列号列表。
• TEXTSPLIT - 按指定的定界符将字符串拆分为列或/和行。
• TOCOL - 将数组或区域转换为单列。
• TOROW - 将区域或数组转换为单行。
• WRAPCOLS - 根据每行指定的值数将行或列转换为二维数组。
• WRAPROWS - 根据每列指定的值数将行或列重新整形为二维数组。
• TAKE - 从数组的开头或结尾提取指定数量的连续行或列。
• DROP - 从数组中删除一定数量的行或列。
• EXPAND - 将数组扩展到指定数量的行和列。
• CHOOSECOLS - 从数组中返回指定的列。
• CHOOSEROWS - 从数组中提取指定的行。
• GROUPBY - 基于一列或多列中的值按行对数据进行分组和聚合。
• PIVOTBY - 按行和列对数据进行分组，并聚合关联的值。

此外，还有两个流行的Excel函数的现代替代品，它们并非正式属于该组，但可以利用动态数组的所有优势：

• XLOOKUP - 是VLOOKUP、HLOOKUP和LOOKUP功能更强大的后继者，它可以在列和行中查找并返回多个值。
• XMATCH - 是MATCH函数功能更通用的后继者，它可以执行垂直和水平查找并返回指定项的相对位置。

Excel动态数组公式

在现代版本的Excel中，动态数组行为已深度集成，并成为所有函数的原生行为，即使这些函数最初并非设计为与数组一起工作。简而言之，对于任何返回多个值的公式，Excel都会自动创建一个可调整大小的区域，将结果输出到该区域。由于这种能力，现有函数现在可以发挥神奇的作用！

下面的例子展示了动态数组公式的实际应用，以及动态数组对现有函数的影响。

示例1. 新的动态数组函数

此示例演示了使用Excel动态数组函数可以实现多么快速简单的解决方案。

要从列中提取唯一值列表，您通常会使用如下所示的复杂CSE公式。在动态Excel中，您只需要一个基本形式的UNIQUE公式：

=UNIQUE(B2:B10)

您在任何空单元格中输入公式并按Enter键。Excel会立即提取列表中的所有不同值，并将它们输出到从您输入公式的单元格（在本例中为D2）开始的单元格区域中。当源数据发生变化时，结果会自动重新计算和更新。

示例2. 在一个公式中组合多个动态数组函数

如果没有办法用一个函数完成任务，可以将几个函数链接在一起！例如，要根据条件过滤数据并按字母顺序排列结果，可以将SORT函数包装在FILTER周围，如下所示：

=SORT(FILTER(A2:C13, B2:B13=F1, "No results"))

其中A2:C13是源数据，B2:B13是要检查的值，F1是条件。

示例3. 将新的动态数组函数与现有函数一起使用

由于Excel 365中实现的新计算引擎可以轻松地将传统公式转换为数组，因此没有什么可以阻止您将新旧函数组合在一起。

例如，要计算某个区域中共有多少唯一值，可以将动态数组UNIQUE函数嵌套在旧的COUNTA函数中：

=COUNTA(UNIQUE(B2:B10))

示例4. 现有函数支持动态数组

如果您在较旧版本的Excel（例如Excel 2016或Excel 2019）中向TRIM函数提供单元格区域，它将为第一个单元格返回单个结果：

=TRIM(A2:A6)

在动态Excel中，相同的公式会处理所有单元格并返回多个结果，如下所示：

示例5. 返回多个值的VLOOKUP公式

众所周知，VLOOKUP函数旨在根据您指定的列索引返回单个值。但是，在Excel 365中，您可以提供一组列号以从几列返回匹配项：

=VLOOKUP(F1, A2:C6, {1,2,3}, FALSE)

示例6. 简化的TRANSPOSE公式

在早期版本的Excel中，TRANSPOSE函数的语法不容出错。要旋转工作表中的数据，您需要计算原始列和行，选择相同数量的空单元格但更改方向（在大型工作表中这是一项令人费解的操作！），在选定的区域中键入TRANSPOSE公式，然后按Ctrl Shift Enter才能正确完成。呼！

在动态Excel中，您只需在输出区域的最左单元格中输入公式并按Enter键：

=TRANSPOSE(A1:B6)

搞定！

溢出区域 - 一个公式，多个单元格

溢出区域是包含动态数组公式返回的值的单元格区域。

当选择溢出区域中的任何单元格时，会出现蓝色边框，以显示其内部的所有内容都是由左上角单元格中的公式计算的。如果您删除第一个单元格中的公式，所有结果都将消失。

溢出区域是一个非常棒的功能，它使Excel用户的操作更加轻松。以前，使用CSE数组公式时，我们必须猜测要将它们复制到多少个单元格中。现在，您只需在第一个单元格中输入公式，然后让Excel处理其余部分。

注意。如果其他数据阻塞了溢出区域，则会发生#SPILL错误。一旦移除阻塞数据，错误就会消失。

有关更多信息，请参阅Excel溢出区域。

溢出区域引用（# 符号）

要引用动态数组公式返回的整个溢出区域，请在区域左上角单元格的地址后加上井号或磅符号（#）。

例如，要查找A2中RANDARRAY公式生成的随机数有多少个，请将溢出区域引用提供给COUNTA函数：

=COUNTA(A2#)

要将溢出区域中的值相加，请使用：

=SUM(A2#)

提示：

• 要快速引用溢出区域，只需使用鼠标选择蓝色框内的所有单元格，Excel就会为您创建溢出引用。
• 与常规区域引用不同，溢出区域引用是动态的，会自动响应区域大小调整。 有关更多详细信息，请参阅溢出区域运算符。

隐式交集和@字符

在动态数组Excel中，公式语言还有一个重要的变化——引入了@字符，称为隐式交集运算符。

在Microsoft Excel中，隐式交集是一种公式行为，它将多个值减少为单个值。在旧版Excel中，单元格只能包含单个值，因此这是默认行为，不需要特殊的运算符。

在新版Excel中，所有公式默认都被视为数组公式。如果您不希望在特定公式中使用数组行为，则可以使用隐式交集运算符。换句话说，如果您希望公式只返回一个值，请在函数名称前加上@，它就会像传统Excel中的非数组公式一样运行。

要查看它在实践中的工作方式，请查看下面的屏幕截图。

在C2中，有一个动态数组公式，它将结果溢出到许多单元格中：

=UNIQUE(A2:A9)

在E2中，函数前缀为@字符，该字符调用隐式交集。结果，只返回第一个唯一值：

=@UNIQUE(A2:A9)

有关更多信息，请参阅Excel中的隐式交集。

Excel动态数组的优势

毫无疑问，动态数组是多年来Excel最好的增强功能之一。与任何新功能一样，它们都有优缺点。幸运的是，新的Excel动态数组公式的优点是压倒性的！

简单而强大

动态数组使创建更强大的公式变得更加简单。以下是一些示例：

• 提取唯一值：传统公式 | 动态数组函数
• 计数唯一值和不同值：传统公式 | 动态数组函数
• 按字母顺序对列进行排序：传统公式 | 动态数组函数

所有公式的原生支持

在动态Excel中，您无需担心哪些函数支持数组，哪些函数不支持。如果公式可以返回多个值，它将默认这样做。这也适用于算术运算和传统函数，如本例所示。

嵌套动态数组函数

要解决更复杂的任务的解决方案，您可以自由地组合新的Excel动态数组函数，或将它们与旧函数一起使用，如此处和此处所示。

相对和绝对引用不太重要

由于采用了“一个公式，多个值”的方法，因此无需使用$符号锁定区域，因为从技术上讲，公式只在一个单元格中。因此，在大多数情况下，使用绝对、相对或混合单元格引用（这始终是新手用户困惑的来源）实际上并不重要——动态数组公式无论如何都会产生正确的结果！

动态数组的局限性

新的动态数组很棒，但与任何新功能一样，也有一些注意事项。

结果无法以通常的方式排序

动态数组公式返回的溢出区域无法使用Excel的排序功能进行排序。任何此类尝试都会导致“无法更改数组的一部分”错误。要将结果从小到大或从大到小排列，请将当前公式包装在SORT函数中。例如，这就是您可以同时过滤和排序的方式。

无法删除溢出区域中的任何值

由于同样的原因，无法删除溢出区域中的任何值：无法更改数组的一部分。此行为是预期且合乎逻辑的。传统的CSE数组公式也是这样工作的。

不受Excel表格支持

此功能（或错误？）相当出乎意料。动态数组公式不适用于Excel表格中，仅适用于常规区域。如果您尝试将溢出区域转换为表格，Excel会这样做。但是，您只会看到#SPILL！错误，而不是结果。

不适用于Excel Power Query

动态数组公式的结果无法加载到Power Query中。例如，如果您尝试使用Power Query将两个或多个溢出区域合并在一起，则此操作无效。

动态数组与传统的CSE数组公式

随着动态数组的引入，我们可以讨论两种类型的Excel：

1. 动态Excel完全支持动态数组、函数和公式。目前只有Excel 365和Excel 2021。
2. 传统Excel，也称为非动态Excel，其中只支持Ctrl Shift Enter数组公式。它是Excel 2019、Excel 2016、Excel 2013及更早版本。

不用说，动态数组在各个方面都优于CSE数组公式。尽管出于兼容性原因保留了传统的数组公式，但从现在开始，建议使用新的数组公式。

以下是最重要的区别：

• 动态数组公式在一个单元格中输入，并使用常规Enter键完成。要完成旧式的数组公式，您需要按Ctrl Shift Enter。
• 新的数组公式会自动溢出到多个单元格。CSE公式必须复制到单元格区域才能返回多个结果。
• 动态数组公式的输出会随着源区域中的数据变化而自动调整大小。如果返回区域太小，CSE公式会截断输出；如果返回区域太大，则会在多余的单元格中返回错误。
• 动态数组公式可以轻松地在单个单元格中编辑。要修改CSE公式，您需要选择并编辑整个区域。
• 在CSE公式区域中无法删除和插入行——您需要先删除所有现有公式。使用动态数组，插入或删除行不是问题。

向后兼容性：传统Excel中的动态数组

当您在旧版Excel中打开包含动态数组公式的工作簿时，它会自动转换为用大括号{}括起来的常规数组公式。当您在新版Excel中再次打开工作表时，大括号将被删除。

在传统Excel中，新的动态数组函数和溢出区域引用会在前面加上_xlfn，以指示不支持此功能。溢出区域引用符号（#）将被ANCHORARRAY函数替换。

例如，以下是Excel 2013中UNIQUE公式的显示方式：

大多数动态数组公式（但并非全部！）都会在传统Excel中继续显示其结果，直到您对它们进行任何更改。编辑公式会立即破坏它并显示一个或多个#NAME？错误值。

Excel动态数组公式无效

根据函数的不同，如果使用不正确的语法或无效的参数，可能会发生不同的错误。以下是使用任何动态数组公式时可能遇到的三个最常见的错误。

#SPILL! 错误

当动态数组返回多个结果，但某些内容阻塞了溢出区域时，会发生#SPILL！错误。

要修复此错误，您只需清除或删除溢出区域中任何不完全为空的单元格。要快速找到所有妨碍的单元格，请单击错误指示器，然后单击选择阻塞单元格。

除了非空的溢出区域外，此错误还可能由其他一些原因引起。有关更多信息，请参阅：

• Excel #SPILL 错误 - 原因和解决方法
• 如何修复VLOOKUP、INDEX MATCH、SUMIF中的#SPILL！错误

#REF! 错误

由于工作簿之间对外部引用的支持有限，动态数组需要同时打开两个文件。如果源工作簿已关闭，则会显示#REF！错误。

#NAME? 错误

如果您尝试在旧版Excel中使用动态数组函数，则会发生#NAME？错误。请记住，新函数仅在Excel 365和Excel 2021中可用。

如果在受支持的Excel版本中出现此错误，请仔细检查有问题的单元格中的函数名称。可能是它被错误输入了:)

这就是如何在Excel中使用动态数组的方法。希望您会喜欢这个很棒的新功能！无论如何，感谢您的阅读，希望下周能在我们的博客上见到您！

以上是Excel动态阵列，功能和公式的详细内容。更多信息请关注PHP中文网其他相关文章！