AGGREGAT具有临界和重复的阵列

Question

我有以下Excel电子表格：

      A            B               C               D              E
1   ProdID       Price       Unique ProdID     1. Biggest     2. Biggest
2   2606639       40            2606639           50            50         
3   2606639       50            4633523           45            35
4   2606639       20            3911436           25            25
5   2606639       50
6   4633523       45
7   4633523       20
8   4633523       35
9   3911436       20
10  3911436       25
11  3911436       25
12  3911436       15

在Cells D2:E4中，我想在列A中显示每个ProdID的1.最大和2.最大的价格。因此，我使用以下公式：

D2 =AGGREGAT(14,6,$B$2:$B$12/($A$2:$A$12=$C2),1)
E2 =AGGREGAT(14,6,$B$2:$B$12/($A$2:$A$12=$C2),2)

只要价格在列B、中是唯一的，就像在第二个ProdID (4633523)上看到的那样，这个公式起作用。

但是，一旦列B中的价格不是唯一的(例如ProdID 26026639为50，ProdID 3911436为25 )，单元格D2:E4中的函数就不能显示正确的结果。

如果你能用AGGREGAT-公式和数组公式解决这个问题，你有什么想法吗？

Answer 1

您可以检查第一个ProdID-价格组合出现的次数，并在AGGREGAT函数的最后一个参数中使用它。所以而不是

=AGGREGAT(14,6,$B$2:$B$12/($A$2:$A$12=$C2),2)

你就会有

=AGGREGAT(14,6,$B$2:$B$12/($A$2:$A$12=$C2),2+COUNTIFS(A:A,C2,B:B,D2)-1)

你当然可以把“1+COUNTIFS.”但我这样说，所以可以更好地理解，它使用的位置2+发生的组合出现的数量最大的ProdID后，第一次发生。