Podmíněné formátování v Excelu – podrobně s příklady

Pokud potřebujete vizuálně prezentovat informace, je velmi vhodné to udělat pomocí podmíněného formátování. Uživatel, který tuto službu využívá, ušetří obrovské množství energie a času. K získání potřebných informací stačí rychlý náhled do souboru.

Jak provést podmíněné formátování v Excelu

„Podmíněné formátování“ najdete na úplně první kartě pásu v části „Styly“. 

Dále musíte okem najít ikonu šipky trochu doprava, přesuňte na ni kurzor. Poté se otevře nastavení, kde můžete flexibilně konfigurovat všechny potřebné parametry. 

Dále musíte vybrat vhodný operátor pro porovnání požadované proměnné s číslem. Existují čtyři operátory porovnání – větší než, menší než, rovno a mezi. Jsou uvedeny v nabídce pravidel. 

Dále jsme specifikovali řadu čísel v rozsahu A1:A11.

Zformátujme vhodnou datovou sadu. Poté otevřeme nabídku nastavení „Podmíněné formátování“ a nastavíme požadovaná kritéria výběru dat. V našem případě například vybereme kritérium „Více“. 

Otevře se okno se sadou možností. V levé části okna je pole, do kterého je třeba zadat číslo 15. V pravé části je uveden způsob zvýraznění informací, pokud splňuje dříve zadané kritérium. Výsledek se zobrazí okamžitě.

Dále dokončete konfiguraci tlačítkem OK.

K čemu je podmíněné formátování?

Do jisté míry se dá říci, že jde o nástroj, po jehož naučení už práce s Excelem už nikdy nebude jako dřív. Je to proto, že to výrazně usnadňuje život. Namísto ručního nastavování formátování buňky, která pokaždé odpovídá určité podmínce, a také pokusu sami zkontrolovat, zda je v souladu s tímto kritériem, stačí nastavit nastavení jednou a Excel pak udělá vše sám.

Můžete například nechat zčervenat všechny buňky, které obsahují číslo větší než 100. Nebo určete, kolik dní zbývá do příští platby, a pak zeleně vybarvěte ty buňky, ve kterých ještě není dostatečně daleko termín. 

Vezměme si příklad.

Tato tabulka zobrazuje zásoby, které jsou na skladě. Má sloupce. jako produkt, průměrné prodeje (měřeno v jednotkách za týden), zásoby a kolik týdnů tohoto produktu zbývá.

Dále je úkolem nákupního manažera určit ty pozice, jejichž doplnění je nutné. K tomu je třeba se podívat na čtvrtý sloupec zleva, který eviduje skladové zásoby zboží po týdnech.

Předpokládejme, že kritériem pro určení příčiny paniky je méně než 3 týdny zásob. To znamená, že musíme připravit objednávku. Pokud je sklad zboží méně než dva týdny, znamená to, že je nutné urychleně zadat objednávku. Pokud tabulka obsahuje velké množství pozic, je ruční kontrola zbývajících týdnů poměrně problematická. I když použijete vyhledávání. A nyní se podívejme, jak vypadá tabulka, která červeně zvýrazňuje nedostatkové zboží.

A skutečně, orientace je mnohem jednodušší. 

Pravda, tento příklad je výchovný, který je oproti skutečnému obrázku poněkud zjednodušený. A ušetřené vteřiny a minuty se pravidelným používáním takových tabulek promění v hodiny. Nyní se stačí podívat do tabulky, abyste pochopili, kterého zboží je nedostatek, a netrávit hodiny rozborem každé buňky (pokud jsou takových komoditních položek tisíce).

Pokud existuje „žluté“ zboží, musíte je začít kupovat. Pokud je odpovídající pozice červená, musíte to udělat okamžitě. 

Podle hodnoty jiné buňky

Nyní se podívejme na následující praktický příklad.

Předpokládejme, že máme takovou tabulku a stojíme před úkolem zvýraznit řádky obsahující určité hodnoty. 

Pokud se tedy projekt stále provádí (to znamená, že ještě není dokončen, proto je označen písmenem „P“), musíme jeho pozadí nastavit červeně. Dokončené projekty jsou označeny zeleně.

Naše sekvence akcí v této situaci bude následující:

1. Vyberte rozsah hodnot. 
2. Klikněte na tlačítko „Podmíněné formátování“ – „Vytvořit pravidlo“.
3. Upozorňujeme, že v našem případě musíte vzorec použít ve formě pravidla. Dále použijeme funkci IFpro zvýraznění odpovídajících čar. 

Dále vyplňte řádky, jak je znázorněno na tomto obrázku.

Důležité! Řetězce musí být odkazovány absolutně. Pokud odkazujeme na buňku, pak je v tomto případě smíšená (s fixací sloupce).

Podobně je vytvořeno pravidlo pro ty pracovní projekty, které nebyly do dnešního dne dokončeny. 

Takto vypadají naše kritéria.

A nakonec máme ve výsledku takovou databázi.

Více podmínek

Pro názornost si vezmeme rozsah A1:A11 z tabulky, abychom demonstrovali, jak na reálném praktickém příkladu můžete použít několik podmínek k určení formátování buňky.

Samotné podmínky jsou následující: pokud číslo v buňce překročí hodnotu 6, pak je zvýrazněno červeně. Pokud je zelená, pak je barva zelená. A nakonec největší čísla, více než 20, budou zvýrazněna žlutě. 

Existuje několik způsobů, jak podmíněné formátování probíhá podle několika pravidel.

1. Metoda 1. Vybereme náš rozsah, poté v nabídce nastavení podmíněného formátování vyberte takové pravidlo výběru buněk jako „Více“. Vlevo je napsáno číslo 6 a vpravo se nastavuje formátování. V našem případě by výplň měla být červená. Poté se cyklus dvakrát opakuje, ale již jsou nastaveny další parametry – více než 10 a zelená barva a více než 20 a žlutá barva. Dostanete takový výsledek.
   

   

2. Metoda 2. Přejděte do hlavní nabídky nastavení nástroje Excel „Podmíněné formátování“. Zde najdeme nabídku „Vytvořit pravidlo“ a klikneme levým tlačítkem myši na tuto položku.
   

   

    Poté, Dále, vyberte položku „Použít vzorec…“ (na obrázku zvýrazněnou červeným rámečkem) a nastavte první podmínku. Poté klikněte na OK. A pak se cyklus opakuje pro následující podmínky stejným způsobem, jak je popsáno výše, ale použije se vzorec.

   

Je důležité s tím počítat. V našem příkladu některé buňky odpovídají několika kritériím najednou. Excel řeší tento konflikt následovně: pravidlo, které je uvedeno výše, se použije jako první. 

A zde je snímek obrazovky pro lepší pochopení.

Máme například číslo 24. Splňuje všechny tři podmínky zároveň. V tomto případě bude výplň splňující první podmínku, přestože spadá spíše pod třetí. Pokud je tedy důležité, aby čísla nad 20 byla vyplněna žlutou barvou, musí být na prvním místě třetí podmínka. 

Podmíněné formátování data

Nyní se podívejme, jak funguje podmíněné formátování s daty. Máme takový skvělý sortiment.

V tomto případě musíte nastavit pravidlo podmíněného formátování, například „Datum“.

Poté se otevře dialogové okno s celou sadou podmínek. Podrobně se s nimi můžete seznámit na tomto snímku obrazovky (jsou uvedeny na levé straně obrazovky ve formě seznamu).

Stačí si vybrat ten správný a kliknout na tlačítko „OK“.

Vidíme, že data, která se vztahují k poslednímu týdnu v době, kdy bylo toto rozmezí vylosováno, byla zvýrazněna červeně. 

Pomocí vzorců

Sada standardních pravidel podmíněného formátování je poměrně velká. Ale situace jsou různé a standardní seznam nemusí stačit. V tomto případě můžete vytvořit vlastní pravidlo formátování pomocí vzorce. 

Chcete-li to provést, klikněte v nabídce podmíněného formátování na tlačítko „Vytvořit pravidlo“ a poté vyberte položku zobrazenou na snímku obrazovky.

Řádky (podle hodnoty buňky)

Předpokládejme, že potřebujeme zvýraznit řádek, který obsahuje buňku s určitou hodnotou. V tomto případě musíme provést stejnou sekvenci akcí jako výše, ale pro řetězce. To znamená, že si můžete vybrat, na který rozsah se má podmíněné formátování vztahovat. 

Jak vytvořit pravidlo

Chcete-li vytvořit pravidlo, musíte použít příslušnou možnost v části „Podmíněné formátování“. Podívejme se na konkrétní sled akcí. 

Nejprve musíte najít položku „Podmíněné formátování“ na pásu karet na kartě „Domů“. Pozná se podle charakteristických vícebarevných buněk různých velikostí s obrázkem s přeškrtnutým rovnítkem. 

Je zde tlačítko „Vytvořit pravidlo“. Můžete také kliknout na tlačítko „Spravovat pravidla“, po kterém se otevře dialogové okno, ve kterém můžete také vytvořit pravidlo kliknutím na odpovídající tlačítko v levém horním rohu. 

V tomto okně můžete také vidět ta pravidla, která již byla aplikována.

Pravidla výběru buněk

Dobře, mluvíme tolik o tom, jak vytvořit pravidla, ale co to je? Pravidlo výběru buněk odkazuje na funkce, které program bere v úvahu při rozhodování, jak formátovat buňky, které jim vyhovují. Podmínky mohou být například větší než určité číslo, menší než určité číslo, vzorec a tak dále. Je jich celá množina. Můžete se s nimi seznámit a procvičit si jejich použití „na pískovišti“. 

Formátování všech buněk na základě jejich hodnot

Osoba může nezávisle nastavit rozsah: celý řádek, celý dokument nebo jednu buňku. Program se může zaměřit pouze na hodnotu jedné buňky. To dodává procesu flexibilitu. 

Formátujte pouze jedinečné nebo duplicitní buňky

Existuje velké množství pravidel přímo zapojených do programu. Můžete se s nimi seznámit v nabídce „Podmíněné formátování“. Zejména, aby se formátování vztahovalo výhradně na duplicitní hodnoty nebo pouze na jedinečné hodnoty, existuje speciální možnost – „Duplicitní hodnoty“.

Pokud vyberete tuto položku, otevře se okno s nastavením, kde můžete zvolit i opačnou možnost – vybrat pouze jedinečné hodnoty. 

Formátujte hodnoty v rozsahu pod a nad průměrem

Chcete-li formátovat hodnoty pod nebo nad průměrem, existuje také speciální možnost ve stejné nabídce. Musíte však vybrat další podnabídku – „Pravidla pro výběr první a poslední hodnoty“. 

Formátování pouze první a poslední hodnoty

Ve stejné podnabídce je možné zvýraznit pouze první a poslední hodnotu speciální barvou, fontem a dalšími způsoby formátování. Podle standardu je zde možnost výběru prvních a posledních deseti prvků a také 10 % z celkového počtu buněk zahrnutých v tomto rozsahu. Ale uživatel může nezávisle určit, kolik buněk potřebuje vybrat.

Formátování pouze buněk s konkrétním obsahem

Chcete-li formátovat pouze buňky s určitým obsahem, musíte vybrat pravidlo formátování Rovná se nebo Text obsahuje. Rozdíl mezi nimi je v tom, že v prvním případě musí řetězec odpovídat kritériu zcela a ve druhém pouze částečně. 

Jak vidíte, podmíněné formátování je multifunkční funkce programu Excel, která poskytuje obrovské množství výhod osobě, která ji zvládla. Víme, že na první pohled se to všechno může zdát složité. Ale ve skutečnosti jsou k němu přitahovány ruce, jakmile se zdá, že zvýrazní určitý kus textu červeně (nebo jej formátuje jiným způsobem), na základě jeho obsahu nebo v souladu s jiným kritériem. Tato funkce je součástí základní sady znalostí Excelu, bez ní se neobejde ani amatérská práce s tabulkami a databázemi.