Všeobecný vzorec
=MAX(INDEX(data,0,MATCH(column,header,0)))
Zhrnutie
Ak chcete načítať maximálnu hodnotu v množine údajov, kde je stĺpec premenlivý, môžete použiť INDEX a MATCH spolu s funkciou MAX. V zobrazenom príklade je vzorec v J5:
=MAX(INDEX(data,0,MATCH(J4,header,0)))
kde údaje (B5: F15) a hlavička (B4: F4) sú pomenované rozsahmi.
Vysvetlenie
Poznámka: Ak ste v indexoch INDEX a MATCH nováčikom, pozrite si: Ako používať INDEX a MATCH
V štandardnej konfigurácii funkcia INDEX načíta hodnotu v danom riadku a stĺpci. Napríklad pre získanie hodnoty v riadku 2 a stĺpci 3 v danom rozsahu:
=INDEX(range,2,3) // get value at row 2, column 3
INDEX má však špeciálny trik - schopnosť načítať celé stĺpce a riadky. Syntax zahŕňa zadanie nuly pre argument „iné“. Ak chcete celý stĺpec, zadajte riadok ako nulu. Ak chcete celý riadok, zadajte stĺpec ako nulu:
=INDEX(data,0,n) // retrieve column n =INDEX(data,n,0) // retrieve row n
V zobrazenom príklade chceme nájsť maximálnu hodnotu v danom stĺpci. Zápletka spočíva v tom, že stĺpec musí byť variabilný, aby sa dal ľahko meniť. Vo F5 je vzorec:
=MAX(INDEX(data,0,MATCH(J4,header,0)))
Keď pracujeme zvnútra von, najskôr použijeme funkciu MATCH na získanie „indexu“ stĺpca požadovaného v bunke J4:
MATCH(J4,header,0) // get column index
Keď je „zelená“ v J4, funkcia MATCH vráti 3, pretože zelená je tretia hodnota v pomenovanej hlavičke rozsahu . Keď MATCH vráti výsledok, vzorec sa dá zjednodušiť takto:
=MAX(INDEX(data,0,3))
S nulou ako číslom riadku vráti INDEX všetky hodnoty v stĺpci 3 pomenovaných údajov rozsahu . Výsledok sa vráti do funkcie MAX v takom poli:
=MAX((83;54;35;17;85;16;70;72;65;93;91))
A MAX vráti konečný výsledok, 93.
Minimálna hodnota
Ak chcete získať minimálnu hodnotu s premenlivým stĺpcom, môžete jednoducho nahradiť funkciu MAX funkciou MIN. Vzorec v J6 je:
=MIN(INDEX(data,0,MATCH(J4,header,0)))
S FILTEROM
Na vyriešenie tohto problému možno použiť aj novú funkciu FILTER, pretože FILTER dokáže filtrovať údaje podľa riadkov alebo stĺpcov. Trik spočíva v zostrojení logického filtra, ktorý vylúči ďalšie stĺpce. COUNTIF v tomto prípade funguje dobre, ale musí byť nakonfigurovaný „dozadu“, s rozsahom J4 a hlavičkou kritérií:
=MAX(FILTER(data,COUNTIF(J4,header)))
Po spustení COUNTIF máme:
=MAX(FILTER(data,(0,0,1,0,0)))
A FILTER dodáva tretí stĺpec na MAX, rovnako ako vyššie uvedená funkcia INDEX.
Ako alternatívu k COUNTIF môžete namiesto toho použiť ISNUMBER + MATCH:
=MAX(FILTER(data,ISNUMBER(MATCH(header,J4,0))))
Funkcia MATCH je opäť nastavená „dozadu“, takže dostaneme pole s 5 hodnotami, ktoré bude slúžiť ako logický filter. Po spustení ISNUMBER a MATCH máme:
=MAX(FILTER(data,(FALSE,FALSE,TRUE,FALSE,FALSE)))
A FILTER opäť dodáva 3. stĺpec na MAX.
