如何在 Google Sheets 中创建多个工作表引用的 INDIRECT 数组字符串? [英] How to create INDIRECT array string of multiple sheet references in Google Sheets?
问题描述
我正在尝试使用查询来显示多个 Google 表格中的数据.我每周都会制作一个具有特定工作表名称的新工作表，例如01/13 周、01/06 周 等等.
以下是我的想法的来源以供参考:
<块引用>我有一个使用 COUNTA(INDIRECT("'" & A5 &"'!E4:E",true)
A5 是一个单元格，它连接日期和单词以复制工作表名称.
直到 B5<=today()
所以我可以将它设置为忘记它，并且工作表将继续随着时间的推移，给我我的每周数据并保持工作表干净直到本周到来.
长话短说，我有一个我使用的查询，它使用特定参数为我提供了我需要的所有数据，但我必须每周使用新的工作表名称手动更新数据语法数组.
=QUERY({'01/13 周'!A:P;'01/06 周'!A:P;'12/30 周'!A:P;'12 周/23'!A:P;'12/16 周'!A:P;'12/09 周'!A:P;'12/02 周'!A:P;'11/25 周'!A:P;'11/18 周'!A:P;'11/11 周'!A:P;'11/04 周'!A:P;'10/28 周'！A:P;'10/21 周'!A:P;'10/14 周'!A:P;'10/07 周'!A:P;'09/30 周'!A:P;'09/23 周'!A:P;'09/16 周'!A:P;'09/09 周'!A:P;'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;",),"")但是当我尝试将它输入到查询函数中时，它只返回连接的文本:
=QUERY(I1,"选择*")
'01/06 周'!A:P;'01/13 周'!A:P我尝试过使用和不使用大括号都没有成功.
我希望工作表能够刷新并查看它是正确的日期、填充新工作表名称并更新查询.
我需要帮助让 I1 工作.
当然，脚本可以更改为onOpen触发器或自定义名称从自定义菜单或按钮触发(但不能直接使用自定义函数作为公式)
I am attempting to use a query to display data off multiple Google Sheets. I make a new sheet every week that has a specific sheet name, e.g. Week of 01/13, Week of 01/06 and so forth.
The following is where my idea spawned from for reference:
I have a summary sheet that is using
COUNTA(INDIRECT("'" & A5 & "'!E4:E",true)A5 being a cell that concatenates a date and words to replicate the sheet names.
The row on the summary sheet does not populate until
B5<=today()So I am able to set it an forget it and the sheet will continue to give me my weekly data as the days progress and keeps the sheets clean until the week is upon us.
Long story short, I have a query that I use that gives me all the data I need with a specific parameter but I have to manually update the data syntax array with the new sheet names each week.
=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)
I would like to build a reference to an array that will auto-populate a concatenation based on the day.
Using the following code I can have the concatenate give me the array I need,
=if(H4<=today(),CONCATENATE("'",H$1,text(H4,"mm/dd"),"'!A:P;",),"")
but when I try to input it into the query function it just returns the concatenated text:
=QUERY(I1,"Select *")
'Week of 01/06'!A:P;'Week of 01/13'!A:P
I have tried with and without the curly brackets with no success.
I would like the sheet to be able to refresh and see that it is the correct day, the new sheet name is populated and the query gets updated.
I need help with making I1 work.
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)
这篇关于如何在 Google Sheets 中创建多个工作表引用的 INDIRECT 数组字符串?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
