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:

1. 带有IMPORTRANGE的QUERY函数.
   在示例数据中，它稍微简单一些-仅从另一张表中获取销售代码，状态代码和日期，然后按日期和销售代码进行排序. A9中的公式:
   =QUERY({Source!D1:E, Source!A1:A}, $B$4, 1)

1. a QUERY function with IMPORTRANGE.
   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屋！