查询仅查看最高计数并按组计数 [英] Query to View only top counts and counted by group
问题描述
我想用这个查询
= query（G2：I80，select G，H，I where H = max（H）group by I， - 1）
从此：
Cdd Cts T
$ NWH 4 A1
LBB 3 A1
<$> MP 3 A1
DC 2 A1
AK 10 A10
CC 3 A10
SC 2 A11
JL 1 A11
VT 1 A11
至此：
T Cdd Cts p>
A1 NWH 4
A10 AK 10
A11 SC 2
我做得对吗？或者我必须想出一些手动计算它们的方法？
试试
$ b $ （
看看它是否适合你？
它是如何工作的：
1：使用sort（）创建一个新的'虚拟'表，其中第一列为第一列。新表格按col H排序（最新日期显示在顶部）。
2：具有唯一值的列表从col I（具有唯一函数）中检索
3：该列表是'look up'（使用vlookup）。当找到匹配时，它将检索前3列（{1，2，3}或：col I，G和H）。 Vlookup只返回找到的第一个匹配项：由于我们的列表已排序，这将是最新日期的条目。
4：如果没有发现任何错误，则用iferror（）来取消错误。
I wanted to use this query =query(G2:I80,"select G,H,I where H=max(H) group by I",-1)
From this:
Cdd Cts T
NWH 4 A1
LBB 3 A1
MP 3 A1
DC 2 A1
AK 10 A10
CC 3 A10
SC 2 A11
JL 1 A11
VT 1 A11
To This:
T Cdd Cts
A1 NWH 4
A10 AK 10
A11 SC 2
Am I doing it right? Or I have to come up with something to manually count them?
Try
=ArrayFormula(iferror(vlookup(unique(I2:I), sort({I2:I, G2:H},3,), {1,2,3},0)))
and see if that works for you ?
How does it work:
1: with sort() a new 'virtual' table is created with col I as the first column. That new table is sorted on col H (latest dates appear on top). 2: a list with the unique values is retrieved from col I (with the unique function) 3: that list is 'looked up' (with vlookup). When a match is found it retrieves the first 3 columns ({1, 2, 3} or: col I, G and H). Vlookup only returns the first match found: since our list is sorted, that will be the entry with the latest date. 4: if nothing is found an error is suppressed with the iferror().
这篇关于查询仅查看最高计数并按组计数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
