如何在 Google 表格中創建多個工作表引用的 INDIRECT 數組字符串？ (How to create INDIRECT array string of multiple sheet references in Google Sheets?)

問題描述

如何在 Google 表格中創建多個工作表引用的 INDIRECT 數組字符串？ (How to create INDIRECT array string of multiple sheet references in Google Sheets?)

我正在嘗試使用查詢來顯示來自多個 Google 表格的數據。我每週製作一張具有特定工作表名稱的新工作表，例如 Week of 01/13、Week of 01/06 等等。

以下是我的想法的參考來源：

我有一個使用 COUNTA(INDIRECT("'" & A5 & 的摘要表; "'!E4:E",true)

A5 是一個單元格，它連接日期和單詞以復制工作表名稱。

摘要上的行工作表在 B5<=today()

之前不會填充 但是當我嘗試將其輸入到查詢函數中時，它只會返回連接的文本：

=QUERY({'Week of 01/13'!A:P;'Week of 01/06'!A:P;'Week of 12/30'!A:P;'Week of 12/23'!A:P;'WEEK OF 12/16'!A:P;'WEEK OF 12/09'!A:P;'WEEK OF 12/02'!A:P;'WEEK OF 11/25'!A:P;'WEEK OF 11/18'!A:P;'WEEK OF 11/11'!A:P;'WEEK OF 11/04'!A:P;'WEEK OF 10/28'!A:P;'WEEK OF 10/21'!A:P;'WEEK OF 10/14'!A:P;'WEEK OF 10/07'!A:P;'WEEK OF 09/30'!A:P;'WEEK OF 09/23'!A:P;'WEEK OF 09/16'!A:P;'WEEK OF 09/09'!A:P;'WEEK OF 09/02'!A:P},
 "Select * where Col11 = 'RD' order by Col2 desc",0)

=if(H4<=today(),CONCATENATE("'",H$1,text(H4,"mm/dd"),"'!A:P;",),"")

我嘗試使用和不使用大括號都沒有成功。

我希望工作表能夠刷新並查看它是正確的日期，填充新的工作表名稱並更新查詢。

我需要幫助以使 I1 工作。

鏈接到測試查詢表

p>我嘗試過使用和不使用大括號都沒有成功。

我希望工作表能夠刷新並看到它是正確的一天，新的工作表名稱被填充並且查詢得到更新。

我需要幫助以使 I1 工作。

鏈接到測試查詢表

p>我嘗試過使用和不使用大括號都沒有成功。

我希望工作表能夠刷新並看到它是正確的一天，新的工作表名稱被填充並且查詢得到更新。

我需要幫助以使 I1 工作。

鏈接到測試查詢表

---

參考解法

方法 1:

dudes who copy‑pasted INDIRECT function into Google Sheets completely failed to understand the potential of it and therefore they made zero effort to improve upon it and cover the obvious logic which is crucial in this age of arrays.

in other words, INDIRECT can't intake more than one array:

=INDIRECT("Sheet1!A:B"; "Sheet2!A:B")

nor convert an arrayed string into active reference, which means that any attempt of concatenation is also futile:

=INDIRECT(MasterSheet!A1:A10)
————————————————————————————————————————————————————————————————————————————————————
=INDIRECT("{Sheet1!A:B; Sheet2!A:B}")
————————————————————————————————————————————————————————————————————————————————————
={INDIRECT("Sheet1!A:B"; "Sheet2!A:B")}
————————————————————————————————————————————————————————————————————————————————————
=INDIRECT("{INDIRECT("Sheet1!A:B"); INDIRECT("Sheet2!A:B")}")

the only possible way is to use INDIRECT for each end every range like:

={INDIRECT("Sheet1!A:B"); INDIRECT("Sheet2!A:B")}

which means that the best you can do is to pre‑program your array like this if only part of the sheets/tabs is existant (let's have a scenario where only 2 sheets are created from a total of 4):

=QUERY(
 {IFERROR(INDIRECT("Sheet1!A1:B5"), {"",""}); 
  IFERROR(INDIRECT("Sheet2!A1:B5"), {"",""}); 
  IFERROR(INDIRECT("Sheet3!A1:B5"), {"",""}); 
  IFERROR(INDIRECT("Sheet4!A1:B5"), {"",""})}, 
 "where Col1 is not null", 0)

so, even if sheet names are predictable (which not always are) to pre‑program 100+ sheets like this would be painful (even if there are various sneaky ways how to write such formula under 30 seconds)

---

an alternative would be to use a script to convert string and inject it as the formula

A1 would be formula that treates a string that looks like real formula:

=ARRAYFORMULA("=QUERY({"&TEXTJOIN("; ", 1, 
 IF(A3:A<>"", "'Week of "&LEFT(A3:A, 5)&"'!A1:D5", ))&
 "}, ""where Col1 is not null"", 1)")

further populating of A6:A will expand the string automatically

then this script will take the string from A1 cell and it will paste it as valid formula into C5 cell:

function onEdit() { 
var sheet = SpreadsheetApp.getActive().getSheetByName('Master Sheet');  
var src = sheet.getRange("A1");
var str = src.getValue(); 
var cell = sheet.getRange("C5"); 
cell.setFormula(str);
}

of course, the script can be changed to onOpen trigger or with custom name triggered from the custom menu or via button (however it's not possible to use the custom function as formula directly)

方法 2:

If you're trying to update the data your query is looking at and you're feeding it a string, you need to put that string within the indirect() function. That will interpret your string as a data reference and point your query() in the right direction.

So for this you'd probably have

=QUERY(INDIRECT(I1),"Select *")

(by s0m3thlng、player0、Luke Allpress)

參考文件

1. How to create INDIRECT array string of multiple sheet references in Google Sheets? (CC BY‑SA 2.5/3.0/4.0)