数组索引必须为正整数或逻辑值_Office 365函数新世界——动态数组

革新一个旧的函数体系，无外乎从这么几个方面入手，函数的运行效率、函数的编写方式以及扩展新的函数功能。今天咱们就学习一下，看看365新函数是如何通过这三个方面打破旧函数条条框框的。

365函数系列推出了一个新的概念，叫做动态数组。它打破了旧数组的概念，不需要按组合键即可执行数组运算，不需要选中范围即可返回多项结果，且结果区域会动态调整，在保持运算高效的同时，灵活性也不差。那么……什么是动态数组？

谈到函数，很多朋友的第一印象是这家伙只适合小数据的腾挪躲闪，数据量一大，就沦落为卡德斯基先生。比如VLOOKUP函数，大概处理个2万左右的数据就有点儿卡顿了——但这印象应该被打破。

事实上，从Excel 2016版开始，微软就对VLOOKUP、HLOOKUP、MATCH等函数的运算机制进行了强力优化，从相同表区域查找多个列时，将为所搜索的列范围创建内部缓存索引，后续查找中，将重用这一缓存的索引——打个响指，365版本中的VLOOKUP函数即便是计算十几万行数据也不是什么大问题。

而在365版本中，绝大部分参数涉及到单元格引用类的函数都采用了相同的优化措施，比如我们所熟悉的SUMIF(S)、AVERAGEIF(S)、COUNTIF(S)、XLOOKUP等等。

此外还涉及到LAA 内存改进、完整列引用情况下减少所占内存和CPU等——也就是说，通常情况下，365版本函数的运算效率远远优于普通版本的。

再说一下365函数新功能。

这个是重点，照例点杯82年的雪碧先。

365函数新功能主要表现在两方面，一个是新函数，比如排序函数SORT/SORTBY；去重函数UNIQUE；高效查询筛选FILTER以及号称灭霸的XLOOKUP函数等等。另外一个就是动态数组功能。

"数组公式返回的是一组元素；但是Excel一个单元格只能显示数组元素中的一个结果(默认为数组中的首个元素)。

如果需要显示数组公式的全部元素呢？——可以使用区域数组公式。

举个简单的例子

如上图所示的表格，选中D2:D5单元格区域，在编辑栏编写公式=B2:B5*C2:C5，然后按Ctrl+Shift+Enter数组三键结束公式输入，也就在D2:D5区域内输入了同一条数组公式，这就是区域数组公式。

该公式返回一个内存数组{12;70;30;15}，系统会将数组的每个元素依次显示在D2:D5区域中。

在一个单元格中输入的公式被称为数组公式，而所谓区域数组公式，也就是在多个单元格中输入同一数组公式，它可以有序返回结果数组中的每个元素。"

在365中，这一规则也被打破了。

在普通Excel版本，数组公式需要按Ctrl+Shift+Enter三键结束才能启用多项运算；365版本抛弃了这个键，绝大部分数组公式都被默认执行数组运算，也就不再需要摁三贱。

更重要的是，如果一个函数公式返回的是多项结果，365会将多个结果自动填充到相关单元格区域，前提是这些区域不存在数据。

依然以上图所示数据为例，不需要选中D2:D5区域，只需要在D2单元格输入公式=B2:B5*C2:C5，系统就会自动将该公式的计算结果，也就是将内存数组{12;70;30;15}中的元素依次显示在D2:D5区域中。

这有什么好处呢？

我们以前一直给函数新人讲，数组的运算效率是优于大批量普通函数的，但一直被打脸，数组公式用多了Excel都卡的很——

事实上，数组运算的效率当然是高于大批量普通函数公式。之所以效率低下，是由于在实际运用时，大家总是在每个单元格都输入数组公式，每个单元格都在做重复的数组运算，这不卡就见鬼了不是？

如果一个数组公式只运算一次就可以获取全部结果了，那只需要将计算结果写入相关单元格区域就OK，为什么还需要每个单元格都去做重复的数组运算呢？

——因为区域数组公式不好用呗。它需要提前选中结果区域，这个区域还不会随计算结果自动扩展，即僵硬又麻烦。

而动态数组的出现则打破了这一切，它只需要计算一次，就可以返回全部计算结果，它还会根据计算结果，动态扩展相应存放结果的单元格区域，所以它效率很高，灵活性也不差。

在365中，能用动态数组解决的问题，就尽量不使用大批量普通函数公式——这两者的计算效率实在是天差地别。非常不认真的说，动态数组用的好，函数的计算效率甚至不弱于VBA编程，简洁性当然是完胜。

我举个例子。

如下图所示，A:D是数据源，需要根据F2单元格指定的班级和G2单元格指定的性别，筛选符合条件的名单，并统计总人数和成绩之和。

蓝色区域是模拟结果。

F5单元格输入以下公式，即可获取符合条件的明细记录。

动态数组▼

=FILTER(A2:D8,(A2:A8=F2)*(C2:C8=G2),"")

FILTER是365中的一个新函数，语法格式如下▼

=FILTER(数据源,筛选条件,容错值)

该函数第2参数是筛选条件，返回的结果须为逻辑值，如果为True则保留相应数据源记录，为False则删除相应记录。

本例中(A2:A8=F2)*(C2:C8=G2)判断A2:A8的班级是否等于F2单元格指定的班级，同时判断C2:C8的性别是否等于G2单元格指定的性别。返回一个内存数组{1;1;1;0;0;0;0}，其中0为False，非0数值为True。

如果该函数查无符合条件的结果，会返回错误值#CALC!，通过第3参数指定一个值，可以避免返回该错误值，本例第3参数指定值为假空。

该函数支持数组运算，可以返回符合条件的一组结果。本例中一班男性一共有三条记录，那么只需要在F5一个单元格输入公式，即可获取全部结果。

系统会自动根据计算结果动态扩展结果区域▼

是不是很酷？

……

很明显，动态数组的计算结果是一个动态区域，那么如何智能引用这个动态区域呢？难道需要使用OFFSET函数去搭建？

当然不用这么麻烦。

可以使用以下语法格式。

动态区域首个单元格#

比如，我们需要在I2单元格计算符合条件的人数，可以使用公式▼

=COUNT(F5#)&"人"

同样的道理，J2单元格计算总成绩，可以使用公式▼

=SUM(F5#)

两个函数的运算效果参见上面的动图。

最后补两个小贴士：

1)如果需要取消动态数组的溢出功能，可以在等号后输入符号@。比如输入以下公式，就只会返回数组的首个元素。

=@FILTER(A2:D8,(A2:A8=F2)*(C2:C8=G2),"")

2)前面讲过，动态数组功能会将结果自动填充到相关单元格区域，但前提是这些区域不存在数据，如果这些区域存在数据，动态数组会返回一个错误值#SPILL!，提示无法填充数据。

(未完待续)

图文制作：看见星光

原载：Excel星球