Excel非VBA实现命名范围匹配及偏移单元格校验的条件格式设置问题

操作流程 #

• 选中所有需要应用高亮规则的源单元格区域
• 依次点击「开始」→「条件格式」→「新建规则」→「使用公式确定要设置格式的单元格」
• 在公式输入栏填入以下内容，注意将公式内的A2替换为你选中的源单元格区域的左上角首个单元格，使用相对引用（不要加$符号锁行/列）：
  =SUMPRODUCT(--(LEFT(A2,1)=K3:K12),--(L3:L12="")) + SUMPRODUCT(--(LEFT(A2,1)=M3:M12),--(N3:N12="")) + SUMPRODUCT(--(LEFT(A2,1)=O3:O12),--(P3:P12="")) > 0
• 点击「格式」设置所需的高亮样式，确认后保存规则即可生效。

规则说明 #

• 由于命名范围CodeX为非连续区域，普通查找/计数函数无法直接识别，因此拆分CodeX的三个连续分段分别做匹配校验
• 每个SUMPRODUCT段逐行判断两个条件：当前行单元格等于源单元格提取的首字符、该单元格右侧偏移1列的同行单元格为空，双条件同时满足时计数+1
• 三个分段的计数总和大于0时，触发高亮规则

调整提示 #

如果后续CodeX的覆盖范围变更，只需对应修改公式内的三个匹配分段和对应的右侧校验列区域即可。

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