请求编写Google Sheets多数据输入表的整合索引查询公式

需求概述 #

• 涉及6个数据输入表：Kindergarten - Data Entry、1st Grade - Data Entry、2nd Grade - Data Entry、3rd Grade - Data Entry、4th Grade - Data Entry、5th Grade - Data Entry
• 目标：在Index表生成整合列表，筛选出**Pre或Post评估结果为'No'/'0'/'1'**的学生记录
• 必填字段：
  • Standard：对应各表C4:BK4行的标准名称（自动填充空值）
  • Student Name：对应各表A列的学生姓名
  • Pre：对应评估项的Pre结果（仅保留'No'/'0'/'1'）
  • Post：对应评估项的Post结果（仅保留'No'/'0'/'1'）
  • Cross Cutting Concept：对应各表C4:BK5行的跨领域概念（自动填充空值）
• 原有公式问题：仅筛选单表C8:AF列的'No'值，未覆盖所有表和扩展筛选条件

整合查询公式 #

=LET(
  tables, {"Kindergarten - Data Entry", "1st Grade - Data Entry", "2nd Grade - Data Entry", "3rd Grade - Data Entry", "4th Grade - Data Entry", "5th Grade - Data Entry"},
  processTable, LAMBDA(tbl,
    LET(
      headers, INDIRECT(tbl&"!C4:BK4"),
      crossCutting, INDIRECT(tbl&"!C5:BK5"),
      preVals, INDIRECT(tbl&"!C8:BK"),
      postVals, INDIRECT(tbl&"!C8:BK"),
      studentNames, INDIRECT(tbl&"!A8:A"),
      standards, SCAN("", headers, LAMBDA(a,c, IF(c="", a, c))),
      crossCuttingFill, SCAN("", crossCutting, LAMBDA(a,c, IF(c="", a, c))),
      filterMask, (preVals="No")+(preVals="0")+(preVals="1")+(postVals="No")+(postVals="0")+(postVals="1")>0,
      filteredData, FILTER(HSTACK(standards, studentNames, preVals, postVals, crossCuttingFill), filterMask),
      filteredData
    )
  ),
  combined, REDUCE("", tables, LAMBDA(acc,tbl, VSTACK(acc, processTable(tbl)))),
  INDEX(combined, SEQUENCE(ROWS(combined)), SEQUENCE(COLUMNS(combined)))
)

公式逻辑说明 #

1. 定义表数组：tables变量包含所有需要整合的6个数据输入表名称
2. 单表处理函数：processTable负责处理单个表的逻辑：
   • 提取表头、跨领域概念、评估值、学生姓名等原始数据
   • 使用SCAN函数填充表头和跨领域概念的空值，确保每个评估项都关联正确的分类
   • 创建筛选掩码，匹配Pre或Post为'No'/'0'/'1'的记录
   • 筛选并组合所需字段成结构化数据
3. 合并多表数据：通过REDUCE和VSTACK将6个表的处理结果纵向合并
4. 输出结果：使用INDEX输出最终的整合列表

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