我试图生成一个彼此相同的人的矩阵 . 在一个工作表中，我有矩阵本身，而在另一个工作表中，我有原始数据 . 所需的输出是如果匹配则在单元格中放置“x”，如果没有匹配则不放置任何内容 .
下面是矩阵工作表的示例，其中A列中的名称与第1行中的名称相同 . 名称采用“firstname middlename lastname”格式（通常）：
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行并将其放入公式中 . 当然，它节省了一点时间，但几乎没有我能让这个公式在整个矩阵中工作而没有任何变化 .
3 回答
我确信有更优雅的解决方案，但如果您添加了辅助列，这将非常容易 .
然后你可以对两个版本的连接字符串使用
Countif你可以在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的col A和Col B中的A2名称时，COUNTIFS计数....反之亦然 . 如果计数> 0则那么匹配就会得到“x”
我假设你总是希望“x”出现在两个方框中......但在你的例子中，你只在一个中显示名字，例如Name2 / Name1以“x”显示，但不是相反的方式 - 如果它应该是这样的话那么它是一个更简单的COUNTIFS，如下所示：
=IF(COUNTIFS(Raw_Data!$A:$A,$A2,Raw_Data!$B:$B,B$1),"X","")我设法通过使用数组公式实现这一目标 . 基本上你需要做的是使用名称（行和列）创建一个键，并在公式中的引用表上执行相同的操作 . 然后，如果有1个匹配，则放一个x .
与往常一样，使用数组公式时，您需要在公式栏中按CTRL SHIFT ENTER以使公式正确计算 .