Stiahnite si ukážkový zošit
Tento tutoriál predvedie, ako nájsť najväčšiu hodnotu bunky, ktorá spĺňa konkrétne podmienky v Exceli a Tabuľkách Google.
Funkcia Max If Array
Funkcia MAX identifikuje najväčšiu hodnotu zo série čísel.
Na identifikáciu najväčšej hodnoty, ktorá spĺňa zadanú podmienku, môžeme použiť funkciu MAX v kombinácii s funkciou IF.
Používateľom Tabuliek Google a Excelu 2022 alebo novšieho sa odporúča používať jednoduchšiu funkciu MAXIFS. Toto je vysvetlené v neskoršej časti.
Tento príklad použije funkcie MAX a IF vo vzorci poľa na identifikáciu najväčších Veľkosť objednávky pre každý Názov obchodu
| 1 | {= MAX (IF (B3: B8 = "A", D3: D8))} |
V Office 365 a verziách Excelu po roku 2022 môžete vyššie uvedený vzorec jednoducho zadať ako obvykle (stlačením klávesu ENTER).
V programe Excel 2022 a staršom však musíte vzorec zadať stlačením klávesov CTRL + SHIFT + ENTER. Potom si všimnete, že okolo vzorca sa zobrazujú zátvorky kučeravých polí.
Aby sme ukázali, ako tento vzorec funguje, rozoberme si ho na kroky.
Toto je náš konečný vzorec (zobrazený bez automaticky pridaných zátvoriek vzorcov poľa):
| 1 | = MAX (IF (B3: B8 = "A", D3: D8)) |
Najprv sa do vzorca pridajú hodnoty rozsahu buniek ako polia:
| 1 | = MAX (IF ({„A“; „B“; „A“; „B“; „A“; „B“} = „A“, {500; 400; 300; 700; 600; 200})) |
Ďalej Názov obchodu = Podmienka „A“ vytvára pole hodnôt PRAVDA/NEPRAVDA:
| 1 | = MAX (IF ({TRUE; FALSE; TRUE; FALSE; TRUE; FALSE}, {500; 400; 300; 700; 600; 200})) |
Potom funkcia IF zmení všetky TRUE hodnoty na relevantné Veľkosť objednávky:
| 1 | = MAX ({500; FALSE; 300; FALSE; 600; FALSE}) |
Funkcia MAX identifikuje najväčšie číslo v poli, pričom ignoruje všetky NEPRAVDIVÉ hodnoty a ukazuje najväčšie Veľkosť objednávky pre Názov obchodu = „A“:
| 1 | =600 |
Max If - viac kritérií
Tiež môžeme identifikovať najväčšiu hodnotu na základe viacerých kritérií pomocou booleovskej logiky.
Tento príklad ukáže najväčší Veľkosť objednávky pre každý Názov obchodu, ale pre Termíny objednávok do 30/4/2021 pomocou funkcií MAX, IF a DATE:
| 1 | {= MAX (IF ((B3: B8 = "A")*(C3: C8 |
Všimnite si tu, že vynásobíme dve sady TRUE/FALSE kritérií spoločne:
| 1 | (B3: B8 = "A") * (C3: C8 |
Ak sú obe kritériá PRAVDIVÉ, celková podmienka sa vypočíta ako PRAVDA, ale ak je jedno (alebo viac) kritérií NEPRAVDA, vypočíta sa ako NEPRAVDA.
Pomocou tejto metodiky je možné do tohto vzorca pridať mnoho rôznych kritérií.
Max If - viac kritérií s referenciami buniek
Obvykle nie je vhodné zafixovať hodnoty do vzorcov. Namiesto toho je flexibilnejšie použiť na definovanie kritérií samostatné bunky.
Aby zodpovedali Názov obchodu na hodnotu uvedenú v stĺpci F môžeme vzorec aktualizovať takto:
| 1 | {= MAX (IF ((B3: B8 = F3)*(C3: C8 |
Zamknutie odkazov na bunky
Aby boli naše vzorce ľahšie čitateľné, ukázali sme vzorce bez uzamknutých odkazov na bunky:
| 1 | {= MAX (IF ((B3: B8 = F3)*(C3: C8 |
Tieto vzorce však nebudú fungovať správne, keď ich skopírujete a vložíte na iné miesto v súbore. Namiesto toho by ste mali použiť uzamknuté odkazy na bunky takto:
| 1 | {= MAX (IF (($ B $ 3: $ B $ 8 = F3)**$ C $ 3: $ C $ 8 |
Ak sa chcete dozvedieť viac, prečítajte si náš článok o uzamknutí odkazov na bunky.
Funkcia MAXIFS
Používatelia Tabuliek Google a Excelu 2022 alebo novšieho môžu pomocou jedinej funkcie MAXIFS replikovať správanie funkcií MAX a IF uvedených v predchádzajúcich príkladoch.
Tento nasledujúci príklad používa funkcie MAXIFS a DATE na zobrazenie najväčších Veľkosť objednávky pre každý Názov obchodu pre Termíny objednávok pred 30/4/2021:
| 1 | = MAXIFS (D3: D8, B3: B8, "A", C3: C8, "<" & DATE (2021,4,30)) |
Funkcia MAXIFS nevyžaduje, aby používateľ pri zadávaní vzorca stlačil CTRL + SHIFT + ENTER.
Max If (maximálna hodnota s podmienkou) v Tabuľkách Google
Vyššie uvedené príklady fungujú v Tabuľkách Google úplne rovnako ako v programe Excel, ale keďže je k dispozícii funkcia MAXIFS, odporúča sa namiesto kombinovania funkcií MAX a IF používať túto jednu funkciu.
Ak sú potrebné príklady, ktoré používajú funkcie MAX a IF, potom Tabuľky Google vyžadujú, aby ste ich zadali ako vzorce poľa. Namiesto zobrazovania vzorca v zátvorkách excelovského zloženého poľa {} stlačením CTRL + SHIFT + ENTER automaticky pridáte funkciu ARRAYFORMULA okolo vzorca:
| 1 | = ARRAYFORMULA (MAX (IF ((B3: B8 = "A"))*(C3: C8 |
