Formule pogojnih formul Excel

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

Ker Excel 2007 ima Excel š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.

Uporaba več pravil

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

Primer: iskanje podatkov, ki presega 25% in 50%, se povečajo s pogojnim oblikovanjem

V naslednjem primeru se bosta na obseg celic B2 do B5 uporabljali dve pogojni pogojni obliki oblikovanja.

• prvo pravilo preveri, ali so podatki v celicah A2 do A5 večji od ustrezne vrednosti v B2 do 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, če je izpolnjen kateri od zgornjih pogojev, se spremeni barva ozadja celice ali celic v območju B1: B4.

• za podatke, pri katerih je razlika večja od 25%, se bo barva ozadja celice spremenila v modro.
• če je razlika večja od 50%, se bo barva ozadja celice spremenila v rdeče barve.

Pravila, uporabljena za izpolnitev te naloge,

bodo vnesene s pogovornim oknom Pogojno oblikovanje pogovornega okna Novo oblikovanje pravilnika .

Vnos podatkov o vadnicah

1. Vnesite podatke v celice od A1 do C5, kot je prikazano na zgornji sliki

Opomba: 3. korak vadbe bo v celice C2: C4 dodal formule, ki bodo prikazali točno razliko med vrednostmi v celicah A2: A5 in B2: B5, da bi preverili točnost pravil pogojnega oblikovanja.

Nastavitev pravil Kondicionalno oblikovanje

Kot smo že omenili, bodo pogojna pravila oblikovanja, ki preverjajo oba pogoja, vnesena s pogovorno okno pogojnega oblikovanja pogovornega okna New Formatting Rule.

Nastavite pogojno oblikovanje, da najdete več kot 25% povečanje

1. Označite celice B2 do B5 v delovnem listu.
2. Kliknite zavihek Domov na traku.
3. V traku kliknite ikono pogojnega oblikovanja, da odprete spustni meni.
4. Izberite Novo pravilo, da odprete pogovorno okno Novo oblikovanje pravil, kot je prikazano na zgornji sliki.
5. V zgornji polovici pogovornega okna kliknite na zadnjo možnost: s formulo določite, katere celice želite formatirati.
6. V spodnji polovici pogovornega okna kliknite v vrednosti Oblika, kjer je ta formula resnična: vrstica.
7. Vnesite formulo : = (A2-B2) / A2> 25% v zagotovljenem prostoru
8. Kliknite gumb Oblika, da odprete pogovorno okno Format Cells.
9. V tem pogovornem oknu kliknite zavihek Polnjenje in izberite barvo modre barve.
10. Dvakrat kliknite OK, da zaprete pogovorna okna in se vrnete na delovni list.
11. Na tej točki mora biti barva ozadja celic B3 in B5 modra.

Nastavitev pogojnega oblikovanja, da bi našli več kot 50% povečanje

1. Če so celice B2 do B5 še vedno izbrane, ponovite korake od 1 do 6 zgoraj.
2. Vnesite formulo: = (A2-B2) / A2> 50% v zagotovljenem prostoru.
3. Kliknite gumb Oblika, da odprete pogovorno okno Format Cells.
4. Kliknite kartico Polnjenje in izberite barvo rdečega polnila.
5. Dvakrat kliknite OK, da zaprete pogovorna okna in se vrnete na delovni list .
6. Barva ozadja celice B3 mora biti še vedno modra, kar kaže, da je odstotna razlika med številkami v celicah A3 in B3 večja od 25%, vendar manjša ali enaka 50%.
7. Barva ozadja celice B5 bi morala biti rdeča, kar pomeni, da je odstotna razlika med številkami v celicah A5 in B5 večja od 50%.

Preverjanje pravil za pogojno oblikovanje

Izračun% Razlika

Za preverjanje pravilnosti pravilnih pogojnih pravil oblikovanja lahko formule vnesemo v celice C2: C5, ki bodo izračunale točno razliko med številom v obsegu A2: A5 in B2: B5.

1. Kliknite celico C2, da postanete aktivna celica.
2. Vnesite formulo = (A2-B2) / A2 in pritisnite tipko Enter na tipkovnici.
3. Odgovor bi moral biti 10% v celici C2, kar pomeni, da je število v celici A2 10% večje od števila v celici B2.
4. Morda bo treba spremeniti oblikovanje v celici C2, da se prikaže odgovor kot odstotek.
5. Uporabite ročico za polnjenje, da kopirate formulo iz celice C2 v celice C3 do C5.
6. Odgovori za celice C3 do C5 morajo biti: 30%, 25% in 60%.
7. Odgovori v teh celicah kažejo, da so pravila pogojnega oblikovanja pravilna, saj je razlika med celicami A3 in B3 večja od 25%, razlika med celicami A5 in B5 pa je večja od 50%.
8. Celica B4 ni spremenila barve, ker je razlika med celicami A4 in B4 enaka 25% in pravilo pogojnega oblikovanja je določalo, da je barva ozadja spremenjena v modro za odstotek, ki je večji od 25%.

Vrstni red prednosti za pravila za pogojno oblikovanje

Uporaba pravil o konfliktnih pogojnih oblikah

Č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 resnično (razlika v vrednosti je večja od 50% med dvema celicama), je prav tako prvo pravilo (razlika v vrednosti več kot 25%).

Excelov red predhodnosti

Ker celica ne more imeti obeh rdečih in modrih ozadij 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 vadnici (= (A2-B2) / A2> 50%), večje na seznamu 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 prednosti, uporabite smerne puščice gor in dol 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 (= (A2-B2) / A2> 25%) formatiralo obseg celic B2: B5 z modro obrobo namesto barve modre barve, obe pogojni obliki oblikovanja ne bi nasprotovali, saj obe obliki lahko uporabite, ne da bi se vmešavali z drugim.

Kot rezultat, bi celica B5 imela modro mejo in rdečo barvo ozadja, saj je razlika med številkami v celicah A5 in B5 večja od 25 in 50 odstotkov.

Pogojno oblikovanje v primerjavi z običajnim oblikovanjem

V primeru navzkrižja med pogojnimi predpisi o oblikovanju 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 rumena barva ozadja prvotno uporabljena na celicah B2 do B5 v primeru, ko so bila dodana pravila pogojnega oblikovanja, bi bile samo celice B2 in B4 ostale rumene barve.

Ker so vnesena pogojna pravila oblikovanja veljajo za celice B3 in B5, njihove barve ozadja bi se spremenile iz rumene v modro ali rdeče.