Google Sheets动态查找：基于单元格值动态切换工作表名的实现

问题描述 #

我需要制作一个统一的查找公式，根据单元格B1（用户输入学科名称）动态切换引用的工作表。目前固定学科的公式如下：

=IFERROR(
         XLOOKUP('Chem count'!C18; FILTER('Chem tot'!$D:$D; 'Chem tot'!$H:$H = A$14); 
                  FILTER('Chem tot'!$F:$F; 'Chem tot'!$H:$H = A$14));
         IFERROR(
                 XLOOKUP('Chem count'!C18; FILTER('Chem tot'!$D:$D; 'Chem tot'!$H:$H = 
                         "exc"); FILTER('Chem tot'!$F:$F; 'Chem tot'!$H:$H = "exc"));
                 ""
                 )
        )

我尝试把'Chem tot'!$D:$D改成'"& B1 & " tot'!$D:$D但无法生效，想知道Google Sheets里有没有正确的公式实现方式，或者用Apps Script会不会更好？

公式实现方法（推荐） #

在Google Sheets中，动态引用工作表需要借助INDIRECT函数解析拼接的工作表路径，把静态引用替换为INDIRECT包裹的动态字符串即可，注意字符串拼接格式要正确：

修改后的完整公式如下：

=IFERROR(
         XLOOKUP(INDIRECT("'"&B1&" count'!C18"); 
                 FILTER(INDIRECT("'"&B1&" tot'!$D:$D"); INDIRECT("'"&B1&" tot'!$H:$H")=A$14); 
                 FILTER(INDIRECT("'"&B1&" tot'!$F:$F"); INDIRECT("'"&B1&" tot'!$H:$H")=A$14));
         IFERROR(
                 XLOOKUP(INDIRECT("'"&B1&" count'!C18"); 
                         FILTER(INDIRECT("'"&B1&" tot'!$D:$D"); INDIRECT("'"&B1&" tot'!$H:$H")="exc"); 
                         FILTER(INDIRECT("'"&B1&" tot'!$F:$F"); INDIRECT("'"&B1&" tot'!$H:$H")="exc"));
                 ""
                 )
        )

关键点说明 #

• INDIRECT("'"&B1&" tot'!$D:$D")：通过拼接B1的学科名称生成对应工作表区域引用，单引号用于兼容含空格或特殊字符的工作表名
• 所有原静态工作表引用（如'Chem count'!C18、'Chem tot'!$H:$H）都需替换为INDIRECT动态生成的引用

Apps Script 实现场景 #

如果需求更复杂（比如批量处理、动态创建工作表，或公式嵌套太深难以维护），可以用Apps Script编写自定义函数：

1. 打开Google Sheets，点击「扩展程序」→「Apps Script」
2. 粘贴以下代码：

function DYNAMIC_XLOOKUP(subject, lookupValue, matchCol, returnCol, criteria) {
  const countSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(`${subject} count`);
  const totSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(`${subject} tot`);
  
  if (!countSheet || !totSheet) return "";
  
  const matchData = totSheet.getRange(matchCol).getValues().flat();
  const returnData = totSheet.getRange(returnCol).getValues().flat();
  
  const filteredMatch = matchData.filter((_, idx) => matchData[idx] === criteria);
  const filteredReturn = returnData.filter((_, idx) => matchData[idx] === criteria);
  
  const result = filteredMatch.indexOf(lookupValue);
  return result !== -1 ? filteredReturn[result] : "";
}

3. 保存并运行一次完成授权，即可在单元格调用：

=IFERROR(DYNAMIC_XLOOKUP(B1; INDIRECT("'"&B1&" count'!C18"); "H:H"; "D:D"; A$14); IFERROR(DYNAMIC_XLOOKUP(B1; INDIRECT("'"&B1&" count'!C18"); "H:H"; "D:D"; "exc"); ""))

适用场景 #

• 公式嵌套层级过深，可读性差
• 需要添加额外逻辑判断（如工作表不存在的提示）
• 批量处理大量数据时，脚本性能更优

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