Přeskočit navigaci | Přeskočit na novinky
     

Jste zde: Úvodní stránka » excel » formatovani » ms-excel-podminene-formatovani-priklady

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

Videokurzy Excel

Ve spolupráci se SEDUO jsem vytvořil několik videokurzů:

Jak využít podmíněné formátování. - barevné označení řádku, sloupců, buněk na základě zadaných podmínek... představeno na praktických příkladech

Doplněno: 30.3.2015: Hra Bingo

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

Logo Excel 2010 - praxe

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í):

Praktický tip


Co je podmíněné formátování jsem popsal v článku o základech podmíněného formátování V následujícím textu ukážu hlavně praktické příklady využití podmíněného formátování.

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.

Podmíněné formátování - menu - MS Excel 2010

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

Podmíněné formátování - sprava pravidel - MS Excel 2010

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

Podmíněné formátování - nove pravidlo - MS Excel 2010

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ě

Podmíněné formátování - sprava pravidel - MS Excel 2010

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" Podmíněné formátování - menu - MS Excel 2010

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íněné formátování - příklad - MS Excel 2010

Podmínky

Do vzorce uvedete:

=HLEDAT($D$5;B4)

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

=$B$4:$B$19 podmíněné formátování - pravidlo - MS Excel 2010

Příklad ke stažení zdarma

Ke stažení zdarma Označení buňky pokud obsahuje zvolené slovo soubor ve formátu *.xlsx 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).

podmíněné formátování - příklad - MS Excel 2010

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 podmíněné formátování - pravidlo - MS Excel 2010

Ke stažení

Soubor podmíněné formátování - dle dne v týdnu soubor ve formátu *.xlsx 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

V MS Excelu potřebujete označovat řádky, které splňují podmínku (tj. jsou po splatnosti). Využijete podmíněné formátování.

Ukázka

MS Excel - podmíněné formátování

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 soubor ve formátu *.xls

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

Kde se nachází kurzor. Označení celého řádku a celého sloupce. Splňujících podmínku, že procházejí danou (aktivní) buňkou.

Ukázka

MS Excel - podmíněné formátování

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í soubor ve formátu *.xls

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.

Podmíněné formátování - MS Excel 2010

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ě soubor ve formátu *.xlsx 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.

MS Excel 2010 - Podmíněné formátování

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.

MS Excel 2010 - Podmíněné formátování

Ke stažení zdarma

Soubor Označ řádek, který se rovná požadované hodnotě soubor ve formátu *.xlsx 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.

Ke stažení zdarma

Soubor Označ řádek pokud jsou splněny dvě podmínky soubor ve formátu *.xlsx ke stažení zdarma, pro Excel 2010 (2007).

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í.

MS Excel 2010 - Podmíněné 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.

MS Excel 2010 - Podmíněné formátování

Ke stažení zdarma

Soubor Označ sloupec, ktery se rovná požadované hodnotě soubor ve formátu *.xlsx 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ší.

MS Excel 2010 - Podmíněné formátování

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í soubor ve formátu *.xlsx 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.

MS Excel 2010 - Podmíněné formátování

Ke stažení

Soubor Označ sudý/lichý řadek v tabulce soubor ve formátu *.xlsx ke stažení zdarma, pro Excel 2010 (2007).

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

MS Excel 2010 - Podmíněné formátování

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 soubor ve formátu *.xlsx 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.

Štětec kopírování - MS Excel 2010

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) Kontrola délky textu - MS Excel 2010

Ke stažení

Soubor Kontrola délky textu soubor ve formátu *.xlsx 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) Test - kontrola odpovědi - MS Excel 2010

Ke stažení

Soubor Test - kontrola odpovědi soubor ve formátu *.xlsx 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) Bingo

Ke stažení

Soubor BINGO soubor ve formátu *.xlsx 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

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

Ke stažení

Soubor buňky, které označeny podle časového rozmezí soubor ve formátu *.xlsx 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

Odměna

Ušetřil vám tento web čas, peníze? Pomohl vyřešit problém? Jste ochotni poskytnout symbolickou odměnu na další rozvoj? Vybrte si formu odměny, která vám vyhovuje.


Pavel Lasák - autor webu

Pavel Lasák

Microsoft Office (Word, Excel, Google tabulky, PowerPoint) se věnuji od roku 2000 (od 2004 na této doméně) - V roce 2017 jsem od Microsoft získal prestižní ocenění MVP (zatím jsem jediný z ČR v kategorií Excel). 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 15 let. Zdarma je zde přes 1.000 návodu, tipů a triků, včetně přes 250 různých šablon, sešitů.

   Pavel Lasák LinkedIn Profil    Pavel Lasák Google+ Profil    Pavel Lasák facebook Profil    Pavel Lasák twitter Profil


Komentáře

" ; // echo "kompletni_cesta :" ; // echo $adresar_pro_vkladani ; // echo "
" ; // echo " Tady bude možnost přidání komentáře" ; // echo "
" ; // echo "
" ; $kompletni_cesta = "komentare"; $adresar_pro_vkladani = $kompletni_cesta; ?> \n"; reset($polozky); while (list($key, $val) = each($polozky)) { if ($val != "." && $val != "..") { if (!is_dir($val)) { // echo "$val
\n"; $fp = FOpen ($adresar_pro_vkladani."/".$val, "r"); //otevře soubor book.dat pro čtení $data = FRead ($fp, FileSize($adresar_pro_vkladani."/".$val)); //přečte data ze souboru a uloží do proměnné "data" - kvůli tomu aby se zobrazoval poslední příspěvek nahoře FClose($fp); echo "$data"; } } } ?>





Sdílejte

Pomohl Vám návod?
Sdílejte na Facebooku, G+
LinkedIn...

Nové články


Reklama


TOPlist Licence Creative Commons webarchiv rss XML

Stránky o MS Office (Excel) produktu společnosti Microsoft. Neslouží jako technická podpora.
| Email na autora: pavel.lasak@gmail.com | Copyright © : Pavel Lasák 2004 - 2020 |