V dnešním videu se podíváme na něco, čemu se v Excelu říká dvojitý negativ. Jedná se o takovou specialitku v Excelu, se kterou se můžete nejčastěji setkat ve funkci SOUČIN.SKALÁRNÍ neboli ve funkci SUMPRODUCT. Hodit se vám může ale i v některých jiných případech. A pokud se vám nebude hodit, tak je minimálně dobré vědět, co dvojitý negativ znamená a co dělá, v případě, že na něj někde narazíte.
Excelový soubor ke stažení:
Dvojitý negativ mění pravdu a nepravdu na jejich číselné protějšky, tedy jedničky a nuly. V Excelu je totiž nepravda vyjádřená rovněž jako nula a pravda jako jednička. Dvojitý negativ se v Excelu vyjadřuje dvěma pomlčkami. Převod pravd a nepravd ověříme, když do libovolné buňky napíšeme rovná se a funkci PRAVDA. Potvrdíme klávesu ENTER a v buňce se vrátí slovo pravda. Když teď před funkci PRAVDA napíšeme dvě pomlčky, tedy dvojitý negativ, a funkci potvrdíme, tak se ze slova pravda stane jednička. To samé můžeme udělat se slovem nepravda. Napíšeme rovná se, funkce nepravda a potvrdíme funkci. Vrátilo se slovo nepravda. Když před funkci napíšeme dvě pomlčky a potvrdíme změnu, tak se nepravda změní na nulu. Tento drobný trik s převodem hodnot na čísla se vám může hodit v několika praktických příkladech. A několik takových příkladů si právě teď ukážeme.
Dvojitý negativ se velmi často používá ve funkci SOUČIN.SKALÁRNÍ neboli anglicky funkce SUMPRODUCT. V praxi totiž máme možnost dvou zápisů parametrů ve funkci SOUČIN.SKALÁRNÍ. a u jednoho z nich právě využijeme dvojitý negativ. Ukážeme si to na velmi jednoduchém příkladu.
V tabulce máme produkty, počet prodaných kusů a tržby. Naším úkolem je spočítat celkové tržby u produktů, kterých se prodalo více než 10 kusů. Samozřejmě můžeme použít funkci jako SUMIFS, ale můžeme rovněž použít i funkci SOUČIN.SKALÁRNÍ. Záleží na vašich preferencích. Pokud použijeme funkci SOUČIN.SKALÁRNÍ tak budeme ve funkci nejprve muset ověřit podmínku, že se prodalo více než 10 kusů. Každá podmínka musí být ve funkci SOUČIN.SKALÁRNÍ v samostatných závorkách. Takže v této závorce ověříme podmínku, že počet prodaných kusů je více než 10. Tato podmínka je prvním polem ve funkci SOUČIN.SKALÁRNÍ. A teď máme na výběr ze dvou zápisů. Můžeme pole ve funkci SOUČIN.SKALÁRNÍ vynásobit, tím pádem místo středníku napíšeme znaménko krát a vynásobíme to druhým polem, což jsou tržby. Ukončíme funkci a potvrdíme. Funkce vrátila součet tržeb u produktů, kterých se prodalo více než 10 kusů.
Existuje ještě druhý zápis funkce, který si ukážeme v buňce pod prvním výsledkem. Napíšeme funkci SOUČIN.SKALÁRNÍ, kde opět nejprve ověříme podmínku, že se prodalo více než 10 kusů a podmínka musí být uvedená v závorce. Teď budeme následovat zápis funkce a použijeme pro oddělení parametrů středník a označíme druhé pole, což jsou tržby. Když funkci potvrdíme, tak se vrátí nula. A důvodem je podmínka ve funkci SOUČIN.SKALÁRNÍ. Označíme podmínku ve funkci a pomocí klávesy F9 zjistíme, že funkce vrací pravdy a nepravdy, podle toho, zda je podmínka splněná nebo ne. V prvním zápisu jsme pole mezi sebou násobily, což převedlo pravdy a nepravdy na čísla. Ve druhém zápisy jsme ale parametry oddělili středníkem a teď se tedy pravda a nepravda násobí číslem, což vrátí nulu. A proto funkce vrací nulu. Vrátíme se do funkce pomocí kláves CTRL+Z. A právě v tomto zápisu využijeme dvojitý negativ. Před podmínku napíšeme dvojitý negativ, což převede pravdy a nepravdy na jedničky a nuly a teď když funkci potvrdíme, tak se vrátí správný výsledek.
Dvojitý negativ využijete i v následujícím příkladu. V příkladu máme pět úkolů a pomocí zaškrtávacích políček určujeme, které úkoly jsou splněné. V buňce splněno chceme spočítat, kolik procent úkolů máme podle zaškrtnutí splněno. Zaškrtávací políčko vrací pravdu, pokud je zaškrtnuté a nepravdu, pokud je prázdné. Takže můžeme použít následující funkci, nejprve ve funkci SUMA sečteme pravdy. Pravdy jsou v Excelu jedničky, aby ale jedničkami byly, tak před ně musíme napsat dvojitý negativ. Dvojitý negativ ve funkci SUMA převede pravdy na jedničky, což jsou zaškrtnutá pole a nepravdy na nuly, což jsou prázdná políčka a výsledek sečte, což vrátí počet zaškrtnutých polí. A to vydělíme počtem hodnot ve sloupci, čemuž můžeme použít funkci POČET2 neboli anglicky funkci COUNTA, která počítá neprázdné buňky. Potvrdíme vzorec a vrátí se procentuální podíl splněných úkolů, který bude reagovat na zaškrtávací políčka.
Dvojitý negativ se vám bude hodit i v následujícím příkladu, kde budete mít s použitím funkce SUMIF problém. V tabulce máme datumy za dva roky a tržby. Vedle tabulky chceme spočítat celkové tržby pro zvolený rok. Jedná se o součet hodnot na základě podmínky, takže by nás mohlo napadnout použít funkci SUMIF nebo funkci SUMIFS. Když to ale uděláme, tak narazíme na jeden problém. Zkusíme příklad vyřešit pomocí funkce SUMIF. Ve funkci SUMIF nejprve označujeme oblast, což je v tomto případě sloupec s datumy. Ze sloupce s datumy ale potřebujeme pouze rok, takže do parametru napíšeme funkci ROK, anglicky funkci YEAR, ve které označíme datumy. Druhým parametrem je samotné kritérium rok a jako poslední parametr zde máme součet, což je sloupec tržeb. Funkci ukončíme a když ji potvrdíme klávesou ENTER, tak na nás vyskočí chyba. A chybu ve funkci SUMIF způsobuje funkce ROK. A to i přesto, že když se podíváme na výsledek funkce ROK pomocí klávesy F9, tak funkce vrací výsledek, který jsme chtěli, vrací jednotlivé roky z datumů. Nicméně funkce SUMIF není schopná tyto roky porovnat s kritériem. Pokud byste tento příklad chtěli vyřešit pomocí funkce SUMIF, tak byste museli do tabulky přidat pomocný sloupec, kde byste nejprve vyselektovali rok z datumu a následně tento sloupec použili ve funkci SUMIF jako oblast.
A nebo použijeme funkci SOUČIN.SKALÁRNÍ s dvojitým negativem a ušetříme si pomocný sloupec. Začneme s funkcí SOUČIN.SKALÁRNÍ, ve které ověříme podmínku, že je rok z datumu roven roku, který máme v buňce. Podmínka musí být ve vlastní závorce a víme, že tato podmínka vrátí pravdy a nepravdy, takže před ní rovnou napíšeme dvojitý negativ. Následuje středník a druhé pole, což je pole s tržbami. Funkci ukončíme a potvrdíme a máme správný výsledek.
Alternativně byste funkci mohli zapsat jako SOUČIN.SKALÁRNÍ, kde ověříme stejnou podmínku s funkcí ROK a nepoužijeme dvojitý negativ, ale pole vynásobíme s polem tržby. I to vrátí stejný výsledek.
V posledním příkladu chceme ověřit, kolik kódů produktů má pět znaků. Řekněme, že všechny kódy, které nemají pět znaků jsou chybné. Opět použijeme funkci SOUČIN.SKALÁRNÍ, kde ověříme podmínku. Podmínka je, že délka kódu je 5 znaků. Takže použijeme funkci DÉLKA, anglicky funkce LEN, kde ověříme, že délka je pět. A žádné jiné pole nemáme. Takže před funkci DÉLKA napíšeme dvojitý negativ, což převede pravdy na jedničky a nepravdy na nuly a výsledek se sečte. Potvrdíme funkci a vrátilo se číslo 3. 3 kódy mají pět znaků.
Tuto funkci můžeme použít i v podmíněném formátu, abychom správné kódy označili, jen ji trochu upravíme. Funkci zkopírujeme, vyskočíme z funkce klávesou ENTER, označíme kódy a na kartě Domů najdeme Podmíněný formát a vybereme Nové pravidlo a pravidlo pomocí vzorce. Do pole vložíme zkopírovanou funkci, kde akorát ve funkci délka upravíme odkaz a odkážeme se pouze na první buňku, kterou zafixujeme pro sloupec a buňku s počtem znaků zafixujeme pevně. Vybereme formát, třeba zelenou výplň buňky a vše potvrdíme. A teď máme obarvené kódy, které splňují pravidlo.
Jedna odpověď
😊