2 způsoby, jak změnit barvu výplně buněk v Excelu na základě jejich hodnot

V tomto článku najdete dva rychlé způsoby, jak změnit barvu buňky na základě její hodnoty v Excelu 2013, 2010 a 2007. Také se dozvíte, jak pomocí vzorců v Excelu změnit barvu prázdných buněk nebo buňky s chybami vzorce.

Každý ví, že chcete-li změnit barvu výplně jedné buňky nebo celého rozsahu v aplikaci Excel, stačí kliknout na tlačítko Vyplň barvu (Vyplň barvu). Co když ale potřebujete změnit barvu výplně všech buněk obsahujících určitou hodnotu? Navíc, co když chcete, aby se barva výplně každé buňky automaticky měnila se změnou obsahu této buňky? Dále v článku najdete odpovědi na tyto otázky a získáte několik užitečných tipů, které vám pomohou vybrat správnou metodu řešení každého konkrétního problému.

• Změna barvy výplně buněk na základě jejich hodnot (dynamicky) – Barva výplně se automaticky změní, když se změní hodnota buňky.
• Změna barvy buňky na základě její aktuální hodnoty (staticky) – Po nastavení se barva výplně nezmění, bez ohledu na to, jak se změní hodnota buňky.
• Změna barvy určitých buněk (prázdných, s chybami, se vzorci)

Jak dynamicky změnit barvu buňky v Excelu na základě její hodnoty

Barva výplně se bude měnit v závislosti na hodnotě buňky.

Problém: Máte tabulku nebo rozsah dat a chcete změnit barvu výplně buněk na základě jejich hodnot. Navíc je nutné, aby se tato barva dynamicky měnila a odrážela změny v datech v buňkách.

Rozhodnutí: Pomocí podmíněného formátování v Excelu zvýrazněte hodnoty větší než X, menší než Y nebo mezi X a Y.

Řekněme, že máte seznam cen plynu v různých státech a chcete ceny, které jsou vyšší než $ 3.7, byly zvýrazněny červeně a menší nebo stejné $ 3.45 - zelená.

Poznámka: Snímky obrazovky pro tento příklad byly pořízeny v Excelu 2010, avšak v Excelu 2007 a 2013 budou tlačítka, dialogy a nastavení úplně stejné nebo mírně odlišné.

Zde je to, co musíte udělat krok za krokem:

1. Vyberte tabulku nebo oblast, ve které chcete změnit barvu výplně buňky. V tomto příkladu zdůrazňujeme $ B $ 2: $ H $ 10 (nejsou vybrány nadpisy sloupců a první sloupec obsahující názvy států).
2. Klepněte na tlačítko Domů (Domů), v sekci Styly (Styly) klikněte Podmíněné formátování (Podmíněné formátování) > Nová pravidla (Vytvořte pravidlo).
3. V horní části dialogového okna Nové pravidlo pro formátování (Vytvořit pravidlo formátování) v poli Vyberte typ pravidla (Vyberte typ pravidla) vyberte Naformátujte pouze buňky, které obsahují (Formátujte pouze buňky, které obsahují).
4. V dolní části dialogového okna v poli Formátovat pouze buňky pomocí (Formátovat pouze buňky, které splňují následující podmínku) Nastavte podmínky pro pravidlo. Zvolíme formátování pouze buněk s podmínkou: Hodnota buňky (hodnota buňky) – větší než (více) - 3.7jak je znázorněno na obrázku níže.Poté stiskněte tlačítko Velikost (Formát) a vyberte, která barva výplně se má použít, pokud je splněna zadaná podmínka.
5. V zobrazeném dialogovém okně Formát buněk (Formátovat buňky). Vyplnit (Vyplňte) a vyberte barvu (my jsme zvolili načervenalou) a klikněte OK.
6. Poté se vrátíte k oknu Nové pravidlo pro formátování (Vytvoření pravidla formátování) kde v poli Náhled (Ukázka) zobrazí ukázku vašeho formátování. Pokud jste spokojeni, klikněte OK.

Výsledek vašeho nastavení formátování bude vypadat nějak takto:

Protože musíme nastavit další podmínku, která nám umožní změnit barvu výplně na zelenou u buněk s hodnotami menšími nebo rovnými 3.45a poté znovu stiskněte tlačítko Nová pravidla (Create Rule) a opakujte kroky 3 až 6 a nastavte požadované pravidlo. Níže je ukázka druhého pravidla podmíněného formátování, které jsme vytvořili:

Když je vše připraveno - klikněte OK. Nyní máte pěkně zformátovanou tabulku, která vám umožní na první pohled vidět maximální a minimální ceny plynu v různých státech. Dobře pro ně tam, v Texasu! 🙂

Tip: Stejným způsobem můžete změnit barvu písma v závislosti na hodnotě buňky. Chcete-li to provést, stačí otevřít kartu Písmo (Písmo) v dialogovém okně Formát buněk (Formát buňky) stejně jako v kroku 5 a vyberte požadovanou barvu písma.

Jak nastavit konstantní barvu buňky na základě její aktuální hodnoty

Po nastavení se barva výplně nezmění, bez ohledu na to, jak se v budoucnu změní obsah buňky.

Problém: Chcete upravit barvu buňky na základě její aktuální hodnoty a chcete, aby barva výplně zůstala stejná, i když se hodnota buňky změní.

Rozhodnutí: Najděte všechny buňky s konkrétní hodnotou (nebo hodnotami) pomocí nástroje Najít vše (Najít vše) a poté pomocí dialogového okna změňte formát nalezených buněk Formát buněk (formát buňky).

Toto je jeden z těch vzácných úkolů, pro které neexistuje žádné vysvětlení v souborech nápovědy aplikace Excel, na fórech nebo blozích a pro které neexistuje žádné přímé řešení. A to je pochopitelné, protože tento úkol není typický. A přesto, pokud potřebujete změnit barvu výplně buňky trvale, tedy jednou provždy (nebo dokud ji nezměníte ručně), postupujte takto.

Najděte a vyberte všechny buňky, které splňují danou podmínku

Zde je možných několik scénářů v závislosti na typu hodnoty, kterou hledáte.

Pokud chcete obarvit buňky konkrétní hodnotou, např. 50, 100 or 3.4 – pak na kartě Domů (Domů) v sekci Editace (Editace) klikněte Najít Vybrat (Najít a zvýraznit) > Najít (Nalézt).

Zadejte požadovanou hodnotu a klikněte Najít vše (Najít vše).

Tip: Na pravé straně dialogového okna Najít a nahradit (Najít a nahradit) je tlačítko možnosti (Možnosti), jehož stisknutím získáte přístup k řadě rozšířených nastavení vyhledávání, jako je např Zápas Case (rozlišují malá a velká písmena) a Porovnejte celý obsah buňky (Celá buňka). Můžete použít zástupné znaky, jako je hvězdička (*) pro shodu s libovolným řetězcem znaků nebo otazník (?) pro shodu s libovolným jedním znakem.

Pokud jde o předchozí příklad, pokud potřebujeme najít všechny ceny benzínu z 3.7 na 3.799, pak nastavíme následující kritéria vyhledávání:

Nyní klikněte na kteroukoli z nalezených položek ve spodní části dialogového okna Najít a nahradit (Najít a nahradit) a klikněte Ctrl +pro zvýraznění všech nalezených záznamů. Poté stiskněte tlačítko Fermer (Zavřít).

Takto můžete pomocí volby vybrat všechny buňky s danou hodnotou (hodnotami). Najít vše (Najít vše) v Excelu.

Ve skutečnosti však musíme najít všechny ceny benzínu, které přesahují $ 3.7. Bohužel nástroj Najít a nahradit (Find and Replace) nám s tím nemůže pomoci.

Změňte barvy výplně vybraných buněk pomocí dialogového okna Formát buněk

Nyní máte vybrané všechny buňky s danou hodnotou (nebo hodnotami), právě to jsme provedli nástrojem Najít a nahradit (Najít a nahradit). Vše, co musíte udělat, je nastavit barvu výplně pro vybrané buňky.

Otevřete dialogové okno Formát buněk (Formát buňky) jedním ze 3 způsobů:

• stisknutím Ctrl + 1.
• kliknutím na libovolnou vybranou buňku pravým tlačítkem myši a výběrem položky z kontextové nabídky Formát buněk (formát buňky).
• Karta Domů (Domů) > Buňky. Buňky. (buňky) > Velikost (Formát) > Formát buněk (formát buňky).

Dále upravte možnosti formátování, jak chcete. Tentokrát nastavíme barvu výplně na oranžovou, jen pro změnu 🙂

Pokud chcete změnit pouze barvu výplně, aniž byste se dotkli ostatních možností formátování, můžete jednoduše kliknout na tlačítko Vyplň barvu (Vyplňte barvu) a vyberte barvu, která se vám líbí.

Zde je výsledek našich změn formátování v Excelu:

Na rozdíl od předchozí metody (s podmíněným formátováním) se takto nastavená barva výplně nikdy nezmění bez vašeho vědomí, bez ohledu na to, jak se změní hodnoty.

Změnit barvu výplně pro speciální buňky (prázdné, s chybou ve vzorci)

Stejně jako v předchozím příkladu můžete změnit barvu výplně konkrétních buněk dvěma způsoby: dynamicky a staticky.

Ke změně barvy výplně speciálních buněk v Excelu použijte vzorec

Barva buňky se automaticky změní v závislosti na hodnotě buňky.

Tento způsob řešení problému s největší pravděpodobností využijete v 99 % případů, to znamená, že se náplň buněk změní v souladu s vámi zadanou podmínkou.

Vezměme si například znovu tabulku cen benzínu, ale tentokrát přidáme několik dalších stavů a ​​některé buňky vyprázdníme. Nyní se podívejte, jak můžete najít tyto prázdné buňky a změnit jejich barvu výplně.

1. Na kartě Upřesnit Domů (Domů) v sekci Styly (Styly) klikněte Podmíněné formátování (Podmíněné formátování) > Nová pravidla (Vytvořte pravidlo). Stejně jako ve 2. kroku příkladu Jak dynamicky měnit barvu buňky na základě její hodnoty.
2. V dialogovém okně Nové pravidlo pro formátování (Vytvořit pravidlo formátování) vyberte možnost Pomocí vzorce určete, které buňky formátovat (Použijte vzorec k určení, které buňky se mají formátovat). Dále do terénu Formátovat hodnoty, kde je tento vzorec pravdivý (Formátujte hodnoty, pro které platí následující vzorec) zadejte jeden ze vzorců:
   • změnit výplň prázdných buněk

     =ISBLANK()

     =ЕПУСТО()

   • změnit stínování buněk obsahujících vzorce, které vracejí chybu

     =ISERROR()

     =ЕОШИБКА()

   Protože chceme změnit barvu prázdných buněk, potřebujeme první funkci. Zadejte jej, umístěte kurzor do hranatých závorek a klikněte na ikonu výběru rozsahu na pravé straně řádku (nebo zadejte požadovaný rozsah ručně):

   =ISBLANK(B2:H12)

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

   

3. lis Velikost (Formát), vyberte požadovanou barvu výplně na kartě Vyplnit (Vyplnit) a potom klepněte na OK. Podrobné pokyny jsou uvedeny v kroku 5 příkladu „Jak dynamicky změnit barvu buňky na základě její hodnoty“. Ukázka vámi nastaveného podmíněného formátování bude vypadat nějak takto:
4. Pokud jste s barvou spokojeni, klikněte OK. Uvidíte, jak se vytvořené pravidlo okamžitě aplikuje na tabulku.

Změňte barvu výplně speciálních buněk staticky

Po konfiguraci zůstane výplň nezměněna bez ohledu na hodnotu buňky.

Pokud chcete nastavit trvalou barvu výplně pro prázdné buňky nebo buňky se vzorci, které obsahují chyby, použijte tuto metodu:

1. Vyberte tabulku nebo rozsah a klikněte F5pro otevření dialogu Přejít na (Skok), poté stiskněte tlačítko Speciální (Zvýraznit).
2. V dialogovém okně Přejít na speciální (Vyberte skupinu buněk) zaškrtněte volbu polotovary (Prázdné buňky), chcete-li vybrat všechny prázdné buňky.Pokud chcete zvýraznit buňky obsahující vzorce s chybami, zaškrtněte volbu Vzorce (vzorce) > chyby (Chyby). Jak můžete vidět na obrázku výše, máte k dispozici mnoho dalších nastavení.
3. Nakonec změňte výplň vybraných buněk nebo nastavte jakékoli jiné možnosti formátování pomocí dialogového okna Formát buněk (Formátovat buňky), jak je popsáno v části Změna výplně vybraných buněk.

Nezapomeňte, že takto provedená nastavení formátování zůstanou zachována, i když jsou prázdné buňky vyplněny hodnotami nebo jsou opraveny chyby ve vzorcích. Je těžké si představit, že by někdo mohl potřebovat jít touto cestou, kromě účelů experimentu 🙂