Excel 双边对账，找到了左边标绿，找不到右边标红

有人咨询一个双边对账的问题，左边为数据表，右边为需要核对的数据，找到了高亮显示左边，找不到的高亮显示右边。

听上去挺复杂，其实很容易，用好条件格式，万变不离其宗。

案例：

将下图 1 中的 H 列与左侧的数据表进行核对，找到的数据，在数据表中填充绿色；找不到的，在 H 列填充红色。

H 列的数据不固定，当新增数据时，填充规则也能应用到新的单元格中。

效果如下图 2 所示。

解决方案：

1. 选中 B2:F9 区域 --> 选择菜单栏的“开始”-->“条件格式”-->“新建规则”

2. 在弹出的对话框中选择“使用公式确定要设置格式的单元格” --> 输入以下公式 --> 点击“格式”：

=COUNTIF($H:$H,B2)

公式释义：

如果在选定的单元格区域找到 H 列的任何值，则触发条件

* 参数 B2 需要设置为相对引用。

3. 在弹出的对话框中选择“填充”选项卡 --> 选择所需的填充色 --> 点击“确定”

4. 点击“确定”

数据表中与 H 列匹配的值都自动填充了绿色。

5. 选中 H 列 --> 选择菜单栏的“开始”-->“条件格式”-->“新建规则”

6. 在弹出的对话框中选择“使用公式确定要设置格式的单元格”--> 输入以下公式 --> 点击“格式”：

=AND(COUNTIF($B$2:$F$9,H1)=0,ISNUMBER(H1))

公式释义：

COUNTIF($B$2:$F$9,H1)=0：当 H 列的值在选中的数据区域不存在

ISNUMBER(H1)：H 列的单元格为数值；这样可以避免所有空单元格都填充为红色

上述两个条件都满足时触发

* 参数中的 H1 都必须相对引用。

7. 在弹出的对话框中选择“填充”选项卡 --> 选择所需的填充色 --> 点击“确定”

8. 点击“确定”

H 列中，与数据表区域不匹配的值都填充了红色。

更改或新增数据时，左右两边的颜色都会自动随之变化。

Excel学习世界

转发、在看也是爱！