Office 365函数新世界 (1)

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


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


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


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


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


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


再说一下365函数新功能。

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

Office 365函数新世界 (1)插图

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

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

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

举个简单的例子

Office 365函数新世界 (1)插图(1)

如上图所示的表格,选中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区域中。

Office 365函数新世界 (1)插图(2)

这有什么好处呢?

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

Office 365函数新世界 (1)插图(3)

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

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

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


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

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

我举个例子。

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

蓝色区域是模拟结果。

Office 365函数新世界 (1)插图(4)

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一个单元格输入公式,即可获取全部结果。

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

Office 365函数新世界 (1)插图(5)

是不是很酷?

Office 365函数新世界 (1)插图(6)

……

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

当然不用这么麻烦。

可以使用以下语法格式。

动态区域首个单元格#

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

=COUNT(F5#)&”人”

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

=SUM(F5#)

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


最后补两个小贴士:

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

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

Office 365函数新世界 (1)插图(7)

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

Office 365函数新世界 (1)插图(8)

未完待续)


图文制作:看见星光

原载公众号:Excel星球

图片部分来自网络,如有侵权请联系QQ897219494,或者发布邮箱897219494@qq.com
技能大全,高效工具 » Office 365函数新世界 (1)

提供最优质的资源集合

立即查看 了解详情