Google Sheets跨文件按单元格范围求和公式需求（含IMPORTRANGE）

核心问题分析 #

原公式的关键错误在于使用TOCOL(range, 1)按列堆叠数据，导致日期顺序完全错乱。B-H列的每周数据会被拆成单列的同周同一天数据（比如所有周一数据连在一起，再所有周二数据），而非按日期先后的连续序列。

修正后的公式 #

基于全年周数计算（适用于数据从当年第一周开始） #

=LET(
  data, IMPORTRANGE("10q1hgvP8FgdTHkwH4y1ls5Tv1di6XYgyblq4gm_PK-I", "'Employee'!B5:H"),
  daily_data, TOCOL(data, 2),  // 按行堆叠，生成按日期顺序的单列数据
  start_day, (WEEKNUM(D2, 2) - 1) * 7 + WEEKDAY(D2, 2),
  end_day, (WEEKNUM(D3, 2) - 1) * 7 + WEEKDAY(D3, 2),
  SUM(CHOOSEROWS(daily_data, SEQUENCE(end_day - start_day + 1, 1, start_day)))
)

基于数据起始日期计算（更可靠，不受跨年/非年初起始影响） #

如果你的数据区域并非从当年第一周开始，建议先确定数据的起始日期（比如B5对应的周一日期，存入单元格E2），使用以下公式：

=LET(
  data, IMPORTRANGE("10q1hgvP8FgdTHkwH4y1ls5Tv1di6XYgyblq4gm_PK-I", "'Employee'!B5:H"),
  daily_data, TOCOL(data, 2),
  start_day, DATEDIF(E2, D2, "D") + 1,
  end_day, DATEDIF(E2, D3, "D") + 1,
  SUM(CHOOSEROWS(daily_data, SEQUENCE(end_day - start_day + 1, 1, start_day)))
)

关键调整说明 #

1. TOCOL参数修正：将TOCOL(..., 1)改为TOCOL(..., 2)，按行堆叠数据，确保每周的B-H列（7天）按日期顺序连续排列。
2. 日期计算逻辑：
   • WEEKNUM(D2, 2)以周一为一周起始，匹配B-H列从周一到周日的顺序；若你的B列是周日，需改为WEEKNUM(D2, 1)。
   • DATEDIF方式直接计算目标日期与数据起始日的天数差，避免跨年周数计算的误差。

注意事项 #

• 首次使用IMPORTRANGE需授权当前文档访问外部只读文档，点击公式旁的提示完成授权。
• 确保D2、D3的日期格式正确，且落在外部文档数据覆盖的日期范围内。
• 外部文档需已共享给当前Google账号，否则会返回权限错误。

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