excel - 在单元格矩阵中查找与第二个工作表的匹配项

Question

我正在尝试生成一个由彼此有共同点的人组成的矩阵。在一个工作表中，我有矩阵本身，在另一个工作表中，我有原始数据。所需的输出是，如果存在匹配，则在单元格中放置一个“x”；如果不匹配，则不放置任何内容。

这是矩阵工作表的示例，其中 A 列中向下的名称与第 1 行中的名称相同。名称采用“名字、中间名、姓氏”格式(通常):

   A     | B     | C     | D     | E    
 ------------------------------------
1|       | Name1 | Name2 | Name3 | Name4
2| Name1 |       |       |       |
3| Name2 |       |       |       |
4| Name3 |       |       |       |
5| Name4 |       |       |       |

这是原始数据工作表的示例。 A 列是排序后的姓名列表，B 列是他们的共同对象。列表中有数百个名字:

   A     | B     
 ------------------------------------
1| Name1 | Name33
2| Name1 | Name5
3| Name1 | Name21
4| Name2 | Name1
5| Name2 | Name92
...

我需要编写的公式进入矩阵工作表中的空白单元格。这是最终输出的示例(尽管实际矩阵中有更多名称):

   A     | B     | C     | D     | E    
 ------------------------------------
1|       | Name1 | Name2 | Name3 | Name4
2| Name1 |       |       | x     | x
3| Name2 | x     |       | x     |
4| Name3 | x     | x     |       |
5| Name4 |       |       | x     |

这是我在碰壁之前已经走了多远。我尝试编写一个 VLOOKUP 公式来执行此操作，但如果我只是告诉它查看原始数据工作表的整个范围，它就不起作用，因为它会从错误的人那里获取匹配项。我只希望它与 B 列中该单元格 A 列中具有名称的部分相匹配。

因此，我能得到的最接近的结果就是将原始数据文件中的特定范围放入矩阵中该特定行上列出的名称中。例如，我手动查找原始数据文件A列中的Name1，发现它存在于第39-62行。因此，对于 Name1，如果在 Raw_Data 的 B 列的第 39-62 行之间找到跨越矩阵第 1 行的名称之一，则标记“x”:

=IF(IFNA(VLOOKUP(B$1,Raw_Data!$B39:$B62,1,FALSE),"")="","","x")

这只适用于我的矩阵表中的一行。然后，当转到下一行时，我必须手动查找该人的 Raw_Data 行并将其放入公式中。诚然，它节省了一点时间，但还不如我可以让这个公式在整个矩阵中运行而无需任何更改。

Answer 1

您可以在 B2 中使用此 COUNTIFS 公式来横向和向下复制

=IF(SUMPRODUCT(COUNTIFS(Raw_Data!$A:$A,IF({1,0},B$1,$A2),Raw_Data!$B:$B,IF({0,1 },B$1,$A2))),"X","")

当 B1 名称位于 Raw_data 的 A 列中且 A2 名称位于 B 列中时，COUNTIFS 才会计数......反之亦然。如果计数 > 0，则有匹配项，您将得到“x”

我假设您总是希望“x”出现在两个框中......但在您的示例中，您只在一个框中显示名称，例如Name2/Name1 显示为“x”，但反之则不然 - 如果应该是这样，那么它是一个更简单的 COUNTIFS，如下所示:

=IF(COUNTIFS(Raw_Data!$A:$A,$A2,Raw_Data!$B:$B,B$1),"X","")