Általános képlet
=MAX(INDEX(data,0,MATCH(column,header,0)))
Összegzés
Ha meg szeretné kapni a maximális értéket egy adatsorban, ahol az oszlop változó, használhatja az INDEX és a MATCH funkciókat a MAX funkcióval együtt. A bemutatott példában a J5 képlete a következő:
=MAX(INDEX(data,0,MATCH(J4,header,0)))
ahol az adatok (B5: F15) és a fejléc (B4: F4) tartományok.
Magyarázat
Megjegyzés: Ha még nem ismeri az INDEX és a MATCH alkalmazást, olvassa el: Az INDEX és a MATCH használata
Normál konfigurációban az INDEX függvény lekér egy értéket egy adott sorban és oszlopban. Például, hogy megkapja az értéket a 2. sorban és a 3. oszlopban egy adott tartományban:
=INDEX(range,2,3) // get value at row 2, column 3
Az INDEX-nek azonban van egy speciális trükkje - a teljes oszlopok és sorok lekérésének képessége. A szintaxis magában foglalja a nulla megadását az "egyéb" argumentumhoz. Ha egy teljes oszlopot szeretne, akkor a sort nullaként adja meg. Ha egy teljes sort szeretne, akkor az oszlopot nullának adja meg:
=INDEX(data,0,n) // retrieve column n =INDEX(data,n,0) // retrieve row n
A bemutatott példában meg akarjuk találni a maximális értéket egy adott oszlopban. A fordulat az, hogy az oszlopnak változónak kell lennie, így könnyen megváltoztatható. Az F5-ben a képlet:
=MAX(INDEX(data,0,MATCH(J4,header,0)))
Belülről kifelé haladva először a MATCH függvényt használjuk, hogy megkapjuk a J4 cellában kért oszlop "indexét":
MATCH(J4,header,0) // get column index
A „zöld” a J4, a match függvény 3, mivel Zöld a harmadik érték a névvel ellátott tartomány fejléc . Miután a MATCH visszaad egy eredményt, a képlet erre egyszerűsíthető:
=MAX(INDEX(data,0,3))
Ha nulla szerepel sorszámként, az INDEX visszaadja az összes értéket a megnevezett tartományadatok 3. oszlopában . Az eredmény egy ilyen tömbben kerül vissza a MAX függvénybe:
=MAX((83;54;35;17;85;16;70;72;65;93;91))
MAX pedig visszaadja a végeredményt, 93-at.
Minimális érték
A változó oszlop minimális értékének megszerzéséhez egyszerűen cserélje le a MAX funkciót a MIN funkcióra. A J6 képlete a következő:
=MIN(INDEX(data,0,MATCH(J4,header,0)))
SZŰRŐ-vel
Az új FILTER funkció szintén használható a probléma megoldására, mivel a FILTER sorok vagy oszlopok szerint szűrheti az adatokat. A trükk egy olyan logikai szűrő létrehozása, amely kizár más oszlopokat. A COUNTIF ebben az esetben jól működik, de "hátrafelé" kell konfigurálni, a J4 a tartomány, és a kritériumok fejléce :
=MAX(FILTER(data,COUNTIF(J4,header)))
A COUNTIF futása után:
=MAX(FILTER(data,(0,0,1,0,0)))
A FILTER pedig elküldi a 3. oszlopot MAX-nak, ugyanúgy, mint a fenti INDEX funkció.
A COUNTIF helyett alternatívaként használhatja az ISNUMBER + MATCH elemet:
=MAX(FILTER(data,ISNUMBER(MATCH(header,J4,0))))
A MATCH függvény ismét "visszafelé" van beállítva, így kapunk egy 5 értékű tömböt, amely logikai szűrőként szolgál. Az ISNUMBER és a MATCH futtatása után:
=MAX(FILTER(data,(FALSE,FALSE,TRUE,FALSE,FALSE)))
És a FILTER ismét elküldi a 3. oszlopot a MAX-nak.
