Általános képlet
=INDEX(names,MATCH(LARGE(values,F5),values,0))
Összegzés
Az n-edik legnagyobb érték megadásához használhatja az INDEX és a MATCH funkciót a LARGE funkcióval. A bemutatott példában a H5 cellában szereplő képlet a következő:
=INDEX(name,MATCH(LARGE(score,F5),score,0))
ahol a név (B5: B16) és a pontszám (D5: D16) tartományokat neveznek meg.
Magyarázat
Dióhéjban ez a képlet a LARGE függvény segítségével keresi meg az adatkészlet n-edik legnagyobb értékét. Miután megvan ez az érték, bedugjuk azt egy standard INDEX és MATCH képletbe a társított név beolvasásához. Más szavakkal, az n-edik legnagyobb értéket, például "kulcsot" használjuk a kapcsolódó információk lekérésére.
A LARGE funkció egy egyszerű módszer a tartomány n-edik legnagyobb értékének megszerzésére. Egyszerűen adjon meg egy tartományt az első argumentumhoz (tömb) és az n értékét második argumentumként (k):
=LARGE(range,1) // 1st largest =LARGE(range,2) // 2nd largest =LARGE(range,3) // 3rd largest
Belülről kifelé haladva az első lépés az, hogy megkapjuk az adatok "1." legnagyobb értékét a LARGE funkcióval:
LARGE(score,F5) // returns 93
Ebben az esetben az F5 értéke 1, ezért az 1. legnagyobb pontszámot (azaz a legfelsőbb pontszámot) kérjük, amely 93. Most egyszerűsíthetjük a képletet:
=INDEX(name,MATCH(93,score,0))
Az INDEX funkción belül a MATCH funkció úgy van beállítva, hogy megtalálja a 93 pozícióját a megnevezett tartomány pontszámában (D5: D16):
MATCH(93,score,0) // returns 3
Mivel a 93 megjelenik a 3. sorban, a MATCH 3-at közvetlenül az INDEX-nek ad vissza sorszámként, névvel tömbként:
=INDEX(name,3) // Hannah
Végül az INDEX függvény visszaadja a 3. sorban szereplő nevet: "Hannah".
Figyelje meg, hogy az n értékeit felvesszük az F5: F7 tartományból annak érdekében, hogy az 1., 2. és 3. legmagasabb pontszámot kapjuk a képlet másolásakor.
Csoport beolvasása
Ugyanaz az alapképlet működik a kapcsolódó információk lekérésére. Ahhoz, hogy a csoport a legnagyobb értékek, egyszerűen meg tudjuk változtatni a tömb szállított INDEX a megnevezett tartomány csoport :
=INDEX(group,MATCH(LARGE(score,F5),score,0))
Az F5 1-es értékével a LARGE kapja a legmagasabb pontszámot, és a képlet "A" -t ad vissza.
Megjegyzés: Az Excel 365 használatával a SZŰRŐ funkció segítségével dinamikusan felsorolhatja a felső vagy alsó eredményeket.
XLOOKUP-tal
Az XLOOKUP függvény az n-edik legnagyobb érték nevének visszaadására is használható:
=XLOOKUP(LARGE(score,F5),score,name)
A LARGE a legnagyobb, 93-as értéket közvetlenül az XLOOKUP számára adja vissza, mint keresési értéket:
=XLOOKUP(93,score,name) // Hannah
Ha a megnevezett tartomány pontszám (D5: D16) a keresési tömb, és a név (B5: B16) a visszatérő tömb, az XLOOKUP a "Hannah" -t adja vissza, mint korábban.
Nyakkendők kezelése
A numerikus adatok ismétlődő értékei "döntetlent" hoznak létre. Ha a rangsorolt értékek között egyenlőség lép fel, például ha az első és a második legnagyobb érték megegyezik, a LARGE mindegyikre ugyanazt az értéket adja vissza. Amikor ezt az értéket átadják a MATCH függvénynek, a MATCH visszaadja az első mérkőzés helyzetét, így ugyanazt (első) nevet fogja látni.
Ha fennáll a kapcsolatok lehetősége, érdemes valamiféle nyakkendőtörési stratégiát megvalósítani. Az egyik megközelítés egy új segítő oszlop létrehozása, amelyet úgy alakítottunk ki, hogy megszakítsa a kapcsolatokat. Ezután használja a segítő oszlop értékeit az információk rangsorolásához és visszakereséséhez. Ez egyértelművé és egyértelművé teszi a kapcsolatok megszakítására használt logikát.
Egy másik megközelítés a kapcsolatok megszakítása csak a pozíció alapján (azaz az első döntetlen "nyer"). Itt van egy képlet, amely ezt a megközelítést alkalmazza:
INDEX(name,MATCH(1,(score=LARGE(score,F5))*(COUNTIF(H$4:H4,name)=0),0))
Megjegyzés: ez egy tömbképlet, amelyet a Control + Shift + Enter billentyűkombinációval kell megadni, az Excel 365 kivételével.
Itt a MATCH segítségével megkeressük az 1-es számot, és egy logikai logika felhasználásával létrehozunk egy keresési tömböt, amely (1) az összes pontszámot összehasonlítja a LARGE által visszaadott értékkel:
score=LARGE(score,F5)
és (2) kiterjeszti a tartományellenőrzést, ha a név már szerepel a rangsorolt listában:
COUNTIF(H$4:H4,name)=0
Amikor egy név már szerepel a listán, akkor a logika "törli", és a következő (ismétlődő) értéket megegyezik. Figyelje meg, hogy a kör alakú hivatkozás elkerülése érdekében a bővülő tartomány az előző sorban kezdődik.
Ez a megközelítés ebben a példában működik, mert a név oszlopban nincsenek ismétlődő nevek. Ha azonban rangsorolt értékekben ismétlődő nevek fordulnak elő, akkor a megközelítést ki kell igazítani. A legegyszerűbb megoldás annak biztosítása, hogy a nevek egyediek legyenek.
Megjegyzések
- Ahhoz, hogy megkapja az n-edik érték nevét feltételekkel (azaz az eredményeket az A vagy B csoportra korlátozza), ki kell bővítenie a képletet további logika használatához.
- Az Excel 365-ben a SZŰRŐ funkció jobb módszer a felső vagy alsó eredmények dinamikus felsorolására. Ez a megközelítés automatikusan kezeli a kapcsolatokat.
