如何在Google Sheets中创建自动遍历行的带表头表尾查询公式

在Sheet3中通过QUERY公式，依据Sheet1列A的数值从Sheet2提取对应数据，需为每组匹配结果添加表头行（对应Sheet1的A列单元格内容）和表尾行（包含"TOTAL"及Sheet1对应行的B列数值），同时要求自动遍历Sheet1列A的所有非空行，直至遇到空行停止。

目前已手动实现前两次循环的公式（如下），但重复数十次过于繁琐，希望简化，询问是否可通过INDEX、VLOOKUP、ARRAY类函数实现，还是需要编写脚本。

当前手动公式：

={ 
{Sheet1!A3,"","","","","",""}; 
query(Sheet2!D:S, "select D,F,H,J,N,P,R where J contains '"&Sheet1!A3&"' order by F",0);
{"TOTAL","","","","","","",Sheet1!B3}; 
{"","","","","","",""}; 
{Sheet1!A4,"","","","","",""}; 
query(Sheet2!D:S, "select D,F,H,J,N,P,R where J contains '"&Sheet1!A4&"' order by F",0); {"TOTAL","","","","","","",Sheet1!B4} 
}

方案1：纯公式实现（Google Sheets 新版可用） #

利用REDUCE+LET函数实现自动遍历，无需手动重复代码：

=REDUCE("", SEQUENCE(COUNTA(Sheet1!A3:A)), LAMBDA(acc, i, 
  LET(
    currentKey, INDEX(Sheet1!A3:A, i),
    totalVal, INDEX(Sheet1!B3:B, i),
    queryRes, QUERY(Sheet2!D:S, "select D,F,H,J,N,P,R where J contains '"&currentKey&"' order by F", 0),
    headerRow, {currentKey, "", "", "", "", "", ""},
    footerRow, {"TOTAL", "", "", "", "", "", "", totalVal},
    blankRow, {"";"";"";"";"";"";""},
    IF(acc="", {headerRow; queryRes; footerRow; blankRow}, {acc; headerRow; queryRes; footerRow; blankRow})
  )
))

公式说明 #

• COUNTA(Sheet1!A3:A)：统计Sheet1 A列第3行起的非空行数
• SEQUENCE：生成1到该行数的遍历序列
• REDUCE：逐行累积拼接每组的表头、查询结果、表尾和空行
• LET：定义变量简化公式结构，提升可读性

注意事项 #

• 若需精确匹配而非模糊匹配，将QUERY中的contains改为=即可，如where J = '"&currentKey&"'
• 若Sheet1 A列存在重复值，可改用UNIQUE(FILTER(Sheet1!A3:A, Sheet1!A3:A<>""))替代Sheet1!A3:A实现去重处理

方案2：Google Apps Script 实现 #

如果数据量较大或需要更复杂的逻辑，脚本实现更灵活高效：

function generateGroupedReport() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet1 = ss.getSheetByName("Sheet1");
  const sheet2 = ss.getSheetByName("Sheet2");
  const sheet3 = ss.getSheetByName("Sheet3");

  // 清空Sheet3原有内容
  sheet3.clearContents();

  // 获取Sheet1非空数据（从第3行开始）
  const sheet1Rows = sheet1.getRange("A3:B").getValues().filter(row => row[0] !== "");
  // 获取Sheet2的D:S列全量数据
  const sheet2Rows = sheet2.getRange("D:S").getValues();

  let outputData = [];

  sheet1Rows.forEach(([key, total]) => {
    // 添加当前组表头行
    outputData.push([key, "", "", "", "", "", ""]);

    // 筛选并排序Sheet2数据：J列包含key，按F列排序
    const filteredData = sheet2Rows.slice(1)
      .filter(row => row[6].includes(key)) // J列对应索引6
      .sort((a, b) => a[2].localeCompare(b[2])) // F列对应索引2，文本排序；数字排序用a[2]-b[2]
      .map(row => [row[0], row[2], row[4], row[6], row[10], row[12], row[14]]); // 提取D/F/H/J/N/P/R列

    // 拼接筛选结果
    outputData = outputData.concat(filteredData);

    // 添加表尾行
    outputData.push(["TOTAL", "", "", "", "", "", "", total]);

    // 添加空行分隔
    outputData.push(["", "", "", "", "", "", ""]);
  });

  // 将结果写入Sheet3
  if (outputData.length > 0) {
    sheet3.getRange(1, 1, outputData.length, outputData[0].length).setValues(outputData);
  }
}

使用方法 #

1. 打开Google Sheets，点击「扩展程序」→「Apps Script」
2. 粘贴上述代码，保存项目
3. 运行generateGroupedReport函数，首次运行需完成权限授权
4. 可设置触发器（如数据更新时自动运行）实现自动化

脚本优势 #

• 处理大规模数据时性能优于公式
• 支持自定义格式、特殊字符处理等复杂逻辑
• 无公式嵌套层数限制

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