对Google工作表工作簿中所选工作表的单元格求和

Question

我创建了一个用于跟踪门店间商品转移的工作簿。每个月的每一天都有页面。我想做一个总计页面，在一张单独的表格上统计所有的转账。（工作簿中还有其他我不想添加到总数中的页面。）

我正在尝试制作一个脚本，它将获取第1页到第31页上单元格A1的值，并将总和放在第32页的单元格A1中，同样地，对于每个其他单元格。

现在我正在使用一个名为“Custom Count and Sum”的google sheets附加组件，它可以让我像我想要的那样将所有页面加在一起，但是页面名称是作为字符串传递的，所以你不能在单元格之间复制和粘贴公式。我找到了一个解决方法，使用address（row（），column（），4，true）引用单元格位置，这样我就不必手动键入其中的每个单元格。现在，我正试图使其在不必重新输入公式的情况下更新值。我不能使用我安装的附加函数来实现这一点，因为据我所知，我需要传递另一个参数，但我不能在附加公式中实现这一点。我正在尝试写我自己的脚本，我已经尝试使用下面问题中的信息，但我没有任何成功。

Google Sheets和工作簿中所有工作表的值

任何帮助都将不胜感激

Answer 1

当使用=SUM_SHEETS（A1Annotation）时，我所做的工作（对于我需要的13行）是输入L1，L2，。。。，L13在A列中，该列将引用我的工作表中的单元格，我想用公式列（B）中的此函数求和。我用单元格A1替换了sum\u sheets（）中的L1部分（因为我刚刚在单元格A13中键入了L1）

示例：|列A|列B | |----------------------|---------------------| | L1|=SUM_SHEETS（A13）||L2|=SUM_SHEETS（A14）|
|...||
|L13|=SUM_SHEETS（A25）|

而不是：| A列| |--------------------| | |=汇总表（“L1”）|
|=汇总表（“L2”）| |……|=汇总表（“L13”）|

最后，为了在需要时刷新，我创建了一个按钮和一个宏来删除单元格中的数据A13:A25，然后将它们放回原来的位置。

function Refresh_Summary() {
  var spreadsheet = SpreadsheetApp.getActive();
  spreadsheet.getRange('A13:A25').activate();
  spreadsheet.getActiveRangeList().clear({contentsOnly: true, skipFilteredRows: true});
  spreadsheet.getRange('A13').activate();
  spreadsheet.getCurrentCell().setValue('L1');
  spreadsheet.getRange('A14').activate();
  spreadsheet.getCurrentCell().setValue('L2');
  spreadsheet.getRange('A15').activate();
  spreadsheet.getCurrentCell().setValue('L3');
  spreadsheet.getRange('A16').activate();
  spreadsheet.getCurrentCell().setValue('L4');
  spreadsheet.getRange('A17').activate();
  spreadsheet.getCurrentCell().setValue('L5');
  spreadsheet.getRange('A18').activate();
  spreadsheet.getCurrentCell().setValue('L6');
  spreadsheet.getRange('A19').activate();
  spreadsheet.getCurrentCell().setValue('L7');
  spreadsheet.getRange('A20').activate();
  spreadsheet.getCurrentCell().setValue('L8');
  spreadsheet.getRange('A21').activate();
  spreadsheet.getCurrentCell().setValue('L9');
  spreadsheet.getRange('A22').activate();
  spreadsheet.getCurrentCell().setValue('L10');
  spreadsheet.getRange('A23').activate();
  spreadsheet.getCurrentCell().setValue('L11');
  spreadsheet.getRange('A24').activate();
  spreadsheet.getCurrentCell().setValue('L12');
  spreadsheet.getRange('A25').activate();
  spreadsheet.getCurrentCell().setValue('L13');
};

Answer 2

一般来说，最好在一个选项卡中收集每天、每个月和所有年份的所有数据。每一项在它自己的行上都有一个日期，就像一个数据库。

将一个选项卡上的数据分解到不同的选项卡中进行求和/计数/分析要比将不同选项卡中的数据聚合到一个选项卡上容易得多。

Answer 3

让我们看看这个变通方法是否适合您。

在这种情况下，如果希望函数获取所有单元格和总和，则应尝试匹配要从中获取值的表。在您的例子中，您可以查找作为数字的所有值。

/**
 * Sum all the values in the cells/range of all the sheets that match the
 * regular expressión `(\d)+`
 *
 * @param {string} A1Notation - Range in A1Notation that would be sum for all the cells  
 */
function SUM_SHEETS(A1Notation){
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheets = ss.getSheets();

  var regEx = /\b\d+\b/; // The regEx that will match the sheets

  var result = 0;
  for(var i=0; i < sheets.length; i++){
    var sheet = sheets[i];

    // Check that your sheet is matching your regular Expression
    if( regEx.test(sheet.getName())){

      var range = sheet.getRange(A1Notation);
      var values = range.getValues();

      // Iterate through all the range from all the current sheet
      for(var j=0; j < values.length; j++){
        for(var k=0; k < values[j].length; k++){
          var value = values[j][k];

          // Only sum when the cell is a number
          if (typeof value == "number"){
            result += value;
          }
        }
      }
    }
  }

  return result;
}

考虑到输入参数是一个表示范围的字符串，因此您可以遍历工作表。

还要关心工作表，在这个解决方案中，我使用了一个regExp，它将求和所有的值，这是一个数字（只有数字）。