☰

如何将 A 列中的值与 B、C 或 D 列中的值进行匹配并返回找到匹配的列的名称？

tag-icon • tag-icon microsoft-excel worksheet-function
如何将 A 列中的值与 B、C 或 D 列中的值进行匹配并返回找到匹配的列的名称？

我有一张看起来像这样的表格（但有数千行）：

A 乙 C 德
a1 a3 a1 a4
a2 a2 a5
a3
a4
a5

最终的想法是添加另一列 E，它告诉我 A 的每个元素属于哪个“类别”（B、C 和 D 是不同的类别）。

在我看来，我希望它在 B:D 中搜索 A 的所有值，然后当找到匹配项时返回找到匹配项的列的名称。

不幸的是，我不习惯使用 Excel（我通常使用 R），这有点不合我的口味。任何帮助都将不胜感激！

期望的输出将是这样的：

A 乙 C 德 埃
a1 a3 a1 a4 “C”
a2 a2 a5 “C”
a3 “B”
a4 “D”
a5 “D”

答案1

尝试这个解决方案。

请参阅下面的屏幕截图。

在此处输入图片描述

在 E1 中公式是。

=SUBSTITUTE(ADDRESS(1,MAX(IF($B$1:$D$5=A1,COLUMN($B$1:$D$5),0)),4),1,"")

CTRL请注意，编写此公式后，您必须通过按公式栏中的+ SHIFT+将其转换为数组公式ENTER。该公式将自动括在花括号中，以表明它是数组公式。

如果没有这一步它将无法正常工作。

将其向下拖动至目标行。

您还可以使用 IFERROR 消除任何 #VALUE 或 #N/A 错误，并在未找到值时将其替换为空白（如果存在）。

根据列表分隔符可能为；而不是，在某些地区，您可能需要在公式中用分号替换逗号，以防万一。

如果您希望在列字母周围使用双引号。

=""""&SUBSTITUTE(ADDRESS(1,MAX(IF($B$1:$D$5=A1,COLUMN($B$1:$D$5),0)),4),1,"")&""""

答案2

如果发生多次的情况，可以尝试以下方法：

=TEXTJOIN(",",,SUBSTITUTE(IFERROR(SORT(ADDRESS(1,($A2=B2:D6)*COLUMN(B2:D6),4)),""),"1",""))

它还对列进行排序。

如果您有除文字字母标签之外的其他与 Excel 列标签匹配的内容，则以下操作将收集所有这些内容。SORT可用于按列顺序排序，或按字母顺序排列实际标题结果：

=TEXTJOIN(",",,IFERROR(SWITCH(SUBSTITUTE(IFERROR(SORT(ADDRESS(1,($A2=B2:D6)*COLUMN(B2:D6),4)),""),"1",""),"B",B1,"C",C1,"D",D1),""))

答案3

编辑：我采用垂直方法解决列标题问题。如果列数合理，这种方法可行，而且逻辑非常清晰。使用这个名为 Table2 的 在此处输入图片描述

为了清晰起见，我在这里用空格打开了表格，并指出了几点。单元格 E2（同样不是表格的一部分）中的公式是： 在此处输入图片描述

SEQUENCE 为我们提供了一个表列形状的数组。

我不知道为什么这样做有效，但 SUBSTITUTE 会用表头替换 SEQUENCE 创建的数组中的 1（一个数字），如图所示。我偶然发现了这一点。您可以将其留空，使用“”或“1”甚至“2”（不出现在任何数组中）或“Donald”，这样就可以了。CHOOSE 的作用相当于 HSTACK，瞧！

开始原始帖子：我使用动态数组和 =LET() 来解决这个问题，只是为了好玩。首先，将数据转换为表格以简化语法。由于 columnarray 解析单元格地址的方式，只要您的数据不超过 Z 列，此解决方案就有效。
请注意，表格语法还会显示列标题，因此如果您可以堆叠该数组，则可以将实际的列标题放入最终输出中，而不仅仅是列字母。唉，Excel 中似乎还没有解决方案可以将 1d 标题数组广播到所需的行数。我手动尝试使用 VSTACK 5 次，它确实有效，但无法以简写形式构建。递归 lambda？

E2 中的公式（并确保 E 列不在表格中）： 我的公式

请随意改进，尤其是 TRANSPOSE(TEXTSPLIT(TEXTJOIN... 允许 LET 返回 IF(boolarray, columnarray, "") 可视化必须折叠为 1d 的 2d 数组以获得最终输出，这样您就可以看到您正在处理的内容。把它放在那里以供评论。

相关内容