如何在Excel中筛选评论量≥100且平均评分最高的Item Id

核心公式（数组公式） #

{=INDEX(A:A,MATCH(MAX(IF(COUNTIF(A:A,A:A)>=100,AVERAGEIF(A:A,A:A,B:B))),IF(COUNTIF(A:A,A:A)>=100,AVERAGEIF(A:A,A:A,B:B)),0))}

公式拆解说明 #

• COUNTIF(A:A,A:A)>=100：遍历每个Item Id，统计其对应的评论总数，生成判断是否≥100的布尔数组
• IF(COUNTIF(A:A,A:A)>=100,AVERAGEIF(A:A,A:A,B:B))：仅保留评论量达标的Item的平均评分，未达标的项返回FALSE，直接过滤不符合条件的数据
• MAX(...)：从过滤后的平均评分数组里提取最大值，也就是达标Item中的最高平均分
• MATCH(...)：定位这个最高平均分在过滤后数组中的首次出现位置
• INDEX(A:A,...)：根据定位到的位置，返回对应的Item Id

注意事项 #

• 输入方式：Excel 2019及更早版本需按Ctrl+Shift+Enter完成数组公式输入（大括号无需手动输入，系统自动添加）；Excel 365/2021支持动态数组，直接回车即可
• 多结果处理：如果有多个Item的最高平均分相同，公式会返回数据中第一个出现的Item Id
• 性能优化：建议用具体数据范围（如A2:A10000）代替整列引用A:A，减少冗余计算，提升大数据集下的运行速度

内容的提问来源于stack exchange，提问作者st_user_321122