使用查询组合来自不断变化的选项卡列表的数据 [英] Use query to combine data from ever-changing tab list
问题描述
我正在构建一个工作表，它将从其他选项卡中获取导入的数据并将其合并到一个汇总表中，供我在其他地方使用.问题是，tabs每个月都会导入，所以很快就会有几十个，我不想每个月都编辑查询功能.它们都具有相同的命名格式，但我想知道是否有人知道如何使用我可以从另一个单元格中提取的工作表名称的变量来制定查询.例如，我可以将选项卡名称导入另一行，并让查询从该行中提取工作表名称，但我不熟悉如何执行此操作.这是我在类似工作表中使用的查询公式示例:
=QUERY({'2019 TEAM SALES'!A2:ZY; '2020 TEAM SALES'!A2:ZY}, "select * where Col1 is not null",1)有了这个，一年更新一次没什么大不了的.但是这个新表是每月一次，所以在短短几个月内就会变得笨拙.有没有办法使用查询通过单元格信息获取工作表名称?就像在第 1 行中，我将工作表名称与范围连接起来，以便 A1 包含'2019 TEAM SALES'！A2:ZY"和 B1 中的下一个等等，然后查询公式为:
=QUERY(A1:ZZ1, "select * where Col1 is not null",1)这样，它将始终拉入所有需要合并的当前选项卡.顺便说一句，我愿意使用其他公式甚至脚本来完成这项工作，因为这是我追求的结果，而不是方法.
我看到了很多类似于
<小时>将其转换为查询范围:
=ARRAYFORMULA("INDIRECT("""&UNIQUE(TEXT(ROW(INDIRECT(DATEVALUE("2020-4-1")&":"&DATEVALUE(TODAY()+90))), "yyyy mmmm"))&"!A:C"")")为了避免还不存在的工作表可能出现的错误:
=ARRAYFORMULA("IFERROR(INDIRECT("""&UNIQUE(TEXT(ROW(INDIRECT(INDIRECT)DATEVALUE("2020-4-1")&":"&DATEVALUE(TODAY()+90))), "yyyy mmmm"))&"!A:C""), {"&TEXTJOIN(",", 1, SPLIT(REPT("""""♥", COLUMNS(A:C)), "♥"))&"})")接下来我们加入它以创建一个用于查询的单一引用:
=ARRAYFORMULA("{"&TEXTJOIN(";", 1,"IFERROR(INDIRECT("""&UNIQUE(TEXT(ROW(INDIRECT(DATEVALUE("2020-4-1")&":"&DATEVALUE(TODAY()+90))), "yyyy mmmm"))&"!A:C""), {"&TEXTJOIN(",", 1, SPLIT(REPT("""""♥", COLUMNS(A:C)), "♥"))&"})")&"}")然后我们可以构建我们的查询:
={"";ARRAYFORMULA("=QUERY({"&TEXTJOIN(";", 1,"IFERROR(INDIRECT("""&UNIQUE(TEXT(ROW(INDIRECT(DATEVALUE("2020-4-1")&":"&DATEVALUE(TODAY()+90))), "yyyy mmmm"))&"!A:C""), {"&TEXTJOIN(",", 1, SPLIT(REPT("""""♥", COLUMNS(A:C)), "♥"))&"})")&"}, ""选择 Col1,Col2，其中 Col3 不为空"", 0)")}通过这种方式，我们将查询公式生成为 G2 中的字符串，因此下一步是每个月手动将其复制粘贴到您需要的位置，或者您可以使用脚本自动复制 G2 的内容并自动粘贴它任何更改都需要它的地方，因此您不再需要触摸它
function onEdit() {var sheet = SpreadsheetApp.getActive().getSheetByName('Sheet1');//工作表名称var src = sheet.getRange("G2");//保存公式的单元格var str = src.getValue();var cell = sheet.getRange("G10");//我想要结果的单元格cell.setFormula(str);}电子表格演示
I am building a Sheet that will be taking imported data from other tabs and combining it in a summary sheet, for me to use elsewhere. The problem is, the tabs will be imported monthly, so there will be dozens of them very quickly, and I don't want to edit the query function every month. They will all have the same naming format, but I'm wondering if anyone has an idea about how to formulate the query with variables for the sheet name that I can pull from another cell. For example, I could import the tab names into another row, and have the query pull the sheet names from that row, but I am not familiar with how to do this. Here is an example of the query formula I'm using in a similar sheet:
=QUERY({'2019 TEAM SALES'!A2:ZY; '2020 TEAM SALES'!A2:ZY}, "select * Where Col1 is not null",1)
With this one, updated once a year isn't a big deal. But this new sheet will be monthly, so it will be unwieldy in just a few months. Is there a way using query to take the sheet names via cell info? Like if in row 1, I concatanate the sheet names with the range, so that A1 contains "'2019 TEAM SALES'!A2:ZY" and the next in B1 and so on, and then the query formula is:
=QUERY(A1:ZZ1, "select * Where Col1 is not null",1)
This way, it will always pull in all the current tabs necessary to combine. Incidentally, I'm open to using other formulas or even a script to get this done, as it's the result I'm after, not the method.
I have seen a lot of answers similar to what's here, but this still forces extremely long formulas that I don't think are very efficient, given that this sheet will eventually have 50 or more tabs in it. Thanks for any insights!
to generate list of sheets from start date to today's date +3 months in advance:
=ARRAYFORMULA(UNIQUE(TEXT(ROW(INDIRECT(
DATEVALUE("2020-4-1")&":"&
DATEVALUE(TODAY()+90))), "yyyy mmmm")))
converting it into ranges for query:
=ARRAYFORMULA("INDIRECT("""&UNIQUE(TEXT(ROW(INDIRECT(
DATEVALUE("2020-4-1")&":"&
DATEVALUE(TODAY()+90))), "yyyy mmmm"))&"!A:C"")")
to avoid possible errors for sheets that do not exist yet:
=ARRAYFORMULA("IFERROR(INDIRECT("""&UNIQUE(TEXT(ROW(INDIRECT(
DATEVALUE("2020-4-1")&":"&
DATEVALUE(TODAY()+90))), "yyyy mmmm"))&"!A:C""), {"&
TEXTJOIN(",", 1, SPLIT(REPT("""""♥", COLUMNS(A:C)), "♥"))&"})")
next we join it to create one single reference for query:
=ARRAYFORMULA("{"&TEXTJOIN("; ", 1,
"IFERROR(INDIRECT("""&UNIQUE(TEXT(ROW(INDIRECT(
DATEVALUE("2020-4-1")&":"&
DATEVALUE(TODAY()+90))), "yyyy mmmm"))&"!A:C""), {"&
TEXTJOIN(",", 1, SPLIT(REPT("""""♥", COLUMNS(A:C)), "♥"))&"})")&"}")
then we can build our query:
={""; ARRAYFORMULA("=QUERY({"&TEXTJOIN("; ", 1,
"IFERROR(INDIRECT("""&UNIQUE(TEXT(ROW(INDIRECT(
DATEVALUE("2020-4-1")&":"&
DATEVALUE(TODAY()+90))), "yyyy mmmm"))&"!A:C""), {"&
TEXTJOIN(",", 1, SPLIT(REPT("""""♥", COLUMNS(A:C)), "♥"))&"})")
&"}, ""select Col1,Col2 where Col3 is not null"", 0)")}
this way we generated our query formula as a string in G2 so the next step is either manually copy-paste this each month where you need it or you can use script which will auto-copy content of G2 and auto-paste it where you need it on any change so you dont need to touch it anymore
function onEdit() {
var sheet = SpreadsheetApp.getActive().getSheetByName('Sheet1'); // sheet name
var src = sheet.getRange("G2"); // cell which holds the formula
var str = src.getValue();
var cell = sheet.getRange("G10"); // cell where I want the results
cell.setFormula(str);
}
spreadsheet demo
这篇关于使用查询组合来自不断变化的选项卡列表的数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
