Podmienené formátovanie pomocou vzorcov v programe Excel
V predvolenom nastavení nám program Excel už poskytol mnoho typov podmieneného formátovania, ktoré sú k dispozícii pre naše údaje, ale ak chce ktorýkoľvek používateľ chcieť, aby sa formátovanie vykonalo podľa konkrétnych kritérií alebo vzorca, môžeme tak urobiť na domovskej karte sekcie podmieneného formátovania, keď kliknutím na nové pravidlo nájdeme možnosť podmieneného formátovania pomocou vzorcov, kde môžeme písať vzorce, ktoré definujú bunky, ktoré sa majú formátovať.
Podmienené formátovanie pomocou vzorca nie je nič iné ako zmena formátu buniek na základe podmienok alebo kritérií daných používateľom. Možno ste použili podmienené formátovanie na zvýraznenie najvyššej hodnoty v rozsahu a tiež na zvýraznenie duplicitných hodnôt.
Dobrá vec je, že máme vzorce v podmienenom formátovaní v programe Excel. Všetky vzorce by mali byť logické; výsledok bude PRAVDA alebo NEPRAVDA. Ak logický test prejde, dostaneme podmienené formátovanie a ak zlyhá logický test programu Excel, nedostaneme nič.
Prehľad
Dovoľte mi predstaviť vám podmienené formátovanie pomocou okna vzorca.
V nasledujúcej časti článku uvidíme širokú škálu príkladov použitia podmieneného formátovania pomocou vzorcov.
Ako používať podmienené formátovanie programu Excel so vzorcami?
# 1 - Zvýraznite bunky, ktoré majú hodnoty menšie ako 500
Predpokladajme, že nižšie sú predajné čísla, s ktorými pracujete, a nižšie je predajná cena za jednotku.
- Krok 1: Prejdite na Podmienené formátovanie a kliknite na Nové pravidlo.
- Krok 2: Teraz vyberte možnosť „ Pomocou vzorca určiť, ktoré bunky sa majú formátovať.“
- Krok 3: Teraz v časti „ Hodnoty formátovania, kde je vzorec pravdivý“ použite vzorec ako A2 <500 a potom kliknutím na tlačidlo Formátovať použite formátovanie programu Excel.
- Krok 4: Vyberte formát podľa vášho želania.
- Krok 5: Teraz môžete v okne s ukážkou vidieť ukážku formátu.
- Krok 6: Teraz kliknite na OK na dokončenie formátovania. Teraz zvýraznila všetky bunky, ktoré majú číslo <500.
# 2 - Zvýraznite jednu bunku na základe inej bunky
Jednu bunku môžeme zvýrazniť na základe inej hodnoty bunky. Predpokladajme, že v prvých dvoch stĺpcoch máme údaje o názve produktu a cene produktu.
Z vyššie uvedenej tabuľky musíme produkty zvýrazniť, ak je predajná cena> 220.
- Krok 1: Vyberte produktový rad a potom prejdite na podmienené formátovanie a kliknite na Nové pravidlo.
- Krok 2: Vo vzorci použite vzorec ako B2> 220.
- Krok 3: Kliknite na kľúč Formát a použite formát podľa vášho želania.
- Krok 5: Kliknite na OK; máme pripravené formátovanie.
# 3 - Zvýraznite všetky prázdne bunky v rozsahu
Predpokladajme, že nižšie sú údaje, ktoré máte.
Vo vyššie uvedených údajoch musíte zvýrazniť všetky bunky, ktoré sú prázdne alebo prázdne. V podmienenom formátovaní programu Excel musíme použiť vzorec ISBLANK.
Použite nasledujúci vzorec v sekcii vzorce.
Vyberte formátovanie podľa vášho želania.
Kliknutím na ok zvýrazníte všetky prázdne bunky vo vybranom rozsahu.
# 4 - Na zvýraznenie buniek použite funkciu AND
Zvážte nižšie uvedené údaje pre tento vzorec.
Ak je región stredný a hodnota predaja je> 500, musíme zvýrazniť celý riadok.
- Krok 1: Najskôr vyberte celé údaje.
- Krok 2: Tu musíme použiť funkciu AND excel, pretože tu musíme otestovať dve podmienky a obe podmienky by mali byť PRAVDA. Použite nasledujúci vzorec.
- Krok 3: Kliknite na Formátovať.
- Krok 4: Prejdite na FILL a vyberte požadovanú farbu a efekty.
- Krok 5: Kliknite na, OK. Uvidíme zvýraznené riadky, ak sú obidve podmienky TRUE.
# 5- Na zvýraznenie buniek použite funkciu ALEBO
Pre príklad zvážte nižšie uvedené údaje.
V uvedenom rozsahu údajov chcem zdôrazniť názvy miest Bangalore a Mysore.
- Krok 1: Najskôr vyberte údaje a prejdite na Podmienené formátovanie, potom kliknite na Nové pravidlo.
- Krok 2: Pretože musíme zvýrazniť niektorú z dvoch hodnotových buniek, použite funkciu OR excel.
- Krok 3: Kliknite na formát a vyberte požadovaný formát.
- Krok 4: Po použití formátovania dokončite úlohu kliknutím na OK. Vzorec zvýrazní všetky bunky, ktoré majú hodnoty Bangalore & Mysore.
# 6 - Na zvýraznenie buniek použite funkciu COUNTIF
Predpokladajme, že pracujete s databázou zákazníkov. Musíte identifikovať všetkých klientov, ktorých mená sa v zozname nachádzajú viac ako päťkrát. Nižšie sú uvedené údaje.
Vyberte údaje a použite vzorec COUNTIF v časti so vzorcom.
Kliknite na formát a použite požadované formátovanie.
Vzorec teraz zvýrazní všetky mená, ak je ich počet viac ako 5.
Jantárová je jediný názov, ktorý sa na zozname objaví viac ako päťkrát.
# 7 - Zvýraznite každý alternatívny riadok
Môžeme mať aj vzorec na zvýraznenie každého alternatívneho riadku v excele. Predpokladajme, že nižšie sú údaje, na ktorých pracujete.
Vyberte údaje a použite nasledujúci vzorec.
= MOD (riadok (), 2)
Prihláste sa do formátu podľa vášho želania a kliknite na OK; vzorec by sme zvýraznili každý alternatívny riadok.
# 8 - Zvýraznite každý alternatívny stĺpec
Podobne, ako sme podobne zvýraznili každý alternatívny riadok, môžeme zvýrazniť aj každý alternatívny stĺpec. Zvážte rovnaké údaje pre príklad.
Použite nasledujúci vzorec.
= MOD (STĹPEC (), 2)
Skopírujte vyššie uvedený vzorec a použite ho v sekcii vzorcov podmieneného formátovania.
Kliknite na OK; zvýrazní sa alternatívny riadok.
Ak chcete vynechať prvý stĺpec a zvýrazniť ho v druhom stĺpci, musíte použiť nasledujúci vzorec.
Zvýraznil by sa alternatívny stĺpec začínajúci od druhého stĺpca.
Na čo treba pamätať
- Podmienené formátovanie akceptuje iba logické vzorce, ktorých výsledky sú buď TRUE, alebo FALSE.
- Ukážka podmieneného formátovania je iba indikáciou toho, ako formátovanie vyzerá.
- Nikdy nepoužívajte absolútnu referenciu ako vo vzorci. Keď aplikujete vzorec a ak vyberiete bunku priamo, urobí to ako absolútny odkaz, musíte to urobiť v relatívnom odkaze v programe Excel.
