Как Lookup Top 5 Значения с повторяющимися значениями, используя INDEX-MATCH в Excel
В этой статье мы узнаем, как искать несколько значений с повторяющимися значениями поиска в Excel.
Итак, вот сценарий. У меня 10 студентов. Я подготовился к экзамену. На этом экзамене каждый ученик набрал баллы из 100. Теперь в Excel я хочу написать формулу, которая сообщит мне имя 5 лучших бомбардиров.
По-видимому, я могу использовать link: / logical-formulas-excel-large-function [LARGE function], чтобы получить максимальные значения. А затем функция ВПР-ВЫБОР или ИНДЕКС-ПОИСКПОЗ, чтобы отследить имена.
Но проблема здесь в том, что у него несовместимые оценки. И когда вы попытаетесь использовать link: / lookup-formulas-use-index-and-match-to-lookup-value [INDEX-MATCH], он вернет первое имя, найденное для тех же оценок. Второе имя повторяющейся оценки не будет получено.
=INDEX($A$2:$A$11,MATCH(LARGE($B$2:$B$11,E2),$B$2:$B$11,0))
Вы можете видеть, что у нас есть два лучших бомбардира, Камаль и Мридам, которые набрали 54 балла. Но на обеих позициях указано только имя Камала.
Эта формула хороша, просто нужна небольшая помощь, чтобы однозначно идентифицировать каждую оценку. Итак, нам нужна колонка помощи.
В C2 напишите эту формулу и скопируйте в C11.
=RAND()+B2
Link: / excel-formula-and-function-the-rand-function [RAND function] возвращает случайное число от 1 до 0.
Теперь в этом столбце к баллам добавляется случайное число. Так как добавленное число находится в диапазоне от 1 до 0, фактический результат не изменится.
Теперь мы можем использовать этот столбец, чтобы получить имена четырех лучших бомбардиров.
=INDEX($A$2:$A$11,MATCH(LARGE($C$2:$C$11,E2),$C$2:$C$11,0))
Здесь БОЛЬШОЙ ($ C $ 2: $ C $ 11, E2):
link: / logic-formulas-excel-large-function [НАИБОЛЬШАЯ функция в Excel] вернет n-е наибольшее число из диапазона $ C $ 2: $ C $ 11, которое будет уникальным значением.
link: / lookup-formulas-excel-match-function [MATCH] (LARGE ($ C $ 2: $ C $ 11, E2), $ C $ 2: $ C $ 11,0):
link: / lookup-formulas-retrieving-matching-values-from-not-neighbour-list [Match function] будет искать это максимальное значение в диапазоне $ C $ 2: $ C $ 11 и вернет его индекс.
link: / lookup-formulas-excel-index-function [INDEX] ($ A $ 2: $ A $ 11, link: / lookup-formulas-excel-match-function [MATCH] (LARGE ($ C $ 2: $ C $ 11, E2), $ C $ 2: $ C $ 11,0) *):
Теперь`ссылка: / поиск-формулы-excel-index-function [ИНДЕКС] ссылка: / поиск-формулы-получение-сопоставления-значений-из-не-соседнего-списка [функция]
будет смотреть на этот индекс в диапазоне $ A $ 2: $ A $ 11 и вернет имя в этой позиции.
Вы можете скрыть этот столбец помощника или сделать его невидимым с помощью цветов.
Обратите внимание, что это работает только для числовых значений. Это не удастся для текстовых значений. Если вы хотите связать: / формулы-и-функции-введение-из-vlookup-function [VLOOKUP]
Несколько значений с повторяющимися значениями поиска, тогда это не сработает.
Я надеюсь, что это было полезно. Дайте мне знать, если у вас есть особые требования. Напишите это в разделе комментариев ниже.
Статьи по теме:
link: / lookup-formulas-vlookup-multiple-values [Как выполнить ВПР для нескольких значений в Excel]
link: / lookup-formulas-use-index-and-match-to-lookup-value [Как использовать ИНДЕКС и ПОИСКПОЗ для поиска значения в Excel]
link: / lookup-formulas-lookup-value-with-множественные критерии [Как найти значение с несколькими критериями в Excel]
Популярные статьи:
link: / keyboard-formula-shortcuts-50-excel-shortcuts-to-grow-your-performance [50 сочетаний клавиш Excel для повышения производительности]
link: / формулы-и-функции-введение-функции-vlookup [Как использовать функцию ВПР в Excel]
link: / tips-countif-in-microsoft-excel [Как использовать функцию СЧЁТЕСЛИ в Excel]
link: / excel-formula-and-function-excel-sumif-function [Как использовать функцию СУММЕСЛИ в Excel]