Vi fremhæver celler i excel, så vi let kan identificere fokusområderne på data. Formatering af celler i excel udføres også til visualisering af dataværdier baseret på farve og stil. For eksempel vil du måske fokusere på de 3 mindste værdier i dine data. Det er svært at gøre i store data, men hvis du kunne fortælle excel at fremhæve 3 laveste værdier, ville det være ganske let. Heldigvis kan vi gøre det ved hjælp af betinget formateringsindstilling i Excel.
Det er let bare at fremhæve 3 mindste værdi i excel, men det bliver lidt vanskelig, når vi tilføjer kriterier. I denne artikel lærer vi, hvordan man fremhæver de mindste 3 værdier med en betingelse.
Generisk formel i betinget formering
= OG (tjek celle = kriterier, værdicelle<= LILLE (HVIS (kriterieområde = kriterier, værdiområde),3))
Tjek celle: Det er den celle, du vil kontrollere, om den opfylder de første kriterier (kategorien).
Kriterier: De kriterier, du vil filtrere.
Værdiecelle: Det er cellen, der indeholder en numerisk værdi. Vi vil sammenligne det med 3. mindste værdi i sin kategori, i værdiområde.
Kriterier: Det er det absolutte område, der indeholder kriterierne.
Værdiinterval: Området, der indeholder alle værdier.
Lad os se et eksempel for at gøre tingene klare.
Eksempel: Fremhæv de tre nederste salg foretaget af afdeling 1
Her har jeg en prøve arbejdsfil, der indeholder salg foretaget af forskellige afdelinger i forskellige måneder.
Jeg vil fremhæve de tre nederste salg foretaget af afdeling 1.
Lad os identificere de variabler, vi har her.
Tjek celle: $ B2 (vi vil kontrollere, om B2 indeholder "forhandler 1". Da vi ikke ønsker, at kolonnen skal ændres, bruger vi $ -tegnet. Dette får hele rækken til at fremhæve.)
Kriterier: "Afdeling 1"
Værdiecelle: $ C2
Kriterier: $ B $ 2: $ B $ 12
Værdiinterval: $ C $ 2: $ C $ 12
Nu hvor vi kender alle de argumenter, vi skal overføre til formlen, er det tid til at implementere ovenstående generiske formel på datasæt.
- Vælg data. Gå hjem ? betinget formatering? Ny regel
- Vælg "Brug en formel til at bestemme, hvilken celle der skal formateres".
- Skriv denne formel i tekstfeltet herunder
= OG ($ B2 = "Forhandler 1", $ C2 <= LILLE (HVIS ($ B $ 2: $ B $ 12 = "Forhandler 1", $ C $ 2: $ C $ 12), 3))
Tryk på OK -knappen. Og det er gjort. Nederste tre eller sig mindste 3 salg fra afdeling 1 er fremhævet.
Hvordan virker det?
Lad os nedbryde formlen indefra.
Denne formel er en matrixformel, men CTRL+SHIFT+ENTER er ikke nødvendig i betinget formatering.
Da vi har to betingelser, er det første Dealerhip 1 og det andet er værdier mindre end eller lig med 3. mindste værdi i Dealerhip 1. Begge skal være SAND. Til det bruger vi AND -funktionen.
$ B2 = "Forhandler 1"
Det første kriterium er let at kontrollere. Vi sætter simpelthen denne erklæring $ B2 = "Forhandler 1". Det vil være SANDT, hvis den aktuelle celle B2 indeholder Forhandler 1.
$ C2 <=LILLE(HVIS($ B $ 2: $ B $ 12 = "Dealerhip 1", $ C $ 2: $ C $ 12), 3)
For det andet kriterium skal vi filtrere forhandler 1 og finde den tredje mindste værdi i den. Sammenlign derefter kriteriecellen med 3. mindste værdi. Dette gøres ved hjælp af udsagnet $ C2 <= SMALL (IF ($ B $ 2: $ B $ 12 = "Dealerhip 1", $ C $ 2: $ C $ 12), 3)
Når vi evaluerer det, vil denne formel løse på denne måde:
Vi tjekker rækkevidde $ B $ 2: $ B $ 12 og får celler, der indeholder "Dealerhip 1" ved hjælp af erklæring $ B $ 2: $ B $ 12 = "Dealerhip 1". Vi vil have en vifte af SAND og FALSK.
$ C2 <= SMÅ (HVIS ({SAND; FALSK; SAND; FALSK; FALSK; FALSK; FALSK; FALSK; SAND}, $ C $ 2: $ C $ 12), 3)
For hver SAND returneres værdien fra værdiområdet $ C $ 2: $ C $ 12, og vi får et andet array.
$ C2 <= LILLE ({12; FALSK; 25; FALSK; FALSK; FALSK; 18; FALSK; FALSK; 14}, 3)
Nu returnerer SMALL -funktion den 3. mindste værdi fra dette array. (FLASE ignoreres).
Nu har vi:
$ C2 <= 18
Endelig er formlen
= AND ($ B2 = "Forhandler 1", $ C2 <= 18)
Nu kontrolleres det, om B2 indeholder Forhandler 1. Det gør det. Derfor er det SANDT
= OG (SAND, $ C2 <= 18)
Dernæst kontrollerer den, om C2 er mindre end eller lig med 18 (tredje mindste salg i forhandler 1). Det er SANDT (12).
= OG (SAND, SAND)
Da begge udsagn er SAND returnerer formlen SAND, og vi får vores første Række fremhævet med farve. Det samme sker i hver række.
Fywww !!! Det var svært at forklare. Jeg håber, jeg var klar nok til at få dig til at forstå metoden. I praksis er det let.
Her brugte vi hårdkodede kriterier, men vi kan også give cellehenvisning. Dette vil gøre formlen mere fleksibel.
Her, for Kriterier Jeg har brugt cellereference til E1. Uanset hvilken forhandler du har i E1, vil den tre mindste værdi fra denne forhandler blive fremhævet.
Formlen i betinget formatering er:
= AND ($ B2 = $ E $ 1, $ C2 <= SMÅ (HVIS ($ B $ 2: $ B $ 12 = $ E $ 1, $ C $ 2: $ C $ 12), 3))
Så ja, sådan kan du fremhæve de tre nederste værdier med en betingelse i excel. Jeg håber, at artiklen var nyttig. Hvis du har spørgsmål, kan du bruge kommentarfeltet herunder. Du kan stille spørgsmål i forbindelse med denne artikel eller enhver anden excel -relateret artikel. Vi hjælper dig gerne.
Betinget formatering baseret på en anden celleværdi
IF og betinget formatering
Betinget formatering med formel 2016
Betinget formatering ved hjælp af VBA i Microsoft Excel
Fremhæv celler, der indeholder specifik tekst
Sådan summeres flere kolonner med betingelse
Populære artikler:
50 Excel -genveje til at øge din produktivitet
VLOOKUP -funktionen i Excel
COUNTIF i Excel 2016
Sådan bruges SUMIF -funktionen i Excel