如何在Google表格中查询引用日期范围的日期 [英] how to query with a date referencing a range of dates in google sheets

问题描述

这里是链接>

https://docs.google.com/a/auind.com/spreadsheets/d/1ZnV0uNwM-uS_kK3CQwx-EGy9c9PAK4enCzSIv0bCva0/编辑

我希望表格1中的行转移到表格2，但只显示列L的值在1-150000之间的行，而列C的日期介于d3和d4之间。
d3和d4每天都在改变，所以导入的数据会相应改变。
te公式应该在表2中的a6中进行。因为两张表上的第1-5行都是冻结的标题。

解决方案

在这种特殊情况下，我会使用FILTER函数。在第二张表格中试试A6单元格：
$ b

= FILTER（'Foreclosure Leads'！A6：L，'Foreclosure Leads'！L6：L> = 1''取消提示信息'！L6：L <= 150000'取消提示信息'！C6：C> ='取消提示信息'！D3，'取消提示信息'！C6：C <='取消提示信息'！D4）

这包括：

1. 'Foreclosure Leads'！A6： L - 首先它告诉我们正在调查'Foreclosure Leads'工作表中的行。第一行是第六行，最后一行是未定义的。从A到L的columbs将被复制。
   

2. 然后是条件：
   
   

   
   
   • 'Foreclosure Leads'！L6：L> = 1 - L单元格中的值应大于或等于1
     
   • 'L Forever Leads'！L6：L <= 150000 - L单元格中的值应小于或等于150000''
     
   • 'Foreclosure Leads'！ C6：C> ='Foreclosure Leads'！D3 - C单元格中的值应该大于或等于D3单元格中的日期（即今天）
   
   
   • 'Foreclosure Leads'！C6：C <='Foreclosure Leads'！D4 - C单元格中的值应小于或等于D4单元格中的日期
   
   

希望它有帮助。

here is the link>

https://docs.google.com/a/auind.com/spreadsheets/d/1ZnV0uNwM-uS_kK3CQwx-EGy9c9PAK4enCzSIv0bCva0/edit

i want rows from sheet one to transfer over to sheet two but only show me the rows with column L with a value between 1-150000 and with Col C with a date between d3 and d4. d3 and d4 change everyday so thus the data being imported over would change accordingly. te formula should go into a6 on sheet two. since rows 1-5 on both sheets are frozen headers.

解决方案

I would use FILTER function in this special case. Try this for the A6 cell on sheet two:

=FILTER('Foreclosure Leads'!A6:L,'Foreclosure Leads'!L6:L>=1,'Foreclosure Leads'!L6:L<=150000,'Foreclosure Leads'!C6:C>='Foreclosure Leads'!D3,'Foreclosure Leads'!C6:C<='Foreclosure Leads'!D4)

This does the following:

1. 'Foreclosure Leads'!A6:L - First it tells that we are investigating the rows from 'Foreclosure Leads' worksheet. First row is the 6th, the last is undefined. The columbs from A to L will be copied.

2. Then there are the conditions:

   • 'Foreclosure Leads'!L6:L>=1 - The value in the L cell should be greater or equal to 1
   • 'Foreclosure Leads'!L6:L<=150000 - The value in the L cell should be less or equal to 150000
   • 'Foreclosure Leads'!C6:C>='Foreclosure Leads'!D3 - The value in the C cell should be greater or equal to the date in the D3 cell (which is today)
   • 'Foreclosure Leads'!C6:C<='Foreclosure Leads'!D4 - The value in the C cell should be less or equal to the date in the D4 cell

Hope it helps.

这篇关于如何在Google表格中查询引用日期范围的日期的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！