如何用Excel公式定位两个数据集行内不匹配的列？

核心思路 #

针对已通过XLOOKUP找出的不匹配行，逐列对比两个工作表的对应单元格，返回差异列的位置或名称，同时兼容字符串、数字、日期等多种数据类型。

实用公式方案 #

1. 逐列单单元格差异判断 #

在目标单元格输入公式，对比两个工作表的对应单元格，直接标记差异：

=IF(EXACT(Sheet1!A2, Sheet2!A2), "", "差异："&COLUMN(A2)&"列")

• 说明：EXACT严格区分字符串大小写，同时能准确识别数字、日期的差异（日期在Excel中以数值存储，可直接对比）；匹配返回空值，不匹配则返回对应列号。
• 批量操作：选中公式单元格，横向拖拽覆盖所有57列，再纵向拖拽至所有行即可批量检查。

2. 单单元格汇总整行差异列 #

如果需要在单个单元格内汇总当前行所有差异列的名称或位置，使用以下数组公式（Excel 365/2021直接输入，旧版本按Ctrl+Shift+Enter触发）：

=TEXTJOIN(", ", TRUE, IF(EXACT(Sheet1!A2:BA2, Sheet2!A2:BA2), "", COLUMN(A2:BA2)&"列"))

• 说明：TEXTJOIN将所有差异列的位置用逗号拼接；EXACT逐列对比整行数据；IF返回空值或列号；TRUE参数自动忽略空值。
• 适配列名：若要返回表头名称（如A列对应"员工ID"），将公式中的COLUMN(A2:BA2)&"列"替换为表头引用：

=TEXTJOIN(", ", TRUE, IF(EXACT(Sheet1!A2:BA2, Sheet2!A2:BA2), "", INDEX(Sheet1!$A$1:$BA$1, COLUMN(A2:BA2))))

3. 特殊数据类型适配细节 #

• 日期：Excel中日期以数值存储，EXACT可直接对比；若为文本格式日期，同样适用。
• 格式不一致的数字：如果一个单元格是数值格式、另一个是文本格式，可先用VALUE统一转换后对比：

=IF(EXACT(VALUE(Sheet1!A2), VALUE(Sheet2!A2)), "", "差异："&COLUMN(A2)&"列")

• 空单元格：两个单元格均为空时EXACT返回TRUE，一个空一个有值时返回FALSE，符合差异判断逻辑。

示例演示 #

假设Sheet1和Sheet2第2行数据如下：

Sheet2第2行的"入职日期"为2021/03/16、"月薪资"为9000，使用汇总公式将返回：入职日期列, 月薪资列

注意事项 #

• 确保两个工作表的行对应关系正确（已通过XLOOKUP匹配行，行号需一一对应）。
• 针对1000行57列的数据集，Excel 365的数组公式性能足够；旧版本Excel建议分区域计算，避免卡顿。

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