Excel跨文档多工作表数据比对条件格式设置求助

前提准备 #

• 确保Document1.xlsx和Document2.xlsx处于打开状态，跨文档引用需要源文件打开才能生效。
• 确定两个文档中用于匹配的关键列（假设为A列，可根据实际调整）。

步骤1：选中目标区域 #

在Document2中选中需要设置颜色匹配的单元格区域（例如A2:A1000，避开表头行）。

步骤2：创建条件格式规则（共3条，依次设置） #

点击菜单栏「开始」→「条件格式」→「新建规则」→选择「使用公式确定要设置格式的单元格」。

规则1：匹配Document1的Sheet1数据

• 输入公式：

  =NOT(ISERROR(XLOOKUP(A2,[Document1.xlsx]Sheet1!$A:$A,[Document1.xlsx]Sheet1!$A:$A,"")))
  

• 点击「格式」→选择填充颜色（例如绿色）→确定。

规则2：匹配Document1的Sheet2数据

• 输入公式：

  =NOT(ISERROR(XLOOKUP(A2,[Document1.xlsx]Sheet2!$A:$A,[Document1.xlsx]Sheet2!$A:$A,"")))
  

• 设置填充颜色（例如黄色）→确定。

规则3：匹配Document1的Sheet3数据

• 输入公式：

  =NOT(ISERROR(XLOOKUP(A2,[Document1.xlsx]Sheet3!$A:$A,[Document1.xlsx]Sheet3!$A:$A,"")))
  

• 设置填充颜色（例如橙色）→确定。

关键注意事项 #

• 如果Document1文件名包含空格或特殊字符，公式中需用单引号包裹文件名，例如：

  =NOT(ISERROR(XLOOKUP(A2,'Document 1.xlsx'!Sheet1!$A:$A,'Document 1.xlsx'!Sheet1!$A:$A,"")))
  

• 公式中的A2是相对引用，确保选中区域内每个单元格都会对应查找自身内容；而$A:$A是绝对引用，固定匹配的列。
• 若之前使用XLOOKUP失败，大概率是未用NOT(ISERROR())转换结果——XLOOKUP找不到匹配值时会返回错误，条件格式需要布尔值（TRUE/FALSE）来触发格式。

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