Dodajanje pogojnega oblikovanja v Excelu vam omogoča, da za celico ali obseg celic uporabite različne možnosti oblikovanja, ki ustrezajo določenim pogojem, ki ste jih nastavili. Nastavitev takšnih pogojev lahko pomaga organizirati vašo preglednico in olajša skeniranje. Možnosti oblikovanja, ki jih lahko uporabite, vključujejo spremembe barve pisave in ozadja, sloge pisave, obrobe celic in dodajanje oblikovanja številk v podatke.
Excel ima vgrajene možnosti za pogosto uporabljene pogoje, kot je iskanje števil, ki so večja ali manjša od določene vrednosti, ali iskanje števil, ki so nad ali pod povprečno vrednostjo. Poleg teh vnaprej nastavljenih možnosti lahko ustvarite tudi pravila pogojnega oblikovanja po meri z uporabo Excelovih formul.
Ta navodila veljajo za Excel 2019, 2016, 2013, 2010 in Excel za Microsoft 365.
Uporaba več pogojev v Excelu
Za iste podatke lahko uporabite več kot eno pravilo, da preizkusite različne pogoje. Podatki o proračunu imajo lahko na primer nastavljene pogoje, ki uveljavijo spremembe oblikovanja, ko so dosežene določene ravni porabe, na primer 50 %, 75 % in 100 % celotnega proračuna.
V takšnih okoliščinah Excel najprej ugotovi, ali so različna pravila v nasprotju, in če je tako, program sledi nastavljenemu prednostnemu vrstnemu redu, da določi, katero pravilo pogojnega oblikovanja naj uporabi za podatke.
Iskanje podatkov, ki presegajo 25 % in 50 % povečanja
V naslednjem primeru bosta dve pravili pogojnega oblikovanja po meri uporabljeni za obseg celic B2 do B5.
- Prvo pravilo preveri, ali so podatki v celicah A2:A5 večji od ustrezne vrednosti v B2:B5 za več kot 25%.
- Drugo pravilo preveri, ali isti podatki v A2:A5 presegajo ustrezno vrednost v B2:B5 za več kot 50%.
Kot je razvidno iz zgornje slike, se bo barva ozadja celice ali celic v obsegu B1:B4 spremenila, če je eden od zgornjih pogojev resničen.
- Za podatke, kjer je razlika več kot 25 %, se bo barva ozadja celice spremenila v zeleno.
- Če je razlika večja od 50 %, se bo barva ozadja celice spremenila v rdečo.
Pravila, uporabljena za izvedbo te naloge, bodo vnesena s pogovornim oknom Novo pravilo oblikovanja. Začnite z vnosom vzorčnih podatkov v celice A1 do C5, kot je prikazano na zgornji sliki.
V zadnjem delu vadnice bomo dodali formule v celice C2:C4, ki prikazujejo natančno odstotno razliko med vrednostmi v celicah A2:A5 in B2:B5; to nam bo omogočilo, da preverimo točnost pravil pogojnega oblikovanja.
Nastavitev pravil pogojnega oblikovanja
Najprej bomo uporabili pogojno oblikovanje, da bi našli 25-odstotno ali več znatno povečanje.
Funkcija bo videti takole:
=(A2-B2)/A2>25%
- Označite celice B2 do B5 na delovnem listu.
- Kliknite zavihek Domov na traku.
- Kliknite ikono Pogojno oblikovanje na traku, da odprete spustni meni.
- Izberite Novo pravilo, da odprete pogovorno okno Novo pravilo oblikovanja.
-
Pod Izberite vrsto pravila kliknite zadnjo možnost: Uporabite formulo, da določite, katere celice želite oblikovati.
- Vnesite zgoraj navedeno formulo v spodnji prostor Oblikuj vrednosti, kjer je ta formula resnična:
- Kliknite gumb Format, da odprete pogovorno okno. Kliknite zavihek Fill in izberite barvo.
- Kliknite OK, da zaprete pogovorna okna in se vrnete na delovni list.
- Barva ozadja celic B3 in B5 se mora spremeniti v barvo, ki ste jo izbrali.
Zdaj bomo uporabili pogojno oblikovanje za iskanje 50-odstotnega ali večjega povečanja. Formula bo videti takole:
- Ponovite prvih pet zgornjih korakov.
- Vnesite zgoraj navedeno formulo v spodnji prostor Oblikuj vrednosti, kjer je ta formula resnična:
- Kliknite gumb Format, da odprete pogovorno okno. Kliknite zavihek Fill in izberite drugo barvo, kot ste jo naredili v prejšnjem nizu korakov.
- Kliknite OK, da zaprete pogovorna okna in se vrnete na delovni list.
Barva ozadja celice B3 mora ostati enaka, kar kaže na odstotno razliko med številkami v celicah A3 inB3 je večji od 25 odstotkov, vendar manjši ali enak 50 odstotkom. Barva ozadja celice B5 bi se morala spremeniti v novo barvo, ki ste jo izbrali, kar kaže na odstotno razliko med številkami v celicah A5 in B5 je večji od 50 odstotkov.
Preverjanje pravil pogojnega oblikovanja
Da preverimo, ali so vnesena pravila pogojnega oblikovanja pravilna, lahko vnesemo formule v celice C2:C5, ki bodo izračunale natančno odstotno razliko med številkami v obsegihA2:A5 in B2:B5.
Formula v celici C2 izgleda takole:
=(A2-B2)/A2
- Kliknite celica C2, da postane aktivna celica.
- Vnesite zgornjo formulo in pritisnite tipko Enter na tipkovnici.
- Odgovor 10 % bi se moral pojaviti v celici C2, kar pomeni, da je število v celici A2 10 % večje od števila v celica B2.
- Morda bo treba spremeniti oblikovanje v celici C2 za prikaz odgovora v odstotkih.
- Uporabite ročico za polnjenje za kopiranje formule iz celice C2 v celice C3 v C5.
- Odgovori za celice C3 do C5 bi morali biti 30%, 25% in 60%.
Odgovori v teh celicah kažejo, da so pravila pogojnega oblikovanja točna, saj je razlika med celicami A3 in B3 večja od 25 odstotkov, razlika med celicami A5 in B5 pa je večja od 50 odstotkov.
Celica B4 ni spremenila barve, ker je razlika med celicami A4 in B4 enaka 25 odstotkov, naše pravilo pogojnega oblikovanja pa je določalo, da je za spremembo barve ozadja potreben odstotek, večji od 25 odstotkov.
Prednostni vrstni red za pogojno oblikovanje
Ko uporabite več pravil za isti obseg podatkov, Excel najprej ugotovi, ali so pravila v nasprotju. Pravila v nasprotju so tista, pri katerih možnosti oblikovanja ni mogoče uporabiti za iste podatke.
V našem primeru so pravila v nasprotju, saj obe uporabljata isto možnost oblikovanja – spreminjanje barve ozadja celice.
V primeru, da je drugo pravilo resnično (razlika v vrednosti je več kot 50 odstotkov med dvema celicama), potem je prvo pravilo (razlika v vrednosti večja od 25 odstotkov) tudi resnično.
Ker celica ne more imeti dveh različnih barvnih ozadij hkrati, mora Excel vedeti, katero pravilo pogojnega oblikovanja naj uporabi.
V vrstnem redu Excela je navedeno, da se najprej uporabi pravilo, ki je višje na seznamu v pogovornem oknu Upravitelj pravil pogojnega oblikovanja.
Kot je prikazano na zgornji sliki, je drugo pravilo, uporabljeno v tej vadnici, višje na seznamu in ima zato prednost pred prvim pravilom. Posledično je barva ozadja celice B5 zelena.
Privzeto so nova pravila na vrhu seznama; če želite spremeniti vrstni red, uporabite puščične gumbe gor in dol v pogovornem oknu.
Uporaba nenasprotujočih si pravil
Če dve ali več pravil pogojnega oblikovanja ni v nasprotju, se obe uporabita, ko pogoj, ki ga preizkuša vsako pravilo, postane resničen.
Če je prvo pravilo pogojnega oblikovanja v našem primeru oblikovalo obseg celic B2:B5 z oranžno obrobo namesto z oranžno barvo ozadja, dve pravili pogojnega oblikovanja ne bi navzkriž, saj je mogoče obe obliki uporabiti, ne da bi motili drugo.
Pogojno oblikovanje v primerjavi z običajnim oblikovanjem
V primeru sporov med pravili pogojnega oblikovanja in ročno uporabljenimi možnostmi oblikovanja ima pravilo pogojnega oblikovanja vedno prednost in bo uporabljeno namesto vseh ročno dodanih možnosti oblikovanja.
