自从 Excel 2021 版本开始,Excel 就陆续地推出了一些动态数组相关的函数:
动态数组相关的函数由于传统的 Excel 函数返回的往往是一个值,我们用一个单元格就能把结果装下了,但动态数组函数是可以返回一组数据,需要一个单元格区域才能装得下所有结果,这可以说是 Excel 公式近年的最有突破性的变化。
但由于在公司里,大部分人用的都是 Excel 2019 以前的版本,所以目前很多人还未接触过这类函数。虽然短期可能用不了,但跟随本文去了解一下也是不错的,觉得不错的话,或者可以提议老板帮忙升级一下版本~
这次重点介绍以下 6 个函数,因为它们推出的时间已经有 3 年了吧,Excel 2021 中也都支持。
FILTER: 按条件去筛选区域 UNIQUE: 提取唯一值 SORT: 按指定列对列表进行排序 SORTBY: 按另一个列表对列表进行排序 SEQUENCE: 在行列范围内生成数字序列 RANDARRAY: 生成一系列随机数
什么是数组?而且还是动态的数组?听到就觉得复杂?看看示例就容易理解了:
FILTER
以前我们做数据筛选时,都是通过鼠标点击筛选手工操作的:
但是现在通过 FILTER 函数就能做到了,它的具体语法是:
= FILTER(要筛选的区域, 筛选条件, [没匹配结果时返回什么])
我来做一个根据城市名称,去筛选数据的效果:
FILTER函数的示例我只是在H4
这一个单元格中输入了以下公式:
= FILTER(A2:F15, C2:C15=I1, "没数据")
结果就返回了一个区域的数据,这个区域的数据就是一个数组,所以数组就理解为一组数即可。
通过 FILTER 的示范,大家应该能感受到这是 Excel 公式突破性的变化,以前你需要先选中一个区域再按 Ctrl + Shift + Enter 三键去进行数组计算,而且还不能动态自适应变化,现在使用了这些动态数组函数后都能高效实现了!
UNIQUE
以前我们做唯一值提取时,主要是通过鼠标点击删除重复值实现的:
但是现在通过 UNIQUE 函数就能做到了,对于提取一列中的唯一值,只要这样写即可:
同样是只在一个单元格(P2)中去写公式,提取后的唯一值列表会自动向下扩展返回结果,非常方便!
这种动态返回的区域结果能不能被再次引用呢?
引用动态数组区域可以看到,我在写 COUNTA 公式时,选中整个当时的整个动态区域后,引用的参数写着是P2#
,其实指的就是以P2
为左上角的一片动态区域。
因为当数据源变化时,UNIQUE 函数返回的结果数量就是动态的,如果再次引用就成了一个问题,于是微软就定义了这样的新语法去表示这种引用。
SORT
以前我们做排序时,都是通过鼠标点击排序来实现的:
但是现在通过 SORT 函数就能做到了,对于提取一列中的唯一值,只要这样写即可:
SORT函数排序同样在一个单元格中写公式,然后排序后的动态数组区域结果,会向右下方扩展显示在单元格中,真是超级方便!
SORTBY
刚才的 SORT 是按第几列进行排序,而 SORTBY 可以理解为自定义排序的依据,和 Excel 设置中的自定义排序功能类似。
例如我们想直接获取排序后的类别,可以这样写:
SORTBY的示范SORT 的排序依据是第几列,而 SORTBY 的排序依据是一个数组,按这个数组的升序或者降序去排序数据。
SEQUENCE
SEQUENCE 是用来生成一个等差数列的,语法是:
= SEQUENCE(几行, [几列], [起始值], [公差/步长])
例如1 行,2 列,首项是 2,公差为 3,你就这样写:
= SEQUENCE(1, 2, 2, 3)
例如3 行,4 列,首项是 0,公差为 5,你就这样写:
= SEQUENCE(3, 4, 0, 5)
由于第 2,3,4 个参数都是可选的,所以如果你写=SEQUENCE(10)
就可以直接生成一列1~10的序列数字
没有这个函数之前,我们都是通过手工鼠标点击去生成序列的:
填充序列功能RANDARRAY
SEQUENCE 是生成序列数,而RANDARRAY
则是生成随机数组。传统的随机函数是RAND
和 RANDBETWEEN
,但它们都是在一个单元格里生成值,而RANDARRAY
则是生成一个数组区域。看下它的语法:
= RANDARRAY([几行], [几列], [最小值], [最大值], [生成整数?])
就是表达你要生成几行几列的随机数,而且是在最小值与最大值之间的,可以选择是否生成整数还是小数。
看几个示范你肯定明白了:
= RANDARRAY(4, 3)生成 4 行 3 列的 1 以内的随机小数
= RANDARRAY(4, 3, 1, 10)生成 4 行 3 列的 1 至 10 区间的任意随机数
= RANDARRAY(4, 3, 1, 10, TRUE)生成 4 行 3 列的 1 至 10 区间的任意随机整数
最后
上面介绍到的 6 个函数都有共同的特点,就是返回的结果会是一个自动扩展的动态区域,所以它们也叫动态数组函数。它们的出现,使得函数功能变得非常强大,能方便地实现以前只能通过手工操作才能做的事情(筛选、排序、删除重复值、生成序列等)。
而且目前在 365 版本中,还有了更多的基于数组区域操作的函数,例如 RRAYTOTEXT
, BYCOL
, BYROW
, CHOOSECOLS
, CHOOSEROWS
, DROP
, EXPAND
, FILTER
, HSTACK
, VSTACK
, TEXTSPLIT
等,它们之间的灵活嵌套更能把 Excel 公式的能力再提升 n 倍!以前需要编程辅助才能实现自动化的效果,以后用函数也能实现,大大提升效率。
当然,也期待微软尽快推出 Office 2024 版本,以便更多的用户能尽快用上强大的动态数组函数。