google-apps-script - 在动态添加的工作表中使用公式

Question

我正在尝试计算出一个公式来汇总不同工作表中特定行的值，如下所示:

Sheet 1
A   |  B
---------
bob |  3
foo | 14
bar |  7

Sheet 2
A   |  B
---------
bob |  2
foo |  1
bar |  9

但需要注意的是，新的“工作表 3”可以添加相关数据，并且会自动被提取。

在处理新的(正确命名的)工作表时，如何获取每张工作表中每一行的总和值？

Results
-------
bob |  5
foo | 15
bar | 16

我们可以假设行值都相同，例如在命名范围内，People = {bob, foo, bar}。

我的尝试是这样的:

={People,ARRAYFORMULA('Sheet 1'!B1:B3+'Sheet 2'!B1:B3)}

但对于新工作表，我必须通过添加手动更新公式

+'Sheet 3'!B1:B3

我试过使用 INDIRECT 动态生成工作表名称，但它不采用数组。我猜我可能不得不使用

SpreadsheetApp.getActiveSpreadsheet().getSheets()

在脚本中，但如果我可以将其作为公式来完成，那么向继承电子表格的人解释起来会更容易。

谢谢

Answer 1

对于您的问题，我有替代解决方案，使用的方法略有不同。我建议将所有数据拉到一个结果页面并在那里汇总。那么您就不需要大量的“求和”函数或脚本。

使用 indirect 您可以从每个工作表中提取信息，前提是数据位于每个工作表的相同单元格位置。如果那不可能，您还可以使用 vlookup 来提取数据。我已经分享了一个示例，说明我将如何使用您的号码执行此操作。

Sheet1 上 'bob' 值的语法 =iferror(indirect("'"&C$2&"'!"&"b3"),"")其中 C$2 是工作表名称(在本例中为 Sheet1)，B3 是 bob 在 Sheet1 上的值.然后，您可以跨列复制此公式并更新表格顶部的工作表名称。

https://docs.google.com/spreadsheets/d/15pB5CclseUetl5zSRPDOR9YA4u6_7TK8RB8CpSxqhnk/edit?usp=sharing