Google Spreash表中查询中的动态工作表名称 [英] Dynamic Sheet Name in Query in Google Spreashsheet

问题描述

在Google电子表格中，我想查询另一张表格中的数据，但问题在于表格的名称存在于单元格中。那么在QUERY函数中有一种方法可以动态提到表单名称。基本上我试图做这样的事情，但使用动态表名：

= QUERY（'2012'！A2：F;select C ，sum（F）where A ='December'group by C order by sum（F）desc）

我试图做到这一点但我得到解析错误：

  = QUERY（INDIRECT（Overview！L5）！A2：F;select C， sum（F）where A ='December'group by C order by sum（F）desc）
  

其中Overview！L5是要查询的表单名称的单元格。我也尝试连接间接引号，但也没有工作。

我认为这是非常明显的，我试图从查询，即获得值的总和单元格按其他单元格中的值分组。

解决方案

INDIRECT看起来是问题。
试试像这样：

  = query（INDIRECT（A1&！A5：A10），select Col1 ）
  

即如果单元格A1包含食物，则上述内容与以下内容相同：

  = query（food！A5：A10，select A ）
  

和以下相同：

  = query（INDIRECT（food！A5：A10），select *）
  

**注意：间接使用Col1等而不是A，因为它不通过大写字母。

另外。谷歌组织论坛可能是寻找电子表格公式答案的好地方。 productforums.google.com/forum/#！categories / docs / spreadsheets

In Google spreadsheet I want to query data in another sheet but the problem is that the name of sheet is present in a cell. So is there a way in QUERY function to dynamically mention sheet name. Basically I am trying to do something like this but with dynamic sheet name:

=QUERY('2012'!A2:F;"select C, sum(F) where A='December' group by C order by sum(F) desc")

I tried to do this but I get Parse Error:

=QUERY(INDIRECT("Overview!L5")!A2:F;"select C, sum(F) where A='December' group by C order by sum(F) desc")

In which Overview!L5 is the cell with sheet name to query. I also tried to concatenate quotes around INDIRECT but that didnt work either.

I think it is quite evident what I am trying to do from the query i.e. get sum of values in cells grouped by values in other cells.

解决方案

the INDIRECT looks to be the problem. Try like this:

=query(INDIRECT(A1&"!A5:A10"),"select Col1")

i.e. if Cell A1 contains "food" the above is the same as:

=query(food!A5:A10,"select A")

and the same as:

=query(INDIRECT("food!A5:A10"),"select *")

**Note: the indirect uses "Col1" etc and not "A" because it does not pass the col letters.

Also ... The google groups forum might be a good place to look for spreadsheet formula answers. productforums.google.com/forum/#!categories/docs/spreadsheets

这篇关于Google Spreash表中查询中的动态工作表名称的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！