Funkce KDYŽ – jak se vyhnout nástrahám při použití vnořených vzorců

Funkce KDYŽ – jak se vyhnout nástrahám při použití vnořených vzorců

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ím se rozumí vzájemné spojení několika funkcí 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

Excel sice umožňuje vnořit do sebe až 64 různých funkcí KDYŽ, ale v žádném případě se to nedoporučuje. 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 vnoření ve vzorci není 100% správné, může vzorec fungovat na 75 %, ale v 25 % případů vrátí 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 sestavujete příkaz KDYŽ, který se pořád rozrůstá a jeho konec není v dohledu, je čas odložit myš a přehodnotit strategii.

Podívejme se na to, jak správně vytvořit komplexní vnořený příkaz KDYŽ s několika podmínkami, a jak poznat, že je čas použít jiný nástroj z bohatého arzenálu Excelu.

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.

Složitý vnořený příkaz KDYŽ– v buňce E2 je vzorec =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"))))))))))))
  • =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:

  1. Pokud je skóre testu (v buňce D2) větší než 89, dostane student známku A.

  2. Pokud je skóre testu větší než 79, dostane student známku B.

  3. Pokud je skóre testu větší než 69, dostane student známku C.

  4. Pokud je skóre testu větší než 59, dostane student známku D.

  5. 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 se vzájemný vztah mezi skóre testu a známkami změní, takže nebude vyžadovat moc údržby. Vkrádá se ale myšlenka – co když potřebujete známky rozčlenit na 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 pořád funkčně správný a bude fungovat podle očekávání, ale dlouho trvá, než ho vytvoříte, a ještě déle, než ho otestujete a ověříte, že funguje jak má. Další nepříjemností je, že byste jednotlivá skóre a ekvivalentní známky museli zadat ručně. Jaká je pravděpodobnost, že nechtěně uděláte překlep? A teď si představte, že u složitějších podmínek byste to dělali 64krát! Klidně můžete, ale opravdu se chcete pustit do takové námahy s možností vzniku chyb, které bude prakticky nemožné odhalit?

Tip : Každá funkce v Excelu vyžaduje levou a pravou závorku (). Obarvením různých částí vzorce během jeho úpravy vám Excel pomůže zjistit, co má kam přijít. Kdybyste například upravovali předchozí vzorec, při pohybu kurzoru přes jednotlivé pravé závorky se odpovídající levá závorka obarví stejnou barvou. To může být zvlášť užitečné u složitých vnořených vzorců, kdy se snažíte zjistit, jestli máte dost 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.

V buňce D9 je vzorec KDYŽ(C9>15000;20%;KDYŽ(C9>12500;17,5%;KDYŽ(C9>10000;15%;KDYŽ(C9>7500;12,5%;KDYŽ(C9>5000;10%;0)))))
  • =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ž se tento vzorec hodně podobá předchozímu příkladu se známkami, je skvělým příkladem toho, jak obtížná může být údržba rozsáhlých příkazů KDYŽ – co byste museli udělat, kdyby se ve vaší organizaci rozhodli přidat nové úrovně odměn a ještě změnili existující peněžní nebo procentuální hodnoty? Měli byste plné ruce práce!

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:

Vzorec v buňce D9 je nefunkční, protože =KDYŽ(C9>5000;10%;KDYŽ(C9>7500;12,5%;KDYŽ(C9>10000;15%;KDYŽ(C9>12500;17,5%;KDYŽ(C9>15000;20%;0)))))

Vidíte, kde je chyba? Srovnejte pořadí porovnávání tržeb s předchozím příkladem. Kterým směrem probíhá? Správně, probíhá odspodu nahoru (od 5 000 do 15 000), a ne naopak. Ale proč na tom tolik záleží? Záleží na tom proto, protože při jakékoli hodnotě nad 5 000 vzorec nepřejde za první vyhodnocení. Řekněme, že máte tržby 12 500 – příkaz KDYŽ vrátí 10 %, protože je to víc než 5 000, a tady se zastaví. To může být pěkný problém, protože v mnoha situacích si tohoto typu chyb nikdo nevšimne, dokud nemají 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:

V buňce D2 je vzorec =SVYHLEDAT(C2;C5:D17;2;PRAVDA)
  • =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.

V buňce C9 je vzorec =SVYHLEDAT(B9;B2:C6;2;PRAVDA)
  • =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 mnohem podrobněji vysvětlená tady, určitě je ale o hodně 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 ostatní referenční tabulku viděli nebo upravovali, jednoduše ji dejte do jiného listu.

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 příkazy KDYŽ a závorky.

Poznámka : Tato funkce je dostupná jenom v případě, že máte předplatné Office 365. Pokud předplatné Office 365 máte, zkontrolujte si, jestli máte nejnovější verzi Office.

Vyzkoušejte Office 365 nebo nejnovější verzi Excelu

Potřebujete další pomoc?

Kdykoli se můžete zeptat některého odborníka v technické komunitě Excelu, získat podporu v komunitě pro odpovědi, případně navrhnout novou funkci nebo vylepšení ve fóru Excel User Voice.

Příbuzná témata

Video: Pokročilé funkce KDYŽ
Funkce IFS (Office 365, Excel 2016 a novější)
Funkce COUNTIF spočítá hodnoty na základě jednoho kritéria
Funkce COUNTIFS spočítá hodnoty na základě několika kritérií
Funkce SUMIF sečte hodnoty na základě jednoho kritéria
Funkce SUMIFS sečte hodnoty na základě několika kritérií
Funkce A
Funkce NEBO
Funkce SVYHLEDAT
Přehled vzorců v Excelu
Jak se vyhnout nefunkčním vzorcům
Oprava chyb ve vzorcích pomocí kontroly chyb
Logické funkce
Funkce Excelu (podle abecedy)
Funkce Excelu (podle kategorie)

Rozšiřte své znalosti a dovednosti
Projít školení
Získejte nové funkce jako první
Připojte se k účastníkům programu Office Insiders

Byly tyto informace užitečné?

Děkujeme vám za zpětnou vazbu.

Děkujeme vám za váš názor. Vypadá to, že bude užitečné, když vás spojíme s některým z našich agentů z podpory Office.

×