Obecný vzorec
(=MAX(IF(rng1=criteria1,IF(rng2=criteria2,values))))
souhrn
Chcete-li získat maximální hodnotu v datové sadě na základě více než jednoho kritéria, můžete použít maticový vzorec založený na funkcích MAX a IF. V zobrazeném příkladu je vzorec v I6:
(=MAX(IF(color=G6,IF(item=H6,price))))
Při barvě „červená“ a položce „klobouk“ je výsledek 11,00 $
Poznámka: Toto je maticový vzorec a musí být zadán pomocí Ctrl + Shift + zadán
Vysvětlení
Tento příklad používá následující pojmenované rozsahy: „color“ = B6: B14, „item“ = C6: C14 a „price“ = E6: E14. Cílem je najít maximální cenu pro danou barvu a předmět.
Tento vzorec používá dvě vnořené funkce IF zabalené uvnitř MAX, aby vrátila maximální cenu se dvěma kritérii. Počínaje logickým testem prvního příkazu IF, color = G6, jsou hodnoty v pojmenovaném rozsahu „color“ (B6: B14) porovnány s hodnotou v buňce G6, „red“. Výsledkem je pole jako toto:
(TRUE;TRUE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE)
V logickém testu pro druhý příkaz IF item = H6 jsou hodnoty v pojmenované položce rozsahu (C6: C14) porovnány s hodnotou v buňce H6, „hat“. Výsledkem je pole jako toto:
(TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;FALSE;FALSE;FALSE)
„Hodnota, pokud je to pravda“ pro 2. příkaz IF je pojmenovaný rozsah „cen“ (E6: E14), což je pole podobné tomuto:
(11;8;9;12;9;10;9;8;7)
Cena se vrací za každou položku v tomto rozsahu, pouze když je výsledek prvních dvou polí výše TRUE pro položky na odpovídajících pozicích. V ukázaném příkladu vypadá konečné pole uvnitř MAX takto:
(11;8;9;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE)
Jediné ceny, které „přežijí“, jsou ceny v pozici, kde je barva „červená“ a položka „klobouk“.
Funkce MAX poté vrátí nejvyšší cenu a automaticky ignoruje FALSE hodnoty.
Alternativní syntaxe využívající logickou logiku
Můžete také použít následující vzorec pole, který používá pouze jednu funkci IF spolu s logickou logikou:
(=MAX(IF((color=G6)*(item=H6),price)))
Výhodou této syntaxe je, že je snazší přidat další kritéria bez přidání dalších vnořených funkcí IF. Pokud potřebujete logiku NEBO, použijte sčítání namísto násobení mezi podmínkami.
S MAXIFY
Funkce MAXIFS zavedená v aplikaci Excel 2016 je navržena k výpočtu maxim na základě jednoho nebo více kritérií bez potřeby maticového vzorce. U MAXIFS je vzorec v I6:
=MAXIFS(price,color,G6,item,H6)
Poznámka: MAXIFS bude automaticky ignorovat prázdné buňky, které splňují kritéria. Jinými slovy, MAXIFS nebude považovat prázdné buňky, které splňují kritéria, za nulové. Na druhou stranu MAXIFS vrátí nulu (0), pokud žádné buňky neodpovídají kritériím.
