Обща формула
=INDEX(names,MATCH(LARGE(values,F5),values,0))
Обобщение
За да получите името на n-тата най-голяма стойност, можете да използвате INDEX и MATCH с функцията LARGE. В показания пример формулата в клетка H5 е:
=INDEX(name,MATCH(LARGE(score,F5),score,0))
където име (B5: B16) и резултат (D5: D16) са именувани диапазони.
Обяснение
Накратко, тази формула използва функцията LARGE, за да намери n-тата най-голяма стойност в набор от данни. След като имаме тази стойност, ние я включваме в стандартна формула INDEX и MATCH, за да извлечем свързаното име. С други думи, ние използваме n-тата най-голяма стойност като "ключ" за извличане на свързана информация.
Функцията LARGE е ясен начин за получаване на n-тата най-голяма стойност в диапазон. Просто предоставете диапазон за първия аргумент (масив) и стойност за n като втори аргумент (k):
=LARGE(range,1) // 1st largest =LARGE(range,2) // 2nd largest =LARGE(range,3) // 3rd largest
Работейки отвътре навън, първата стъпка е да се получи „1-вата“ най-голяма стойност в данните с функцията LARGE:
LARGE(score,F5) // returns 93
В този случай стойността във F5 е 1, така че искаме 1-вата по големина оценка (т.е. най-високата оценка), която е 93. Сега можем да опростим формулата до:
=INDEX(name,MATCH(93,score,0))
Вътре в функцията INDEX, функцията MATCH е настроена да локализира позицията 93 в посочения резултат от диапазона (D5: D16):
MATCH(93,score,0) // returns 3
Тъй като 93 се появява в 3-ти ред, MATCH връща 3 директно в INDEX като номер на реда, с име като масив:
=INDEX(name,3) // Hannah
И накрая, функцията INDEX връща името в 3-ти ред, "Хана".
Забележете, че взимаме стойностите за n от диапазона F5: F7, за да получим 1-ви, 2-ри и 3-ти най-високи резултати при копиране на формулата.
Извличане на група
Същата основна формула ще работи за извличане на всякаква свързана информация. За да получите групата за най-големите стойности, можете просто да промените масива, предоставен на INDEX с посочената група от диапазони :
=INDEX(group,MATCH(LARGE(score,F5),score,0))
Със стойността 1 във F5, LARGE ще получи най-високата оценка и формулата ще върне „A“.
Забележка: с Excel 365 можете да използвате функцията ФИЛТЪР за динамично изброяване на горните или долните резултати.
С XLOOKUP
Функцията XLOOKUP може да се използва и за връщане на името на n-тата най-голяма стойност по следния начин:
=XLOOKUP(LARGE(score,F5),score,name)
LARGE връща най-голямата стойност, 93, директно в XLOOKUP като справочна стойност:
=XLOOKUP(93,score,name) // Hannah
С посочения резултат от диапазон (D5: D16) като масив за търсене и име (B5: B16) като масив за връщане, XLOOKUP връща "Hannah" както преди.
Работа с връзки
Дублиращите се стойности в числовите данни ще създадат "равенство". Ако при класираните стойности възникне равенство, например, ако първата и втората най-големи стойности са еднакви, LARGE ще върне една и съща стойност за всяка. Когато тази стойност бъде предадена във функцията MATCH, MATCH ще върне позицията на първото съвпадение, така че ще видите върнатото същото (първо) име.
Ако има възможност за връзки, може да искате да приложите някаква стратегия за прекъсване на вратовръзката. Един от подходите е да се създаде нова помощна колона от стойности, които са били коригирани за прекъсване на връзките. След това използвайте стойностите на помощната колона за класиране и извличане на информация. Това прави логиката, използвана за скъсване на връзките, ясна и ясна.
Друг подход е да се прекъснат вратовръзки само въз основа на позиция (т.е. първата равенство "печели"). Ето формула, която възприема този подход:
INDEX(name,MATCH(1,(score=LARGE(score,F5))*(COUNTIF(H$4:H4,name)=0),0))
Забележка: това е формула на масив и трябва да се въведе с control + shift + enter, освен в Excel 365.
Тук използваме MATCH, за да намерим числото 1, и конструираме справочен масив, използвайки логическа логика, която (1) сравнява всички резултати със стойността, върната от LARGE:
score=LARGE(score,F5)
и (2) използва разширяваща се проверка на обхвата, ако името вече е в класирания списък:
COUNTIF(H$4:H4,name)=0
Когато дадено име вече е в списъка, то се „отменя“ от логиката и се съпоставя следващата (дублирана) стойност. Забележете, че разширяващият се диапазон започва от предишния ред, за да се избегне кръгова препратка.
Този подход работи в този пример, тъй като в колоната с имена няма дублирани имена. Ако обаче дублирани имена се появят в класирани стойности, подходът трябва да бъде коригиран. Най-лесното решение е да се уверите, че имената са уникални.
Бележки
- За да получите името на n-та стойност с критерии (т.е. ограничете резултатите до група A или B), ще трябва да разширите формулата, за да използвате допълнителна логика.
- В Excel 365 функцията ФИЛТЪР е по-добър начин за динамично изброяване на резултатите отгоре или отдолу. Този подход автоматично ще се справи с връзките.
