Google表格将单元格累加在不断变化的表格上 [英] Google Sheets Summing a cell over a changing number of sheets
本文介绍了Google表格将单元格累加在不断变化的表格上的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我正在尝试将电子表格从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屋!
查看全文