Betinget formatering i Excel: Instruksjoner og bruk

Bruk disse instruksjonene til å lage en rekke formater

Betinget formatering i Microsoft Excel er et nyttig verktøy for å gjøre tabeller, rapporter eller celler mer fremtredende, å markere et område eller flere kolonner, å analysere data, å avklare forskjeller eller å identifisere trender. Datastenger, symbolsett eller fargede skalaer brukes til dette. Avhengig av betingelsene som er angitt, endres visningen av celler eller celleområdet formateres. Du kan lage din egen for de mange integrerte forholdene.

Betinget formatering gir mening og hjelper deg

Betinget formatering i Excel kan brukes på mange måter, enten det er på en Excel -tabell, et bestemt celleområde eller en pivottabellrapport. Ulike handlinger og formatmaler er tilgjengelige for deg. Den avgjørende fordelen for Excel -brukere er at spesielle verdier eller resultater kan fremheves med fargekoding. Hvis en verdi endres, justeres formateringen automatisk - avhengig av forholdene.

info: Pivottabellen ("Pivottabell") er en spesiell tabell som en stor mengde data kan beregnes, oppsummeres og analyseres med. I tillegg kan du raskt gjøre sammenligninger og identifisere trender i undersøkelsesdataene.

Betinget formatering med eksisterende regler for tall, tekstinnhold eller datoer

Hvis du formaterer med en sammenligningsoperator, er det lettere å finne celler. Du kan gjøre verdier mer synlige, for eksempel større eller mindre enn en definert verdi eller visse verdier som ligger mellom en øvre og nedre grense. Dette betyr at de ønskede eller søkte dataene vises i farger. Rask formatering eller avansert formatering er tilgjengelig for redigering. Med den utvidede formateringen kan du jobbe med formler, for eksempel for å automatisk merke en Excel -kolonne i farger.

Eksempler:

• En lagerbetjent ønsker å markere alle aksjer under 15 stykker med farget bakgrunn.
• Alle byer med mer enn 150 000 innbyggere skal merkes i en tabell.
• Et produksjonsselskap ønsker å markere alle varer produsert mellom 1. januar 2022-2023 og 31. januar 2022-2023 med rødt.

Kjapt format:

Valg av en eller flere celler.

Klikk på "Start" på fanen.

Gå til pilen ved siden av "Betinget formatering" -knappen i "Stil".

Klikk på "Regler for markering av celler".

Velg ønsket kommando, f.eks. B. større enn, mindre enn, mellom, dato osv.

Skriv inn verdier og velg format.

Utvidet formatering:

1. Velg én eller flere celler i en tabell eller pivottabellrapport.

2. Gå til "Hjem" -fanen, i gruppen Stiler, klikk på pilen ved siden av "Betinget formatering".

3. Klikk på "Administrer regler": Dialogboksen "Behandler for betingede formateringsregler" vises.

4. Velg handling:

   • Klikk på "Ny regel": Dialogboksen "Ny formateringsregel" vises.
   • For å endre et format, må du kontrollere at det korrekte regnearket eller tabellen er valgt i listeboksen "Vis formateringsregel for".
   • Gå til "Skjul dialogboks" i "Gjelder" -feltet; velg deretter det nye celleområdet i regnearket.
   • Klikk på "Utvid dialogboksen" -knappen.
   • Velg regelen og klikk på "Rediger regel": Dialogboksen "Rediger formateringsregel" vises.

5. Gjør følgende for å endre området for felt i verdiområdet "Bruk regel på" i en pivottabell -rapport:

   • Utvalg: Klikk på "Bare disse cellene"
   • Tilsvarende felt: Klikk på "Alle celler med samme felt"
   • Verdifelt: Klikk på "Alle celler"

6. Under "Velg regeltype" klikker du på "Formater bare celler som inneholder"

Nå kan du utføre handlinger under "Rediger regelbeskrivelse" i listefeltet "Format celler bare med":

Format etter nummer, dato eller klokkeslett:

For å gjøre dette, velg en celleverdi og sammenligningsoperator og skriv inn nummer, dato eller klokkeslett. eksempel: For "Mellom", skriv inn 300 og 500 eller velg "Lik med" og skriv inn en dato.

Du kan bruke en formel for å få et tall, dato eller klokkeslett tilbake.

Format etter tekst:

Velg "Spesifikk tekst" og "Relasjonsoperatør".

Skriv inn tekst, f.eks. B. on "inneholder" ordet "gull" eller på "starter med" et prefiks du vil søke etter.

Maksimalt 255 tegn kan legges inn.

Verdier kan returneres ved hjelp av formler. For å gjøre dette må formelen begynne med (=). Ugyldige formler er ikke formatert. Det er lurt å teste formelen for feil først.

Format etter dato:

• Velg dato og sammenligningsdato, f.eks. B. I går, neste uke eller denne måneden

Deretter: For å spesifisere et format, klikk på "Format", "Format celler" dialogboksen vises. Velg ønsket tallformat, fyllformat, ramme eller skrift, klikk på "OK".

Det er mulig å velge flere formater. Du kan se hvilke formater som er valgt i "Forhåndsvisning" -feltet.

Betinget formatering med databaser, fargevalg og symbolsett

Innhold og verdier i celler kan visuelt oppgraderes med databaser, fargeskalaer eller symbolsett og gjøres mer attraktive og tydeligere. Hvis verdier i et celleområde skal sammenlignes med hverandre, er dette lettere med denne betingede formateringen.

Formater celler med datalinjer

Datastolper gjør det lettere å sammenligne celler med hverandre. Verdien i cellen er angitt med lengden på linjen. Jo høyere verdi, jo lengre datalinje, jo lavere verdi, jo kortere er linjen. Dette gjør at høye eller lave verdier kan bestemmes raskt.

Kjapt format:

Merk ønsket område (celle, bord eller hele arket).

I kategorien Hjem klikker du på knappen Betinget formatering.

Gå til "datalinje" og velg en fylling.

Formater celler med fargede rammer

Fargeskalaer hjelper til med å klassifisere forskjellig datainformasjon lettere og kan raskt sammenligne innholdet med hverandre. Avhengig av fargenyansen vises høye eller lave verdier. Du kan formatere celler med en 2- eller 3-fargeskala.

2-farge skala

Kjapt format:

Velg celle, tabell eller hele arket.

Klikk Betinget formatering på Hjem -fanen.

Gå til "fargeskala" og klikk på ønsket 2-fargeskalaformat. Den øvre fargen står for høyere verdier, den nedre fargen for lavere verdier.

3-farge skala

Den tredje fargen muliggjør visualisering av gjennomsnittsverdier- i tillegg til fargegjengivelsen av høye og lave verdier som i 2-fargeskalaen.

Kjapt format:

Velg celle, tabell eller hele arket.

Klikk Betinget formatering på Hjem -fanen.

Gå til "fargeskala" og klikk på ønsket 3-fargeskalaformat. Den øvre fargen står for høyere verdier, den nedre fargen for lavere verdier.

Formater celler med symbolsett

Med et sett med symboler kan data vises og klassifiseres i separate kategorier. Et symbol står for et bestemt verdiområde. Den betingede formateringen i Excel tilbyr symboler i forskjellige farger: piler, prikker, kryss, utropstegn og mye mer.

Kjapt format:

Velg celle, tabell eller hele arket.

Klikk Betinget formatering på Hjem -fanen.

Gå til "Symbolsett" og velg ønsket symbolsett

Lag formateringsregler selv

Hvis du ikke finner et passende alternativ i den betingede formateringen som tilbys av Excel, kan du enkelt lage individuelle regler for formatering.

Merk celleområdet som skal formateres betinget.

Gå til "Betinget formatering" på "Hjem" -fanen.

Velg "Ny regel" og bekreft med "OK".

Hvis du ikke finner alternativet du trenger for din egen formateringsregel, kan en logisk formel hjelpe. For eksempel kan du bruke en formel for å farge Excel -celler automatisk så snart innholdet er tilgjengelig. Omvendt kan celler som er tomme formateres betinget.

Eksempel: Ved å bruke en formel kan du sammenligne data utenfor det valgte området. Dette inkluderer til og med data som er på et annet regneark i samme arbeidsbok. Eller du kan sammenligne verdier med et resultat returnert av en funksjon. Formelen må være logisk og returnere "true" eller "false".

Ved å markere celler direkte på regnearket, kan du skrive inn cellereferanser i en formel. Dette er absolutte cellereferanser. Imidlertid kan Excel også justere cellereferansene for hver celle i det valgte området. Hvis du ønsker det, velger du relative cellereferanser.

Trinn-for-trinn-instruksjoner for å lage en regel

1. I kategorien "Hjem", "Stiler", går du til pilen ved siden av "Betinget formatering" og klikker på "Administrer regler". Dialogboksen Manager for betinget formateringsregler vises.

2. Følgende handlinger er mulige:

   • Hvis du vil legge til et betinget format, går du til "Ny regel".
   • Endre et betinget format:
   • • Kontroller at du er på riktig regneark.
     • Klikk først på Skjul dialogboks i Gjelder for. Velg deretter det nye celleområdet og gå til "Utvid dialogboks".
     • Velg ønsket regel, klikk på "Rediger regel". "Rediger formateringsregel" vises.

3. Under "Velg regeltype" klikker du på "Bruk formel for å bestemme cellene som skal formateres".

   • Under "Rediger regelbeskrivelse" skriver du inn en formel i "Formater verdier som denne regelen er sann for". Formelen begynner med (=) og må returnere “true” (1) eller “false” (0).
   • Klikk Format for å vise dialogboksen Formatceller.
   • Velg nå tallet, fonten, fyllformatet etc. som skal brukes hvis celleverdien er sann, og bekreft med "OK". De valgte formatene vises i "Forhåndsvisning".

For eksempel returnerer COUNTIF -formelen logiske verdier. Hvis denne formelen brukes, kan duplikatverdier bestemmes, eller innhold som forekommer i en bestemt frekvens kan utheves i farger. På denne måten kan unike gjenstander også bli funnet og fremhevet i farger. Andre formler som kan brukes for betinget formatering er

• REST: slik at vekslende linjefarger kan settes automatisk
• IDENTISK: Med denne formelen kan dataene du ser etter merkes i en Excel -tabell som tilsvarer en spesifisert verdi
• FEIL: merker automatisk tomme celler i Excel -tabellen eller får feilverdier til å forsvinne fra arbeidsboken
• NOT og ISNUMBER: Formaterer alle Excel -celler som ikke inneholder tall
• MAX: den største verdien av cellen er uthevet i tabellen
• MIN: tillater betinget formatering av den minste verdien i Excel -listen, slik at minimumet kan sees med et øyeblikk
• VENSTRE: avhengig av en tegnstreng i begynnelsen, blir Excel -celler automatisk formatert og gitt en jevn tekstfarge
• LARGE og LEFT: gjør det mulig å fargelegge en celle med nytt innhold
• HØYRE: Celler kan formateres basert på strengens siste tegn
• I DAG: Med dette kan avtaler formateres i farger ved hjelp av trafikklysmetoden, eller datoverdier kan utheves som snart vil være oppdaterte
• MÅNED eller ÅR: lar deg dele Excel -listen i måneder eller år; Med YEAR kan datoverdier for inneværende år også merkes og årlige verdier utheves
• ELLER og UKEDAG: fremhever verdier i henhold til ukedag i farger

Eksempel på å opprette en regel for AND og OR sjekker:

I dette eksemplet brukes to verdier for betinget formatering. Hvis den første regelen ikke gjelder, gjelder den andre regelen automatisk.

Første regel: En boligkjøper ønsker å sjekke om det planlagte eiendomskjøpet er i tråd med budsjettet hans. Han kan ta inn opptil € 80 000 av sine egne midler som forskuddsbetaling og betjene en månedlig lånebetaling på maksimalt € 1200. Hvis både forskuddsbetalingen og lånebetalingen oppfyller disse kravene, er de tilsvarende cellene markert med grønt.

Andre regel: Hvis enten forskuddsbetalingen eller avdraget ikke oppfyller kravene, er de tilsvarende cellene merket med rødt.

Formelen for å lage den første regelen ved bruk av fargen grønn er som følger:

= AND (IF ($ B $ 4 <= 80000,1); IF (ABS ($ B $ 5) <= 1200,1))

Formelen for å lage den andre regelen ved hjelp av fargen rød er som følger:

= ELLER (HVIS ($ B $ 4 = 1200,1))

Overfør og slett betinget formatering

Betinget formatering kan kopieres og brukes på andre eller nye data i regnearket eller slettes igjen.

Overfør betinget formatering

For å kopiere betinget formatering og lime den inn i andre celler eller celleområder, bruk "Overfør format" -knappen.

Velg cellene med den betingede formateringen du vil kopiere og lime inn i andre celler, og klikk på dem.

Gå nå til "Start" og deretter "Overføringsformat". Markøren vises som en pensel.

Du kan legge til betinget formatering i en celle eller et celleområde ved å flytte penselen over cellen.

Med ESC avslutter du bruk av børsten.

Du kan fortsatt bruke penselen til å sette inn betinget formatering ved å dobbeltklikke på "Overfør format".

Alternativt kan den betingede formateringen kopieres ved hjelp av hurtigtastene og limes inn i en annen Excel -celle.

Klar betinget formatering

Det skilles mellom en slettingsprosess på et regneark og en slettingsprosess i en rekke celler.

Slett på et regneark eller i hele regnearket

Gå til "Betinget formatering" på "Hjem" -fanen.

Klikk deretter på "slett regler"

Klikk deretter på "Slett regler i hele arket".

Alternativt kan du slette den betingede formateringen ved hjelp av dialogboksen "Velg innhold".

Slett i celleområdet

Uthev celler som inneholder den betingede formateringen du ønsker.

Klikk på "Leseanalyse -forstørrelsesglass" -knappen (knappen ligger nederst til høyre ved siden av de valgte dataene).

Gå til "Slett format".

Finn og slett de samme betingede formatene i hele regnearket

Klikk på cellen med passende betinget format.

Klikk på pilen ved siden av "Finn og velg" i kategorien "Hjem", og klikk deretter på "Velg innhold".

Klikk på "Betingede formater".

Under "Datakontroll" går du til "Samme" (dette vil velge alle celler med samme betingede formatering).

Gå til "Betinget formatering" på "Start" -fanen, deretter "Slett regler" og "Slett regler i utvalgte celler".

Betinget formatering i pivottabellrapporten

Med en pivottabell -rapport kan brukerne raskt oppsummere store datamengder. Celleområde, Excel -regneark eller pivottabellrapport - betinget formatering fungerer i utgangspunktet det samme overalt. Bare i pivottabellrapporten er det noen få spesialfunksjoner du bør være oppmerksom på:

• Noen betingede formater fungerer ikke på felt i verdiområdet for en pivottabellrapport.
• Hvis du endrer layouten på pivottabellrapporten på noen måte, vil den betingede formateringen vedvare til feltene i de underliggende dataene er fjernet.
• Betinget formatering for felt i verdiområdet kan være basert på datahierarkiet. Området for denne betingede formateringen bestemmes av neste lavere eller neste høyere nivå i hierarkiet i rader for en eller flere kolonner eller i kolonner for en eller flere rader.
• Området for betinget formatering for felt i verdiområdet kan defineres ved hjelp av tre metoder:

• • valg
  • Tilsvarende felt
  • Verdifelt

Standardmetoden er valg.

Område definisjon etter valg

Gå til "Bruk formateringsregel på".

Klikk enten dialogboksen Ny formateringsregel eller dialogboksen Rediger formateringsregel.

Denne metoden er egnet for en sammenhengende rekke felt i verdiområdet, f.eks. B. undersøkelsesdata for en region, så vel som for en ikke-sammenhengende rekke felt i verdiområdet, for eksempel undersøkelsesdata for forskjellige regioner på forskjellige nivåer i datahierarkiet.

Områdedefinisjon gjennom et tilsvarende felt

Denne metoden fungerer hvis du har

• vil unngå å måtte velge mange ikke -relaterte varer
• ønsker å betinget formatere flere felt i verdiområdet for et datahierarkinivå
• Vil du ekskludere delvise resultater

Områdedefinisjon gjennom et verdifelt

Denne metoden fungerer hvis du har

• vil unngå å måtte velge mange ikke -relaterte varer
• ønsker å betinget formatere flere felt i verdiområdet for alle hierarkinivåer
• Vil du inkludere totaler og delvise resultater.

Konklusjon: Betinget formatering i Excel gjør det lettere å holde oversikt over data

Hvis du vil markere visse dataverdier i Excel -tabellen i farger, er den betingede formateringen i Excel ideell. Du har to alternativer for å bruke den betingede formateringen. Enten bruker du reglene fra Excel for å markere tekstinnhold, numeriske verdier eller datoer i farger. Eller du kan definere en regel selv med forskjellige forhold der de relevante verdiene skal merkes. Du kan enkelt og effektivt sammenligne data med hverandre ved hjelp av datalinjer, fargestolper og symbolsett. Hvis designalternativene ikke lenger er nødvendige, kan du fjerne dem med noen få klikk.

Flere artikler om betinget formatering

• Slik genererer du automatisk piler i Excel -lister
• Vis negative tidsverdier i Excel -tabeller
• Farge negative prosenter rød
• Gjenkjenne negative prosenter med et blikk
• Formater tall med et tilpasset format i farger
• Formater Excel -celler som ikke har den angitte verdien
• Lagre celler automatisk med de tilsvarende valutaformatene
• Merk cellene i farger ved hjelp av en makrofunksjon
• Bruk betinget formatering og uthev data som er større enn eller lik den tredje største cellen
• Omvendt sannhetsverdier