如何在Excel中匹配无重复交叉引用姓名对并筛选未匹配人员

问题背景 #

我有一项活动的参与者报名数据，A2:A99列为参与者姓名，B2:B99列为参与者填写的搭档姓名（可留空）。每位参与者的搭档若报名，会在搭档栏填写该参与者姓名。需实现两个功能：

1. 在D2:D99与E2:E99列生成无重复的双向确认搭档对（即A填B为搭档，B也填A为搭档）；
2. 在G2:G99列生成未匹配人员列表，包含未填搭档或搭档未报名的人员。

示例输入数据 #

预期结果 #

确认搭档对

未匹配人员

解决方案 #

1. 生成无重复双向确认搭档对 #

D列（提取参与者）公式（D2单元格输入）：

=INDEX($A$2:$A$99, SMALL(IF(($B$2:$B$99<>"")*(COUNTIFS($A$2:$A$99,$B$2:$B$99,$B$2:$B$99,$A$2:$A$99)>0)*(MATCH($A$2:$A$99&$B$2:$B$99,$A$2:$A$99&$B$2:$B$99,0)=ROW($A$2:$A$99)-1), ROW($A$2:$A$99)-1, ""), ROW(A1)))

• Excel 2019及更早版本：输入后按 Ctrl+Shift+Enter 执行数组公式；
• Excel 365/2021：直接回车即可。

E列（匹配对应搭档）公式（E2单元格输入）：

=VLOOKUP(D2,$A$2:$B$99,2,FALSE)

下拉填充D、E列到D99、E99即可得到无重复的确认搭档对。

公式逻辑：

1. 先筛选出搭档栏非空、且对方存在报名记录并填写自己为搭档的行；
2. 通过MATCH判断当前行是该搭档对的首次出现，避免重复；
3. 用INDEX+SMALL按顺序提取符合条件的参与者姓名，再用VLOOKUP匹配对应搭档。

2. 生成未匹配人员列表 #

G2单元格输入以下公式，下拉填充到G99：

=INDEX($A$2:$A$99, SMALL(IF(($B$2:$B$99="")+(COUNTIFS($A$2:$A$99,$B$2:$B$99,$B$2:$B$99,$A$2:$A$99)=0), ROW($A$2:$A$99)-1, ""), ROW(A1)))

• Excel 2019及更早版本：按 Ctrl+Shift+Enter 执行数组公式；
• Excel 365/2021：直接回车即可。

公式逻辑：

筛选出两类人员：一是未填写搭档的，二是填写的搭档未报名、或对方未填写自己为搭档的，按顺序提取这些人员姓名。

内容的提问来源于stack exchange，提问作者Lex Plantenga