使用程序化查找在Google工作表中合并多个范围 [英] Union multiple ranges in google sheets with programmatic lookup

问题描述

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

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")

我不知道是否有可能(我一直在搜索1个多小时)将我的工作表名称查找与联合操作结合起来，或者是否需要在Google脚本中编写一些内容来请允许我参加工会.

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.

编辑

示例表

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

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

最新"工作表显示了已命名范围WORKSHEET_NAMES，该范围正使用INDIRECT从字符串中选择范围.

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

推荐答案

没有脚本，您可以在 C1 中构建公式生成器，例如:

without scripts you can build a formula generator in C1 like:

=ARRAYFORMULA({""; "={"&TEXTJOIN("; ", 1, 
 IF(A1:A="",,"QUERY("&A1:A&"!A2:C, ""where B != ''"")"))&"}"})

然后在需要的地方复制粘贴 C2

and then copy-paste C2 wherever you need

要跳过重新复制粘贴，可以使用脚本:

to skip re-copy-pasting you can use script:

function onEdit() { 
var sheet = SpreadsheetApp.getActive().getSheetByName("Contants");  
var src = sheet.getRange("C2");   // The cell which holds the formula
var str = src.getValue();
var cell = sheet.getRange("C10");  // The cell where I want the results to be
cell.setFormula(str);
}

因此它将从 C2 复制生成的公式"字符串，并将其粘贴为 C10 作为真实公式，因此您要做的就是在工作表中键入 A 列中的名称，所有内容都会自动更新

so it will copy generated "formulaa"-string from C2 and it will paste it in C10 as true formula so all you need to do is type in sheet names in A column and everything gets auto-updated

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