Generička formula
=MAX(INDEX(data,0,MATCH(column,header,0)))
Sažetak
Da biste dohvatili maksimalnu vrijednost u skupu podataka, gdje je stupac promjenjiv, možete upotrijebiti INDEX i MATCH zajedno s MAX funkcijom. U prikazanom primjeru formula u J5 je:
=MAX(INDEX(data,0,MATCH(J4,header,0)))
gdje su podaci (B5: F15) i zaglavlje (B4: F4) imenovani rasponi.
Obrazloženje
Napomena: Ako ste novi u INDEX-u i MATCH-u, pogledajte: Kako koristiti INDEX i MATCH
U standardnoj konfiguraciji funkcija INDEX dohvaća vrijednost u danom retku i stupcu. Na primjer, da biste dobili vrijednost u retku 2 i stupcu 3 u danom rasponu:
=INDEX(range,2,3) // get value at row 2, column 3
Međutim, INDEX ima poseban trik - mogućnost dohvaćanja cijelih stupaca i redaka. Sintaksa uključuje nulu za argument "drugi". Ako želite cijeli stupac, red navodite kao nulu. Ako želite cijeli redak, stupac navodite kao nulu:
=INDEX(data,0,n) // retrieve column n =INDEX(data,n,0) // retrieve row n
U prikazanom primjeru želimo pronaći maksimalnu vrijednost u danom stupcu. Zaokret je taj da stupac mora biti promjenjiv kako bi se mogao lako mijenjati. U F5, formula je:
=MAX(INDEX(data,0,MATCH(J4,header,0)))
Radeći iznutra prema van, prvo koristimo funkciju MATCH da bismo dobili "indeks" stupca traženog u ćeliji J4:
MATCH(J4,header,0) // get column index
Sa "Green" u J4, funkcija MATCH vraća 3, jer je Green treća vrijednost u imenovanom zaglavlju raspona . Nakon što MATCH vrati rezultat, formula se može pojednostaviti na ovo:
=MAX(INDEX(data,0,3))
S nulom navedenom kao broj retka, INDEX vraća sve vrijednosti u stupcu 3 imenovanih podataka raspona . Rezultat se vraća u funkciju MAX u nizu poput ovog:
=MAX((83;54;35;17;85;16;70;72;65;93;91))
I MAX vraća konačni rezultat, 93.
Minimalna vrijednost
Da biste dobili minimalnu vrijednost s promjenjivim stupcem, možete jednostavno zamijeniti funkciju MAX funkcijom MIN. Formula u J6 je:
=MIN(INDEX(data,0,MATCH(J4,header,0)))
S FILTROM
Nova funkcija FILTER također se može koristiti za rješavanje ovog problema, jer FILTER može filtrirati podatke po retku ili stupcu. Trik je u izradi logičkog filtra koji će izuzeti ostale stupce. COUNTIF u ovom slučaju dobro funkcionira, ali mora biti konfiguriran "unatrag", s J4 kao rasponom i zaglavljem za kriterije:
=MAX(FILTER(data,COUNTIF(J4,header)))
Nakon pokretanja COUNTIF-a imamo:
=MAX(FILTER(data,(0,0,1,0,0)))
A FILTER isporučuje treći stupac na MAX, isto kao i gornja funkcija INDEX.
Kao zamjenu za COUNTIF, umjesto toga možete upotrijebiti ISUMBER + MATCH:
=MAX(FILTER(data,ISNUMBER(MATCH(header,J4,0))))
Funkcija MATCH se ponovno postavlja "unatrag", tako da dobivamo niz s 5 vrijednosti koji će služiti kao logički filtar. Nakon ISNUMBER i MATCH trčanja imamo:
=MAX(FILTER(data,(FALSE,FALSE,TRUE,FALSE,FALSE)))
I FILTER ponovno isporučuje 3. stupac na MAX.
