我有一个数组,我想提取每个标题下与另一张表中的列表匹配的所有值。示例:
第 1 页:
艾伦 | 辛迪 | 乔 | 布拉德 | 珍妮 |
---|---|---|---|---|
1 | 2 | 3 | 4 | 5 |
A | b | C | d | 埃 |
第 2 页:
列表 |
---|
艾伦 |
乔 |
布拉德 |
我希望它返回与列表匹配的名称下的值。因此在这种情况下它将返回:
输出 |
---|
1 |
A |
3 |
C |
4 |
d |
答案1
答案2
我更喜欢使用FILTER
,但它不能直接跨列工作。TRANSPOSE
但是使用可以解决这个问题:
=TRANSPOSE(FILTER(TRANSPOSE(A2:E3),TRANSPOSE(IFERROR(FIND(A1:E1,ARRAYTOTEXT(H1:H3)),0))))
这将创建一个数组,其中包含要在数据集中查找的元素(我将它们放在我的工作模型的 H1:H3 中),ARRAYTOTEXT
因为示例并不复杂。如果复杂的话,TEXTJOIN
使用类似的|
作为其分隔符将是一个好主意。然后FIND
应用列标题作为要在数组中查找的值。这会产生数字值和错误,后者通过变成 0。IFERROR
这样做是为了生成一个 TRUE/FALSE 数组,用于FILTER
选择要输出哪些列的数据。它将采用 的形式{0,0,4,0,0,0,28,0,0,83}
,即:零和非零,但所有数字。这是因为当 Excel 将其视为布尔数组时,它会将任何非零 NUMBER 值视为 TRUE。任何非数字的东西都会成为问题,因此需要努力让所有值都是某种数字。
必须将TRANPOSE
所需的行作为输出(不包括标题行,因为您不希望它出现在输出中),这样它FILTER
认为它在行上工作,因为它拒绝直接在列基础上工作。
无论如何,现在使用示例数据可以得到一个“3 列 2 行”的二维数组作为输出。由于需要单列,因此TOCOL
选择列式输出来实现所需的输出:
=TOCOL(TRANSPOSE(FILTER(TRANSPOSE(A2:E3),TRANSPOSE(IFERROR(FIND(A1:E1,ARRAYTOTEXT(H1:H3)),0)))),,TRUE)
这就是所需要的全部内容。
如果愿意,可以随时使用它INDEX
来实现转置。我没有尝试过,但它可以转置所有四种方式,而不仅仅是两种方式TRANSPOSE
,而且这是可能的,只需要做一次。另外,如果想要“1,3,4,a,c,d”输出而不是“1,a,3,c,4,d”输出,这是一个小小的调整。
这里真正的关键是用来TOCOL
生成单列输出。