QUERY函数可从一组数据中获取最新日期 [英] QUERY Function that pulls latest date from a group of data

问题描述

我从工作表1中获得了这些数据:

I have this data from a sheet1:

colA -- colB -- colC -- colD -- colE
user1 -- prodA -- 1mo -- 2020-05-01 -- 50
user1 -- prodA -- 1mo -- 2020-06-01 -- 50
user1 -- prodA -- 1mo -- 2020-07-01 -- 50
user1 -- prodA -- 1mo -- 2020-08-01 -- 50
user1 -- prodA -- 1mo -- 2020-09-01 -- 45

然后我只需要在sheet2 col A，B，C中显示，并在colD中显示最新日期，并在colE中显示该行的值.如何使用QUERY拉动它?

Then I need to just show up in sheet2 col A,B,C and the latest date from colD and the value of that row from colE. How can I pull that using QUERY?

输出:

user1 -- prodA -- 1mo -- 2020-09-01 -- 45

谢谢

推荐答案

您可以这样做:

=QUERY(Sheet1!A:E,"select A,B,C,D,E order by D desc limit 1")

将D降序并限制为1.

要删除标题，您可以执行以下操作:

To remove the headers, you can do this:

=INDEX(QUERY(Sheet1!A:E,"select A,B,C,D,E order by D desc limit 1"),2)

或作为纯QUERY:

=QUERY(Sheet1!A:E,"select A,B,C,D,E 
order by D desc 
limit 1
label A'',B'',C'',D'',E''")

这篇关于QUERY函数可从一组数据中获取最新日期的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！