Podmíněné formátování v aplikaci Excel: Pokyny a použití

Pomocí těchto pokynů můžete vytvářet různé formáty

Podmíněné formátování v aplikaci Microsoft Excel je užitečný nástroj, který zvýrazní tabulky, zprávy nebo buňky, zvýrazní oblast nebo několik sloupců, analyzuje data, vyjasní rozdíly nebo identifikuje trendy. K tomu slouží datové pruhy, sady symbolů nebo barevné stupnice. V závislosti na zadaných podmínkách se změní zobrazení buněk nebo se formátuje rozsah buněk. Můžete si vytvořit vlastní pro mnoho integrovaných podmínek.

Podmíněné formátování má smysl a pomáhá vám

Podmíněné formátování v aplikaci Excel lze použít mnoha způsoby, ať už jde o tabulku aplikace Excel, konkrétní rozsah buněk nebo sestavu kontingenční tabulky. K dispozici máte různé akce a šablony formátů. Rozhodující výhodou pro uživatele aplikace Excel je, že speciální hodnoty nebo výsledky lze zvýraznit barevným kódováním. Pokud se hodnota změní, formátování se automaticky upraví - v závislosti na podmínkách.

informace: Kontingenční tabulka („kontingenční tabulka“) je speciální tabulka, pomocí které lze vypočítat, shrnout a analyzovat velké množství dat. Kromě toho můžete rychle porovnávat a identifikovat trendy v datech vyšetření.

Podmíněné formátování s existujícími pravidly pro čísla, textový obsah nebo data

Pokud formátujete pomocí operátoru porovnání, buňky lze snáze najít. Hodnoty můžete zviditelnit, například větší nebo menší než definovaná hodnota nebo určité hodnoty, které leží mezi horní a dolní mezí. To znamená, že požadovaná nebo hledaná data jsou zobrazena barevně. K úpravám je k dispozici rychlé formátování nebo pokročilé formátování. S rozšířeným formátováním můžete pracovat se vzorci, například automaticky označovat sloupec aplikace Excel barevně.

Příklady:

• Skladník by chtěl zvýraznit všechny zásoby pod 15 kusů s barevným pozadím.
• Všechna města s více než 150 000 obyvateli mají být označena v tabulce.
• Výrobní společnost chce zvýraznit všechno zboží vyrobené mezi 1. lednem 2022-2023 a 31. lednem 2022-2023 červeně.

Rychlý formát:

Výběr jedné nebo více buněk.

Na kartě klikněte na „Start“.

Ve „Stylu“ přejděte na šipku vedle tlačítka „Podmíněné formátování“.

Klikněte na „Pravidla pro zvýraznění buněk“.

Vyberte požadovaný příkaz, např. B. větší než, menší než, mezi, datum atd.

Zadejte hodnoty a vyberte formát.

Rozšířené formátování:

1. Vyberte jednu nebo více buněk v tabulce nebo v sestavě kontingenční tabulky.

2. Přejděte na kartu „Domů“, ve skupině Styly klikněte na šipku vedle „Podmíněné formátování“.

3. Klikněte na „Spravovat pravidla“: Zobrazí se dialogové okno „Správce pravidel podmíněného formátování“.

4. Vyberte akci:

   • Klikněte na „Nové pravidlo“: Zobrazí se dialogové okno „Nové pravidlo formátování“.
   • Chcete -li změnit formát, ujistěte se, že je v seznamu „Zobrazit pravidlo formátování pro“ vybrán správný list nebo tabulka.
   • V poli „Platí pro“ přejděte na „Dialogové okno Sbalit“; poté na listu vyberte nový rozsah buněk.
   • Klikněte na tlačítko „Rozbalit dialogové okno“.
   • Vyberte pravidlo a klikněte na „Upravit pravidlo“: Zobrazí se dialogové okno „Upravit pravidlo formátování“.

5. Chcete -li změnit rozsah pro pole v hodnotové oblasti „Použít pravidlo na“ sestavy kontingenční tabulky, postupujte takto:

   • Výběr: Klikněte na „Pouze tyto buňky“
   • Odpovídající pole: Klikněte na „Všechny buňky se stejnými poli“
   • Pole hodnoty: Klikněte na „Všechny buňky“

6. V části „Vybrat typ pravidla“ klikněte na „Formátovat pouze buňky, které obsahují“

Nyní můžete provádět akce v části „Upravit popis pravidla“ v poli seznamu „Formátovat buňky pouze pomocí“:

Formát podle čísla, data nebo času:

Chcete -li to provést, vyberte operátor hodnoty buňky a porovnání a zadejte číslo, datum nebo čas. příklad: Pro „Mezi“ zadejte 300 a 500 nebo vyberte „Rovno“ a zadejte datum.

K získání čísla, data nebo času zpět můžete použít vzorec.

Formát podle textu:

Vyberte „Specifický text“ a „Relační operátor“.

Zadejte text, např. B. na „obsahuje“ slovo „zlato“ nebo na „začíná“ předponou, kterou chcete vyhledat.

Lze zadat maximálně 255 znaků.

Hodnoty lze vrátit pomocí vzorců. K tomu musí vzorec začínat (=). Neplatné vzorce nejsou formátovány. Doporučuje se nejprve vyzkoušet vzorec na chyby.

Formátovat podle data:

• Vyberte datum a datum srovnání, např. B. Včera, příští týden nebo tento měsíc

Poté: Chcete -li určit formát, klikněte na „Formát“, zobrazí se dialogové okno „Formát buněk“. Vyberte požadovaný formát čísla, formát výplně, rámeček nebo písmo, klikněte na „OK“.

Je možné zvolit více formátů. V poli „Náhled“ můžete vidět, které formáty byly vybrány.

Podmíněné formátování pomocí databází, barevných schémat a sad symbolů

Obsahy a hodnoty v buňkách lze vizuálně upgradovat pomocí databází, barevných stupnic nebo sad symbolů a učinit je atraktivnějšími a přehlednějšími. Pokud mají být hodnoty v rozsahu buněk navzájem porovnávány, je to s tímto podmíněným formátováním jednodušší.

Formátujte buňky pomocí datových pruhů

Datové pruhy usnadňují vzájemné porovnávání buněk. Hodnota v buňce je indikována délkou pruhu. Čím vyšší hodnota, tím delší datový pruh, tím nižší hodnota, tím kratší pruh. To umožňuje rychlé stanovení vysokých nebo nízkých hodnot.

Rychlý formát:

Označte požadovanou oblast (buňka, tabulka nebo celý list).

Na kartě Domů klikněte na tlačítko Podmíněné formátování.

Přejděte na „datový řádek“ a vyberte výplň.

Buňky formátujte pomocí barevných rámečků

Barevné stupnice pomáhají snáze třídit různé údaje a rychleji porovnávat jejich obsah. V závislosti na barevném odstínu se zobrazí vysoké nebo nízké hodnoty. Buňky můžete formátovat ve dvou nebo tříbarevném měřítku.

2barevná stupnice

Rychlý formát:

Vyberte buňku, tabulku nebo celý list.

Na kartě Domů klikněte na Podmíněné formátování.

Přejděte na „barevnou škálu“ a klikněte na požadovaný formát dvoubarevné stupnice. Horní barva znamená vyšší hodnoty, spodní barva nižší hodnoty.

3barevná stupnice

Třetí barva umožňuje vizualizaci průměrných hodnot- navíc k barevnému znázornění vysokých a nízkých hodnot jako ve dvoubarevné škále.

Rychlý formát:

Vyberte buňku, tabulku nebo celý list.

Na kartě Domů klikněte na Podmíněné formátování.

Přejděte na „barevnou škálu“ a klikněte na požadovaný formát 3barevné stupnice. Horní barva znamená vyšší hodnoty, spodní barva nižší hodnoty.

Buňky formátujte pomocí sad symbolů

Se sadou symbolů lze data prohlížet a třídit do samostatných kategorií. Symbol znamená určitý rozsah hodnot. Podmíněné formátování v Excelu nabízí různé barevné symboly: šipky, tečky, křížky, vykřičníky a mnoho dalšího.

Rychlý formát:

Vyberte buňku, tabulku nebo celý list.

Na kartě Domů klikněte na Podmíněné formátování.

Přejděte na „Sady symbolů“ a vyberte požadovanou sadu symbolů

Vytvořte si pravidla formátování sami

Pokud v podmíněném formátování nabízeném aplikací Excel nemůžete najít vhodnou možnost, můžete snadno vytvořit jednotlivá pravidla pro formátování.

Označte rozsah buněk, které mají být podmíněně formátovány.

Na kartě „Domů“ přejděte na „Podmíněné formátování“.

Vyberte „Nové pravidlo“ a potvrďte „OK“.

Pokud nemůžete najít možnost, kterou potřebujete pro své vlastní pravidlo formátování, může vám pomoci logický vzorec. Můžete například použít vzorec k automatickému vybarvování buněk aplikace Excel, jakmile je obsah k dispozici. Naopak buňky, které jsou prázdné, lze podmíněně formátovat.

Příklad: Použití vzorce vám umožní porovnat data mimo vybraný rozsah. To dokonce zahrnuje data, která jsou na jiném listu ve stejném sešitu. Nebo můžete porovnat hodnoty s výsledkem vráceným funkcí. Vzorec musí být logický a musí vracet „true“ nebo „false“.

Zvýrazněním buněk přímo v listu můžete do vzorce zadat odkazy na buňky. Toto jsou absolutní odkazy na buňky. Excel však může také upravit odkazy na buňky pro každou buňku ve vybrané oblasti. Pokud si přejete, vyberte relativní odkazy na buňky.

Podrobné pokyny pro vytvoření pravidla

1. Na kartě „Domů“ ve skupině „Styly“ přejděte na šipku vedle položky „Podmíněné formátování“ a klikněte na „Spravovat pravidla“. Zobrazí se dialogové okno Správce pravidel podmíněného formátování.

2. Jsou možné následující akce:

   • Pokud chcete přidat podmíněný formát, přejděte na „Nové pravidlo“.
   • Změnit podmíněný formát:
   • • Zkontrolujte, zda jste na správném listu.
     • Nejprve v části Platí pro klikněte na Sbalit dialogové okno. Poté vyberte nový rozsah buněk a přejděte na „Rozbalit dialogové okno“.
     • Vyberte požadované pravidlo, klikněte na „Upravit pravidlo“. Zobrazí se „Upravit pravidlo formátování“.

3. V části „Vybrat typ pravidla“ klikněte na „Použít vzorec k určení buněk, které mají být formátovány“.

   • V části „Upravit popis pravidla“ zadejte vzorec do pole „Formátovat hodnoty, pro které toto pravidlo platí“. Vzorec začíná (=) a musí vracet „true“ (1) nebo „false“ (0).
   • Kliknutím na Formát zobrazíte dialogové okno Formát buněk.
   • Nyní vyberte číslo, písmo, formát výplně atd., Které by měly být použity, pokud je hodnota buňky pravdivá, a potvrďte pomocí „OK“. Vybrané formáty se zobrazí v "Náhledu".

Například vzorec COUNTIF vrací logické hodnoty. Pokud se použije tento vzorec, lze určit duplicitní hodnoty nebo barevně zvýraznit obsah, který se vyskytuje v určité frekvenci. Tímto způsobem lze také najít a barevně zvýraznit jedinečné položky. Další vzorce, které lze použít pro podmíněné formátování, jsou

• REST: takže střídavé barvy čar lze nastavit automaticky
• IDENTICKÉ: Pomocí tohoto vzorce lze hledaná data označit v tabulce aplikace Excel, která odpovídá zadané hodnotě
• ISERROR: automaticky označí prázdné buňky v tabulce aplikace Excel nebo způsobí, že chybové hodnoty zmizí ze sešitu
• NOT a ISNUMBER: Formátuje všechny buňky aplikace Excel, které neobsahují čísla
• MAX: v tabulce je zvýrazněna největší hodnota buňky
• MIN: umožňuje podmíněné formátování nejmenší hodnoty v seznamu aplikace Excel, aby bylo minimum viditelné na první pohled
• VLEVO: v závislosti na řetězci znaků na začátku jsou buňky aplikace Excel automaticky formátovány a mají jednotnou barvu textu
• VELKÉ a VLEVO: umožňuje vybarvení buňky s novým obsahem
• VPRAVO: Buňky lze formátovat na základě posledního znaku řetězce
• DNES: Díky tomu lze schůzky barevně formátovat pomocí metody semaforu nebo zvýraznit hodnoty data, které budou brzy aktuální
• MĚSÍC nebo ROK: umožňuje rozdělit seznam aplikace Excel na měsíce nebo roky; Pomocí YEAR lze také označit hodnoty data aktuálního roku a zvýraznit roční hodnoty
• NEBO a VÍKEND: barevně zvýrazní hodnoty podle dne v týdnu

Příklad vytvoření pravidla pro kontroly AND a OR:

V tomto případě se pro podmíněné formátování používají dvě hodnoty. Pokud první pravidlo neplatí, automaticky platí druhé pravidlo.

První pravidlo: Kupující domu chce zkontrolovat, zda je plánovaný nákup nemovitosti v souladu s jeho rozpočtem. Může přinést až 80 000 EUR ze svých vlastních prostředků jako zálohu a obsluhovat měsíční splátku půjčky maximálně 1 200 EUR. Pokud záloha i splátka půjčky splňují tyto požadavky, jsou příslušné buňky označeny zeleně.

Druhé pravidlo: Pokud záloha nebo splátka nesplňují požadavky, jsou příslušné buňky označeny červeně.

Vzorec pro vytvoření prvního pravidla pomocí zelené barvy je následující:

= AND (IF ($ B $ 4 <= 80000,1); IF (ABS ($ B $ 5) <= 1200,1))

Vzorec pro vytvoření druhého pravidla pomocí červené barvy je následující:

= NEBO (IF ($ B $ 4 = 1200,1))

Přeneste a odstraňte podmíněné formátování

Podmíněné formátování lze zkopírovat a použít na jiná nebo nová data v listu nebo znovu odstranit.

Přenos podmíněného formátování

Chcete -li zkopírovat podmíněné formátování a vložit jej do jiných buněk nebo oblastí buněk, použijte tlačítko „Přenést formát“.

Vyberte buňky s podmíněným formátováním, které chcete zkopírovat a vložit do jiných buněk, a klikněte na ně.

Nyní přejděte na „Start“ a poté na „Přenos formátu“. Kurzor se zobrazí jako štětec.

Podmíněné formátování můžete do buňky nebo oblasti buněk přidat přesunutím štětce nad buňku.

S ESC ukončíte používání štětce.

Stále můžete použít štětec k vložení podmíněného formátování poklepáním na „Přenést formát“.

Alternativně lze podmíněné formátování zkopírovat pomocí klávesových zkratek a vložit do jiné buňky aplikace Excel.

Jasné podmíněné formátování

Rozlišuje se mezi procesem mazání na listu a procesem mazání v řadě buněk.

Vymazat na listu nebo v celém listu

Na kartě „Domů“ přejděte na „Podmíněné formátování“.

Poté klikněte na „odstranit pravidla“

Poté klikněte na „Odstranit pravidla v celém listu“.

Případně můžete podmíněné formátování odstranit pomocí dialogového okna „Vybrat obsah“.

Odstranit v oblasti buněk

Zvýrazněte buňky, které obsahují požadované podmíněné formátování.

Klikněte na tlačítko „Lupa pro analýzu čtení“ (tlačítko se nachází vpravo dole vedle vybraných dat).

Přejděte na „Odstranit formát“.

Najděte a odstraňte stejné podmíněné formáty v celém listu

Klikněte na buňku s příslušným podmíněným formátem.

Na kartě „Domů“ klikněte na šipku vedle „Najít a vybrat“ a poté klikněte na „Vybrat obsah“.

Klikněte na „Podmíněné formáty“.

V části „Ověření dat“ přejděte na „Stejné“ (tím vyberete všechny buňky se stejným podmíněným formátováním).

Na kartě „Start“ přejděte na „Podmíněné formátování“, poté „Odstranit pravidla“ a „Odstranit pravidla ve vybraných buňkách“.

Podmíněné formátování v sestavě kontingenční tabulky

Díky sestavě kontingenční tabulky mohou uživatelé rychle shrnout velké množství dat. Rozsah buněk, tabulka aplikace Excel nebo sestava kontingenční tabulky - podmíněné formátování v zásadě funguje všude stejně. Pouze v sestavě kontingenční tabulky existuje několik speciálních funkcí, o kterých byste měli vědět:

• Některé podmíněné formáty nefungují na polích v rozsahu hodnot sestavy kontingenční tabulky.
• Pokud jakýmkoli způsobem změníte rozložení sestavy kontingenční tabulky, podmíněné formátování bude přetrvávat, dokud nebudou odstraněna pole v podkladových datech.
• Podmíněné formátování pro pole v rozsahu hodnot může být založeno na hierarchii dat. Rozsah tohoto podmíněného formátování je určen další nižší nebo další vyšší úrovní hierarchie v řádcích pro jeden nebo více sloupců nebo ve sloupcích pro jeden nebo více řádků.
• Rozsah podmíněného formátování pro pole v rozsahu hodnot lze definovat pomocí tří metod:

• • výběr
  • Odpovídající pole
  • Pole hodnoty

Výchozí metodou je výběr.

Definice oblasti výběrem

Přejděte na „Použít pravidlo formátování na“.

Klikněte buď na dialogové okno Nové pravidlo formátování, nebo na dialogové okno Upravit pravidlo formátování.

Tato metoda je vhodná pro souvislou řadu polí v rozsahu hodnot, např. B. data průzkumu pro region i pro nesousedící řadu polí v hodnotovém rozsahu, jako jsou data průzkumu pro různé regiony na různých úrovních datové hierarchie.

Definice oblasti prostřednictvím odpovídajícího pole

Tato metoda funguje, pokud máte

• chcete vyhnout se nutnosti výběru mnoha nesouvisejících položek
• chcete podmíněně formátovat několik polí v rozsahu hodnot pro úroveň datové hierarchie
• Chcete vyloučit dílčí výsledky

Definice oblasti prostřednictvím hodnotového pole

Tato metoda funguje, pokud máte

• chcete vyhnout se nutnosti výběru mnoha nesouvisejících položek
• chcete podmíněně formátovat několik polí v rozsahu hodnot pro všechny úrovně hierarchie
• Chcete zahrnout součty a dílčí výsledky.

Závěr: Podmíněné formátování v Excelu usnadňuje sledování dat

Pokud chcete v tabulce Excelu barevně zvýraznit určité hodnoty dat, je ideální podmíněné formátování v Excelu. Pro použití podmíněného formátování máte dvě možnosti. Buď použijete pravidla poskytovaná aplikací Excel k barevnému zvýraznění obsahu textu, číselných hodnot nebo dat. Nebo můžete sami definovat pravidlo s různými podmínkami, za kterých mají být označeny příslušné hodnoty. Data můžete snadno a efektivně porovnávat mezi sebou pomocí datových pruhů, barevných pruhů a sad symbolů. Pokud již nejsou potřebné možnosti návrhu, můžete je odstranit několika kliknutími.

Další články o podmíněném formátování

• Jak automaticky generovat šipky v seznamech aplikace Excel
• Zobrazte záporné časové hodnoty v tabulkách aplikace Excel
• Procenta negativních barev červená
• Rozpoznejte záporná procenta na první pohled
• Formátujte čísla pomocí vlastního barevného formátu
• Formátujte buňky aplikace Excel, které nemají zadanou hodnotu
• Automaticky ukládat buňky s odpovídajícími formáty měn
• Označte buňky barevně pomocí funkce makra
• Použijte podmíněné formátování a zvýrazněte data, která jsou větší nebo rovna třetí největší buňce
• Obraťte hodnoty pravdy