Google Sheets条件格式公式修复：多余单元格误着色问题

用@rockinfreakshow编写的条件格式公式存在问题：按教师姓名后的分钟数为下方单元格填充背景色时，出现多余单元格被着色的情况。比如标注Gordon 45（对应45分钟），实际仅需着色其下方2个单元格，但当前公式错误地着色了3个。

当前使用的公式：

=INDEX(LET(Σ,XLOOKUP(1,--REGEXMATCH(B$2:B2," \d+$"),B$2:B2,,,-1),XMATCH(CELL("address",B2),ADDRESS(ROW(Σ)+SEQUENCE(CHOOSECOLS(SPLIT(Σ," "),-1)/15,1,0),COLUMN(Σ)))))

问题原因 #

公式里SEQUENCE(...,1,0)的起始偏移量设为0，这会把教师姓名所在的标记行本身也纳入着色范围。以45分钟为例，45/15=3的序列长度加上标记行，最终会选中标记行+下方2行，共3个单元格，不符合仅着色下方2个单元格的需求。

修正方案 #

根据实际需求，有两种修正方式：

1. 仅着色标记行下方对应分钟数的单元格（45分钟对应3个下方单元格） #

把SEQUENCE的起始偏移量从0改成1，这样就从标记行的下一行开始生成序列，不会包含标记行本身：

=INDEX(LET(Σ,XLOOKUP(1,--REGEXMATCH(B$2:B2," \d+$"),B$2:B2,,,-1),XMATCH(CELL("address",B2),ADDRESS(ROW(Σ)+SEQUENCE(CHOOSECOLS(SPLIT(Σ," "),-1)/15,1,1),COLUMN(Σ)))))

2. 按示例需求，45分钟对应2个下方单元格 #

如果需求是分钟数对应的单元格数要减1（即扣除标记行的时段），则把序列长度改为(分钟数/15)-1，同时保持起始偏移量为1：

=INDEX(LET(Σ,XLOOKUP(1,--REGEXMATCH(B$2:B2," \d+$"),B$2:B2,,,-1),XMATCH(CELL("address",B2),ADDRESS(ROW(Σ)+SEQUENCE((CHOOSECOLS(SPLIT(Σ," "),-1)/15)-1,1,1),COLUMN(Σ)))))

验证 #

以Gordon 45为例：

• 用第一种修正公式：序列从标记行下一行开始，长度3，会选中下方3个单元格
• 用第二种修正公式：长度为3-1=2，会选中下方2个单元格，完全匹配示例要求

内容的提问来源于stack exchange，提问作者R.K.