使用程序化查找联合谷歌表格中的多个范围 [英] Union multiple ranges in google sheets with programmatic lookup

问题描述

我有一个谷歌工作表，其中包含动态变化的工作表数量.我希望能够自动合并包含在具有工作表名称的命名范围中的所有工作表.

我有一个手动公式，可以将所有数据合并到一个工作表中.我想根据命名范围进行计算.

联合公式={QUERY('Sheet1'!A2:L, "select * where A != ''");QUERY('Sheet2'!A2:L, "select * where A != ''")}

我在另一个工作表中也有一个命名范围，其中包含所有工作表名称

命名范围WORKSHEET_NAMES = {Sheet1,Sheet2,Sheet3)

我之前也曾使用它从工作表中提取数据

Col A - 这会复制 A 列中工作表名称的范围=QUERY(WORKSHEET_NAMES, "选择 A，其中 A != ''")Col B-Z - 这会查找工作表名称并从字符串中提取范围=QUERY(indirect($A2&"!$A$2:$N"), "select * order by B desc limit 1")

我不知道是否有可能(我已经搜索了 1 个多小时)将我的工作表名称查找与我的联合操作结合起来，或者我是否需要求助于在谷歌脚本中写一些东西来允许我做工会.

编辑

示例表

联合"表显示了所需的输出，但它是一个手动公式，不是从命名范围 WORKSHEET_NAMES 生成的

最新"工作表显示命名范围 WORKSHEET_NAMES 正在使用 INDIRECT 从字符串中选择一个范围.

解决方案

无需 Google App Script 的解决方案.

要求:

• 3 个独立的辅助细胞
• 1 个辅助列，每次您需要手动向下拖动添加一个新的工作表名称，或者您可以将其向下拖动 1,000 次然后这对接下来的 1,000 张有好处......

以您的剪贴簿为例，您在 A 列中有一个列表.

在单元格 B1 中放置 =arrayformula(if(len("'"&A1&"'!A2:C")<1,"",query(transpose(替代(查询(替代(间接('"&A1&'！A2:C")，"，_")，选择*其中Col1不为空"，counta(array_constrain(indirect(')"&A1&"'!A2:C"),99^99,1)))," ","\")),"select * where Col1 is not null",99^99))) 将这个公式向下拖到你需要的工作表名称，你可以超越

在单元格 C1 中输入 =arrayformula(if(len(B:B)<1,"",split(B:B," ")))

在单元格 F1 中放置 =arrayformula(query(transpose(substitute(query({C:E},"select * where Col1 is not null",counta(array_constrain(A:A,99^99,1)))," ","\")),"select *", 99^99))

在单元格 F2 中放置 =arrayformula(substitute(transpose(split(transpose(split(F1," ")),"\")),"_"," "))

现在，在单元格 F2 中，您将在添加工作表名称时动态更新加入的报表.

I have a google sheet with a dynamically changing number of worksheets within it. I'd like to be able to automatically union all worksheets that included in a named range that has the worksheet names.

I have a manual formula that is working and will union all data into a single worksheet. I'd like to make this computed from the named range.

UNION FORMULA

={QUERY('Sheet1'!A2:L, "select * where A != ''");QUERY('Sheet2'!A2:L, "select * where A != ''")}

I also have a named range in another sheet which has all the worksheet names

NAMED RANGE

WORKSHEET_NAMES = {Sheet1,Sheet2,Sheet3)

I also have previously used this to pull data from the worksheets using

Col A - this duplicates the range of worksheet names in column A
=QUERY(WORKSHEET_NAMES, "select A where A != ''")

Col B-Z - this looks up the worksheet name and pulls in the range from a string
=QUERY(indirect($A2&"!$A$2:$N"), "select * order by B desc limit 1")

I don't know if it's possible to (I've been searching for 1+ hours now) to combine my worksheet name lookup with my union operation, or if I'll need to resort to writing something in google scripts to allow me to do the union.

EDIT

Example Sheet

The "Union" sheet shows the desired output, but is a manual formula and not generated from the named range WORKSHEET_NAMES

The "Latest" sheet show the named range WORKSHEET_NAMES being used using an INDIRECT to pick a range from a string.

解决方案

Solution without Google App Script.

Requirements:

• 3 individual helper cells
• 1 helper column you will need to manually drag down each time you add a new sheet name, or you can just drag it down 1,000 times and that will be good for the next 1,000 sheets...

Using your scrap sheet as an example, you have a list in column A.

In cell B1 put =arrayformula(if(len("'"&A1&"'!A2:C")<1,"",query(transpose(substitute(query(substitute(indirect("'"&A1&"'!A2:C")," ","_"),"Select * where Col1 is not null",counta(array_constrain(indirect("'"&A1&"'!A2:C"),99^99,1)))," ","\")),"select * where Col1 is not null",99^99))) DRAG THIS FORMULA DOWN AS FAR AS YOU NEED SHEET NAMES YOU CAN OVERSHOOT

In cell C1 put =arrayformula(if(len(B:B)<1,"",split(B:B," ")))

In cell F1 put =arrayformula(query(transpose(substitute(query({C:E},"select * where Col1 is not null",counta(array_constrain(A:A,99^99,1)))," ","\")),"select *", 99^99))

In cell F2 put =arrayformula(substitute(transpose(split(transpose(split(F1," ")),"\")),"_"," "))

Now in cell F2 you will have your joined report dynamically updating as you add sheet names.

这篇关于使用程序化查找联合谷歌表格中的多个范围的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！