V předchozích dvou videích jsme si ukázali, jak pracovat s jednoduchou podmínkovou funkcí KDYŽ a jak tuto funkci skládat s dalšími dvěma logickými funkcemi A a NEBO. V dnešním videu na tato videa navážeme a ukážeme si, jak vnořovat více podmínek KDYŽ do sebe. Tedy, jak pracovat s více podmínkami KDYŽ v jedné funkci. Jako vždy si použití složených podmínek KDYŽ ukážeme na několika následujících příkladech.
Excelový soubor ke stažení
Vnořené funkce KDYŽ | Jak pracovat s více podmínkami KDYŽ
Než se pustíme do složených podmínek KDYŽ, tak začneme základním příkladem na funkci KDYŽ, abychom si ukázali rozdíl v použití jednoduché a vnořené podmínky KDYŽ.
Máme následující tabulku se zákazníky a jejich tržbami. Zákazníky máme rozdělit podle toho, zda jsou jejich tržby menší nebo rovny 40 000 Kč a nebo vyšší než 40 000 Kč. Jelikož máme jen dvě možnosti, tak příklad vyřešíme pomocí jednoduché funkce KDYŽ. Do první buňky D5 bychom napsali funkci KDYŽ, kde by podmínka byla, pokud je tržba u prvního zákazníka (C5) menší nebo rovna limitu (G6), nezapomeneme tuto buňku zafixovat klávesou F4 ($G$6). Pokud bude podmínka splněná, tak chceme vrátit slovo „Menší“, v opačném případě chceme vrátit slovo „Větší“. Uzavřeme závorku a funkci potvrdíme a stáhneme ji pro ostatní řádky dolů. Jednoduchá funkce KDYŽ správně doplnila podmínky.
Co kdybychom ale chtěli rozdělit zákazníky do třech kategorií? U zákazníků, kteří mají nižší tržby než 40 000 Kč chceme slovo „Menší“, u těch, jejichž tržby se přesně rovnají 40 000 Kč chceme slovo „Rovná se” a u zákazníků, kteří mají tržby vyšší chceme vrátit slovo „Větší“.
Přesně v tomto případě nám poslouží vnořené funkce KDYŽ.
Do první buňky (D14) napíšeme funkci KDYŽ, kde jako podmínka bude, zda jsou tržby u prvního zákazníka (C14) menší než 40 000 Kč. Limit 40 000 Kč máme vedle tabulky uvedený v buňce (G15), takže tuto buňku označíme a nezapomeneme ji plně zafixovat klávesou F4 ($G$15). Následuje středník a přepneme se do parametru ano, tedy co se má stát, pokud podmínka bude splněná. Pokud podmínka bude splněná, tak chceme vrátit slovo „Menší“, nesmíme zapomenout na to, uvést slovo do uvozovek, jelikož se jedná o textovou hodnotu. Napíšeme středník a přepneme se do posledního parametru funkce KDYŽ, což je ne, tedy, co se má stát, pokud podmínka splněná nebude.
A právě toto je místo, kde můžeme vnořit další podmínkovou funkci KDYŽ. Pokud tedy tržby nebudou nižší než 40 000 Kč, tak musí následovat další podmínka KDYŽ. Do parametru ne tedy napíšeme další funkce KDYŽ, kde podmínka bude, pokud se tržby (C14) budou rovnat přesně 40 000 Kč, opět označíme buňku s limitem (G15) a zafixujeme ji klávesou F4. Pokud se tržby budou rovnat, tak chceme vrátit slovo „Rovná se“, opět nezapomene na uvozovky. Následuje středník ve druhé funkci KDYŽ a opět parametr ne. V tomto příkladu máme pouze tři možnosti, tržby zákazníka buď budou nižší, rovny nebo vyšší než zadaný limit, takže nám již teď nezbývá jiná možnost, než že tržby budou vyšší. Do tohoto parametru ne tedy napíšeme slovo „Větší“ opět v uvozovkách. Ukončíme závorku u druhé funkce KDYŽ a nezapomeneme ukončit i druhou závorku u první funkce KDYŽ.
Funkci potvrdíme a jelikož jsme buňky správně zafixovali klávesou F4, tak můžeme tuto funkci poslat dolů i pro ostatní buňky. Vnořená funkce KDYŽ správně doplnila k jednotlivým zákazníkům popis jejich tržeb.
Více podmínek KDYŽ v kombinaci s podmínkami A a NEBO
Skládat podmínky KDYŽ můžete i v kombinaci s pomocnými funkcemi A a NEBO. Jako v následujícím příkladu. Zde potřebujeme k jednotlivým zákazníkům přiřadit rating, dle výše jejich tržeb. Pokud budou mít tržby menší nebo rovny 20 000 Kč, tak obdrží rating C (H24). Pokud budou jejich tržby mezi 20 000 a 50 000 Kč tak obdrží rating B (H25) a s tržbami nad 50 000 Kč obdrží rating A (H26).
Do první buňky (D23) napíšeme funkci KDYŽ, ve které začneme první podmínkou, když budou tržby (C23) menší nebo rovny 20 000 Kč (G24), pokud tato podmínka splněná bude, tak chceme vrátit rating uvedený v buňce H24. Nezapomeneme na správné fixace buněk.
Přepneme se do posledního parametru funkce KDYŽ, kde bude následovat další funkce KDYŽ. V této druhé funkce KDYŽ ale potřebujeme vyjádřit, že tržby mají být vyšší než 20 000 Kč, ale zároveň nižší než 50 000 Kč. K tomu tedy použijeme funkci A. Ve druhé funkci KDYŽ v podmínce tedy bude funkce A, ve které bude jako první podmínka, pokud budou tržby (C23) větší než 20 000 Kč (G24), středník a druhá podmínka je, že tržby (C23) budou nižší než 50 000 Kč (G25), ukončíme závorku u funkce A. Následuje středník a parametr, co se má stát, pokud tato podmínka splněná bude, v takovém případě chceme vrátit rating B, který je uvedený v buňce H25.
Následuje parametr ne, a jelikož už nám zbývá pouze poslední možnost, tedy že budou tržby vyšší než 50 000 Kč, tak to napíšeme do tohoto parametru, tedy že v ostatních případech chceme vrátit rating A, který je uvedený v buňce H26. Ukončíme závorky pro obě funkce KDYŽ a funkce potvrdíme a pošleme dolů.
Více podmínek KDYŽ – 4 a více podmínek
Vnořené funkce KDYŽ vám dovolí vytvářet i poměrně komplexní výpočty bez nutnosti pomocných sloupců. V následujícím příkladu potřebujeme vynásobit tržby jednotlivých zákazníků procentem, které je ovšem závislé od města, ve kterém zákazník sídlí.
Začneme zase postupně. V první buňce (E33) napíšeme funkci KDYŽ, kde ověříme, zda se sídlo zákazníka (C33) rovná Praze (H33). Pokud se rovná, tak chceme vynásobit tržby u zákazníka procentem uvedeným u Prahy (D33*I33). Jelikož máme v plánu vzorec stahovat dolů, tak nesmíme zapomenout na správnou fixaci buněk pomocí klávesy F4. V tomto případě musíme zafixovat buňku H33 s Prahou a buňku I33 s procentem.
Následuje další podmínka KDYŽ. V této druhé podmínce ověříme, zda se sídlo (C33) rovná druhému městu, což je Brno (H34). Pokud ano, tak chceme tržby vynásobit procentem u Brna, opět správně zafixujeme buňky (D33*I34). Tentokrát nemáme pouze tři možnosti, ale máme čtyři, takže následuje další podmínka KDYŽ, kde ověříme, zda se sídlo (C33) rovná Ostravě (H35), pokud ano, vynásobíme tržby procentem u Ostravy (D33*I35). A pokud ani jedna z těchto předešlých podmínek splněná nebude, tak víme, že se jedná o možnost ostatní, takže budeme chtít vynásobit tržby procentem u ostatních (D33*I36). Ukončíme závorky, funkci potvrdíme a stáhneme dolů.
Funkce IFS – zjednodušení vnořených podmínek KDYŽ
Vícenásobné vnořené podmínky KDYŽ si můžete trochu zjednodušit, pokud používáte verzi Excelu od 2019 výše. V těchto verzích totiž existuje funkce IFS, což je funkce, která vnořování funkcí KDYŽ trochu usnadní.
Vrátíme se k prvnímu příkladu a ukážeme si na něm použití funkce IFS. Ve funkci IFS máte v zásadě dva parametry, prvním je logický test a druhým je podmínka.
= IFS (logický_test1; podmínka1; logický_test2; podmínka2…)
Do buňky D44 napíšeme tedy funkci IFS, kde jako logický test napíšeme, pokud bude tržba (C44) menší než 40 000 Kč (G45), a v podmínce bude, co chceme vrátit, tedy slovo „Menší” v uvozovkách. Na rozdíl od vnořených funkcí KDYŽ ve funkci IFS rovnou pokračujete druhou podmínkou, bez nutnosti opakování funkce KDYŽ. Následuje tedy druhá podmínka, pokud se tržba (C44) bude rovnat 40 000 Kč (G45), tak chceme vrátit slovo „Rovná se“, a pokud bude tržba (C44) vyšší než 40 000 Kč (G45), tak chceme vrátit slovo „Větší“ v uvozovkách. Ukončíme závorku, funkci potvrdíme a pošleme ji dolů.
Funkce IFS v kombinaci s funkcí PRAVDA pro alternativní výsledek
Důležité, co si u funkce IFS musíte pamatovat je, jak napsat poslední podmínku, tedy pokud předešlé podmínky nebudou splněny. Tuto variantu si ukážeme na příkladu s městy. Opět chceme spočítat násobek tržeb a procent u příslušných měst. V buňce E53 začneme tedy funkcí IFS, kde první logický test bude, pokud se město (C53) bude shodovat s Prahou (H53), tak chceme vynásobit tržby procentem (D53*I53), druhý logický test bude, pokud se město (C53) bude shodovat s Brnem (H54), tak chceme vynásobit tržby procentem u Brna (D53*I54), středník a třetí logický test bude, pokud se bude město (C53) shodovat s Ostravou (H55), tak chceme vynásobit tržby s procentem u Ostravy (D53*I55).
A teď přichází poslední podmínka, kde musíme vyjádřit, že jakékoliv jiné město se má násobit 5 %. A právě k tomu slouží funkce PRAVDA. Pokud ve funkci IFS chceme vyjádřit, že pokud předcházející podmínky nebudou splněné, tak že chceme vrátit alternativní výsledek, tak napíšeme do logického testu PRAVDA, a co se má stát. Tedy, že se tržby mají vynásobit 5 % (I56*D53). Ukončíme závorku a funkci potvrdíme a pošleme ji dolů.
Hotovo.
2 komentáře
Dobrý den,
v prvé řadě bych chtěl poděkovat za skvělý obsah, který tvoříte! :-))
Mohl bych se jen zeptat, v případě IFS, pokud bych nakonec všech podmínek nepoužil funkci pravda, co by to způsobilo? funkce by vyhodila chybovou hlášku? Nebo by poskytovala nějaký výsledek, který není správný?
Děkuji
Dobrý den, PRAVDA se do poslední podmínky KDYŽ ve funkci IFS píše proto, že nemůžete přeskočit povinný parametr logická podmínka ve funkci IFS. Ve standardní funkci KDYŽ napíšete podmínky a do parametru ne případně napíšete, co se má stát, když předchozí podmínky splněné nebudou. Ve funkci IFS ale musíte vyplnit i logickou podmínku a právě “všechny ostatní případy, v případě, že předchozí podmínky splněné nebudou” se nahrazují PRAVDOU. Jinak by vám tam chyběl parametr a funkce by nefungovala. Nicméně používá se to jen v případech kdy může existovat více možností. Pokud máte například jen tři podmínky, které mohou nastat, tak do funkce IFS uvedete tři logické podmínky a tři výpočty. Pokud ale máte tři podmínky, které chcete ošetřit, ale existuje i daleko více možností, které kromě těchto tří podmínek mohou nastat, tak ty ostatní ošetříte právě pravdou 🙂 Doufám, že je to srozumitelné 😀