在 Google 表格中过滤具有最高值的唯一行的公式 [英] Formula to filter unique rows with highest value in Google Sheets
问题描述
我有一个工作表,每天几次自动从多个帐户中提取社交指标,因此我经常有同一个帖子的多行数据.如何过滤新工作表中的行,以便每个唯一帖子仅保留具有最高展示次数(G 列)的帖子 ID(F 列)?
I have a sheet that automatically pulls in social metrics from multiple accounts a few times a day, because of this I often have multiple lines of data for the same post. How do I filter the rows in a new sheet so that only the post ID (column F) with the highest number of impressions (column G) remains for each unique post?
我想要的过滤结果:
我探索了查询(不是我的强项)和过滤公式,但似乎找不到完全符合我需要的组合.这是我得到的公式:
I've explored queries (not my strong suit) and filter formulas and can't seem to find a combination that does exactly what I need. Here's the formula that I've gotten to:
=FILTER('Intermediate Data'!A2:L,MMULT(('Intermediate
Data'!H2:H>TRANSPOSE('Intermediate Data'!H2:H))*('Intermediate
Data'!F2:F=TRANSPOSE('Intermediate Data'!F2:F)),SIGN(ROW('Intermediate
Data'!F2:F)))<>0)
此代码未提取所有帖子 ID ---任何人有我可以尝试的想法吗?
This code doesn't pull in all the post IDs---anyone has any ideas I can try?
推荐答案
你也可以使用sort/sortn:
You can also use sort/sortn:
=sortn(sort(A2:C,1,1,2,1,3,0),999,2,1,1,2,1)
这篇关于在 Google 表格中过滤具有最高值的唯一行的公式的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!