3

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

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

Name           score
A              40
B              55
C              37
D              55            

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

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

3 ответа3

3

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

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

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

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

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

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


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

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

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

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

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

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


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

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

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


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

1

Это можно сделать с помощью двух простых формул для столбцов 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))}
  • Можно даже рандомизировать порядок идентичных баллов, генерируя модифицированный балл со случайным числом между баллом и номером строки.
1

Чтобы получить очки, в ячейке 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 лучших результатов.

Всё ещё ищете ответ? Посмотрите другие вопросы с метками .