Betinget formatering i Excel – detaljert med eksempler

Hvis du trenger å presentere informasjon visuelt, er det veldig praktisk å gjøre dette ved å bruke betinget formatering. Brukeren som bruker denne tjenesten sparer enormt mye energi og tid. For å få den nødvendige informasjonen er en rask oversikt over filen nok.

Hvordan gjøre betinget formatering i Excel

Du kan finne "Betinget formatering" på den aller første fanen på båndet ved å gå til "Stiler" -delen. 

Deretter må du finne pilikonet litt til høyre med øyet, flytte markøren til det. Deretter åpnes innstillingene, hvor du fleksibelt kan konfigurere alle nødvendige parametere. 

Deretter må du velge riktig operator for å sammenligne ønsket variabel med et tall. Det er fire sammenligningsoperatorer – større enn, mindre enn, lik og mellom. De er oppført i regelmenyen. 

Deretter spesifiserte vi en serie tall i området A1:A11.

La oss formatere et passende datasett. Deretter åpner vi innstillingsmenyen "Betinget formatering" og setter de nødvendige datavalgkriteriene. For oss vil vi for eksempel velge kriteriet "Mer". 

Et vindu med et sett med alternativer åpnes. I venstre del av vinduet er det et felt der du må spesifisere tallet 15. I høyre del er metoden for utheving av informasjon angitt, forutsatt at den oppfyller det tidligere spesifiserte kriteriet. Resultatet vil vises umiddelbart.

Fullfør deretter konfigurasjonen med OK-knappen.

Hva er betinget formatering for?

Til en viss grad kan vi si at det er et verktøy, etter å ha lært noe som aldri vil bli det samme å jobbe med Excel. Dette er fordi det gjør livet mye enklere. I stedet for manuelt å angi formateringen av en celle som samsvarer med en bestemt tilstand hver gang, og også prøve å sjekke det selv for samsvar med dette kriteriet, trenger du bare å angi innstillingene en gang, og så vil Excel gjøre alt selv.

Du kan for eksempel få alle celler som inneholder et tall større enn 100 til å bli røde. Eller finn ut hvor mange dager som er igjen til neste betaling, og farge deretter grønt de cellene der fristen fortsatt ikke er langt nok unna. 

La oss ta et eksempel.

Denne tabellen viser varelageret som er på lager. Den har kolonner. som produkt, gjennomsnittlig salg (målt i enheter per uke), lager og hvor mange uker som er igjen av dette produktet.

Videre er oppgaven til innkjøpssjefen å bestemme de stillingene hvis påfylling er nødvendig. For å gjøre dette, må du se på den fjerde kolonnen fra venstre, som registrerer varelageret etter uke.

Anta at kriteriet for å bestemme årsaken til panikk er mindre enn 3 ukers inventar. Dette indikerer at vi må forberede en ordre. Hvis beholdningen av varer er mindre enn to uker, indikerer dette behovet for å raskt legge inn en bestilling. Hvis tabellen inneholder et stort antall stillinger, er det ganske problematisk å manuelt sjekke hvor mange uker som er igjen. Selv om du bruker søket. Og la oss nå se hvordan bordet ser ut, som fremhever knappe varer i rødt.

Og faktisk er det mye lettere å navigere. 

Riktignok er dette eksemplet pedagogisk, noe som er noe forenklet sammenlignet med det virkelige bildet. Og de sparte sekundene og minuttene med regelmessig bruk av slike tabeller blir til timer. Nå er det bare nok å se på tabellen for å forstå hvilke varer som er mangelvare, og ikke bruke timer på å analysere hver celle (hvis det er tusenvis av slike varer).

Hvis det er "gule" varer, må du begynne å kjøpe dem. Hvis den tilsvarende posisjonen er rød, må du gjøre det umiddelbart. 

Etter verdien av en annen celle

La oss nå ta en titt på følgende praktiske eksempel.

Anta at vi har en slik tabell, og vi står overfor oppgaven med å markere rader som inneholder visse verdier. 

Så hvis prosjektet fortsatt utføres (det vil si at det ennå ikke er fullført, derfor er det merket med bokstaven "P"), må vi gjøre bakgrunnen rød. Gjennomførte prosjekter er markert med grønt.

Handlingssekvensen vår i denne situasjonen vil være som følger:

1. Velg et verdiområde. 
2. Klikk på knappen "Betinget formatering" - "Opprett regel".
3. Vær oppmerksom på at i vårt tilfelle må du bruke formelen i form av en regel. Deretter bruker vi funksjonen IFfor å markere samsvarende linjer. 

Deretter fyller du ut linjene som vist på dette bildet.

Viktig! Strenger må refereres absolutt. Hvis vi refererer til en celle, er den i dette tilfellet blandet (med kolonnefiksering).

Tilsvarende opprettes en regel for de arbeidsprosjektene som hittil ikke er fullført. 

Slik ser kriteriene våre ut.

Og til slutt har vi en slik database som et resultat.

Flere betingelser

For klarhetens skyld, la oss ta området A1:A11 fra tabellen for å demonstrere hvordan du i et virkelig praktisk eksempel kan bruke flere forhold for å bestemme celleformateringen.

Selve betingelsene er som følger: hvis tallet i cellen overskrider verdien 6, er det uthevet i rødt. Hvis den er grønn, er fargen grønn. Og til slutt vil de største tallene, mer enn 20, markeres med gult. 

Det er flere måter betinget formatering utføres på i henhold til flere regler.

1. Metode 1. Vi velger vårt område, hvoretter, i innstillingsmenyen for betinget formatering, velg en slik cellevalgsregel som "Mer". Tallet 6 er skrevet til venstre, og formatering er satt til høyre. I vårt tilfelle skal fyllingen være rød. Etter det gjentas syklusen to ganger, men andre parametere er allerede satt - henholdsvis mer enn 10 og grønn farge og mer enn 20 og gul farge. Du vil få et slikt resultat.
   

   

2. Metode 2. Gå til hovedinnstillingsmenyen til Excel-verktøyet "Betinget formatering". Der finner vi menyen "Opprett regel" og venstreklikker på dette elementet.
   

   

    Etter det, Velg deretter elementet "Bruk formel ..." (uthevet i figuren med en rød ramme) og angi den første betingelsen. Etter det klikker du på OK. Og så gjentas syklusen for påfølgende forhold på samme måte som beskrevet ovenfor, men formelen brukes.

   

Det er viktig å ta hensyn til dette. I vårt eksempel samsvarer noen celler med flere kriterier samtidig. Excel løser denne konflikten som følger: regelen ovenfor brukes først. 

Og her er et skjermbilde for bedre forståelse.

For eksempel har vi tallet 24. Det oppfyller alle tre betingelsene samtidig. I dette tilfellet vil det være en fylling som oppfyller den første betingelsen, til tross for at den faller mer under den tredje. Derfor, hvis det er viktig at tall over 20 fylles med gult, må den tredje betingelsen settes på første plass. 

Betinget datoformatering

La oss nå se hvordan betinget formatering fungerer med datoer. Vi har et så kult utvalg.

I dette tilfellet må du angi en betinget formateringsregel som "Dato".

Etter det åpnes en dialogboks med et helt sett med betingelser. Du kan bli kjent med dem i detalj i dette skjermbildet (de er oppført på venstre side av skjermen i form av en liste).

Vi må bare velge den riktige og klikke på "OK" -knappen.

Vi ser at de datoene som refererer til den siste uken på tidspunktet dette området ble trukket, ble uthevet i rødt. 

Ved hjelp av formler

Settet med standard betinget formateringsregler er ganske stort. Men situasjonene er forskjellige, og standardlisten er kanskje ikke nok. I dette tilfellet kan du lage din egen formateringsregel ved å bruke en formel. 

For å gjøre dette, klikk på "Opprett regel"-knappen i menyen for betinget formatering, og velg deretter elementet som vises på skjermbildet.

Rader (etter celleverdi)

Anta at vi må markere raden som inneholder en celle med en viss verdi. I dette tilfellet må vi utføre samme sekvens av handlinger som ovenfor, men for strenger. Det vil si at du kan velge hvilket område den betingede formateringen skal gjelde for. 

Hvordan lage en regel

For å opprette en regel må du bruke det aktuelle alternativet i delen "Betinget formatering". La oss se på en bestemt sekvens av handlinger. 

Først må du finne elementet "Betinget formatering" på båndet på fanen "Hjem". Det kan gjenkjennes av de karakteristiske flerfargede cellene i forskjellige størrelser med et bilde ved siden av med et utstreket likhetstegn. 

Det er en "Opprett regel"-knapp. Du kan også klikke på knappen "Administrer regler", hvoretter en dialogboks åpnes, der du også kan opprette en regel ved å klikke på den tilsvarende knappen i øvre venstre hjørne. 

Du kan også se i dette vinduet de reglene som allerede er tatt i bruk.

Regler for cellevalg

Ok, vi snakker så mye om hvordan man lager regler, men hva er det? Cellevalgsregelen refererer til funksjonene som tas i betraktning av programmet for å bestemme hvordan de skal formateres cellene som passer til dem. For eksempel kan betingelser være større enn et visst tall, mindre enn et visst tall, en formel og så videre. Det er et helt sett med dem. Du kan gjøre deg kjent med dem og øve på bruken "i sandkassen". 

Formatere alle celler basert på verdiene deres

En person kan uavhengig angi rekkevidden: en hel linje, hele dokumentet eller en enkelt celle. Programmet kan kun fokusere på verdien av én celle. Dette gir fleksibilitet til prosessen. 

Formater bare unike eller dupliserte celler

Det er et stort antall regler direkte koblet inn i programmet. Du kan bli kjent med dem i menyen "Betinget formatering". Spesielt for at formatering skal gjelde eksklusivt for dupliserte verdier eller bare for unike, er det et spesielt alternativ - "Dupliserte verdier".

Hvis du velger dette elementet, åpnes et vindu med innstillinger, hvor du også kan velge det motsatte alternativet – velg kun unike verdier. 

Formater verdier i området under og over gjennomsnittet

For å formatere verdier under eller over gjennomsnittet, er det også et spesielt alternativ i samme meny. Men du må velge en annen undermeny - "Regler for valg av første og siste verdi". 

Formaterer kun den første og siste verdien

I samme undermeny er det mulig å markere bare den første og siste verdien med en spesiell farge, skrifttype og andre formateringsmetoder. I henhold til standarden er det et alternativ for å velge de første og siste ti elementene, samt 10% av det totale antallet celler som er inkludert i dette området. Men brukeren kan uavhengig bestemme hvor mange celler han trenger å velge.

Formaterer kun celler med spesifikt innhold

For å formatere bare celler med spesifikt innhold, må du velge formateringsregelen Lik med eller Tekst inneholder. Forskjellen mellom dem er at i det første tilfellet må strengen matche kriteriet helt, og i det andre bare delvis. 

Som du kan se, er betinget formatering en multifunksjonell funksjon i Excel-programmet som gir et stort antall fordeler til personen som har mestret det. Vi vet at ved første øyekast kan alt dette virke komplisert. Men faktisk trekkes hendene til det så snart det ser ut til å markere et bestemt tekststykke i rødt (eller formatere det på annen måte), basert på innholdet eller i samsvar med et annet kriterium. Denne funksjonen er inkludert i det grunnleggende settet med Excel-kunnskap, uten det er til og med amatørarbeid med regneark og databaser umulig.