Google表格：QUERY使用单元格内容作为SQL变量 [英] Google Sheets: QUERY using cell contents as SQL variables

问题描述

以下是我与之合作的电子表格： 总结表 ）

源数据

（同一张表格，低于输出）

< img src =https://i.stack.imgur.com/whRqo.pngalt =screenshot>

https://docs.google.com/spreadsheets/d/1-DrhZKYM2Kk4qDS1Koqz9eYy1L35JXIeyat61A2v0JQ/edit?usp=sharing

我使用 QUERY 函数来填充汇总表中的相应提要，并将数据从A24开始放入。

以下是公式I' m在单元格C6中使用（在整个汇总表中使用相似的公式） p>

  = QUERY（$ A $ 24：$ D $ 57，Select D Where B ='ENQ'and A ='2/27 / 14  -  Thu'）
  

获得正确的信息，但需要编辑公式这个问题不能用A ='2/27/14 - Thu'来快速填充单元格。

我试图设置它，以便：在接受数据之前，A中的

• 日期与ROW 2中的标题中的日期进行比较 li>
  
• B中的房间类型将与汇总表中每行的值A进行比较

是否可以写QUERY函数来将这些值作为变量来引用，而不是在我的原始函数中使用字面值？

您可以将公式引用到输出的A列中的索引，而不是像'ENQ'这样的固定字符串。例如，您可以在单元格C4中将公式更改为此：

  = QUERY（$ A $ 24：$ D，选择D其中B =''& $ A4&''和A ='2/27/14  -  Thu'）
 ^^^^^^^ 
  

符号（& ）用于连接字符串段。

请注意，由于源数据延伸到表单数据范围的底部，因此我们可以放弃指定最下面一行。 $ A $ 24：$ D 范围将包含所有行，因此它会自动调整为其他源数据。

li>

要比较日期，两个值都需要是日期。 2014年2月27日 - 星期四在源数据表中不被识别为日期，而是作为文本，即使您已将数字格式设置为最新。因此，请确保您将所有源数据更改为A列中的实际日期。

您仍然可以按自己喜欢的方式格式化日期 - 请参阅此屏幕截图。 A2的内容现在是适当的日期，2/27/2014，但格式设置为显示mm / dd / yy - DDD。只要 data 是日期，可以使用电子表格 TEXT 函数来构建查询，以解析yyyy-mm-dd中的日期格式。

使用源列中的日期，可以使用 todate（）标量函数来告诉 QUERY 将源中的日期转换为查询日期，并将日期关键字用于将以下文字字符串（来自C2）作为日期。

单元格C4的结果函数为：

  = QUERY （A）=日期'和文本（$ C $ 2，yyyy-MM-dd）和$ A $ 24：$ D，选择D其中B ='& $ A4& '）
 ^^^^^^ ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ ^^ 
  

同样，对于D4：

  = QUERY（$ A $ 24：$ D，选择C在哪里B ='& $ A4&'和todate（A）= da （$ C $ 2，yyyy-MM-dd）&'）
  

您可以改进E列中的计算，以处理出现在列C或D中的＃N / A 结果：

  = if（isna（C12 * D12），0，C12 * D12）
  

Here is what the spreadsheet I'm working with looks like:

Summary table

(top of sheet)

Source data

(same sheet, below output)

https://docs.google.com/spreadsheets/d/1-DrhZKYM2Kk4qDS1Koqz9eYy1L35JXIeyat61A2v0JQ/edit?usp=sharing

I'm using the QUERY function to populate the appropriate feeds in the summary table with the data starting at A24 needs to be placed into .

Here is the formula I'm using in cell C6 (similar formulas are used throughout the summary table):

=QUERY($A$24:$D$57, "Select D Where B='ENQ' and A='2/27/14 - Thu'")

This gets the right information, but the formula needs to be edited to be unique in each cell it's used in. The problem being unable to quickly populate the cells with "A='2/27/14 - Thu'" being too specific.

I was trying to set it up so that:

• date in A would compare with dates found on headers in ROW 2 before accepting data
• room type in B would compare with value from A in each row of the summary table

How can the QUERY function be written to refer to these values as variables, instead of using the literal strings in my original function?

解决方案

1. Instead of fixed strings such as 'ENQ', you can have your formula refer to the index in column A of your output. For example, you could change your formula to this, in cell C4:

   =QUERY($A$24:$D, "Select D Where B='" & $A4 & "' and A='2/27/14 - Thu'")
                                            ^^^^^^^
   

   The ampersand (&) is used to concatenate string segments.

   Note that since the source data extends to the bottom of the data range of the sheet, we can forego specifying the bottom row. The range $A$24:$D will take in all rows, so it will automatically adjust to additional source data.

2. To compare dates, both values need to be dates. "2/27/14 - Thu" is not recognized as a date in your source data sheet, but as text, even though you've set the numeric format to date. So make sure you change all your source data to have actual dates in column A.

   You can still have your dates formatted the way you like - see this screenshot. The content of A2 is now a proper date, "2/27/2014", but the format is set to display "mm/dd/yy - DDD". As long as the data is a date, the query can be built using the spreadsheet TEXT function to interpret the date in its yyyy-mm-dd format.

3. With dates in your source column, you can use todate() scalar function to tell QUERY to convert the date in the source to a query-date, and the date keyword to treat the following literal string (from C2) as a date.

   The resulting function for cell C4 is:

   =QUERY($A$24:$D , "Select D Where B='" & $A4 & "'and todate(A) = date '" & TEXT($C$2,"yyyy-MM-dd") & "'")
                                                           ^^^^^^      ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
   

   Similarly, for D4:

   =QUERY($A$24:$D , "Select C Where B='"&$A4&"'and todate(A) = date '"&TEXT($C$2,"yyyy-MM-dd")&"'")
   

4. Your calculations in column E can be improved to handle #N/A results appearing in columns C or D:

   =if(isna(C12*D12),0,C12*D12)
   

这篇关于Google表格：QUERY使用单元格内容作为SQL变量的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！