Dodavanje uvjetnog oblikovanja u Excelu omogućuje vam primjenu različitih opcija oblikovanja na ćeliju ili raspon ćelija koje ispunjavaju određene uvjete koje postavite. Postavljanje takvih uvjeta može vam pomoći organizirati proračunsku tablicu i olakšati skeniranje. Opcije oblikovanja koje možete koristiti uključuju promjene boje fonta i pozadine, stilove fonta, obrube ćelija i dodavanje formatiranja brojeva podacima.
Excel ima ugrađene opcije za često korištene uvjete kao što je pronalaženje brojeva koji su veći ili manji od određene vrijednosti ili pronalaženje brojeva koji su iznad ili ispod prosječne vrijednosti. Uz ove unaprijed postavljene opcije, također možete stvoriti prilagođena uvjetna pravila oblikovanja pomoću Excel formula.
Ove upute vrijede za Excel 2019, 2016, 2013, 2010 i Excel za Microsoft 365.
Primjena više uvjeta u Excelu
Možete primijeniti više od jednog pravila na iste podatke za testiranje različitih uvjeta. Na primjer, podaci o proračunu mogu imati postavljene uvjete koji primjenjuju promjene oblikovanja kada se dosegnu određene razine potrošnje, kao što su 50%, 75% i 100% ukupnog proračuna.
U takvim okolnostima, Excel prvo utvrđuje jesu li različita pravila u sukobu i, ako je tako, program slijedi postavljeni redoslijed prvenstva kako bi odredio koje pravilo uvjetnog oblikovanja primijeniti na podatke.
Pronalaženje podataka koji premašuju 25% i 50% povećanja
U sljedećem primjeru, dva prilagođena pravila uvjetnog oblikovanja primijenit će se na raspon od ćelija B2 do B5.
- Prvo pravilo provjerava jesu li podaci u ćelijama A2:A5 veći od odgovarajuće vrijednosti u B2:B5 za više od 25%.
- Drugo pravilo provjerava da li isti podaci u A2:A5 premašuju odgovarajuću vrijednost u B2:B5 za više od 50%.
Kao što se može vidjeti na gornjoj slici, ako je bilo koji od gornjih uvjeta istinit, boja pozadine ćelije ili ćelija u rasponu B1:B4 će se promijeniti.
- Za podatke gdje je razlika veća od 25%, boja pozadine ćelije promijenit će se u zelenu.
- Ako je razlika veća od 50%, boja pozadine ćelije promijenit će se u crvenu.
Pravila korištena za izvršenje ovog zadatka bit će unesena pomoću dijaloškog okvira Novo pravilo oblikovanja. Započnite unosom uzorka podataka u ćelije A1 do C5 kao što se vidi na gornjoj slici.
U završnom dijelu vodiča dodat ćemo formule u ćelije C2:C4 koje pokazuju točnu postotnu razliku između vrijednosti u ćelijama A2:A5 i B2:B5; ovo će nam omogućiti da provjerimo točnost pravila uvjetnog oblikovanja.
Postavljanje pravila uvjetnog oblikovanja
Prvo ćemo primijeniti uvjetno oblikovanje kako bismo pronašli značajno povećanje od 25 posto ili više.
Funkcija će izgledati ovako:
=(A2-B2)/A2>25%
- Označite ćelije B2 do B5 na radnom listu.
- Kliknite na karticu Početna na vrpci.
- Kliknite na Uvjetno oblikovanje ikonu na vrpci da biste otvorili padajući izbornik.
-
Odaberite Novo pravilo za otvaranje dijaloškog okvira Novo pravilo oblikovanja.
- Pod Odaberite vrstu pravila, kliknite posljednju opciju: Upotrijebite formulu da odredite koje ćelije formatirati.
- Upišite formulu gore navedenu u prostor ispod Formatirajte vrijednosti gdje je ova formula točna:
- Kliknite gumb Format za otvaranje dijaloškog okvira. Pritisnite karticu Ispuna i odaberite boju.
- Kliknite OK za zatvaranje dijaloških okvira i povratak na radni list.
- Boja pozadine ćelija B3 i B5 trebala bi se promijeniti u boju koju ste odabrali.
Sada ćemo primijeniti uvjetno oblikovanje kako bismo pronašli povećanje od 50 posto ili više. Formula će izgledati ovako:
- Ponovite prvih pet gornjih koraka.
- Upišite formulu navedenu gore u prostor ispod Formatirajte vrijednosti gdje je ova formula točna:
- Kliknite gumb Format za otvaranje dijaloškog okvira. Pritisnite karticu Ispuna i odaberite boju koja se razlikuje od one u prethodnom nizu koraka.
- Kliknite OK za zatvaranje dijaloških okvira i povratak na radni list.
Boja pozadine ćelije B3 treba ostati ista pokazujući postotak razlike između brojeva u ćelijama A3 iB3 je veći od 25 posto, ali manji ili jednak 50 posto. Boja pozadine ćelije B5 trebala bi se promijeniti u novu boju koju ste odabrali pokazujući postotak razlike između brojeva u ćelijama A5 i B5 je veći od 50 posto.
Provjera pravila uvjetnog oblikovanja
Da bismo provjerili jesu li unesena pravila uvjetnog oblikovanja ispravna, možemo unijeti formule u ćelije C2:C5 koje će izračunati točnu postotnu razliku između brojeva u rasponimaA2:A5 i B2:B5.
Formula u ćeliji C2 izgleda ovako:
=(A2-B2)/A2
- Kliknite na ćeliju C2 da biste je učinili aktivnom ćelijom.
- Upišite gornju formulu i pritisnite tipku Enter na tipkovnici.
- Odgovor 10% trebao bi se pojaviti u ćeliji C2, pokazujući da je broj u ćeliji A2 10% veći od broja u ćelija B2.
- Možda će biti potrebno promijeniti oblikovanje na ćeliji C2 za prikaz odgovora kao postotka.
- Koristite ručicu za popunjavanje za kopiranje formule iz ćelije C2 u ćelije C3 u C5.
- Odgovori za ćelije C3 do C5 trebaju biti 30%, 25% i 60%.
Odgovori u ovim ćelijama pokazuju da su pravila uvjetnog oblikovanja točna jer je razlika između ćelija A3 i B3 veća od 25 posto, a razlika između ćelija A5 i B5 veća je od 50 posto.
Ćelija B4 nije promijenila boju jer je razlika između ćelija A4 i B4 jednaka 25 posto, a naše pravilo uvjetnog oblikovanja navodi da je za promjenu boje pozadine potreban postotak veći od 25 posto.
Red prvenstva za uvjetno oblikovanje
Kada primijenite više pravila na isti raspon podataka, Excel prvo utvrđuje jesu li pravila u sukobu. Konfliktna pravila su ona kod kojih se opcije oblikovanja ne mogu obje primijeniti na iste podatke.
U našem primjeru, pravila su u sukobu jer oba koriste istu opciju oblikovanja - mijenjanje boje pozadine ćelije.
U situaciji kada je drugo pravilo istinito (razlika u vrijednosti je više od 50 posto između dvije ćelije), tada je prvo pravilo (razlika u vrijednosti veća od 25 posto) također istinito.
Budući da ćelija ne može imati dvije različite pozadine u boji u isto vrijeme, Excel mora znati koje pravilo uvjetnog oblikovanja treba primijeniti.
Redoslijed prvenstva programa Excel navodi da se prvo primjenjuje pravilo koje je više na popisu u dijaloškom okviru Upravitelj pravila uvjetnog oblikovanja.
Kao što je prikazano na gornjoj slici, drugo pravilo korišteno u ovom vodiču je više na popisu i stoga ima prednost nad prvim pravilom. Kao rezultat toga, boja pozadine ćelije B5 je zelena.
Prema zadanim postavkama, nova pravila idu na vrh popisa; za promjenu redoslijeda koristite gore i dolje gumbe sa strelicama u dijaloškom okviru.
Primjena nekonfliktnih pravila
Ako dva ili više pravila uvjetnog oblikovanja nisu u sukobu, oba se primjenjuju kada uvjet koji svako pravilo testira postane istinit.
Ako je prvo pravilo uvjetnog oblikovanja u našem primjeru oblikovalo raspon ćelija B2:B5 s narančastim obrubom umjesto narančastom bojom pozadine, dva pravila uvjetnog oblikovanja ne bi sukob budući da se oba formata mogu primijeniti bez ometanja drugoga.
Uvjetno oblikovanje u odnosu na redovno oblikovanje
U slučaju sukoba između pravila uvjetnog oblikovanja i ručno primijenjenih opcija oblikovanja, pravilo uvjetnog oblikovanja uvijek ima prednost i primjenjivat će se umjesto bilo kojih ručno dodanih opcija oblikovanja.
