V dnešním videu se podíváme na tři pravidla, která si musíte pamatovat u použití podmíněné formátu na základě vzorce. Podmíněný formát pomocí vzorce je nejflexibilnějším formátem, jelikož vám dovoluje na základě vlastní logiky stanovit, jak se buňky zformátují a nejste tak odkázáni pouze na přednastavené možnosti v podmíněném formátu.
Excelový soubor ke stažení:
U podmíněného formátování vzorcem existují tři pravidla, která si musíte pamatovat proto, aby vám podmíněné formátování vzorcem fungovalo přesně tak, jak potřebujete, a to vždy. Tyto pravidla si ukážeme na praktických příkladech.
I. pravidlo – PRAVDA a NEPRAVDA
Prvním pravidlem je, že výsledkem vzorce nebo funkce, který chceme použít v podmíněném formátu musí být pravda nebo nepravda. Eventuálně musí být výsledkem jedničky nebo nuly, jelikož i ty se v Excelu překládají jako pravda a nepravda. Stejně tak, pokud bude výsledkem vzorce jakékoliv kladné číslo, tak se uplatní podmíněný formát. Podmíněný formát se uplatní na hodnoty, který vzorec vyhodnotil jako pravdu, jedničku nebo kladné číslo. Na hodnoty, kde byla výsledkem vzorce nepravda nebo nula, tak na ty se formát neuplatní.
Když pravidlo pro podmíněný formát tvoříte, tak není na škodu nejprve otestovat vzorec nebo funkci vedle tabulky, abyste ověřili, že pravidlo funguje, tak jak potřebujete.
V příkladu máme tabulku faktur, včetně datumů, kdy byly faktury splatné. Potřebujeme označit faktury, které jsou po splatnosti k dnešnímu dni. Nejprve vedle tabulky vymyslíme vzorec a když budeme mít jistotu, že funguje, tak ho teprve použijeme v pravidlu pro podmíněný formát.
Začneme v první buňce, kde musíme ověřit, že je datum na řádku menší nebo rovno dnešnímu datumu a dnešní datum vyjádříme dynamicky pomocí funkce DNES. To je celé pravidlo, které potvrdíme a stáhneme ho pro všechny řádky dolů. Na řádcích, na kterých je pravda, je pravidlo splněné, tedy datum splatnosti je menší nebo rovno dnešnímu datumu a tím pádem se jedná o faktury, které jsou po splatnosti. Výsledkem vzorce je série pravd a nepravd, což splňuje první pravidlo pro podmíněný formát. Když víme, že vzorec funguje, tak ho můžeme zkopírovat. Zkopírujeme vzorec z příkazového řádku a vyskočíme ze vzorce pomocí klávesy ENTER.
II. pravidlo – Označení buněk
Druhým pravidlem je, že než otevřeme podmíněný formát, tak musíme nejprve označit buňky, na které chceme podmíněný formát uplatnit. Řekněme, že chceme obarvit celé řádky tabulky, na kterých je faktura po splatnosti. Označíme tedy celou tabulku. Na kartě Domů najdeme podmíněný formát a v nabídce vybereme Nové pravidlo a Určit buňky k formátování pomocí vzorce.
Klikneme do pole a vložíme do něj zkopírovaný vzorec.
III. pravidlo – Správná fixace buněk
Třetím pravidlem je správná fixace buněk. Vždy musíte správně určit, které buňky ve vzorci nebo funkci zafixovat pevně a které relativně. K tomu si musíte představit, jak se pravidlo v označených buňkách bude vyhodnocovat. Na pozadí Excelu totiž běží porovnání pravidla vzorcem pro každou označenou buňku. To znamená, že aby se v tomto případě označil celý řádek, na kterém je faktura po splatnosti, tak se bude pravidlo porovnávat v každé označené buňce. Tím pádem musíme zafixovat buňku, ve které máme splatnost faktury pro sloupce. Tím, že jsme zafixovali buňku pro sloupec, tak se na řádku každá buňka vztahuje k datumu splatnosti na daném řádku a porovnává se s dnešním datumem, tím pádem, pokud je pravidlo splněné, tak se v každé buňce na řádku vrátí pravda a nebo nepravda, pokud pravidlo splněné není. Tím pádem dojde k obarvení celého řádku, jelikož bude na celém řádku v každé buňce pravda.
Vybereme ještě formát buňky, třeba nějakou světlou výplň a potvrdíme pravidlo.
Podmíněný formát správně označil celé řádky, na kterých je faktura po splatnosti.
Vyhodnocení pravidla si můžeme ukázat vedle tabulky. V tabulce nejprve ve sloupci splatnost ověříme pravidlo, že je datum menší nebo rovno dnešnímu datumu. Tento vzorec stáhneme dolů a máme stejný výsledek jako v pomocném sloupci. Jelikož ale chceme obarvit celé řádky, tak se musí pravidlo vyhodnotit v každé označené buňce, takže pravidlo přetáhneme doleva. A vidíme, že se nevrací správný výsledek, jelikož buňka nebyla zafixovaná a tím pádem se teď společnost a hodnota porovnávají s funkcí DNES, což je nesmysl.
Naopak, pokud bychom buňku zafixovali pevně jak pro řádek a sloupec, a vzorec přetáhli doleva a dolů, tak se vrátí pravda všude, jelikož se každá buňka bude vyhodnocovat k prvnímu datu splatnosti.
Jediným správným řešením tedy je, zafixovat buňku pro sloupec. Zafixujeme buňku a přetáhneme vzorec a teď máme na celých řádcích pravdy a nebo nepravdy. Jelikož se každá buňka vztahuje k datumu a tím se na celých řádcích vrátí stejná hodnota.
Pokud by nám naopak stačilo obarvit pouze datumu splatnosti, tak stačí označit hodnoty datumů, otevřít podmíněné formátování a do pole napsat vzorec. V tomto případě nemusíme buňku nijak fixovat, jelikož jsme označili pouze jeden sloupec hodnot. Vybereme formát a potvrdíme a teď máme obarvené pouze datumy, které jsou po splatnosti.
V posledním příkladu si ukážeme, jak můžeme pravidla i zkombinovat z pomocí funkce A. V tabulce chceme zvýraznit hodnotu, kde se potkává vybraná společnost a měsíc. Chceme tedy zvýraznit průsečík řádku a sloupce. A pravidlo zkusíme rovnou napsat v podmíněném formátu. Nejprve označíme číselné hodnoty v tabulce, následně otevřeme podmíněný formát pomocí vzorce a začneme tvořit pravidlo. Použijeme logickou podmínku A (funkce AND), kde nejprve ověříme podmínku, že se produkt na řádku rovná vybranému produktu a produkt na řádku zafixujeme pro sloupec. Druhá podmínka je, že se měsíc v záhlaví rovná vybranému měsíci a měsíc v záhlaví musí být zafixovaný pro řádek.
=A($A32=$A$29;A$31=$B$29)
Vybereme formát a potvrdíme a máme obarvenou průsečíkovou hodnotu.
2 komentáře
😊
Dobrý den, připomenete mi prosím, jak se fixují buňky? (jaká je kláv. zkr. pro dolar) a základní formát funkce, když chci uplatnit pravidlo pouze pro vyplněné buňky se starším nebo dnešním datem. Tzn. aby mi to vrátilo NEPRAVDU pro prázdné buňky a data větší než dnes. Předem moc děkuji, P.