Как обрабатывать повторяющиеся значения с помощью функции LARGE()?

Question

Я хочу вычислить лучшие 3 результата с дублирующимися значениями, но разными именами.

Пример данных:

Name           score
A              40
B              55
C              37
D              55            

Ожидаемый результат:

order        score     name
1            55        B
1            55        D
3            40        A

Answer 1

Это можно сделать с помощью сводной таблицы, а также с помощью функции RANK() .

Шаг 1 - Подготовка сводной таблицы

1. Добавьте столбец (например, в C) к вашим данным со следующей формулой: =RANK(B2,$B$2:$B$5) -> это покажет, как B2 занимает место среди всех значений в $B$2:$B$5

2. Выберите ваши данные (включая столбец рейтинга) и нажмите Вставить> Сводная таблица
3. Поместите поля "Оценка" и "Имя" как строки, а поле "Оценка" как значения, как показано

4. Отформатируйте сводную таблицу, щелкнув следующие пункты меню:

Теперь у вас должно быть что-то похожее на это:

---

Шаг 2 - Отображение 3 верхних значений (метод A)

1. Нажмите на стрелку возле первого поля вашей сводной таблицы (Ранг)
2. Выберите "Фильтры значений"
3. Выберите "Топ 10"

4. Введите 3 вместо 10 чтобы получить верхние 3 значения

Теперь у вас должно быть что-то вроде этого:

Примечание: с помощью этого метода, если несколько значений имеют одинаковый ранг (как в данных вашего примера), они будут показаны несколько раз. Это поведение по умолчанию сводной таблицы и функций "Top X".

Если вы не хотите 4 строки, а только 3, см. Метод B

---

Шаг 2 - Отображение 3 верхних значений (метод B)

1. Нажмите на стрелку возле первого поля вашей сводной таблицы (Ранг)
2. Выберите "Фильтры меток"
3. Выберите "Меньше или равно" и введите 3 при появлении запроса

Примечание: будьте осторожны с этим методом: если есть 5 значений с рангом 1, будет 5 строк. Второе по величине значение будет иметь ранг 6 и не будет отображаться (больше 3). Так что это уже не топ-3, но я думаю, что этот метод стоит упомянуть, так как в некоторых случаях он может работать.

---

Когда данные изменятся, не забудьте обновить сводную таблицу!

Answer 2

Это можно сделать с помощью двух простых формул для столбцов order и score , а также третьей, немного более сложной, для столбца name .

Настройте электронную таблицу следующим образом:

Затем многоэлементный массив - введите эти формулы в указанные диапазоны (не забудьте исключить окружающие { и }):

Формула 1 (D2:D4):

{=RANK(E2:E4,E2:E4)}

Формула 2 (E2:E4):

{=LARGE(B2:B5,{1;2;3})}

Формула 3 (F2:F4):

{=INDEX(A:A,10^5-MOD(LARGE(10^5*B2:B5+10^5-ROW(B2:B5),{1;2;3}),10^5))}

---

Чтобы перейти от первых 3 результатов к верхним N результатам, где N - большое число, скажем, например, 10 , вместо использования жестко закодированного массива для рангов, {1;2;3;4;5;6;7;8;9;10} , используйте следующее:

ROW(INDEX(X:X,1):INDEX(X:X,10))

Хотя это работает как есть, предпочтительно заменить X:X на столбец, в который введена формула. Таким образом, формула не будет нарушена, если столбец X будет удален.

Это гораздо лучшее решение, чем вездесущий и более короткий ROW(INDIRECT("1:10")) поскольку он энергонезависимый.

---

Формула Объяснения:

Первые две формулы достаточно просты.

Предварительно подтвержденная версия третьей формулы выглядит следующим образом:

=
INDEX(
  (A:A),
  10^5-
  MOD(
    LARGE(
      10^5*B2:B5+10^5-ROW(B2:B5),
      {1;2;3}
    ),
    10^5
  )
)

Формула работает, изменяя оценки так, чтобы они также содержали десятичное дополнение индекса строки. Затем, после того, как LARGE выберет соответствующую модифицированную оценку, индекс извлекается и используется с INDEX() чтобы получить имя, соответствующее выбранной оценке.

Пройдя по формуле в F3 следует прояснить вышесказанное:

• 10^5*B2:B5
  → 10^5*{40;55;37;55}
  → {4000000;5500000;3700000;5500000} , шкала оценок
• 10^5-ROW(B2:B5)
  → 10^5-{2;3;4;5}
  → {99998;99997;99996;99995} , десятая часть чисел в строке
• {4000000;5500000;3700000;5500000}+{99998;99997;99996;99995}
  → {4099998;5599997;3799996;5599995} , "модифицированные" баллы
• LARGE({4099998;5599997;3799996;5599995},{1;2;3})
  → {5599997;5599995;4099998} , три верхние модифицированные оценки
• 10^5-MOD({5599997;5599995;4099998},10^5)
  → 10^5-{99997;99995;99998}
  → {3;5;2} , номера строк трех первых баллов
• INDEX(A:A,{3;5;2})
  → {B;D;A}
  → D так как мы находимся во второй ячейке формулы ввода множества ячеек

Заметки:

• Предварительно подтвержденная формула действительно работает, если введена.
• Скобки вокруг (A:A) необходимы, чтобы A:A оставалось на своей линии.
• Для идентичных результатов формула возвращает имена в порядке возрастания строк. Формула может быть изменена, чтобы возвращать имена в порядке убывания, используя простой номер строки вместо десятичного дополнения номера строки при изменении баллов:

{=INDEX(A:A,MOD(LARGE(10^5*B2:B5+ROW(B2:B5),{1;2;3}),10^5))}

• Можно даже рандомизировать порядок идентичных баллов, генерируя модифицированный балл со случайным числом между баллом и номером строки.

Answer 3

Чтобы получить очки, в ячейке F3 напишите эту формулу и заполните 3 строку:

=LARGE(B$3:B$6,ROWS(F$3:F3)) .

Чтобы получить Имя, в ячейке G3 напишите эту формулу массива и заполните:

NB. Завершите эту формулу массива с помощью Ctrl+Shift+Enter.

{=IFERROR(INDEX(A$3:A$6,SMALL(IF(B$3:B$6=F3,ROW(B$3:B$6)-ROW(F$3)+1),COUNTIF(F$3:F3,F3))),"")}

Чтобы получить заказ, в ячейке E3 напишите эту формулу и заполните:

=IFERROR(RANK(LARGE(B$3:B$6,ROWS(E$3:E3)),$F$3:$F$6),"")

Как работает формула:

• Формула в F3 находит 3 лучших результата.
• Формула в G3 находит имена для первых 3 баллов, сравнивает оценку из столбца F с столбцом B и извлекает имя из столбца A
• Формула в E3 находит Ранг для 3 лучших результатов.