Splošna formula
=MAX(INDEX(data,0,MATCH(column,header,0)))
Povzetek
Če želite pridobiti največjo vrednost v naboru podatkov, kjer je stolpec spremenljiv, lahko uporabite INDEX in MATCH skupaj s funkcijo MAX. V prikazanem primeru je formula v J5:
=MAX(INDEX(data,0,MATCH(J4,header,0)))
kjer so podatki (B5: F15) in glava (B4: F4) imenovani obsegi.
Pojasnilo
Opomba: Če še ne uporabljate INDEX in MATCH, glejte: Kako uporabljati INDEX in MATCH
V standardni konfiguraciji funkcija INDEX pridobi vrednost v dani vrstici in stolpcu. Če želite na primer dobiti vrednost v vrstici 2 in stolpcu 3 v določenem obsegu:
=INDEX(range,2,3) // get value at row 2, column 3
Vendar ima INDEX poseben trik - možnost pridobivanja celotnih stolpcev in vrstic. Sintaksa vključuje podajanje ničle za argument "drugo". Če želite cel stolpec, vrstico navedete kot nič. Če želite celo vrstico, stolpec navedete kot nič:
=INDEX(data,0,n) // retrieve column n =INDEX(data,n,0) // retrieve row n
V prikazanem primeru želimo najti največjo vrednost v danem stolpcu. Zasuk je, da mora biti stolpec spremenljiv, da ga je mogoče enostavno spremeniti. V F5 je formula:
=MAX(INDEX(data,0,MATCH(J4,header,0)))
Če delamo od znotraj navzven, najprej uporabimo funkcijo MATCH, da dobimo "indeks" stolpca, zahtevanega v celici J4:
MATCH(J4,header,0) // get column index
Z "Green" v J4 funkcija MATCH vrne 3, saj je Green tretja vrednost v imenovani glavi obsega . Ko MATCH vrne rezultat, lahko formulo poenostavimo na to:
=MAX(INDEX(data,0,3))
Če je številka vrstice navedena kot nič, INDEX vrne vse vrednosti v stolpcu 3 imenovanih podatkov obsega . Rezultat se vrne v funkcijo MAX v matriki, kot je ta:
=MAX((83;54;35;17;85;16;70;72;65;93;91))
In MAX vrne končni rezultat, 93.
Najmanjša vrednost
Če želite dobiti minimalno vrednost s spremenljivim stolpcem, lahko preprosto nadomestite funkcijo MAX s funkcijo MIN. Formula v J6 je:
=MIN(INDEX(data,0,MATCH(J4,header,0)))
S FILTROM
Za rešitev te težave je mogoče uporabiti tudi novo funkcijo FILTER, saj lahko FILTER podatke filtrira po vrsticah ali stolpcih. Trik je v izdelavi logičnega filtra, ki bo izključil druge stolpce. COUNTIF v tem primeru dobro deluje, vendar ga je treba konfigurirati "nazaj", z J4 kot obsegom in glavo za kriterije:
=MAX(FILTER(data,COUNTIF(J4,header)))
Po zagonu COUNTIF imamo:
=MAX(FILTER(data,(0,0,1,0,0)))
In FILTER dostavi 3. stolpec na MAX, enako kot zgornja funkcija INDEX.
Namesto COUNTIF lahko namesto tega uporabite ISNUMBER + MATCH:
=MAX(FILTER(data,ISNUMBER(MATCH(header,J4,0))))
Funkcija MATCH je spet nastavljena "nazaj", tako da dobimo matriko s 5 vrednostmi, ki bo služila kot logični filter. Po teku ISNUMBER in MATCH imamo:
=MAX(FILTER(data,(FALSE,FALSE,TRUE,FALSE,FALSE)))
In FILTER ponovno dostavi 3. stolpec na MAX.
