Excel

Pogojno oblikovanje s formulami (10 primerov)

Conditional Formatting With Formulas

Hiter začetek | Primeri | Odpravljanje težav | Usposabljanje

Pogojno oblikovanje je odličen način za hitro vizualizacijo podatkov v preglednici. S pogojnim oblikovanjem lahko v naslednjih 30 dneh naredite nekaj, na primer označite datume, označite težave pri vnosu podatkov, označite vrstice, ki vsebujejo najboljše stranke, prikažete podvojene podatke in drugo.



Excel je opremljen z velikim številom 'prednastavitev', ki olajšajo ustvarjanje novih pravil brez formul. Lahko pa ustvarite pravila tudi z lastnimi formulami po meri. Z uporabo lastne formule prevzamete pogoj, ki sproži pravilo, in lahko uporabite točno tisto logiko, ki jo potrebujete. Formule vam dajejo največjo moč in prilagodljivost.

Na primer, s prednastavitvijo 'Equal to' je enostavno označiti celice, enake 'apple'.





Kaj pa, če želite poudariti celice, enake 'jabolko' ali 'kivi' ali 'limeta'? Seveda lahko za vsako vrednost ustvarite pravilo, vendar je to veliko težav. Namesto tega lahko preprosto uporabite eno pravilo, ki temelji na formuli z ALI funkcija :

Pravilo za označevanje x, y ali z



Tukaj je rezultat pravila, ki se uporablja za obseg B4: F8 v tej preglednici:

Pogojno oblikovanje s funkcijo OR

Tu je uporabljena natančna formula:

 
= OR (B4='apple',B4='kiwi',B4='lime')

Hiter začetek

Pogojno oblikovanje na podlagi formule lahko ustvarite v štirih preprostih korakih:

1. Izberite celice, ki jih želite oblikovati.

Izberite celice za oblikovanje

2. Ustvarite pravilo pogojnega oblikovanja in izberite možnost Formula

Izberite možnost formule

3. Vnesite formulo, ki vrne TRUE ali FALSE.

Vnesite formulo glede na aktivno celico

4. Nastavite možnosti oblikovanja in shranite pravilo.

Nastavite možnosti oblikovanja

The ISODD funkcija vrne TRUE le za lihe številke, kar sproži pravilo:

kako uporabiti povprečno funkcijo v

Funkcija ISODD vrne TRUE za lihe številke, kar sproži pravilo

Video: Kako uporabiti pogojno oblikovanje s formulo

Ponujamo tudi video usposabljanje na to temo .

Logika formule

Formule, ki uporabljajo pogojno oblikovanje, morajo vrniti TRUE ali FALSE ali številske ekvivalente. Tu je nekaj primerov:

kako v Excel vnesti formulo matrike
 
= ISODD (A1) = ISNUMBER (A1) =A1>100 = AND (A1>100,B1<50) = OR (F1='MN',F1='WI')

Vse zgornje formule vračajo TRUE ali FALSE, zato odlično delujejo kot sprožilec pogojnega oblikovanja.

Ko je pogojno oblikovanje uporabljeno za obseg celic, vnesite sklice na celice glede na prvo vrstico in stolpec v izbiri (to je zgornja leva celica). Trik pri razumevanju, kako delujejo formule za pogojno oblikovanje, je vizualizacija iste formule, na katero se nanaša vsako celico v izboru , z referencami celic posodobljene kot običajno. Predstavljajte si, da ste formulo vnesli v zgornjo levo celico izbora, nato pa formulo prekopirali po celotnem izboru. Če se s tem spopadate, glejte razdelek o Lažne formule spodaj.

Primeri formul

Spodaj so primeri formul po meri, ki jih lahko uporabite za uporabo pogojnega oblikovanja. Nekatere od teh primerov je mogoče ustvariti z uporabo vgrajenih Excelovih prednastavitev za označevanje celic, vendar lahko formule po meri presežejo prednastavitve, kot lahko vidite spodaj.

Glej tudi: Več kot 30 formul za pogojno oblikovanje

Označite naročila iz Teksasa

Če želite označiti vrstice, ki predstavljajo naročila iz Teksasa (skrajšano TX), uporabite formulo, ki zaklene sklic na stolpec F:

 
=$F5='TX'

S formulo označite vrstice, kjer je stanje =

Za več podrobnosti glejte ta članek: Označite vrstice s pogojnim oblikovanjem .

Video: Kako označiti vrstice s pogojnim oblikovanjem

Označite datume v naslednjih 30 dneh

Za označevanje datumov v naslednjih 30 dneh potrebujemo formulo, ki (1) zagotavlja, da so datumi v prihodnosti, in (2), da so datumi 30 dni ali manj od danes. Eden od načinov za to je uporaba IN funkcija skupaj z Funkcija ZDAJ Všečkaj to:

 
= AND (B4> NOW (),B4<=( NOW ()+30))

S trenutnim datumom 18. avgusta 2016 pogojno oblikovanje označuje datume na naslednji način:

Pogojno oblikovanje za označevanje datumov v naslednjih 30 dneh

The Funkcija ZDAJ vrne trenutni datum in čas. Za podrobnosti o delovanju te formule si oglejte ta članek: Označite datume v naslednjih N dneh .

Poudarite razlike v stolpcih

Glede na dva stolpca, ki vsebujeta podobne informacije, lahko s pogojnim oblikovanjem odkrijete subtilne razlike. Formula, uporabljena za sprožitev spodnjega oblikovanja, je:

 
=$B4$C4

Pogojno oblikovanje za primerjavo stolpcev

Poglej tudi: različica te formule, ki uporablja funkcijo EXACT za primerjavo med malimi in malimi črkami .

Označite manjkajoče vrednosti

Če želite označiti vrednosti na enem seznamu, ki manjkajo na drugem, lahko uporabite formulo, ki temelji na Funkcija COUNTIF :

 
= COUNTIF (list,B5)=0

Manjkajoče vrednosti označite s pogojnim oblikovanjem

Ta formula preprosto preveri vsako vrednost Seznam A v primerjavi z vrednostmi v imenovanem obsegu 'list' (D5: D10). Ko je štetje nič, formula vrne TRUE in sproži pravilo, ki označuje vrednosti v Seznam A ki manjkajo Seznam B .

Video: Kako poiskati manjkajoče vrednosti z COUNTIF

Označite nepremičnine s 3+ spalnicami pod 350.000 USD

Če želite na tem seznamu najti nepremičnine, ki imajo vsaj 3 spalnice, vendar so manjše od 300.000 USD, lahko uporabite formulo, ki temelji na funkciji AND:

 
= AND ($C5<350000,$D5>=3)

Znaki za dolar ($) zaklenejo sklicevanje na stolpca C in D ter IN funkcija se uporablja za zagotovitev, da sta oba pogoja res. V vrsticah, kjer funkcija AND vrne TRUE, se uporabi pogojno oblikovanje:

Pogojno oblikovanje za označevanje seznamov lastnosti

Označite najvišje vrednosti (dinamični primer)

Čeprav ima Excel prednastavitve za 'najvišje vrednosti', ta primer prikazuje, kako narediti isto s formulo in kako so formule lahko bolj prilagodljive. Z uporabo formule lahko naredimo delovni list interaktiven - ko se vrednost v F2 posodobi, se pravilo takoj odzove in poudari nove vrednosti.

Dinamično pogojno oblikovanje za najvišje vrednosti

Formula, uporabljena za to pravilo, je:

kako narediti časovni list v
 
=B4>= LARGE (data,input)

Kjer so „podatki“ poimenovano območje B4: G11 in „vnos“ je poimenovano območje F2. Ta stran ima podrobnosti in popolno razlago .

Ganttovi grafikoni

Če verjamete ali ne, lahko celo uporabite formule za ustvarjanje preprostih Gantovih grafikonov s pogojnim oblikovanjem, kot je ta:

Za ustvarjanje Ganttovega grafikona uporabite pogojno oblikovanje

Ta delovni list uporablja dva pravila, eno za palice in eno za senčenje za konec tedna:

 
= AND (D>=$B5,D<=$C5) // bars = WEEKDAY (D,2)>5 // weekends

Ta članek pojasnjuje formulo za palice , in ta članek pojasnjuje formulo za senčenje ob koncu tedna .

Enostavno iskalno polje

Eden kul trikov, ki jih lahko naredite s pogojnim oblikovanjem, je sestaviti preprosto polje za iskanje. V tem primeru pravilo poudarja celice v stolpcu B, ki vsebujejo besedilo, vneseno v celico F2:

Iskalno polje za pogojno oblikovanje

Uporabljena formula je:

 
= ISNUMBER ( SEARCH ($F,B2))

Za več podrobnosti in popolno razlago glej:

Odpravljanje težav

Če ne morete pravilno sprožiti pravil pogojnega oblikovanja, je najverjetneje težava v formuli. Najprej se prepričajte, da ste začeli formulo z znakom enakosti (=). Če pozabite na ta korak, bo Excel tiho pretvoril celotno formulo v besedilo, zaradi česar bo neuporabna. Če želite popraviti, odstranite dvojne narekovaje, ki jih je Excel dodal na obeh straneh, in se prepričajte, da se formula začne z enakim (=).

Če je vaša formula pravilno vnesena, vendar ne sproži pravila, boste morda morali kopati nekoliko globlje. Običajno, lahko s tipko F9 preverite rezultate v formuli ali uporabite funkcijo Oceni za prehod skozi formulo. Na žalost teh orodij ne morete uporabljati s formulami za pogojno oblikovanje, lahko pa uporabite tehniko, imenovano 'lažne formule'.

Lažne formule

Lažne formule so način, da preizkusite formule za pogojno oblikovanje neposredno na delovnem listu, tako da lahko vidite, kaj dejansko počnejo. To lahko prihrani veliko časa, ko se trudite, da bi reference celic delovale pravilno.

Na kratko vnesite isto formulo v vrsto celic, ki ustreza obliki vaših podatkov. Tako si lahko ogledate vrednosti, ki jih vrne vsaka formula, in to je odličen način za vizualizacijo in razumevanje delovanja pogojnega oblikovanja na osnovi formul. Za podrobno razlago, glej ta članek .

Za preverjanje formul za pogojno oblikovanje uporabite lažne formule

Video: Preizkusite pogojno oblikovanje z lažnimi formulami

Omejitve

Pri pogojnem oblikovanju na osnovi formule obstajajo nekatere omejitve:

  1. Ikone, barvne lestvice ali podatkovne vrstice ne morete uporabiti s formulo po meri. Omejeni ste na standardno oblikovanje celic, vključno z oblikami številk, pisavo, barvo polnila in obrobami.
  2. Za merila pogojnega oblikovanja ne morete uporabiti določenih konstrukcij formule, kot so zveze, križišča ali konstante matrike.
  3. Na druge delovne zvezke se ne morete sklicevati v formuli za pogojno oblikovanje.

Včasih lahko delate okoli #2 in #3. Morda boste lahko logiko formule premaknili v celico na delovnem listu, nato pa se namesto tega obrnite na to celico v formuli. Če poskušate uporabiti konstanto matrike, raje ustvarite imenovani obseg.

Več virov formule CF

  • Več kot 30 primerov formul za pogojno oblikovanje
  • Video usposabljanje z delovnimi listi
Avtor Dave Bruns


^