Brug tilpassede betingede formateringsregler for datoer i Excel

Tilføjelse af betinget formatering til en celle i Excel giver dig mulighed for at anvende forskellige formateringsindstillinger, f.eks. Farve, når dataene i den celle opfylder de betingelser, du har angivet.

For at gøre betinget formatering nemmere understøtter Excel forudindstillede muligheder, der dækker almindeligt anvendte situationer, såsom:

• Datoer
• Duplikat data
• Værdier over eller under gennemsnitsværdien i en række celler

I tilfælde af datoer forenkler de forudindstillede muligheder processen med at tjekke dine data for datoer tæt på den aktuelle dato - f.eks. I går, i morgen, i sidste uge eller i næste måned.

Hvis du vil tjekke for datoer, der falder uden for de angivne muligheder, kan du dog tilpasse betinget formatering ved at tilføje din egen formel ved hjælp af en eller flere af Excels datafunktioner.

01 af 06

Kontrol af datoer 30, 60 og 90 dage forfalden

Tilpas betinget formatering ved hjælp af formler ved at indstille en ny regel, som Excel følger, når du evaluerer dataene i en celle.

Excel anvender betinget formatering i top til bund rækkefølge, som de vises i Conditional Formatting Rules Manager dialog boks.

Selvom flere regler kan gælde for nogle celler, anvendes den første regel, der opfylder betingelsen, til cellerne.

Fortsæt læsning herunder

02 af 06

Kontrol af datoer 30 dage forfalden

Fremhæv cellerne C1 til C4 for at vælge dem. Dette er det område, som vi vil anvende de betingede formateringsregler på

1. KlikHjem > Betinget formatering > Ny regelat åbneNy formatering regel dialog boks.
2. Klik på knappenBrug en formel til at bestemme, hvilke celler der skal formateres mulighed.
3. Indtast følgende formel i feltet underFormater værdier, hvor denne værdi er sand mulighed i nederste halvdel af dialogboksen:= DAG () - C1> 30Denne formel kontrollerer, om datoerne i cellerne C1 til C4 er mere end 30 dage tidligere
4. Klik på knappen Format knappen for at åbne dialogboksen Formaterceller.
5. Klik på knappenFylde fanebladet for at se baggrundsfarveindstillingerne.
6. Vælg en baggrundsfarve.
7. Klik på knappenSkrifttype fanen for at se indstillinger for skrifttypeformat
8. Indstil skrifttypens farve.
9. KlikOkay to gange for at lukke dialogboksen og vende tilbage til regnearket.
10. Baggrundsfarven på cellerne C1 til C4 ændres til den valgte fyldfarve, selv om der ikke er nogen data i cellerne.

Fortsæt læsning herunder

03 af 06

Tilføjelse af en regel til datoer mere end 60 dage forfalden

I stedet for at gentage alle trin ovenfor for at tilføje de to næste regler, vil vi gøre brug af indstillingen Administrer regler, der gør det muligt for os at tilføje de ekstra regler på én gang.

1. Fremhæv cellerne C1 til C4, hvis det er nødvendigt.
2. Klik Hjem > Betinget formatering > Administrer regler at åbne Conditional Formatting Rules Manager dialog boks.
3. Klik på knappen Ny regel mulighed i øverste venstre hjørne af dialogboksen
4. Klik på knappen Brug en formel til at bestemme, hvilke celler der skal formateres mulighed fra listen øverst i dialogboksen.
5. Indtast følgende formel i feltet under Formater værdier, hvor denne værdi er mulighed i nederste halvdel af dialogboksen:= TODAY () - C1> 60Denne formel kontrollerer, om datoerne i cellerne C1 til C4 er større end 60 dage tidligere.
6. Klik på knappen Format knappen for at åbne Format celler dialog boks.
7. Klik på knappen Fylde fanebladet for at se baggrundsfarveindstillingerne.
8. Vælg en baggrundsfarve.
9. Klik Okay to gange for at lukke dialogboksen og vende tilbage til Conditional Formatting Rules Manager dialog boks.

04 af 06

Tilføjelse af en regel til datoer mere end 90 dage forfalden

Gentag trin fem til syv for at tilføje en ny regel.

1. Til formel brug:= TODAY () - C1> 90
2. Vælg en baggrundsfarve.
3. Indstil skrifttypens farve.
4. KlikOkay to gange for at lukke dialogboksen og vende tilbage tilConditional Formatting Rules Manager dialog boks
5. KlikOkay igen for at lukke denne dialogboks og vende tilbage til regnearket.
6. Baggrundsfarven på cellerne C1 til C4 ændres til den sidst valgte farve.

Fortsæt læsning herunder

05 af 06

Testning af betingede formateringsregler

Test de betingede formateringsregler i cellerne C1 til C4 ved at indtaste følgende datoer:

• Den aktuelle dato - cellen skal skifte til standard hvid baggrund med sort tekst, da ingen af ​​de betingede formateringsregler gælder.
• 40 dage før den aktuelle dato - cellen skal skifte til en lysegrøn baggrund med hvid tekst. Formlen = DAG () - 40 Indtast dato 40 dage før den aktuelle dato.
• 70 dage før den aktuelle dato - cellen skal skifte til en gul baggrund med hvid tekst.
• 100 dage før den aktuelle dato - cellen skal skifte til en mørk rød baggrund med hvid tekst.

06 af 06

Alternative betingede formateringsregler

Hvis dit regneark allerede viser den aktuelle dato - og mange regneark gør - bruger en alternativ formel en cellereference til cellen, hvor den aktuelle dato vises, frem for at bruge funktionen TODAY.

For eksempel, hvis datoen i celle B4, formlen indtastet som regel til betinget format datoer, der er mere end 30 dage forfaldne kunne være:

= $ B $ 4> 30

Dollarsignalerne ($), der omgiver cellehenvisningen B4, forhindrer cellereferencen i at ændre sig, hvis den betingede formateringsregel er kopieret til andre celler i regnearket.

Dollar tegnene skaber, hvad der er kendt som en absolut celle reference.

Hvis dollartegnene udelades, og den betingede formateringsregel er kopieret, vil målcellen eller cellerne sandsynligvis vise a #REF! fejl besked.