Excel - Нахождение n-го наибольшего значения для каждого члена списка

Question

У меня есть куча данных, которая состоит из значений на разных сайтах, как это:

Site  Value
A      1
A      2
A      5
A      7
B      5
B      7
B      11

Количество значений на каждом сайте может варьироваться. Я хочу извлечь n-е наибольшее значение для каждого сайта, например. для n = 2

Site  Value
A     2
B     7

Есть ли более простой способ сделать это, чем вручную добавить пару пустых строк между каждым сайтом и использовать LARGE?

Answer 1

Вы можете обернуть LARGE внутри IF и создать формулу массива, чтобы получить n-е наибольшее значение из подмножества. Одна формула должна работать для каждого сайта.

В столбцах A & B - ваши данные. Создайте основной список сайтов, скажем, в столбце D, и поместите значение квалификатора nth larget, скажем, 2,3 4, что нужно в столбце E. Теперь в F поместите формулу

=LARGE(IF($A$1:$A$7=D1,$B$1:$B$7,""),E1)

Заключите его в формулу массива, нажав сочетание клавиш CTRL + SHIFT + ENTER и перетащите его вниз.

Answer 2

Связано с моим ответом на другой вопрос.

Вы можете построить массив или диапазон, используя комбинацию этих формул

INDEX( value_range, MATCH( lookup_value, lookup_range, 0 )) для начала диапазона

INDEX( value_range, MATCH( lookup_value, lookup_range, 1 )) для конца диапазона

Итак, в вашем вопросе вы можете использовать это в сочетании с LARGE

=LARGE(INDIRECT(
   INDEX($B$1:$B$7, MATCH($C1, $A$1:$A$7, 0))&":"
   INDEX($B$1:$B$7, MATCH($C1, $A$1:$A$7, 1))
   ), 2)

Примечание: вам нужно отсортировать сайты. По возрастанию или по убыванию это хорошо, просто чтобы убедиться, что сайты сгруппированы.

Где столбец B относится к вашему списку значений, а столбец A к вашему списку сайтов.

$C1 относится к сайту, который вы хотите получить n-ным значением (в вашем примере это должно быть "A")