Google Sheets条件格式公式：高亮首个缺失值后的所有序列号

需求说明 #

按工作室和财年分组，找出每组中从1开始的首个缺失编号，将该组内所有大于这个缺失编号的数字所在单元格高亮（条件格式公式返回TRUE时触发高亮）。

核心规则示例 #

• 工作室A+财年X-X：首个缺失编号为7，所有大于7的编号需标红
• 工作室A+财年O-O：首个缺失编号为3，所有大于3的编号需标红
• 工作室B+财年O-O：首个缺失编号为1，所有大于1的编号需标红

注意事项 #

• 每组（工作室+财年）的编号从1开始计数
• 允许编号重复出现
• 数据行无固定顺序
• 单个单元格可包含多个逗号分隔的编号

条件格式公式 #

假设数据对应列：

• 工作室列 = A列（当前行单元格为A2）
• 财年列 = B列（当前行单元格为B2）
• 编号列 = C列（当前行单元格为C2）

选中需要应用格式的编号列区域（比如C2:C），添加条件格式的自定义公式：

=LET(
  group, FILTER($C:$C, $A:$A=$A2, $B:$B=$B2),
  all_nums, FLATTEN(SPLIT(TEXTJOIN(",", TRUE, group), ",")),
  sorted_unique, SORT(UNIQUE(VALUE(all_nums))),
  first_missing, XMATCH(FALSE, sorted_unique=SEQUENCE(MAX(sorted_unique))),
  MAX(VALUE(SPLIT($C2, ","))) > IFERROR(first_missing, 9^9)
)

公式逻辑拆解 #

1. group：筛选出当前行同工作室、同财年的所有编号单元格
2. all_nums：将所有单元格的编号拆分并展平成单个数字的列表
3. sorted_unique：提取唯一编号并按升序排序
4. first_missing：对比排序后的唯一编号与从1开始的连续序列，找到第一个不匹配的位置（即首个缺失编号）
5. 最后判断当前单元格中的最大编号是否大于首个缺失编号，若是则返回TRUE触发高亮

示例验证 #

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