Általános képlet
=INDEX(range,MATCH(LARGE(filtered_range,F5),filtered_range,0))
Összegzés
Ahhoz, hogy megkapja az n. Legnagyobb értéket a feltételekkel, használhatja az INDEX és MATCH, a LARGE függvényt, valamint az IF függvénnyel létrehozott szűrőt. A bemutatott példában a G5 cellában levő képlet:
=INDEX(name,MATCH(LARGE(IF(group="A",score),F5),IF(group="A",score),0))
ahol a név (B5: B16), a csoport (C5: C16) és a pontszám (D5: D16) tartományokat neveznek meg. A képlet az A csoport 1., 2. és 3. legmagasabb értékéhez társított nevet adja vissza.
Megjegyzés: Ezt a tömbképletet a Control + Shift + Enter billentyűkombinációval kell megadni, az Excel 365 kivételével.
Magyarázat
A LARGE funkció egyszerű módszer a tartomány n-edik legnagyobb értékének megszerzésére:
=LARGE(range,1) // 1st largest =LARGE(range,2) // 2nd largest =LARGE(range,3) // 3rd largest
Ebben a példában a LARGE funkciót használhatjuk a legmagasabb pontszám eléréséhez, majd a pontszámot "kulcsként" használhatjuk a társított név beolvasására az INDEX és a MATCH segítségével. Figyelje meg, hogy az n értékeit felvesszük az F5: F7 tartományból, hogy megszerezzük az 1., 2. és 3. legmagasabb pontszámot.
A fordulat azonban ebben az esetben az, hogy meg kell különböztetnünk az A és a B csoport eredményeit. Más szavakkal, kritériumokat kell alkalmaznunk. Ezt az IF függvénnyel tesszük, amelyet az értékek "szűrésére" használnak, mielőtt azokat a LARGE-vel kiértékelik. Általános példa: a 2. tartomány legnagyobb értékének (azaz 1. értékének) megszerzéséhez, ahol az 1. tartomány = "A", használhat egy következő képletet:
LARGE(IF(range="A",range2),1)
Megjegyzés: az IF ilyen módon történő használata tömbképletté teszi ezt.
Belülről kifelé haladva az első lépés az "1." legnagyobb érték megadása az A csoporthoz tartozó adatokban a LARGE funkcióval:
LARGE(IF(group="A",score),F5)
Ebben az esetben, az értéket F5 1, így kérünk a felső pontszám Csoport A. Ha a HA függvény értékelik, ez a vizsgálat minden egyes érték a megnevezett tartományban csoport . A megnevezett tartomány pontszáma megadja az érték_ha_az igaz értéket. Ez egy új tömböt generál, amelyet közvetlenül a LARGE függvény hoz vissza:
LARGE((79;FALSE;93;FALSE;83;FALSE;67;FALSE;85;FALSE;69;FALSE),1)
Figyelje meg, hogy a szűrő egyetlen pontszáma az A csoportból származik. A NAGY ezután a legmagasabb fennmaradó pontszámot (93) közvetlenül a MATCH függvénynek adja vissza keresési értékként. Most egyszerűsíthetjük a képletet:
=INDEX(name,MATCH(93,IF(group="A",score),0))
Most láthatjuk, hogy a MATCH függvény ugyanabban a szűrt tömbben van konfigurálva, mint fent. Az IF függvény ismét kiszűri a nem kívánt értékeket, és a képlet MATCH része feloldódik:
MATCH(93,(79;FALSE;93;FALSE;83;FALSE;67;FALSE;85;FALSE;69;FALSE),0)
Mivel a 93 a 3. pozícióban jelenik meg, a MATCH 3-at közvetlenül az INDEX függvénynek ad vissza:
=INDEX(name,3) // Hannah
Végül az INDEX függvény visszaadja a 3. sorban szereplő nevet: "Hannah".
XLOOKUP-tal
Az XLOOKUP függvény szintén használható a probléma megoldására, a fentiekben ismertetett megközelítés alkalmazásával:
=XLOOKUP(LARGE(IF(group="A",score),F5),IF(group="A",score),name)
A fentiekhez hasonlóan a LARGE úgy van konfigurálva, hogy egy IF által szűrt tömbmel működjön, és a 93 eredményt adja vissza az XLOOKUP-nak keresési értékként:
=XLOOKUP(93,IF(group="A",score),name) // Hannah
A keresési tömböt úgy is létrehozzák, hogy az IF-t szűrőként használják az A csoport eredményeinek szűrésére. A visszatérő tömb névként van megadva (B5: B16). Az XLOOKUP a "Hannah" -t adja vissza végeredményként.
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.
