后台有朋友留言,问怎么用公式将几个动态数组拼接为一个数组。
在很多编程语言中,数组合并是一个很频繁的操作。Excel支持了动态数组后,肯定会有越来越多的数组操作。因此,很有必要讨论一下数组合并的方法。
今天就介绍一个公式。
注:这个公式我已经做成了自定义函数,上传到了GITHUB。地址在文末。
注:既然说到动态数组,就是新版本的Excel,至少是Excel 2019以后的版本。今天介绍的这个公式会用到新函数。
首先,我们需要一个基本假设:
要合并的两个数组,必须具有相同的列数。
这个假设是合理的,在很多数据库的合并操作中,也是基于这样的假设。至于两个数组的行数,可以忽略不记。
另外,在新版本的Excel中,由于动态数组的出现,我们一般对区域和数组不做区分了。以下的说明就以区域为例进行说明。
假设我们有两个区域如下:
左边的两个区域分别命名为:FA和FB。希望将它们合并为右边的结果区域所示的样子。
直接看公式:
=IF(SEQUENCE(ROWS(FA)+ROWS(FB))<=ROWS(FA),INDEX(FA,SEQUENCE(ROWS(FA)+ROWS(FB)),SEQUENCE(1,COLUMNS(FA))),INDEX(FB,SEQUENCE(ROWS(FA)+ROWS(FB))-ROWS(FA),SEQUENCE(1,COLUMNS(FA))))
为了方便解释,我们对这个公式排下版:
=IF( SEQUENCE(ROWS(FA)+ROWS(FB))<=ROWS(FA), INDEX(FA,SEQUENCE(ROWS(FA)+ROWS(FB)),SEQUENCE(1,COLUMNS(FA))), INDEX(FB,SEQUENCE(ROWS(FA)+ROWS(FB))-ROWS(FA),SEQUENCE(1,COLUMNS(FA))))
注:插入空行是为了提高可读性。使用时请复制上面的那个没有空行的公式。
解释一下:
最外面是个IF函数,条件是
SEQUENCE(ROWS(FA)+ROWS(FB))<=ROWS(FA)
SEQUENCE是个数组函数,生成一个数组,这个数组具有5个元素(共5行),所以是:{1;2;3;4;5},
总体上这个条件是:
{1;2;3;4;5}<=3
根据上面的数组,前3个满足条件,后2个不满足,得到条件:
{TRUE;TRUE;TRUE;FALSE;FALSE}
对于满足条件的前3行来说,执行:
INDEX(FA,SEQUENCE(ROWS(FA)+ROWS(FB)),SEQUENCE(1,COLUMNS(FA)))
中间的SEQUENCE(ROWS(FA)+ROWS(FB))生成一个数组,
{1;2;3;4;5} (注:只有1;2;3起作用)
而SEQUENCE(1,COLUMNS(FA))生成另一个数组:
{1,2}
整个INDEX公式就是,
INDEX(FA,{1;2;3},{1,2})
这个公式会取出第一个数组的前3行和前两列,也就是整个数组。
类似地,对于不满足条件的后两行来说,
INDEX(FB,SEQUENCE(ROWS(FA)+ROWS(FB))-ROWS(FA),SEQUENCE(1,COLUMNS(FA))))
实际上就是
INDEX(FB, {1;2},{1,2})
取出的是第二个数组的后两行。
完成。
这个公式是用区域做例子说明的,但是也可以用数组。比如,将FA用FILTER函数代替:
=IF(SEQUENCE(ROWS(FILTER(FA,B2:B4>0))+ROWS(FB))<=ROWS(FILTER(FA,B2:B4>0)),INDEX(FA,SEQUENCE(ROWS(FILTER(FA,B2:B4>0))+ROWS(FB)),SEQUENCE(1,COLUMNS(FILTER(FA,B2:B4>0)))),INDEX(FB,SEQUENCE(ROWS(FILTER(FA,B2:B4>0))+ROWS(FB))-ROWS(FILTER(FA,B2:B4>0)),SEQUENCE(1,COLUMNS(FILTER(FA,B2:B4>0)))))
我已经将上面的公式用LAMBDA函数创建了自定义函数,请访问Github:
https://github.com/abel881969/ExcelCustomFunctions
在common目录下,函数名叫做:
UNIONARRAY。
联系客服