Všeobecný vzorec
(=MAX(IF(rng1=criteria1,IF(rng2=criteria2,values))))
Zhrnutie
Ak chcete získať maximálnu hodnotu v množine údajov na základe viac ako jedného kritéria, môžete použiť vzorec poľa založený na funkciách MAX a IF. V zobrazenom príklade je vzorec v I6:
(=MAX(IF(color=G6,IF(item=H6,price))))
Pri farbe „červená“ a položke „klobúk“ je výsledok 11,00 dolárov
Poznámka: Toto je vzorec poľa a musí sa zadávať pomocou klávesov Ctrl + Shift +
Vysvetlenie
V tomto príklade sa používajú tieto pomenované rozsahy: „color“ = B6: B14, „item“ = C6: C14 a „price“ = E6: E14. Cieľom je nájsť maximálnu cenu pre danú farbu a položku.
Tento vzorec používa dve vnorené funkcie IF zabalené vo vnútri MAX, aby vrátili maximálnu cenu s dvoma kritériami. Počnúc logickým testom prvého príkazu IF, color = G6, sa hodnoty v pomenovanom rozsahu „color“ (B6: B14) porovnajú s hodnotou v bunke G6 „red“. Výsledkom je pole ako je toto:
(TRUE;TRUE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE)
V logickom teste pre druhý príkaz IF, item = H6, sa hodnoty v pomenovanej položke rozsahu (C6: C14) porovnajú s hodnotou v bunke H6 „hat“. Výsledkom je pole ako je toto:
(TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;FALSE;FALSE;FALSE)
„Hodnota, ak je to pravda“ pre druhý príkaz IF, je pomenovaný rozsah „cien“ (E6: E14), čo je pole podobné tomuto:
(11;8;9;12;9;10;9;8;7)
Cena sa vracia za každú položku v tomto rozsahu, iba ak je výsledok prvých dvoch polí vyššie TRUE pre položky na zodpovedajúcich pozíciách. V zobrazenom príklade vyzerá konečné pole vo vnútri MAX takto:
(11;8;9;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE)
Všimnite si, že jediné ceny, ktoré „prežijú“, sú ceny v pozícii, keď je farba „červená“ a položka „klobúk“.
Funkcia MAX potom vráti najvyššiu cenu a FALŠNÉ hodnoty automaticky ignoruje.
Alternatívna syntax pomocou logickej logiky
Môžete tiež použiť nasledujúci vzorec poľa, ktorý používa iba jednu funkciu IF spolu s logickou logikou:
(=MAX(IF((color=G6)*(item=H6),price)))
Výhodou tejto syntaxe je, že je ľahšie pridať ďalšie kritériá bez pridania ďalších vnorených funkcií IF. Ak potrebujete logiku ALEBO, použite násobenie medzi podmienkami.
S MAXIFS
Funkcia MAXIFS zavedená v programe Excel 2016 je navrhnutá na výpočet maximálnych hodnôt na základe jedného alebo viacerých kritérií bez potreby maticového vzorca. Pri MAXIFS je vzorec v I6:
=MAXIFS(price,color,G6,item,H6)
Poznámka: MAXIFS bude automaticky ignorovať prázdne bunky, ktoré spĺňajú kritériá. Inými slovami, MAXIFS nebude považovať prázdne bunky, ktoré spĺňajú kritériá, za nulové. Na druhej strane funkcia MAXIFS vráti nulu (0), ak žiadne bunky nezodpovedajú kritériám.
