时间戳

首页 > 解决方案 > 使用范围时无法比较查询公式中的日期列

google-sheets-formula - 使用范围时无法比较查询公式中的日期列

问题描述

我有一个从多个工作表中获取数据的公式。除了日期，其他东西都可以正常工作。

这是单子

       =QUERY({
iferror(indirect(if(E1,"January!A:I","")),{"","","","","","","","",""});
iferror(indirect(if(F1,"February!A:I","")),{"","","","","","","","",""});
iferror(indirect(if(G1,"March!A:I","")),{"","","","","","","","",""});
iferror(indirect(if(H1,"April!A:I","")),{"","","","","","","","",""});
iferror(indirect(if(I1,"May!A:I","")),{"","","","","","","","",""});
iferror(indirect(if(J1,"June!A:I","")),{"","","","","","","","",""});
iferror(indirect(if(E3,"July!A:I","")),{"","","","","","","","",""});
iferror(indirect(if(F3,"August!A:I","")),{"","","","","","","","",""});
iferror(indirect(if(G3,"September!A:I","")),{"","","","","","","","",""});
iferror(indirect(if(H3,"October!A:I","")),{"","","","","","","","",""});
iferror(indirect(if(I3,"November!A:I","")),{"","","","","","","","",""});
iferror(indirect(if(J1,"December!A:I","")),{"","","","","","","","",""})}, "Select *  where Col2 <>'' AND Col1 = date '2020-01-02'" )

一月份的工作表有数据，但我仍然无法显示查询结果。

更新：一些月表可能存在也可能不存在，即使在 E1 Fq G1 等中选择了 true

2 月表丢失，如果在 F1 中选择了 true，则应该不会出错，所以我使用 iferror 的间接公式

标签： google-sheets-formula

解决方案

其实我不知道为什么有时它不能比较日期。此外，我不明白为什么我不能在查询中嵌套 iferror 并使用大括号处理范围。

最后，我决定制作一个小表格，在其中检查工作表是否存在（独立于带有复选框的表格）。

如果两个条件都满足，Query 使用范围。如果不是，它使用一行空白单元格。

我的解决方案在这里：

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

我使用的公式如下所示：

 =QUERY({
if(E1+M2=2,{January!A1:I},{"","","","","","","","",""});
if(F1+M3=2,{Febryary!A1:I},{"","","","","","","","",""});
if(G1+M4=2,{March!A1:I},{"","","","","","","","",""});
if(H1+M5=2,{April!A1:I},{"","","","","","","","",""});
if(I1+M6=2,{May!A1:I},{"","","","","","","","",""});
if(J1+M7=2,{June!A1:I},{"","","","","","","","",""});
if(E3+M8=2,{July!A1:I},{"","","","","","","","",""});
if(F3+M9=2,{August!A1:I},{"","","","","","","","",""});
if(G3+M10=2,{September!A1:I},{"","","","","","","","",""});
if(H3+M11=2,{October!A1:I},{"","","","","","","","",""});
if(I3+M12=2,{November!A1:I},{"","","","","","","","",""});
if(J3+M13=2,{December!A1:I},{"","","","","","","","",""})}, "Select *  where Col1 < date '2020-12-02' and Col1 is not null" )

列 M 包含测试是否存在工作表。

推荐阅读