Funkce KDYŽ testuje podmínku a při jejím splnění nebo nesplnění vrátí výsledek. Umožňuje tak logické porovnání mezi nějakou hodnotou a tím, co očekáváte.
-
=KDYŽ(je něco pravda; udělej něco; jinak udělej něco jiného)
To znamená, že příkaz KDYŽ může mít dva výsledky. První výsledek platí, pokud je výsledkem porovnání pravda, a druhý výsledek platí v případě nepravdy.
Příkazy IF jsou neuvěřitelně robustní a tvoří základ mnoha tabulkových modelů, zároveň jsou ale hlavní příčinou mnoha problémů s tabulkami. V ideálním případě by se měl příkaz KDYŽ používat s minimálním podmínkami, například Muž/Žena, Ano/Ne/Možná, někdy ale můžete potřebovat vyhodnotit složitější situace, které vyžadují vnoření* víc než tří funkcí KDYŽ do sebe.
* "Vnoření" odkazuje na praxi spojení více funkcí dohromady v jednom vzorci.
Funkce KDYŽ, jedna z logických funkcí, vrátí jednu hodnotu, pokud se zadaná podmínka vyhodnotí jako Pravda, a jinou hodnotu, pokud se vyhodnotí jako Nepravda.
Syntaxe
KDYŽ(podmínka;ano;ne)
Příklady:
-
=KDYŽ(A2>B2;"Překročil se rozpočet.";"OK")
-
=KDYŽ(A2=B2;B4-A4;"")
|
Název argumentu |
Popis |
|
podmínka (povinné) |
Podmínka, kterou chcete testovat |
|
ano (povinné) |
Hodnota, která se má vrátit, pokud se podmínka vyhodnotí jako PRAVDA |
|
ne (volitelné) |
Hodnota, která se má vrátit, pokud se podmínka vyhodnotí jako NEPRAVDA |
Poznámky
I když Excel umožňuje vnořit až 64 různých funkcí KDYŽ, není to vůbec vhodné. Proč?
-
Správné sestavení vícenásobných příkazů KDYŽ vyžaduje hodně přemýšlení, abyste si byli jistí správnou logikou výpočtu každé podmínky až do konce příkazu. Pokud vzorec přesně 100 % nevnořete, může fungovat 75 % času, ale 25 % času vrátit neočekávané výsledky. Pravděpodobnost, že těch 25 % zachytíte, je bohužel malá.
-
Udržování vícenásobných příkazů KDYŽ může být velice obtížné, zvlášť když se k příkazu vrátíte po nějaké době a snažíte se přijít na to, co jste se vy (nebo v horším případě někdo jiný) pokoušeli udělat.
Pokud zjistíte, že jste s příkazem KDYŽ, který se zdá, že stále roste bez konce v dohledu, je čas odložit myš a znovu promyslíte svou strategii.
Podívejme se na to, jak správně vytvořit složitý vnořený příkaz KDYŽ pomocí více IF a kdy rozpoznat, že je čas použít jiný nástroj v excelovém arsenalu.
Příklady
Dole najdete příklad relativně standardního vnořeného příkazu KDYŽ, který převádí skóre studentských testů na známky vyjádřené písmenem.
-
=KDYŽ(D2>89;"A";KDYŽ(D2>79;"B";KDYŽ(D2>69;"C";KDYŽ(D2>59;"D";"F"))))
Tento složitý vnořený příkaz KDYŽ má přímočarou logiku:
-
Pokud je skóre testu (v buňce D2) větší než 89, dostane student známku A.
-
Pokud je skóre testu větší než 79, dostane student známku B.
-
Pokud je skóre testu větší než 69, dostane student známku C.
-
Pokud je skóre testu větší než 59, dostane student známku D.
-
V opačném případě dostane student známku F.
Tento konkrétní příklad je relativně bezpečný, protože není pravděpodobné, že by se korelace mezi výsledky testů a známkami písmen změnila, takže nebude vyžadovat velkou údržbu. Ale tady je myšlenka – co když potřebujete rozdělit známky mezi A+, A a A- (a tak dále)? Příkaz KDYŽ se 4 podmínkami by se musel přepsat tak, aby obsahoval 12 podmínek! Vzorec by teď vypadal takto:
-
=KDYŽ(B2>97;"A+";KDYŽ(B2>93;"A";KDYŽ(B2>89;"A-";KDYŽ(B2>87;"B+";KDYŽ(B2>83;"B";KDYŽ(B2>79;"B-";KDYŽ(B2>77;"C+";KDYŽ(B2>73;"C";KDYŽ(B2>69;"C-";KDYŽ(B2>57;"D+";KDYŽ(B2>53;"D";KDYŽ(B2>49;"D-";"F"))))))))))))
Je stále funkčně přesný a bude fungovat podle očekávání, ale psaní trvá dlouho a delší dobu, než se otestuje, abyste se ujistili, že dělá to, co chcete. Dalším do očí bijícím problémem je, že jste museli zadat skóre a ekvivalentní známky ručně. Jaká je pravděpodobnost, že dojde k překlepu? A teď si představte, že u složitějších podmínek byste to dělali 64krát! Jistě, je to možné, ale opravdu se chcete podrobovat takovému úsilí a pravděpodobným chybám, které bude opravdu těžké najít?
Tip: Každá funkce v Excelu vyžaduje levou a pravou závorku (). Excel se vám při úpravách vzorce pokusí pomoct zjistit, co kam směřuje, tím, že vybarví různé části vzorce. Pokud byste například chtěli upravit výše uvedený vzorec, při přesouvání kurzoru za každou koncovou závorku ")" se její odpovídající levá závorka změní na stejnou barvu. To může být užitečné hlavně u složitých vnořených vzorců, když se snažíte zjistit, jestli máte dostatek odpovídajících závorek.
Další příklady
Dalším příkladem je úplně běžný výpočet provize z prodeje, který je založený na úrovních dosažených tržeb.
-
=KDYŽ(C9>15000;20%;KDYŽ(C9>12500;17,5%;KDYŽ(C9>10000;15%;KDYŽ(C9>7500;12,5%;KDYŽ(C9>5000;10%;0)))))
Tento vzorec můžete přečíst takto: KDYŽ(C9 je větší než 15 000, vrať 20 %, KDYŽ(C9 je větší než 12 500, vrať 17,5 %, a tak dále...
I když je tento vzorec nápadně podobný předchozímu příkladu Známky, je tento vzorec skvělým příkladem toho, jak obtížné může být udržovat velké příkazy KDYŽ – co byste museli udělat, kdyby se vaše organizace rozhodla přidat nové úrovně kompenzace a možná dokonce změnit stávající hodnoty v dolarech nebo procentech? Měl bys hodně práce na svých rukou!
Tip: Do řádku vzorců můžete vložit konce řádku, aby byly dlouhé vzorce přehlednější. Stačí, když před textem, který chcete zalomit na nový řádek, stisknete klávesy ALT+ENTER.
Tady je příklad stejné provize s nefunkční logikou:
Vidíte, co je špatně? Srovnejte pořadí porovnávání tržeb s předchozím příkladem. Kterým směrem probíhá? Správně, jde to zdola nahoru (5 000 až 15 000 usd), ne naopak. Ale proč na tom tolik záleží? Je to velký problém, protože vzorec nemůže projít prvním vyhodnocením pro hodnotu větší než 5 000 USD. Řekněme, že máte 12 500 USD v výnosech – příkaz KDYŽ vrátí 10 %, protože je větší než 5 000 USD, a tím to skončí. To může být neuvěřitelně problematické, protože v mnoha situacích tyto typy chyb zůstanou bez povšimnutí, dokud nebudou mít negativní dopad. Když teď víte, že složité vnořené příkazy KDYŽ mají určitá vážná úskalí, jak z toho ven? Ve většině případů můžete místo sestavování složitých vzorců s funkcí KDYŽ použít funkci SVYHLEDAT. Při použití funkce SVYHLEDAT si napřed musíte vytvořit referenční tabulku:
-
=SVYHLEDAT(C2;C5:D17;2;PRAVDA)
Tento vzorec říká, že se má v oblasti C5:C17 vyhledat hodnota v buňce C2. Pokud se tato hodnota najde, vrátí se odpovídající hodnota ze stejného řádku ve sloupci D.
-
=SVYHLEDAT(B9;B2:C6;2;TRUE)
Tento vzorec hledá podobně hodnotu v buňce B9 v oblasti B2:B22. Pokud se tato hodnota najde, vrátí se odpovídající hodnota ze stejného řádku ve sloupci C.
Poznámka: Oba tyto vzorce SVYHLEDAT používají na konci argument PRAVDA, což znamená, že mají hledat přibližnou shodu. Jinými slovy budou ve vyhledávací tabulce porovnávat přesné hodnoty, stejně jako hodnoty, které mezi ně spadají. V tomto případě musí být vyhledávací tabulky seřazené vzestupně od nejnižších po nejvyšší hodnoty.
Funkce SVYHLEDAT je zde popsána mnohem podrobněji, ale je to určitě mnohem jednodušší než 12úrovňový složitý vnořený příkaz KDYŽ! Navíc má další výhody, které nejsou hned zřejmé:
-
Referenční tabulky příkazu SVYHLEDAT jsou viditelné a přehledné.
-
Hodnoty v tabulce se dají snadno aktualizovat a při změně podmínek nemusíte s vzorcem vůbec nic dělat.
-
Pokud nechcete, aby lidé vaši referenční tabulku viděli nebo do ní zasahovali, stačí ji umístit na jiný list.
Víte to?
K dispozici je teď funkce IFS, která dokáže nahradit vícenásobné vnořené příkazy KDYŽ jedinou funkcí. Tady je náš první příklad se známkami, který má 4 vnořené funkce KDYŽ:
-
=KDYŽ(D2>89;"A";KDYŽ(D2>79;"B";KDYŽ(D2>69;"C";KDYŽ(D2>59;"D";"F"))))
Jedinou funkcí IFS se dá podstatně zjednodušit:
-
=IFS(D2>89;"A";D2>79;"B";D2>69;"C";D2>59;"D";TRUE;"F")
Funkce IFS je skvělá, protože se nemusíte starat o všechny tyto příkazy a závorky IF.
Poznámka: Tato funkce je dostupná jenom v případě, že máte předplatné Microsoft 365. Pokud jste Microsoft 365předplatitelem, ujistěte se, že máte nejnovější verzi Office.Koupit nebo vyzkoušet Microsoft 365
Potřebujete další pomoc?
Kdykoli se můžete zeptat odborníka z komunity Excel Tech nebo získat podporu v komunitách.
