Excel-найти n-е по величине значение на основе критериев

Это своего рода расширение этой задачи: Excel-сумма значений из набора данных на основе критериев

У меня есть такая таблица: 

Country Region    Code  Name of product Year   Value
Sweden  Stockholm 52    Apple           1995   1000
Sweden  Malmö     25    Pancake         1991   1500
Sweden  Malmö     52    Apple           1992   2470
Finland Helsinki  21    Candy           1987   2500
Denmark Copenhagen 52   Apple           1987   2571
То, что я хочу сделать, - это создать код, который может дать мне сумму N-й по величине стоимости продуктов, проданных в определенной стране. То есть, если я хочу получить наибольшее value за products проданное в Sweden, то должен вернуть Apple и сумму проданных яблок, 3470.

Edit: решение проблемы Glitch_Doctor:

Введите описание изображения здесь

1 2
excel

1 ответ:

Во-первых, для значения:

обе формулы являются формулами массива, Пожалуйста, подтвердите формулу с помощью Ctrl+сдвиг+введите , оставаясь в строке формул

=MAX(SUMIFS($F$2:$F$6,$A$2:$A$6,$I2,$D$2:$D$6,$D$2:$D$6))

Это строит массив SUMIFS() результатов для страны в ячейке $I2, и каждое название продукта затем захватывает результат MAX().

И название продукта:

=INDEX($D$2:$D$6,SMALL(IF(SUMIFS($F$2:$F$6,$A$2:$A$6,$I2,$D$2:$D$6,$D$2:$D$6)=$K2,ROW($D$2:$D$6)-1),1))

Теперь, используя результат max SUMIFS(), мы ссылаемся на список SUMIFS() результаты и получить строку продукта (смещение к началу INDEX()) и получить наименьший номер строки.

Введите описание изображения здесь

Вы можете настроить MAX() в первой формуле, чтобы она была LARGE(,n), где n-n-й по величине результат.

1