Betinget formatering i Excel – detaljeret med eksempler

Hvis du har brug for visuelt at præsentere information, så er det meget praktisk at gøre dette ved hjælp af betinget formatering. Brugeren, der bruger denne service, sparer en enorm mængde energi og tid. For at få de nødvendige oplysninger er et hurtigt overblik over filen nok.

Sådan laver du betinget formatering i Excel

Du kan finde "Betinget formatering" på den allerførste fane på båndet ved at gå til sektionen "Styles". 

Dernæst skal du finde pileikonet lidt til højre med øjet, flytte markøren til det. Så åbnes indstillingerne, hvor du fleksibelt kan konfigurere alle de nødvendige parametre. 

Dernæst skal du vælge den relevante operator for at sammenligne den ønskede variabel med et tal. Der er fire sammenligningsoperatorer - større end, mindre end, lig med og mellem. De er angivet i reglermenuen. 

Dernæst specificerede vi en række tal i området A1:A11.

Lad os formatere et passende datasæt. Derefter åbner vi indstillingsmenuen "Betinget formatering" og indstiller de nødvendige dataudvælgelseskriterier. I tilfælde af os vil vi for eksempel vælge kriteriet "Mere". 

Et vindue med et sæt muligheder åbnes. I venstre del af vinduet er der et felt, hvor du skal angive tallet 15. I højre del er metoden til at fremhæve information angivet, forudsat at den opfylder det tidligere angivne kriterium. Resultatet vil blive vist med det samme.

Fuldfør derefter konfigurationen med knappen OK.

Hvad er betinget formatering til?

Til en vis grad kan vi sige, at det er et værktøj, efter at have lært, hvilket arbejde med Excel aldrig bliver det samme. Det er fordi det gør livet meget lettere. I stedet for manuelt at indstille formateringen af ​​en celle, der matcher en bestemt betingelse hver gang, og også prøve at kontrollere det selv for overholdelse af dette kriterium, skal du bare indstille indstillingerne én gang, og så vil Excel gøre alt selv.

For eksempel kan du få alle celler, der indeholder et tal større end 100, til at blive røde. Eller bestem, hvor mange dage der er tilbage til næste betaling, og farve derefter de celler, hvor fristen stadig ikke er langt nok væk, med grøn. 

Lad os tage et eksempel.

Denne tabel viser den beholdning, der er på lager. Den har søjler. som produkt, gennemsnitlig salg (målt i enheder pr. uge), lagerbeholdning og hvor mange uger der er tilbage af dette produkt.

Endvidere er indkøbschefens opgave at bestemme de stillinger, hvis genopfyldning er nødvendig. For at gøre dette skal du se på den fjerde kolonne fra venstre, som registrerer varebeholdningen pr. uge.

Antag, at kriteriet for at bestemme årsagen til panik er mindre end 3 ugers inventar. Dette indikerer, at vi skal forberede en ordre. Hvis varebeholdningen er mindre end to uger, indikerer dette behovet for hurtigst muligt at afgive en ordre. Hvis tabellen indeholder et stort antal positioner, er det ret problematisk at manuelt kontrollere, hvor mange uger der er tilbage. Også selvom du bruger søgningen. Og lad os nu se, hvordan bordet ser ud, som fremhæver sparsomme varer med rødt.

Og faktisk er det meget nemmere at navigere. 

Sandt nok er dette eksempel pædagogisk, hvilket er noget forenklet i forhold til det virkelige billede. Og de sparede sekunder og minutter med regelmæssig brug af sådanne tabeller bliver til timer. Nu er det bare nok at se på tabellen for at forstå, hvilke varer der mangler, og ikke bruge timer på at analysere hver celle (hvis der er tusindvis af sådanne varer).

Hvis der er "gule" varer, så skal du begynde at købe dem. Hvis den tilsvarende position er rød, skal du gøre det med det samme. 

Efter værdi af en anden celle

Lad os nu tage et kig på følgende praktiske eksempel.

Antag, at vi har sådan en tabel, og vi står over for opgaven med at fremhæve rækker, der indeholder bestemte værdier. 

Så hvis projektet stadig udføres (det vil sige, det er endnu ikke afsluttet, derfor er det markeret med bogstavet "P"), så skal vi gøre dets baggrund rød. Afsluttede projekter er markeret med grønt.

Vores rækkefølge af handlinger i denne situation vil være som følger:

1. Vælg en række værdier. 
2. Klik på knappen "Betinget formatering" - "Opret regel".
3. Bemærk venligst, at du i vores tilfælde skal anvende formlen i form af en regel. Dernæst bruger vi funktionen IFfor at fremhæve matchende linjer. 

Udfyld derefter linjerne som vist på dette billede.

Vigtigt! Strings skal refereres absolut. Hvis vi henviser til en celle, så er den i dette tilfælde blandet (med kolonnefiksering).

Tilsvarende oprettes en regel for de arbejdsprojekter, der ikke er afsluttet til dato. 

Sådan ser vores kriterier ud.

Og endelig har vi en sådan database som resultat.

Flere betingelser

For klarhedens skyld, lad os tage området A1:A11 fra tabellen for at demonstrere, hvordan du i et rigtigt praktisk eksempel kan bruge flere betingelser til at bestemme celleformateringen.

Selve betingelserne er som følger: Hvis tallet i cellen overstiger værdien 6, er det fremhævet med rødt. Hvis den er grøn, så er farven grøn. Og endelig vil de største tal, mere end 20, blive fremhævet med gult. 

Der er flere måder, hvordan betinget formatering udføres efter flere regler.

1. Metode 1. Vi vælger vores område, hvorefter i menuen for betingede formateringsindstillinger skal du vælge en sådan cellevalgsregel som "Mere". Tallet 6 er skrevet til venstre, og formatering er indstillet til højre. I vores tilfælde skal fyldningen være rød. Derefter gentages cyklussen to gange, men andre parametre er allerede indstillet - henholdsvis mere end 10 og grøn farve og mere end 20 og gul farve. Du vil få et sådant resultat.
   

   

2. Metode 2. Gå til hovedindstillingsmenuen i Excel-værktøjet "Betinget formatering". Der finder vi menuen "Opret regel" og venstreklikker på dette punkt.
   

   

    Derefter skal du vælge punktet "Brug formel ..." (fremhævet i figuren med en rød ramme) og indstille den første betingelse. Klik derefter på OK. Og så gentages cyklussen for efterfølgende betingelser på samme måde som beskrevet ovenfor, men formlen bruges.

   

Det er vigtigt at tage højde for dette. I vores eksempel matcher nogle celler flere kriterier på én gang. Excel løser denne konflikt som følger: reglen ovenfor anvendes først. 

Og her er et skærmbillede for bedre forståelse.

For eksempel har vi tallet 24. Det opfylder alle tre betingelser på samme tid. I dette tilfælde vil der være en fyldning, der opfylder den første betingelse, på trods af at den falder mere ind under den tredje. Hvis det derfor er vigtigt, at tal over 20 udfyldes med gult, skal den tredje betingelse sættes på førstepladsen. 

Betinget datoformatering

Lad os nu se, hvordan betinget formatering fungerer med datoer. Vi har sådan et fedt udvalg.

I dette tilfælde skal du indstille en betinget formateringsregel såsom "Dato".

Derefter åbnes en dialogboks med et helt sæt betingelser. Du kan stifte nærmere bekendtskab med dem i dette skærmbillede (de er anført i venstre side af skærmen i form af en liste).

Vi skal bare vælge den passende og klikke på knappen "OK".

Vi ser, at de datoer, der refererer til den sidste uge på det tidspunkt, hvor dette interval blev tegnet, var fremhævet med rødt. 

Brug af formler

Sættet af standard betingede formateringsregler er ret stort. Men situationerne er anderledes, og standardlisten er måske ikke nok. I dette tilfælde kan du oprette din egen formateringsregel ved hjælp af en formel. 

For at gøre dette skal du klikke på knappen "Opret regel" i menuen for betinget formatering og derefter vælge det element, der vises på skærmbilledet.

Rækker (efter celleværdi)

Antag, at vi skal fremhæve den række, der indeholder en celle med en bestemt værdi. I dette tilfælde skal vi udføre den samme rækkefølge af handlinger som ovenfor, men for strenge. Det vil sige, at du kan vælge, hvilket område den betingede formatering skal gælde for. 

Sådan opretter du en regel

For at oprette en regel skal du bruge den relevante mulighed i afsnittet "Betinget formatering". Lad os se på en bestemt rækkefølge af handlinger. 

Først skal du finde punktet "Betinget formatering" på båndet på fanen "Hjem". Det kan genkendes på de karakteristiske flerfarvede celler i forskellige størrelser med et billede ved siden af ​​med et overstreget lighedstegn. 

Der er en "Opret regel" knap. Du kan også klikke på knappen "Administrer regler", hvorefter der åbnes en dialogboks, hvorigennem du også kan oprette en regel ved at klikke på den tilsvarende knap i øverste venstre hjørne. 

Du kan også i dette vindue se de regler, der allerede er blevet anvendt.

Cellevalgsregler

Okay, vi taler så meget om, hvordan man laver regler, men hvad er det? Cellevalgsreglen henviser til de funktioner, der tages i betragtning af programmet for at beslutte, hvordan de celler, der passer til dem, skal formateres. For eksempel kan betingelser være større end et bestemt tal, mindre end et bestemt tal, en formel og så videre. Der er et helt sæt af dem. Du kan sætte dig ind i dem og øve dig i deres brug "i sandkassen". 

Formatering af alle celler baseret på deres værdier

En person kan selvstændigt indstille området: en hel linje, hele dokumentet eller en enkelt celle. Programmet kan kun fokusere på værdien af ​​én celle. Dette tilføjer fleksibilitet til processen. 

Formater kun unikke eller dublerede celler

Der er et stort antal regler direkte forbundet til programmet. Du kan stifte bekendtskab med dem i menuen "Betinget formatering". Især for at formatering udelukkende skal gælde for duplikerede værdier eller kun for unikke, er der en særlig mulighed - "Duplicate values".

Hvis du vælger dette punkt, åbnes et vindue med indstillinger, hvor du også kan vælge den modsatte mulighed – vælg kun unikke værdier. 

Formater værdier i området under og over gennemsnittet

For at formatere værdier under eller over gennemsnittet er der også en særlig mulighed i samme menu. Men du skal vælge en anden undermenu - "Regler for valg af første og sidste værdi". 

Formaterer kun den første og sidste værdi

I samme undermenu er det muligt kun at fremhæve den første og sidste værdi med en speciel farve, skrifttype og andre formateringsmetoder. I henhold til standarden er der mulighed for at vælge de første og sidste ti elementer samt 10% af det samlede antal celler, der er inkluderet i dette område. Men brugeren kan uafhængigt bestemme, hvor mange celler han skal vælge.

Formaterer kun celler med specifikt indhold

For kun at formatere celler med specifikt indhold, skal du vælge formateringsreglen Lige til eller Tekst indeholder. Forskellen mellem dem er, at i det første tilfælde skal strengen matche kriteriet fuldstændigt, og i det andet kun delvist. 

Som du kan se, er betinget formatering en multifunktionel funktion i Excel-programmet, der giver et stort antal fordele til den person, der har mestret det. Vi ved, at alt dette ved første øjekast kan virke kompliceret. Men faktisk trækkes hænder til det, så snart det ser ud til at fremhæve et bestemt stykke tekst i rødt (eller formatere det på anden måde), baseret på dets indhold eller i overensstemmelse med et andet kriterium. Denne funktion er inkluderet i det grundlæggende sæt af Excel-viden, uden det er selv amatørarbejde med regneark og databaser umuligt.