Formule za uslovno oblikovanje Excela

Dodavanje uslovnog oblikovanja u Excelu omogućava vam da primenite različite opcije oblikovanja u ćeliju ili niz ćelija koje ispunjavaju određene uslove koje ste postavili.

Opcije oblikovanja se primjenjuju samo ako odabrane ćelije ispunjavaju navedene postavljene uslove.

Opcije oblikovanja koje se mogu primeniti uključuju promene boje fonta i pozadine, stilove fontova, granice ćelija i dodavanje broja formata na podatke.

Pošto je Excel 2007, Excel je imao brojne ugrađene opcije za najčešće korišćene uslove, kao što je pronalaženje brojeva koji su veći ili manji od određene vrijednosti ili pronalazak brojeva koji su iznad ili ispod prosječne vrijednosti .

Pored ovih unapred podešenih opcija, takođe je moguće kreirati prilagođena pravila za uslovno oblikovanje koristeći Excel formule za testiranje uslova specificiranih korisnikom.

Primjena višestrukih pravila

Za iste podatke može se primijeniti više od jednog pravila za testiranje različitih uslova. Na primjer, podaci o budžetu mogu imati postavljene uslove koji primjenjuju promjene formata kada se potroše određeni nivoi - kao što su 50%, 75% i 100% - od ukupnog budžeta.

U takvim okolnostima, Excel prvo utvrđuje da li se različita pravila sukobljavaju i, ukoliko je to slučaj, program prati određeni red precedence da bi se odredilo koje uslovno pravilo oblikovanja se primenjuje na podatke.

Primer: Pronalaženje podataka koji prelaze 25% i 50% povećava se s uslovnim oblikovanjem

U sledećem primjeru će se primijeniti dvije prilagođene pravila za uslovno oblikovanje na opseg ćelija B2 do B5.

• prvo pravilo provjerava da li su podaci u ćelijama A2 do A5 veći od odgovarajuće vrijednosti u B2 do B5 za više od 25%.
• drugo pravilo proverava 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 videti na slici iznad, ako je jedan od gorenavedenih uslova tačan, boja boje ćelije ili ćelija u opsegu B1: B4 će se promeniti.

• za podatke gde je razlika veća od 25%, boja pozadine ćelije će se promeniti u plavo.
• ako je razlika veća od 50% boje boje ćelije će se promeniti u crveno.

Pravila koja su korišćena za ostvarivanje ovog zadatka,

= (A2-B2) / A2> 25% = (A2-B2) / A2> 50%

biće upisani koristeći dijalog okvir za novo oblikovanje pravila u uslovnom formatu.

Unos podataka o tutorijalu

1. Unesite podatke u ćelije A1 do C5 kao što vidite na slici iznad

Napomena: 3. korak vodiča će dodati formule ćelijama C2: C4 koje prikazuju tačnu razliku između vrednosti u ćelijama A2: A5 i B2: B5 kako bi se proverila tačnost pravila usaglašenog oblikovanja.

Podešavanje pravila konačnog oblikovanja

Kao što je pomenuto, pravila uslovnog oblikovanja koja proveravaju dva uslova bit će uneta pomoću dijaloga New Formatting Rule u uslovima formatiranja.

Postavljanje uslovnog oblikovanja da biste pronašli veći od 25% povećanje

1. Označite ćelije B2 do B5 na radnom listu.
2. Kliknite na karticu Home na traci.
3. Kliknite na ikonu Conditional Formatting u traci da biste otvorili padajući meni.
4. Izaberite novo pravilo da biste otvorili dijalog prozor New Ruleting Rule kao što vidite na slici iznad.
5. U gornjoj polovini dijaloga kliknite na zadnju opciju: Koristite formulu koja određuje koje ćelije treba formatirati.
6. U donjoj polovini dijaloga kliknite na vrijednosti Format gdje je ova formula istinita: linija.
7. Unesite formulu : = (A2-B2) / A2> 25% u datom prostoru
8. Kliknite na dugme Format da biste otvorili dijalog Format Cells.
9. U ovom dijalog box-u kliknite na karticu Fill i izaberite boju plave boje.
10. Kliknite OK dva puta da biste zatvorili dijalog i vratili se na radni list.
11. U ovom trenutku, boja pozadine ćelija B3 i B5 bi trebala biti plava.

Postavljanje uslovnog oblikovanja da biste pronašli povećanje od 50%

1. Sa ćelijama B2 do B5 i dalje izabrani, ponovite korake od 1 do 6 iznad.
2. Unesite formulu: = (A2-B2) / A2> 50% u datom prostoru.
3. Kliknite na dugme Format da biste otvorili dijalog Format Cells.
4. Kliknite na karticu Fill i izaberite boju crvene boje.
5. Kliknite OK dva puta da biste zatvorili dijalog i vratili se na radni list .
6. Boja pozadine ćelije B3 i dalje bi bila plava, što ukazuje na to da je procentualna razlika između brojeva u ćelijama A3 i B3 veća od 25%, ali manja ili jednaka 50%.
7. Boja pozadine ćelije B5 bi trebalo da se promeni na crveno ukazujući da je procentualna razlika između brojeva u ćelijama A5 i B5 veća od 50%.

Provera pravila za uslovno oblikovanje

Izračunavanje% razlike

Da bi proverili da li su uneta pravila uslovljenog formiranja, možemo uneti formule u ćelije C2: C5 koji će izračunati tačnu razliku između brojeva u opsegu A2: A5 i B2: B5.

1. Kliknite na ćeliju C2 da biste postali aktivna ćelija.
2. Unesite formulu = (A2-B2) / A2 i pritisnite taster Enter na tastaturi.
3. Odgovor 10% treba da se pojavi u ćeliji C2, što ukazuje da je broj u ćeliji A2 10% veći od broja u ćeliji B2.
4. Možda će biti potrebno promjeniti formatiranje na ćeliji C2 kako bi se odgovor prikazao kao procenat.
5. Koristite ručicu za punjenje da kopirate formulu iz ćelije C2 u ćelije C3 do C5.
6. Odgovori za ćelije C3 do C5 treba da budu: 30%, 25% i 60%.
7. Odgovori u ovim ćelijama pokazuju da su pravila usaglašenog oblikovanja tačna jer je razlika između ćelija A3 i B3 veća od 25%, a razlika između ćelija A5 i B5 je veća od 50%.
8. Ćelija B4 nije promenila boju, jer je razlika između ćelija A4 i B4 jednaka 25%, a naše uslovno pravilo oblikovanja određuje da je procenat veći od 25% potreban za promjenu boje pozadine u plavoj boji.

Red precedence za pravila za uslovno oblikovanje

Primjena pravila sukobljavanja uslovnog oblikovanja

Kada se primeni više pravila za isti opseg podataka, Excel prvo određuje da li su pravila u sukobu.

Pravila koja se suprotstavljaju su ona gde se izabrane opcije oblikovanja za svako pravilo ne mogu primijeniti na iste podatke .

U primjeru koji se koristi u ovom tutorialu, pravila se suprotstavljaju jer oba pravila koriste istu opciju za oblikovanje - promjenu boje pozadine.

U situaciji kada je drugo pravilo tačno (razlika u vrijednosti je veća od 50% između dvije ćelije) tada je prvo pravilo (razlika u vrijednosti veća od 25%) također tačna.

Excelov red precedence

Pošto ćelija ne može imati istovremeno i crvenu i plavu pozadinu, Excel mora znati koje uslovno pravilo oblikovanja treba primijeniti.

Koji se pravilo primenjuje određuje Excelov redosled prvenstva, koji navodi da je pravilo koje je više na listi u dijalogu Upravljač pravilima pravilnog formiranja prioritet.

Kao što je prikazano na gornjoj slici, drugo pravilo korišteno u ovom priručniku (= (A2-B2) / A2> 50%) je veće u listi i stoga ima prednost u odnosu na prvo pravilo.

Kao rezultat, boja pozadine ćelije B5 je promenjena u crvenu boju.

Podrazumevano se nova pravila dodaju na vrh liste i stoga imaju viši prioritet.

Da biste promenili redosled prioriteta, koristite dugmad sa strelicama gore i dolje u dijaloškom okviru kako je navedeno na slici iznad.

Primjena nekonfliktnih pravila

Ako se dva ili više pravila usaglašenog oblikovanja ne sukobljavaju oba, primenjuju se kada stanje svakog pravila postane tačno.

Ako je prvo uslovno pravilo za formatiranje u našem primeru (= (A2-B2) / A2> 25%) formatirao opseg ćelija B2: B5 sa plavom granicom umjesto plave boje pozadine, ova dva uslovna pravila za formatiranje ne bi bila sukobljena od oba formata mogu se primeniti bez ometanja druge.

Kao rezultat, ćelija B5 bi imala i plavu granicu i crvenu boju pozadine, jer je razlika između brojeva u ćelijama A5 i B5 veća od 25 i 50 posto.

Uslovno oblikovanje naspram regularnog oblikovanja

U slučaju konflikta između pravila uslovnog oblikovanja i ručno primenjenih opcija oblikovanja, uslovno pravilo oblikovanja uvek ima prednost i biće primenjeno umesto bilo koje ručno dodate opcije oblikovanja.

Ako je žutu boju boja u početku primijenjena na ćelije B2 do B5 u primjeru, kada su dodana pravila uslovnog oblikovanja, samo ćelije B2 i B4 ostaju žute.

Pošto se unesena pravila uslovnog oblikovanja primjenjuju na ćelije B3 i B5, njihove pozadinske boje bi se promijenile od žute do plave i crvene.