Excel

Podmienené formátovanie pomocou vzorcov (10 príkladov)

Conditional Formatting With Formulas

Rýchly štart | Príklady | Riešenie problémov | Školenia

Podmienené formátovanie je fantastický spôsob, ako rýchlo vizualizovať údaje v tabuľke. S podmieneným formátovaním môžete vykonávať napríklad zvýraznenia dátumov na nasledujúcich 30 dní, označovanie problémov so zadávaním údajov, zvýraznenie riadkov obsahujúcich najlepších zákazníkov, zobrazenie duplikátov a ďalšie.



Excel je dodávaný s veľkým počtom „predvolieb“, ktoré uľahčujú vytváranie nových pravidiel bez vzorcov. Pravidlá však môžete vytvárať aj pomocou vlastných vzorcov. Použitím vlastného vzorca prevezmete podmienku, ktorá spúšťa pravidlo, a môžete použiť presne takú logiku, akú potrebujete. Vzorce vám poskytnú maximálnu silu a flexibilitu.

Napríklad pomocou predvoľby „Rovná sa“ je ľahké zvýrazniť bunky rovnaké ako „jablko“.





Ale čo keď chcete zvýrazniť bunky rovnaké ako „jablko“ alebo „kiwi“ alebo „vápno“? Iste, môžete vytvoriť pravidlo pre každú hodnotu, ale je to veľa problémov. Namiesto toho môžete jednoducho použiť jedno pravidlo na základe vzorca s príponou ALEBO funkcia :

Pravidlo na zvýraznenie x, y alebo z



Tu je výsledok pravidla aplikovaného na rozsah B4: F8 v tejto tabuľke:

Podmienené formátovanie pomocou funkcie OR

Tu je presný použitý vzorec:

 
= OR (B4='apple',B4='kiwi',B4='lime')

Rýchly štart

Pravidlo podmieneného formátovania na základe vzorca môžete vytvoriť v štyroch jednoduchých krokoch:

1. Vyberte bunky, ktoré chcete formátovať.

Vyberte bunky, ktoré chcete formátovať

2. Vytvorte pravidlo podmieneného formátovania a vyberte možnosť Vzorec

Vyberte možnosť vzorca

3. Zadajte vzorec, ktorý vráti hodnotu TRUE alebo FALSE.

Zadajte vzorec vzhľadom na aktívnu bunku

4. Nastavte možnosti formátovania a pravidlo uložte.

Nastavte možnosti formátovania

The Funkcia ISODD vráti iba TRUE pre nepárne čísla, čím sa spustí pravidlo:

vnorené, ak potom príkazy v programe Excel

Funkcia ISODD vráti hodnotu TRUE pre nepárne čísla, čím sa spustí pravidlo

Video: Ako použiť podmienené formátovanie pomocou vzorca

Ponúkame tiež video školenie na túto tému .

Logika vzorca

Vzorce, ktoré používajú podmienené formátovanie, musia vrátiť hodnotu TRUE alebo FALSE alebo číselné ekvivalenty. Tu je niekoľko príkladov:

ako kopírovať bunky v programe Excel pomocou vzorca
 
= ISODD (A1) = ISNUMBER (A1) =A1>100 = AND (A1>100,B1<50) = OR (F1='MN',F1='WI')

Vyššie uvedené vzorce vracajú hodnotu TRUE alebo FALSE, takže fungujú perfektne ako spúšťač podmieneného formátovania.

Keď sa na rozsah buniek uplatňuje podmienené formátovanie, zadajte odkazy na bunky vzhľadom na prvý riadok a stĺpec vo výbere (t. J. Hornú ľavú bunku). Trikom na pochopenie fungovania vzorcov podmieneného formátovania je vizualizácia rovnakého vzorca, na ktorý sa aplikuje každá bunka vo výbere , s odkazmi na bunky aktualizovanými ako obvykle. Predstavte si, že ste vzorec zadali do ľavej hornej bunky bunky výberu a potom ste vzorec skopírovali do celého výberu. Ak s tým máte problém, pozrite si časť o Falošné vzorce nižšie.

Príklady vzorcov

Nasledujú príklady vlastných vzorcov, ktoré môžete použiť na použitie podmieneného formátovania. Niektoré z týchto príkladov je možné vytvoriť pomocou vstavaných predvolieb programu Excel na zvýraznenie buniek, ale vlastné vzorce môžu presahovať predvoľby, ako vidíte nižšie.

Pozri tiež: Viac ako 30 vzorcov podmieneného formátovania

Zvýraznite objednávky z Texasu

Na zvýraznenie riadkov, ktoré predstavujú objednávky z Texasu (skrátene TX), použite vzorec, ktorý uzamkne odkaz na stĺpec F:

 
=$F5='TX'

Pomocou vzorca zvýraznite riadky, kde state =

Viac podrobností nájdete v tomto článku: Zvýraznite riadky s podmieneným formátovaním .

Video: Ako zvýrazniť riadky podmieneným formátovaním

Zvýraznite dátumy v nasledujúcich 30 dňoch

Aby sme zvýraznili dátumy vyskytujúce sa v nasledujúcich 30 dňoch, potrebujeme vzorec, ktorý (1) zabezpečí, že dátumy budú v budúcnosti, a (2) zabezpečí, aby dátumy boli 30 dní alebo menej od dnešného dňa. Jedným zo spôsobov, ako to dosiahnuť, je použiť A funkcia spolu s TERAZ funkcia Páči sa ti to:

 
= AND (B4> NOW (),B4<=( NOW ()+30))

Pri aktuálnom dátume 18. augusta 2016 podmienené formátovanie zvýrazňuje dátumy nasledovne:

Podmienené formátovanie na zvýraznenie dátumov v nasledujúcich 30 dňoch

The TERAZ funkcia vráti aktuálny dátum a čas. Podrobnosti o tom, ako tento vzorec funguje, nájdete v tomto článku: Zvýraznite dátumy v nasledujúcich N dňoch .

Zvýraznite rozdiely v stĺpcoch

Vzhľadom na dva stĺpce, ktoré obsahujú podobné informácie, môžete na rozpoznanie jemných rozdielov použiť podmienené formátovanie. Na spustenie nižšie uvedeného formátovania sa používa nasledujúci vzorec:

 
=$B4$C4

Podmienené formátovanie na porovnanie stĺpcov

Pozri tiež: verzia tohto vzorca, ktorá používa funkciu PRESNE na porovnanie s rozlišovaním malých a veľkých písmen .

Zvýraznite chýbajúce hodnoty

Na zvýraznenie hodnôt v jednom zozname, ktoré v inom chýbajú, môžete použiť vzorec založený na Funkcia COUNTIF :

 
= COUNTIF (list,B5)=0

Zvýraznite chýbajúce hodnoty pomocou podmieneného formátovania

Tento vzorec jednoducho skontroluje každú hodnotu v Zoznam A. voči hodnotám v pomenovanom rozsahu „zoznam“ (D5: D10). Keď je počet nula, vzorec vráti hodnotu TRUE a spustí pravidlo, ktoré zvýrazní hodnoty v Zoznam A. ktoré chýbajú z Zoznam B. .

Video: Ako nájsť chýbajúce hodnoty pomocou COUNTIF

Zvýraznite nehnuteľnosti s 3+ spálňami pod 350 000 dolárov

Na vyhľadanie nehnuteľností v tomto zozname, ktoré majú aspoň 3 spálne, ale sú nižšie ako 300 000 dolárov, môžete použiť vzorec založený na funkcii AND:

 
= AND ($C5<350000,$D5>=3)

Znaky dolára ($) uzamknú odkaz na stĺpce C a D a A funkcia sa používa na zaistenie, že obe podmienky sú PRAVDIVÉ. V riadkoch, kde funkcia AND vracia hodnotu TRUE, sa použije podmienené formátovanie:

Podmienené formátovanie na zvýraznenie zoznamov vlastností

Zvýraznite najvyššie hodnoty (dynamický príklad)

Napriek tomu, že Excel má predvoľby pre „najvyššie hodnoty“, tento príklad ukazuje, ako urobiť to isté so vzorcom a ako môžu byť vzorce flexibilnejšie. Pomocou vzorca môžeme urobiť hárok interaktívnym - keď sa hodnota v F2 aktualizuje, pravidlo okamžite odpovie a zvýrazní nové hodnoty.

Dynamické podmienené formátovanie pre najvyššie hodnoty

Vzorec použitý pre toto pravidlo je:

čo je päta v exceli
 
=B4>= LARGE (data,input)

Kde „údaje“ sú pomenovaný rozsah B4: G11 a „vstup“ je pomenovaný rozsah F2. Táto stránka má podrobnosti a úplné vysvetlenie .

Ganttove grafy

Verte či neverte, dokonca môžete použiť vzorce na vytvorenie jednoduchých Ganttových diagramov s podmieneným formátovaním takto:

Použitie podmieneného formátovania na vytvorenie Ganttovho diagramu

Tento pracovný hárok používa dve pravidlá, jedno pre tyče a jedno pre víkendové tieňovanie:

 
= AND (D>=$B5,D<=$C5) // bars = WEEKDAY (D,2)>5 // weekends

Tento článok vysvetľuje vzorec pre tyče a tento článok vysvetľuje vzorec na víkendové tieňovanie .

Jednoduché vyhľadávacie pole

Jeden skvelý trik, ktorý môžete s podmieneným formátovaním urobiť, je vytvoriť jednoduché vyhľadávacie pole. V tomto prípade pravidlo zvýrazní bunky v stĺpci B, ktoré obsahujú text napísaný v bunke F2:

Vyhľadávacie pole podmieneného formátovania

Použitý vzorec je:

 
= ISNUMBER ( SEARCH ($F,B2))

Viac podrobností a úplné vysvetlenie nájdete na:

Riešenie problémov

Ak nemôžete správne spustiť pravidlá podmieneného formátovania, je pravdepodobne problém vo vašom vzorci. Najprv sa uistite, že ste vzorec začali znamienkom rovnosti (=). Ak na tento krok zabudnete, Excel ticho prevedie celý váš vzorec na text, čím sa stane nepoužiteľným. Ak to chcete opraviť, stačí odstrániť dvojité úvodzovky, ktoré Excel pridal na obidve strany, a uistiť sa, že vzorec začína na rovné (=).

Ak je váš vzorec zadaný správne, ale nespúšťa pravidlo, možno budete musieť siahnuť trochu hlbšie. Normálne, môžete pomocou klávesu F9 skontrolovať výsledky vo vzorci alebo pomocou funkcie Vyhodnotiť prechádzať po vzorci. Tieto nástroje bohužiaľ nemôžete používať so vzorcami podmieneného formátovania, ale môžete použiť techniku ​​nazývanú „fiktívne vzorce“.

Falošné vzorce

Falošné vzorce sú spôsob, ako otestovať vzorce podmieneného formátovania priamo v pracovnom hárku, aby ste videli, čo v skutočnosti robia. Keď sa snažíte, aby správne fungovali odkazy na bunky, môže to výrazne ušetriť čas.

Stručne povedané, zadáte rovnaký vzorec v celom rozsahu buniek, ktoré zodpovedajú tvaru vašich údajov. Vďaka tomu uvidíte hodnoty vrátené každým vzorcom a je to skvelý spôsob, ako si vizualizovať a porozumieť tomu, ako funguje podmienené formátovanie založené na vzorcoch. Na podrobné vysvetlenie pozri tento článok .

Na kontrolu vzorcov podmieneného formátovania použite fiktívne vzorce

Video: Otestujte podmienené formátovanie pomocou fiktívnych vzorcov

Obmedzenia

Podmienené formátovanie založené na vzorcoch obsahuje niekoľko obmedzení:

  1. S vlastným vzorcom nemôžete používať ikony, farebné škály ani dátové pruhy. Ste obmedzení na štandardné formátovanie buniek vrátane formátov čísel, písma, farby výplne a možností orámovania.
  2. Pre kritériá podmieneného formátovania nemôžete používať určité konštrukcie vzorcov, ako sú zväzky, priesečníky alebo konštanty poľa.
  3. Na iné zošity nemôžete odkazovať vo vzorci podmieneného formátovania.

Niekedy môžete pracovať okolo č. 2 a č. 3. Logiku vzorca možno budete môcť presunúť do bunky v hárku a potom sa odkazovať na danú bunku vo vzorci. Ak sa pokúšate použiť konštantu poľa, skúste namiesto toho vytvoriť pomenovaný rozsah.

Viac zdrojov vzorca CF

  • Viac ako 30 príkladov vzorcov podmieneného formátovania
  • Video školenie s cvičnými listami
Autor Dave Bruns


^