如何用公式按薪资周期为Shifts表设置行交替颜色？

我需要根据Shifts表中Punch-out时间匹配Pay Checks表内的薪资周期，为Shifts工作表的行设置交替颜色，目前使用的条件格式规则公式如下：

=MOD(MATCH(A2,'Pay Checks'!B:B,-1),2)=1

操作尝试 #

1. 打开Shifts工作表的条件格式设置界面
2. 新建规则并选择「使用公式确定要设置格式的单元格」
3. 输入上述公式并设置对应填充颜色
4. 将规则应用到目标数据区域

相关数据 #

Shifts工作表数据 #

Pay Checks工作表数据（第一列为周期开始，第二列为周期结束） #

原公式存在两个核心问题：

1. 引用错误：Shifts表中Punch-out时间在B列，但公式误用了A2，应改为B2
2. 匹配模式错误：MATCH函数的-1参数要求查找区域必须降序排列，但Pay Checks表的B列是升序的，会导致匹配结果完全错误

正确的条件格式公式应为：

=MOD(MATCH(B2,'Pay Checks'!B:B,1),2)=1

公式说明 #

• MATCH(B2,'Pay Checks'!B:B,1)：在Pay Checks的B列（周期结束时间）中，找到小于等于当前Punch-out时间的最大数值的位置，精准匹配对应的薪资周期
• MOD(...,2)=1：判断周期位置的奇偶性，为奇数位置的周期行应用第一种颜色；再新建一条规则使用MOD(...,2)=0，为偶数位置的周期行应用第二种颜色，即可实现交替着色

完整操作步骤 #

1. 选中Shifts表中需要着色的行区域（例如A2:B19）
2. 新建第一个条件格式规则：
   • 选择「使用公式确定要设置格式的单元格」
   • 输入公式=MOD(MATCH(B2,'Pay Checks'!B:B,1),2)=1
   • 设置第一种填充颜色
3. 新建第二个条件格式规则：
   • 同样选择公式模式
   • 输入公式=MOD(MATCH(B2,'Pay Checks'!B:B,1),2)=0
   • 设置第二种填充颜色
4. 确认规则应用范围正确后完成设置

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