Vi markerer celler i excel slik at vi enkelt kan identifisere fokusområdene på data. Formatering av celler i excel er også gjort for å visualisere dataverdier basert på farge og stil. For eksempel kan det være lurt å fokusere på de tre minste verdiene i dataene dine. Det er vanskelig å gjøre i store data, men hvis du kunne fortelle excel å markere de 3 laveste verdiene, ville det være ganske enkelt. Heldigvis kan vi gjøre det ved å bruke betinget formateringsalternativ i Excel.
Det er lett å bare markere 3 minste verdi i excel, men det blir litt vanskelig når vi legger til kriterier. I denne artikkelen vil vi lære å markere de minste 3 verdiene med en tilstand.
Generisk formel i betinget formering
= OG (sjekk celle = kriterier, verdicelle<= LITEN (HVIS (kriterieområde = kriterier, verdiområde),3))
Sjekk celle: Det er cellen du vil sjekke om den kjøper de første kriteriene (kategorien).
Kriterier: Kriteriene du vil filtrere.
Verdicelle: Det er cellen som inneholder en numerisk verdi. Vi vil sammenligne den med tredje minste verdi i sin kategori, i verdiområde.
Kriterier: Det er det absolutte området som inneholder kriteriene.
Verdiområde: Området som inneholder alle verdier.
La oss se et eksempel for å gjøre ting klart.
Eksempel: Fremhev de tre nederste salgene utført av avdeling 1
Her har jeg en eksempelarbeidsfil som inneholder salg gjort av forskjellige avdelinger i forskjellige måneder.
Jeg vil markere de tre nederste salgene gjort av avdeling 1.
La oss identifisere variablene vi har her.
Sjekk celle: $ B2 (vi vil sjekke om B2 inneholder "forhandler 1". Siden vi ikke vil at kolonnen skal endres, bruker vi $ -tegnet. Dette får hele raden til å markere seg.)
Kriterier: "Avdeling 1"
Verdicelle: $ C2
Kriterier: $ B $ 2: $ B $ 12
Verdiområde: $ C $ 2: $ C $ 12
Nå som vi kjenner alle argumentene vi trenger for å overføre til formelen, er det på tide å implementere den generiske formelen ovenfor på datasett.
- Velg dataene. Gå hjem ? betinget formatering ? Ny regel
- Velg alternativet "Bruk en formel for å bestemme hvilken celle du vil formatere".
- Skriv denne formelen i tekstboksen nedenfor
= AND ($ B2 = "Dealerhip 1", $ C2 <= SMALL (IF ($ B $ 2: $ B $ 12 = "Dealership 1", $ C $ 2: $ C $ 12), 3))
Trykk på OK -knappen. Og det er gjort. De tre nederste eller si at de minste 3 salgene fra avdeling 1 er markert.
Hvordan virker det?
La oss bryte ned formelen fra innsiden.
Denne formelen er en matriseformel, men CTRL+SKIFT+ENTER er ikke nødvendig i betinget formatering.
Siden vi har to betingelser, er det første Dealership 1 og det andre er verdier mindre enn eller lik den tredje minste verdien i Dealerhip 1. Begge må være SANN. For det bruker vi OG -funksjonen.
$ B2 = "Forhandler 1"
Det første kriteriet er enkelt å sjekke. Vi setter ganske enkelt denne uttalelsen $ B2 = "Forhandler 1". Det vil være SANT hvis gjeldende celle B2 inneholder Forhandler 1.
$ C2 <=LITEN(HVIS($ B $ 2: $ B $ 12 = "Dealerhip 1", $ C $ 2: $ C $ 12), 3)
For det andre kriteriet må vi filtrere forhandler 1 og finne den tredje minste verdien i den. Sammenlign deretter kriteriecellen med tredje minste verdi. Dette gjøres ved å bruke setningen $ C2 <= SMALL (IF ($ B $ 2: $ B $ 12 = "Dealership 1", $ C $ 2: $ C $ 12), 3)
Når vi vurderer det, vil denne formelen løse på denne måten:
Vi sjekker intervallet $ B $ 2: $ B $ 12 og får celler som inneholder "Dealerhip 1" ved å bruke uttalelsen $ B $ 2: $ B $ 12 = "Dealership 1". Vi vil ha en rekke SANN og FALSK.
$ C2 <= LITEN (HVIS ({TRUE; FALSE; TRUE; FALSE; FALSE; FALSE; TRUE; FALSE; TRUE}, $ C $ 2: $ C $ 12), 3)
For hver TRUE vil verdien bli returnert fra verdiområdet $ C $ 2: $ C $ 12, og vi vil ha en annen matrise.
$ C2 <= LITEN ({12; FALSK; 25; FALSK; FALSK; FALSK; FALSK; 18; FALSK; FALSK; 14}, 3)
Nå vil SMALL Function returnere den tredje minste verdien fra denne matrisen. (FLASE blir ignorert).
Nå har vi:
$ C2 <= 18
Til slutt er formelen
= AND ($ B2 = "Forhandler 1", $ C2 <= 18)
Nå sjekker det om B2 inneholder Forhandler 1. Det gjør det. Derfor er det SANT
= OG (SANN, $ C2 <= 18)
Deretter sjekker den om C2 er mindre enn eller lik 18 (tredje minste salg i forhandler 1). Det er SANT (12).
= OG (SANN, SANN)
Siden begge utsagnene er Sanne, returnerer formelen SANN og vi får vår første RAD uthevet med farge. Det samme skjer i hver rad.
Fåwww !!! Det var vanskelig å forklare. Jeg håper jeg var tydelig nok til å få deg til å forstå metoden. I praksis er det enkelt.
Her brukte vi hardkodede kriterier, men vi kan også gi cellehenvisning. Dette vil gjøre formelen mer fleksibel.
Her for Kriterier Jeg har brukt cellereferanse til E1. Uansett hvilken forhandler du har i E1, vil den tre minste verdien fra forhandleren bli markert.
Formelen i betinget formatering er:
= AND ($ B2 = $ E $ 1, $ C2 <= SMALL (HVIS ($ B $ 2: $ B $ 12 = $ E $ 1, $ C $ 2: $ C $ 12), 3))
Så ja, slik kan du markere de tre nederste verdiene med en betingelse i excel. Jeg håper artikkelen var nyttig. Hvis du har spørsmål, kan du bruke kommentarfeltet nedenfor. Du kan stille spørsmål relatert til denne artikkelen eller en annen Excel -relatert artikkel. Vi hjelper deg gjerne.
Betinget formatering basert på en annen celleverdi
IF og betinget formatering
Betinget formatering med formel 2016
Betinget formatering ved hjelp av VBA i Microsoft Excel
Marker celler som inneholder spesifikk tekst
Slik summerer du flere kolonner med betingelse
Populære artikler:
50 Excel -snarveier for å øke produktiviteten
VLOOKUP -funksjonen i Excel
COUNTIF i Excel 2016
Slik bruker du SUMIF -funksjonen i Excel