如何编写Google Sheets函数遍历工作表数组并汇总SUMIFS结果？

自定义函数实现多表条件求和累加 #

核心函数代码 #

function SUMACROSSSHEETS(formulaTemplate, sheetNames) {
  // 处理单元格引用类型的工作表名输入，提取非空值数组
  if (sheetNames && sheetNames.constructor.name === 'Range') {
    sheetNames = sheetNames.getValues().flat().filter(name => name !== '');
  }
  
  const totalSheetName = "Totals for categories 2023";
  let total = 0;
  
  sheetNames.forEach(sheetName => {
    // 替换公式模板：为无工作表前缀的范围添加当前表名，替换固定汇总表占位符
    let formula = formulaTemplate.replace(/([A-Z]+\d+:[A-Z]+\d+)/g, `${sheetName}!$1`)
                                 .replace(/absolute_sheet!/g, `${totalSheetName}!`);
    
    // 执行公式并累加结果，非数值结果按0处理
    const result = SpreadsheetApp.getActiveSpreadsheet().getRange(formula).getValue();
    total += isNaN(result) ? 0 : result;
  });
  
  return total;
}

使用说明 #

• 参数1（formulaTemplate）：传入求和公式模板，比如SUMIFS(G:G, A:A, absolute_sheet!B2)。规则：需要跨表计算的范围不要带工作表名，固定引用汇总表的部分用absolute_sheet!占位。
• 参数2（sheetNames）：支持两种输入方式：直接传入工作表名数组（比如getAllSheetNames("2023")），或者传入存放工作表名的单元格范围（比如L4:L）。

示例用法 #

在“Totals for categories 2023”的C2单元格中输入：

=SUMACROSSSHEETS("SUMIFS(G:G, A:A, absolute_sheet!B2)", getAllSheetNames("2023"))

如果工作表名已经列在L4到L15区域，也可以写：

=SUMACROSSSHEETS("SUMIFS(G:G, A:A, absolute_sheet!B2)", L4:L15)

扩展说明 #

• 兼容其他数值类公式：只要是返回单一数值的公式都可以用这个模板，比如统计类的COUNTIFS：

  =SUMACROSSSHEETS("COUNTIFS(A:A, absolute_sheet!B2)", getAllSheetNames("2023"))
  

• 自动过滤空值：如果传入的单元格范围里有空行，函数会自动跳过，只处理有效的工作表名。

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