Bruke formler for betinget formatering i Excel

Hvis du legger til betinget formatering i Excel, kan du bruke forskjellige formateringsalternativer til en celle eller en rekke celler som oppfyller spesifikke forhold som du angir. Formateringsalternativene blir bare brukt når de valgte cellene oppfyller disse betingelsene.

Formateringsalternativene som kan brukes, inkluderer skrifttype- og bakgrunnsfargeendringer, skrifttypestiler, cellegrenser og legge til tallformatering til data.

Excel har hatt en rekke innebygde alternativer for vanlige forhold, for eksempel å finne tall som er større enn eller mindre enn en bestemt verdi eller finne tall som er over eller under gjennomsnittlig verdi.

I tillegg til disse forhåndsinnstilte alternativene, er det også mulig å lage egendefinerte betingede formateringsregler ved hjelp av Excel-formler for å teste for brukerdefinerte forhold.

01 av 04

Bruke flere regler i Excel

Mer enn én regel kan brukes på de samme dataene for å teste for forskjellige forhold. Budsjettdata kan for eksempel ha betingelser som gjelder for formatering endres når bestemte nivåer, for eksempel 50%, 75% og 100% av det totale budsjettet blir brukt.

Under slike omstendigheter bestemmer Excel først om de ulike reglene er i konflikt, og i så fall følger programmet en forhåndsordre for å bestemme hvilken betinget formateringsregel som brukes på dataene.

Finne data som overstiger 25% og 50% øker

I følgende eksempel vil to tilpassede betingede formateringsregler bli brukt til rekkevidden av celler B2 til B5.

• Den første regelen kontrollerer for å se om dataene i celler A2 til A5 er større enn tilsvarende verdi i B2 til B5 med mer enn 25%.
• Den andre regelen kontrollerer for å se om de samme dataene i A2: A5 overstiger tilsvarende verdi i B2: B5 med mer enn 50%.

Som det fremgår av bildet ovenfor, hvis en av de ovennevnte forholdene er sant, er bakgrunnsfargen til cellen eller cellene i området B1: B4 Kommer til å endres.

• For data der forskjellen er større enn 25%, endres cellebakgrunnsfargen til grønt.
• Hvis forskjellen er større enn 50%, endres cellebakgrunnsfargen til rød.

Reglene som ble brukt for å utføre denne oppgaven, blir oppgitt ved bruk av betinget formatering Ny formateringsregel dialogboks. Begynn med å skrive inn eksempeldataene i celler A1 til C5 som vist i bildet ovenfor

I tredje trinn av opplæringen vil legge til formler til celler C2: C4 som viser den eksakte prosentforskjellen mellom verdiene i celler A2: A5 og B2: B5 for å kontrollere nøyaktigheten av de betingede formateringsreglene.

02 av 04

Innstilling av betingede formateringsregler

Som nevnt, vil de betingede formatteringsreglene som kontrollerer de to betingelsene, skrives inn ved hjelp av betinget formatering Ny formateringsregel dialogboks. La oss begynne med å sette vår tilpassede betingede formatering:

Betinget formatering for å finne større enn 25 prosent økning

= (A2-B2) / A2> 25%

1. Fremheve celler B2 til B5 i regnearket.
2. Klikk på Hjem-fanen av bånd.
3. Klikk på Betinget formatering ikonet i bånd for å åpne rullegardinmenyen.
4. Velge Ny regel å åpne Ny formateringsregel dialogboks.
5. Fra Stil: boksen, velg Classic alternativ.
6. I den andre rullegardinmenyen klikker du på det siste alternativetBruk en formel til å bestemme hvilke celler som skal formateres.
7. Skriv inn formel gitt ovenfor i det oppgitte rommet.
8. Klikk på Format med: rullegardinmeny og velg deretter Lysrød fyll med mørk rød tekst alternativ.
9. Klikk OK for å lukke dialogboksen og gå tilbake til regnearket.
10. På dette punktet er bakgrunnsfargen til celler B3 og B5 bør være grønn

Betinget formatering for å finne større enn 50 prosent økning

= (A2-B2) / A2> 50%

Fremheve celler B2 til B5 i regnearket.

1. Klikk på Hjem-fanen av bånd.
2. Klikk på Betinget formatering ikonet i bånd for å åpne rullegardinmenyen.
3. Velge Ny regel å åpne Ny formateringsregel dialogboks.
4. Fra Stil: boksen, velg Classic alternativ.
5. I den andre rullegardinmenyen klikker du på det siste alternativetBruk en formel til å bestemme hvilke celler som skal formateres.
6. Skriv inn formel gitt ovenfor i det oppgitte rommet.
7. Klikk på Format med: rullegardinmeny og velg deretter Lysegrønn Fyll med Mørkegrønn Tekst alternativ.
8. Klikk OK for å lukke dialogboksen og gå tilbake til regnearket.

Bakgrunnsfargen til celle B3 bør fortsatt være grønt som indikerer at prosentforskjellen mellom tallene i celler A3 og B3 er større enn 25 prosent, men mindre enn eller lik 50 prosent. Bakgrunnsfargen til celle B5 bør endres til rødt som indikerer at prosentforskjellen mellom tallene i celler A5 og B5 er større enn 50 prosent.

03 av 04

Kontrollerer betingede formateringsregler

For å sjekke at betingelsene for betinget formatering er riktig, kan vi skrive inn formler i celler C2: C5 som vil beregne den nøyaktige prosentforskjellen mellom tallene i områdene A2: A5 og B2: B5.

= (A2-B2) / A2

1. Klikk på celle C2 for å gjøre den til den aktive cellen.
2. Skriv inn formelen ovenfor og trykk på Tast inn tast på tastaturet.
3. Svaret 10% skal vises i celle C2, som indikerer at tallet i celle A2 er 10% større enn tallet i celle B2.
4. Det kan være nødvendig å endre formateringen på celle C2 for å vise svaret som en prosentandel.
5. Bruke fyll håndtaket å kopiere formelen fra celle C2 til celler C3 til C5.
6. Svarene til celler C3 til C5 bør være 30%, 25% og 60%.

Svarene i disse cellene viser at de betingede formatteringsreglene som er opprettet er korrekte siden forskjellen mellom celler A3 og B3 er større enn 25 prosent og forskjellen mellom celler A5 og B5 er større enn 50 prosent.

Cell B4 Endret ikke farge fordi forskjellen mellom celler A4 og B4 tilsvarer 25 prosent, og vår betingede formateringsregel spesifiserte at en prosentandel på over 25 prosent var nødvendig for at bakgrunnsfargen skulle skifte til grønt.

04 av 04

Ordre av forutsetning for betinget formatering

Når flere regler blir brukt på samme dataområde, bestemmer Excel først om reglene er i konflikt. Konflikteregler er de hvor de valgte formateringsalternativene for hver regel ikke kan brukes begge til de samme dataene.

I eksemplet som brukes i denne opplæringen, er reglene i konflikt siden begge reglene bruker det samme formateringsalternativet - det å endre bakgrunnsfargen.

I situasjonen der den andre regelen er sant (differansen i verdien er større enn 50 prosent mellom to celler), er den første regelen (differansen i verdien som er større enn 25 prosent) også sant.

Excels rekkefølge av precedence

Siden en celle ikke kan ha både en rød og grønn bakgrunn på samme tid, trenger Excel å vite hvilken betinget formateringsregel den skal gjelde.

Hvilken regel som blir brukt, bestemmes av Excels prioritetsordre, som sier at regelen som er høyere i listen i dialogboksen Betinget formateringsregleringsbehandling, har forrang.

Som vist i bildet over, er den andre regelen som brukes i denne opplæringen høyere i listen, og har derfor forrang over den første regelen. Som et resultat blir bakgrunnsfargen på celle B5 endret til rød.

Som standard legges nye regler øverst på listen og har derfor høyere prioritet. For å endre rekkefølgen av forrang, bruk Opp og ned piltastene i dialogboksen som identifisert i bildet ovenfor.

Bruk av ikke-konfliktende regler

Hvis to eller flere betingede formateringsregler ikke er i konflikt, brukes begge når tilstanden hver regel tester blir sann.

Hvis den første betingede formateringsregelen i vårt eksempel formaterte rekkevidden av celler B2: B5 med en grønn ramme i stedet for en grønn bakgrunnsfarge, ville de to betingede formateringsregler ikke være i konflikt siden begge formatene kan brukes uten å forstyrre den andre.

Som et resultat, celle B5 ville ha både en grønn grense og en rød bakgrunnsfarge, siden forskjellen mellom tallene i celler A5 og B5 er større enn både 25 og 50 prosent.

Betinget formatering vs vanlig formatering

I tilfelle konflikter mellom betingede formateringsregler og manuelt anvendte formateringsalternativer, har forutgående formateringsregel alltid forrang og vil bli brukt i stedet for noen manuelt lagt formateringsalternativer.

Hvis en gul bakgrunnsfarge først ble brukt til celler B2 til B5 i eksemplet, når de betingede formatteringsreglene ble lagt til, bare celler B2 og B4 ville bli gul. Fordi de betingede formatteringsreglene er lagt inn gjelder for celler B3 og B5, deres bakgrunnsfarger vil forandre seg fra gul til grønn og rød henholdsvis.