Excel

Betinget formatering med formler (10 eksempler)

Conditional Formatting With Formulas

Hurtig start | Eksempler | Fejlfinding | Uddannelse

Betinget formatering er en fantastisk måde at hurtigt visualisere data i et regneark på. Med betinget formatering kan du gøre ting som fremhævelsesdatoer i de næste 30 dage, markere dataindtastningsproblemer, fremhæve rækker, der indeholder topkunder, vise dubletter og mere.

Excel leveres med et stort antal 'forudindstillinger', der gør det let at oprette nye regler uden formler. Du kan dog også oprette regler med dine egne brugerdefinerede formler. Ved at bruge din egen formel overtager du den betingelse, der udløser en regel og kan anvende nøjagtigt den logik, du har brug for. Formler giver dig maksimal kraft og fleksibilitet.



For eksempel ved at bruge forudindstillingen 'Lig med' er det let at fremhæve celler svarende til 'æble'.



Men hvad nu hvis du vil fremhæve celler svarende til 'æble' eller 'kiwi' eller 'kalk'? Sikker på, du kan oprette en regel for hver værdi, men det er en masse problemer. I stedet kan du blot bruge en regel baseret på en formel med ELLER funktion :

En regel, der markerer x, y eller z



Her er resultatet af reglen anvendt på området B4: F8 i dette regneark:

Betinget formatering med OR-funktionen

Her er den nøjagtige formel, der anvendes:



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

Hurtig start

Du kan oprette en formelbaseret betinget formateringsregel i fire nemme trin:

1. Vælg de celler, du vil formatere.

Vælg de celler, der skal formateres

2. Opret en betinget formateringsregel, og vælg indstillingen Formel

Vælg formelindstillingen

3. Indtast en formel, der returnerer SAND eller FALSK.

Indtast formlen i forhold til den aktive celle

4. Indstil formateringsindstillinger, og gem reglen.

Indstil formateringsmuligheder

Det ISODD-funktion returnerer kun SAND for ulige tal, der udløser reglen:

excel konverter decimal tid til timer og minutter

ISODD-funktionen returnerer SAND for ulige tal, der udløser reglen

Video: Sådan anvendes betinget formatering med en formel

Vi tilbyder også videotræning om dette emne .

Formel logik

Formler, der anvender betinget formatering, skal returnere SAND eller FALSK eller numeriske ækvivalenter. Her er nogle eksempler:

excelformel for forfaldsdato
 
= ISODD (A1) = ISNUMBER (A1) =A1>100 = AND (A1>100,B1<50) = OR (F1='MN',F1='WI')

Ovenstående formler returnerer alle SAND eller FALSK, så de fungerer perfekt som en udløser for betinget formatering.

Når betinget formatering anvendes på en række celler, skal du indtaste cellereferencer i forhold til den første række og kolonne i markeringen (dvs. den øverste venstre celle). Tricket til at forstå, hvordan betingede formateringsformler fungerer, er at visualisere den samme formel, der anvendes på hver celle i markeringen , med cellehenvisninger opdateret som normalt. Forestil dig, at du indtastede formlen i markeringens øverste venstre celle og derefter kopierede formlen over hele markeringen. Hvis du kæmper med dette, se afsnittet om Dummy-formler under.

Formeleksempler

Nedenfor er eksempler på brugerdefinerede formler, du kan bruge til at anvende betinget formatering. Nogle af disse eksempler kan oprettes ved hjælp af Excels indbyggede forudindstillinger til fremhævning af celler, men tilpassede formler kan gå langt ud over forudindstillinger, som du kan se nedenfor.

Se også: Mere end 30 betingede formateringsformler

Fremhæv ordrer fra Texas

For at fremhæve rækker, der repræsenterer ordrer fra Texas (forkortet TX), skal du bruge en formel, der låser henvisningen til kolonne F:

 
=$F5='TX'

Brug en formel til at fremhæve rækker, hvor state =

For flere detaljer, se denne artikel: Fremhæv rækker med betinget formatering .

Video: Sådan fremhæves rækker med betinget formatering

Fremhæv datoer i de næste 30 dage

For at fremhæve datoer, der finder sted i de næste 30 dage, har vi brug for en formel, der (1) sørger for, at datoer er i fremtiden, og (2) sørger for, at datoer er 30 dage eller derunder fra i dag. En måde at gøre dette på er at bruge OG funktion sammen med NU-funktion sådan her:

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

Med en aktuel dato den 18. august 2016 fremhæver den betingede formatering datoer som følger:

Betinget formatering for at fremhæve datoer i de næste 30 dage

Det NU-funktion returnerer den aktuelle dato og tid. For detaljer om, hvordan denne formel fungerer, se denne artikel: Fremhæv datoer i de næste N dage .

Fremhæv kolonneforskelle

Givet to kolonner, der indeholder lignende oplysninger, kan du bruge betinget formatering til at få øje på subtile forskelle. Formlen, der bruges til at udløse nedenstående formatering, er:

 
=$B4$C4

Betinget formatering for at sammenligne kolonner

Se også: en version af denne formel, der bruger funktionen EXAKT til at foretage en sagsfølsom sammenligning .

Fremhæv manglende værdier

For at fremhæve værdier på en liste, der mangler fra en anden, kan du bruge en formel baseret på COUNTIF-funktion :

 
= COUNTIF (list,B5)=0

Fremhæv manglende værdier med betinget formatering

Denne formel kontrollerer simpelthen hver værdi i Liste A mod værdier i det navngivne område 'liste' (D5: D10). Når antallet er nul, returnerer formlen SAND og udløser reglen, som fremhæver værdier i Liste A der mangler fra Liste B .

Video: Sådan finder du manglende værdier med COUNTIF

Fremhæv ejendomme med 3+ soveværelser under $ 350.000

For at finde ejendomme på denne liste, der har mindst 3 soveværelser, men er mindre end $ 300.000, kan du bruge en formel baseret på AND-funktionen:

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

Dollartegnene ($) låser henvisningen til kolonne C og D og OG funktion bruges til at sikre, at begge betingelser er SANDE. I rækker, hvor AND-funktionen returnerer SAND, anvendes den betingede formatering:

Betinget formatering for at fremhæve ejendomsfortegnelser

Fremhæv topværdier (dynamisk eksempel)

Selvom Excel har forudindstillinger for 'topværdier', viser dette eksempel, hvordan man gør det samme med en formel, og hvordan formler kan være mere fleksible. Ved at bruge en formel kan vi gøre regnearket interaktivt - når værdien i F2 opdateres, reagerer reglen med det samme og fremhæver nye værdier.

Dynamisk betinget formatering til topværdier

Formlen, der anvendes til denne regel, er:

hvordan man laver drop downs i excel
 
=B4>= LARGE (data,input)

Hvor 'data' er det navngivne område B4: G11, og 'input' er det navngivne område F2. Denne side har detaljer og en komplet forklaring .

Gantt-diagrammer

Tro det eller ej, du kan endda bruge formler til at oprette enkle Gantt-diagrammer med betinget formatering som denne:

Brug af betinget formatering til at oprette et Gantt-diagram

Dette regneark bruger to regler, en til bjælkerne og en til weekendskygge:

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

Denne artikel forklarer formlen for søjler og denne artikel forklarer formlen for weekendskygge .

Enkelt søgefelt

Et sejt trick, du kan gøre med betinget formatering, er at oprette et simpelt søgefelt. I dette eksempel fremhæver en regel celler i kolonne B, der indeholder tekst skrevet i celle F2:

Søgefelt til betinget formatering

Den anvendte formel er:

 
= ISNUMBER ( SEARCH ($F,B2))

For flere detaljer og en fuldstændig forklaring, se:

Fejlfinding

Hvis du ikke kan få dine betingede formateringsregler til at udløse korrekt, er der sandsynligvis et problem med din formel. Først skal du sørge for at du startede formlen med et ligetegn (=). Hvis du glemmer dette trin, konverterer Excel hele din formel til tekst, hvilket gør den ubrugelig. For at rette, skal du bare fjerne de dobbelte citater, Excel tilføjet på begge sider, og sørg for, at formlen begynder med lig med (=).

Hvis din formel er indtastet korrekt, men ikke udløser reglen, skal du muligvis grave lidt dybere. Normalt, kan du bruge F9-tasten til at kontrollere resultaterne i en formel eller bruge evalueringsfunktionen til at gå gennem en formel. Desværre kan du ikke bruge disse værktøjer med betingede formateringsformler, men du kan bruge en teknik kaldet 'dummy formler'.

Dummy-formler

Dummy-formler er en måde at teste dine betingede formateringsformler direkte på regnearket, så du kan se, hvad de rent faktisk laver. Dette kan være en stor tidsbesparelse, når du kæmper for at få cellereferencer til at fungere korrekt.

I en nøddeskal indtaster du den samme formel på tværs af en række celler, der matcher formen på dine data. Her kan du se værdierne returneret af hver formel, og det er en fantastisk måde at visualisere og forstå, hvordan formelbaseret betinget formatering fungerer. For en detaljeret forklaring, se denne artikel .

Brug dummy-formler til at kontrollere betingede formateringsformler

Video: Test betinget formatering med dummy-formler

Begrænsninger

Der er nogle begrænsninger, der følger med formelbaseret betinget formatering:

  1. Du kan ikke anvende ikoner, farveskalaer eller databjælker med en brugerdefineret formel. Du er begrænset til standard celleformatering, herunder talformater, skrifttype, udfyldningsfarve og kantindstillinger.
  2. Du kan ikke bruge bestemte formelkonstruktioner som fagforeninger, kryds eller matrixkonstanter til betingede formateringskriterier.
  3. Du kan ikke henvise til andre projektmapper i en betinget formateringsformel.

Du kan nogle gange arbejde omkring nr. 2 og nr. 3. Du kan muligvis flytte formlenes logik ind i en celle i regnearket, og henvis derefter til den celle i formlen i stedet. Hvis du prøver at bruge en matrixkonstant, skal du prøve at oprette et navngivet område i stedet.

Flere CF formel ressourcer

  • Mere end 30 betingede formateringsformler
  • Videotræning med øvelsesark
Forfatter Dave Bruns


^