2 spôsoby, ako zmeniť farbu výplne buniek v Exceli na základe ich hodnôt

V tomto článku nájdete dva rýchle spôsoby, ako zmeniť farbu bunky na základe jej hodnoty v Exceli 2013, 2010 a 2007. Dozviete sa tiež, ako používať vzorce v Exceli na zmenu farby prázdnych buniek alebo bunky s chybami vzorcov.

Každý vie, že ak chcete zmeniť farbu výplne jednej bunky alebo celého rozsahu v Exceli, stačí kliknúť na tlačidlo Farba výplne (Farba výplne). Čo ak však potrebujete zmeniť farbu výplne všetkých buniek obsahujúcich určitú hodnotu? Navyše, čo ak chcete, aby sa farba výplne každej bunky automaticky menila pri zmene obsahu danej bunky? Ďalej v článku nájdete odpovede na tieto otázky a získate niekoľko užitočných tipov, ktoré vám pomôžu vybrať správnu metódu riešenia každého konkrétneho problému.

• Zmena farby výplne buniek na základe ich hodnôt (dynamicky) – Farba výplne sa zmení automaticky, keď sa zmení hodnota bunky.
• Zmena farby bunky na základe jej aktuálnej hodnoty (staticky) – Po nastavení sa farba výplne nezmení bez ohľadu na to, ako sa zmení hodnota bunky.
• Zmena farby určitých buniek (prázdne, s chybami, so vzorcami)

Ako dynamicky zmeniť farbu bunky v Exceli na základe jej hodnoty

Farba výplne sa bude meniť v závislosti od hodnoty bunky.

Problém: Máte tabuľku alebo rozsah údajov a chcete zmeniť farbu výplne buniek na základe ich hodnôt. Okrem toho je potrebné, aby sa táto farba dynamicky menila, odrážajúc zmeny v údajoch v bunkách.

Rozhodnutie: Použite podmienené formátovanie v Exceli na zvýraznenie hodnôt väčších ako X, menších ako Y alebo medzi X a Y.

Povedzme, že máte zoznam cien plynu v rôznych štátoch a chcete ceny, ktoré sú vyššie ako $ 3.7, boli zvýraznené červenou farbou a menšie alebo rovnaké $ 3.45 - zelená.

Poznámka: Snímky obrazovky pre tento príklad boli urobené v Exceli 2010, avšak v Exceli 2007 a 2013 budú tlačidlá, dialógové okná a nastavenia úplne rovnaké alebo mierne odlišné.

Takže, tu je to, čo musíte urobiť krok za krokom:

1. Vyberte tabuľku alebo rozsah, v ktorom chcete zmeniť farbu výplne bunky. V tomto príklade zvýrazňujeme $ B $ 2: $ H $ 10 (záhlavia stĺpcov a prvý stĺpec obsahujúci názvy štátov nie sú vybraté).
2. Kliknite na tlačidlo Domov (Domov), v sekcii Štýly (Štýly) kliknite Podmienené formátovanie (Podmienené formátovanie) > Nové pravidlá (Vytvorte pravidlo).
3. V hornej časti dialógového okna Nové pravidlo formátovania (Vytvoriť pravidlo formátovania) v poli Vyberte typ pravidla (Vyberte typ pravidla) vyberte Formátovať len bunky, ktoré obsahujú (Formátovať len bunky, ktoré obsahujú).
4. V spodnej časti dialógového okna v poli Formátovať iba bunky s (Formátovať len bunky, ktoré spĺňajú nasledujúcu podmienku) Nastavte podmienky pre pravidlo. Zvolili sme formátovanie iba buniek s podmienkou: Hodnota bunky (hodnota bunky) – väčší ako (viac) - 3.7ako je znázornené na obrázku nižšie.Potom stlačte tlačidlo Veľkosť (Formát) vyberte, ktorá farba výplne sa má použiť, ak je splnená špecifikovaná podmienka.
5. V zobrazenom dialógovom okne formátovanie buniek (Formátovať bunky). Vyplniť (Vyplniť) a vybrať farbu (my sme zvolili červenkastú) a kliknúť OK.
6. Potom sa vrátite do okna Nové pravidlo formátovania (Vytvorenie pravidla formátovania) kde v poli náhľad (Ukážka) zobrazí ukážku vášho formátovania. Ak ste spokojní, kliknite OK.

Výsledok vašich nastavení formátovania bude vyzerať asi takto:

Keďže musíme nastaviť ďalšiu podmienku, ktorá nám umožní zmeniť farbu výplne na zelenú pre bunky s hodnotami menšími alebo rovnými 3.45, potom znova stlačte tlačidlo Nové pravidlá (Vytvoriť pravidlo) a zopakujte kroky 3 až 6, čím nastavíte požadované pravidlo. Nižšie je uvedený príklad druhého pravidla podmieneného formátovania, ktoré sme vytvorili:

Keď je všetko pripravené - kliknite OK. Teraz máte pekne naformátovanú tabuľku, ktorá vám umožní na prvý pohľad vidieť maximálne a minimálne ceny plynu v rôznych štátoch. Dobré pre nich tam, v Texase! 🙂

Tip: Rovnakým spôsobom môžete zmeniť farbu písma v závislosti od hodnoty bunky. Ak to chcete urobiť, stačí otvoriť kartu písmo (Písmo) v dialógovom okne formátovanie buniek (Formát bunky), ako sme to urobili v kroku 5, a vyberte požadovanú farbu písma.

Ako nastaviť konštantnú farbu bunky na základe jej aktuálnej hodnoty

Po nastavení sa farba výplne nezmení, bez ohľadu na to, ako sa v budúcnosti zmení obsah bunky.

Problém: Chcete upraviť farbu bunky na základe jej aktuálnej hodnoty a chcete, aby farba výplne zostala rovnaká, aj keď sa hodnota bunky zmení.

Rozhodnutie: Pomocou nástroja vyhľadajte všetky bunky s konkrétnou hodnotou (alebo hodnotami). Nájsť všetko (Nájsť všetko) a potom pomocou dialógového okna zmeňte formát nájdených buniek formátovanie buniek (formát bunky).

Toto je jedna z tých zriedkavých úloh, pre ktoré neexistuje vysvetlenie v súboroch pomocníka programu Excel, na fórach alebo blogoch a pre ktoré neexistuje priame riešenie. A to je pochopiteľné, pretože táto úloha nie je typická. A predsa, ak potrebujete zmeniť farbu výplne bunky natrvalo, to znamená raz a navždy (alebo kým ju nezmeníte ručne), postupujte podľa týchto krokov.

Nájdite a vyberte všetky bunky, ktoré spĺňajú danú podmienku

Tu je možných niekoľko scenárov v závislosti od typu hodnoty, ktorú hľadáte.

Ak chcete vyfarbiť bunky konkrétnou hodnotou, napr. 50, 100 or 3.4 – potom na karte Domov (Domov) v sekcii Editácia (Úprava) kliknite Nájsť Vybrať (Nájsť a zvýrazniť) > nájsť (Nájsť).

Zadajte požadovanú hodnotu a kliknite Nájsť všetko (Nájsť všetko).

Tip: Na pravej strane dialógového okna Nájsť a nahradiť (Nájsť a nahradiť) je tu tlačidlo možnosti (Možnosti), stlačením ktorého získate prístup k množstvu rozšírených nastavení vyhľadávania, ako napr Match Case (rozlišujú sa malé a veľké písmená) a Porovnajte obsah celej bunky (Celá bunka). Môžete použiť zástupné znaky, ako napríklad hviezdičku (*) na zhodu s ľubovoľným reťazcom znakov alebo otáznik (?) na zhodu s ľubovoľným jedným znakom.

Pokiaľ ide o predchádzajúci príklad, ak potrebujeme nájsť všetky ceny benzínu z 3.7 na 3.799, potom nastavíme nasledujúce kritériá vyhľadávania:

Teraz kliknite na niektorú z nájdených položiek v spodnej časti dialógového okna Nájsť a nahradiť (Nájsť a nahradiť) a kliknite Ctrl +na zvýraznenie všetkých nájdených záznamov. Potom stlačte tlačidlo Fermer (Zavrieť).

Takto môžete pomocou voľby vybrať všetky bunky s danou hodnotou (hodnotami). Nájsť všetko (Nájsť všetko) v Exceli.

V skutočnosti však musíme nájsť všetky ceny benzínu, ktoré presahujú $ 3.7. Bohužiaľ nástroj Nájsť a nahradiť (Nájsť a nahradiť) nám s tým nemôže pomôcť.

Zmeňte farby výplne vybratých buniek pomocou dialógového okna Formát buniek

Teraz máte vybraté všetky bunky s danou hodnotou (alebo hodnotami), práve sme to urobili pomocou nástroja Nájsť a nahradiť (Nájsť a nahradiť). Jediné, čo musíte urobiť, je nastaviť farbu výplne pre vybrané bunky.

Otvorte dialógové okno formátovanie buniek (Formát bunky) jedným z 3 spôsobov:

• stlačením Ctrl + 1.
• kliknutím na ľubovoľnú vybranú bunku pravým tlačidlom myši a výberom položky z kontextového menu formátovanie buniek (formát bunky).
• pútko Domov (Domov) > Bunky. Bunky. (bunky) > Veľkosť (Formát) > formátovanie buniek (formát bunky).

Ďalej upravte možnosti formátovania, ako chcete. Tentokrát pre zmenu nastavíme farbu výplne na oranžovú 🙂

Ak chcete zmeniť iba farbu výplne bez toho, aby ste sa dotkli ostatných možností formátovania, môžete jednoducho kliknúť na tlačidlo Farba výplne (Farba výplne) a vyberte farbu, ktorá sa vám páči.

Tu je výsledok našich zmien formátovania v Exceli:

Na rozdiel od predchádzajúcej metódy (s podmieneným formátovaním) sa takto nastavená farba výplne nikdy nezmení bez vášho vedomia, bez ohľadu na to, ako sa zmenia hodnoty.

Zmeňte farbu výplne pre špeciálne bunky (prázdne, s chybou vo vzorci)

Rovnako ako v predchádzajúcom príklade môžete zmeniť farbu výplne konkrétnych buniek dvoma spôsobmi: dynamicky a staticky.

Použite vzorec na zmenu farby výplne špeciálnych buniek v Exceli

Farba bunky sa automaticky zmení v závislosti od hodnoty bunky.

Tento spôsob riešenia problému s najväčšou pravdepodobnosťou použijete v 99% prípadov, to znamená, že výplň buniek sa zmení v súlade s vami zadanou podmienkou.

Vezmime si napríklad opäť tabuľku cien benzínu, ale tentoraz pridáme niekoľko ďalších stavov a niektoré bunky vyprázdnime. Teraz sa pozrite, ako môžete nájsť tieto prázdne bunky a zmeniť farbu ich výplne.

1. Na karte Rozšírené Domov (Domov) v sekcii Štýly (Štýly) kliknite Podmienené formátovanie (Podmienené formátovanie) > Nové pravidlá (Vytvorte pravidlo). Rovnako ako v 2. kroku príkladu Ako dynamicky meniť farbu bunky na základe jej hodnoty.
2. V dialógovom okne Nové pravidlo formátovania (Vytvorte pravidlo formátovania) vyberte možnosť Na určenie buniek použite vzorec formátovať (Použite vzorec na určenie, ktoré bunky sa majú formátovať). Ďalej do terénu Formátujte hodnoty, kde platí tento vzorec (Formátovať hodnoty, pre ktoré platí nasledujúci vzorec) zadajte jeden zo vzorcov:
   • zmeniť výplň prázdnych buniek

     =ISBLANK()

     =ЕПУСТО()

   • zmeniť tieňovanie buniek obsahujúcich vzorce, ktoré vracajú chybu

     =ISERROR()

     =ЕОШИБКА()

   Keďže chceme zmeniť farbu prázdnych buniek, potrebujeme prvú funkciu. Zadajte ho, umiestnite kurzor do zátvoriek a kliknite na ikonu výberu rozsahu na pravej strane riadku (alebo zadajte požadovaný rozsah ručne):

   =ISBLANK(B2:H12)

   =ЕПУСТО(B2:H12)

   

3. lis Veľkosť (Formát), vyberte požadovanú farbu výplne na karte Vyplniť (Vyplniť) a potom kliknite OK. Podrobné pokyny sú uvedené v kroku 5 príkladu „Ako dynamicky meniť farbu bunky na základe jej hodnoty“. Príklad vami nastaveného podmieneného formátovania bude vyzerať takto:
4. Ak ste s farbou spokojní, kliknite OK. Uvidíte, ako sa vytvorené pravidlo okamžite aplikuje na tabuľku.

Staticky zmeňte farbu výplne špeciálnych buniek

Po nakonfigurovaní zostane výplň nezmenená bez ohľadu na hodnotu bunky.

Ak chcete nastaviť trvalú farbu výplne pre prázdne bunky alebo bunky so vzorcami, ktoré obsahujú chyby, použite túto metódu:

1. Vyberte tabuľku alebo rozsah a kliknite F5na otvorenie dialógového okna Prejsť na (Skočiť), potom stlačte tlačidlo špeciálna (Zlatý klinec).
2. V dialógovom okne Prejdite na položku Špeciálne (Vyberte skupinu buniek) začiarknite túto možnosť polotovary (Prázdne bunky), ak chcete vybrať všetky prázdne bunky.Ak chcete zvýrazniť bunky obsahujúce vzorce s chybami, začiarknite túto možnosť vzorca (vzorce) > chyby (Chyby). Ako môžete vidieť na obrázku vyššie, máte k dispozícii mnoho ďalších nastavení.
3. Nakoniec zmeňte výplň vybratých buniek alebo nastavte akékoľvek iné možnosti formátovania pomocou dialógového okna formátovanie buniek (Formátovať bunky), ako je popísané v časti Zmena výplne vybratých buniek.

Nezabudnite, že takto vykonané nastavenia formátovania sa zachovajú aj vtedy, keď sa prázdne bunky naplnia hodnotami alebo sa opravia chyby vo vzorcoch. Je ťažké si predstaviť, že by niekto mohol potrebovať ísť touto cestou, okrem účelov experimentu 🙂