Podmíněné formátování s pomocí vzorce

Podmíněné formátování s pomocí vzorce je tématem pro pokročilejší uživatele. Zatímco začátky necháváme na průvodcích, jako je Zvýraznit pravidla buněk a další nástroje (pruhy, škály, ikony), komplikovanější pravidla tvoříme sami. Měli bychom přitom už mít znalosti stran tvorby vzorců, řady funkcí a logiky. Hodí se jednoduše všechno možné. Užitý vzorec má podobu jednoduchého výroku (testu, podmínky), např. =D2=B2, tj. nepoužívá funkci KDYŽ.

Jak definovat pravidlo pro více buněk naráz

Formát je možné nastavit pro jednu buňku a do ostatních buněk jej následně rozšířit (kopírováním, tažením za vyplňovací úchyt pravým tlačítkem myši). Nicméně doporučuji efektivnější způsob.

1. Vybereme předem (souvislou) oblast buněk, na nichž chceme formát aplikovat.
2. Karta Domů / skupina Styly / Podmíněné formátování / Nové pravidlo / Určit buňky k formátování pomocí vzorce.
3. Vzorec pro pravidlo vztahujeme na první (levou horní) buňku oblasti. Excel sám toho pravidlo posléze rozšíří na všechny buňky výběru a to s ohledem na styl adresování buněk (relativní, absolutní, smíšené)! Chování je tedy prakticky stejné, jaké vykazuje vyplňovací úchyt na listu.

Podmíněné formátování s pomocí vzorce

Podmíněné formátování s pomocí vzorce

Tip: Pokud výstup neodpovídá požadavku, zkuste si pravidlo nejprve na listu. Zajímá vás samozřejmě hodnota PRAVDA coby výsledek vzorce.

Testování pravidla podmíněného formátování

Testování pravidla podmíněného formátování

Příklady na podmíněné formátování

Jako první předkládám jakýsi značkovač hodnot v oblasti. Využívá vstupní seznam a Data / Ověření. Tentokrát bylo výsledku dosaženo ještě díky průvodci, nicméně již dokážete příklad přepsat s pomocí vlastního vzorce (viz výše).

Značkovač

Značkovač

Následuje ukázka porovnávání položek v rámci řádku. Je-li rozhodující velikost písmen, obraťte se na funkci STEJNÉ.

Porovnávání položek v řádku

Porovnávání položek v řádku

Další úloha se zabývá více kritérii v rámci jednoho pravidla. V řešení se objevují logické funkce A („a současně“, AND) a NEBO (OR).

Více kritérii v rámci jednoho pravidla

Více kritérii v rámci jednoho pravidla

V požadavcích se mohou uplatnit i zástupné znaky * (žádný, jeden, nebo více znaků) a ? (jeden libovolný znak). V daném případě hledáme jména začínající na písmeno J, resp. pětiznaková jména, a obarvujeme celé záznamy.

Zástupné znaky

Zástupné znaky

Když už jsme načali funkci COUNTIF, je vhodné ji zmínit v souvislosti s hledáním duplicitních hodnot.

Jedinečné a duplicitní hodnoty

Jedinečné a duplicitní hodnoty

První dvě tabulky ukazují řešení s pomocí průvodce i vlastního vzorce, který se v obou případech výrazně neliší. U třetí tabulky si povšimněte drobné niance v adresování (koncová buňka). Funkce COUNTIF zde má kumulativní charakter (expanduje s přibývajícím řádkem).

Typická je potřeba vizuálně odlišit položky jednoho seznamu, které se (ne)vyskytují ve druhém (porovnávání dat ze dvou zdrojů).

Vyhledávání v seznamu

Vyhledávání v seznamu

U posloupností zpravidla nasazujeme pravidlo až od druhé buňky. Další varianty si jistě zpracujete sami (záměna srovnávacího operátoru, matematické operace).

Posloupnosti

Posloupnosti

Přeci jen se ještě budeme věnovat nakrátko posloupností datumů.

Posloupnosti datumů

Posloupnosti datumů

Zajímavou techniku představuje druhý vzoreček aplikovaný na sloupci K. Tuším, že svého času s ním příšel kolega Radek Jureček.

No a co by to bylo za podmíněné formátování, kdybychom nezmínili obarvování víkendů a svátků v pomyslném kalendáři. Vystačíme si s vyhledávací funkcí (NE neguje výsledek výroku JE.NEDEF) a funkcí DENTÝDNE.

Formátování víkendů a svátků

Formátování víkendů a svátků

Teď si otevřeme malé hračkářství, jejímž majitelem je funkce MOD. Spolu se zaměstanci ŘÁDEK, SLOUPEC, ŘÁDKY a SLOUPCE dokáže divy.

Liché a sudé řádky jsem se naučil řešit s pomocí funkce MOD. Funkce ISODD a ISEVEN jsem nechal stranou ze dvou důvodů. Jednak (pokud se pamatuji) byly dříve součástí doplňku Analytické nástroje, jednak mám z minulosti zkušenost, že zlobily právě v podmíněném formátování.

Liché a sudé řádky

Liché a sudé řádky

Tak barvičkování á la Tabulka máme, a co bloky řádků?

Bloky řádků

Bloky řádků

Nemůže chybět ani úloha s požadavkem barvení každého n-tého řádku.

Každý n-tý řádek

Každý n-tý řádek

Řádky prvního sloupce splňují podmínku barvení „první z n-prvkové skupiny“, ve druhém sloupci se jedná o negaci takové úlohy, a ve třetím je řešení pro „každý n-tý z n-prvkové množiny“.

Následující analogické příklady přizpůsobené pro sloupce.

Liché a sudé sloupce

Liché a sudé sloupce

Bloky sloupců

Bloky sloupců

Každý n-tý sloupec

Každý n-tý sloupec

Přihodím ještě formátování šachovnice.

Šachovnice

Šachovnice

Pozn. Možná jste se při procházení příkladů pozastavili nad konstrukcí typu MOD(ŘÁDEK-ŘÁDEK), resp. MOD(SLOUPEC-SLOUPEC). Je zde proto, aby ošetřila možný posun tabulky, a aby se pořadí řádků (sloupců) vztahovalo k počátku tabulky, nikoli k počátku listu (A1). I zde je podstatný styl adresování v odkazech.

Na závěr trocha maticového pojetí – hlavní diagonála, horní a dolní trojúhelníková matice a ještě něco navrch.

Hlavní diagonála, trojúhelníkové matice a něco navíc...

Hlavní diagonála, trojúhelníkové matice a něco navíc…

S výjimkou formátování datumů jsme dnes aplikovali pouze jedno pravidlo na dané oblasti. Jaké vztahy a zákonitosti platí při vícero pravidlech, si přečtěte v článku Pravidla pro podmíněné formátování.

Příloha
podminene_formatovani_priklady.zip

Klikni a stahuj!