动态数组功能到底有多牛?颠覆函数的编写方式和运算效率

HI,大家好,我是星光。

最近很多朋友在讨论WPS将于近期正式发布的动态数组功能,讨论的很热闹,不过不少朋友还是对这个功能一知半解。今天我把之前一篇旧的推文稍微整理了一下,主要说明以下几个大家应该了解的事儿。

1,什么是动态数组

2,动态数组的优点是什么

3,使用时有哪些注意事项

4,从此改变编写函数的习惯


1,什么是动态数组

所谓动态数组,通俗的理解,就是将数组公式返回的多个结果,动态溢出到对应大小的单元格区域。它包含了两个要素,其一是数组公式,其二是结果动态溢出,所以叫动态数组。

至于什么是数组公式,咱们这里就不展开聊了,展开的话篇幅太长字数太多我怕你困觉……。又或者您可以阅读我的另外一篇推文,它专门讲解了什么是数组、数组运算;什么是数组公式、区域数组公式等等▼

■万字解析什么是函数数组


2,动态数组有哪些优点

最大的优点有两个:一个是简化函数公式的输入方式,另外一个是极大提高函数公式的计算效率。

◆ 简化了数组公式的输入方式

在过去WPS版本中输入数组公式,你需要同时按下<Ctrl+Shift+Enter>组合键结束公式输入,才能启用数组运算,使公式返回正确的计算结果。这三键因此又被称为数组三键

例如,上图所示的表格,按数组三键输入以下数组公式可以正确统计二班的语文总分:371分。

=SUM((A2:B10="二班")*(C2:C10))

但如果你不按数组三键,同一个公式会返回错误的结果:61分。

在EH论坛中,有很多表弟和表妹对这个规则不了解,就会抱怨说答贴者提供了错误的函数解法。著名函数大佬海鲜同志,在饱经摧残之后,将论坛签名修改成了下面辣眼睛的大红大绿的动图——由此你就知道这个三键规则有多么不方便。

而动态数组取消了三键规则,不需要按三键,绝大部分数组公式都会被默认执行数组运算(区域数组公式除外)。

……

除此之外,更重要的是,如果一个数组公式返回的结果是多个,在普通版本中,你可能需要嵌套其它函数逐个取值,而使用动态数组的话,它会自动将多个结果,动态溢出到公式相邻的单元格区域中。

举一个典型的例子。

如上图所示,A~D列是数据源,需要在F~I列查询班级名称为"二班"的数据明细。

在过去版本的WPS中,你需要在F2单元格输入以下公式,再复制填充到预设的合适大小的单元格区域中,例如F2:I7。

👋

=IFERROR(

INDEX(FILTER($A$2:$D$10,$A$2:$A$10="二班"),

ROW(A1),

COLUMN(A1)

),"")

公式先使用FILTER函数筛选A列班级为二班的数据明细,返回的是3行4列的内存数组,然后使用INDEX函数按行列索引将数据一个一个提取出来,最后使用IFERROR函数将错误值屏蔽为假空。

公式的嵌套层次很多,更糟糕的是,当数据源数据的大小发生了改变,你可能还需要重新调整公式的输入范围,F2:I7的区域未必够用。

如果使用动态数组的话,在F2单元格输入以下FILTER函数就可以了,系统会自动将结果动态溢出到相邻区域。

=FILTER(A2:D10,A2:A10="二班","查无")

综上,如果说之前嵌套多层的普通公式就像上个世纪老太太的裹脚布,那把现在这个动态数组比喻成清凉的洞洞鞋,不过份吧?

◆ 极大提高了函数的计算效率

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

事实上,数组运算的效率确实是高于大批量普通函数公式,之所以在实际应用中效率低下,是由于大家总是在每个单元格都输入数组公式,以至于每个单元格都在做重复的数组运算

以查询"二班"数据明细的案例来说,以下公式它在F2:I7的区域中,重复做了N次运算,每次都使用FILTER函数遍历数据源提取相关数据,然后再使用INDEX函数逐个取值。

👋

=IFERROR(

INDEX(FILTER($A$2:$D$10,$A$2:$A$10="二班"),

ROW(A1),

COLUMN(A1)

),"")

如果一个数组公式只运算一次就可以获取全部结果,那就应该只计算一次,然后将结果写入相关单元格区域就可以了,为什么还要每个单元格都去做重复的数组运算呢?这样的表格功能岂不是很呆?

有朋友可能会讲,传统的区域数组公式可以避免重复运算。

是的,区域数组可以将公式返回的多个结果一次性写入到单元格区域中,但它需要提前选中结果区域,而且这个区域是静态的,并不会随计算结果的大小自动扩展——所以区域数组公式的用法一直很冷门,属于空心大萝卜,中看不中用。

区域数组公式不会自动扩展▲

使用动态数组,则可以避免区域数组公式的局限。它不但可以只计算一次就返回全部计算结果,而且可以根据结果数组的大小,动态扩展范围,将结果溢出到公式相邻单元格区域中,所以它不但计算效率很高,操作也很灵活。

打个响指,认真脸,当你拥有动态数组,能用动态数组解决的问题,就尽量不使用大批量普通函数公式——这两者的计算效率有云泥之别。非常不认真的说,动态数组用的好,函数的计算效率不弱于VBA/JSA编程,简洁性当然是完胜。


3,使用动态数组注意事项

◆ 结果区域需要保持空白

歪兔苏芮,看我手指的方向👆,上面讲过,动态数组功能会将多个结果自动溢出到相邻的单元格区域,但前提是这些区域不存在数据,而且不属于【超级表】。如果存在非空值,动态数组会返回一个错误值#SPILL!,提示溢出区域不是空白区域——换句话说,它会动态扩张地盘,但不会强拆别人的房子。

◆ 智能引用动态区域的方式

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

当然不用这么麻烦。

可以使用以下语法格式:

动态区域首个单元格#

比如,上图所示的表格,需要在G1单元格统计F3单元格动态数组查询结果的人数,可以使用公式:

=ROWS(F3#)

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

=SUM(F3#)

◆ 取消动态数组的溢出功能

如果需要取消动态数组的溢出功能,只需要获取多个结果的首个值,可以在等号后输入运算符@

举个例子,如上图所示,输入以下公式可以返回全校总分倒数第1名的人名——大名鼎鼎小名随风小妞的罗子阳同志。

擒贼只擒王▼
=@SORTBY(B2:B10,C2:C10+D2:D10)

公式使用SORTBY函数对B列的人名按总分升序排序,返回的是N行1列的内存数组,再使用@运算符取首个值,也就是那个全校总分倒数第一的家伙了。


4,改变你编写函数的习惯吧

看我真诚的小眼神,动态数组会彻底改变人们编写函数的方式,骗你娶你不论男女。

关于这事,除了上面举过的典型的FILTER函数的案例,我再举一个同样常见的数据查询的例子,你可能就会多一点相信了

如上图所示,需要根据A~E列的明细,查询G列人名对应的各科成绩。

以前,你需要考虑单元格各种引用的方式,先在H2单元格输入以下公式,再向右向下复制填充:

=XLOOKUP($G2,$B:$B,C:C,"")

查找值需要混合引用,查找范围列需要绝对引用,查找结果列需要相对引用

现在,不需要过多的考虑单元格的引用方式,在H2单元格输入以下动态数组,向下复制填充即可:

=XLOOKUP(G2,B:B,C:E,"")

但注意不能使用以下动态数组,XLOOKUP函数患有和VLOOKUP相同的毛病,不支持查找值和返回区域同时多项的情况。

=XLOOKUP(G2:G5,B:B,C:E,"")

……

哒~打个响指,正如一些朋友所了解的,动态数组并非WPS独创,微软Excel在3年前就发布了该功能——但是,在国内,由于低版本Excel充斥行业、又缺乏直接向后升级兼容的特性,以及高版本Office高昂的价格,就导致微软虽然是开创者,却不是很好的普及者,3年过去了,动态数组应用者寥寥依然。

而微软的缺点恰恰是WPS的长处(软件免费,可直接快速升级向后兼容),这也是为什么Excel圈里热烈的期待WPS即将到来的更新。我们目前确实有理由相信凭借WPS的力量可以将动态数组真正普及化,进而真正改变表格函数的编写方式和计算效率等等…

摊手,耸肩,今就聊到这吧,不知不觉字数渐涨篇幅又很长了,能看到这儿的都是爱学习的家人们呐。有啥问题咱们后续补充吧,挥挥手,咱们以后再见了,拜拜~

福建省
浏览 4095
5
28
分享
28 +1
15
5 +1
全部评论 15
 
小可耐
关键是加了@的确能取消动态数组溢出,但是如果我想F9看下呢 怎么变成老版本的那种F9后编辑栏出来内存数组呢
· 湖北省
回复
 
高空
永远的星光,不停止学习!
· 河北省
回复
 
Toby
跟着星光学习过不少技能
· 陕西省
回复
 
快乐的塔塔
快乐的塔塔

创作者俱乐部成员

哇塞星光大佬来wps了一直在跟着星光老师学习
· 广东省
回复
 
老坛子
专业这个词,我可以说无数次
· 广东省
1
回复
 
阿滨
阿滨

社区优秀创作者

膜拜大神
· 江苏省
回复
 
刘大侠X
为星光老师打call
· 吉林省
回复
 
牛魔王
学习了
· 江苏省
回复
 
峰言隽语
边姐,又来学习了
· 湖北省
回复
 
疯狂摇摇摆
涨知识了
· 广东省
回复
 
庄
点个赞再看
· 广东省
回复
 
黄兴文
好文章!
· 广东省
回复
 
葉
文章写的真好
· 广东省
回复
 
kia
哈哈,隔着山河的三个键从此退出江湖
· 广东省
回复
 
张俊
张俊

WPS函数专家

星光老师一出手,就知道有没有!
· 上海
1
回复