excel - 只为一组中的 5 个最高值查找 AVERAGEIFS

Question

问题
如果满足这些标准，我正在尝试获得平均价格......

搜索词=“杯子”

顶级竞争对手 = "TRUE"

我只想对集合中的前 5 个结果(按搜索排名)进行平均。

所以公式应该返回 8.00
我已经尝试过
=AVERAGEIFS(C:C,A:A,"cup",D:D,"TRUE",B:B,"<="&SMALL(B:B,5))
我的公式返回 7.5，太低了。在我添加最后一个标准之前，该公式似乎有效: B:B,"<="&SMALL(B:B,5)



一个
乙
C
D


1
搜索词
秩
价格
顶级竞争对手

2
杯子
1
5.00
真的

3
杯子
2
5.00
错误的

4
杯子
3
10.00
真的

5
杯子
4
5.00
真的

6
杯子
5
5.00
错误的

7
杯子
6
10.00
错误的

8
杯子
7
10.00
真的

9
杯子
8
10.00
真的

10
杯子
9
5.00
真的

11
杯子
10
5.00
真的

12
盘子
1
20.00
真的

13
盘子
2
10.00
错误的

14
盘子
3
10.00
真的

15
盘子
4
20.00
真的

16
盘子
5
10.00
错误的

17
盘子
6
20.00
错误的

18
盘子
7
20.00
错误的

19
盘子
8
20.00
错误的

20
盘子
9
15.00
真的


谢谢你的帮助!

Answer 1

问题是您需要在应用 cup 和 true 参数之后应用排名参数。
如果没有 5 个项目符合您的 Search Term，您没有说明您想要发生的情况。和 Top Competitor要求。所以这两个公式都会返回 plate 的错误。因为你只有四 (4) 个 TRUE的
在 Excel Office 365 中，您可以使用:

=LET(x,FILTER(tbl,(tbl[Search Term]="cup")*tbl[Top Competitor]),AVERAGE(FILTER(INDEX(x,0,3),INDEX(x,0,2)<=SMALL(INDEX(x,0,2),5))))

您首先按杯子和真实过滤的地方；然后查看过滤结果的前五名。
如果您有较早的版本，则可以尝试以下公式:

=AVERAGE(AVERAGEIFS(tbl[Price],tbl[Rank],AGGREGATE(15,6,1/((tbl[Search Term]="cup")*tbl[Top Competitor])*tbl[Rank],{1;2;3;4;5}),tbl[Search Term],"cup",tbl[Top Competitor],TRUE))

请注意，在这两个公式中，我都使用了带有结构化引用的表。当然，如果您愿意，可以使用常规引用。
第二个公式将返回一个值数组，这是要平均的值。
在某些早期版本的 Excel 中，您可能需要“确认”此 数组公式 通过按住 ctrl + shift 同时按回车键。如果您正确执行此操作，Excel 将放置大括号 {...}在公式栏中观察到的公式周围