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
现在动态数组还在内测阶段,还没有正式上线,小伙伴可以先了解下新函数功能语法,近期动态数组功能将会正式上线,更多精彩,等你创建!
最后再说四个字
遥遥领先!
创作者俱乐部成员
KVP函数专家、创作者俱乐部成员
KVP函数专家、创作者俱乐部成员
创作者俱乐部成员
创作者俱乐部成员
KVP函数专家、创作者俱乐部成员