Stáhněte si ukázkový sešit
Tento tutoriál předvede, jak najít největší hodnotu buňky, která splňuje konkrétní podmínky v Excelu a Tabulkách Google.
Funkce Max If Array
Funkce MAX identifikuje největší hodnotu v řadě čísel.
Funkci MAX v kombinaci s funkcí IF můžeme použít k identifikaci největší hodnoty, která splňuje zadanou podmínku.
Uživatelům Tabulek Google a Excelu 2022 nebo novějších se doporučuje používat jednodušší funkci MAXIFS. To je vysvětleno v pozdější části.
Tento příklad použije funkce MAX a IF ve vzorci pole k identifikaci největšího Velikost objednávky pro každého Jméno obchodu
| 1 | {= MAX (IF (B3: B8 = "A", D3: D8))} |
V Office 365 a verzích Excelu po roce 2022 můžete výše uvedený vzorec jednoduše zadat jako obvykle (stisknutím klávesy ENTER).
V aplikaci Excel 2022 a dřívější však musíte vzorec zadat stisknutím kláves CTRL + SHIFT + ENTER. Poté si všimnete, že se kolem vzorce objevují složené závorky pole.
Abychom ukázali, jak tento vzorec funguje, rozdělme si ho na kroky.
Toto je náš konečný vzorec (zobrazen bez automaticky přidaných závorek vzorců pole):
| 1 | = MAX (IF (B3: B8 = "A", D3: D8)) |
Nejprve se do vzorce přidají hodnoty rozsahu buněk jako pole:
| 1 | = MAX (IF ({"A"; "B"; "A"; "B"; "A"; "B"} = "A", {500; 400; 300; 700; 600; 200})) |
Další Jméno obchodu = Podmínka „A“ vytvoří řadu hodnot PRAVDA/NEPRAVDA:
| 1 | = MAX (IF ({TRUE; FALSE; TRUE; FALSE; TRUE; FALSE}, {500; 400; 300; 700; 600; 200})) |
Poté funkce IF změní všechny TRUE hodnoty na relevantní Velikost objednávky:
| 1 | = MAX ({500; NEPRAVDA; 300; NEPRAVDA; 600; NEPRAVDA}) |
Funkce MAX identifikuje největší číslo v poli, přičemž ignoruje všechny NEPRAVDIVÉ hodnoty a ukazuje největší Velikost objednávky pro Jméno obchodu = „A“:
| 1 | =600 |
Max If - více kritérií
Největší hodnotu můžeme také identifikovat na základě více kritérií pomocí booleovské logiky.
Tento příklad ukáže největší Velikost objednávky pro každého Jméno obchodu, ale pro Termíny objednávek před 30/4/2021 pomocí funkcí MAX, IF a DATE:
| 1 | {= MAX (IF ((B3: B8 = "A")*(C3: C8 |
Všimněte si zde, že společně vynásobíme dvě sady kritérií PRAVDA/NEPRAVDA:
| 1 | (B3: B8 = "A") * (C3: C8 |
Pokud jsou obě kritéria PRAVDA, pak se celková podmínka vypočítá jako PRAVDA, ale pokud je jedno (nebo více) kritérií NEPRAVDA, vypočítá se jako NEPRAVDA.
Pomocí této metodiky je možné do tohoto vzorce přidat mnoho různých kritérií.
Max If - více kritérií s odkazy na buňky
Obvykle není dobrým zvykem zakódovat hodnoty do vzorců. Místo toho je flexibilnější použít k definování kritérií samostatné buňky.
Aby odpovídal Jméno obchodu na hodnotu uvedenou ve sloupci F můžeme vzorec aktualizovat takto:
| 1 | {= MAX (IF ((B3: B8 = F3)*(C3: C8 |
Zamykání odkazů na buňky
Aby byly naše vzorce čitelnější, ukázali jsme vzorce bez zamčených odkazů na buňky:
| 1 | {= MAX (IF ((B3: B8 = F3)*(C3: C8 |
Tyto vzorce ale nebudou správně fungovat, když je zkopírujete a vložíte jinam do souboru. Místo toho byste měli použít uzamčené odkazy na buňky takto:
| 1 | {= MAX (IF (($ B $ 3: $ B $ 8 = F3)**$ C $ 3: $ C $ 8 |
Další informace najdete v našem článku o zamykání odkazů na buňky.
Funkce MAXIFS
Uživatelé tabulek Google a Excelu 2022 nebo novějších mohou pomocí jediné funkce MAXIFS replikovat chování funkcí MAX a IF uvedených v předchozích příkladech.
Tento další příklad používá funkce MAXIFS a DATE k zobrazení největšího Velikost objednávky pro každého Jméno obchodu pro Termíny objednávek před 30/4/2021:
| 1 | = MAXIFS (D3: D8, B3: B8, "A", C3: C8, "<" & DATE (2021,4,30)) |
Funkce MAXIFS nevyžaduje, aby uživatel při zadávání vzorce stiskl CTRL + SHIFT + ENTER.
Max If (maximální hodnota s podmínkou) v Tabulkách Google
Výše uvedené příklady fungují v Tabulkách Google úplně stejně jako v Excelu, ale protože je k dispozici funkce MAXIFS, doporučuje se místo kombinace funkcí MAX a IF použít tuto jedinou funkci.
Pokud jsou použity příklady využívající funkce MAX a IF, pak Tabulky Google vyžadují, abyste je zadali jako maticové vzorce. Namísto zobrazování vzorce pomocí závorkových maticových závorek pole {} stisknutím CTRL + SHIFT + ENTER automaticky přidá funkci ARRAYFORMULA kolem vzorce:
| 1 | = ARRAYFORMULA (MAX (IF ((B3: B8 = "A")*(C3: C8) |
