如何用唯一ID标签核对Excel两工作表中姓名数组的差异？

示例数据 #

Sheet1 #

Sheet2 #

解决方案 #

方法一：辅助列标记缺失姓名 #

在Sheet1中新增一列（比如G列，表头设为「匹配状态」），在G2单元格输入以下公式，下拉填充至所有行：

=IF(AND($A2<>"",B2<>""),IF(SUMPRODUCT((Sheet2!$A:$A=$A2)*(Sheet2!$B:$F=B2))=0,"缺失","存在"),"")

公式解释：

• AND($A2<>"",B2<>"")：排除ID或姓名为空的单元格，避免无效判断
• (Sheet2!$A:$A=$A2)：判断Sheet2中每一行的ID是否和当前行ID一致，生成包含TRUE/FALSE的数组
• (Sheet2!$B:$F=B2)：判断Sheet2中所有姓名单元格是否和当前姓名一致，生成二维数组
• 两个数组相乘：只有ID和姓名同时匹配的位置会返回1，其余为0
• SUMPRODUCT(...)：对相乘后的数组求和，结果为0说明该姓名在Sheet2对应ID下未出现，返回「缺失」；否则返回「存在」

方法二：条件格式高亮缺失姓名 #

无需新增辅助列，直接用条件格式高亮Sheet1中未在Sheet2对应ID下出现的姓名：

1. 选中Sheet1中B2:F4的所有姓名单元格
2. 点击「开始」→「条件格式」→「新建规则」→选择「使用公式确定要设置格式的单元格」
3. 输入公式：

=AND($A2<>"",B2<>"",SUMPRODUCT((Sheet2!$A:$A=$A2)*(Sheet2!$B:$F=B2))=0)

4. 设置高亮格式（比如填充红色），确认后即可看到缺失的姓名被自动标记

方法三：XMATCH函数写法（Excel 365/2021适用） #

如果使用Excel 365或2021版本，也可以用XMATCH函数实现，公式更直观：

=IF(AND($A2<>"",B2<>""),IF(ISERROR(XMATCH(B2,OFFSET(Sheet2!$A$1,MATCH($A2,Sheet2!$A:$A,0)-1,1,1,5))),"缺失","存在"),"")

公式解释：

• MATCH($A2,Sheet2!$A:$A,0)：找到当前ID在Sheet2中对应的行号
• OFFSET(Sheet2!$A$1,行号-1,1,1,5)：定位到Sheet2中该ID对应的Name1至Name5的单元格区域（1行5列）
• XMATCH(B2, 上述区域)：在目标区域查找当前姓名，找不到则返回错误值
• ISERROR(...)：判断查找是否失败，失败则返回「缺失」，否则「存在」

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