需要公式加法 [英] Formula Addition Required

问题描述

使用这个公式效果很好，但我还想再添加1个.

Using this formula which is working perfectly but i also want 1 more addition in it.

现在Col8只是显示那些填充有日期的单元格，我也希望在Col8中将那些空单元格显示在工作表中.

Right now Col8 is just showing those cells which are filled with dates i also want in Col8 that those cells which are empty should appear in sheet.

=IFERROR(QUERY({Data!A2:T}, "Select Col3, Col4, Col5, Col6, Col7, Col8, Col9, Col10, Col11, Col12, Col13, Col14 Where Col1 contains '"&B2&"' and Col2 contains '"&D2&"' and Col9 contains '"&F2&"' and Col8 > 0 and date "&TEXT($I$2,"'YYYY-MM-DD'")&" <= Col8 and date "&TEXT(I3,"'YYYY-MM-DD'")&" >= Col8" ,0))

如果您需要帮助，请在链接

Here is the link if you could help

推荐答案

摘要答案:搜索小于1900的日期，再加上所需的日期范围，将查找空白日期和该范围内的日期.

Summary Answer: Searching for dates less than 1900, plus your desired date range, finds both blank dates, and dates within your range.

QUERY(... WHERE (Col8 <= date '1900-01-01' or 
                (Col8 >= date '1950-01-01'  and Col8 <= date '2020-07-30'))

详细信息: 我只是以鲁本所做的为基础，但我想我拥有您所有的过滤器"，按照您想要的方式工作.我还添加了覆盖切换，以分别关闭每个过滤器，包括日期范围.看看我添加到样本表中的标签-日志-报告-GK".我最终将查询组装为多个文本字符串，每个过滤器一个.最终的主要公式如下:

Details: I've just built on what Ruben did but I think I have all of your "filters" working the way you'd like. I've also added an override toggle, to turn off each of the filters individually, including the date range. Have a look at the tab I added to your sample sheet - "Log - Report-GK". I ended up assembling the query as several strings of text, one for each filter. The final main formula looks like this:

=QUERY({Data!A2:T}, "Select Col3, Col4, Col5, Col6, Col7, Col8, Col9, Col10, Col11, Col12, Col13, Col14 Where Col1 " & B4 & D4 & F4 & I4 & I5 ,0)

其中选择标准保存在B4，D4，F4，I4和I5中.它们下方的复选框控制该特定过滤器是否有效.

where the selection criteria are held in B4, D4, F4, I4, and I5. The tickboxes below them control whether that specific filter has any effect.

单个条件公式如下所示，其中IF语句检查是否应使用此特定条件(勾选框为空白/FALSE)或忽略.

The individual criteria formula look like this, where the IF statement checks if this specific criteria should be used (tickbox is blank/FALSE) or ignored.

=if(H2=FALSE," and Col8 >= date '"&TEXT($I$2,"yyyy-mm-dd") &"' ","")

如果为FALSE(勾选框为空白)，则此文本字符串将串联在基本查询语句上.如果为TRUE(选择了打勾框)，则将空字符串连接到查询.

If FALSE (tickbox blank), this text string is concatenated on to the base query statement. If TRUE (tickbox selected), an empty string is concatenated to the query.

B16动态显示当前查询的文本以及过滤条件. B17只是提供查询返回的记录计数.如果您打开所有四个复选框，它将选择数据"选项卡中的所有记录，作为有效性检查.

B16 Dynamically shows the text of the current query, with the filter criteria. B17 just gives a record count returned from the query. If you turn on all four tickboxes, it selects all of the records in the Data tab, as a validity check.

请注意，我没有在查询中添加错误检查以方便调试.您可以根据需要将其添加回去.

Note that I didn't add an error check in the query, to facilitate debugging. You can add that back if you want.

我在N列中列出了您数据中所有唯一的Status值，并将F2的数据验证指向该列表.数据中的状态值不一致.

I listed all of the unique Status values from your data in column N, and pointed the data validation for F2 to that list. The Status values in your data are inconsistent.

最后，正如Ruben所说，我认为不可能使用查询来选择日期在所选日期范围内的行，或者没有日期的行.由于该列中的数据类型混合，因此查询会忽略没有日期的记录.

Lastly, as Ruben said, I don't think it is possible to use query to select rows that have a date in your selected date range, OR, have no date. The query ignores records with no date, due to the mixed data types in that column.

更新:我确实找到了一条查询语句来提取特定日期范围内的两条记录以及您说需要的任何带有空白日期的记录.请确认.该公式有效地如下，而忽略了其他并发条件:

Update: I did find a single query statement to pull both records within a specific date range, as well as any records with a blank date, which you said you needed. Please confirm this. The formula is effectively as follows, ignoring the other concurrent criteria:

QUERY({Data!A2:T}, "Select Col3, Col4, ... Col14 Where Col1  <> '' and (Col8 <= date '1900-01-01' or (Col8 >= date '1950-01-01'  and Col8 <= date '2020-07-30'))

选择小于1900-01-01的日期值也将检索空白记录.选择NULL日期值无效.我将其添加到示例标签日志-报告-GK"在样品表中.

Selecting for date values less than 1900-01-01 retrieves the blank records as well. Selecting for NULL date values did not work. I'll add this to the sample tab "Log - Report-GK" in your sample sheet.

让我知道这是否有帮助.

Let me know if this helps.

这篇关于需要公式加法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！