工作表中所有工作表的Google表格总和值 [英] Google Sheets Sum values from ALL sheets in a workbook

问题描述

因此，假设我有一个带有随机工作表名称的工作表工作簿，其中包括鲍勃"，萨利"，比利"，约翰"或不连续的类似名称.我还有另一张叫做总计"的表.如何将特定单元格中的值求和，将所有这些值都说成"H4"?具体来说，如果我在创建公式后添加了新工作表，而不必每次都更改公式?

So assuming I have a Sheets Workbook with random sheet names, "Bob", "Sally", "Billy", "John" or something similar that isn't sequential. And I have another sheet called "Totals". How can I sum the values from a particular cell, say "H4" from all of them? Specifically if I added new sheets after I created the formula without having to change the formula every time?

推荐答案

您可能要为此使用自定义函数.将此添加到脚本编辑器

You may want to use a custom function for that. Add this to the scripteditor

function sumCellAS(cell) {
var val = [];
SpreadsheetApp.getActive()
    .getSheets()
    .forEach(function (s) {
        val.push(Number(s.getRange(cell)
            .getValue()))
    });
return val.reduce(function (x, y) {
    return x + y;
    });
}

然后在电子表格中输入公式= sumCellAS("H2")，将要求和的单元格作为字符串传入.

Then in your spreadsheet enter the formula =sumCellAS("H2"), passing in the cell to be summed, as a string.

这篇关于工作表中所有工作表的Google表格总和值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！