Splošna formula
=INDEX(names,MATCH(LARGE(values,F5),values,0))
Povzetek
Če želite dobiti ime n-te največje vrednosti, lahko uporabite INDEX in MATCH s funkcijo LARGE. V prikazanem primeru je formula v celici H5:
=INDEX(name,MATCH(LARGE(score,F5),score,0))
kjer sta ime (B5: B16) in rezultat (D5: D16) imenovani obsegi.
Pojasnilo
Na kratko ta formula uporablja funkcijo LARGE za iskanje n-te največje vrednosti v naboru podatkov. Ko dobimo to vrednost, jo vključimo v standardno formulo INDEX in MATCH, da pridobimo povezano ime. Z drugimi besedami, n-to največjo vrednost uporabljamo kot "ključ" za pridobivanje povezanih informacij.
Funkcija LARGE je preprost način, da dobite n-to največjo vrednost v območju. Preprosto navedite obseg za prvi argument (matriko) in vrednost za n kot drugi argument (k):
=LARGE(range,1) // 1st largest =LARGE(range,2) // 2nd largest =LARGE(range,3) // 3rd largest
Delo od znotraj navzven je prvi korak, da dobimo "1." največjo vrednost v podatkih s funkcijo LARGE:
LARGE(score,F5) // returns 93
V tem primeru je vrednost v F5 enaka 1, zato zahtevamo 1. največji rezultat (tj. Najvišji rezultat), ki je 93. Formulo lahko zdaj poenostavimo na:
=INDEX(name,MATCH(93,score,0))
Znotraj funkcije INDEX je funkcija MATCH nastavljena tako, da poišče položaj 93 v imenovanem rezultatu obsega (D5: D16):
MATCH(93,score,0) // returns 3
Ker se 93 pojavi v 3. vrstici, MATCH vrne 3 neposredno v INDEX kot številko vrstice z imenom kot matriko:
=INDEX(name,3) // Hannah
Končno funkcija INDEX vrne ime v 3. vrstici "Hannah".
Upoštevajte, da pobiramo vrednosti za n iz območja F5: F7, da dobimo 1., 2. in 3. najvišjo oceno, ko formulo prepišemo.
Pridobi skupino
Ista osnovna formula bo delovala za pridobivanje vseh povezanih informacij. Če želite dobiti skupino za največje vrednosti, lahko preprosto spremenite polje, dobavljeno v INDEX, z imenovano skupino obsega :
=INDEX(group,MATCH(LARGE(score,F5),score,0))
Z vrednostjo 1 v F5 bo LARGE dobil najvišjo oceno in formula bo vrnila "A".
Opomba: pri Excelu 365 lahko s funkcijo FILTER dinamično prikažete zgornje ali spodnje rezultate.
Z XLOOKUP
S funkcijo XLOOKUP lahko vrnete tudi ime n-te največje vrednosti, kot je ta:
=XLOOKUP(LARGE(score,F5),score,name)
LARGE vrne največjo vrednost, 93, neposredno v XLOOKUP kot vrednost iskanja:
=XLOOKUP(93,score,name) // Hannah
Z imenovano oceno obsega (D5: D16) kot iskalno polje in imenom (B5: B16) kot vrnilno matriko, XLOOKUP vrne "Hannah" kot prej.
Ravnanje z vezmi
Podvojene vrednosti v številskih podatkih bodo ustvarile "kravato". Če pride do izenačenja vrednosti, ki se uvrščajo, na primer če sta prva in druga največja vrednost enaki, bo LARGE vrnil enako vrednost za vsako. Ko je ta vrednost posredovana v funkcijo MATCH, bo MATCH vrnil položaj prvega ujemanja, zato boste videli isto (prvo) ime.
Če obstaja možnost povezav, boste morda želeli izvesti nekakšno strategijo preloma vezi. Eden od pristopov je ustvariti nov pomožni stolpec z vrednostmi, ki so bile prilagojene prekinitvi vezi. Nato uporabite vrednosti pomožnih stolpcev za razvrščanje in pridobivanje informacij. Zaradi tega je logika, ki se uporablja za prekinitev vezi, jasna in jasna.
Drug pristop je prekinitev vezi na podlagi samo položaja (tj. Prva neodločena zmaga). Tu je formula, ki upošteva ta pristop:
INDEX(name,MATCH(1,(score=LARGE(score,F5))*(COUNTIF(H$4:H4,name)=0),0))
Opomba: to je formula matrike, ki jo je treba vnesti s tipko control + shift + enter, razen v Excelu 365.
Tu uporabimo MATCH za iskanje številke 1 in z uporabo logične logike izdelamo iskalno polje, ki (1) primerja vse rezultate z vrednostjo, ki jo vrne LARGE:
score=LARGE(score,F5)
in (2) uporablja preverjanje obsega, če je ime že na seznamu:
COUNTIF(H$4:H4,name)=0
Ko je ime že na seznamu, ga logika "prekliče" in se ujema naslednja (podvojena) vrednost. Upoštevajte, da se razširitev obsega začne v prejšnji vrstici, da se izognete krožnemu sklicu.
Ta pristop deluje v tem primeru, ker v stolpcu z imeni ni podvojenih imen. Če pa se v razvrščenih vrednostih pojavijo podvojena imena, je treba pristop prilagoditi. Najlažja rešitev je zagotoviti, da so imena unikatna.
Opombe
- Če želite dobiti n-to vrednost z merili (tj. Omejite rezultate na skupino A ali B), boste morali formulo razširiti, da bo uporabila dodatno logiko.
- V programu Excel 365 je funkcija FILTER boljši način za dinamično uvrstitev zgornjih ali spodnjih rezultatov. Ta pristop bo samodejno obravnaval vezi.
