如果在Google表格中跨多个表格求和

Question

我需要计算每行不同表上1列的总和。这是我的示例文件-https://docs.google.com/spreadsheets/d/11KWjso_sLdHI-NWBk0Rtp-EnGfMyU6a3G143C1Tl6lE/edit?usp=sharing表1上的С列应计算所有页面上T列的总和。我正在尝试使用以下公式，但它不起作用：＝SUMPRODUCT(SUMIFS(INDIRECT("'"&TO_TEXT(规则！$D$1:$1(&quot；'"amp"T： T"(，INDIRECT("&TO_TEXT(规则！$D$1:$1(&quot；'"amp"A： A"(，A2，INDIRECT("'"&TO_TEXT(规则！$D$1:$1(&quot；'"amp"B： B〃(，B2((它只计算第一页的总和。请帮忙。

Answer 1

此代码将帮助您

function onEdit(e) {
let source = e.source,
range  = e.range,
col    = range.columnStart,
row    = range.rowEnd,
sheet  = range.getSheet();
if (sheet.getName() == 'Rules' && row == 1 && col>2){
let sheetNames = sheet.getRange('1:1').getDisplayValues()[0].filter(String).slice(3),
formula = sheetNames.reduce((r,element)=>{
return r += `SUMIF('` + element + `'!A:A&'` + element + `'!B:B,A2:A&B2:B,'` + element + `'!T:T)+`
},`={"SUM";ArrayFormula(if(A2:A<>"",`).replace(/+$/,',))}');
SpreadsheetApp.getActive().getSheetByName('Sheet1').getRange('C1').setFormula(formula)
}
}

每次更改Rules上1:1行的数据时，该脚本都会重写Sheet1上C1单元格中的公式

已添加

以便将公式更改为您想要的公式={"SUM";ArrayFormula( if(A2:A<>"",ifs((SUMIF('5/31/2021'!A:A&'5/31/2021'!B:B,A2:A&B2:B,'5/31/2021'!T:T)+SUMIF('6/16/2021'!A:A&'6/16/2021'!B:B,A2:A&B2:B,'6/16/2021'!T:T))=2,"New Full Time",SUMIF('5/31/2021'!A:A&'5/31/2021'!B:B,A2:A&B2:B,'5/31/2021'!T:T)+SUMIF('6/16/2021'!A:A&'6/16/2021'!B:B,A2:A&B2:B,'6/16/2021'!T:T))<2,"Part Time",(SUMIF('5/31/2021'!A:A&'5/31/2021'!B:B,A2:A&B2:B,'5/31/2021'!T:T)+SUMIF('6/16/2021'!A:A&'6/16/2021'!B:B,A2:A&B2:B,'6/16/2021'!T:T))>2,"Full Time"),))}将代码的一部分更改为该

...
subFormula = sheetNames.reduce((r,element)=>{
return r += `SUMIF('` + element + `'!A:A&'` + element + `'!B:B,A2:A&B2:B,'` + element + `'!T:T)+`
},`(`).replace(/+$/,')'),
formula = '={"SUM";ArrayFormula(if(A2:A<>"",ifs(' + 
subFormula + '=2,"New Full Time",' + subFormula + '<2,"Part Time",' + subFormula + '>2,"Full Time"),))}'
...

但我认为下一个公式会更容易，运行速度更快={"SUM";ArrayFormula( if(A2:A<>"",SWITCH((SUMIF('5/31/2021'!A:A&'5/31/2021'!B:B,A2:A&B2:B,'5/31/2021'!T:T)+SUMIF('6/16/2021'!A:A&'6/16/2021'!B:B,A2:A&B2:B,'6/16/2021'!T:T)),0,"Part Time",1,"Part Time",2,"New Full Time","Full Time"),))}将其插入代码中，更改以下

...
formula = sheetNames.reduce((r,element)=>{
return r += `SUMIF('` + element + `'!A:A&'` + element + `'!B:B,A2:A&B2:B,'` + element + `'!T:T)+`
},`={"SUM";ArrayFormula(if(A2:A<>"",SWITCH((`).replace(/+$/,'),0,"Part Time",1,"Part Time",2,"New Full Time","Full Time"),))}');
...

但更快的是可以写入下一列D的常数公式

={"-Header-";ArrayFormula(if(C2:C<>"",ifs(C2:C<2,"Part Time",C2:C=2,"New Full Time",C2:C>2,"Full Time"),))}