Obecný vzorec
=MAX(INDEX(data,0,MATCH(column,header,0)))
souhrn
Chcete-li načíst maximální hodnotu v sadě dat, kde je sloupec proměnný, můžete použít INDEX a MATCH společně s funkcí MAX. V zobrazeném příkladu je vzorec v J5:
=MAX(INDEX(data,0,MATCH(J4,header,0)))
kde data (B5: F15) a záhlaví (B4: F4) jsou pojmenované rozsahy.
Vysvětlení
Poznámka: Pokud s INDEX a MATCH začínáte, přečtěte si: Jak používat INDEX a MATCH
Ve standardní konfiguraci funkce INDEX načte hodnotu v daném řádku a sloupci. Například pro získání hodnoty na řádku 2 a sloupci 3 v daném rozsahu:
=INDEX(range,2,3) // get value at row 2, column 3
INDEX však má speciální trik - schopnost načítat celé sloupce a řádky. Syntaxe zahrnuje zadání nuly pro argument „other“. Pokud chcete celý sloupec, zadejte řádek jako nulu. Pokud chcete celý řádek, zadejte sloupec jako nulu:
=INDEX(data,0,n) // retrieve column n =INDEX(data,n,0) // retrieve row n
V zobrazeném příkladu chceme najít maximální hodnotu v daném sloupci. Zvrat je v tom, že sloupec musí být variabilní, aby jej bylo možné snadno změnit. V F5 je vzorec:
=MAX(INDEX(data,0,MATCH(J4,header,0)))
Při práci zevnitř ven nejprve použijeme funkci MATCH k získání „indexu“ sloupce požadovaného v buňce J4:
MATCH(J4,header,0) // get column index
S "zelenou" v J4 vrátí funkce MATCH hodnotu 3, protože zelená je třetí hodnota v pojmenované hlavičce rozsahu . Poté, co MATCH vrátí výsledek, lze vzorec zjednodušit na toto:
=MAX(INDEX(data,0,3))
S nulou uvedenou jako číslo řádku vrátí INDEX všechny hodnoty ve sloupci 3 pojmenovaných dat rozsahu . Výsledek se vrací do funkce MAX v takovém poli:
=MAX((83;54;35;17;85;16;70;72;65;93;91))
A MAX vrátí konečný výsledek, 93.
Minimální hodnota
Chcete-li získat minimální hodnotu s proměnným sloupcem, můžete jednoduše nahradit funkci MAX funkcí MIN. Vzorec v J6 je:
=MIN(INDEX(data,0,MATCH(J4,header,0)))
S FILTREM
K vyřešení tohoto problému lze také použít novou funkci FILTER, protože FILTER může filtrovat data podle řádku nebo sloupce. Trik spočívá v konstrukci logického filtru, který vylučuje další sloupce. COUNTIF v tomto případě funguje dobře, ale musí být nakonfigurován „zpět“, s rozsahem J4 a hlavičkou pro kritéria:
=MAX(FILTER(data,COUNTIF(J4,header)))
Po spuštění COUNTIF máme:
=MAX(FILTER(data,(0,0,1,0,0)))
A FILTER dodává 3. sloupec MAX, stejně jako výše uvedená funkce INDEX.
Jako alternativu k COUNTIF můžete místo toho použít ISNUMBER + MATCH:
=MAX(FILTER(data,ISNUMBER(MATCH(header,J4,0))))
Funkce MATCH je opět nastavena „zpět“, takže dostaneme pole s 5 hodnotami, které bude sloužit jako logický filtr. Po spuštění ISNUMBER a MATCH máme:
=MAX(FILTER(data,(FALSE,FALSE,TRUE,FALSE,FALSE)))
A FILTER opět dodává 3. sloupec MAX.
