Excel条件格式：高亮匹配或最接近N3的E3:M3单元格

操作前提 #

选中目标区域 E3:M3，进入「条件格式」→「新建规则」→「使用公式确定要设置格式的单元格」，分别添加以下两条规则，注意完全匹配规则要放在最上方（可通过「条件格式规则管理器」调整顺序）。

1. 完全匹配时高亮对应单元格

使用公式：

=E3=$N$3

• 说明：E3是相对引用，会自动遍历选中区域的每个单元格；$N$3是绝对引用，固定参照单元格。当单元格值与N3完全相等时，触发高亮格式。

2. 无匹配时高亮最接近值的单元格

使用公式：

=AND(COUNTIF($E$3:$M$3,$N$3)=0, ABS(E3-$N$3)=MIN(ABS($E$3:$M$3-$N$3)))

• 说明：
  • COUNTIF($E$3:$M$3,$N$3)=0：判断E3:M3中没有和N3完全匹配的值；
  • ABS(E3-$N$3)=MIN(ABS($E$3:$M$3-$N$3))：计算当前单元格与N3的差值绝对值，判断是否等于区域内所有差值绝对值的最小值（即最接近）；
  • AND确保两个条件同时满足时才触发高亮。

示例验证 #

当N3=$2.20，E3:M3包含2.12和2.65时：

• 无完全匹配值，COUNTIF返回0；
• 计算差值绝对值：|2.12-2.20|=0.08，|2.65-2.20|=0.45，最小值为0.08；
• 2.12所在单元格满足公式，被高亮。

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