Villkorlig formatering i Excel – i detalj med exempel

Om du behöver visa information visuellt är det väldigt bekvämt att göra detta med villkorlig formatering. Användaren som använder denna tjänst sparar enormt mycket energi och tid. För att få nödvändig information räcker det med en snabb visning av filen.

Hur man gör villkorlig formatering i Excel

Du kan hitta "Villkorlig formatering" på den allra första fliken på menyfliksområdet genom att gå till avsnittet "Stilar". 

Därefter måste du hitta pilikonen lite till höger med ögat, flytta markören till den. Då öppnas inställningarna, där du flexibelt kan konfigurera alla nödvändiga parametrar. 

Därefter måste du välja lämplig operator för att jämföra den önskade variabeln med ett tal. Det finns fyra jämförelseoperatorer – större än, mindre än, lika med och mellan. De är listade i reglermenyn. 

Därefter specificerade vi en serie siffror i intervallet A1:A11.

Låt oss formatera en lämplig datamängd. Sedan öppnar vi inställningsmenyn "Villkorlig formatering" och ställer in de nödvändiga dataurvalskriterierna. När det gäller oss, till exempel, kommer vi att välja kriteriet "Mer". 

Ett fönster med en uppsättning alternativ öppnas. I den vänstra delen av fönstret finns ett fält där du måste ange siffran 15. I den högra delen anges metoden för att markera information, förutsatt att den uppfyller det tidigare angivna kriteriet. Resultatet kommer att visas omedelbart.

Slutför sedan konfigurationen med OK-knappen.

Vad är villkorlig formatering till för?

Till viss del kan vi säga att det är ett verktyg, efter att ha lärt sig vilket att arbeta med Excel aldrig kommer att bli detsamma. Detta för att det gör livet mycket lättare. Istället för att manuellt ställa in formateringen av en cell som matchar ett visst villkor varje gång, och även försöka kontrollera det själv för att uppfylla detta kriterium, behöver du bara ställa in inställningarna en gång, och sedan kommer Excel att göra allt själv.

Du kan till exempel göra att alla celler som innehåller ett nummer större än 100 blir röda. Eller bestäm hur många dagar som är kvar till nästa betalning och färga sedan grönt de celler där deadline fortfarande inte är tillräckligt långt borta. 

Låt oss ta ett exempel.

Denna tabell visar det lager som finns i lager. Den har kolumner. som produkt, genomsnittlig försäljning (mätt i enheter per vecka), lager och hur många veckor kvar av denna produkt.

Vidare är inköpschefens uppgift att bestämma de positioner vars påfyllning är nödvändig. För att göra detta måste du titta på den fjärde kolumnen från vänster, som registrerar varulagret per vecka.

Antag att kriteriet för att fastställa orsaken till panik är mindre än 3 veckors inventering. Detta indikerar att vi måste förbereda en beställning. Om lagret av varor är mindre än två veckor, indikerar detta behovet av att snabbt lägga en beställning. Om tabellen innehåller ett stort antal positioner är det ganska problematiskt att manuellt kontrollera hur många veckor som återstår. Även om du använder sökningen. Och nu ska vi se hur bordet ser ut, som lyfter fram knappa varor i rött.

Och det är faktiskt mycket lättare att navigera. 

Det är sant att detta exempel är pedagogiskt, vilket är något förenklat jämfört med den verkliga bilden. Och de sparade sekunderna och minuterna med regelbunden användning av sådana tabeller förvandlas till timmar. Nu räcker det bara att titta på tabellen för att förstå vilka varor som är en bristvara, och inte spendera timmar på att analysera varje cell (om det finns tusentals sådana varor).

Om det finns "gula" varor måste du börja köpa dem. Om motsvarande position är röd, måste du göra det omedelbart. 

Efter värdet av en annan cell

Låt oss nu ta en titt på följande praktiska exempel.

Anta att vi har en sådan tabell och vi står inför uppgiften att markera rader som innehåller vissa värden. 

Så om projektet fortfarande utförs (det vill säga det har ännu inte slutförts, därför är det markerat med bokstaven "P"), måste vi göra dess bakgrund röd. Genomförda projekt är markerade med grönt.

Vår sekvens av åtgärder i denna situation kommer att vara följande:

1. Välj ett värdeintervall. 
2. Klicka på knappen "Villkorlig formatering" - "Skapa regel".
3. Observera att i vårt fall måste du tillämpa formeln i form av en regel. Därefter använder vi funktionen IFför att markera matchande linjer. 

Fyll sedan i raderna som visas på den här bilden.

Viktigt! Strängar måste refereras absolut. Om vi ​​hänvisar till en cell, så är den i detta fall blandad (med kolumnfixering).

På samma sätt skapas en regel för de arbetsprojekt som hittills inte har slutförts. 

Så här ser våra kriterier ut.

Och slutligen har vi en sådan databas som ett resultat.

Flera villkor

För tydlighetens skull, låt oss ta intervallet A1:A11 från tabellen för att visa hur du i ett verkligt praktiskt exempel kan använda flera villkor för att bestämma cellformateringen.

Villkoren i sig är följande: om siffran i cellen överstiger värdet 6, markeras den i rött. Om den är grön är färgen grön. Och slutligen kommer de största siffrorna, fler än 20, att markeras med gult. 

Det finns flera sätt hur villkorlig formatering utförs enligt flera regler.

1. Metod 1. Vi väljer vårt intervall, varefter, i menyn för villkorlig formatering, välj en sådan cellvalsregel som "Mer". Siffran 6 skrivs till vänster och formateringen ställs in till höger. I vårt fall ska fyllningen vara röd. Därefter upprepas cykeln två gånger, men andra parametrar är redan inställda - mer än 10 och grön färg respektive mer än 20 och gul färg. Du kommer att få ett sådant resultat.
   

   

2. Metod 2. Gå till huvudinställningsmenyn i Excel-verktyget "Villkorlig formatering". Där hittar vi menyn "Skapa regel" och vänsterklickar på detta objekt.
   

   

    Därefter, välj sedan objektet "Använd formel ..." (markerat i figuren med en röd ram) och ställ in det första villkoret. Efter det, klicka på OK. Och sedan upprepas cykeln för efterföljande förhållanden på samma sätt som beskrivits ovan, men formeln används.

   

Det är viktigt att ta hänsyn till detta. I vårt exempel matchar vissa celler flera kriterier samtidigt. Excel löser denna konflikt enligt följande: regeln ovan tillämpas först. 

Och här är en skärmdump för bättre förståelse.

Vi har till exempel siffran 24. Den uppfyller alla tre villkoren samtidigt. I det här fallet kommer det att finnas en fyllning som uppfyller det första villkoret, trots att det faller mer under det tredje. Därför, om det är viktigt att nummer över 20 fylls med gult, måste det tredje villkoret sättas på första plats. 

Villkorlig datumformatering

Låt oss nu se hur villkorlig formatering fungerar med datum. Vi har ett så häftigt utbud.

I det här fallet måste du ställa in en villkorlig formateringsregel som "Datum".

Därefter öppnas en dialogruta med en hel uppsättning villkor. Du kan bekanta dig med dem i detalj i den här skärmdumpen (de listas på vänster sida av skärmen i form av en lista).

Vi måste bara välja den lämpliga och klicka på knappen "OK".

Vi ser att de datum som hänvisar till den senaste veckan då detta intervall drogs var markerade i rött. 

Använda formler

Uppsättningen standardregler för villkorlig formatering är ganska stor. Men situationerna är olika och standardlistan kanske inte räcker. I det här fallet kan du skapa din egen formateringsregel med hjälp av en formel. 

För att göra detta, klicka på knappen "Skapa regel" i menyn för villkorlig formatering och välj sedan objektet som visas på skärmdumpen.

Rader (efter cellvärde)

Anta att vi behöver markera raden som innehåller en cell med ett visst värde. I det här fallet måste vi utföra samma sekvens av åtgärder som ovan, men för strängar. Det vill säga att du kan välja vilket intervall den villkorliga formateringen ska gälla för. 

Hur man skapar en regel

För att skapa en regel måste du använda lämpligt alternativ i avsnittet "Villkorlig formatering". Låt oss titta på en specifik sekvens av åtgärder. 

Först måste du hitta objektet "Villkorlig formatering" på menyfliksområdet på fliken "Hem". Det kan kännas igen av de karakteristiska flerfärgade cellerna i olika storlekar med en bild bredvid med ett överstruket likhetstecken. 

Det finns en "Skapa regel"-knapp. Du kan också klicka på knappen "Hantera regler", varefter en dialogruta öppnas, genom vilken du också kan skapa en regel genom att klicka på motsvarande knapp i det övre vänstra hörnet. 

Du kan också se de regler som redan har tillämpats i det här fönstret.

Regler för cellval

Okej, vi pratar så mycket om hur man skapar regler, men vad är det? Cellvalsregeln hänvisar till de funktioner som tas i beaktande av programmet för att bestämma hur de celler som passar dem ska formateras. Till exempel kan villkor vara större än ett visst tal, mindre än ett visst tal, en formel och så vidare. Det finns en hel uppsättning av dem. Du kan bekanta dig med dem och öva på hur de används "i sandlådan". 

Formatera alla celler baserat på deras värden

En person kan självständigt ställa in intervallet: en hel rad, hela dokumentet eller en enda cell. Programmet kan bara fokusera på värdet av en cell. Detta ger flexibilitet till processen. 

Formatera endast unika eller dubbletter av celler

Det finns ett stort antal regler direkt kopplade till programmet. Du kan bekanta dig med dem i menyn "Villkorlig formatering". I synnerhet, för att formatering ska gälla exklusivt för dubbletter av värden eller bara för unika, finns det ett speciellt alternativ - "Duplicera värden".

Om du väljer det här objektet öppnas ett fönster med inställningar där du också kan välja det motsatta alternativet – välj endast unika värden. 

Formatera värden i intervallet under och över genomsnittet

För att formatera värden under eller över genomsnittet finns det också ett speciellt alternativ i samma meny. Men du måste välja en annan undermeny - "Regler för val av första och sista värden". 

Formaterar endast det första och sista värdet

I samma undermeny är det möjligt att markera endast de första och sista värdena med en speciell färg, typsnitt och andra formateringsmetoder. Enligt standarden finns det ett alternativ att välja de första och sista tio elementen, samt 10% av det totala antalet celler som ingår i detta intervall. Men användaren kan självständigt bestämma hur många celler han behöver välja.

Formaterar endast celler med specifikt innehåll

Om du bara vill formatera celler med specifikt innehåll måste du välja formateringsregeln Lika med eller Text innehåller. Skillnaden mellan dem är att i det första fallet måste strängen matcha kriteriet helt och i det andra endast delvis. 

Som du kan se är villkorlig formatering en multifunktionell funktion i Excel-programmet som ger ett stort antal fördelar för den person som har behärskat det. Vi vet att allt detta vid första anblicken kan verka komplicerat. Men i själva verket dras händerna till det så snart det verkar markera ett visst stycke text i rött (eller formatera det på något annat sätt), baserat på dess innehåll eller i enlighet med ett annat kriterium. Denna funktion ingår i den grundläggande uppsättningen av Excel-kunskaper, utan det är ens amatörarbete med kalkylblad och databaser omöjligt.