使用间接从多个工作表中提取查询 [英] Using indirect to pull from multiple sheets for a Query

问题描述

我在Google工作簿中有动态的工作表列表.我希望创建一个从这些不同工作表中提取的查询-尽管我经常添加和删除工作表.

I have a dynamic list of the sheets in a Google workbook. I am hoping to create a query that pulls from these different sheets - although I am often adding and deleting sheets.

现在我的查询看起来很不错，而且效果很好:

Right now my query looks like, and it works great:

=query({'1.17 invoice'!A25:N;'1.31 invoice'!A25:N;'2.1 invoice'!A25:N;'invoice 2.1 (2)'!A25:N},"SELECT Col1, Col2, Col3, Col7, Col12, Col13 where Col1 <> ''",0)

我可以创建一个单元格A1，其值是:'1.17发票'！A25:N;'1.31发票'！A25:N;'2.1发票'！A25:N;'发票2.1(2)'！ A25:N

I can create a cell A1 that has a value of: '1.17 invoice'!A25:N;'1.31 invoice'!A25:N;'2.1 invoice'!A25:N;'invoice 2.1 (2)'!A25:N

但是现在我必须将A1的结果复制并粘贴到查询公式中以便对其进行更新.当我尝试创建像这样的公式时:

But right now I have to copy and paste the results of A1 into the query formula in order for it to update. When I try to create a formula like:

=query({indirect(A1)},"SELECT Col1, Col2, Col3, Col7, Col12, Col13 where Col1 <> ''",0)

我只是得到一个错误，即间接结果不是有效的单元格范围".有没有什么好办法可以自动将工作表列表放入查询功能?

I just get an error that 'the results of the indirect are not a valid cell range'. Is there any good way to automate getting the list of sheets into the query function?

推荐答案

OP试图使数组范围更加灵活.原始公式:

The OP has tried to make the array range more flexible. Original formula:

=query({'1.17 invoice'!A25:N;
        '1.31 invoice'!A25:N;
         '2.1 invoice'!A25:N;
     'invoice 2.1 (2)'!A25:N},
 "SELECT Col1, Col2, Col3, Col7, Col12, Col13 where Col1 <> ''",0)

OP在单元格A1中输入了数组范围，并修改了公式:

The OP entered the array range in cell A1, and modified the formula:

A1 = '1.17 invoice'!A25:N;'1.31 invoice'!A25:N;'2.1 invoice'!A25:N;'invoice 2.1 (2)'!A25:N

修改后的公式:

=query({indirect(A1)},"SELECT Col1, Col2, Col3, Col7, Col12, Col13 where Col1 <> ''",0)

这不起作用，因为INDIRECT返回单个数组值，但是A1包含多个范围.但是，如果分别声明每个范围，该查询将起作用.

This doesn't work because INDIRECT returns a single array value, but A1 consists of several ranges. However, if each of the ranges is declared separately, the query will work.

布局:

A1 = '1.17 invoice'!A25:N    
A2 = '1.31 invoice'!A25:N    
A3 = '2.1 invoice'!A25:N    
A4 = 'invoice 2.1 (2)'!A25:N 

公式:

=query({indirect(A1);
        Indirect(A2);
        indirect(A3);
        Indirect(A4)},
 "SELECT Col1, Col2, Col3, Col7, Col12, Col13 where Col1 <> ''",0)

信用:
鲁本( https://webapps.stackexchange.com/a/88726/196152 )
Karl_S( https://webapps.stackexchange.com/a/104517/196152 )

Credit:
Ruben (https://webapps.stackexchange.com/a/88726/196152)
Karl_S (https://webapps.stackexchange.com/a/104517/196152)

这篇关于使用间接从多个工作表中提取查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！