Funkce IF v Excelu. Příklady (s více podmínkami)

Jednou z hlavních výhod excelových tabulek je možnost naprogramovat funkčnost konkrétního dokumentu. Jak většina lidí ví ze školních hodin informatiky, jednou z hlavních součástí, která vám to umožní uvést do praxe, jsou logické operátory. Jedním z nich je operátor IF, který zajišťuje provedení určitých akcí při splnění určitých podmínek. 

Pokud se například hodnota shoduje s určitou hodnotou, zobrazí se v buňce jeden štítek. Pokud ne, je to jiné. Podívejme se na tento účinný nástroj podrobněji v praxi.

Funkce IF v Excelu (obecné informace)

Každý program, i když je malý, nutně obsahuje sekvenci akcí, která se nazývá algoritmus. Může to vypadat takto:

1. Zkontrolujte celý sloupec A, zda neobsahuje sudá čísla.
2. Pokud je nalezeno sudé číslo, přidejte takové a takové hodnoty.
3. Pokud není nalezeno sudé číslo, zobrazí se nápis „nenalezeno“.
4. Zkontrolujte, zda je výsledné číslo sudé. 
5. Pokud ano, přidejte jej ke všem sudým číslům vybraným v odstavci 1.

A i když se jedná pouze o hypotetickou situaci, která pravděpodobně nebude v reálném životě zapotřebí, provedení jakéhokoli úkolu nutně znamená přítomnost podobného algoritmu. Před použitím funkce LI, musíte mít v hlavě jasnou představu o tom, jakého výsledku chcete dosáhnout. 

Syntaxe funkce IF s jednou podmínkou

Jakákoli funkce v Excelu se provádí pomocí vzorce. Vzor, podle kterého musí být data předána funkci, se nazývá syntaxe. V případě operátora IF, vzorec bude v tomto formátu.

=IF (logický_výraz, hodnota_pokud_pravda, hodnota_pokud_nepravda)

Podívejme se na syntaxi podrobněji:

1. Booleovský výraz. Jedná se o samotnou podmínku, jejíž splnění či nesplnění Excel kontroluje. Lze kontrolovat jak číselné, tak textové informace.
2. Value_if_true. Výsledek, který se zobrazí v buňce, pokud kontrolovaná data splňují zadaná kritéria.
3. value_if_false. Výsledek, který se zobrazí v buňce, pokud kontrolovaná data neodpovídají podmínce.

Zde je pro názornost příklad.

Zde funkce porovná buňku A1 s číslem 20. Toto je první odstavec syntaxe. Pokud je obsah větší než tato hodnota, zobrazí se v buňce, do které byl vzorec zapsán, hodnota „větší než 20“. Pokud situace neodpovídá této podmínce – „menší nebo rovno 20“.

Pokud chcete v buňce zobrazit textovou hodnotu, musíte ji uzavřít do uvozovek.

Zde je další situace. Aby byli studenti způsobilí ke zkoušce, musí projít testem. Studentům se podařilo získat kredity ze všech předmětů a nyní zbývá poslední, který se ukázal jako rozhodující. Naším úkolem je určit, kdo ze studentů je ke zkouškám připuštěn a kdo ne.

Protože chceme kontrolovat text a ne číslo, první argument je B2=”cons.”.

Syntaxe funkce IF s více podmínkami

Často k porovnání hodnoty nestačí jedno kritérium. Pokud potřebujete zvážit více než jednu možnost, můžete funkce vnořit IF jeden do druhého. Bude zde několik vnořených funkcí.

Aby to bylo jasnější, zde je syntaxe.

=KDYŽ(logický_výraz, hodnota_pokud_pravda, KDYŽ(logický_výraz, hodnota_pokud_pravda, hodnota_pokud_nepravda))

V tomto případě bude funkce kontrolovat dvě kritéria najednou. Pokud je první podmínka pravdivá, vrátí se hodnota získaná jako výsledek operace v prvním argumentu. Pokud ne, zkontroluje se splnění druhého kritéria.

Zde je příklad.

A pomocí takového vzorce (zobrazeného na snímku obrazovky níže) můžete analyzovat výkon každého studenta.

Jak vidíte, přibyla zde ještě jedna podmínka, ale princip se nezměnil. Můžete tedy zkontrolovat několik kritérií najednou.

Jak rozšířit funkcionalitu IF pomocí operátorů AND a OR

Čas od času nastane situace, kdy je nutné okamžitě zkontrolovat shodu s několika kritérii a nepoužít logické vnořené operátory, jako v předchozím příkladu. K tomu použijte některou z funkcí И nebo funkci OR podle toho, zda potřebujete splnit několik kritérií najednou nebo alespoň jedno z nich. Pojďme se na tato kritéria podívat blíže.

Funkce IF s podmínkou AND

Někdy je potřeba zkontrolovat výraz pro více podmínek najednou. K tomu se používá funkce AND zapsaná v prvním argumentu funkce IF. Funguje to takto: je-li a rovno jedné a a rovno 2, bude hodnota c.

Funkce IF s podmínkou „NEBO“.

Podobně funguje funkce OR, ale v tomto případě platí pouze jedna z podmínek. Maximálně lze tímto způsobem zkontrolovat až 30 podmínek. 

Zde je několik způsobů použití funkcí И и OR jako argument funkce IF.

Porovnání dat ve dvou tabulkách

Čas od času je možné porovnat dvě podobné tabulky. Člověk například pracuje jako účetní a potřebuje porovnat dvě sestavy. Existují další podobné úkoly, jako je porovnávání nákladů na zboží různých šarží, dále hodnocení studentů za různá období a tak dále.

Chcete-li porovnat dvě tabulky, použijte funkci COUNTIF. Pojďme se na to podívat podrobněji.

Řekněme, že máme dvě tabulky obsahující specifikace dvou kuchyňských robotů. A my je musíme porovnávat a zvýraznit rozdíly barvou. To lze provést pomocí podmíněného formátování a funkce COUNTIF. 

Náš stůl vypadá takto.

Vybíráme sortiment odpovídající technickým vlastnostem prvního kuchyňského robotu.

Poté klikněte na následující nabídky: Podmíněné formátování – vytvořte pravidlo – pomocí vzorce určete formátované buňky.

Ve formě vzorce pro formátování zapíšeme funkci = COUNTIF (rozsah k porovnání; první buňka první tabulky)=0. Tabulka s vlastnostmi druhého kuchyňského robotu slouží jako srovnávací řada.

Musíte se ujistit, že adresy jsou absolutní (se znakem dolaru před názvy řádků a sloupců). Za vzorec přidejte =0, aby Excel hledal přesné hodnoty.

Poté je třeba nastavit formátování buněk. Chcete-li to provést, musíte vedle vzorku kliknout na tlačítko „Formátovat“. V našem případě používáme výplň, protože je pro tento účel nejvýhodnější. Můžete si ale vybrat libovolné formátování.

Jako rozsah jsme přiřadili název sloupce. To je mnohem pohodlnější než ruční zadávání rozsahu.

Funkce SUMIF v Excelu

Nyní přejdeme k funkcím IF, což pomůže nahradit dva body algoritmu najednou. První je SUMMESLEY, který sečte dvě čísla splňující určitou podmínku. Stojíme například před úkolem určit, kolik peněz by se mělo platit měsíčně všem prodejcům. K tomu je to nutné.

1. Přidejte řádek s celkovým příjmem všech prodejců a po zadání vzorce klikněte na buňku, která bude obsahovat výsledek. 
2. Najdeme tlačítko fx, které se nachází vedle řádku pro vzorce. Dále se zobrazí okno, kde najdete potřebnou funkci pomocí vyhledávání. Po výběru operátora musíte kliknout na tlačítko „OK“. Ruční zadání je však vždy možné.
   

   

3. Dále se zobrazí okno pro zadání argumentů funkce. Všechny hodnoty lze zadat v odpovídajících polích a rozsah lze zadat pomocí tlačítka vedle nich.
   

   

4. První argument je rozsah. Zde zadáte buňky, u kterých chcete zkontrolovat shodu s kritérii. Pokud mluvíme o nás, jedná se o pozice zaměstnanců. Zadejte rozsah D4:D18. Nebo jen vyberte buňky, které vás zajímají.
5. Do pole „Kritéria“ zadejte pozici. V našem případě – „prodejce“. Jako součtový rozsah označujeme ty buňky, kde jsou uvedeny platy zaměstnanců (to se provádí ručně i výběrem myší). Klikněte na „OK“ a dostaneme hotové vypočítané mzdy všech zaměstnanců, kteří jsou prodejci.

Souhlaste, že je to velmi pohodlné. Není to ono?

Funkce SUMIFS v Excelu

Tato funkce umožňuje určit součet hodnot, které splňují více podmínek. Dostali jsme například za úkol určit celkovou mzdu všech manažerů působících v jižní pobočce firmy.

Přidejte řádek, kde bude konečný výsledek, a vložte vzorec do požadované buňky. Chcete-li to provést, klikněte na ikonu funkce. Zobrazí se okno, ve kterém musíte funkci najít SUMMESLIMN. Dále jej vyberte ze seznamu a otevře se známé okno s argumenty. Ale počet těchto argumentů je nyní jiný. Tento vzorec umožňuje použít nekonečné množství kritérií, ale minimální počet argumentů je pět. 

V dialogovém okně pro zadání argumentu lze zadat pouze pět. Pokud potřebujete více kritérií, budete je muset zadat ručně podle stejné logiky jako první dvě.

Podívejme se na hlavní argumenty podrobněji:

1. Rozsah součtu. Buňky, které mají být sečteny.
2. Rozsah podmínek 1 – rozsah, u kterého se bude kontrolovat splnění určitého kritéria. 
3. Podmínka 1 je podmínka samotná.
4. Rozsah kritérií 2 je druhý rozsah, který bude porovnán s kritériem.
5. Podmínka 2 je druhá podmínka.

Další logika je podobná. V důsledku toho jsme určili mzdy všem manažerům jižní pobočky.

Funkce COUNTIF v Excelu

Pokud potřebujete určit, kolik buněk spadá pod určité kritérium, použijte funkci COUNTIF. Řekněme, že potřebujeme pochopit, kolik prodejců pracuje v této organizaci:

1. Nejprve přidejte řádek obsahující počet prodejců. Poté musíte kliknout na buňku, kde se zobrazí výsledek.
2. Poté musíte kliknout na tlačítko „Vložit funkci“, které najdete na kartě „Vzorce“. Zobrazí se okno se seznamem kategorií. Musíme vybrat položku „Úplný abecední seznam“. V seznamu nás zajímá vzorec COUNTIF. Poté, co jej vybereme, musíme kliknout na tlačítko „OK“.
   

   

3. Poté máme počet prodejců zaměstnaných v této organizaci. Byl získán spočítáním počtu buněk, ve kterých je napsáno slovo „prodejce“. Všechno je jednoduché. 

Funkce COUNTSLIM v Excelu

Podobně jako u vzorce SUMMESLIMN, tento vzorec počítá počet buněk, které odpovídají více podmínkám. Syntaxe je podobná, ale mírně odlišná od vzorce SUMMESLIMN:

1. Rozsah podmínek 1. Toto je rozsah, který bude testován podle prvního kritéria.
2. Podmínka 1. Přímo první kritérium.
3. Podmínka Rozsah 2. Toto je rozsah, který bude testován podle druhého kritéria. 
4. Podmínka 2.
5. Podmínky dojezdu 3.

A tak dále.

Takže funkce IF v Excelu – není jediný, existuje několik dalších jeho druhů, které automaticky provádějí nejběžnější akce, což výrazně zjednodušuje život člověka. 

Z velké části díky funkci IF Excelové tabulky jsou považovány za programovatelné. Je to víc než jen obyčejná kalkulačka. Když se nad tím zamyslíte, tak funkce IF je základním kamenem jakéhokoli programování.

Pokud se tedy naučíte pracovat s velkým množstvím dat v Excelu, bude mnohem snazší se naučit programovat. Díky logickým operátorům mají tyto oblasti opravdu mnoho společného, ​​i když Excel častěji používají účetní. Mechanismus práce s daty je ale z velké části stejný. 

Funkce ve správných rukou IF a jeho variace umožňují přeměnit list Excelu na plnohodnotný program, který může pracovat se složitými algoritmy. Pochopení toho, jak funkce funguje IF je prvním krokem k učení maker – dalším krokem k flexibilnější práci s tabulkami. To už je ale profesionálnější úroveň.