Excel公式-如何根据另一个单元格获取一个单元格的最高2个值

Question

我需要一些帮助来满足以下要求:

原始数据

            A            B            C
      -------------------------------------
   1 |     Opp     |   Vendor    |  Amount |
     |-------------|-------------|---------|
   2 |     101     |   Vendor1   | 100000  |
   3 |     101     |   Vendor2   |   5000  |
   4 |     103     |   Vendor1   |  30000  |
   5 |     103     |   Vendor2   |   5000  |
   6 |     103     |   Vendor3   |  50000  |

输出表

            A             B           C         D         E
      ---------------------------------------------------------
   1 |     Opp     |  MainVendor | Amount1 | 2Vendor | Amount2 |
     |-------------|-------------|---------|---------|---------|
   2 |     101     |   Vendor1   | 100000  | Vendor2 |  5000   |
   3 |     103     |   Vendor3   |  50000  | Vendor1 |  30000  |

主要供应商 : 金额最高的厂商

金额1 : MainVendor 的金额

2供应商 : 第二高的供应商

金额2 : 金额为 2Vendor

我只能使用以下数组公式获得 Amount1 值:

{=MAX(IF(A:A=[@[Opp]];C:C))} 

在 C 列。

我无法获取列 的值乙 , 电话 & 邮箱 .

Answer 1

这是一个解决方案 - 如果人们可以重构公式，那么很高兴听到 build 性的批评，这可能有点令人费解。

获取 Amount1 , Amount2您可以使用的值:

=INDEX(LARGE($C$2:$C$10*--($A$2:$A$10=$A13),1),1)

在哪里:

$C$2:$C$10是 Amount 中的值专栏

乘以 --($A$2:$A$10=$A13) - 含义 {1;1;0;0;0;0;0;0;0} * {100000;5000;30000;5000;50000;45000;50000;40000;51000} - 这会影响Amount 中的值。与 Opp 不对应的列行的。

LARGE然后函数只选择 100000 中的第一个最大的和 5000因为数组中的其他值现在是 0 .

获得第二大，第三大等你供应2 , 3等进入LARGE功能

LARGE函数包含在 INDEX 中得到实际值，因为我相信它将它作为一个 1 元素数组返回(尽管公式检查器并不清楚)

样本:



获取 MainVendor和 Vendor2您可以使用的值:

=INDEX($B$2:$B$10,INDEX(MATCH(LARGE($C$2:$C$10*--($A$2:$A$10=$A13),1),$C$2:$C$10*--($A$2:$A$10=$A13),0),1))

这是:

$B$2:$B$10是 Vendor 中的值专栏

使用 MATCH 检索索引在 相同的公式用于AmountX针对自己的源数组

MATCH包裹在另一个 INDEX再次获取实际值，我认为该值作为 1 元素数组返回

样本:



我创建了另一组 Opp值和公式适用于 Amount 中的一些重复值列，但不同 Opp s。