有没有办法在没有 VBA 的情况下在 Excel 中连接两个数组? [英] Is there a way to concatenate two arrays in Excel without VBA?
问题描述
我正在尝试创建一个公式,该公式返回两个不同长度数组的串联.我需要将这种连接用于另一个公式的一部分,如果可能,我想避免使用辅助"行.
I am trying to create a formula that returns the concatenation of two arrays of different lengths. I need this concatenation for part of another formula and I would like to avoid "helper" rows, if possible.
请参阅下面的示例数据.
See below for example data.
目标是使输出为 {10;11;12;13;20;21;22}
.当然,这可以很容易地硬编码到公式中,但这些值是动态的,因此不是一个选项.
The goal is to have the output be {10;11;12;13;20;21;22}
. Of course, this can easily be hardcoded into the formula but these values are dynamic so that is not an option.
我尝试了以下方法:
{A1:A4;B1:B3}
但这显然不是有效的 Excel 语法.
but this is apparently not valid Excel syntax.
有解决办法吗?
推荐答案
就其价值而言,这里是一个连接两个任意两个垂直数组的解决方案(不限制数据必须是数字).
For what it's worth, here is a solution that concatenates two any two vertical arrays (without the limitation that the data must be numbers).
这是数组公式:(例如组合A1:A4
和C7:C9
)
Here is the array formula: (e.g. combining A1:A4
and C7:C9
)
= INDEX(CHOOSE({1,2},A1:A4,C7:C9),
N(IF({1},ROW(INDEX($A:$A,1):INDEX($A:$A,ROWS(A1:A4)+ROWS(C7:C9)))-IF(
ROW(INDEX($A:$A,1):INDEX($A:$A,ROWS(A1:A4)+ROWS(C7:C9)))<=ROWS(A1:A4),0,ROWS(A1:A4)))),
N(IF({1},2-(ROW(INDEX($A:$A,1):INDEX($A:$A,ROWS(A1:A4)+ROWS(C7:C9)))<=ROWS(A1:A4)))))
这里是组合两个水平数组的数组公式(例如A1:D1
和C3:E3
)
And here is the array formula to combine two horizontal arrays (e.g. A1:D1
and C3:E3
)
= INDEX(CHOOSE({1;2},A1:D1,C3:E3),
N(IF({1},2-(COLUMN(INDEX($1:$1,1):INDEX($1:$1,COLUMNS(A1:D1)+COLUMNS(C3:E3)))
<=COLUMNS(A1:D1)))),N(IF({1},COLUMN(INDEX($1:$1,1):INDEX($1:$1,COLUMNS(A1:D1)+
COLUMNS(C3:E3)))-IF(COLUMN(INDEX($1:$1,1):INDEX($1:$1,COLUMNS(A1:D1)+COLUMNS(C3:E3)))
<=COLUMNS(A1:D1),0,COLUMNS(A1:D1)))))
这篇关于有没有办法在没有 VBA 的情况下在 Excel 中连接两个数组?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!