【Excel新函数】动态数组系列

一、简介

相比Power BI，Power Query和Power Pivot在行列层级运行计算，Excel一直以来主要还是在单元格层面上的。Excel里，每行每列所有单元格进行相同逻辑的计算时，常规的做法是在第一个单元格填写公式，然后向下向右填充每一个单元格。如下图所示，计算各洲折后价的表格，蓝色区域所有单元格都要填入一个公式。

近年Excel提供了动态数组运算能力和一系列相关函数，能够类似于Power BI那样，直接在行列层级运算。一方面节省了公式填充复制的工作量，另一方面为更复杂的计算提供了可能性和便捷性。所谓数组，可以粗略地理解为一组数据，即行或列的数据。上面这个例子，利用数组运算，我们先清空b3:d5区域，然后直接在B3单元格输入，只需填一次公式，即可自动将运算填充到整个区域。

=A3:A5*B2:D2

二、支持版本和函数功能

目前下列版本的Excel支持使用动态数组：

• Excel 365 for Windows
• Excel 365 for Mac
• Excel 2021
• Excel 2021 for Mac
• Excel for iPad
• Excel for iPhone
• Excel for Android tablets
• Excel for Android phones
• Excel for the web

动态数组函数包括下列这些：

• UNIQUE - 从一系列单元格中提取去重的项目。
• FILTER - 根据您定义的标准过滤数据。
• SORT - 按指定列对一系列单元格进行排序。
• SORTBY - 按另一个范围或数组对一系列单元格进行排序。
• RANDARRAY - 生成随机数数组。
• SEQUENCE - 生成序列号列表。
• TEXTSPLIT - 跨列或/和行按指定的分隔符拆分字符串。
• TOCOL - 将数组或范围转换为单个列。
• TOROW - 将范围或数组转换为单行。
• WRAPCOLS - 根据每行指定的值数将行或列转换为二维数组。
• WRAPROWS - 根据每列指定的值数将行或列重新整形为二维数组。
• TAKE - 从数组的开头或结尾提取指定数量的连续行或列。
• DROP - 从数组中删除一定数量的行或列。
• EXPAND - 将数组增长到指定的行数和列数。
• CHOOSECOLS - 从数组中返回指定的列。
• CHOOSEROWS - 从数组中提取指定的行

以及lookup函数升级版XLOOKUP和match函数升级版XMATCH。这些函数将在后面的文章里展开介绍。

三、应用案例——查询多列结果

以我们常用的vlookup为例。在下图这种场景中，需要查询不同产品，三个地区的售价。常规做法，我们需要在I2:K2三个单元格中，各写一个相似的vlookup公式。当然，可以把第三个参数统一改成column()-6，直接复制填充即可。

如果使用数组运算，我们只需要在I3单元格输入一个公式，即可自动填充到J和K中。注意，此时的数组是通过大括号来触发的。公式中第三个参数，用大括号引用了3、4、5列，即要查询第3、4、5列的值。

=VLOOKUP(H2,$A:$E,{3,4,5},0)

三、隐式交集运算符@

隐式交集逻辑将多个值减少为单个值。上文两个例子中，我们一个公式产生的结果，会自动填充到相邻的范围。假设我们不需要这种扩展填充，希望只显示当前单元格的值，那么我们只需要在公式中的数组部分前面加上@。比如第一例把公式改为：

=@B3:B5*@C2:E2

则仅C3单元格会显示结果，其他区域不填充，如下图所示。

四、优点

1. 简单高效

通过数组计算，以往一些麻烦的运算，可以更简便地实现。比如一列数据去重、计算非重复值的个数，可以无需点击去重按钮，直接通过一个公式实现。后续文章会分享这个做法。

2. 绝对和相对引用不再那么讲究

以前我们下拉填充公式的时候，比如第一个例子，往往需要考虑行列位置谨慎注意使用绝对还是相对引用。一招不慎就会出错。而动态数组的自动填充功能，使得单元格的引用不再那么严格，节省了很多时间。

五、不足和限制

1. 计算结果无法点击排序按钮来排序

动态数组生成的结果，不支持升序降序按钮来排序。但可以通过sort函数解决这个问题。

2. 无法删除结果数列中的任意值

动态数组生成的结果，是一个整体，无法像平常excel列那样，删除其中任意的值。

3. 不支持超级表和Power Query

预告：下期将会逐步介绍动态数组函数的应用