Excel

Ime n -te največje vrednosti

Name Nth Largest Value

Formula Excel: Ime n -te največje vrednostiSplošna formula | _+_ | Povzetek

Če želite dobiti ime n -te največje vrednosti, lahko uporabite INDEX in MATCH z VELIKA funkcija . V prikazanem primeru je formula v celici H5:





= INDEX (names, MATCH ( LARGE (values,F5),values,0))

kje ime (B5: B16) in rezultat (D5: D16) so imenovanih obsegov .

če celica ni prazna, potem
Pojasnilo

Na kratko, ta formula uporablja funkcijo LARGE za iskanje n -te največje vrednosti v nizu podatkov. Ko imamo to vrednost, jo vključimo v standard INDEX in MATCH formulo za pridobitev povezanega imena. Z drugimi besedami, za pridobivanje povezanih podatkov uporabljamo n -to največjo vrednost, kot je 'ključ'.





The VELIKA funkcija je enostaven način, da dobite n -to največjo vrednost v razponu. Preprosto podajte obseg za prvi argument ( matriko ) in vrednost za n kot drugi argument ( do ):

 
= INDEX (name, MATCH ( LARGE (score,F5),score,0))

Če delate od znotraj navzven, je prvi korak, da dobite 'prvo' največjo vrednost v podatkih s funkcijo LARGE:



 
 = LARGE (range,1) // 1st largest = LARGE (range,2) // 2nd largest = LARGE (range,3) // 3rd largest

V tem primeru je vrednost v F5 1, zato zahtevamo prvo največjo oceno (tj. Najvišjo oceno), ki je 93. Formulo lahko zdaj poenostavimo na:

 
 LARGE (score,F5) // returns 93

Znotraj INDEX funkcija , Funkcija MATCH je nastavljen za iskanje položaja 93 v imenovani obseg rezultat (D5: D16):

 
= INDEX (name, MATCH (93,score,0))

Ker se 93 pojavi v tretji vrstici, MATCH vrne 3 neposredno v INDEX kot številko vrstice z ime kot matrika:

 
 MATCH (93,score,0) // returns 3

Nazadnje, funkcija INDEX vrne ime v tretji vrstici, 'Hannah'.

Upoštevajte, da zberemo vrednosti za n iz območja F5: F7, da dobite 1., 2. in 3. najvišjo oceno pri kopiranju formule.

Pridobite skupino

Ista osnovna formula bo delovala pri pridobivanju vseh povezanih informacij. Če želite dobiti skupino za največje vrednosti, lahko preprosto spremenite matriko, podano v INDEX, z imenovanim obsegom skupina :

 
= INDEX (name,3) // Hannah

Z vrednostjo 1 v F5 bo LARGE dobil najvišjo oceno, formula pa bo vrnila 'A'.

Opomba: s Excel 365 , lahko uporabite Funkcija FILTER do dinamično navajajte zgornje ali spodnje rezultate .

Z XLOOKUP -om

The Funkcija XLOOKUP lahko uporabite tudi za vrnitev imena n -te največje vrednosti, kot je ta:

 
= INDEX (group, MATCH ( LARGE (score,F5),score,0))

LARGE vrne največjo vrednost 93 neposredno v XLOOKUP kot iskalno vrednost:

kako ustvariti tabelo množenja v
 
= XLOOKUP ( LARGE (score,F5),score,name)

Z imenovanim obsegom rezultat (D5: D16) kot iskalno polje , in ime (B5: B16) kot return array , XLOOKUP vrne 'Hannah' kot prej.

Ravnanje s kravatami

Podvojene vrednosti v numeričnih podatkih bodo ustvarile 'kravato'. Če na primer pri vrednostih, ki se uvrščajo, pride do izenačenja, če sta prva in druga največja vrednosti enaki, bo LARGE za vsako vrnil isto vrednost. Ko se ta vrednost prenese v funkcijo MATCH, MATCH vrne položaj prvi match, zato boste videli vrnjeno isto (prvo) ime.

kako iz vrtilne tabele ustvariti vrtilni grafikon

Če obstaja možnost povezav, boste morda želeli izvesti nekakšno strategijo prekinitve. Eden od pristopov je ustvarjanje nov pomožni stolpec vrednosti, ki je bil prilagojen za prekinitev vezi . Nato uporabite vrednosti stolpcev pomočnikov za razvrščanje in pridobivanje informacij. Zaradi tega je logika, ki se uporablja za prekinitev vezi, jasna in eksplicitna.

Drug pristop je prekinitev vezi samo na podlagi položaja (tj. Prva zmaga 'zmaga'). Tukaj je formula, ki uporablja ta pristop:

 
= XLOOKUP (93,score,name) // Hannah

Opomba: to je formula matrike in ga je treba vnesti s tipko control + shift + enter, razen v Excel 365 .

Tukaj uporabljamo MATCH za iskanje številke 1 in z uporabo sestavimo iskalno matriko logična logika da (1) primerja vse ocene z vrednostjo, ki jo vrne LARGE:

 
 INDEX (name, MATCH (1,(score= LARGE (score,F5))*( COUNTIF (H:H4,name)=0),0))

in (2) uporablja an širi obseg preveri če je ime že na uvrščeni listi:

 
score= LARGE (score,F5)

Ko je ime že na seznamu, ga logika „prekliče“ in ujema naslednja (podvojena) vrednost. Opazite, da se širi obseg začne na prejšnja vrstica , da bi se izognili krožnemu sklicevanju.

Ta pristop deluje v tem primeru, ker v stolpcu z imeni ni podvojenih imen. Če pa dvojnik imena pojavljajo v razvrščenih vrednostih, je treba pristop prilagoditi. Najlažja rešitev je, da se prepričate, da so imena edinstvena.

Opombe

  1. Če želite dobiti ime n -te vrednosti z merili , (tj. omejite rezultate na skupino A ali B), ki jih boste morali razširite formulo za uporabo dodatne logike .
  2. V Excel 365 , Funkcija FILTER je boljši način dinamično navajajte zgornje ali spodnje rezultate . Ta pristop bo samodejno obravnaval vezi.
Priloge mapa ime n -te največje vrednosti.xlsx Avtor Dave Bruns


^