Podmíněné formátování - Praktické příklady

Úvodem do podmíněného formátování

Teorie

doplněn teoretický úvod.

• Trocha teorie - aneb jak vlastní podmíněné formátování pomocí vzorce vložit

Seznam příkladů

Popsány jsou tyto praktické příklady (včetně souboru zdarma ke stažení):

• Podmínka aplikovaná na buňku:
  • Obsahuje hledané slovo
  • Datum odpovídá hledanému dnu například sobota
  • Obsahuje (rovná se) požadované hodnotě
  • Buňku která splňuje datum v příslušném časové rozmezí
  • Označ ne/zamčené buňky
• Podmínka s datum a čas:
  • Buňka která splňuje datum v příslušném časové rozmezí
  • Označ buňky, podle časového rozmezí
• Podmínka aplikovaná na řádek/sloupec:
  • Označení řádku na základě splněné podmínky
  • Označ celý řádek splňující podmínky - více možností podmínek
  • Označ celý sloupec splňující podmínky - více možností podmínek
  • Označit sudý/lichý řádek - v tabulce
  • Označit řádek pokud je odlišný od předchozího - vždy druhý odlišný
  • Kontrola dle délky textu - využívá funkci DÉLKA nově doplněno
  • Test - kontrola odpovědi - na základě skrytého sloupce nově doplněno
• Kombinace:
  • Kde se nachází kurzor - tj. označí aktivní buňku
• Speciálnosti...
  • Hra Bingo - pomocí podmíněného formátování
  • podmínka má platit pro dvě a více hodnot
  • podmínka kontroluje část slova

Praktický tip

• Jak podmíněné formátování kopírovat

Trocha teorie

Pokud Vám nevyhovují přednastavené formáty Microsoft Excelu (podrobněji v článku podmíněné formátování základy je k dispozici možnost nastavit si podmínky samostatně pomocí pomoci vzorce.

Nastavení provedete na kartě Domů sekce styly ikona podmíněné formátování z menu Správa pravidel...

V dialogovém okně Nové pravidlo... a v dalším zobrazeném dialogovém okně Určit buňky k formátování pomocí vzorce

Při použití podmíněného formátu za použití Určit buňky k formátování pomocí vzorce je potřeba vyplnit.

• Pravidlo - které se bude kontrolovat, zda buňka obsahuje
• Formát - jak bude buňka vypadat, pokud bude pravidlo splněno
• Platí pro - ať nemusíte pro každou buňku vytvářet jednotlivě

Platí pro

Začneme od konce. V položce platí pro uvede se oblast, pro kterou bude platit.

Formát

Jak nastavit formát buňky je popsáno v článku: Jak nastavit formát buňky.

Pravidlo

Tato položka je nejdůležitější (hlavně ji správně pochopit). Pro pochopení předpokládám základní znalost relativního a absolutního adresování.

Jako pravidlo co musí daná buňka splňovat. Zadává se první a do dalších buněk se tato první rozkopíruje. Podle zadání (absolutně relativně) se provedou příslušné odkazy. Důležité je správně zadat $. Viz ukázka po rozkopírováni $A1 (rozná se 5).

=$A1="5"

V rozkopírování se můžete podivat dle jakého vzorce se budou další buňky kontrolovat.

Poznámka

Pokud jste z mého vysvětlení nepochopily, jsou k dispozici praktické příklady, kde je teorie názorně předvedena.

Označ buňky obsahující hledané slovo

Potřebujete-li v tabulce označit buňky které obsahují slovo. V ukázkovém příkladu hledáte slovo koruna.

Ukázka

Podmínky

Do vzorce uvedete:

=HLEDAT($D$5;B4)

Platí pro uvedete (tj. oblast tabulky ve které bude pravidlo platit):

=$B$4:$B$19

Příklad ke stažení zdarma

Ke stažení zdarma Označení buňky pokud obsahuje zvolené slovo verze pro Excel 2010 (2007).

Zpět na seznam kapitol o podmíněném formátování.

Podmíněné formátování - den týdne

Potřebujete-li v tabulce označit buňce jejíž datum splňuje podmínku roznající se určitému dnu v týdnu (například sobota, nebo kombinace sobota, neděle).

Pokud znáte funkce datum a čas, konkrétně funkci DENTÝDNE. Nepotřebujete nápovědu, do vzorce podmíněného formátování uvedete:

=DENTÝDNE(B6;2)=6

Pro úterý:

=DENTÝDNE(B6;2)=2

Nastavíte platí pro celou tabulku (tj. oblast tabulky ve které bude pravidlo platit), ve které chcete, aby podmíněné formátování bylo prováděno:

=$B$4:$B$35

Ke stažení

Soubor podmíněné formátování - dle dne v týdnu ke stažení zdarma. Pro Excel 2010, 2007.

Zpět na seznam kapitol o podmíněném formátování.

Označení řádku na základě splněné podmínky

Ukázka

Příprava pro podmíněné formátování

Druhý sloupec bude udávat, kdy musí být splaceno. Třetí a další budou údaje o faktuře, dodávce atd. (co je potřeba).

První sloupec vám řekne, zda je či není daný řádek po splatnosti. Pro zjištění (zda je po splatnosti) využijete funkci KDYŽ.

• Hodnota 1 známená, že daný řádek je po splatnosti.
• Prázdná buňka, na splacení je dost času.

Aby první sloupce fungoval mustí mít někde v listu buňku s aktuálním datem např. B3. Doplníte ji o funkci =DNES(). Do prvního slupce v prním testovaném řádku (u ukázce A6) doplníte funkci:

=KDYŽ(B6<$B$3;1;"")

Nastavení podmínky podmíněného formátování

Označíme první řádek.

Přes kartu Domů - Podmíněné formátování vybereme Správa pravidel .... Přes Nové pravidlo... . Typ pravidla zvolíme Určit buňky k formátování pomocí vzorce. a vzorec zadáme =$A1.

Toto pravidlo podmíněné formátování poté rozkopírujte na další řádky.

Příklad ke stažení zdarma

Lepší než slova je praktická ukázka ke stažení zdarma Označení řádku na základě splněné podmínky

Modifikace

V příkladu se kontroluje datum, ale můžeme kontrolovat splnění dle jiného kriteria (splněný věk, zda jde o víkend, první den v měsíci, je to muž, atd.).

Zpět na seznam kapitol o podmíněném formátování.

Kde se nachází aktivní buňka

Ukázka

Nastavení podmíněného formátování

Označíme požadovanou oblast (pro kterou budou podmínky platit).

Přes kartu Domů - Podmíněné formátování vybereme Správa pravidel .... Přes Nové pravidlo... . Typ pravidla zvolíme Určit buňky k formátování pomocí vzorce. a vzorec zadáme:

1. Podmínka

=ŘÁDEK()=POLÍČKO("Řádek")

2. Podmínka

=SLOUPEC()=POLÍČKO("Sloupec")

Příklad ke stažení zdarma

Praktická ukázka ke stažení zdarma (pokud je popis málo pochopitelný) Kde se nachází kurzor - podmíněné formátování

Zpět na seznam kapitol o podmíněném formátování.

Označ buňku, která se rovná požadované hodnotě

Potřebujete-li označit jednu buňku splňující určité kritérium. Konkrétně potřebujete označit v oblasti A1:H8, číslo které se shoduje s číslem v buňce A10, kdy v oblasti jsou různá čísla ( v příladu jsou náhodně rozesetá čísla 1 - 64, ale klidně se některá čísla mohou opakovat). Pro názornost si můžete stahnout ukázkový příklad.

Praktické řešení

Pro buňku A1 nastavíte vlastní formát: Karta Domů - podmíněné formátování - nové pravidlo. Vyberete typ pavidla: Určit buňky k formátování pomocí vzorce a zadáte:

=A1=$A$10

Nastavíte výplň - třeba na žlutou a stisknete OK.

Formát buňky rozkopírujete na celou požadovanou oblast A1:H8.

Poznamka k podmíněnému formátování:

Rozebereme si vzorec =A1=$A$10

• =A1 - pro kterou buňku platí - je uvedeno relativně protože budeme rozkopírovávat a v další buňce už musí být B1, ...
• =$A$10 - protože se bude stále porovnávat s buňkou A10 - kde je uvedeno číslo, které hledáte.

Ke stažení zdarma

Soubor Označ buňku, která se rovná požadované hodnotě ke stažení zdarma, pro Excel 2010 (2007).

Poznámka: Ukázka je doplněna o číselník, kdy můžete čísla měnit (zobrazovat) pomocí tlačítek. Jedno číslo v řadě 1 až 64 chybí a jedno je 2 x ;)

Zpět na seznam kapitol o podmíněném formátování.

Označ celý řádek - splněna podmínka - Podmínění formátování

Označ celý řádek podle informace uvedené ve sloupci A, například Při uvedení Červená bude řádek červený, Při Žlutá bude žlutý atd.

Stačí nastavit tato pravidla. Pro Zelenou uvést Určit buňky k formátování pomocí vzorce

=$A1="Zelená"

Pro Červenou

=$A1="Červená"

Atd. Výsledek po vyplnění podmínek.

Ke stažení zdarma

Soubor Označ řádek, který se rovná požadované hodnotě ke stažení zdarma, pro Excel 2010 (2007).

Označ celý řádek - pokud jsou splněny dvě podmínky (pro jeden řádek)

Oznaš podmíněným formátem řádek pokud jsou splněny dvě (či více) podmínek. Ve sloupci A a zároveň B.

Zpět na seznam kapitol o podmíněném formátování.

Označ celý sloupec - více podmínek - Podmínění formátování

Označ celý sloupec podle informace uvedené v řádku 1, (například Při uvedení Červená bude sloupec červený, Při Žlutá bude sloupec žlutý atd.). řešení bude provedeno za využití podmíněného formátování.

Stačí nastavit tato pravidla. Pro Zelenou uvést v Určit buňky k formátování pomocí vzorce

=A$1="Zelená"

Pro Červenou

=A$1="Červená"

Atd. Výsledek po vyplnění podmínek.

Ke stažení zdarma

Soubor Označ sloupec, ktery se rovná požadované hodnotě ke stažení zdarma, pro Excel 2010 (2007).

Zpět na seznam kapitol o podmíněném formátování.

Označ buňku která splňuje datum a příslušné časové rozmezí

Potřebujeteli v tabulce označit buňku která odpovída datu a časovému rozmezí. Máte v tabulce řádky které představují jednotlivé dny v měsíci a sloupce, které představují časové rozmezí. Chcete automaticky označit buňku, které dle zadaného data a času splňuje obě kretériá. Z ukázky bude požadavek jasnější.

V ukázce je den 9.12 a čas 11:32. Takže je potřeba automaticky označit průsečík řádku s dnem 9 a slopuce s rozmezím 11:30 až 11:44.

Poznámka

Řešení využívá kromě podmíněného formátování i funkce KDYŽ.

Ke stažení zdarma

Soubor Označ buňku která splňuje datum a příslušné časové rozmezí ke stažení zdarma, pro Excel 2010 (2007).

Zpět na seznam kapitol o podmíněném formátování.

Speciálnosti...

Na základě komentářů pár triků:

Kontrola dvou a více podobných textů

Potřebuji označit text, který začíná Celkem a nějak pokračuje Celkem 2012, Celkem 2013, .... Možnost řešení omezit pomocí textové funkce:

=ZLEVA($A7;5)="Celkem"

nebo využít logické funkce NEBO, pokud jde jen o (Celkem 2012, Celkem 2013) a na žádnou jinou hodnotu nemá reagovat.

=NEBO($A7="Celkem 2012";$A7="Celkem 2013")

Označit sudý/lichý řádek

Jak v tabulce označovat sudé, liché řádky.

Ke stažení

Soubor Označ sudý/lichý řadek v tabulce ke stažení zdarma, pro Excel 2010 (2007).

Označit řádek pokud je odlišný od předchozího

V tabulce označovat řádky pokud dojde ke změně hodnoty. 1, 2, 2, 2, 3, 4, 5, 6, 6, 6, 8, 8 ... budou označeny řádky 1, 3, 5, 8, 8, ... viz ukázka:

Lze využít vzorec:

=SUMA(($B4:$B$4<>$B5:$B$5)/1)

Výsledkem bude řada čísel, vždy zvětšena o jedičku pokud dojde ke změně. Tím tento úkol převedete na sudé a liché hodnoty a jeho řešení je popsáno v předchozím článku. Pozor je nutno zadávat maticově - Shift+Ctrl+Enter

Nebo zadat do podmíněného formátování tento vzorec:

=MOD(SUMA(($B4:$B$4<>$B5:$B$5)/1);2)=1

Poznámka: tohle řešení jsem našel na internetu, takže není z mé hlavy, ale je elegantní tak proč jej nepublikovat v češtině. Navíc se nemusí zadávat jako maticový vzorec (podmíněné formátování to udělá za vás samo ;) )

Ke stažení

Soubor Označit řádek pokud je odlišný od předchozího ke stažení zdarma, pro Excel 2010 (2007).

Označ ne/zamčené buňky

Potřebujete-li označit v listu buňky, které jsou zamčené. Stačí využít jednoduchého vzorce.

=POLÍČKO("zámek";A1)=0

Kopírování podmíněného formátu

I podmíněné formátování je možno kopírovat (stejně jako klasický formát) pomocí ikony štětce kopírovat formát.

Poznámka: Musí být správně nastaveno odkazování (relativní, absolutní, smíšené).

Kontrola délky textu

Je potřeba označit celý řádek, pokud daná buňka v řádku obsahuje více než daný počet znaků. Za využití funkce DÉLKA. Případně pokud daná buňka neobsahuje přesně stanovaný počet znaků.

Pro délku větší než 6 znaků

=(DÉLKA($B5)>6)

Pro délku různou od 5 znaků

=(DÉLKA($B16)<>5)

Ke stažení

Soubor Kontrola délky textu ke stažení zdarma, pro Excel 2010 (2007).

Test - kontrola odpovědi

Je potřeba zkontrolovat odpověď, který je ve skrytém řádku. Můžete využít pro své děti pro testy matematiky, na učení slovíčet, atd. Pokud neodpovíte dobře buňka se zabarví (například načerveno). Případně pokud je odpověď správná zabarví se zeleně.

Pouze špatné odpovědi

=A($C5<>$E5;DÉLKA($E5)>0)

Špatné odpovědi i dobré odpovědi

=$E18=$C18 a =A($C18<>$E18;DÉLKA($E18)>0)

Ke stažení

Soubor Test - kontrola odpovědi ke stažení zdarma, pro Excel 2010 (2007).

BINGO

Je potřeba Označit na kartičkách hry BINGO vylosovaná čísla.

Podmínka pro přehllednost rozdělena na dvě části

=NEBO(D2=$U$2;D2=$U$3;D2=$U$4;D2=$U$5;D2=$U$6;D2=$U$7;D2=$U$8;D2=$U$9;D2=$U$10;D2=$U$11;D2=$U$12;D2=$U$13;D2=$U$14) =NEBO(D2=$V$2;D2=$V$3;D2=$V$4;D2=$V$5;D2=$V$6;D2=$V$7;D2=$V$8;D2=$V$9;D2=$V$10;D2=$V$11;D2=$V$12;D2=$V$13;D2=$V$14)

Ke stažení

Soubor BINGO ke stažení zdarma. Od 2007 až (2016).

Označ buňky, podle časového rozmezí

Potřebujeme označit buňky (celý řádek) podle datumu, který je starší o požadovaný počet dnů. Například před 5 dny (před tři dny) a poté od dnešního dne a novější.

V buňce E6 je uloženo aktuální (dnešní) datum.

Kódy

=$B5>=$E$6 =$B5>=($E$6-3) =$B5>=($E$6-5)

Ukázka

Ke stažení

Soubor buňky, které označeny podle časového rozmezí ke stažení zdarma, pro Excel 2010 (2007).

Závěrem

O další příklady si můžete napsat v komentářích.

Článek byl aktualizován: 19.09.2020 10:56

Pavel Lasák - autor webu

Microsoft Office (Word, Excel, Google tabulky, PowerPoint) se věnuji od roku 2000 (od dubna roku 2004 na této doméně) - V roce 2017 jsem od Microsoft získal prestižní ocenění MVP (zatím 8x za sebou). Své vědomosti a zkušenosti dávám k dispozici i on-line ve videích pro SEDUO. Ve firmách školím a konzultuji, učím na MUNI. Tento web již tvořím přes 20 let (o Excel píší přes 25). Zdarma je zde přes 1.500 návodu, tipů a triků, včetně přes 350 různých šablon, sešitů a přes 70 taháků v pdf.