Betinget formatering i Excel: Instruktioner og brug

Brug disse instruktioner til at oprette forskellige formater

Betinget formatering i Microsoft Excel er et nyttigt værktøj til at gøre tabeller, rapporter eller celler mere fremtrædende, fremhæve et område eller flere kolonner, analysere data, afklare forskelle eller identificere tendenser. Datastænger, symbolsæt eller farvede skalaer bruges til dette. Afhængigt af de angivne betingelser ændres visningen af celler, eller celleområdet formateres. Du kan oprette din egen til de mange integrerede forhold.

Betinget formatering giver mening og hjælper dig

Betinget formatering i Excel kan anvendes på mange måder, uanset om det er på en Excel -tabel, et specifikt celleområde eller en pivottabelrapport. Forskellige handlinger og formatskabeloner er tilgængelige for dig. Den afgørende fordel for Excel -brugere er, at særlige værdier eller resultater kan fremhæves ved hjælp af farvekodning. Hvis en værdi ændres, justeres formateringen automatisk - afhængigt af forholdene.

info: Pivottabellen ("Pivottabel") er en særlig tabel, hvormed en stor mængde data kan beregnes, opsummeres og analyseres. Derudover kan du hurtigt foretage sammenligninger og identificere tendenser i undersøgelsesdataene.

Betinget formatering med eksisterende regler for tal, tekstindhold eller datoer

Hvis du formaterer med en sammenligningsoperator, kan celler lettere findes. Du kan gøre værdier mere synlige, for eksempel større eller mindre end en defineret værdi eller bestemte værdier, der ligger mellem en øvre og nedre grænse. Det betyder, at de ønskede eller søgte data vises i farver. Hurtig formatering eller avanceret formatering er tilgængelig til redigering. Med den udvidede formatering kan du arbejde med formler, for eksempel automatisk at markere en Excel -kolonne i farver.

Eksempler:

• En lagerbetjent vil gerne fremhæve alle lagre under 15 stykker med en farvet baggrund.
• Alle byer med mere end 150.000 indbyggere skal markeres i en tabel.
• En produktionsvirksomhed ønsker at fremhæve alle varer fremstillet mellem 1. januar 2022-2023 og 31. januar 2022-2023 med rødt.

Hurtig formatering:

Udvælgelse af en eller flere celler.

Klik på "Start" på fanen.

Gå til pilen ud for knappen "Betinget formatering" i "Style".

Klik på "Regler for fremhævelse af celler".

Vælg den ønskede kommando, f.eks. B. større end, mindre end, mellem, dato osv.

Indtast værdier, og vælg format.

Udvidet formatering:

1. Vælg en eller flere celler i en tabel eller pivottabelrapport.

2. Gå til fanen "Hjem", i gruppen Stilarter, klik på pilen ud for "Betinget formatering".

3. Klik på "Administrer regler": Dialogboksen "Manager for betingede formateringsregler" vises.

4. Vælg handling:

   • Klik på "Ny regel": Dialogboksen "Ny formateringsregel" vises.
   • Hvis du vil ændre et format, skal du sørge for, at det korrekte regneark eller den korrekte tabel er valgt i listeboksen "Vis formateringsregel for".
   • I feltet "Gælder" skal du gå til "Skjul dialogboks"; vælg derefter det nye celleområde på regnearket.
   • Klik på knappen "Udvid dialogboksen".
   • Vælg reglen, og klik på "Rediger regel": Dialogboksen "Rediger formateringsregel" vises.

5. Gør følgende for at ændre området for felter i værdiområdet "Anvend regel til" i en pivottabelrapport:

   • Udvælgelse: Klik på "Kun disse celler"
   • Tilsvarende felt: Klik på "Alle celler med de samme felter"
   • Værdifelt: Klik på "Alle celler"

6. Under "Vælg regeltype" skal du klikke på "Formater kun celler, der indeholder"

Nu kan du udføre handlinger under "Rediger regelbeskrivelse" i listefeltet "Formater kun celler med":

Formater efter nummer, dato eller klokkeslæt:

For at gøre dette skal du vælge en celleværdi og sammenligningsoperator og indtaste nummer, dato eller tid. eksempel: Indtast 300 og 500 for "Mellem", eller vælg "Lig med" og indtast en dato.

Du kan bruge en formel til at få et tal, dato eller tidsværdi tilbage.

Format efter tekst:

Vælg "Specific text" og "Relational operator".

Indtast tekst, f.eks. B. på "indeholder" ordet "guld" eller på "starter med" et præfiks, som du vil søge efter.

Der kan maksimalt indtastes 255 tegn.

Værdier kan returneres ved hjælp af formler. For at gøre dette skal formlen begynde med (=). Ugyldige formler er ikke formateret. Det tilrådes først at teste formlen for fejl.

Formater efter dato:

• Vælg dato og sammenligningsdato, f.eks. B. I går, næste uge eller denne måned

Derefter: For at angive et format skal du klikke på "Format", dialogboksen "Format celler" vises. Vælg det ønskede talformat, udfyldningsformat, ramme eller skrifttype, klik på "OK".

Det er muligt at vælge flere formater. Du kan se, hvilke formater der er valgt i feltet "Preview".

Betinget formatering med databaser, farveskemaer og symbolsæt

Indhold og værdier i celler kan visuelt opgraderes med databaser, farveskalaer eller symbolsæt og gøres mere attraktive og klarere. Hvis værdier i et celleområde skal sammenlignes med hinanden, er dette lettere med denne betingede formatering.

Formater celler med datalinjer

Datastænger gør det lettere at sammenligne celler med hinanden. Værdien i cellen angives ved længden af søjlen. Jo højere værdi, jo længere datalinje, jo lavere værdi, jo kortere bar. Dette gør det muligt hurtigt at bestemme høje eller lave værdier.

Hurtig formatering:

Marker det ønskede område (celle, bord eller hele ark).

Klik på knappen Betinget formatering på fanen Startside.

Gå til "datalinje", og vælg en fyldning.

Formater celler med farvede kanter

Farveskalaer hjælper med at klassificere forskellige dataoplysninger lettere og til hurtigt at kunne sammenligne deres indhold med hinanden. Afhængigt af farveskærmen vises høje eller lave værdier. Du kan formatere celler med en 2- eller 3-farve skala.

2-farve skala

Hurtig formatering:

Vælg celle, tabel eller hele ark.

Klik på Betinget formatering på fanen Startside.

Gå til "farveskala" og klik på det ønskede 2-farve skalaformat. Den øverste farve står for højere værdier, den nederste farve for lavere værdier.

3-farve skala

Den tredje farve muliggør visualisering af middelværdier- ud over farverepræsentationen af høje og lave værdier som i 2-farve skalaen.

Hurtig formatering:

Vælg celle, tabel eller hele ark.

Klik på Betinget formatering på fanen Startside.

Gå til "farveskala" og klik på det ønskede 3-farve skalaformat. Den øvre farve står for højere værdier, den nederste farve for lavere værdier.

Formater celler med symbolsæt

Med et sæt symboler kan data ses og klassificeres i separate kategorier. Et symbol står for et bestemt værdiområde. Den betingede formatering i Excel tilbyder symboler i forskellige farver: pile, prikker, kryds, udråbstegn og meget mere.

Hurtig formatering:

Vælg celle, tabel eller hele ark.

Klik på Betinget formatering på fanen Startside.

Gå til "Symbolsæt", og vælg det ønskede symbolsæt

Opret formateringsregler selv

Hvis du ikke kan finde en passende mulighed i den betingede formatering, der tilbydes af Excel, kan du nemt oprette individuelle regler for formatering.

Marker celleområdet, der skal formateres betinget.

Gå til "Betinget formatering" på fanen "Start".

Vælg "Ny regel", og bekræft med "OK".

Hvis du ikke kan finde den indstilling, du har brug for til din egen formateringsregel, kan en logisk formel hjælpe. For eksempel kan du bruge en formel til automatisk at farve Excel -celler, så snart indholdet er tilgængeligt. Omvendt kan celler, der er tomme, formateres betinget.

Eksempel: Ved at anvende en formel kan du sammenligne data uden for det valgte område. Dette inkluderer endda data, der er på et andet regneark i den samme projektmappe. Eller du kan sammenligne værdier med et resultat returneret af en funktion. Formlen skal være logisk og returnere "sand" eller "falsk".

Ved at markere celler direkte på regnearket kan du indtaste cellereferencer i en formel. Disse er absolutte cellereferencer. Excel kan dog også justere cellereferencerne for hver celle i det valgte område. Hvis du ønsker det, skal du vælge relative cellereferencer.

Trin-for-trin instruktioner til oprettelse af en regel

1. På fanen "Start", gruppen "Stilarter" skal du gå til pilen ud for "Betinget formatering" og klikke på "Administrer regler". Dialogboksen Manager for betinget formateringsregler vises.

2. Følgende handlinger er mulige:

   • Hvis du vil tilføje et betinget format, skal du gå til "Ny regel".
   • Skift et betinget format:
   • • Kontroller, at du er på det korrekte regneark.
     • Først skal du klikke på Skjul dialogboksen i Gælder for. Vælg derefter det nye celleområde og gå til "Udvid dialogboks".
     • Vælg den ønskede regel, klik på "Rediger regel". "Rediger formateringsregel" vises.

3. Under "Vælg regeltype" skal du klikke på "Brug formel til at bestemme de celler, der skal formateres".

   • Under "Rediger regelbeskrivelse" skal du indtaste en formel i "Formatér værdier, som denne regel er sand for". Formlen begynder med (=) og skal returnere “true” (1) eller “false” (0).
   • Klik på Format for at få vist dialogboksen Format celler.
   • Vælg nu det nummer, skrifttype, udfyldningsformat osv., Der skal bruges, hvis celleværdien er sand, og bekræft med "OK". De valgte formater vises i "Preview".

F.eks. Returnerer COUNTIF -formlen logiske værdier. Hvis denne formel bruges, kan duplikatværdier bestemmes, eller indhold, der forekommer i en bestemt frekvens, kan fremhæves i farve. På denne måde kan unikke genstande også findes og fremhæves i farver. Andre formler, der kan bruges til betinget formatering, er

• REST: så skiftende linjefarver kan indstilles automatisk
• IDENTISK: Med denne formel kan de data, du leder efter, markeres i en Excel -tabel, der svarer til en bestemt værdi
• FEJL: markerer automatisk tomme celler i Excel -tabellen eller får fejlværdier til at forsvinde fra projektmappen
• NOT og ISNUMBER: Formaterer alle Excel -celler, der ikke indeholder tal
• MAX: cellens største værdi fremhæves i tabellen
• MIN: muliggør den betingede formatering af den mindste værdi i Excel -listen, så minimum kan ses med et øjeblik
• VENSTRE: afhængigt af en tegnstreng i begyndelsen formateres Excel -celler automatisk og får en ensartet tekstfarve
• LARGE og LEFT: gør det muligt at farve en celle med nyt indhold
• HØJRE: Celler kan formateres baseret på strengens sidste tegn
• I DAG: Med dette kan aftaler formateres i farver ved hjælp af trafiklysmetoden, eller datoværdier kan fremhæves, som snart vil være opdaterede
• MÅNED eller ÅR: giver dig mulighed for at opdele Excel -listen i måneder eller år; Med YEAR kan datoværdier for det aktuelle år også markeres, og årlige værdier kan fremhæves
• ELLER og WEEKDAY: fremhæver værdier i henhold til ugedag i farver

Eksempel på oprettelse af en regel for AND og OR -kontroller:

I dette eksempel bruges to værdier til betinget formatering. Hvis den første regel ikke finder anvendelse, gælder den anden regel automatisk.

Første regel: En boligkøber ønsker at kontrollere, om det planlagte ejendomskøb er i overensstemmelse med hans budget. Han kan indbetale op til € 80.000 af sine egne midler som en forskudsbetaling og betjene en månedlig låneafbetaling på maksimalt € 1.200. Hvis både udbetalingen og låneafdraget opfylder disse krav, er de tilsvarende celler markeret med grønt.

Anden regel: Hvis enten udbetalingen eller afdraget ikke opfylder kravene, er de tilsvarende celler markeret med rødt.

Formlen til oprettelse af den første regel ved hjælp af farven grøn er som følger:

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

Formlen til oprettelse af den anden regel ved hjælp af farven rød er som følger:

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

Overfør og slet betinget formatering

Betinget formatering kan kopieres og anvendes på andre eller nye data i regnearket eller slettes igen.

Overfør betinget formatering

Hvis du vil kopiere betinget formatering og indsætte den i andre celler eller celleområder, skal du bruge knappen "Overfør format".

Vælg cellerne med den betingede formatering, som du vil kopiere og indsætte i andre celler, og klik på dem.

Gå nu til "Start" og derefter "Overfør format". Markøren vises som en pensel.

Du kan føje betinget formatering til en celle eller et celleområde ved at flytte børsten over cellen.

Med ESC afslutter du brugen af børsten.

Du kan stadig bruge penslen til at indsætte betinget formatering ved at dobbeltklikke på "Overfør format".

Alternativt kan den betingede formatering kopieres ved hjælp af tastaturgenveje og indsættes i en anden Excel -celle.

Klar betinget formatering

Der skelnes mellem en sletningsproces på et regneark og en sletningsproces i en række celler.

Slet på et regneark eller i hele regnearket

Gå til "Betinget formatering" på fanen "Start".

Klik derefter på "slet regler"

Klik derefter på "Slet regler i hele arket".

Alternativt kan du slette den betingede formatering ved hjælp af dialogboksen "Vælg indhold".

Slet i celleområdet

Fremhæv celler, der indeholder den betingede formatering, du ønsker.

Klik på knappen "Læse analyse forstørrelsesglas" (knappen er placeret nederst til højre ud for de valgte data).

Gå til "Slet format".

Find og slet de samme betingede formater i hele regnearket

Klik på cellen med det relevante betingede format.

Klik på pilen ved siden af "Find og vælg" på fanen "Hjem", og klik derefter på "Vælg indhold".

Klik på "Betingede formater".

Under "Datakontrol" skal du gå til "Samme" (dette markerer alle celler med den samme betingede formatering).

Gå til "Betinget formatering" på fanen "Start", derefter "Slet regler" og "Slet regler i udvalgte celler".

Betinget formatering i pivottabelrapporten

Med en pivottabelrapport kan brugerne hurtigt opsummere store datamængder. Celleområde, Excel -regneark eller pivottabelrapport - betinget formatering fungerer stort set det samme overalt. Kun i pivottabelrapporten er der et par specialfunktioner, du skal være opmærksom på:

• Nogle betingede formater fungerer ikke på felter i værdiområdet for en pivottabelrapport.
• Hvis du på nogen måde ændrer layoutet på pivottabelrapporten, vil den betingede formatering fortsætte, indtil felterne i de underliggende data er fjernet.
• Betinget formatering af felter i værdiområdet kan være baseret på datahierarkiet. Området for denne betingede formatering bestemmes af det næste lavere eller næste højere niveau i hierarkiet i rækker for en eller flere kolonner eller i kolonner for en eller flere rækker.
• Området for betinget formatering for felter i værdiområdet kan defineres ved hjælp af tre metoder:

• • udvælgelse
  • Tilsvarende felt
  • Værdifelt

Standardmetoden er valg.

Områdedefinition efter valg

Gå til "Anvend formateringsregel på".

Klik enten på dialogboksen Ny formateringsregel eller på dialogboksen Rediger formateringsregel.

Denne metode er velegnet til en sammenhængende række felter i værdiområdet, f.eks. B. undersøgelsesdata for en region såvel som for en ikke-sammenhængende række felter i værdiområdet, f.eks. Undersøgelsesdata for forskellige regioner på forskellige niveauer i datahierarkiet.

Områdedefinition gennem et tilsvarende felt

Denne metode virker, hvis du har

• vil undgå at skulle vælge mange ikke -relaterede varer
• ønsker at betinget formatere flere felter i værdiområdet for et datahierarkiniveau
• Vil du udelukke delvise resultater

Områdedefinition gennem et værdifelt

Denne metode virker, hvis du har

• vil undgå at skulle vælge mange ikke -relaterede varer
• ønsker at betinget formatere flere felter i værdiområdet for alle hierarkiniveauer
• Vil medtage totaler og delresultater.

Konklusion: Betinget formatering i Excel gør det lettere at holde styr på data

Hvis du vil fremhæve bestemte dataværdier i din Excel -tabel med farver, er den betingede formatering i Excel ideel. Du har to muligheder for at anvende den betingede formatering. Enten bruger du Excel -reglerne til at markere tekstindhold, numeriske værdier eller datoer i farver. Eller du kan selv definere en regel med forskellige betingelser, hvorunder de relevante værdier skal markeres. Du kan let og effektivt sammenligne data med hinanden ved hjælp af datastænger, farvebjælker og symbolsæt. Hvis designmulighederne ikke længere er nødvendige, kan du fjerne dem med et par klik.

Flere artikler om betinget formatering

• Sådan genereres pile automatisk i Excel -lister
• Vis negative tidsværdier i Excel -tabeller
• Farve negative procenter rød
• Genkend negative procentsatser på et øjeblik
• Formater tal med et brugerdefineret format i farver
• Formater Excel -celler, der ikke har den angivne værdi
• Gem automatisk celler med de tilsvarende valutaformater
• Marker celler i farve ved hjælp af en makrofunktion
• Brug betinget formatering og fremhæv data, der er større end eller lig med den tredjestørste celle
• Omvendt sandhedsværdier