Podmienené formátovanie v programe Excel: Pokyny a použitie

Tieto pokyny použite na vytvorenie rôznych formátov

Podmienené formátovanie v programe Microsoft Excel je užitočný nástroj na zvýšenie viditeľnosti tabuliek, zostáv alebo buniek, zvýraznenie oblasti alebo niekoľkých stĺpcov, analýzu údajov, objasnenie rozdielov alebo identifikáciu trendov. Slúžia na to dátové pruhy, sady symbolov alebo farebné stupnice. V závislosti od zadaných podmienok sa zmení zobrazenie buniek alebo sa formátuje rozsah buniek. Môžete si vytvoriť svoj vlastný pre množstvo integrovaných podmienok.

Podmienené formátovanie má zmysel a pomáha vám

Podmienené formátovanie v programe Excel je možné použiť mnohými spôsobmi, či už ide o tabuľku programu Excel, konkrétny rozsah buniek alebo zostavu kontingenčnej tabuľky. K dispozícii máte rôzne akcie a šablóny formátov. Rozhodujúcou výhodou pre používateľov Excelu je, že špeciálne hodnoty alebo výsledky je možné zvýrazniť farebným kódovaním. Ak sa zmení hodnota, formátovanie sa automaticky upraví - v závislosti od podmienok.

Info: Kontingenčná tabuľka („kontingenčná tabuľka“) je špeciálna tabuľka, pomocou ktorej je možné vypočítať, zhrnúť a analyzovať veľké množstvo údajov. Okrem toho môžete rýchlo porovnávať a identifikovať trendy v údajoch z vyšetrení.

Podmienené formátovanie s existujúcimi pravidlami pre čísla, textový obsah alebo dátumy

Ak formátujete pomocou porovnávacieho operátora, bunky sa dajú nájsť jednoduchšie. Môžete zviditeľniť hodnoty, napríklad väčšie alebo menšie ako definovaná hodnota alebo určité hodnoty, ktoré ležia medzi hornou a dolnou hranicou. To znamená, že požadované alebo hľadané údaje sú zobrazené farebne. Na úpravu je k dispozícii rýchle formátovanie alebo pokročilé formátovanie. S rozšíreným formátovaním môžete pracovať so vzorcami, napríklad s automatickým farebným označením stĺpca programu Excel.

Príklady:

• Skladník by chcel zvýrazniť všetky zásoby pod 15 kusov s farebným pozadím.
• Všetky mestá s viac ako 150 000 obyvateľmi majú byť označené v tabuľke.
• Výrobná spoločnosť chce zvýrazniť všetok tovar vyrobený od 1. januára 2022-2023 do 2022 do 31. januára 2022-2023 do 2022 červenou farbou.

Rýchly formát:

Výber jednej alebo viacerých buniek.

Na karte kliknite na „Štart“.

V časti „Štýl“ prejdite na šípku vedľa tlačidla „Podmienené formátovanie“.

Kliknite na „Pravidlá pre zvýraznenie buniek“.

Vyberte požadovaný príkaz, napr. B. väčšie ako, menšie ako, medzi, dátumom atď.

Zadajte hodnoty a vyberte formát.

Rozšírené formátovanie:

1. Vyberte jednu alebo viac buniek v tabuľke alebo zostave kontingenčnej tabuľky.

2. Prejdite na kartu „Domov“, v skupine Štýly kliknite na šípku vedľa položky „Podmienené formátovanie“.

3. Kliknite na „Spravovať pravidlá“: Zobrazí sa dialógové okno „Správca pravidiel podmieneného formátovania“.

4. Vyberte akciu:

   • Kliknite na „Nové pravidlo“: Zobrazí sa dialógové okno „Nové pravidlo formátovania“.
   • Ak chcete zmeniť formát, uistite sa, že je v zozname „Zobraziť pravidlo formátovania pre“ vybratý správny pracovný hárok alebo tabuľka.
   • V poli „Platí pre“ prejdite na „Zbaliť dialógové okno“; potom v pracovnom hárku vyberte nový rozsah buniek.
   • Kliknite na tlačidlo „Rozbaliť dialógové okno“.
   • Vyberte pravidlo a kliknite na „Upraviť pravidlo“: Zobrazí sa dialógové okno „Upraviť pravidlo formátovania“.

5. Ak chcete zmeniť rozsah polí v hodnotovej oblasti „Použiť pravidlo na“ zostavy kontingenčnej tabuľky, postupujte takto:

   • Výber: Kliknite na „Iba tieto bunky“
   • Zodpovedajúce pole: Kliknite na „Všetky bunky s rovnakými poľami“
   • Pole hodnoty: Kliknite na „Všetky bunky“

6. V časti „Vybrať typ pravidla“ kliknite na „Formátovať iba bunky, ktoré obsahujú“

Teraz môžete vykonávať akcie v časti „Upraviť popis pravidla“ v poli zoznamu „Formátovať bunky iba pomocou“:

Formát podľa čísla, dátumu alebo času:

Ak to chcete urobiť, vyberte operátor hodnoty a porovnania bunky a zadajte číslo, dátum alebo čas. príklad: Pre „Medzi“ zadajte 300 a 500 alebo zvoľte „Rovná sa“ a zadajte dátum.

Na vrátenie hodnoty čísla, dátumu alebo času môžete použiť vzorec.

Formát podľa textu:

Vyberte položku „Konkrétny text“ a „Relačný operátor“.

Zadajte text, napr. B. na „obsahuje“ slovo „zlato“ alebo na „začína“ predponou, ktorú chcete vyhľadať.

Môžete zadať maximálne 255 znakov.

Hodnoty je možné vrátiť pomocou vzorcov. Na to musí vzorec začínať (=). Neplatné vzorce nie sú formátované. Odporúča sa najskôr otestovať vzorec na chyby.

Formátovať podľa dátumu:

• Vyberte dátum a dátum porovnania, napr. B. Včera, budúci týždeň alebo tento mesiac

Potom: Ak chcete zadať formát, kliknite na „Formátovať“, zobrazí sa dialógové okno „Formátovať bunky“. Vyberte požadovaný formát čísla, formát výplne, rám alebo písmo, kliknite na „OK“.

Je možné zvoliť viacero formátov. V poli „Náhľad“ môžete vidieť, ktoré formáty boli vybraté.

Podmienené formátovanie s databázami, farebnými schémami a sadami symbolov

Obsah a hodnoty v bunkách je možné vizuálne aktualizovať pomocou databáz, farebných mierok alebo množín symbolov a zatraktívniť a sprehľadniť. Ak sa majú hodnoty v rozsahu buniek navzájom porovnávať, je to s týmto podmieneným formátovaním jednoduchšie.

Bunky formátujte pomocou údajových pruhov

Dátové pruhy uľahčujú vzájomné porovnávanie buniek. Hodnota v bunke je označená dĺžkou pruhu. Čím vyššia je hodnota, tým dlhší je údajový pruh, tým nižšia je hodnota a tým je pruh kratší. To umožňuje rýchle stanovenie vysokých alebo nízkych hodnôt.

Rýchly formát:

Označte požadovanú oblasť (bunka, tabuľka alebo celý list).

Na karte Domov kliknite na tlačidlo Podmienené formátovanie.

Prejdite na „údajový panel“ a vyberte výplň.

Bunky formátujte s farebnými fazetami

Farebné škály pomáhajú jednoduchšie klasifikovať rôzne údaje a rýchlejšie porovnávať ich obsah. V závislosti od odtieňa farby sa zobrazujú vysoké alebo nízke hodnoty. Bunky môžete formátovať v dvoj alebo trojfarebnej škále.

2-farebná stupnica

Rýchly formát:

Vyberte bunku, tabuľku alebo celý hárok.

Na karte Domov kliknite na Podmienené formátovanie.

Prejdite na „farebnú škálu“ a kliknite na požadovaný formát dvojfarebnej stupnice. Horná farba znamená vyššie hodnoty, spodná farba znamená nižšie hodnoty.

3-farebná stupnica

Tretia farba umožňuje vizualizáciu priemerných hodnôt- okrem farebného znázornenia vysokých a nízkych hodnôt ako v dvojfarebnej škále.

Rýchly formát:

Vyberte bunku, tabuľku alebo celý hárok.

Na karte Domov kliknite na Podmienené formátovanie.

Prejdite na „farebnú škálu“ a kliknite na požadovaný formát 3-farebnej stupnice. Horná farba znamená vyššie hodnoty, spodná farba znamená nižšie hodnoty.

Bunky formátujte pomocou množín symbolov

So sadou symbolov je možné údaje zobrazovať a klasifikovať do oddelených kategórií. Symbol znamená určitý rozsah hodnôt. Podmienené formátovanie v programe Excel ponúka rôzne farebné symboly: šípky, bodky, krížiky, výkričníky a mnoho ďalších.

Rýchly formát:

Vyberte bunku, tabuľku alebo celý hárok.

Na karte Domov kliknite na Podmienené formátovanie.

Prejdite na „Sady symbolov“ a vyberte požadovanú sadu symbolov

Vytvorte si pravidlá formátovania sami

Ak nemôžete nájsť vhodnú voľbu v podmienenom formátovaní, ktoré ponúka Excel, môžete ľahko vytvoriť individuálne pravidlá pre formátovanie.

Označte rozsah buniek, ktoré majú byť podmienene formátované.

Na karte „Domov“ prejdite na „Podmienené formátovanie“.

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

Ak nemôžete nájsť možnosť, ktorú potrebujete pre svoje vlastné pravidlo formátovania, môže vám pomôcť logický vzorec. Môžete napríklad použiť vzorec na automatické zafarbenie buniek Excelu, hneď ako bude obsah k dispozícii. Naopak, bunky, ktoré sú prázdne, je možné podmienene formátovať.

Príklad: Použitie vzorca vám umožní porovnať údaje mimo vybratého rozsahu. To dokonca zahŕňa aj údaje, ktoré sú na inom pracovnom hárku v tom istom zošite. Alebo môžete porovnať hodnoty s výsledkom vráteným funkciou. Vzorec musí byť logický a musí vracať „true“ alebo „false“.

Zvýraznením buniek priamo v hárku môžete do vzorca zadať odkazy na bunky. Toto sú absolútne odkazy na bunky. Excel však môže tiež upraviť odkazy na bunky pre každú bunku vo vybratej oblasti. Ak chcete, vyberte relatívne odkazy na bunky.

Podrobné pokyny na vytvorenie pravidla

1. Na karte „Domov“ v skupine „Štýly“ prejdite na šípku vedľa položky „Podmienené formátovanie“ a kliknite na položku „Spravovať pravidlá“. Zobrazí sa dialógové okno Správca pravidiel podmieneného formátovania.

2. Sú možné nasledujúce akcie:

   • Ak chcete pridať podmienený formát, prejdite na „Nové pravidlo“.
   • Zmeniť podmienený formát:
   • • Skontrolujte, či ste na správnom pracovnom hárku.
     • Najprv v časti Vzťahuje sa na kliknite na položku Zbaliť dialógové okno. Potom vyberte nový rozsah buniek a prejdite na „Rozbaliť dialógové okno“.
     • Vyberte požadované pravidlo, kliknite na „Upraviť pravidlo“. Zobrazí sa „Upraviť pravidlo formátovania“.

3. V časti „Vybrať typ pravidla“ kliknite na „Použiť vzorec na určenie buniek, ktoré sa majú formátovať“.

   • V časti „Upraviť popis pravidla“ zadajte vzorec do poľa „Formátovať hodnoty, pre ktoré toto pravidlo platí“. Vzorec začína na (=) a musí vrátiť hodnotu „true“ (1) alebo „false“ (0).
   • Kliknutím na položku Formát zobrazíte dialógové okno Formát buniek.
   • Teraz vyberte číslo, písmo, formát výplne atď., Ktoré sa má použiť, ak je hodnota bunky pravdivá, a potvrďte tlačidlom „OK“. Vybraté formáty sa zobrazia v časti „Náhľad“.

Napríklad vzorec COUNTIF vracia logické hodnoty. Ak sa použije tento vzorec, je možné určiť duplicitné hodnoty alebo farebne zvýrazniť obsah, ktorý sa vyskytuje v určitej frekvencii. Týmto spôsobom je tiež možné nájsť a farebne zvýrazniť jedinečné položky. Ďalšie vzorce, ktoré je možné použiť na podmienené formátovanie, sú

• REST: takže sa dajú automaticky nastaviť striedavé farby čiar
• IDENTICKÉ: Pomocou tohto vzorca môžu byť hľadané údaje označené v tabuľke programu Excel, ktorá zodpovedá zadanej hodnote
• ISERROR: automaticky označí prázdne bunky v tabuľke programu Excel alebo nechá zmiznúť chybové hodnoty zo zošita
• NOT a ISNUMBER: Formátuje všetky bunky programu Excel, ktoré neobsahujú čísla
• MAX: v tabuľke je zvýraznená najväčšia hodnota bunky
• MIN: umožňuje podmienené formátovanie najmenšej hodnoty v zozname programu Excel, aby bolo minimum viditeľné na prvý pohľad
• VĽAVO: V závislosti od reťazca znakov na začiatku sa bunky programu Excel automaticky naformátujú a získajú jednotnú farbu textu
• VEĽKÉ a ĽAVÉ: umožní zafarbenie bunky s novým obsahom
• SPRÁVNE: Bunky je možné formátovať na základe posledného znaku reťazca
• DNES: Vďaka tomu môžu byť schôdzky farebne formátované pomocou semaforovej metódy alebo môžu byť zvýraznené hodnoty dátumu, ktoré budú čoskoro aktuálne.
• MESIAC alebo ROK: umožňujú rozdeliť zoznam programu Excel na mesiace alebo roky; V ROKU je možné označiť aj dátumové hodnoty aktuálneho roku a zvýrazniť ročné hodnoty
• ALEBO a TÝŽDEŇ: farebne zvýrazní hodnoty podľa dňa v týždni

Príklad vytvorenia pravidla pre kontroly A a ALEBO:

V tomto prípade sa na podmienené formátovanie používajú dve hodnoty. Ak prvé pravidlo neplatí, automaticky platí druhé pravidlo.

Prvé pravidlo: Kupujúci domu si chce overiť, či je plánovaná kúpa nehnuteľnosti v súlade s jeho rozpočtom. Môže priniesť až 80 000 eur z vlastných prostriedkov ako zálohu a mesačnú splátku úveru v maximálnej výške 1 200 eur. Ak záloha aj splátka úveru spĺňajú tieto požiadavky, zodpovedajúce bunky sú označené zelenou farbou.

Druhé pravidlo: Ak záloha alebo splátka nespĺňa požiadavky, zodpovedajúce bunky sú označené červenou farbou.

Vzorec na vytvorenie prvého pravidla pomocou zelenej farby je nasledujúci:

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

Vzorec na vytvorenie druhého pravidla pomocou červenej farby je nasledujúci:

= ALEBO (AK ($ B $ 4 = 1200,1))

Preneste a odstráňte podmienené formátovanie

Podmienené formátovanie je možné skopírovať a použiť na iné alebo nové údaje v hárku alebo znova odstrániť.

Prenos podmieneného formátovania

Ak chcete skopírovať podmienené formátovanie a prilepiť ho do iných buniek alebo rozsahov buniek, použite tlačidlo „Preniesť formát“.

Vyberte bunky s podmieneným formátovaním, ktoré chcete skopírovať a prilepiť do iných buniek, a kliknite na ne.

Teraz prejdite na „Štart“ a potom na „Formát prenosu“. Kurzor sa zobrazí ako štetec.

Podmienené formátovanie môžete do bunky alebo rozsahu buniek pridať posunutím štetca nad bunkou.

S ESC ukončíte používanie kefy.

Štetec môžete stále používať na vkladanie podmieneného formátovania dvojitým kliknutím na „Formát prenosu“.

Podmienené formátovanie je tiež možné skopírovať pomocou klávesových skratiek a vložiť do inej bunky programu Excel.

Jasné podmienené formátovanie

Rozlišuje sa proces vymazania na pracovnom hárku a proces vymazania v rade buniek.

Vymažte na hárku alebo v celom hárku

Na karte „Domov“ prejdite na „Podmienené formátovanie“.

Potom kliknite na „odstrániť pravidlá“

Potom kliknite na položku „Odstrániť pravidlá v celom hárku“.

Prípadne môžete podmienené formátovanie odstrániť pomocou dialógového okna „Vybrať obsah“.

Odstráňte v rozsahu buniek

Zvýraznite bunky, ktoré obsahujú požadované podmienené formátovanie.

Kliknite na tlačidlo „Lupa na analýzu čítania“ (tlačidlo sa nachádza v pravom dolnom rohu vedľa vybratých údajov).

Prejdite na „Odstrániť formát“.

V hárku vyhľadajte a odstráňte rovnaké podmienené formáty

Kliknite na bunku s príslušným podmieneným formátom.

Na karte „Domov“ kliknite na šípku vedľa položky „Nájsť a vybrať“ a potom kliknite na „Vybrať obsah“.

Kliknite na „Podmienené formáty“.

V časti „Overenie údajov“ prejdite na „Rovnaké“ (tým sa vyberú všetky bunky s rovnakým podmieneným formátovaním).

Na karte „Štart“ prejdite na „Podmienené formátovanie“, potom „Odstrániť pravidlá“ a „Odstrániť pravidlá vo vybraných bunkách“.

Podmienené formátovanie v zostave kontingenčnej tabuľky

Vďaka zostave kontingenčnej tabuľky môžu používatelia rýchlo zhrnúť veľké množstvo údajov. Rozsah buniek, tabuľkový procesor Excel alebo zostava kontingenčnej tabuľky - podmienené formátovanie funguje v zásade všade rovnako. Iba v prehľade kontingenčnej tabuľky je niekoľko špeciálnych funkcií, o ktorých by ste mali vedieť:

• Niektoré podmienené formáty nefungujú na poliach v rozsahu hodnôt zostavy kontingenčnej tabuľky.
• Ak akýmkoľvek spôsobom zmeníte rozloženie zostavy kontingenčnej tabuľky, podmienené formátovanie bude pretrvávať, kým sa neodstránia polia v podkladových údajoch.
• Podmienené formátovanie polí v rozsahu hodnôt môže byť založené na hierarchii údajov. Rozsah tohto podmieneného formátovania je určený nasledujúcou nižšou alebo ďalšou vyššou úrovňou hierarchie v riadkoch pre jeden alebo viac stĺpcov alebo v stĺpcoch pre jeden alebo viac riadkov.
• Rozsah podmieneného formátovania pre polia v rozsahu hodnôt je možné definovať pomocou troch metód:

• • výber
  • Zodpovedajúce pole
  • Pole hodnoty

Predvolená metóda je výber.

Definícia oblasti výberom

Prejdite na „Použiť pravidlo formátovania na“.

Kliknite buď na dialógové okno Nové pravidlo formátovania, alebo na dialógové okno Upraviť pravidlo formátovania.

Táto metóda je vhodná pre súvislú sériu polí v rozsahu hodnôt, napr. B. údaje prieskumu pre región, ako aj pre nesúvislú sériu polí v hodnotovom rozsahu, ako sú údaje prieskumu pre rôzne regióny na rôznych úrovniach hierarchie údajov.

Definícia oblasti prostredníctvom zodpovedajúceho poľa

Táto metóda funguje, ak máte

• sa chcete vyhnúť tomu, aby ste museli vyberať veľa nesúvisiacich položiek
• chcete podmienene naformátovať niekoľko polí v rozsahu hodnôt pre úroveň hierarchie údajov
• Chcete vylúčiť čiastočné výsledky

Definícia oblasti prostredníctvom hodnotového poľa

Táto metóda funguje, ak máte

• sa chcete vyhnúť tomu, aby ste museli vyberať veľa nesúvisiacich položiek
• chcete podmienečne formátovať niekoľko polí v rozsahu hodnôt pre všetky úrovne hierarchie
• Chcete zahrnúť súčty a čiastkové výsledky.

Záver: Podmienené formátovanie v programe Excel uľahčuje sledovanie údajov

Ak chcete vo svojej tabuľke programu Excel farebne zvýrazniť určité hodnoty údajov, podmienené formátovanie v programe Excel je ideálne. Máte dve možnosti použitia podmieneného formátovania. Buď použijete pravidlá poskytnuté Excelom na farebné zvýraznenie obsahu textu, číselných hodnôt alebo dátumov. Alebo môžete sami definovať pravidlo s rôznymi podmienkami, za ktorých majú byť označené príslušné hodnoty. Údaje môžete navzájom jednoducho a efektívne porovnávať pomocou údajových pruhov, farebných pruhov a symbolových sád. Ak už nie sú potrebné možnosti návrhu, môžete ich odstrániť niekoľkými kliknutiami.

Ďalšie články o podmienenom formátovaní

• Ako automaticky generovať šípky v zoznamoch programu Excel
• Zobrazte záporné časové hodnoty v tabuľkách programu Excel
• Farebné negatívne percentá červená
• Poznáte záporné percentá na prvý pohľad
• Formátujte čísla pomocou vlastného formátu vo farbe
• Naformátujte bunky programu Excel, ktoré nemajú zadanú hodnotu
• Automaticky ukladajte bunky so zodpovedajúcimi formátmi mien
• Bunky označte farebne pomocou funkcie makra
• Použite podmienené formátovanie a zvýraznite údaje, ktoré sú väčšie alebo rovné tretej najväčšej bunke
• Obráťte hodnoty pravdy