Генеричка формула
=INDEX(names,MATCH(LARGE(values,F5),values,0))
Резиме
Да бисте добили име н-те највеће вредности, можете да користите ИНДЕКС и МАТЦХ са функцијом ЛАРГЕ. У приказаном примеру, формула у ћелији Х5 је:
=INDEX(name,MATCH(LARGE(score,F5),score,0))
где су имена (Б5: Б16) и резултат (Д5: Д16) именовани опсези.
Објашњење
Укратко, ова формула користи функцију ЛАРГЕ за проналажење н-те највеће вредности у скупу података. Једном када имамо ту вредност, укључујемо је у стандардну формулу ИНДЕКС и МАТЦХ да бисмо преузели придружено име. Другим речима, користимо н-у највећу вредност попут „кључа“ за преузимање повезаних информација.
Функција ЛАРГЕ је једноставан начин за добијање н-те највеће вредности у опсегу. Једноставно наведите опсег за први аргумент (низ) и вредност за н као други аргумент (к):
=LARGE(range,1) // 1st largest =LARGE(range,2) // 2nd largest =LARGE(range,3) // 3rd largest
Радећи изнутра према ван, први корак је добити „1.“ највећу вредност у подацима са функцијом ЛАРГЕ:
LARGE(score,F5) // returns 93
У овом случају, вредност у Ф5 је 1, па тражимо прву највећу оцену (тј. Најбољу оцену), која је 93. Формулу сада можемо поједноставити на:
=INDEX(name,MATCH(93,score,0))
Унутар функције ИНДЕКС, функција МАТЦХ је постављена да лоцира положај 93 у именованом резултату опсега (Д5: Д16):
MATCH(93,score,0) // returns 3
Пошто се 93 појављује у 3. реду, МАТЦХ враћа 3 директно у ИНДЕКС као број реда, са именом као низом:
=INDEX(name,3) // Hannah
Коначно, функција ИНДЕКС враћа име у 3. реду „Ханнах“.
Приметићемо да прикупљамо вредности за н из опсега Ф5: Ф7, како бисмо добили 1., 2. и 3. највишу оцену док се формула преписује.
Преузми групу
Иста основна формула ће радити за проналажење свих повезаних информација. Да бисте добили групу за највеће вредности, можете једноставно променити низ испоручен у ИНДЕКС са именованом групом опсега :
=INDEX(group,MATCH(LARGE(score,F5),score,0))
Са вредношћу 1 у Ф5, ЛАРГЕ ће добити највећи резултат, а формула ће вратити „А“.
Напомена: са програмом Екцел 365 можете да користите функцију ФИЛТЕР да бисте динамички приказивали горње или доње резултате.
Уз КСЛООКУП
Функција КСЛООКУП се такође може користити за враћање имена н-те највеће вредности попут ове:
=XLOOKUP(LARGE(score,F5),score,name)
ЛАРГЕ враћа највећу вредност, 93, директно у КСЛООКУП као вредност претраживања:
=XLOOKUP(93,score,name) // Hannah
Са именованим резултатом опсега (Д5: Д16) као низом претраживања и именом (Б5: Б16) као низом повратка, КСЛООКУП враћа „Ханнах“ као и раније.
Руковање везама
Дупликати вредности у нумеричким подацима створиће „везу“. Ако дође до изједначења у вредностима које се рангирају, на пример, ако су прва и друга највеће вредности исте, ЛАРГЕ ће вратити исту вредност за сваку. Када се ова вредност проследи у функцију МАТЦХ, МАТЦХ ће вратити положај првог подударања, па ћете видети исто (прво) име.
Ако постоји могућност веза, можда ћете желети да примените неку врсту стратегије прекида везе. Један приступ је стварање нове помоћне колоне вредности које су прилагођене прекиду веза. Затим користите вредности помоћних ступаца за рангирање и преузимање података. То чини логику која се користи за прекидање веза јасном и експлицитном.
Други приступ је прекидање веза на основу само позиције (тј. Прва нерешена победа). Ево формуле која заузима тај приступ:
INDEX(name,MATCH(1,(score=LARGE(score,F5))*(COUNTIF(H$4:H4,name)=0),0))
Напомена: ово је формула низа и мора се унети са цонтрол + схифт + ентер, осим у програму Екцел 365.
Овде користимо МАТЦХ да пронађемо број 1 и конструишемо низ претраживања користећи логичку логику која (1) упоређује све резултате са вредношћу коју враћа ЛАРГЕ:
score=LARGE(score,F5)
и (2) користи проверу опсега који се шири ако је име већ на ранг листи:
COUNTIF(H$4:H4,name)=0
Када се име већ налази на листи, логика га „отказује“ и подудара се следећа (дупликата) вредност. Обратите пажњу да опсег ширења почиње у претходном реду, како би се избегла кружна референца.
Овај приступ делује у овом примеру, јер у колони имена нема дупликата имена. Међутим, ако се у рангираним вредностима појаве дупликати имена, приступ треба прилагодити. Најлакше решење је осигурати да су имена јединствена.
Напомене
- Да бисте добили име н-те вредности са критеријумима (тј. Ограничите резултате на групу А или Б), мораћете да проширите формулу тако да користи додатну логику.
- У програму Екцел 365, функција ФИЛТЕР је бољи начин да динамички наведете горње или доње резултате. Овај приступ ће аутоматски поднијети везе.
