Google Sheet五周制日期对应周列校验公式修复需求

问题分析 #

原公式存在以下核心问题：

• AND函数不支持数组运算，无法逐行执行多条件判断；
• 固定引用I10:M10，未对应每行的I-M列数据；
• 未实现“I-M列存在非1值”的校验逻辑；
• 重复调用IMPORTRANGE导致运行效率低下；
• 周数计算逻辑错误（按全年周数划分，而非需求中的当月固定日期区间）。

修复后的公式 #

=LET(
  src_url, "https://docs.google.com/spreadsheets/d/1wT4KoLxAIuUvlmKtq0O9ye9LpW0LhCGSEg0xMSR48q8/edit#gid=0",
  dates, IMPORTRANGE(src_url, "Sheet1!D10:D200"),
  weeks_data, IMPORTRANGE(src_url, "Sheet1!I10:M200"),
  // 按需求计算日期对应的周数（1-7日→1，8-14日→2…）
  calc_week, ARRAYFORMULA(IF(NOT(ISBLANK(dates)), CEILING(DAY(dates)/7, 1), "")),
  // 检查每行I-M列是否存在非1值（非空且不等于1即判定错误）
  invalid_week_vals, ARRAYFORMULA(IF(NOT(ISBLANK(dates)), MMULT(N(weeks_data<>1), SEQUENCE(COLUMNS(weeks_data),1,1,0))>0, FALSE)),
  // 检查日期周数与标记1的列是否匹配
  week_mismatch, ARRAYFORMULA(IF(NOT(ISBLANK(dates)), calc_week<>XLOOKUP(1, weeks_data, SEQUENCE(1,COLUMNS(weeks_data))), FALSE)),
  // 汇总错误：只要有一行错误返回1，全对返回空
  IF(MAX(ARRAYFORMULA(IF(invalid_week_vals+week_mismatch, 1, 0)))>0, 1, "")
)

公式说明 #

1. LET封装优化：统一管理数据源URL和导入的列数据，避免重复调用IMPORTRANGE，提升运行效率；
2. 周数计算逻辑：通过CEILING(DAY(dates)/7,1)直接根据日期的日数计算对应周数，完全匹配需求中的五周划分规则；
3. 非1值校验：用MMULT统计每行I-M列中非1值的数量，只要存在非1值就标记为错误；
4. 周数匹配校验：逐行定位I-M列中标记1的列序号，与计算出的周数对比，不匹配则标记为错误；
5. 结果汇总：只要任意一行存在错误，最终返回1；所有行无错误则返回空值。

使用注意事项 #

• 首次使用需授权IMPORTRANGE访问目标表格；
• 确保目标表格中Sheet1!I10:M200仅在对应周数的列标记1，其余列留空或填非1值都会触发错误判定。

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