Google Sheet日期周数映射校验公式/脚本故障排查需求

需求说明 #

需在独立工作表中创建公式，对SourceSheet的D10:D200行执行以下校验：

• 仅处理D列非空行；
• D列存储日期的日部分，按5周规则划分：第1周1-7日、第2周8-14日、第3周15-21日、第4周22-28日、第5周29-31日，校验该日期所属周数是否与I-M列（对应第1-5周）中标记为1的列匹配；
• 触发错误的场景：日期周数与标记列不匹配、I-M列存在非1值、D列空但I-M列有值；
• 公式输出要求：首次发现错误返回1，无错误返回空字符串。

用户尝试的无效/偶失效公式 #

公式1（完全无效） #

=IFERROR(IF(ARRAYFORMULA(IF(AND(NOT(ISBLANK(IMPORTRANGE("https://docs.google.com/spreadsheets/d/1VW5d5Hx7BHu9SqXEKjHIti4a6758naDRBahhBDekhFI/edit?usp=drive_link", "Sheet1!D10:D200"))), INT(IMPORTRANGE("https://docs.google.com/spreadsheets/d/1VW5d5Hx7BHu9SqXEKjHIti4a6758naDRBahhBDekhFI/edit?usp=drive_link", "Sheet1!D10:D200")/7)+1<>MATCH(1, IMPORTRANGE("https://docs.google.com/spreadsheets/d/1VW5d5Hx7BHu9SqXEKjHIti4a6758naDRBahhBDekhFI/edit?usp=drive_link", "Sheet1!I10:M10"), 0)), 1, ""))<>"", 1, ""), "")

公式2（偶尔失效） #

=reduce("",Sheet1!$D$10:$D$20,lambda(a,c,
         if(a="",
            if(c="",
               if(sum(offset(c,0,column(Sheet1!$I:$I)-column(c),1,5))>0,
                  1,
                  ""),
               if(and(sum(offset(c,0,column(Sheet1!$I:$I)-column(c),1,5))=1,
                      offset(c,0,column(Sheet1!I:I)-column(c)+quotient(c-B1+1,7))=1),
                  "",
                  1)),
            a)))

解决方案公式 #

=REDUCE("", Sheet1!$D$10:$D$200, LAMBDA(result, current_row,
  IF(result<>"", result,
    LET(
      d_val, current_row,
      im_range, OFFSET(current_row, 0, COLUMN(Sheet1!$I:$I)-COLUMN(current_row), 1, 5),
      im_non_blank, TOCOL(im_range, 1),
      // 检查I-M列是否有非1值
      has_invalid_im, NOT(COUNTA(im_non_blank)=SUM(im_non_blank)),
      // D空但I-M有值
      d_empty_im_filled, d_val="" AND SUM(im_range)>0,
      // 计算D值对应的周数
      week_num, QUOTIENT(d_val-1, 7)+1,
      // 周数匹配检查
      is_week_match, d_val<>"" AND INDEX(im_range, 1, week_num)=1 AND SUM(im_range)=1,
      // 判断是否触发错误
      IF(OR(has_invalid_im, d_empty_im_filled, d_val<>"" AND NOT(is_week_match)), 1, "")
    )
  )
))

核心修复点 #

1. 非1值校验：用TOCOL(im_range,1)提取I-M列非空值，通过COUNTA(im_non_blank)=SUM(im_non_blank)判断是否存在非1值；
2. 周数计算修正：QUOTIENT(d_val-1,7)+1确保1-7日对应第1周，消除原公式依赖B列的错误；
3. 遍历范围修正：覆盖D10:D200（原公式2仅到D20）；
4. 逻辑明确化：拆分每个错误条件，避免逻辑重叠导致的偶发失效。

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