Základy Excelu - podmíněné formátování
Těžko asi najdeme tradera, který ke svému obchodování nepoužívá program Microsoft Excel. Rozhodli jsme se proto, že budeme na stránkách Finančníka občas publikovat samostatné články se zajímavými tipy pro práci s tímto programem.
V dnešním článku se podíváme na možnosti při používání logických příkazů při práci v Excelu. Je totiž rozdíl použití podmínek při formátu a podmínek při výpočtech, což někdy může uživatelům motat hlavu.
Veškeré logické příkazy v Excelu mají za úkol nám ulehčit práci v tom smyslu, že Excel za nás bude dle určitých parametrů hlídat hodnoty a podle zadaných kritérií je vyhodnocovat. Po jejich vyhodnocení provede následně příkazy, které mu nastavíme. Je to jakési pravidlo „AKCE -> REAKCE“. Důležité však je, o jaké příkazy jde a co pomocí nich chceme dokázat.
V případě formátu používáme logické podmínky hlavně, jak už název napovídá, u podmíněného formátování. V případě výpočtů je používáme u logických funkcí, bez kterých můžeme říci, že se trader ve svých tabulkách pravděpodobně nikdy neobejde.
Logika používaná pro formátování
Oproti „ručním“ úpravám grafiky buněk, kterému říkáme formátování, si v Excelu můžeme nastavit tzv. podmínky, při kterých Excel zformátuje data obsažená v jednotlivých buňkách přesně podle toho, co nadefinujeme. V Podmíněném formátování, které najdeme na kartě Domů, část Podmíněné formátování, najdeme spoustu možností jaké podmínky využít. Můžeme nastavit téměř jakoukoli podmínku. Mohou to být varianty od základních porovnání, kdy pokud bude splněna podmínka, např. hodnota v buňce bude „větší než“ zadané číslo Excel, zpracuje buňku tak, jak mu zadáme (jako parametr v připravené podmínce můžeme použít buď čistě číslo- „5“ - nebo můžeme použít i odkazy na jiné buňky - „A5“), až po složité podmínky, které musíme vyjádřit zadáním klasických funkcí. Ty používáme k nastavení všeho, čeho nelze dosáhnout pomocí předdefinovaných nástrojů podmíněného formátování.
Výběr možných podmínek je opravdu široký, Excel má už předpřipravené nejčastěji používané podmínky a to i pro různé formáty. Najdeme tu tedy podmínky pro práci jak s čísly, tak i textem, datem apod.
Nemusí však vždy nutně jít o porovnávání nebo jiné podmínky stanovené pomocí výpočtů. Předchozí varianta pravidel jsou jediná, která fungují jen v těch konkrétních buňkách, což znamená, že každá buňka je samostatně porovnána s kritérii a podle toho je následně zformátována. Všechna ostatní podmíněná formátování jsou závislá na zbývajících hodnotách buněk formátovaných podle stejných podmínek. Excel totiž umí a dokonce to i nabízí v nabídce mezi pro nejčastěji používané příkazy, vybrat z určité oblasti např. prvních 10 % hodnot. Prohlíží tedy určitou oblast tabulky a z té vybere a zformátuje dle připravených příkazů 10 % nejvyšších hodnot.
Při zadávání podmínek také není potřeba se jakkoli omezovat, pro buňky lze nastavit i více pravidel než jen jedno. Bohužel zde nepotěším uživatele starších verzí Excelu, protože do verze 2000 (a starší) lze pro buňky zadat pouze 3 podmínky. Proto jestli potřebujete pracovat s více podmínkami, doporučím upgrade na vyšší verzi. Osobně jsem však zatím nepoužil více jak dvě pravidla najednou…
Příprava logických podmínek u podmíněného formátování je však jen první část, druhou částí je nastavení toho, co má Excel provést po splnění takto připravených podmínek. Různé verze formátů nebo příprava vlastních formátů je ale spíše téma pro jiný článek.
Logika při práci s výpočty
Druhá oblast, ve které se setkáváme se zadáváním podmínek, jsou logické funkce. Jde však o dvě naprosto odlišné kategorie. Zatímco v předchozí části jsme sice zadávali podmínky, podle kterých má Excel cosi zpracovat, týkaly se tyto podmínky jen a pouze formátování těch konkrétních buněk. Při práci s logickými funkcemi ať už zadáme jakoukoli podmínku, nikdy tato podmínka nebude mít vliv na formátování buňky a samozřejmě naopak.
Při přípravě podmínek ve vzorcích nechceme po Excelu, aby něco formátoval, ale aby určité podmínky vyhodnotil a na základě toho nám vrátil buď PRAVDA či NEPRAVDA. Výsledky logických funkcí totiž nejsou běžné hodnoty, jako jsme zvyklí, ale pouze tyto dvě varianty. S těmi samozřejmě můžeme dále pracovat ať už za použití dalších funkcí nebo už přímo za použití funkce KDYŽ.
Je tedy nutné rozlišovat, pro co stanovujeme podmínky, zda nám jde o zformátování buňky (tedy barva, text, ohraničení apod.) nebo připravujeme výpočet (funkci) pro zjištění nějakého stavu.
Kombinace povolena
Je však potřeba dodat, že samozřejmě může nastat situace, kdy budete chtít připravit takovou podmínku pro formátování, kdy se nevyhnete nutnosti použít logickou funkci. Na druhou stranu je dobré, že to lze jen jedním směrem a to pouze vložením funkce do podmíněného formátování, opačně se to dá udělat těžko.
Závěrem trochu praxe
Zkuste si tedy ve svých backtesterech nejprve nastavit podmínky pro zformátování sloupečku s vašimi zisky a ztrátami tak, že ztráty budou červeně a zisky zeleně a ještě tučně. Například u této úlohy je nutné použít právě dvě podmínky. Podmínky tedy budou vypadat následovně:
1) Zvýraznit pravidla buněk -> Je větší než -> zde zadáme 0 a ve formátu zelenný text
2) Zvýraznit pravidla buněk -> Je menší než -> zde zadáme opět 0 a ve formátu vlastní, kde barvu textu zaměníme na červenou a tučné písmo
Tímto jsme přidali dvě pravidla, jež můžete následně vidět ve Správci pravidel a zde je i měnit. Zkuste sami vymyslet a upravit jedno z pravidel tak, aby byla zahrnuta i nula, kterou jsme teď vynechali…
A nyní vyzkoušíme logiku ve funkcích. Porovnejte si vstupy a výstupy a zjistěte, zda je PRAVDA, že máte výsledek větší než 0 nebo ne.
1) Do buňky vložte funkci = “adresa buňky s výsledkem“>0
Na těchto příkladech vidíte, že sice porovnáváme stejné věci, ale výsledek je vždy jiný…
Závěrem vám tedy přeji hodně zdaru při tvorbě podmínek pro formáty nebo komponování logických funkcí ve vašich tabulkách či backtesterech.
Finančník.cz