有人咨询一个双边对账的问题,左边为数据表,右边为需要核对的数据,找到了高亮显示左边,找不到的高亮显示右边。
听上去挺复杂,其实很容易,用好条件格式,万变不离其宗。
将下图 1 中的 H 列与左侧的数据表进行核对,找到的数据,在数据表中填充绿色;找不到的,在 H 列填充红色。
H 列的数据不固定,当新增数据时,填充规则也能应用到新的单元格中。
效果如下图 2 所示。
解决方案:
1. 选中 B2:F9 区域 --> 选择菜单栏的“开始”-->“条件格式”-->“新建规则”
2. 在弹出的对话框中选择“使用公式确定要设置格式的单元格” --> 输入以下公式 --> 点击“格式”:
=COUNTIF($H:$H,B2)
公式释义:
* 参数 B2 需要设置为相对引用。
3. 在弹出的对话框中选择“填充”选项卡 --> 选择所需的填充色 --> 点击“确定”
4. 点击“确定”
数据表中与 H 列匹配的值都自动填充了绿色。
5. 选中 H 列 --> 选择菜单栏的“开始”-->“条件格式”-->“新建规则”
6. 在弹出的对话框中选择“使用公式确定要设置格式的单元格”--> 输入以下公式 --> 点击“格式”:
=AND(COUNTIF($B$2:$F$9,H1)=0,ISNUMBER(H1))
公式释义:
* 参数中的 H1 都必须相对引用。
7. 在弹出的对话框中选择“填充”选项卡 --> 选择所需的填充色 --> 点击“确定”
8. 点击“确定”
H 列中,与数据表区域不匹配的值都填充了红色。
更改或新增数据时,左右两边的颜色都会自动随之变化。