使用单元格内容作为SQL变量的QUERY [英] QUERY using cell contents as SQL variables
问题描述
这是我正在使用的电子表格的样子:
Here is what the spreadsheet I'm working with looks like:
(页面顶部)
(同一张纸，在输出下方)
(same sheet, below output)
我正在使用查询函数在摘要中填充适当的供稿数据从A24开始的表格需要放入.
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.
这是我在C6单元格中使用的公式(汇总表中使用了相似的公式):
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'")
这将获得正确的信息，但是需要对公式进行编辑以使其在使用的每个单元格中唯一.问题是无法快速填充单元格，而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.
我试图将其设置为:
- A中的日期将与接受数据之前在ROW 2中的标头上找到的日期进行比较
- B中的房间类型将与摘要表中每一行中A的值进行比较
如何编写QUERY函数以将这些值称为变量，而不是在原始函数中使用文字字符串?
How can the QUERY function be written to refer to these values as variables, instead of using the literal strings in my original function?
推荐答案
-
您可以使公式引用输出的A列中的索引，而不是像
'ENQ'这样的固定字符串.例如，您可以在单元格C4中将公式更改为此:
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.
请注意，由于源数据扩展到工作表数据范围的底部，因此我们可以放弃指定底部行.范围$A$24:$D将占据所有行，因此它将自动调整为其他源数据.
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/27/14-星期四"在源数据表中也不会被识别为日期，而是文本.因此，请确保将所有源数据更改为在A列中有实际日期.
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.
您仍然可以按照自己喜欢的方式设置日期格式-请查看此屏幕截图. A2的内容现在是正确的日期"2014年2月27日"，但是格式设置为显示"mm/dd/yy-DDD".只要数据是日期，就可以使用电子表格TEXT函数以yyyy-mm-dd格式解释日期来构建查询.
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.
在源列中带有日期，您可以使用todate()标量函数告诉QUERY将源中的日期转换为查询日期，并使用date关键字来处理以下文字字符串(来自C2)作为日期.
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.
单元格C4的结果函数为:
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") & "'")
^^^^^^ ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
类似地，对于D4:
=QUERY($A$24:$D , "Select C Where B='"&$A4&"'and todate(A) = date '"&TEXT($C$2,"yyyy-MM-dd")&"'")
您可以改进E列中的计算以处理出现在C或D列中的#N/A结果:
=if(isna(C12*D12),0,C12*D12)
这篇关于使用单元格内容作为SQL变量的QUERY的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
