Če dodate pogojno oblikovanje v Excelu, lahko uporabite različne možnosti oblikovanja v celico ali obseg celic, ki ustrezajo določenim pogojem, ki ste jih nastavili. Možnosti oblikovanja se uporabljajo samo, če izbrane celice izpolnjujejo te nastavljene pogoje.
Možnosti oblikovanja, ki jih je mogoče uporabiti, vključujejo spreminjanje barv pisave in ozadja, sloge pisave, robove celic in dodajanje številk v podatke.
Excel ima na voljo številne vgrajene možnosti za pogosto uporabljene pogoje, kot so iskanje številk, ki so večje ali manjše od določene vrednosti ali iskanje številk, ki so nad ali pod povprečno vrednostjo.
Poleg teh vnaprej določenih možnosti je tudi mogoče ustvariti pravila za pogojno oblikovanje s pomočjo formul Excel za preskušanje pogojev, ki jih določi uporabnik.
01 od 04Uporaba več pravil v Excelu
Za iste podatke lahko uporabite več kot eno pravilo za testiranje različnih pogojev. Na primer, proračunski podatki imajo lahko določene pogoje, ki uporabljajo spremembe oblikovanja, kadar se porabijo določene ravni, kot so 50%, 75% in 100% skupnega proračuna.
V takih okoliščinah Excel najprej ugotovi, ali so različna pravila v navzkrižju, in če je tako, program sledi določenemu prednostnemu vrstnemu redu, s katerim določi, katero pravilo pogojnega oblikovanja se uporablja za podatke.
Iskanje podatkov, ki presega 25% in 50% poveča
V naslednjem primeru se dve različni meri za pogojno oblikovanje uporabljajo za obseg celice B2 do B5.
- Prvo pravilo preveri, ali podatki v celice A2 do A5 je večja od ustrezne vrednosti v B2 do B5 za več kot 25%.
- Drugo pravilo preveri, ali so isti podatki v A2: A5 presega ustrezno vrednost v B2: B5 za več kot 50%.
Kot je razvidno iz zgornje slike, če je kateri od zgoraj navedenih pogojev resnična, je barva ozadja celice ali celic v dosegu B1: B4 se bo spremenil.
- Za podatke, pri katerih je razlika večja od 25%, se bo barva ozadja celice spremenila v zeleno.
- Če je razlika večja od 50%, se bo barva ozadja celice spremenila v rdeče barve.
Pravila, uporabljena za izpolnjevanje te naloge, bodo vnesena s pogojnim oblikovanjem Novo oblikovanje pravila pogovorno okno. Začnite tako, da vnesete podatke vzorca v celice A1 do C5, kot je prikazano na zgornji sliki
V Ljubljani tretji korak vadnice bodo dodajale formule celicam C2: C4 ki kažejo točno razliko med vrednostmi v celicah A2: A5 in B2: B5 da bi preverili točnost pravil pogojnega oblikovanja.
02 od 04Nastavljanje pravil za pogojno oblikovanje
Kot smo že omenili, bodo pogojna pravila oblikovanja, ki preverjajo oba pogoja, vnesena s pogojnim oblikovanjem Novo oblikovanje pravila pogovorno okno. Začnimo z nastavitvijo pogojnega oblikovanja po meri:
Pogojno oblikovanje, da bi našli več kot 25 odstotkov povečanja
= (A2-B2) / A2> 25%
- Označite celice B2 do B5 v delovnem listu.
- Kliknite na Zavihek Domov od trak.
- Kliknite na Pogojno oblikovanje ikona v trak da odprete spustni seznam.
- Izberite Novo pravilo da odprete Novo oblikovanje pravila pogovorno okno.
- Iz Slog: polje, izberite Classic možnost.
- V drugem spustnem meniju kliknite na zadnjo možnostUporabite formulo, s katero določite, katere celice želite formatirati.
- Vnesite formula zagotovljeno zgoraj v predvidenem prostoru.
- Kliknite na Oblikujte z: spustite, nato izberite Svetlo rdeča Nalepka s temno rdečim besedilom možnost.
- Kliknite v redu da zaprete pogovorna okna in se vrnete na delovni list.
- Na tej točki je barva ozadja celice B3 in B5 bi morala biti zelena.
Pogojno oblikovanje najti več kot 50 odstotkov povečanje
= (A2-B2) / A2> 50%
Označite celice B2 do B5 v delovnem listu.
- Kliknite na Zavihek Domov od trak.
- Kliknite na Pogojno oblikovanje ikona v trak da odprete spustni seznam.
- Izberite Novo pravilo da odprete Novo oblikovanje pravila pogovorno okno.
- Iz Slog: polje, izberite Classic možnost.
- V drugem spustnem meniju kliknite na zadnjo možnostUporabite formulo, s katero določite, katere celice želite formatirati.
- Vnesite formula zagotovljeno zgoraj v predvidenem prostoru.
- Kliknite na Oblikujte z: spustite, nato izberite Svetlo zeleno izpolnite s temno zelenim besedilom možnost.
- Kliknite v redu da zaprete pogovorna okna in se vrnete na delovni list.
Barva ozadja celica B3 bi morala biti še vedno zelena, kar pomeni, da je odstotek razlike med številkami v celice A3 in B3 je večja od 25 odstotkov, vendar manjša ali enaka 50 odstotkom. Barva ozadja celica B5 se mora spremeniti na rdeče, kar pomeni, da je odstotek razlike med številkami v celice A5 in B5 je več kot 50 odstotkov.
03 od 04Preverjanje pravil za pogojno oblikovanje
Če želite preveriti pravilnost vnešenih pravil pogojnega oblikovanja, lahko formule vnesemo v celice C2: C5 ki bo izračunal točno razliko med številom v obsegu A2: A5 in B2: B5.
= (A2-B2) / A2
- Kliknite na celica C2 da postane aktivna celica.
- Vnesite zgornjo formulo in pritisnite Vnesite tipko na tipkovnici.
- Odgovor bi moral biti 10% celica C2, kar pomeni, da je številka v celica A2 je za 10% večji od števila v celica B2.
- Morda bo treba spremeniti formatiranje celica C2 da bi odgovor prikazal kot odstotek.
- Uporabi napolniti ročaj za kopiranje formule iz celica C2 do celice C3 do C5.
- Odgovori za celice C3 do C5 mora biti 30%, 25% in 60%.
Odgovori v teh celicah kažejo, da so pravila pogojnega oblikovanja pravilna, saj je razlika med celice A3 in B3 je večja od 25 odstotkov in razlika med celice A5 in B5 je več kot 50 odstotkov.
Celica B4 ni spremenila barve, ker je razlika med celice A4 in B4 je enak 25 odstotkom, pravilo pogojnega oblikovanja pa je določalo, da je odstotek več kot 25 odstotkov potreben za spreminjanje barve ozadja v zeleno.
04 od 04Vrstni red prednosti za pogojno oblikovanje
Če se za isto vrsto podatkov uporablja več pravil, Excel najprej ugotovi, ali so pravila v nasprotju. Pravila, ki se sprožajo, so tista, v katerih se izbrane možnosti oblikovanja za vsako pravilo ne morejo uporabiti za iste podatke.
V primeru, ki se uporablja v tej vadnici, se pravila ne strmijo, ker obe pravili uporabljata enako možnost oblikovanja - spreminjanje barve ozadja celice.
V primeru, ko je drugo pravilo res (razlika v vrednosti je večja od 50 odstotkov med dvema celicama), je prav tako prvo pravilo (razlika v vrednosti več kot 25 odstotkov).
Excelov red predhodnosti
Ker celica ne more imeti hkrati rdečega in zelenega ozadja hkrati, mora Excel vedeti, za katero pogojno pravilo oblikovanja naj velja.
Katero pravilo se uporabi, se določi s prednostnim vrstnim redom Excela, ki navaja, da je prednostno pravilo, ki je večje na seznamu pogovornega okna pravilnika pogojnega oblikovanja.
Kot je prikazano na zgornji sliki, je drugo pravilo, uporabljeno v tej vajah, na seznamu višje in ima prednost pred prvim pravilom. Zato je barva ozadja celice B5 spremenjena v rdeče barve.
Privzeto so na vrhu seznama dodana nova pravila in zato imajo višjo prednost. Če želite spremeniti vrstni red prednost, uporabite Gor in dol puščične gumbe v pogovornem oknu, kot je navedeno na zgornji sliki.
Uporaba nekonsistentnih pravil
Če dve ali več pravil pogojnega oblikovanja nista v navzkrižju oba, se uporabi, če pogoj vsakega pravila testa postane resničen.
Če je prvo pogojno pravilo za oblikovanje v našem primeru formatiralo obseg celic B2: B5 z zeleno roko namesto zelene barve ozadja, oba pogojna pravila za oblikovanje ne bi bila v nasprotju, ker se obe obliki lahko uporabita, ne da bi se vmešali v drugega.
Kot rezultat, celica B5 bi imela tako zeleno mejo kot rdečo barvo ozadja, saj je razlika med številkami v celice A5 in B5 je večja od 25 in 50 odstotkov.
Pogojno oblikovanje v primerjavi z običajnim oblikovanjem
V primeru navzkrižja med pogojnimi pravili oblikovanja in ročnimi možnostmi oblikovanja, ima pogojno pravilo za oblikovanje vedno prednost in se bo uporabilo namesto ročno dodanih možnosti oblikovanja.
Če je bila na začetku uporabljena rumena barva ozadja celice B2 do B5 v primeru, ko so bila dodana pogojna pravila za oblikovanje, samo celice B2 in B4 ostala rumena. Ker vnesena pravila pogojnega oblikovanja veljajo za celice B3 in B5, bi se barve ozadja spremenile iz rumene v zeleno in rdeče.
