WPS动态数组,打开函数公式的新世界

会飞的鱼
会飞的鱼

KVP函数专家、创作者俱乐部成员

小伙伴们大家好,等了半年的WPS动态数组功能终于等到了,已上手体验了几天,来和大家分享。

1、什么是数组公式?

正常函数公式是返回一个值比如=sum(a1:a5),函数返回的是a1到a5单元格合计,同时,函数公式也可以返回多个值,比如=a1:a5*2 ,公式计算后会5个值,这个时候需要使用数组公式(ctrl+shift+回车)或者使用index、row下拉公式才可以把5个依次返回到单元格中。

2、动态数组是什么意思?

动态数组功能是软件可以根据一个公式返回的多个结果,自动向下向右溢出,把多个值对应填充到多个单元格中,如在F1输入a1:c5 只需要输入一个公式,软件即可自动把a1:c5单元格区域5行3列15个值 自动填充到F1单元格开始向下5行向右3列区域。

3、动态数组有什么优点?

同需求下动态数组溢出功能可以更简单、更快速、更优雅的实现,输入一个公式即可,无需三键确认,无需下拉或双击填充公式,特别在一些新函数的加持下,可以简化之前很多复杂的公式,以及可以实现很多之前只能通过VBA编程才可以实现的功能。并且在溢出功能的特性下可以大大提升计算效率。

下面我们通过几个案例来了解下。

3.1 、去重复项

这是早期的数组公式

=INDEX(A:A,SMALL(IF(MATCH($A$2:$A$25,$A$2:$A$25,0)=ROW($A$2:$A$25)-1,ROW($A$2:$A$25),4^8),ROW(A1)))&""

这是使用了新函数后的公式

=IFERROR(INDEX(UNIQUE($A$2:$A$25),ROW(A1))&"","")

有了动态数组后的公式

=UNIQUE(A2:A25)

多条件去重复

=UNIQUE(A2:B25)

3.2、统计汇总(透视表效果)

在G2单元格输入公式

=UNIQUE(C2:C140)

在H2单元格输入公式

=SUMIFS(D:D,C:C,UNIQUE(C2:C140))

还可以更简便的公式

=SUMIFS(D:D,C:C,G3#)

关于#号的使用方法下方有详细说明

还可以通过构建数组使用一条公式搞定

=IF({1,0},UNIQUE(C2:C140),SUMIFS(D:D,C:C,UNIQUE(C2:C140)))

根据统计后的重量对结果降序排序

=SORT(IF({1,0},UNIQUE(C2:C140),SUMIFS(D:D,C:C,UNIQUE(C2:C140))),2,-1)

3.3一对多查找

这是早期的数组公式

=IFERROR(INDEX(A:A,SMALL(IF($A$1:$A$10=$K$4,ROW($A$1:$A$10),9E+307),ROW(A1))),"")

有了动态数组后

=FILTER(A2:C10,A2:A10=K4)

增加难度,金额排序对查询结果排序

SORT(FILTER(A2:C10,A2:A10=K4),3,-1)

再次增加难度,对查询结果多条件排序

=SORTBY(FILTER(A2:C10,A2:A10=K4),FILTER(B2:B10,A2:A10=K4),-1,FILTER(C2:C10,A2:A10=K4),-1)

3.4、多条件查找

根据多个产品名称查询返回多行结果

FILTER(A1:E9759,IFERROR(MATCH(C1:C9759,H2:H5,0),0)>0)

返回结果根据查询条件排序

SORTBY(FILTER(A1:E9759,IFERROR(MATCH(C1:C9759,H2:H5,0),0)>0),MATCH(FILTER(C1:C9759,IFERROR(MATCH(C1:C9759,H2:H5,0),0)>0),H2:H5,0),1)

4、@#符号如何使用?

4.1、@符号

如果公式的返回结果是多个值,在公式前加@符号可以取消溢出,返回数组结果的第一个值

使用index(公式,1,1) 也是一样的效果

此外使用 index row column 可以返回多行指定行数列数区域,比如返回重量最大前三的行数

INDEX(SORT(G3:H10,2,-1),ROW(1:3),COLUMN(A:B))

也可以使用SEQUENCE 新函数 来生成指定行列数量来实现,公式更好理解=INDEX(SORT(G3:H10,2,-1),SEQUENCE(3,1),SEQUENCE(1,2))

4.2、#符号

当我们想引用动态数组返回的数据时,可以引用 动态数组左上角单元格后加#符号可以直接引用这个单元格公式的返回的数组结果。

使用函数对动态数组结果再次计算

需要注意的是,如果在引用没有公式的单元格后加# 公式会返回 #REF! 错误

5、使用动态数组注意事项

5.1、动态数组溢出只能向空白单元格溢出,如果当溢出区域已经有内容时,会报#SPILL! 错误。

5.2、溢出区域不能有合并单元格,就是在输入公式的单元格以及动态数据溢出区域的单元格,都是不能有合并单元格,不然同样会报错#SPILL! 错误。

5.3、当使用FILTER函数,如果没有满足条件的数据,返回空数组时会显示错误#CALC!错误或者当遇到某些不支持的计算也会返回此错误。

6、要了解的新函数

动态数组虽然功能强大,新函数的加持才让动态数组有了灵魂,以下新函数一定要收藏好

6.1、UNIQUE 去重复,可以实现删除重复项功能。

6.2、FILTER 条件筛选,可以实现高级筛选功能。

6.3、SORT 排序,可以实现排序功能。

6.4、SORTBY 多条件排序,可以实现自定义多条件排序功能。

6.5、TEXTSPLIT 文本拆分,可以实现分列功能。

6.6、SEQUENCE 生成序列函数,嵌套INDEX使用可以实现单行或单列转多列。

7、VBA动态数组相关属性

7.1、向单元格写入动态数组公式

Range("F2").Formula2 = "=UNIQUE(B2:b6)"

7.2、获取动态数组溢出结果单元格地址

ad = Range("F2").SpillingToRange.Address

7.3、获取动态数组结果值到数组

arr = Range("F2").SpillingToRange.Value

7.4、获取动态数组结果左上角单元格地址

ad2 = Range("F4").SpillParent.Address

7.5、判断单元格是否有数组公式溢出

nn = Range("F4").HasSpill

现在动态数组还在内测阶段,还没有正式上线,小伙伴可以先了解下新函数功能语法,近期动态数组功能将会正式上线,更多精彩,等你创建!

最后再说四个字

遥遥领先!

辽宁省
浏览 10889
10
25
分享
25 +1
15
10 +1
全部评论 15
 
赵二
非常实用!
· 辽宁省
1
回复
赵二
点赞学习收藏!
· 辽宁省
1
回复
 
Arno
函数的变化真的是日新月异啊,最近在学习新函数,文中的要点解答了平常工作中碰到的几个困惑,特来致谢
· 江苏省
1
回复
 
zha7090
zha7090

创作者俱乐部成员

学习了
· 广东省
回复
 
苏打、
用的是公式,怎么老是给我改成数组?
· 广东省
1
回复
会飞的鱼
会飞的鱼

KVP函数专家、创作者俱乐部成员

看下文件格式,xls格式会出现这种情况,可以另存为xlsx后,选中数组公式区域后 删除重新输入公式。
· 辽宁省
1
回复
 
魏太福
怎么获取图片的excel文件?以便练习。
· 重庆
1
回复
 
凡人梵语
原数组单元格是空白内容的,怎么使用FILTER溢出后对应的单元格内容变为0? 怎么能保持单元格为空白?
· 广东省
1
回复
会飞的鱼
会飞的鱼

KVP函数专家、创作者俱乐部成员

将FILTER函数第1个参数 '[2023年报表.xlsx]部门'!A6:AC9999 修改为 if('[2023年报表.xlsx]部门'!A6:AC9999="","",'[2023年报表.xlsx]部门'!A6:AC9999)
· 辽宁省
1
回复
 
丁功令
丁功令

创作者俱乐部成员

· 四川省
1
回复
 
懒得批爆
懒得批爆

创作者俱乐部成员

TEXTSPLIT 目前在哪里可以实现?在线表格也没这个函数,今天是2023/10/30
· 四川省
1
回复
会飞的鱼
会飞的鱼

KVP函数专家、创作者俱乐部成员

https://home.wps.cn/topic/9746 这里可以下载
· 辽宁省
1
回复
 
wanglongyun
一开始还好,后面有点懵了,公式还是懂得太少
· 上海
回复