Kvalitetne formule formata za 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.

Od programa Excel 2007, Excel je imao nekoliko ugrađenih opcija 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.

Primjena više pravila

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 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.

Primjer: pronalaženje podataka koji premašuju 25% i 50% povećanja uvjetnog oblikovanja

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

• prvo pravilo provjerava je li podatak u ćelijama A2 do A5 veći od odgovarajuće vrijednosti u B2 do 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 gore navedenih uvjeta istinit, boja pozadine stanica ili stanica u rasponu B1: B4 će se promijeniti.

• za podatke gdje je razlika veća od 25%, boja stanične pozadine će se promijeniti u plavo.
• ako je razlika veća od 50%, pozadinska boja staništa će se promijeniti u crvenu boju.

Pravila korištena za ostvarenje ovog zadatka,

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

će se unijeti pomoću uvjetnog oblikovanja dijaloškog okvira Novi oblik oblikovanja .

Unos podataka o vodiču

1. Unesite podatke u ćelije A1 do C5 kao što se vidi na gornjoj slici

Napomena: Korak 3 udžbenika dodati će formule stanicama C2: C4 koje pokazuju točnu razinu postotka između vrijednosti u stanicama A2: A5 i B2: B5 kako bi se provjerila točnost uvjetovanih pravila oblikovanja.

Postavljanje pravila Condtional formatting

Kao što je spomenuto, pravila uvjetnog oblikovanja koje provjeravaju dva uvjeta bit će unesena pomoću dijaloškog okvira za uvjetno oblikovanje novog pravila oblikovanja.

Postavljanje uvjetnog oblikovanja da biste pronašli više od 25% povećanja

1. Istaknite stanice B2 do B5 u radnom listu.
2. Kliknite karticu Početna vrpce.
3. Kliknite ikonu uvjetnog oblikovanja na vrpci da biste otvorili padajući izbornik.
4. Odaberite novo pravilo da biste otvorili dijaloški okvir Pravilo novog oblikovanja kako se vidi na gornjoj slici.
5. U gornjoj polovici dijaloškog okvira kliknite zadnju opciju: Koristite formulu za određivanje stanica koje treba formatirati.
6. U donjoj polovici dijaloškog okvira kliknite Format vrijednosti u kojima je ova formula istinita: redak.
7. Upišite sljedeću formulu : = (A2-B2) / A2> 25%
8. Kliknite gumb Format za otvaranje dijaloškog okvira Oblikovanje ćelija.
9. U ovom dijaloškom okviru kliknite karticu Ispuni i odaberite plavu boju za punjenje.
10. Dvaput kliknite U redu da biste zatvorili dijaloške okvire i vratili se na radni list.
11. U ovom trenutku, pozadinska boja ćelija B3 i B5 bi trebala biti plava.

Postavljanje uvjetnog oblikovanja da biste pronašli više od 50% povećanja

1. S odabranim ćelijama B2 do B5, ponovite korake od 1 do 6 gore.
2. Upišite sljedeću formulu: = (A2-B2) / A2> 50%.
3. Kliknite gumb Format za otvaranje dijaloškog okvira Oblikovanje ćelija.
4. Kliknite karticu Ispuni i odaberite crvenu boju za punjenje.
5. Dvaput kliknite U redu da biste zatvorili dijaloške okvire i vratili se na radni list .
6. Boja pozadine ćelije B3 bi i dalje trebala biti plava, što znači da je postotna razlika između brojeva u ćelijama A3 i B3 veća od 25%, ali manja ili jednaka 50%.
7. Boja pozadine ćelije B5 treba promijeniti na crvenu boju, što znači da je postotna razlika između brojeva u ćelijama A5 i B5 veća od 50%.

Provjera uvjetnih pravila oblikovanja

Izračunavanje% razlike

Da bismo provjerili jesu li pravilno uneseni uvjeti za uvjetno oblikovanje, možemo unijeti formule u ćelije C2: C5 koje će izračunati točnu razinu postotka između brojeva u rasponima A2: A5 i B2: B5.

1. Kliknite na ćeliju C2 da biste ga učinili aktivnom ćelijom.
2. Upišite formulu = (A2-B2) / A2 i pritisnite tipku Enter na tipkovnici.
3. U ćeliji C2 trebao bi biti odgovor 10%, što znači da je broj u ćeliji A2 10% veći od broja u ćeliji B2.
4. Možda je potrebno promijeniti oblikovanje na ćeliji C2 da bi se odgovor prikazao kao postotak.
5. Koristite ručicu za punjenje da biste kopirali formulu iz ćelije C2 na ćelije C3 do C5.
6. Odgovori za stanice C3 do C5 trebali bi biti: 30%, 25% i 60%.
7. Odgovori u tim stanicama pokazuju da su stvorena pravila uvjetovanog oblikovanja ispravna jer je razlika između stanica A3 i B3 veća od 25%, a razlika između stanica A5 i B5 veća je od 50%.
8. Stanični B4 nije promijenio boju jer razlika između ćelija A4 i B4 iznosi 25%, a uvjetovano pravilo oblikovanja navodi da je postotak veći od 25% potreban za boju pozadine da se promijeni u plavo.

Redoslijed prioriteta za pravila uvjetnog oblikovanja

Primjena pravila o sukobljenim uvjetnim oblikovanjem

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% između dvije ćelije), tada vrijedi i prvo pravilo (razlika vrijednosti veća od 25%).

Excelov redoslijed prednosti

Budući da stanica ne može istodobno imati crvenu i plavu pozadinu, Excel treba znati koje uvjetno pravilo 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 korišteno u ovom udžbeniku (= (A2-B2) / A2> 50%) već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.

Da biste promijenili redoslijed prednosti koristite tipke sa strelicama Gore i Dolje 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 pravilno uvjetno oblikovanje u našem primjeru (= (A2-B2) / A2> 25%) oblikovao raspon ćelija B2: B5 s plavom granicom umjesto plave boja pozadine, dva pravila uvjetnog oblikovanja ne bi bila sukobljena oba formata mogu se primijeniti bez ometanja drugog.

Kao rezultat toga, stanica B5 bi imala oboje plavu granicu i crvenu boju pozadine, budući da je razlika između brojeva u ćelijama A5 i B5 veća 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 je u primjeru primijenjena žuta boja pozadine na stanice B2 do B5, nakon što su dodani uvjetni oblikovni pravilnici, samo bi stanice B2 i B4 ostale žute.

Budući da se primjenjuju uvjeti za uvjetno oblikovanje primjenjuju se na stanice B3 i B5, njihove pozadinske boje mijenjale bi se od žutog do plave i crvene.