Google表格:使用QUERY选择组内的前N个案例 [英] Google Sheets: select top N cases within groups using QUERY
问题描述
我要报告:选择最近有N个状态代码= 2,3,4,5的推销员".
I want to make a report: "select salesmen who have N recent Status codes = 2,3,4,5".
我的数据的一个结构示例有35行(包括1个标题行): 链接. 该文件具有日期,销售代码(销售人员编号),状态代码(交易成功的编号)和其他字段对此不是必需的.
A structural example of my data has 35 rows (including 1 header row): link. This file has a Date, Sales code (id of a salesman), Status code (id of how successful a transaction was) and other fields which are not necessary for the purpose.
我最终使用了三个公式:
I ended up using three formulas:
-
带有
IMPORTRANGE
的QUERY
函数.
在示例数据中,它稍微简单一些-仅从另一张表中获取销售代码,状态代码和日期,然后按日期和销售代码进行排序. A9中的公式:
=QUERY({Source!D1:E, Source!A1:A}, $B$4, 1)
a
QUERY
function withIMPORTRANGE
.
In the example data it is slightly simpler - take only Sales Codes, Status Code, and Date from another sheet, then order by Date, Sales Code. Formula in A9:
=QUERY({Source!D1:E, Source!A1:A}, $B$4, 1)
带有顺序编号的附加列. D10中的公式:
=ArrayFormula(if(len(A10:A), ROW(A10:A) - MATCH(A10:A,A10:A,0) - 8, ))
an additional column with a sequentional numbering. Formula in D10:
=ArrayFormula(if(len(A10:A), ROW(A10:A) - MATCH(A10:A,A10:A,0) - 8, ))
一个QUERY函数,仅提取N个案例(比方说5个). F9中的公式:
=QUERY(A9:D, "select A, B where D <="&B3, 1)
a QUERY function to extract only N cases (let's say 5). Formula in F9:
=QUERY(A9:D, "select A, B where D <="&B3, 1)
是否有一种方法可以将所有3个步骤组合为一个步骤,这样我就可以使用一个(并且希望速度更快)公式来获得F10:G24所示的输出?我尝试过的公式(为便于阅读而进行了扩展):
Is there a way to combine all 3 steps into one so I get the output like in F10:G24 using one (and hopefully fast :)) formula? The formula I tried (expanded for readability):
=QUERY(
{
QUERY({Source!D1:E, Source!A1:A}, $B$4, 1),
ArrayFormula(
IF(len(J10:J),
ROW(J10:J) - MATCH(J10:J, J10:J, 0) - 8,
)
)
},
"select Col1, Col2 where Col4 <="&B3,
1
)
它给了我错误:
函数ARRAY_ROW参数2的行大小不匹配.期望值:28.实际值:991."
"Function ARRAY_ROW parameter 2 has mismatched row size. Expected: 28. Actual: 991."
我还尝试了ROW()
和MATCH()
中的有限数据范围,但是会产生一个空表.
在原始数据库中,大约有3500行,并且它们会扩展,所以我认为我应该坚持无限范围来自动进行数据提取.
I also tried finite data ranges in ROW()
and MATCH()
but that yields an empty table.
In the original database there are ~3500 rows and they will expand, so I think I should stick to infinite ranges to automate data extraction.
推荐答案
嗯,这有点像TBH的噩梦-之前曾出现过类似的问题,但没有简单的答案.这是一个草稿,涉及多次重复基本查询-
Hm well it's a bit of a nightmare TBH - a similar question has cropped up before but no easy answer. Here is a draft which involves repeating the basic query several times-
=ArrayFormula(query({query(A2:E,"select * where E>=2 and E<=5 order by D, A desc"),
row(indirect("2:"&count(filter(E2:E,E2:E>=2,E2:E<=5))+1))-
match(query(A2:E,"select D where E>=2 and E<=5 order by D, A desc"),
query(A2:E,"select D where E>=2 and E<=5 order by D, A desc"),0)
},"select * where Col6<=5"))
但是需要研究更多内容以查看它是否可以简化.
But needs looking at more to see if it can be simplified.
这是包含标题的全列版本-我认为还可以
This is the full-column version including headers - I think it's OK
=ArrayFormula(query({query(A:E,"select * where E>=2 and E<=5 order by D, A desc"),
row(indirect("1:"&count(filter(E:E,E:E>=2,E:E<=5))+1))+1-
match(query(A:E,"select D where E>=2 and E<=5 order by D, A desc"),
query(A:E,"select D where E>=2 and E<=5 order by D, A desc"),0)
},"select * where Col6<=5"))
这篇关于Google表格:使用QUERY选择组内的前N个案例的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!