如何在Google SpreadSheets上动态引用多个工作表? [英] How do I dynamically reference multiple sheets on Google SpreadSheets?

问题描述

我使用Google SpreadSheet插件制作Google Analytics(分析)报告.我的问题是，对于每个查询，此加载项都会创建一个不同的工作表.我经常有大约50个不同的工作表，这些数据必须汇总到一个工作表中.例如:

I use a Google SpreadSheet add-on to make Google Analytics Reports. My problem is that for each query this add-on creates a different sheet. I often have around 50 different sheet with data that must be compiled into a single sheet. For example:

我可能有3个工作表，分别是:有机数据"，直接数据"和其他数据"

I might have 3 sheets named: "Organic Data", "Direct Data" and "Other Data"

每个工作表都有一组不同的数据，我希望将它们编译成第4个工作表中的单个表，称为报表".该表的第一列数据来自有机数据"表，第二列数据来自直接数据"表，第三列数据来自其他数据"表.

Each sheet has a different set of data that I want to compile into a single table in a 4th sheet called "Report". This table will have in its first column data from the "Organic Data" sheet, in its second column data from the "Direct Data" sheet and in its third column data from the "Other Data" sheet.

我这样做的方法是一一引用单元格.因此，在报表"表中，我将编写= Organic Data！B4，然后拖动以填充该列的其他单元格，然后对其他表执行此操作，以填充报表"表的其他列.

The way I´m doing this is by referencing the cells one by one. So in the "Report" sheet I will write =Organic Data!B4 and then drag to fill the other cells of the column, and then I would do this for the other sheets to fill up the other columns of the "Report" sheet.

问题是，正如我已经说过的，我经常有50多种不同的图纸，引用它们会变得很困难.

The problem is that, as I´ve said, I often have more than 50 different sheets and referencing them can get quite hard.

我想动态地做到这一点.在单元格上具有工作表的名称，并使用该名称来引用单元格.例如:

I would like to do this dynamically. Have the names of the sheets on a cell and use that to reference the cells. For example:

在报告"表中:

A1 =有机数据"，B1 =直接数据"，C1 =其他数据"

A1 = "Organic Data", B1 = "Direct Data", C1 = "Other Data"

在"Report！A2"单元格中，我尝试编写= CONCAT("="，A1，！"，"B4")，希望在此单元格中获得有机数据！B4"单元格的值，但是我得到的是字符串"= Organic Data！B4"，而不是"Organic Data"工作表中单元格的值.

In cell "Report!A2" I´ve tried writing =CONCAT("=",A1,"!","B4") hoping to get in this cell the value of the cell "Organic Data!B4", but instead I get the string "=Organic Data!B4, and not the value of the cell in "Organic Data" sheet.

我制作了一个自定义公式来解决此问题:

I`ve made a custom formula to solve this problem:

function referenceSheet(sheetName, cellInput) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var cell = ""
  var cell = sheetName+'!'+cellInput;

  var value = ss.getRange(cell).getValue();

  return value;
}

效果很好.在上面的示例中，为了将单元格"Organic Data！B4"的值放入单元格"Report！A2"，我需要在"Report！A2"上编写以下内容:

It works quite well. In my example above to get the value of the cell "Organic Data!B4" into the cell "Report!A2", I need to write on "Report!A2" the following:

"=referenceSheet(A1, cell("address",B4))" 

，它将返回单元格"Organic Data！B4"的值.

and it will return the value of the cell "Organic Data!B4".

我的自定义公式确实存在一些问题.通常，自定义公式不会刷新onChange，因此，如果我更改有机数据！B4"的值，报告！A2"将不会更改.

I do have some issues with this custom formula that I´ve made. Mainly, custom formulas don´t refresh onChange, so if I change the value of "Organic Data!B4", "Report!A2" will not change.

我想知道是否存在一个与我的自定义公式相似的本地公式?

如果没有，当我更改其他单元格中的日期时，使自定义公式刷新的最佳方法是什么?

If not, what´s the best way to make my custom formula to refresh as I change the date from other cells?

非常感谢！

推荐答案

我已经在Google论坛上发布了相同的问题，并获得了不错的答案.

I´ve posted the same question on Google Forums and got a nice answer.

有一个本地公式调用= INDIRECT

There is a native formula call =INDIRECT

在我的例子中，它的工作方式如下:

For my exemple it works like this:

=INDIRECT(CONCATENATE(A$1,"!",cell("address",$B4)),true)

这篇关于如何在Google SpreadSheets上动态引用多个工作表?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！