您当前的位置:首页 > 文本与office

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

时间:08-31来源:作者:点击数:

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

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

案例:

将下图 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 列中,与数据表区域不匹配的值都填充了红色。

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

方便获取更多学习、工作、生活信息请关注本站微信公众号城东书院 微信服务号城东书院 微信订阅号
推荐内容
相关内容
栏目更新
栏目热门