如何将数组公式逻辑整合为条件格式自定义规则中的单个公式

问题原因 #

你的假设完全成立，Google Sheets的条件格式规则会针对应用范围内的每一个单元格单独执行公式计算，不存在ARRAYFORMULA的全局数组上下文，所有相对引用的范围会随着当前计算的单元格行号自动偏移：
比如你设置条件格式的应用范围包含第n行，公式中写的B4:B会自动解析为Bn:B，仅统计当前行及以下的匹配数据，和ARRAYFORMULA全局统计整列的逻辑完全不一致，因此高亮结果有偏差。

修复方案 #

将所有统计范围的起始行设置为绝对引用，锁死起始行号即可对齐原ARRAYFORMULA的计算逻辑：

1. 简化版教师冲突检测条件格式公式修改为：

=COUNTIFS($A$4:$A, $A4, $B$4:$B, $B4) > 1

这里$A$4:$A、$B$4:$B锁死了统计范围从第4行开始覆盖整列，$A4、$B4仅锁死列号，逐行计算时会自动读取当前行的时间段、教师信息做匹配，和原ARRAYFORMULA逻辑完全一致。

2. 包含助教交叉冲突检测的复杂公式修改为：

=(COUNTIFS($A$3:$A, $A3, $B$3:$B, $B3) + COUNTIFS($A$3:$A, $A3, $C$3:$C, $B3)) > 1

替换原来的条件格式公式即可覆盖所有冲突场景，不会出现漏高亮的问题。

补充说明 #

你原来用=len(C4)的方案没有问题，是因为C列的结果已经是ARRAYFORMULA全局计算完成的静态值，条件格式仅需要读取当前行C列的结果做判断，不需要自行执行全局统计，因此不会触发范围偏移的问题。

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