动态数组很强大，Excel的“新”功能

今天介绍下Excel的新功能，动态数组。

当然，这个功能并不算新，不过对于大部分使用Excel的朋友，这应该是个新功能。尤其是对于关注本公众号的粉丝来说，这个功能我们还没有介绍过。也没有在任何案例中用过。

这个功能只能在Excel 2019或者Office 365中使用。这也是我一直没有给大家介绍的最主要的原因。

但是，以动态数组为代表的一大批功能越来越多的出现在了Office 365和Excel 2019中。跟Power Query和Power Pivot不一样，这些功能都是对每个人的日常工作非常有帮助的，可以说是提高我们工作效率的利器，因此，还是很有必要了解和掌握一下的。

01

认识动态数组

在Excel中，数组都是被作为“高级”功能看待的！一般，说到数组公式，很多人心里就先有了三分敬畏。确实，一般用到数组公式的地方，那个公式往往比较复杂。而且，输入的时候还必须使用CTRL+SHIFT+回车三个键一起输入。

下图就是一个在以前版本的Excel中，使用数组公式的例子：

这里，我们使用了公式：

=IF(B2:B7>4,B2:B7,0)

这就是一个数组公式。

普通的IF公式是这样的，

=IF(B2>4,B2,0)

意思是判断B2是否大于4，如果是，则返回B2，否则就返回0。

而公式：

=IF(B2:B7>4,B2:B7,0)

的意思则是，判断B2:B7中的每个单元格的值是否大于4，如果是，则返回对应单元格的值，否则就返回0。

这个公式会返回一个1列6行的数据结果，所以是个数组公式，必须通过CTRL+SHIFT+ENTER输入：

返回的结果被放置在D2:D7区域

这个公式的返回结果区域（D2:D7)是一个整体，如果你想操作修改其中的一个单元格（比如删除第一个单元格的公式），就会报错：

这种传统的数组公式，有两个不太方便的地方：

  1. 公式输入必须实现确定范围，比如，我必须首先圈定跟返回数组一样大小（7行1列）的区域

  2. 删除公式时必须选中整个结果区域一起操作

  3. 输入公式必须使用CTRL+SHIFT+ENTER3键

而在新版本的Excel中，动态数组是这样的：

只要在D2单元格中输入公式：

=IF(B2:B7>4,B2:B7,0)

按回车（只要按回车就可以，不需要三键），公式输入成功：

注意看公式编辑栏中，公式并没有{}。

这就是动态数组！！！

不用实现选定结果区域，不用按三键输入。

02

动态数组详解

从上面的例子大家已经看到了，只要在一个单元格中输入公式，Excel就会自动将结果扩展到一个合适大小的区域，这个区域跟公式返回的数组行列数一致。

这种行为，在Excel中称为“溢出”，英文是SPILLING。

这是“动态”的一个基本含义，Excel自行判断返回值需要占据多大的区域，然后就将这个区域用公式结果填充。

使用动态数组公式有一个推荐的使用场景，即将源数据（公式引用的参数）放在超级表中，这样这个区域就是一个可以变化的区域。例如，我们可以将B2:B7转换为超级表，然后在数组公式中引用超级表：

这个公式的行为跟上面引用普通区域没什么区别。但是如果超级表的内容发生变化：

像变魔术一样，结果是随之变化的。

这是“动态”的另一重含义。实际上我们利用这个特性可以做出非常强大的应用效果，我会在后续其他文章中为大家介绍一些案例。

如果我们选中动态数组公式结果区域的任意的单元格，就会发现整个动态数组返回的区域被加上了一个蓝色边框：

边框提示我们这个区域是一个整体。边框内的任意单元格都不可更改（例如，你不能删除第2个单元格。如果你试图删除，也没有提示，只是没有反应而已，边框内只有左上角的单元格是可以被修改的，这个修改分为两种情况：

  1. 改成另外的数组公式
    结果区域重新变化，并用新的公式结果填充区域

  2. 改成非数组公式，例如输入一个文本“E学会”，或者干脆删除公式
    结果区域的边框不见了，对整个区域的锁定消失了，左上角的单元格变成了一个普通的单元格。

提示，如果你想删掉这个区域，只要删除左上角单元格的公式就可以了。

实际上，你选择不同的单元格，在编辑栏中会发现不同：

03

几个注意的问题

这里有几个需要注意的问题。

01

如果“溢出”的区域被占了怎么办

比如，如果我们在D2中输入数组公式，这个公式预计会溢出到D2:D13，但是，现在D13中已经有内容了：

此时，按回车输入公式后，在D2中会返回错误值：

#SPILL!

02

在超级表中不可以使用动态数组公式

尽管我们建议把动态数组公式的引用参数放在超级表中，但是在超级表中是不能使用数组公式的：

我们在超级表的第二列中，使用数组公式，期望溢出到整个列中，失败了：

其实，这种情况，根本不用数组公式：

03

新旧版本切换时动态数组会带来问题吗

答案是不会的。在动态数组公式这个问题上，Excel会智能的实现自动切换。具体的来说：

  • 如果在新版本创建了动态数组公式，保存后，再用旧版本打开时，动态数组公式会变成传统的数组公式（编辑栏中有大括号）。而这个文件在此用新版本打开后，这些公式又变成了动态数组公式了。

  • 如果在旧版本中创建了传统数组公式（用三键输入），用新版本打开时，仍然是传统的数组公式。

04

其他

在新版本的Excel中，微软还推出了一系列的返回动态数组的函数：

  • SORT

  • SORTBY

  • UNIQUE

  • FILTER

  • RANDARRAY

  • SEQUENCE

这些函数极大的丰富了我们进行数据处理的武器库。在后续文章中，我会为大家详细介绍

加入E学会，学习更多Excel函数，数据处理方法和案例。一次加入，永久有效，学习多达20门Excei精品课程

赞 (0)

相关推荐