V dnešním videu si představíme funkci, která je v Excelu dostupná téměř pro všechny a přesto je neznámá. Skoro 99% excelových uživatelů ji nezná a nepoužívá. Řeč je o funkci XOR. Funkce XOR je logická funkce, která patří do skupiny funkcí jako funkce A nebo NEBO, tedy funkce AND nebo OR. I přesto ji skoro nikdo nepoužívá, i přesto, že v určitých situacích vám může nejen ušetřit čas, ale rovněž zkrátit vnořené podmínkové funkce.
Excelový soubor ke stažení:
Pojďme se na funkci XOR podívat.
Většina z nás v Excelu používá nebo někdy použila logické funkce A a NEBO, anglicky funkce AND a OR. Jedná se o logické funkce, které se nejčastěji kombinují s podmínkovou funkcí KDYŽ. Funkce A vrátí pravdu ve chvíli, kdy jsou všechny parametry ve funkci vyhodnocené jako pravdivé. Funkce NEBO vrátí pravdu tehdy, pokud je alespoň jeden z parametrů funkce vyhodnocený jako pravdivý.
Co tedy dělá funkce XOR? Funkce XOR vrátí pravdu nebo nepravdu na základě toho, kolik pravd a nepravd vyjde v podmínce.
Chování funkce si ukážeme nejprve na příkladu s dvěma parametry. V tabulce máme kombinace pravd a nepravd. Může se jednat o výsledky podmínkové funkce KDYŽ. V prvním případě jsou obě podmínky splněné a výsledkem jsou dvě pravdy, na dalších řádcích je kombinace pravdy a nepravdy a na posledním řádku není splněná ani jedna podmínka.
Nejprve si ukážeme, co v těchto případech vrátí funkce A. Napíšeme funkci A a označíme oba parametry. Funkci ukončíme, potvrdíme a stáhneme dolů. Funkce A vrátí pravdu pouze tehdy, pokud jsou všechny podmínky splněné, takže vrátí pravdu pouze na prvním řádku.
Naproti tomu funkce NEBO vrátí pravdu tehdy, pokud je alespoň jedna podmínka splněná, takže pokud v dalším sloupci použijeme funkci NEBO, tak se pravda vrátí na prvních třech řádcích.
A co vrátí funkce XOR?
Napíšeme funkci XOR, která má stejně jako předešlé funkce pouze logické parametry. Takže ve funkci označíme dva parametry a funkci potvrdíme. Stáhneme ji dolů a podíváme se na výsledek. Na prvním a posledním řádku vrátila funkce XOR nepravdy. A je to proto, protože v případě dvou parametrů vrátí funkce XOR nepravdu tehdy, pokud jsou oba parametry vyhodnocené jako pravdivé nebo nepravdivé. Pokud jsou tedy oba výsledky stejné, tak vrátí nepravdu. Naopak pravdu vrátí tehdy, pokud je výsledkem jednoho parametru pravda a druhého nepravda. Proto máme u prostředních dvou řádků pravdu, jelikož zde máme kombinaci pravdy a nepravdy.
A jak by to bylo v případě tří parametrů?
Napíšeme funkci XOR a opět v ní označíme tři parametry. Funkci ukončíme a potvrdíme a stáhneme ji dolů. Teď je výsledek jiný. A je to proto, protože v případě, že máme parametrů více, tak funkce vrátí pravdu tehdy, pokud je výsledkem parametrů pravda na lichém počtu parametrů.
Jinými slovy, funkce XOR vrátí nepravdu, pokud je výsledkem funkce sudý počet pravd a pravdu vrátí, když je výsledkem lichý počet pravd.
Na prvním řádku máme tři pravdy, tedy tři splněné podmínky, což je lichý počet pravd a proto funkce XOR vrací pravdu. Na druhém řádku máme dvě pravdy a jednu nepravdu, takže sudý počet pravda a proto funkce vrací nepravdu. Na třetím řádku máme lichý počet pravd, jelikož je zde jen jedna. A na posledním řádku je nepravda, jelikož tam není žádná pravda, tedy není žádná podmínka splněná.
A takto bychom mohli pokračovat i s více parametry. U čtyř parametrů by byla logika stejná. Na řádcích s lichým počtem pravd se vrátí pravda a na ostatních nepravda.
Možná si teď říkáte, co za divnou funkci to je a kde ji využijete?
V příkladu máme studenty a dva testy. Pokud student test absolvoval, tak je test označený křížkem. Zajímají nás studenti, kteří mají splněný jen jeden test ze dvou.
Jak bychom příklad vyřešili, kdybychom neznali funkci XOR?
Nejspíše bychom použili vnořenou funkci KDYŽ, kde bychom pomocí dvou funkcí A ověřili dvě možné kombinace, které nás zajímají.
=KDYŽ(A(B24=”x”;C24=””);”Ano”;KDYŽ(A(B24=””;C24=”x”);”Ano”;””))
Ale my známe funkci XOR, takže použijeme tuto funkci. Začneme podmínkovou funkcí KDYŽ, a v podmínce použijeme funkci XOR. Ve funkci XOR ověříme, zda je u prvního testu křížek, a rovnou ověříme, zda je i u druhého testu křížek, a pokud ano, tak chceme vrátit slovo Ano, jinak chceme buňku s prázdným textovým řetězcem. To je celé. Funkci ukončíme a pošleme ji dolů. Funkce XOR vrátí pravdu tam, kde je lichý počet pravd, což je na každém řádku, kde je jen jeden křížek. Tam kde jsou dva nebo žádný křížek vrátí funkce XOR nepravdu. Takže jsme si ušetřili díky funkci XOR jednu vnořenou funkci KDYŽ.
=KDYŽ(XOR(B24=”x”;C24=”x”);”Ano”;””)
U příkladů se studenty ještě zůstaneme a ukážeme si další příklad s podobnou logikou. V další tabulce máme studenty a opět dva testy. Tentokrát potřebujeme označit studenty následovně. Student, který prošel oběma testy bude mít u sebe slovo Splněno. Student, který prošel pouze jedním ze dvou testů, musí opakovat a student, který neprošel ani z jednoho testu propadl. Nejprve si ukážeme, jak bychom příklad řešili pomocí vnořených funkcí KDYŽ.
=KDYŽ(A(B36=”Prošel”;C36=”Prošel”);”Splněno”;KDYŽ(A(B36=”Prošel”;C36=”Neprošel”);”Opakuje”;KDYŽ(A(B36=”Neprošel”;C36=”Prošel”);”Opakuje”;”Propadl”)))
Někteří z vás mají přístup k funkci IFS, která usnadňuje psaní vnořených funkcí KDYŽ, ale zápis by nebyl zase o tolik kratší.
=IFS(A(B36=”Prošel”;C36=”Prošel”);”Splněno”;A(B36=”Prošel”;C36=”Neprošel”);”Opakuje”;A(B36=”Neprošel”;C36=”Prošel”);”Opakuje”;PRAVDA;”Propadl”)
A jak to vyřešíme s funkcí XOR?
=KDYŽ(A(B36:C36=”Prošel”);”Splněno”;KDYŽ(XOR(B36:C36=”Prošel”);”Opakuje”;”Propadl”))
Zápis je tedy díky funkci XOR kratší než u použití funkcí KDYŽ v kombinaci s funkcí NEBO.
Ještě si ukážeme poslední příklad, kde chceme označit řádky podle následující logiky. Na řádku, kde tržby za oba kvartály přesáhly nebo byly rovné 100 000 Kč, vyplatíme plný bonus. Tam, kde alespoň jeden kvartál přesáhl 100 000 Kč vyplatíme poloviční bonus a v ostatních případech bude bonus nulový.
=KDYŽ(A(A48:B48>=100000);”Plný”;KDYŽ(XOR(A48:B48>=100000);”Polovina”;””))
