Pogojno oblikovanje v Excelu: končni vodnik s primeri

Pogojno oblikovanje je ena najpreprostejših, a zmogljivih funkcij v Excelovih preglednicah.

Kot že ime pove, lahko v Excelu uporabite pogojno oblikovanje, če želite označiti celice, ki izpolnjujejo določen pogoj.

Omogoča vam hitro dodajanje plasti vizualne analize nad nabor podatkov. S pogojnim oblikovanjem v Excelu lahko ustvarite toplotne zemljevide, prikažete naraščajoče/padajoče ikone, Harveyjeve mehurčke in še veliko več.

Uporaba pogojnega oblikovanja v Excelu (primeri)

V tej vadnici vam bom pokazal sedem neverjetnih primerov uporabe pogojnega oblikovanja v Excelu:

• Hitro prepoznajte podvojene datoteke s pogojnim oblikovanjem v Excelu.
• Označite celice z vrednostjo večjo/manjšo od števila v naboru podatkov.
• Označevanje zgornjih/spodnjih 10 (ali 10%) vrednosti v naboru podatkov.
• Označevanje napak/praznin z uporabo pogojnega oblikovanja v Excelu.
• Ustvarjanje toplotnih zemljevidov s pogojnim oblikovanjem v Excelu.
• Označite vsako N -to vrstico/stolpec s pogojnim oblikovanjem.
• Iščite in označite z uporabo pogojnega oblikovanja v Excelu.

1. Hitro prepoznajte dvojnike

Pogojno oblikovanje v Excelu je mogoče uporabiti za prepoznavanje dvojnikov v naboru podatkov.

To lahko storite tako:

• Izberite nabor podatkov, v katerem želite označiti dvojnike.
• Pojdite na Domov -> Pogojno oblikovanje -> Označevanje pravil celice -> Podvojene vrednosti.
• V pogovornem oknu Podvojene vrednosti se prepričajte, da je v levem spustnem meniju izbrano Podvojeno. Z desnim spustnim menijem lahko določite obliko, ki jo želite uporabiti. Obstaja nekaj obstoječih oblik, ki jih lahko uporabite, ali določite svojo obliko z možnostjo Oblika po meri.
• Kliknite V redu.

S tem bi takoj označili vse celice, ki imajo podvojen v izbranem nizu podatkov. Vaš nabor podatkov je lahko v enem stolpcu, več stolpcih ali v neslednem območju celic.

Poglej tudi: Končni vodnik za iskanje in odstranjevanje podvojenih datotek v Excelu.

2. Označite celice z vrednostjo večjo/manjšo od števila

Pogojno oblikovanje v Excelu lahko uporabite za hitro označevanje celic, ki vsebujejo vrednosti, večje/manjše od podane vrednosti. Na primer označevanje vseh celic s prodajno vrednostjo manj kot 100 milijonov ali označevanje celic z oznakami, manjšimi od prehodnega praga.

Tu so naslednji koraki:

• Izberite celoten nabor podatkov.
• Pojdi na domačo stran -> Pogojno oblikovanje -> Pravila za označevanje celic -> Več kot… / Manj kot…
• Na podlagi izbrane možnosti (večje ali manjše) se odpre pogovorno okno. Recimo, da izberete možnost »Več kot«. V pogovorno okno vnesite številko v polje na levi. Namen je označiti celice, ki imajo večje število od tega določenega števila.

• Obliko, ki jo želite uporabiti za celice, ki izpolnjujejo pogoj, določite v spustnem meniju na desni. Obstaja nekaj obstoječih oblik, ki jih lahko uporabite, ali določite svojo obliko z možnostjo Oblika po meri.
• Kliknite V redu.

To bi takoj označilo vse celice z vrednostmi, večjimi od 5 v naboru podatkov.Opomba: Če želite označiti vrednosti, večje od enake 5, morate znova uporabiti pogojno oblikovanje s kriteriji »Enako«.

Po istem postopku lahko označimo celice z vrednostjo, manjšo od določenih vrednosti.

3. Označevanje zgoraj/spodaj 10 (ali 10%)

Pogojno oblikovanje v Excelu lahko hitro prepozna 10 najboljših elementov ali 10% najboljših iz nabora podatkov. To bi lahko bilo v pomoč v primerih, ko želite v podatkih o prodaji hitro videti najboljše kandidate glede na ocene ali vrednosti najvišje ponudbe.

Podobno lahko hitro identificirate tudi spodnjih 10 elementov ali 10% spodnjih v nizu podatkov.

Tu so naslednji koraki:

• Izberite celoten nabor podatkov.
• Pojdite na domačo stran -> pogojno oblikovanje -> zgornja / spodnja pravila -> 10 najboljših elementov (ali %) / spodnjih 10 elementov (ali %).
• Na podlagi izbranega odpre pogovorno okno. Recimo, da ste izbrali 10 najboljših elementov, potem bi se odprlo pogovorno okno, kot je prikazano spodaj:
• Z spustnim menijem na desni določite obliko, ki jo želite uporabiti za celice, ki izpolnjujejo pogoj. Obstaja nekaj obstoječih oblik, ki jih lahko uporabite, ali določite svojo obliko z možnostjo Oblika po meri.
• Kliknite V redu.

To bi takoj označilo prvih 10 elementov v izbranem naboru podatkov. Upoštevajte, da to deluje samo za celice, ki imajo številčno vrednost.

Če imate v naboru podatkov manj kot 10 celic in izberete možnosti za označbo Top 10 items/Bottom 10 Elements, bodo vse celice označene.

Tu je nekaj primerov, kako bi delovalo pogojno oblikovanje:

4. Označevanje napak/praznin

Če delate z veliko številčnimi podatki in izračuni v Excelu, bi vedeli, kako pomembno je prepoznati in obravnavati celice, ki imajo napake ali so prazne. Če te celice uporabimo pri nadaljnjih izračunih, lahko pride do napačnih rezultatov.

Pogojno oblikovanje v Excelu vam lahko pomaga hitro prepoznati in označiti celice z napakami ali prazne.

Recimo, da imamo nabor podatkov, kot je prikazano spodaj:

Ta niz podatkov ima prazno celico (A4) in napake (A5 in A6).

Tu so koraki za označevanje celic, ki so prazne ali imajo napake:

• Izberite nabor podatkov, v katerem želite označiti prazne celice in celice z napakami.
• Pojdite na dom -> Pogojno oblikovanje -> Novo pravilo.
• V pogovornem oknu Novo pravilo oblikovanja izberite Uporabi formulo, da določite, katere celice želite formatirati.
• V polje v polje »Uredi opis pravila« vnesite naslednjo formulo:
  = ALI (ISBLANK (A1), ISERROR (A1))
  • Zgornja formula preveri vse celice za dva pogoja - ali je prazno ali ne in ali ima napako ali ne. Če je kateri od pogojev TRUE, vrne TRUE.
• Nastavite obliko, ki jo želite uporabiti za celice, ki so prazne ali imajo napake. Če želite to narediti, kliknite gumb Oblika. Odprlo se bo pogovorno okno »Oblikovanje celic«, kjer lahko določite obliko.
• Kliknite V redu.

S tem bi takoj označili vse celice, ki so bodisi prazne ali imajo napake.

Opomba: V pogojnem oblikovanju vam ni treba uporabiti celotnega obsega A1: A7 v formuli. Zgoraj omenjena formula uporablja samo A1. Ko uporabite to formulo za celotno območje, Excel preveri eno celico naenkrat in prilagodi referenco. Ko na primer preveri A1, uporabi formulo = ALI (ISBLANK (A1), ISERROR (A1)). Ko preveri celico A2, potem uporabi formulo = ALI (ISBLANK (A2), ISERROR (A2)). Samodejno prilagodi referenco (ker so to relativne reference) glede na to, katero celico analiziramo. Zato vam za vsako celico ni treba napisati ločene formule. Excel je dovolj pameten, da sam spremeni sklic na celico 🙂

Poglej tudi: Uporaba IFERROR in ISERROR za odpravljanje napak v Excelu.

5. Ustvarjanje toplotnih zemljevidov

Toplotni zemljevid je vizualni prikaz podatkov, kjer barva predstavlja vrednost v celici. Na primer, lahko ustvarite toplotni zemljevid, kjer je celica z najvišjo vrednostjo obarvana zeleno in se z zmanjšanjem vrednosti premika proti rdeči barvi.

Nekaj, kot je prikazano spodaj:

Zgornji niz podatkov ima vrednosti med 1 in 100. Celice so označene glede na vrednost v njem. 100 dobi zeleno barvo, 1 dobi rdečo barvo.

Tu so koraki za ustvarjanje toplotnih zemljevidov s pogojnim oblikovanjem v Excelu.

• Izberite nabor podatkov.
• Pojdite na Domov -> Pogojno oblikovanje -> Barvne lestvice in izberite eno od barvnih shem.

Takoj, ko kliknete ikono toplotnega zemljevida, bo oblikovanje uporabilo za nabor podatkov. Izbirate lahko med več barvnimi prelivi. Če z obstoječimi barvnimi možnostmi niste zadovoljni, lahko izberete več pravil in določite želeno barvo.

Opomba: Na podoben način lahko uporabite tudi nabore podatkovnih vrstic in ikon.

6. Označite vsako drugo vrstico/stolpec

Morda boste želeli označiti nadomestne vrstice, da povečate berljivost podatkov.

Te se imenujejo črte zebra in bi lahko bile še posebej koristne, če tiskate podatke.

Zdaj obstajata dva načina za ustvarjanje teh linij zebre. Najhitrejši način je pretvoriti svoje tabelarne podatke v Excelovo tabelo. Samodejno je uporabil barvo za nadomestne vrstice. Več o tem si lahko preberete tukaj.

Drug način je uporaba pogojnega oblikovanja.

Recimo, da imate nabor podatkov, kot je prikazano spodaj:

Tu so koraki za označevanje nadomestnih vrstic s pogojnim oblikovanjem v Excelu.

• Izberite nabor podatkov. V zgornjem primeru izberite A2: C13 (ki izključuje glavo). Če želite vključiti tudi glavo, izberite celoten nabor podatkov.
• Odprite pogovorno okno Pogojno oblikovanje (Domov-> Pogojno oblikovanje-> Novo pravilo) [Bližnjica na tipkovnici - Alt + O + D].
• V pogovornem oknu izberite »Uporabi formulo za določitev celic, ki jih želite formatirati«.
• V polje v razdelku »Urejanje opisa pravila« vnesite naslednjo formulo:
  = ISODD (ROW ())

• Zgornja formula preveri vse celice in če je ROW številka celice liha, potem vrne TRUE. Določena pogojna oblika bi bila uporabljena za vse celice, ki vrnejo TRUE.
• Nastavite obliko, ki jo želite uporabiti za celice, ki so prazne ali imajo napake. Če želite to narediti, kliknite gumb Oblika. Odprlo se bo pogovorno okno »Oblikovanje celic«, kjer lahko določite obliko.
• Kliknite V redu.

To je to! Nadomestne vrstice v naboru podatkov bodo označene.

V mnogih primerih lahko uporabite isto tehniko. Vse kar morate storiti je, da v pogojnem oblikovanju uporabite ustrezno formulo. Tu je nekaj primerov:

• Označite nadomestne parne vrstice: = ISEVEN (ROW ())
• Označite nadomestne vrstice za dodajanje: = ISODD (ROW ())
• Označite vsako tretjo vrstico: = MOD (ROW (), 3) = 0

7. Iščite in označite podatke s pogojnim oblikovanjem

Ta je nekoliko napredna uporaba pogojnega oblikovanja. Tako bi bil videti kot Excel rock zvezda.

Recimo, da imate nabor podatkov, kot je prikazano spodaj, z imenom izdelka, prodajnim predstavnikom in zemljepisom. Zamisel je, da v celico C2 vnesete niz, in če se ujema s podatki v kateri koli celici, je treba to označiti. Nekaj, kot je prikazano spodaj:

Tukaj je nekaj korakov za ustvarjanje te funkcije Iskanje in označevanje:

• Izberite nabor podatkov.
• Pojdite na dom -> Pogojno oblikovanje -> Novo pravilo (Bližnjica na tipkovnici - Alt + O + D).
• V pogovornem oknu Novo pravilo oblikovanja izberite možnost »Uporabi formulo za določitev celic, ki jih želite oblikovati«.
• V polje v polje »Uredi opis pravila« vnesite naslednjo formulo:
  = AND ($ C $ 2 ””, $ C $ 2 = B5)
• Nastavite obliko, ki jo želite uporabiti za celice, ki so prazne ali imajo napake. Če želite to narediti, kliknite gumb Oblika. Odprlo se bo pogovorno okno »Oblikovanje celic«, kjer lahko določite obliko.
• Kliknite V redu.

To je to! Zdaj, ko v celico C2 vnesete karkoli in pritisnete enter, bodo označene vse ustrezne celice.

Kako to deluje?

Formula, uporabljena pri pogojnem oblikovanju, ovrednoti vse celice v naboru podatkov. Recimo, da v celico C2 vstopite na Japonsko. Zdaj bi Excel ocenil formulo za vsako celico.

Formula bi vrnila TRUE za celico, če sta izpolnjena dva pogoja:

• Celica C2 ni prazna.
• Vsebina celice C2 se popolnoma ujema z vsebino celice v naboru podatkov.

Tako so označene vse celice, ki vsebujejo besedilo Japonska.

Prenesite datoteko z vzorcem

Z isto logiko lahko ustvarite različice, kot so:

• Namesto celice označite celotno vrstico.
• Označite, tudi če je delno ujemanje.
• Med tipkanjem označite celice/vrstice (dinamično) [Ta trik vam bo všeč :)].

Kako odstraniti pogojno oblikovanje v Excelu

Po uporabi pogojno oblikovanje ostane na mestu, razen če ga odstranite ročno. Kot najboljšo prakso naj bo pogojno oblikovanje uporabljeno samo za tiste celice, kjer ga potrebujete.

Ker je nestanoviten, lahko povzroči počasen delovni zvezek Excel.

Če želite odstraniti pogojno oblikovanje:

• Izberite celice, iz katerih želite odstraniti pogojno oblikovanje.
• Pojdite na Domov -> Pogojno oblikovanje -> Počisti pravila -> Počisti pravila iz izbranih celic.
  • Če želite odstraniti pogojno oblikovanje iz celotnega delovnega lista, izberite Počisti pravila iz celotnega lista.

Pomembne stvari, ki jih morate vedeti o pogojnem oblikovanju v Excelu

• Pogojno oblikovanje v nestanovitnem. Lahko povzroči počasen delovni zvezek. Uporabite ga le, kadar je to potrebno.
• Ko kopirate celice za lepljenje, ki vsebujejo pogojno oblikovanje, se kopira tudi pogojno oblikovanje.
• Če za isti niz celic uporabite več pravil, ostanejo vsa pravila aktivna. V primeru prekrivanja ima prednost zadnje uporabljeno pravilo. Lahko pa spremenite vrstni red tako, da spremenite vrstni red v pogovornem oknu Upravljanje pravil.