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.
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.
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).
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É.
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 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.
Když už jsme načali funkci COUNTIF, je vhodné ji zmínit v souvislosti s hledáním duplicitních hodnot.
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ů).
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).
Přeci jen se ještě budeme věnovat nakrátko posloupností 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.
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í.
Tak barvičkování á la Tabulka máme, a co bloky řádků?
Nemůže chybět ani úloha s požadavkem barvení každého n-tého řádku.
Řá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.
Přihodím ještě formátování š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.
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í.