用于过滤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屋！