Как найти наибольшие значения ячеек в нескольких столбцах в Excel?

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

Я предоставил изображение с тестовыми данными, чтобы попытаться проиллюстрировать, что мне нужно:

Справа от оранжевых прямоугольников показаны пятеро лучших людей на основе критериев, которые я хочу использовать. Базово, я хочу, чтобы первый прямоугольник показывал, кто имеет наивысшее значение в поле K, за ним второе наивысшее значение во втором прямоугольнике и так далее. Если значение в прямоугольнике K идентично, я хочу, чтобы значение в столбце Total служило в качестве разрядного показателя, а если это все еще не разрешеет ничью, я хочу использовать столбец I в качестве окончательного разрядного показателя.

Очевидно, я хочу оставить сортировку в таблице как есть, и значения внутри таблицы будут регулярно изменяться (поэтому копирование всех данных на вторичный лист данных вручную для использования функций сортировки не сработает, если этот процесс не может быть автоматизирован). Любая помощь будет оценена. Я пробовал различные варианты функций VLOOKUP, INDEX и MAX без успеха.

Я бы использовал:

=INDEX(FILTER(SORTBY(A1:K20,K1:K20,-1,J1:J20,-1,I1:I20,-1),SORT(K1:K20,,-1)>=MIN(LARGE(K1:K20,SEQUENCE(5)))),SEQUENCE(5),2)

для имен и:

=INDEX(FILTER(SORTBY(A1:K20,K1:K20,-1,J1:J20,-1,I1:I20,-1),SORT(K1:K20,,-1)>=MIN(LARGE(K1:K20,SEQUENCE(5)))),SEQUENCE(5),11)

для баллов в столбце K.

Сортируйте данные формульно, используя SORTBY(), и используйте этот отсортированный набор данных в качестве входного диапазона для FILTER(). Это подготавливает данные для следования вашим разрядным показателям без каких-либо усилий, плюс, странно (каждая странная вещь с FILTER() получает “странно” в качестве описания), использование MIN() не сработает (как я это написал) без настройки сортировки. Так что именно поэтому SORTBY() перед передачей диапазона данных в FILTER().

Для того чтобы критерии в функции FILTER() работали, диапазон критериев также должен быть отсортирован, прежде чем он будет установлен >= для пяти наибольших результатов в столбце K.

По какой-то причине, на которую я не потратил много времени на выяснение, а скорее на преодоление, прямое сравнение баллов в столбце K с пятью наибольшими значениями в столбце K не сработало. Но использование LARGE() для поиска пяти наивысших, затем MIN() для выбора наименьшего из них, а затем сравнение отсортированных значений в столбце K как критерия для FILTER() сработало. Так что вы видите.

Это создало, внутренне в Excel, не как результат, таблицу значений таблицы в желаемом порядке и ограничение до пяти строк. Использование INDEX() позволило выбрать интересующий столбец, столбец 2, чтобы получить имена, и столбец 11, чтобы получить баллы в столбце K. Поэтому две формулы, отличающиеся только столбцом на своих концах, который передается в INDEX(), чтобы получить имена для одного столбца вашей выходной таблицы и баллы в столбце K для другого.

Имейте в виду, что если кто-то другой совпал бы с Джо по всем трем столбцам, если бы этот человек находился выше Джо в исходном диапазоне, он бы занял пятую позицию, а Джо – шестую. И наоборот, если бы он находился ниже Джо в исходном диапазоне. Формула не обрабатывает это никаким образом, и я имею в виду СОВСЕМ.

Тем не менее, она делает то, что фактически запрашивается.

Это также показывает, что не нужно решать трудности в том смысле, что потом можно было бы делать то, что пробовали. Часто можно просто преодолеть это и двигаться дальше, не зная, что могло бы сделать логический подход работающим. Мне всегда нравится решать каждую ситуацию, но это практичный мир, и рабочий результат обычно желательнее, чем идеальный результат за счет кучи времени. Так что довольно часто приходится идти на компромисс и откладывать понимание на другой день. Это работает, так что…

Используя стратегию комбинирования критериев с учетом наиболее значимых статистических данных (@fixer1234), получаем следующие варианты формул.

Эта формула будет работать, когда:

• Ничья невозможна после применения третьего критерия.

  =INDEX( $C$2:$C$20, MATCH( LARGE( $K$2:$K$20*10^6+$J$2:$J$20*10^3+$I$2:$I$20, $B24), $K$2:$K$20*10^6+$J$2:$J$20*10^3+$I$2:$I$20, 0))
  

• Имена уникальны (или совпадающие имена также не совпадают по всем 3 критериям).

  =INDEX( $C$2:$C$20, MATCH( LARGE( $K$2:$K$20*10^6+$J$2:$J$20*10^3+$I$2:$I$20, $B24), IF( NOT( COUNTIF( $D$23:$D23, $C$2:$C$20)), $K$2:$K$20*10^6+$J$2:$J$20*10^3+$I$2:$I$20, ""), 0))
  

• Всегда, возвращая результаты как идентификаторы пользователей (вспомогательный столбец, начиная с E24).

  =INDEX( $B$2:$B$20, MATCH( LARGE( $K$2:$K$20*10^6+$J$2:$J$20*10^3+$I$2:$I$20, $B24), IF( NOT( COUNTIF( $E$23:$E23, $B$2:$B$20)), $K$2:$K$20*10^6+$J$2:$J$20*10^3+$I$2:$I$20, ""), 0))
  

• Для имени используйте =VLOOKUP($E24,$B$2:$C$20,2), где $E24 – это идентификатор пользователя, возвращенный формулой.

Как использовать

• Независимо от выбранной основной формулы, вставьте ее в первую строку результатов. Это формулы CSE, поэтому после ввода формулы или внесения изменений нажмите Ctrl–Shift–Enter.
• Протяните формулу вниз. Эти формулы не возвращают массив/диапазон и не будут работать, если введены в качестве формулы массива с выделенными всеми 5 ячейками.
• Как $E$23:$E23 в этой формуле, так и $D$23:$D23 в формуле уникальных имен; являются расширяющимися диапазонами, которые начинаются в ячейке над первой строкой формулы. Эта первая упоминаемая ячейка расширяющегося диапазона не должна совпадать с любыми строками в столбце идентификаторов или столбце имен соответственно.