Uvjetno oblikovanje u Excelu: Ultimativni vodič s primjerima

Uvjetno oblikovanje jedna je od najjednostavnijih, ali najmoćnijih značajki u proračunskim tablicama programa Excel.

Kao što naziv sugerira, možete koristiti uvjetno oblikovanje u Excelu kada želite istaknuti ćelije koje zadovoljavaju određeni uvjet.

Omogućuje vam brzo dodavanje sloja vizualne analize vašem skupu podataka. Pomoću uvjetnog oblikovanja u Excelu možete stvoriti toplinske karte, prikazati ikone za povećanje/smanjenje, Harvey mjehuriće i još mnogo toga.

Korištenje uvjetnog oblikovanja u Excelu (primjeri)

U ovom vodiču pokazat ću vam sedam nevjerojatnih primjera korištenja uvjetnog oblikovanja u Excelu:

• Brzo identificirajte duplikate pomoću uvjetnog oblikovanja u Excelu.
• Označite ćelije s vrijednošću većom/manjom od broja u skupu podataka.
• Isticanje gornjih/donjih 10 (ili 10%) vrijednosti u skupu podataka.
• Isticanje pogrešaka/praznina pomoću uvjetnog oblikovanja u Excelu.
• Izrada toplinskih karata pomoću uvjetnog oblikovanja u Excelu.
• Označite svaki N -ti redak/stupac pomoću uvjetnog oblikovanja.
• Pretražujte i označite pomoću uvjetnog oblikovanja u Excelu.

1. Brzo identificirajte duplikate

Uvjetno oblikovanje u Excelu može se koristiti za identifikaciju duplikata u skupu podataka.

Evo kako to možete učiniti:

• Odaberite skup podataka u kojem želite istaknuti duplikate.
• Idite na Početna -> Uvjetno oblikovanje -> Isticanje pravila ćelije -> Duplicirane vrijednosti.
• U dijaloškom okviru Duplicate Values ​​provjerite je li Duplicate odabran u lijevom padajućem izborniku. Pomoću desnog padajućeg izbornika možete odrediti format koji će se primijeniti. Postoje neki postojeći formati koje možete koristiti ili odrediti vlastiti format pomoću opcije Prilagođeni format.
• Pritisnite U redu.

Time bi se odmah istaknule sve ćelije koje imaju duplikat u odabranom skupu podataka. Vaš skup podataka može biti u jednom stupcu, više stupaca ili u neslijednom rasponu ćelija.

Vidi također: Ultimativni vodič za pronalaženje i uklanjanje duplikata u Excelu.

2. Označite ćelije s vrijednošću većom/manjom od broja

Uvjetno oblikovanje u Excelu možete koristiti za brzo označavanje ćelija koje sadrže vrijednosti veće/manje od navedene vrijednosti. Na primjer, isticanje svih ćelija čija je prodajna vrijednost manja od 100 milijuna ili označavanje ćelija oznakama manjim od praga prolaska.

Evo koraka za to:

• Odaberite cijeli skup podataka.
• Idite na početnu stranicu -> Uvjetno oblikovanje -> Označavanje pravila ćelije -> Više od … / Manje od …
• Na temelju opcije koju odaberete (veću ili manju od), otvorit će se dijaloški okvir. Recimo, odaberete opciju "Veće od". U dijaloški okvir unesite broj u polje s lijeve strane. Namjera je istaknuti ćelije koje imaju broj veći od navedenog broja.

• Navedite format koji će se primijeniti na ćelije koje ispunjavaju uvjet pomoću padajućeg izbornika s desne strane. Postoje neki postojeći formati koje možete koristiti ili odrediti vlastiti format pomoću opcije Prilagođeni format.
• Pritisnite U redu.

Time bi se odmah istaknule sve ćelije s vrijednostima većim od 5 u skupu podataka.Napomena: Ako želite istaknuti vrijednosti veće od jednake 5, trebali biste ponovno primijeniti uvjetno oblikovanje s kriterijem „Jednako prema“.

Isti se postupak može slijediti za označavanje ćelija s vrijednošću manjom od navedenih vrijednosti.

3. Isticanje vrha/dna 10 (ili 10%)

Uvjetno oblikovanje u Excelu može brzo identificirati 10 najboljih stavki ili 10% najboljih iz skupa podataka. To bi moglo biti korisno u situacijama u kojima želite brzo vidjeti najbolje kandidate prema rezultatima ili vrijednostima najbolje ponude u podacima o prodaji.

Slično tome, također možete brzo identificirati 10 najnižih stavki ili 10% najnižih u skupu podataka.

Evo koraka za to:

• Odaberite cijeli skup podataka.
• Idite na Početna -> Uvjetno oblikovanje -> Gornja / donja pravila -> 10 najboljih stavki (ili %) / 10 donjih stavki (ili %).
• Na temelju onoga što odaberete, otvorit će se dijaloški okvir. Recimo da ste odabrali 10 najboljih stavki, tada bi se otvorio dijaloški okvir kao što je prikazano u nastavku:
• Navedite format koji će se primijeniti na ćelije koje ispunjavaju uvjet pomoću padajućeg izbornika s desne strane. Postoje neki postojeći formati koje možete koristiti ili odrediti vlastiti format pomoću opcije Prilagođeni format.
• Pritisnite U redu.

Time bi se trenutno istaknulo prvih 10 stavki u odabranom skupu podataka. Imajte na umu da ovo radi samo za ćelije koje imaju numeričku vrijednost.

Također, ako u skupu podataka imate manje od 10 ćelija, a odaberete opcije za isticanje Top 10 stavki/Donjih 10 stavki, tada će se sve ćelije istaknuti.

Evo nekoliko primjera kako bi uvjetno oblikovanje funkcioniralo:

4. Isticanje pogrešaka/praznina

Ako radite s mnogo numeričkih podataka i izračuna u Excelu, znali biste koliko je važno identificirati i tretirati ćelije koje imaju pogreške ili su prazne. Ako se te ćelije koriste u daljnjim izračunima, to bi moglo dovesti do pogrešnih rezultata.

Uvjetno oblikovanje u Excelu može vam pomoći da brzo identificirate i označite ćelije koje imaju pogreške ili su prazne.

Pretpostavimo da imamo skup podataka kao što je prikazano u nastavku:

Ovaj skup podataka ima praznu ćeliju (A4) i pogreške (A5 i A6).

Evo koraka za označavanje ćelija koje su prazne ili sadrže pogreške:

• Odaberite skup podataka u kojem želite označiti prazne ćelije i ćelije s pogreškama.
• Idite na Početna -> Uvjetno oblikovanje -> Novo pravilo.
• U dijaloškom okviru Novo pravilo oblikovanja odaberite Koristi formulu da biste odredili koje ćelije oblikovati.
• Unesite sljedeću formulu u polje u odjeljak "Uređivanje opisa pravila":
  = ILI (ISBLANK (A1), ISERROR (A1))
  • Gornja formula provjerava sve ćelije za dva uvjeta - je li prazno ili nije i ima li pogrešku ili ne. Ako je bilo koji od uvjeta TRUE, vraća TRUE.
• Postavite format koji želite primijeniti na ćelije koje su prazne ili imaju pogreške. Da biste to učinili, kliknite gumb Oblikovanje. Otvorit će se dijaloški okvir "Oblikovanje ćelija" u kojem možete odrediti format.
• Pritisnite U redu.

Time bi se odmah istaknule sve ćelije koje su ili prazne ili imaju pogreške.

Bilješka: Ne morate koristiti cijeli raspon A1: A7 u formuli u uvjetnom oblikovanju. Gore spomenuta formula koristi samo A1. Kada ovu formulu primijenite na cijeli raspon, Excel provjerava jednu po jednu ćeliju i prilagođava referencu. Na primjer, kada provjerava A1, koristi formulu = ILI (ISBLANK (A1), ISERROR (A1)). Kad provjerava ćeliju A2, tada koristi formulu = ILI (ISBLANK (A2), ISERROR (A2)). Automatski prilagođava referencu (budući da su to relativne reference) ovisno o tome koja se ćelija analizira. Dakle, ne morate pisati zasebnu formulu za svaku ćeliju. Excel je dovoljno pametan da sam promijeni referencu ćelije 🙂

Vidi također: Korištenje IFERROR -a i ISERROR -a za rješavanje pogrešaka u Excelu.

5. Izrada toplinskih karata

Toplinska karta je vizualni prikaz podataka gdje boja predstavlja vrijednost u ćeliji. Na primjer, možete stvoriti toplinsku kartu gdje je ćelija s najvećom vrijednošću obojena zelenom bojom i dolazi do pomaka prema crvenoj boji kako se vrijednost smanjuje.

Nešto kao što je prikazano ispod:

Gornji skup podataka ima vrijednosti između 1 i 100. Ćelije su označene na temelju vrijednosti u njemu. 100 dobiva zelenu boju, 1 dobiva crvenu boju.

Evo koraka za stvaranje toplinskih karata pomoću uvjetnog oblikovanja u Excelu.

• Odaberite skup podataka.
• Idite na Početna -> Uvjetno oblikovanje -> Ljestvice boja i odaberite jednu od shema boja.

Čim kliknete na ikonu toplinske karte, formatiranje će se primijeniti na skup podataka. Možete izabrati više gradijenata boja. Ako niste zadovoljni postojećim opcijama boja, možete odabrati više pravila i odrediti boju koju želite.

Napomena: Na sličan način možete primijeniti i skupove podatkovnih traka i ikona.

6. Označite svaki drugi redak/stupac

Možda ćete htjeti istaknuti zamjenske redove kako biste povećali čitljivost podataka.

To se naziva zebra linija i moglo bi biti osobito korisno ako ispisujete podatke.

Sada postoje dva načina za stvaranje ovih zebrinih linija. Najbrži način je pretvoriti svoje tablične podatke u Excel tablicu. Automatski je primijenio boju na naizmjenične redove. Više o tome možete pročitati ovdje.

Drugi način je korištenje uvjetnog oblikovanja.

Pretpostavimo da imate skup podataka kao što je prikazano u nastavku:

Evo koraka za označavanje zamjenskih redaka pomoću uvjetnog oblikovanja u Excelu.

• Odaberite skup podataka. U gornjem primjeru odaberite A2: C13 (koji isključuje zaglavlje). Ako želite uključiti i zaglavlje, odaberite cijeli skup podataka.
• Otvorite dijaloški okvir Uvjetno oblikovanje (Početna-> Uvjetno oblikovanje-> Novo pravilo) [Tipkovni prečac - Alt + O + D].
• U dijaloškom okviru odaberite dijaloški okvir "Koristi formulu za određivanje ćelija za oblikovanje".
• Unesite sljedeću formulu u polje u odjeljak "Uređivanje opisa pravila":
  = ISODD (ROW ())

• Gornja formula provjerava sve ćelije i ako je ROW broj ćelije neparan, onda vraća TRUE. Navedeni uvjetni format primijenio bi se na svu ćeliju koja vraća TRUE.
• Postavite format koji želite primijeniti na ćelije koje su prazne ili imaju pogreške. Da biste to učinili, kliknite gumb Oblikovanje. Otvorit će se dijaloški okvir "Oblikovanje ćelija" u kojem možete odrediti format.
• Pritisnite U redu.

To je to! Zamjenski retci u skupu podataka bit će istaknuti.

U mnogim slučajevima možete koristiti istu tehniku. Sve što trebate učiniti je koristiti odgovarajuću formulu u uvjetnom oblikovanju. Evo nekoliko primjera:

• Označite zamjenske parne redove: = ISEVEN (ROW ())
• Označite zamjenske redove za dodavanje: = ISODD (ROW ())
• Označite svaki 3. red: = MOD (ROW (), 3) = 0

7. Pretražujte i označite podatke pomoću uvjetnog oblikovanja

Ovo je pomalo napredna upotreba uvjetnog oblikovanja. Tako biste izgledali kao Excel rock zvijezda.

Pretpostavimo da imate skup podataka kao što je prikazano u nastavku, s nazivom proizvoda, prodajnim predstavnikom i zemljopisom. Ideja je upisati niz u ćeliju C2, a ako se podudara s podacima u bilo kojoj ćeliji (ćelijama), to bi trebalo biti istaknuto. Nešto kao što je prikazano ispod:

Evo koraka za stvaranje ove funkcije Pretraživanje i isticanje:

• Odaberite skup podataka.
• Idite na Početna -> Uvjetno oblikovanje -> Novo pravilo (Prečac na tipkovnici - Alt + O + D).
• U dijaloškom okviru Novo pravilo oblikovanja odaberite opciju "Koristi formulu za određivanje ćelija koje ćete oblikovati".
• Unesite sljedeću formulu u polje u odjeljak "Uređivanje opisa pravila":
  = AND ($ C $ 2 ””, $ C $ 2 = B5)
• Postavite format koji želite primijeniti na ćelije koje su prazne ili imaju pogreške. Da biste to učinili, kliknite gumb Oblikovanje. Otvorit će se dijaloški okvir "Oblikovanje ćelija" u kojem možete odrediti format.
• Pritisnite U redu.

To je to! Sada kada unesete bilo što u ćeliju C2 i pritisnete enter, označit će sve odgovarajuće ćelije.

Kako ovo radi?

Formula korištena u uvjetnom oblikovanju procjenjuje sve ćelije u skupu podataka. Recimo da uđete u Japan u ćeliji C2. Sada bi Excel procijenio formulu za svaku ćeliju.

Formula će vratiti TRUE za ćeliju ako su zadovoljena dva uvjeta:

• Ćelija C2 nije prazna.
• Sadržaj ćelije C2 točno se podudara sa sadržajem ćelije u skupu podataka.

Stoga će sve ćelije koje sadrže tekst Japan biti istaknute.

Preuzmite datoteku primjera

Možete koristiti istu logiku za stvaranje varijacija kao što su:

• Označite cijeli redak umjesto ćelije.
• Istaknite čak i ako postoji djelomično podudaranje.
• Istaknite ćelije/retke dok tipkate (dinamički) [Svidjet će vam se ovaj trik :)].

Kako ukloniti uvjetno oblikovanje u Excelu

Nakon primjene, uvjetno oblikovanje ostaje na snazi, osim ako ga ručno uklonite. Kao najbolju praksu, neka se uvjetno oblikovanje primjenjuje samo na one ćelije gdje vam je potrebno.

Budući da je nestabilan, može dovesti do spore radne knjige programa Excel.

Da biste uklonili uvjetno oblikovanje:

• Odaberite ćelije iz kojih želite ukloniti uvjetno oblikovanje.
• Idite na Početna -> Uvjetno oblikovanje -> Obriši pravila -> Izbriši pravila iz odabranih ćelija.
  • Ako želite ukloniti uvjetno oblikovanje s cijelog radnog lista, odaberite Očisti pravila s cijelog lista.

Važne stvari koje trebate znati o uvjetnom oblikovanju u Excelu

• Uvjetno oblikovanje u nestabilnom obliku. To može dovesti do spore radne sveske. Koristite ga samo po potrebi.
• Kad kopirate ćelije zalijepljenja koje sadrže uvjetno oblikovanje, kopira se i uvjetno oblikovanje.
• Ako primijenite više pravila na isti skup ćelija, sva pravila ostaju aktivna. U slučaju bilo kakvog preklapanja, prednost se daje posljednjem primijenjenom pravilu. Međutim, možete promijeniti redoslijed promjenom redoslijeda u dijaloškom okviru Upravljanje pravilima.