Google表格将单元格累加在不断变化的表格上 [英] Google Sheets Summing a cell over a changing number of sheets

问题描述

我正在尝试将电子表格从Excel转换为Google表格. 除以下公式外，大多数公式都按预期工作:

I am trying to convert a Spreadsheet from Excel to Google Sheets. Most formulas have worked as expected, with the exception of this formula:

=SUM('J Slater:Job Sheet'!C6)

我用它来求和一个工作表范围内的特定单元格，此公式允许在范围之间添加工作表，也可以将求和单元格添加到求和单元格中，而无需对公式进行任何更改.我找不到在Google表格中复制它的方法.

I am using it to sum a specific cell over a range of sheets this formula allows sheets to be added between the range an also be added to the sum cell without any change to the formula. I can't find a way of replicating this in Google Sheets.

推荐答案

您可以使用Google表格中的自定义功能来做到这一点:

You can do this with custom functions in Google sheets:

/**
 * @returns {Number} Sum of all values from startSheet to endSheet
 * @customfunction
 *
 * @param {String=} startSheet
 * @param {String=} endSheet
 * @param {String=} range
 */
function SUMSHEETS(startSheet = 'Sheet1', endSheet = 'Sheet2', range = 'A2') {
  if ([...arguments].some(arg => typeof arg !== 'string' || arg === ''))
    throw new Error('All arguments must be non-empty strings');
  const [addValue, getOutput] = (() => {
    let output = 0;
    return [
      sh => {
        output += sh.getRange(range).getValue();
      },
      () => output,
    ];
  })();
  let toggle = 0;
  const end = SpreadsheetApp.getActive()
    .getSheets()
    .some(sheet => {
      if (toggle) addValue(sheet);
      switch (sheet.getName()) {
        case startSheet:
          toggle = 1;
          addValue(sheet);
          break;
        case endSheet:
          return true;
      }
    });
  if (!toggle || !end) throw new Error('Sheet(s) not found!');
  return getOutput();
}

用法:

=SUMSHEETS("J Slater","Job Sheet","C6")

这篇关于Google表格将单元格累加在不断变化的表格上的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！