我如何返回与一系列标题匹配的一系列单元格?

我如何返回与一系列标题匹配的一系列单元格?

我有一个数组,我想提取每个标题下与另一张表中的列表匹配的所有值。示例:

第 1 页:

艾伦 辛迪 布拉德 珍妮
1 2 3 4 5
A b C d

第 2 页:

列表
艾伦
布拉德

我希望它返回与列表匹配的名称下的值。因此在这种情况下它将返回:

输出
1
A
3
C
4
d

答案1

我建议您找出所有与标题匹配的数据。

该公式可以=INDEX($A$1:$E$3,COLUMN(B1),MATCH($H2,$A$1:$E$1,0))基于我的简单样本。

在此处输入图片描述

然后我们可以将两列合并为一列。

公式可以是=INDEX($I$2:$J$4,ROUNDUP(ROW(I1)/2,),MOD(ROW(I1)-1,2)+1)

在此处输入图片描述

请注意,要获取行号,公式ROUNDUP(ROW(I1)/2,)是固定的,并且不会随着您的数据范围而改变。

列号公式也是如此MOD(行(I1)-1,2)+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生成单列输出。

相关内容