如何从Google Sheet所有标签页按工作表名和行号提取匹配数据？

当你有几十张标签页时，手动逐个指定工作表名称写QUERY公式效率极低，以下两种方法可以解决这个问题：

方法1：通过表名索引+动态范围拼接实现 #

这种方法适合需要灵活控制要包含的工作表的场景：

1. 创建工作表名称索引
   在空白工作表（建议命名为「索引表」）的A列列出所有标签页名称。如果不想手动输入，可运行以下Google Apps Script自动生成：

   function getSheetNames() {
     const ss = SpreadsheetApp.getActiveSpreadsheet();
     const sheets = ss.getSheets();
     const names = sheets.map(sheet => [sheet.getName()]);
     const targetSheet = ss.getSheetByName("索引表");
     targetSheet.getRange(2, 1, names.length, 1).setValues(names);
   }
   

   运行脚本后，所有表名会自动填充到「索引表」的A2及以下单元格。

2. 动态合并所有工作表数据并查询
   使用REDUCE函数循环合并所有工作表的数据范围，再用QUERY筛选：

   =QUERY(REDUCE({}, 索引表!A2:A, LAMBDA(acc, sheet, IF(sheet="", acc, {acc; INDIRECT(sheet&"!A:Z")}))), 
          "select * where Col[工作表名列序号] = '目标表名' and Col[行号列序号] = 目标行号", 
          1)
   

   • 把A:Z替换为你实际的数据列范围；
   • Col[工作表名列序号]和Col[行号列序号]要替换为对应数据在合并范围中的列位置（比如如果工作表名是你数据的第1列，就写Col1）；
   • 1表示数据包含表头，没有表头则改为0。

方法2：自定义函数自动获取全表数据 #

如果不需要手动控制包含的工作表，用自定义函数更省心：

1. 添加自定义函数
   打开Google Sheets的「扩展程序」→「Apps脚本」，粘贴以下代码并保存：

   function getAllSheetData() {
     const ss = SpreadsheetApp.getActiveSpreadsheet();
     const sheets = ss.getSheets();
     let allData = [];
     
     sheets.forEach(sheet => {
       // 跳过隐藏工作表（不需要的话可删除此行判断）
       if (sheet.isSheetHidden()) return;
       
       const data = sheet.getDataRange().getValues();
       // 给每一行添加工作表名称和行号列，方便后续筛选
       const labeledData = data.map((row, index) => {
         if (index === 0) {
           // 表头行添加新的表头
           return ["工作表名称", "行号"].concat(row);
         } else {
           // 数据行添加对应表名和行号
           return [sheet.getName(), index].concat(row);
         }
       });
       allData = allData.concat(labeledData);
     });
     
     // 移除重复的表头（保留第一个表头）
     const header = allData[0];
     return allData.filter((row, idx) => idx === 0 || JSON.stringify(row) !== JSON.stringify(header));
   }
   

2. 调用函数并查询
   在任意单元格输入以下公式，替换筛选条件即可：

   =QUERY(getAllSheetData(), "select * where Col1 = '目标工作表名' and Col2 = 目标行号", 1)
   

   这个函数会自动同步所有非隐藏工作表的数据，且每行都带有对应的工作表名称和行号，直接用QUERY筛选即可。

关键注意事项 #

• 确保所有工作表的核心数据列结构一致，否则合并后筛选会出现列不匹配的问题；
• 自定义函数首次运行需要授权，按照页面提示完成授权即可；
• 数据量过大时，方法1的性能会更稳定，方法2可以通过限制数据范围（比如只取A1:1000而非全表）来优化速度。

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