Podmienené formátovanie v Exceli – podrobne s príkladmi

Ak potrebujete vizuálne prezentovať informácie, je veľmi vhodné to urobiť pomocou podmieneného formátovania. Používateľ, ktorý túto službu využíva, ušetrí obrovské množstvo energie a času. Na získanie potrebných informácií postačí rýchle zobrazenie súboru.

Ako urobiť podmienené formátovanie v Exceli

„Podmienené formátovanie“ nájdete na úplne prvej karte pásu v časti „Štýly“. 

Ďalej musíte okom nájsť ikonu šípky trochu doprava, presuňte na ňu kurzor. Následne sa otvoria nastavenia, kde si môžete flexibilne nakonfigurovať všetky potrebné parametre. 

Ďalej musíte vybrať vhodný operátor na porovnanie požadovanej premennej s číslom. Existujú štyri porovnávacie operátory – väčší ako, menší ako, rovný a medzi. Sú uvedené v ponuke pravidiel. 

Ďalej sme špecifikovali sériu čísel v rozsahu A1:A11.

Naformátujme vhodný súbor údajov. Potom otvoríme ponuku nastavení „Podmienené formátovanie“ a nastavíme požadované kritériá výberu údajov. V našom prípade napríklad vyberieme kritérium „Viac“. 

Otvorí sa okno so sadou možností. V ľavej časti okna je pole, do ktorého musíte zadať číslo 15. V pravej časti je uvedený spôsob zvýraznenia informácií za predpokladu, že spĺňa vyššie uvedené kritérium. Výsledok sa zobrazí okamžite.

Ďalej dokončite konfiguráciu tlačidlom OK.

Na čo slúži podmienené formátovanie?

Do istej miery sa dá povedať, že je to nástroj, po naučení sa, ktorá práca s Excelom už nikdy nebude ako predtým. Je to preto, že to značne uľahčuje život. Namiesto manuálneho nastavenia formátovania bunky, ktorá zakaždým vyhovuje určitej podmienke, a tiež pokusu o to, aby ste sa sami pokúsili skontrolovať súlad s týmto kritériom, stačí nastaviť nastavenia raz a potom Excel urobí všetko sám.

Môžete napríklad zafarbiť všetky bunky, ktoré obsahujú číslo väčšie ako 100, na červenú. Alebo určte, koľko dní zostáva do ďalšej platby, a potom vyfarbite nazeleno tie bunky, v ktorých termín ešte nie je dostatočne vzdialený. 

Zoberme si príklad.

Táto tabuľka zobrazuje zásoby, ktoré sú na sklade. Má stĺpce. ako produkt, priemerný predaj (meraný v jednotkách za týždeň), zásoby a koľko týždňov tohto produktu zostáva.

Úlohou nákupného manažéra je ďalej určiť tie pozície, ktorých doplnenie je potrebné. K tomu si treba pozrieť štvrtý stĺpec zľava, ktorý eviduje zásoby tovaru podľa týždňov.

Predpokladajme, že kritériom na určenie príčiny paniky je menej ako 3 týždne zásob. To znamená, že musíme pripraviť objednávku. Ak je tovar na sklade menej ako dva týždne, znamená to, že je potrebné urýchlene zadať objednávku. Ak tabuľka obsahuje veľké množstvo pozícií, potom je ručná kontrola počtu zostávajúcich týždňov dosť problematická. Aj keď použijete vyhľadávanie. A teraz sa pozrime, ako vyzerá tabuľka, ktorá zvýrazňuje nedostatkový tovar červenou farbou.

A skutočne, navigácia je oveľa jednoduchšia. 

Je pravda, že tento príklad je vzdelávací, ktorý je v porovnaní so skutočným obrázkom trochu zjednodušený. A ušetrené sekundy a minúty sa pri pravidelnom používaní takýchto tabuliek menia na hodiny. Teraz sa stačí pozrieť do tabuľky, aby ste pochopili, o ktorý tovar je nedostatok, a netráviť hodiny analýzou každej bunky (ak sú takých tovarových položiek tisíce).

Ak existuje „žltý“ tovar, musíte ho začať kupovať. Ak je príslušná pozícia červená, musíte to urobiť okamžite. 

Podľa hodnoty inej bunky

Teraz sa pozrime na nasledujúci praktický príklad.

Predpokladajme, že máme takúto tabuľku a stojíme pred úlohou zvýrazniť riadky obsahujúce určité hodnoty. 

Ak sa teda projekt stále realizuje (to znamená, že ešte nie je dokončený, preto je označený písmenom „P“), musíme dať jeho pozadie červené. Dokončené projekty sú označené zelenou farbou.

Naša postupnosť akcií v tejto situácii bude nasledovná:

1. Vyberte rozsah hodnôt. 
2. Kliknite na tlačidlo „Podmienené formátovanie“ – „Vytvoriť pravidlo“.
3. Upozorňujeme, že v našom prípade musíte použiť vzorec vo forme pravidla. Ďalej použijeme funkciu IFna zvýraznenie zodpovedajúcich čiar. 

Ďalej vyplňte riadky, ako je znázornené na tomto obrázku.

Dôležité! Reťazce musia byť absolútne odkazované. Ak hovoríme o bunke, potom je v tomto prípade zmiešaná (s fixáciou stĺpca).

Podobne sa vytvorí pravidlo pre tie pracovné projekty, ktoré do dnešného dňa nie sú ukončené. 

Takto vyzerajú naše kritériá.

A nakoniec máme vo výsledku takúto databázu.

Viaceré podmienky

Pre názornosť si zoberme rozsah A1:A11 z tabuľky, aby sme demonštrovali, ako na reálnom praktickom príklade môžete použiť niekoľko podmienok na určenie formátovania bunky.

Samotné podmienky sú nasledovné: ak číslo v bunke presiahne hodnotu 6, potom je zvýraznené červenou farbou. Ak je zelená, potom je farba zelená. A nakoniec, najväčšie čísla, viac ako 20, budú zvýraznené žltou farbou. 

Existuje niekoľko spôsobov, ako sa podmienené formátovanie vykonáva podľa niekoľkých pravidiel.

1. Metóda 1. Vyberieme náš rozsah, po ktorom v ponuke nastavení podmieneného formátovania vyberte také pravidlo výberu buniek ako „Viac“. Naľavo je napísané číslo 6 a napravo je nastavené formátovanie. V našom prípade by výplň mala byť červená. Potom sa cyklus dvakrát zopakuje, ale už sú nastavené ďalšie parametre – viac ako 10 a zelená farba a viac ako 20 žltá farba. Dostanete takýto výsledok.
   

   

2. Metóda 2. Prejdite do hlavnej ponuky nastavení nástroja Excel „Podmienené formátovanie“. Tam nájdeme ponuku „Vytvoriť pravidlo“ a klikneme ľavým tlačidlom myši na túto položku.
   

   

    Potom, Ďalej, vyberte položku „Použiť vzorec ...“ (na obrázku zvýraznenú červeným rámom) a nastavte prvú podmienku. Potom kliknite na OK. Potom sa cyklus opakuje pre nasledujúce podmienky rovnakým spôsobom, ako je opísané vyššie, ale použije sa vzorec.

   

Je dôležité to vziať do úvahy. V našom príklade niektoré bunky zodpovedajú viacerým kritériám naraz. Excel rieši tento konflikt takto: pravidlo, ktoré je uvedené vyššie, sa použije ako prvé. 

A tu je snímka obrazovky pre lepšie pochopenie.

Napríklad máme číslo 24. Spĺňa všetky tri podmienky súčasne. V tomto prípade bude výplň, ktorá spĺňa prvú podmienku, napriek tomu, že spadá viac pod tretiu. Preto, ak je dôležité, aby čísla nad 20 boli vyplnené žltou farbou, treba dať na prvé miesto tretiu podmienku. 

Podmienené formátovanie dátumu

Teraz sa pozrime, ako funguje podmienené formátovanie s dátumami. Máme taký skvelý sortiment.

V tomto prípade musíte nastaviť pravidlo podmieneného formátovania, napríklad „Dátum“.

Potom sa otvorí dialógové okno s celým súborom podmienok. Podrobne sa s nimi môžete zoznámiť na tejto snímke obrazovky (sú uvedené na ľavej strane obrazovky vo forme zoznamu).

Stačí si vybrať ten správny a kliknúť na tlačidlo „OK“.

Vidíme, že dátumy, ktoré sa vzťahujú na posledný týždeň v čase, keď bol tento rozsah vykreslený, boli zvýraznené červenou farbou. 

Pomocou vzorcov

Sada štandardných pravidiel podmieneného formátovania je pomerne veľká. Ale situácie sú rôzne a štandardný zoznam nemusí stačiť. V tomto prípade si môžete vytvoriť vlastné pravidlo formátovania pomocou vzorca. 

Ak to chcete urobiť, kliknite na tlačidlo „Vytvoriť pravidlo“ v ponuke podmieneného formátovania a potom vyberte položku zobrazenú na snímke obrazovky.

Riadky (podľa hodnoty bunky)

Predpokladajme, že musíme zvýrazniť riadok, ktorý obsahuje bunku s určitou hodnotou. V tomto prípade musíme vykonať rovnakú postupnosť akcií ako vyššie, ale pre reťazce. To znamená, že si môžete vybrať, na ktorý rozsah sa má podmienené formátovanie vzťahovať. 

Ako vytvoriť pravidlo

Ak chcete vytvoriť pravidlo, musíte použiť príslušnú možnosť v časti „Podmienené formátovanie“. Pozrime sa na konkrétnu postupnosť akcií. 

Najprv musíte nájsť položku „Podmienené formátovanie“ na páse s nástrojmi na karte „Domov“. Dá sa rozpoznať podľa charakteristických viacfarebných buniek rôznych veľkostí, vedľa ktorých je obrázok s preškrtnutým rovnítkom. 

Existuje tlačidlo „Vytvoriť pravidlo“. Môžete tiež kliknúť na tlačidlo „Spravovať pravidlá“, po ktorom sa otvorí dialógové okno, prostredníctvom ktorého môžete vytvoriť pravidlo aj kliknutím na príslušné tlačidlo v ľavom hornom rohu. 

V tomto okne môžete vidieť aj tie pravidlá, ktoré už boli aplikované.

Pravidlá výberu buniek

Dobre, hovoríme toľko o tom, ako vytvoriť pravidlá, ale čo to je? Pravidlo výberu buniek sa týka funkcií, ktoré program berie do úvahy pri rozhodovaní o tom, ako naformátovať bunky, ktoré im vyhovujú. Podmienky môžu byť napríklad väčšie ako určité číslo, menšie ako určité číslo, vzorec atď. Je ich celá množina. Môžete sa s nimi zoznámiť a precvičiť si ich používanie „v pieskovisku“. 

Formátovanie všetkých buniek na základe ich hodnôt

Osoba môže nezávisle nastaviť rozsah: celý riadok, celý dokument alebo jednu bunku. Program sa môže sústrediť len na hodnotu jednej bunky. To dodáva procesu flexibilitu. 

Formátujte iba jedinečné alebo duplicitné bunky

Existuje veľké množstvo pravidiel priamo zapojených do programu. Môžete sa s nimi zoznámiť v ponuke „Podmienené formátovanie“. Najmä na to, aby sa formátovanie vzťahovalo výlučne na duplicitné hodnoty alebo iba na jedinečné hodnoty, existuje špeciálna možnosť - „Duplicitné hodnoty“.

Ak vyberiete túto položku, otvorí sa okno s nastaveniami, kde môžete zvoliť aj opačnú možnosť – vybrať len jedinečné hodnoty. 

Formátujte hodnoty v rozsahu pod a nad priemerom

Na formátovanie hodnôt pod alebo nad priemerom je v tej istej ponuke aj špeciálna možnosť. Musíte však vybrať ďalšie podmenu – „Pravidlá pre výber prvej a poslednej hodnoty“. 

Formátovanie iba prvej a poslednej hodnoty

V tom istom podmenu je možné zvýrazniť iba prvú a poslednú hodnotu špeciálnou farbou, typom písma a inými spôsobmi formátovania. Podľa normy existuje možnosť výberu prvých a posledných desiatich prvkov, ako aj 10% z celkového počtu buniek zahrnutých v tomto rozsahu. Používateľ však môže nezávisle určiť, koľko buniek potrebuje vybrať.

Formátovanie iba buniek so špecifickým obsahom

Ak chcete formátovať iba bunky so špecifickým obsahom, musíte vybrať pravidlo formátovania Rovná sa alebo Text obsahuje. Rozdiel medzi nimi je v tom, že v prvom prípade musí reťazec zodpovedať kritériu úplne av druhom prípade len čiastočne. 

Ako vidíte, podmienené formátovanie je multifunkčná funkcia programu Excel, ktorá poskytuje obrovské množstvo výhod osobe, ktorá ju ovláda. Vieme, že na prvý pohľad sa to všetko môže zdať komplikované. Ale v skutočnosti sa k nemu priťahujú ruky hneď, ako sa zdá, že zvýrazní určitý kus textu červenou farbou (alebo ho naformátuje akýmkoľvek iným spôsobom), na základe jeho obsahu alebo v súlade s iným kritériom. Táto funkcia je súčasťou základnej sady znalostí Excelu, bez nej nie je možná ani amatérska práca s tabuľkami a databázami.