Tilføjelse af betinget formatering i Excel giver dig mulighed for at anvende forskellige formateringsindstillinger til en celle eller en række celler, der opfylder de specifikke betingelser, du har angivet. Formateringsindstillingerne anvendes kun, når de valgte celler opfylder disse indstillede betingelser.
De formateringsindstillinger, der kan anvendes, omfatter skrifttype- og baggrundsfarveændringer, skrifttypestile, cellegrænser og tilføjelse af nummerformatering til data.
Excel har haft en række 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, som er over eller under gennemsnitsværdien.
Ud over disse forudindstillede indstillinger er det også muligt at oprette brugerdefinerede betingede formateringsregler ved hjælp af Excel-formler til at teste for brugerspecificerede betingelser.
01 af 04Anvendelse af flere regler i Excel
Mere end én regel kan anvendes på de samme data for at teste for forskellige forhold. For eksempel kan budgetdata have betingelser, der gælder for formatering, når visse niveauer, f.eks. 50%, 75% og 100% af det samlede budget bruges.
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 forudbestemt rækkefølge for at bestemme hvilken betinget formateringregel der anvendes på dataene.
At finde data, der overstiger 25% og 50% stigninger
I det følgende eksempel vil der blive brugt to brugerdefinerede betingede formateringsregler til rækken af celler B2 til B5.
- Den første regel kontrollerer for at se om dataene i celler A2 til A5 er større end den tilsvarende værdi i B2 til B5 med mere end 25%.
- Den anden regel kontrollerer for at se 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, hvis en af de ovennævnte betingelser er sand, baggrundsfarven på cellen eller cellerne i området B1: B4 vil ændre sig.
- For data, hvor forskellen er større end 25%, ændres celle baggrundsfarven til grønt.
- Hvis forskellen er større end 50%, ændres celle baggrundsfarven til rød.
Reglerne, der bruges til at udføre denne opgave, indtastes ved hjælp af den betingede formatering Ny formatering regel dialog boks. Begynd ved at indtaste prøvedataene i cellerne A1 til C5 som det ses i billedet ovenfor
I tredje trin af øvelsen vil tilføje formler til celler C2: C4 der viser den nøjagtige procentforskel mellem værdierne i celler A2: A5 og B2: B5 for at kontrollere nøjagtigheden af de betingede formateringsregler.
02 af 04Indstilling af betingede formateringsregler
Som nævnt vil de betingede formateringsregler, der kontrollerer de to betingelser, indtastes ved hjælp af den betingede formatering Ny formatering regel dialog boks. Lad os begynde med at indstille vores tilpassede betingede formatering:
Conditional Formatering for at finde mere end 25 procent stigning
= (A2-B2) / A2> 25%
- Fremhæv celler B2 til B5 i regnearket.
- Klik på Startside fanen af bånd.
- Klik på Betinget formatering ikon i bånd for at åbne rullemenuen.
- Vælge Ny regel at åbne Ny formatering regel dialog boks.
- Fra Stil: boks, vælg klassisk mulighed.
- I den anden rullemenu skal du klikke på den sidste indstillingBrug en formel til at bestemme, hvilke celler der skal formateres.
- Indtast formel forudsat ovenfor i det udbudte rum.
- Klik på Format med: drop-down og vælg derefter Lysrød Fyld med mørk rød tekst mulighed.
- Klik Okay for at lukke dialogboksene og returnere til regnearket.
- På dette tidspunkt, baggrundsfarven på celler B3 og B5 bør være grøn
Betinget formatering for at finde større end 50 procent stigning
= (A2-B2) / A2> 50%
Fremhæv celler B2 til B5 i regnearket.
- Klik på Startside fanen af bånd.
- Klik på Betinget formatering ikon i bånd for at åbne rullemenuen.
- Vælge Ny regel at åbne Ny formatering regel dialog boks.
- Fra Stil: boks, vælg klassisk mulighed.
- I den anden rullemenu skal du klikke på den sidste indstillingBrug en formel til at bestemme, hvilke celler der skal formateres.
- Indtast formel forudsat ovenfor i det udbudte rum.
- Klik på Format med: drop-down og vælg derefter Lysegrøn Fyld med mørkegrøn tekst mulighed.
- Klik Okay for at lukke dialogboksene og returnere til regnearket.
Baggrundsfarven på celle B3 skal stadig være grøn, hvilket angiver, at procentforskellen mellem tallene i celler A3 og B3 er større end 25 procent, men mindre end eller lig med 50 procent. Baggrundsfarven på celle B5 bør ændres til rødt, hvilket angiver, at procentforskellen mellem tallene i cellerne A5 og B5 er større end 50 procent.
03 af 04Kontrol af betingede formateringsregler
For at kontrollere, at de indstillede betingede formateringsregler er korrekte, kan vi indtaste formler i celler C2: C5 der beregner den nøjagtige procentforskel mellem tallene i intervallerne A2: A5 og B2: B5.
= (A2-B2) / A2
- Klik på celle C2 for at gøre den til den aktive celle.
- Indtast ovenstående formel og tryk på Gå ind tast på tastaturet.
- Svaret 10% skal vises i celle C2, hvilket indikerer at nummeret i celle A2 er 10% større end nummeret i celle B2.
- Det kan være nødvendigt at ændre formateringen på celle C2 for at vise svaret som en procent.
- Brug fyld håndtag at kopiere formlen fra celle C2 til celler C3 til C5.
- Svarene til celler C3 til C5 bør være 30%, 25% og 60%.
Svarene i disse celler viser, at de betingede formateringsregler, der oprettes, er korrekte, da forskellen mellem celler A3 og B3 er større end 25 procent og forskellen mellem cellerne A5 og B5 er større end 50 procent.
Celle B4 ændrede ikke farve fordi forskellen mellem celler A4 og B4 svarer til 25 procent, og vores betingede formateringsregel angav, at en procentdel på over 25 procent var nødvendig for at baggrundsfarven skulle skifte til grønt.
04 af 04Ordre af præcedens for betinget formatering
Når flere regler anvendes på samme dataområde, afgør Excel først, om reglerne er i konflikt. Konfliktiske regler er dem, hvor de valgte formateringsmuligheder for hver regel ikke begge kan anvendes på de samme data.
I eksemplet, der bruges i denne vejledning, er reglerne i konflikt, da begge regler bruger den samme formateringsindstilling - det vil sige at ændre baggrundscellens farve.
I den situation, hvor den anden regel er sand (differencen i værdi er større end 50 procent mellem to celler), er den første regel (differencen i værdi større end 25 procent) også sand.
Excel's præcedensorden
Da en celle ikke kan have både en rød og grøn baggrund på samme tid, skal Excel vide, hvilken betinget formatteringsregel den skal gælde.
Hvilken regel der anvendes, bestemmes af Excel's prioritetsordre, der angiver, at reglen, der er højere i listen i dialogboksen Betinget formatering Reglerstyring, har forrang.
Som vist i billedet ovenfor er den anden regel, der bruges i denne vejledning, højere i listen og har derfor forrang over den første regel. Som følge heraf ændres baggrundsfarven på celle B5 til rød.
Som standard tilføjes nye regler øverst på listen og har derfor højere prioritet. For at ændre rækkefølgen af forrang bruger du Op og ned piletasterne i dialogboksen som identificeret i billedet ovenfor.
Anvendelse af ikke-modstridende regler
Hvis to eller flere betingede formateringsregler ikke er i konflikt, anvendes begge, når betingelsen hver regel tester bliver sand.
Hvis den første betingede formateringsregel i vores eksempel formaterede rækkevidden af celler B2: B5 med en grøn grænse i stedet for en grøn baggrundsfarve vil de to betingede formateringsregler ikke være i konflikt, da begge formater kan anvendes uden at blande sig i den anden.
Som resultat, celle B5 ville have både en grøn grænse og en rød baggrundsfarve, da forskellen mellem tallene i cellerne A5 og B5 er større end både 25 og 50 procent.
Conditional Formatering vs Regular Formatering
I tilfælde af konflikter mellem betingede formateringsregler og manuelt anvendte formateringsindstillinger har forordnet formateringsregel altid forrang og vil blive anvendt i stedet for eventuelle manuelt tilføjede formateringsindstillinger.
Hvis en gul baggrundsfarve oprindeligt blev anvendt til celler B2 til B5 i eksemplet, når de betingede formatteringsregler blev tilføjet, kun celler B2 og B4 ville blive gul. Fordi de betingede formateringsregler, der er angivet, gælder for celler B3 og B5, vil deres baggrundsfarver ændres fra henholdsvis gul til grøn og rød.
