Genom att lägga till villkorad formatering i Excel kan du ange olika formateringsalternativ till en cell eller ett intervall av celler som uppfyller specifika villkor som du ställt in. Formateringsalternativen tillämpas endast när de valda cellerna uppfyller dessa inställda villkor.
Formateringsalternativen som kan användas inkluderar teckensnitt och bakgrundsfärgsändringar, typsnittstyp, cellgränser och tilläggsnummerformatering till data.
Excel har haft ett antal inbyggda alternativ för vanliga användningsvillkor som att hitta nummer som är större än eller mindre än ett visst värde eller att hitta siffror som är över eller under medelvärdet.
Förutom dessa förinställda alternativ är det också möjligt att skapa anpassade villkorliga formateringsregler med hjälp av Excel-formler för att testa för användarspecificerade villkor.
01 av 04Använda flera regler i Excel
Mer än en regel kan tillämpas på samma data för att testa för olika förhållanden. Budgetdata kan till exempel ha inställda villkor som gäller formatering ändras när vissa nivåer, till exempel 50%, 75% och 100% av den totala budgeten, spenderas.
Under sådana omständigheter bestämmer Excel först om de olika reglerna strider mot varandra, och i så fall följer programmet en bestämd prioriteringsordning för att bestämma vilken villkorlig formateringsregel som tillämpas på data.
Att hitta data som överstiger 25% och 50% ökar
I följande exempel tillämpas två anpassade villkorliga formateringsregler till intervallet cellerna B2 till B5.
- Den första regeln kontrollerar om uppgifterna i cellerna A2 till A5 är större än motsvarande värde i B2 till B5 med mer än 25%.
- Den andra regeln kontrollerar om samma data i A2: A5 överstiger motsvarande värde i B2: B5 med mer än 50%.
Som kan ses i bilden ovan, om någon av ovanstående villkor är sanna, bakgrundsfärgen hos cellen eller cellerna i intervallet B1: B4 kommer att ändras.
- För data där skillnaden är större än 25%, ändras cellens bakgrundsfärg till grön.
- Om skillnaden är större än 50% ändras cellens bakgrundsfärg till röd.
Reglerna som används för att uppnå denna uppgift kommer att anges med hjälp av den villkorliga formateringen Ny formateringsregel dialog ruta. Börja med att ange provdata i cellerna A1 till C5, vilket framgår av bilden ovan
I tredje steget av handledningen kommer att lägga till formler till celler C2: C4 som visar den exakta procentskillnaden mellan värdena i cellerna A2: A5 och B2: B5 för att kontrollera noggrannheten i de villkorliga formateringsreglerna.
02 av 04Ställa in villkorliga formateringsregler
Som nämnts kommer de villkorliga formateringsreglerna som kontrollerar de två villkoren att anges med hjälp av den villkorliga formateringen Ny formateringsregel dialog ruta. Låt oss börja med att ställa in vår anpassade villkorliga formatering:
Villkorlig formatering för att hitta större än 25 procent ökning
= (A2-B2) / A2> 25%
- Markera cellerna B2 till B5 i arbetsbladet.
- Klicka på Hem-fliken av band.
- Klicka på Villkorlig formatering ikon i band för att öppna rullgardinsmenyn.
- Välja Ny regel att öppna Ny formateringsregel dialog ruta.
- Från Stil: rutan, välj Klassisk alternativ.
- I den andra rullgardinsmenyn klickar du på det sista alternativetAnvänd en formel för att bestämma vilka celler som ska formateras.
- Skriv formel som tillhandahålls ovanför utrymmet.
- Klicka på Formatera med: rull ned och välj sedan Ljusröd fyllning med mörk röd text alternativ.
- Klick ok för att stänga dialogrutorna och återgå till kalkylbladet.
- Vid denna punkt, bakgrundsfärgen på cellerna B3 och B5 bör vara grön.
Villkorlig formatering för att hitta större än 50 procent ökning
= (A2-B2) / A2> 50%
Markera cellerna B2 till B5 i arbetsbladet.
- Klicka på Hem-fliken av band.
- Klicka på Villkorlig formatering ikon i band för att öppna rullgardinsmenyn.
- Välja Ny regel att öppna Ny formateringsregel dialog ruta.
- Från Stil: rutan, välj Klassisk alternativ.
- I den andra rullgardinsmenyn klickar du på det sista alternativetAnvänd en formel för att bestämma vilka celler som ska formateras.
- Skriv formel som tillhandahålls ovanför utrymmet.
- Klicka på Formatera med: rull ned och välj sedan Ljusgrön fyllning med mörkgrön text alternativ.
- Klick ok för att stänga dialogrutorna och återgå till kalkylbladet.
Bakgrundsfärgen på cell B3 bör fortfarande vara grön som anger att procentskillnaden mellan siffrorna i cellerna A3 och B3 är större än 25 procent men mindre än eller lika med 50 procent. Bakgrundsfärgen på cell B5 bör ändras till rött vilket indikerar att procentskillnaden mellan siffrorna i cellerna A5 och B5 är större än 50 procent.
03 av 04Kontrollera villkorliga formateringsregler
För att kontrollera att de villkorliga formateringsreglerna som anges är korrekta, kan vi ange formler i celler C2: C5 som kommer att beräkna exakt procentskillnaden mellan siffrorna i intervallet A2: A5 och B2: B5.
= (A2-B2) / A2
- Klicka på cell C2 för att göra den aktiva cellen.
- Skriv ovanstående formel och tryck på Stiga på tangent på tangentbordet.
- Svaret 10% ska visas i cell C2, vilket indikerar att numret i cell A2 är 10% större än numret i cell B2.
- Det kan vara nödvändigt att ändra formateringen på cell C2 för att visa svaret som en procentandel.
- Använd fylla handtaget att kopiera formeln från cell C2 till celler C3 till C5.
- Svaren till celler C3 till C5 bör vara 30%, 25% och 60%.
Svaren i dessa celler visar att de villkorliga formateringsreglerna som skapats är korrekta eftersom skillnaden mellan cellerna A3 och B3 är större än 25 procent och skillnaden mellan cellerna A5 och B5 är större än 50 procent.
Cell B4 ändrade inte färg eftersom skillnaden mellan celler A4 och B4 motsvarar 25 procent och vår villkorliga formateringsregel specificerade att en procentandel som var större än 25 procent var nödvändig för att bakgrundsfärgen skulle växla till grön.
04 av 04Order of Precedence for Conditional Formatting
När flera regler tillämpas på samma dataområde, bestämmer Excel först om reglerna strider mot varandra. Konflikterande regler är de där de valda formateringsalternativen för varje regel inte båda kan tillämpas på samma data.
I det exempel som används i denna handledning är reglerna i konflikt eftersom båda reglerna använder samma formateringsalternativ - det vill säga att ändra bakgrundscellens färg.
I den situation där den andra regeln är sann (skillnaden i värde är större än 50 procent mellan två celler) är den första regeln (skillnaden i värde större än 25 procent) också sant.
Excel: s förekomstordning
Eftersom en cell inte kan ha både en röd och grön bakgrund samtidigt måste Excel veta vilken villkorlig formateringsregel den ska gälla.
Vilken regel som tillämpas tillämpas av Excel: s prioritetsordning, där det anges att regeln som är högre i listan i dialogrutan Villkorlig formateringsregleradministratör har förrang.
Som visas i bilden ovan är den andra regeln som används i denna handledning högre i listan och har därför företräde framför den första regeln. Som ett resultat ändras bakgrundsfärgen på cell B5 till röd.
Som standard läggs nya regler överst i listan och har därför högre prioritet. För att ändra prioritetsordningen använder du Upp och ner pilknappar i dialogrutan som identifieras i bilden ovan.
Tillämpa icke-konflikterande regler
Om två eller flera villkorliga formateringsregler inte strider mot varandra tillämpas båda när tillståndet varje regel testar blir sant.
Om den första villkorliga formateringsregeln i vårt exempel formaterade cellområdet B2: B5 med en grön gräns istället för en grön bakgrundsfärg, skulle de två villkorliga formateringsreglerna inte vara konflikta eftersom båda formaten kan appliceras utan att störa den andra.
Som ett resultat, cell B5 skulle ha både en grön gräns och en röd bakgrundsfärg, eftersom skillnaden mellan siffrorna i cellerna A5 och B5 är större än både 25 och 50 procent.
Villkorlig formatering vs. Vanlig formatering
I händelse av konflikter mellan villkorliga formateringsregler och manuellt tillämpade formateringsalternativ har förordningsformateringsregeln alltid företräde och kommer att tillämpas istället för alla manuellt tillagda formateringsalternativ.
Om en gul bakgrundsfärg ursprungligen användes på cellerna B2 till B5 i exemplet, när de villkorliga formateringsreglerna endast lagts till cellerna B2 och B4 skulle vara gul. Eftersom de villkorliga formateringsreglerna gäller gäller för celler B3 och B5, deras bakgrundsfärger skulle ändras från gult till grönt och rött respektive.
