Korištenje formula za uvjetno oblikovanje u programu Excel

Dodavanje uvjetnog oblikovanja u programu Excel omogućuje primjenu različitih opcija oblikovanja na ćeliju ili raspon ćelija koje zadovoljavaju određene uvjete koje postavite. Opcije oblikovanja primjenjuju se samo kada odabrane stanice zadovoljavaju ove postavljene uvjete.

Opcije oblikovanja koje se mogu primijeniti uključuju promjene fontova i pozadinske boje, stilove fonta, granice ćelija i dodavanje formata broja u podatke.

Excel je imao brojne ugrađene opcije za uobičajene uvjete kao što su 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.

Pored ovih unaprijed postavljenih opcija, također je moguće stvoriti prilagođena pravila o oblikovanju uvjeta pomoću Excelovih formula za testiranje korisničkih uvjeta.

01 od 04

Primjena više pravila u programu Excel

Na istim se podacima može primijeniti više pravila za testiranje različitih uvjeta. Na primjer, podaci o proračunu mogu imati postavljene uvjete koji primjenjuju oblikovanje izmjena kada se potroše određene razine, kao što je 50%, 75% i 100% ukupnog proračuna.

U takvim okolnostima, Excel prvo određuje jesu li različita pravila sukobljena, a ako je tako, program slijedi postavljen redoslijed prednosti kako bi se utvrdilo koji se uvjetni oblikovni pravilo primjenjuje na podatke.

Pronalaženje podataka koji premašuju 25% i 50% povećanja

U sljedećem primjeru dva se pravila uvjetnog oblikovanja primjenjuju na raspon od stanice B2 do B5.

• Prvo pravilo provjerava jesu li podaci u sustavu stanice A2 do A5 je veća od odgovarajuće vrijednosti u B2 do B5 za više od 25%.
• Drugo pravilo provjerava jesu li isti podaci u sustavu Windows Vista A2: A5 prelazi 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 gore navedenih uvjeta istinit, boja pozadine stanica ili stanica u rasponu B1: B4 će promijeniti.

• Za podatke gdje je razlika veća od 25%, boja pozadine ćelije će se promijeniti u zelenu boju.
• Ako je razlika veća od 50%, boja pozadine staništa će se promijeniti u crvenu boju.

Pravila koja se koriste za izvršenje ovog zadatka bit će unesena pomoću uvjetnog oblikovanja Novi pravilo oblikovanja dijaloški okvir. Započnite unosom uzorka podataka u ćelije A1 do C5 kao što je vidljivo na slici iznad

U treći korak udžbenika će dodati formule stanicama C2: C4 koji pokazuju točnu razinu postotka između vrijednosti u stanicama A2: A5 i B2: B5 kako bi se provjerila točnost pravila uvjetnog oblikovanja.

02 od 04

Postavljanje uvjetnog pravila za oblikovanje

Kao što je spomenuto, pravila uvjetnog oblikovanja koje provjeravaju dva uvjeta bit će unesena pomoću uvjetnog oblikovanja Novi pravilo oblikovanja dijaloški okvir. Počnimo s postavljanjem prilagođenog uvjetnog oblikovanja:

Uvjetno oblikovanje da se pronađe više od 25 posto povećanja

= (A2-B2) / A2> 25%

1. vrhunac stanice B2 do B5 u radnom listu.
2. Klikni na Početna kartica od vrpca.
3. Klikni na Uvjetno oblikovanje ikona u vrpca za otvaranje padajućeg izbornika.
4. izabrati Novi pravilo otvoriti Novi pravilo oblikovanja dijaloški okvir.
5. Od Stil: , odaberite Klasik opcija.
6. U drugom padajućem izborniku kliknite zadnju opcijuUpotrijebite formulu za određivanje stanica za oblikovanje.
7. Upišite formula gore navedeno u predviđenom prostoru.
8. Klikni na Formatiranje pomoću: padajući izbornik, a zatim odaberite Svijetlo crveno ispunite tamno crvenim tekstom opcija.
9. Klik u redu da biste zatvorili dijaloške okvire i vratili se na radni list.
10. U ovom trenutku, boja pozadine stanice B3 i B5 trebao bi biti zelen.

Uvjetno oblikovanje kako bi se pronašao veći od 50 posto povećanja

= (A2-B2) / A2> 50%

vrhunac stanice B2 do B5 u radnom listu.

1. Klikni na Početna kartica od vrpca.
2. Klikni na Uvjetno oblikovanje ikona u vrpca za otvaranje padajućeg izbornika.
3. izabrati Novi pravilo otvoriti Novi pravilo oblikovanja dijaloški okvir.
4. Od Stil: , odaberite Klasik opcija.
5. U drugom padajućem izborniku kliknite zadnju opcijuUpotrijebite formulu za određivanje stanica za oblikovanje.
6. Upišite formula gore navedeno u predviđenom prostoru.
7. Klikni na Formatiranje pomoću: padajući izbornik, a zatim odaberite Svijetlo zeleno ispunite tamnim zelenim tekstom opcija.
8. Klik u redu da biste zatvorili dijaloške okvire i vratili se na radni list.

Boja pozadine stanica B3 bi i dalje trebalo biti zeleno, što znači da je postotna razlika između brojeva u stanice A3 i B3 je veći od 25 posto, ali manji ili jednak 50 posto. Boja pozadine stanice B5 trebao bi se promijeniti na crvenu boju, što znači da je postotna razlika između brojeva u stanice A5 i B5 je veći od 50 posto.

03 od 04

Provjera uvjetnih pravila oblikovanja

Da biste provjerili jesu li uneseni uvjeti uvjetnog oblikovanja ispravni, možemo unijeti formule u ćelije C2: C5 koji će izračunati točan postotak razlike između brojeva u rasponima A2: A5 i B2: B5.

= (A2-B2) / A2

1. Kliknite na ćelije C2 da je postane aktivna stanica.
2. Upišite gore navedenu formulu i pritisnite Unesi na tipkovnici.
3. Treba se pojaviti odgovor od 10% ćelije C2, što znači da je broj u stanica A2 je 10% veći od broja u stanica B2.
4. Možda je potrebno promijeniti oblikovanje ćelije C2 kako bi se odgovor prikazao kao postotak.
5. Koristiti punjenje ručicom za kopiranje formule iz ćelije C2 do stanice C3 do C5.
6. Odgovori za stanice C3 do C5 treba biti 30%, 25% i 60%.

Odgovori u tim ćelijama pokazuju da su stvorena pravila uvjetovanog oblikovanja ispravna jer razlika između stanice A3 i B3 je veći od 25 posto i razlika između stanice A5 i B5 je veći od 50 posto.

Stanica B4 nije promijenio boju jer razlika između stanice A4 i B4 jednako je 25%, a naš uvjetovani pravilno oblikovanje navodi da je postotak veći od 25% bio potreban za boju pozadine da se promijeni u zelenu boju.

04 od 04

Redoslijed prioriteta uvjetnog oblikovanja

Kada se primjenjuju više pravila za isti raspon podataka, Excel prvo određuje sukladnost pravila. Sukobna pravila su ona u kojima se odabrane opcije oblikovanja za svako pravilo ne mogu primijeniti na iste podatke.

U primjeru koji se koristi u ovom vodiču, pravila se sukobljuju jer oba pravila upotrebljavaju istu opciju oblikovanja - promjenu boje pozadinske ćelije.

U situaciji u kojoj je drugo pravilo istinito (razlika u vrijednosti veća je od 50 posto između dvije ćelije), tada vrijedi i prvo pravilo (razlika vrijednosti veća od 25 posto).

Excelov redoslijed prednosti

Budući da stanica ne može istodobno imati crvenu i zelenu pozadinu, Excel treba znati koji uvjet uvjetnog oblikovanja treba primijeniti.

Kakvo pravilo se primjenjuje određuje Excelov redoslijed prednosti, koji navodi da je pravilo veće na popisu u dijaloškom okviru Upravljanje pravilima uvjetnog oblikovanja ima prednost.

Kao što je prikazano na gornjoj slici, drugo pravilo upotrijebljeno u ovom vodiču više je na popisu i stoga ima prednost nad prvim pravilom. Kao rezultat, boja pozadine ćelije B5 promijenjena je u crvenu boju.

Prema zadanim postavkama, nova se pravila dodaju na vrh popisa i stoga imaju veću prednost. Za promjenu redoslijeda koristite Gore i dolje tipke sa strelicama u dijaloškom okviru kao što je naznačeno na gornjoj slici.

Primjena pravila koja nisu u sukobu

Ako se dva ili više pravila uvjetnog oblikovanja ne uspoređuju, oba se primjenjuju kada se stanje svako pravilo provodi.

Ako je prvi pravilo uvjetnog oblikovanja u našem primjeru oblikovalo raspon ćelija B2: B5 s zelenom granicom umjesto zelene boje pozadine, dva pravila uvjetnog oblikovanja ne bi bila sukobljena jer se oba formata mogu primijeniti bez ometanja drugog.

Kao rezultat, stanice B5 imali bi i zelenu granicu i crvenu boju pozadine, budući da je razlika između brojeva u stanice A5 i B5 je veći od 25 i 50 posto.

Uvjetno oblikovanje u odnosu na redovno oblikovanje

U slučaju sukoba između uvjetnih pravila oblikovanja i ručno primijenjenih opcija oblikovanja, uvjetno pravilo oblikovanja uvijek ima prednost, a primijenit će se umjesto ručno dodanih opcija oblikovanja.

Ako se na početku primijeni žuta boja pozadine stanice B2 do B5 u primjeru, nakon što su dodani pravila uvjetnog oblikovanja, samo stanice B2 i B4 ostat će žuta. Budući da se primjenjuju uvjeti za uvjetno oblikovanje primjenjuju se na stanice B3 i B5, njihove pozadinske boje mijenjale bi se od žute do zelene i crvene.