Tilføjelse af betinget formatering i Excel giver dig mulighed for at anvende forskellige formateringsindstillinger på en celle eller et celleområde, der opfylder specifikke betingelser, som du har angivet. Indstilling af sådanne betingelser kan hjælpe med at organisere dit regneark og gøre det nemmere at scanne. De formateringsmuligheder, du kan bruge, omfatter ændringer af skrifttype og baggrundsfarve, skrifttypestile, cellekanter og tilføjelse af talformatering til data.
Excel har indbyggede muligheder for almindeligt anvendte forhold, såsom at finde tal, der er større end eller mindre end en bestemt værdi, eller finde tal, der er over eller under gennemsnitsværdien. Ud over disse forudindstillede muligheder kan du også oprette brugerdefinerede betingede formateringsregler ved hjælp af Excel-formler.
Disse instruktioner gælder for Excel 2019, 2016, 2013, 2010 og Excel til Microsoft 365.
Anvendelse af flere betingelser i Excel
Du kan anvende mere end én regel på de samme data for at teste for forskellige forhold. For eksempel kan budgetdata have betingelser, der anvender formateringsændringer, når visse forbrugsniveauer nås, såsom 50 %, 75 % og 100 %, af det samlede budget.
Under sådanne omstændigheder afgør Excel først, om de forskellige regler er i konflikt, og i så fald følger programmet en fastsat rækkefølge for at bestemme, hvilken betinget formateringsregel, der skal anvendes på dataene.
Find data, der overstiger 25 % og 50 % stigninger
I det følgende eksempel vil to tilpassede betingede formateringsregler blive anvendt på intervallet celler B2 til B5.
- Den første regel kontrollerer, om dataene i celler A2:A5 er større end den tilsvarende værdi i B2:B5 ved mere end 25%.
- Den anden regel kontrollerer, om de samme data i A2:A5 overstiger den tilsvarende værdi i B2:B5 med mere end 50%.
Som det kan ses på billedet ovenfor, ændres baggrundsfarven på cellen eller cellerne i området B1:B4, hvis en af ovenstående betingelser er sande.
- For data, hvor forskellen er mere end 25 %, ændres cellebaggrundsfarven til grøn.
- Hvis forskellen er større end 50 %, ændres cellebaggrundsfarven til rød.
Reglerne, der bruges til at udføre denne opgave, vil blive indtastet ved hjælp af New Formatting Rule dialogboksen. Begynd med at indtaste eksempeldataene i celler A1 til C5 som vist på billedet ovenfor.
I den sidste del af selvstudiet vil vi tilføje formler til celler C2:C4, der viser den nøjagtige procentvise forskel mellem værdierne i cellerne A2:A5 og B2:B5; dette giver os mulighed for at kontrollere nøjagtigheden af reglerne for betinget formatering.
Indstilling af betingede formateringsregler
Først anvender vi betinget formatering for at finde en væsentlig stigning på 25 procent eller mere.
Funktionen vil se sådan ud:
=(A2-B2)/A2>25 %
- Fremhæv celler B2 til B5 i regnearket.
- Klik på fanen Hjem i båndet.
- Klik på ikonet Betinget formatering i ribbon for at åbne rullemenuen.
-
Vælg Ny regel for at åbne dialogboksen New Formatting Rule.
- Under Vælg en regeltype, klik på den sidste mulighed: Brug en formel til at bestemme, hvilke celler der skal formateres.
- Indtast formlen, der er angivet ovenfor, i feltet under Format værdier, hvor denne formel er sand:
- Klik på knappen Format for at åbne dialogboksen. Klik på fanen Fyld, og vælg en farve.
- Klik OK for at lukke dialogboksene og vende tilbage til regnearket.
- Baggrundsfarven for celler B3 og B5 skal ændres til den farve, du har valgt.
Nu vil vi anvende betinget formatering for at finde en stigning på 50 procent eller mere. Formlen vil se sådan ud:
- Gentag de første fem trin ovenfor.
- Indtast formlen ovenfor i feltet nedenfor Format værdier, hvor denne formel er sand:
- Klik på knappen Format for at åbne dialogboksen. Klik på fanen Fyld, og vælg en anden farve, end du gjorde i det foregående sæt trin.
- Klik OK for at lukke dialogboksene og vende tilbage til regnearket.
Baggrundsfarven for celle B3 skal forblive den samme, hvilket indikerer, at den procentvise forskel mellem tallene i cellerne A3 ogB3 er større end 25 procent, men mindre end eller lig med 50 procent. Baggrundsfarven for celle B5 skal ændres til den nye farve, du har valgt, hvilket indikerer, at den procentvise forskel mellem tallene i cellerne A5 og B5 er større end 50 procent.
Kontrol af betingede formateringsregler
For at bekræfte, at de indtastede betingede formateringsregler er korrekte, kan vi indtaste formler i cellerne C2:C5, som vil beregne den nøjagtige procentvise forskel mellem tallene i intervallerneA2:A5 og B2:B5.
Formlen i celle C2 ser sådan ud:
=(A2-B2)/A2
- Klik på celle C2 for at gøre den til den aktive celle.
- Indtast ovenstående formel, og tryk på Enter-tasten på tastaturet.
- Svaret 10 % skal vises i celle C2, hvilket indikerer, at tallet i celle A2 er 10 % større end tallet i celle B2.
- Det kan være nødvendigt at ændre formateringen på celle C2 for at få vist svaret som en procent.
- Brug udfyldningshåndtaget til at kopiere formlen fra celle C2 til celler C3 til C5.
- Svarene for celler C3 til C5 skal være 30 %, 25 % og 60 %.
Svarene i disse celler viser, at reglerne for betinget formatering er nøjagtige, da forskellen mellem celler A3 og B3 er større end 25 procent, og forskellen mellem celler A5 og B5 er større end 50 procent.
Celle B4 ændrede ikke farve, fordi forskellen mellem celler A4 og B4 er lig med 25 procent, og vores regel for betinget formatering specificerede, at en procentdel større end 25 procent var påkrævet, for at baggrundsfarven kunne ændres.
Rækkefølge for betinget formatering
Når du anvender flere regler på det samme dataområde, afgør Excel først, om reglerne er i konflikt. Modstridende regler er dem, hvor formateringsmulighederne ikke begge kan anvendes på de samme data.
I vores eksempel er reglerne i konflikt, da begge bruger den samme formateringsmulighed - ændring af baggrundscellefarven.
I den situation, hvor den anden regel er sand (forskellen i værdi er mere end 50 procent mellem to celler), så er den første regel (værdiforskellen er større end 25 procent) også sand.
Da en celle ikke kan have begge to forskellige farvebaggrunde på samme tid, skal Excel vide, hvilken betinget formateringsregel den skal anvende.
Excels rækkefølge angiver, at den regel, der er højere på listen i dialogboksen Conditional Formatting Rules Manager, anvendes først.
Som vist på billedet ovenfor, er den anden regel, der bruges i denne øvelse, højere på listen og har derfor forrang over den første regel. Som følge heraf er baggrundsfarven for celle B5 grøn.
Som standard går nye regler til toppen af listen; for at ændre rækkefølgen, brug Op og Ned pileknapperne i dialogboksen.
Anvendelse af ikke-modstridende regler
Hvis to eller flere betingede formateringsregler ikke er i konflikt, anvendes begge, når betingelsen, som hver regel tester, bliver sand.
Hvis den første betingede formateringsregel i vores eksempel formaterede celleområdet B2:B5 med en orange kant i stedet for en orange baggrundsfarve, ville de to betingede formateringsregler ikke konflikt, da begge formater kan anvendes uden at forstyrre det andet.
Betinget formatering vs. almindelig formatering
I tilfælde af konflikter mellem betingede formateringsregler og manuelt anvendte formateringsindstillinger, har betinget formateringsregel altid forrang og vil blive anvendt i stedet for eventuelle manuelt tilføjede formateringsindstillinger.
