google-sheets - QUERY 使用单元格内容作为 SQL 变量

Question

这是我正在使用的电子表格的样子:

汇总表

(工作表顶部)



源数据

(同一张纸，在输出下方)



我正在使用 QUERY函数来填充汇总表中的适当提要，其中需要放入从 A24 开始的数据。

这是我在单元格 C6 中使用的公式(在整个汇总表中使用了类似的公式):

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

这获得了正确的信息，但需要编辑公式以使其在使用的每个单元格中都是唯一的。无法使用 A='2/27/14 - Thu' 快速填充单元格的问题。过于具体。

我试图设置它以便:

在接受数据之前，A 中的日期将与在 ROW 2 中的标题中找到的日期进行比较

B 中的房间类型将与汇总表中每一行中 A 的值进行比较

如何编写 QUERY 函数以将这些值引用为变量，而不是在我的原始函数中使用文字字符串？

Answer 1

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

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

与号 ( & ) 用于连接字符串段。

请注意，由于源数据扩展到工作表数据范围的底部，我们可以放弃指定底部行。范围 $A$24:$D将接收所有行，因此它会自动调整以适应其他源数据。

要比较日期，两个值都必须是日期。 “2/27/14 - Thu”在源数据表中不被识别为日期，而是被识别为文本，即使您已将数字格式设置为日期。因此，请确保将所有源数据更改为在 A 列中包含实际日期。

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

对于源列中的日期，您可以使用 todate()标量函数告诉 QUERY将源中的日期转换为查询日期，以及 date关键字将以下文字字符串(来自 C2)视为日期。

单元格 C4 的结果函数为:

=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 列中的计算可以改进以处理 #N/A结果出现在 C 或 D 列中:

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