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