Podmíněné formátování v aplikaci Excel: Konečný průvodce s příklady

Podmíněné formátování je jednou z nejjednodušších, ale výkonných funkcí v tabulkách aplikace Excel.

Jak název napovídá, můžete použít podmíněné formátování v Excelu, když chcete zvýraznit buňky, které splňují zadanou podmínku.

Poskytuje vám možnost rychle přidat do sady dat vrstvu vizuální analýzy. Pomocí podmíněného formátování v aplikaci Excel můžete vytvářet tepelné mapy, zobrazovat rostoucí/klesající ikony, Harveyho bubliny a mnoho dalšího.

Použití podmíněného formátování v aplikaci Excel (příklady)

V tomto tutoriálu vám ukážu sedm úžasných příkladů použití podmíněného formátování v Excelu:

• Rychle identifikujte duplikáty pomocí podmíněného formátování v aplikaci Excel.
• Zvýrazněte buňky s hodnotou větší/menší než číslo v datové sadě.
• Zvýraznění horních/dolních hodnot 10 (nebo 10%) v datové sadě.
• Zvýraznění chyb/mezer pomocí podmíněného formátování v aplikaci Excel.
• Vytváření tepelných map pomocí podmíněného formátování v aplikaci Excel.
• Zvýrazněte každý N -tý řádek/sloupec pomocí podmíněného formátování.
• Hledejte a zvýrazněte pomocí podmíněného formátování v Excelu.

1. Rychle identifikujte duplikáty

K identifikaci duplikátů v datové sadě lze použít podmíněné formátování v Excelu.

Zde je návod, jak to udělat:

• Vyberte datovou sadu, ve které chcete zvýraznit duplikáty.
• Přejděte na Domů -> Podmíněné formátování -> Zvýraznění pravidel buňky -> Duplicitní hodnoty.
• V dialogovém okně Duplikovat hodnoty zkontrolujte, zda je v levém rozevíracím seznamu vybrána možnost Duplikovat. Formát, který se má použít, můžete určit pomocí pravé rozevírací nabídky. Existují některé existující formáty, které můžete použít, nebo můžete zadat vlastní formát pomocí možnosti Vlastní formát.
• Klikněte na OK.

To by okamžitě zvýraznilo všechny buňky, které mají ve vybrané sadě dat duplikát. Vaše datová sada může být v jednom sloupci, více sloupcích nebo v nesousedícím rozsahu buněk.

Viz také: The Ultimate Guide to Find and remove Duplicates in Excel.

2. Zvýrazněte buňky s hodnotou větší/menší než číslo

Podmíněné formátování v Excelu můžete použít k rychlému zvýraznění buněk, které obsahují hodnoty větší/menší než zadaná hodnota. Například zvýraznění všech buněk s hodnotou prodeje nižší než 100 milionů nebo zvýraznění buněk se značkami menšími, než je prahová hodnota.

Zde jsou kroky, jak toho dosáhnout:

• Vyberte celou datovou sadu.
• Přejít na domovskou stránku -> Podmíněné formátování -> Zvýraznění pravidel buňky -> Větší než… / Méně než…
• Podle toho, kterou možnost vyberete (větší nebo menší než), se otevře dialogové okno. Řekněme, že vyberete možnost „Větší než“. V dialogovém okně zadejte číslo do pole vlevo. Záměrem je zvýraznit buňky, které mají číslo větší než toto zadané číslo.

• Pomocí rozevíracího seznamu vpravo určete formát, který se má použít na buňky, které splňují podmínku. Existují některé existující formáty, které můžete použít, nebo můžete zadat vlastní formát pomocí možnosti Vlastní formát.
• Klikněte na OK.

To by okamžitě zvýraznilo všechny buňky s hodnotami většími než 5 v datové sadě.Poznámka: Pokud chcete zvýraznit hodnoty větší než rovné 5, měli byste znovu použít podmíněné formátování s kritérii „Equal To“.

Stejným postupem lze zvýraznit buňky s hodnotou menší než zadané hodnoty.

3. Zvýraznění nahoře/dole 10 (nebo 10%)

Podmíněné formátování v aplikaci Excel může rychle identifikovat 10 nejlepších položek nebo 10% ze sady dat. To může být užitečné v situacích, kdy chcete v datech o prodeji rychle vidět nejlepší kandidáty podle skóre nebo hodnot nejlepších nabídek.

Podobně můžete také rychle identifikovat 10 nejnižších položek nebo 10% v datové sadě.

Zde jsou kroky, jak toho dosáhnout:

• Vyberte celou datovou sadu.
• Přejděte na Domů -> Podmíněné formátování -> Pravidla pro horní / dolní část -> Top 10 položek (nebo %) / Dolních 10 položek (nebo %).
• Na základě toho, co vyberete, se otevře dialogové okno. Řekněme, že jste vybrali 10 nejlepších položek, otevře se dialogové okno, jak je uvedeno níže:
• Pomocí rozevíracího seznamu vpravo určete formát, který se má použít na buňky, které splňují podmínku. Existují některé existující formáty, které můžete použít, nebo můžete zadat vlastní formát pomocí možnosti Vlastní formát.
• Klikněte na OK.

To by okamžitě zvýraznilo 10 nejlepších položek ve vybrané datové sadě. Všimněte si, že to funguje pouze pro buňky, které mají číselnou hodnotu.

Pokud máte v datové sadě méně než 10 buněk a vyberete možnosti pro zvýraznění Top 10 položek/Bottom 10 Items, všechny buňky se zvýrazní.

Zde je několik příkladů, jak by podmíněné formátování fungovalo:

4. Zvýraznění chyb/mezer

Pokud pracujete se spoustou číselných dat a výpočtů v aplikaci Excel, věděli byste, že je důležité identifikovat a zpracovat buňky, které mají chyby nebo jsou prázdné. Pokud jsou tyto buňky použity v dalších výpočtech, mohlo by to vést k chybným výsledkům.

Podmíněné formátování v aplikaci Excel vám pomůže rychle identifikovat a zvýraznit buňky, které obsahují chyby nebo jsou prázdné.

Předpokládejme, že máme datovou sadu, jak je uvedeno níže:

Tato sada dat obsahuje prázdnou buňku (A4) a chyby (A5 a A6).

Zde jsou kroky k zvýraznění buněk, které jsou prázdné nebo obsahují chyby:

• Vyberte datovou sadu, ve které chcete zvýraznit prázdné buňky a buňky s chybami.
• Přejděte na Domů -> Podmíněné formátování -> Nové pravidlo.
• V dialogovém okně Nové pravidlo formátování vyberte Použít vzorec k určení, které buňky chcete formátovat.
• Do pole v části „Upravit popis pravidla“ zadejte následující vzorec:
  = NEBO (ISBLANK (A1), ISERROR (A1))
  • Výše uvedený vzorec kontroluje všechny buňky pro dvě podmínky - zda je prázdné nebo ne a zda obsahuje chybu nebo ne. Pokud je některá z podmínek PRAVDA, vrátí PRAVDU.
• Nastavte formát, který chcete použít na buňky, které jsou prázdné nebo obsahují chyby. Chcete -li to provést, klikněte na tlačítko Formát. Otevře se dialogové okno „Formát buněk“, kde můžete určit formát.
• Klikněte na Ok.

To by okamžitě zvýraznilo všechny buňky, které jsou buď prázdné, nebo v nich jsou chyby.

Poznámka: V podmíněném formátování nemusíte ve vzorci používat celý rozsah A1: A7. Výše uvedený vzorec používá pouze A1. Když použijete tento vzorec na celý rozsah, Excel zkontroluje jednu buňku najednou a upraví referenci. Když například kontroluje A1, používá vzorec = NEBO (ISBLANK (A1), ISERROR (A1)). Když zkontroluje buňku A2, použije vzorec = NEBO (ISBLANK (A2), ISERROR (A2)). Automaticky upravuje referenci (protože se jedná o relativní reference) podle toho, která buňka je analyzována. Nemusíte tedy pro každou buňku psát samostatný vzorec. Excel je dost chytrý na to, aby sám změnil odkaz na buňku 🙂

Viz také: Použití IFERROR a ISERROR ke zpracování chyb v aplikaci Excel.

5. Vytváření tepelných map

Tepelná mapa je vizuální reprezentace dat, kde barva představuje hodnotu v buňce. Můžete například vytvořit tepelnou mapu, kde je buňka s nejvyšší hodnotou zbarvena zeleně a při poklesu hodnoty dojde k posunu směrem k červené barvě.

Něco, jak je uvedeno níže:

Výše uvedený soubor dat má hodnoty mezi 1 a 100. Buňky jsou zvýrazněny na základě hodnoty v něm. 100 dostane zelenou barvu, 1 získá červenou barvu.

Zde jsou kroky k vytvoření tepelných map pomocí podmíněného formátování v aplikaci Excel.

• Vyberte datovou sadu.
• Přejděte na Domů -> Podmíněné formátování -> Barevné stupnice a vyberte jedno z barevných schémat.

Jakmile kliknete na ikonu tepelné mapy, použije se formátování na datovou sadu. Můžete si vybrat z několika barevných přechodů. Pokud nejste spokojeni s existujícími možnostmi barev, můžete vybrat více pravidel a určit požadovanou barvu.

Poznámka: Podobným způsobem můžete také použít sady Data Bard a Icon.

6. Zvýrazněte každý další řádek/sloupec

Chcete -li zvýšit čitelnost dat, můžete zvýraznit alternativní řádky.

Říká se jim zebra linky a mohly by být obzvláště užitečné při tisku dat.

Nyní existují dva způsoby, jak tyto zebry vytvořit. Nejrychlejším způsobem je převést tabulková data do tabulky aplikace Excel. Na alternativní řádky automaticky aplikovalo barvu. Více si o tom můžete přečíst zde.

Dalším způsobem je použití podmíněného formátování.

Předpokládejme, že máte datovou sadu, jak je uvedeno níže:

Zde jsou kroky k zvýraznění alternativních řádků pomocí podmíněného formátování v Excelu.

• Vyberte datovou sadu. Ve výše uvedeném příkladu vyberte A2: C13 (což vylučuje záhlaví). Pokud chcete zahrnout také záhlaví, vyberte celou sadu dat.
• Otevřete dialogové okno Podmíněné formátování (Domů-> Podmíněné formátování-> Nové pravidlo) [Klávesová zkratka - Alt + O + D].
• V dialogovém okně vyberte „Použít vzorec k určení, které buňky chcete formátovat“.
• Do pole v části „Upravit popis pravidla“ zadejte následující vzorec:
  = ISODD (ŘÁDEK ())

• Výše uvedený vzorec zkontroluje všechny buňky a pokud je ŘÁDKOVÉ číslo buňky liché, vrátí hodnotu PRAVDA. Zadaný podmíněný formát by byl použit na všechny buňky, které vracejí hodnotu TRUE.
• Nastavte formát, který chcete použít na buňky, které jsou prázdné nebo obsahují chyby. Chcete -li to provést, klikněte na tlačítko Formát. Otevře se dialogové okno „Formát buněk“, kde můžete určit formát.
• Klikněte na OK.

A je to! Alternativní řádky v datové sadě budou zvýrazněny.

Stejnou techniku ​​můžete použít v mnoha případech. Vše, co musíte udělat, je použít příslušný vzorec v podmíněném formátování. Zde jsou nějaké příklady:

• Zvýrazněte alternativní sudé řádky: = ISEVEN (ROW ())
• Zvýrazněte alternativní přidat řádky: = ISODD (ROW ())
• Zvýrazněte každý 3. řádek: = MOD (ROW (), 3) = 0

7. Hledejte a zvýrazněte data pomocí podmíněného formátování

Toto je trochu pokročilé použití podmíněného formátování. Budeš vypadat jako excelentní rocková hvězda.

Předpokládejme, že máte datovou sadu, jak je uvedeno níže, s názvy produktů, obchodním zástupcem a geografií. Cílem je zadat řetězec do buňky C2 a pokud se shoduje s daty v jakékoli buňce (buňkách), mělo by se to zvýraznit. Něco, jak je uvedeno níže:

Zde jsou kroky k vytvoření této funkce Hledat a zvýraznit:

• Vyberte datovou sadu.
• Přejděte na Domů -> Podmíněné formátování -> Nové pravidlo (Klávesová zkratka - Alt + O + D).
• V dialogovém okně Nové pravidlo formátování vyberte možnost „Pomocí vzorce určit, které buňky chcete formátovat“.
• Do pole v části „Upravit popis pravidla“ zadejte následující vzorec:
  = AND ($ C $ 2 ””, $ C $ 2 = B5)
• Nastavte formát, který chcete použít na buňky, které jsou prázdné nebo obsahují chyby. Chcete -li to provést, klikněte na tlačítko Formát. Otevře se dialogové okno „Formát buněk“, kde můžete určit formát.
• Klikněte na OK.

A je to! Když nyní do buňky C2 zadáte cokoli a stisknete klávesu Enter, zvýrazní se všechny odpovídající buňky.

Jak to funguje?

Vzorec použitý v podmíněném formátování vyhodnotí všechny buňky v datové sadě. Řekněme, že zadáte Japonsko do buňky C2. Nyní by Excel vyhodnotil vzorec pro každou buňku.

Vzorec by vrátil buňku PRAVDA, pokud jsou splněny dvě podmínky:

• Buňka C2 není prázdná.
• Obsah buňky C2 přesně odpovídá obsahu buňky v datové sadě.

Proto se zvýrazní všechny buňky, které obsahují text Japonsko.

Stáhněte si ukázkový soubor

Stejnou logiku můžete použít k vytváření variant, jako například:

• Zvýrazněte místo buňky celý řádek.
• Zvýrazněte, i když dojde k částečné shodě.
• Při psaní zvýrazněte buňky/řádky (dynamické) [Tento trik si zamilujete :)].

Jak odebrat podmíněné formátování v aplikaci Excel

Po použití zůstane podmíněné formátování na místě, pokud jej neodstraníte ručně. Jako doporučený postup ponechejte podmíněné formátování aplikované pouze na buňky, kde je potřebujete.

Protože je volatilní, může vést k pomalému sešitu aplikace Excel.

Odebrání podmíněného formátování:

• Vyberte buňky, ze kterých chcete odebrat podmíněné formátování.
• Přejděte na Domů -> Podmíněné formátování -> Vymazat pravidla -> Vymazat pravidla z vybraných buněk.
  • Pokud chcete odstranit podmíněné formátování z celého listu, vyberte Vymazat pravidla z celého listu.

Důležité informace o podmíněném formátování v Excelu

• Podmíněné formátování v volatile. Může to vést k pomalému sešitu. Použijte jej pouze v případě potřeby.
• Když kopírujete buňky, které obsahují podmíněné formátování, zkopíruje se také podmíněné formátování.
• Pokud na stejnou sadu buněk použijete více pravidel, všechna pravidla zůstanou aktivní. V případě překrývání je upřednostňováno pravidlo použité jako poslední. Pořadí však můžete změnit změnou objednávky v dialogovém okně Spravovat pravidla.