Vi markerar celler i excel så att vi enkelt kan identifiera fokusområdena på data. Formatering av celler i excel görs också för att visualisera datavärden baserat på färg och stil. Till exempel kanske du vill fokusera på de tre minsta värdena i dina data. Det är svårt att göra i en stor data, men om du kunde berätta för excel att markera 3 lägsta värden då skulle det vara ganska enkelt. Lyckligtvis kan vi göra det med villkorligt formateringsalternativ i Excel.
Det är lätt att bara markera 3 minsta värde i excel men det blir lite knepigt när vi lägger till kriterier. I den här artikeln kommer vi att lära oss att markera de minsta tre värdena med ett villkor.
Generisk formel i villkorlig formning
= OCH (check cell = kriterier, värde cell<= LILLA (OM (kriterieintervall = kriterier, värdeintervall),3))
Kontrollera cell: Det är cellen som du vill kontrollera om den uppfyller de första kriterierna (kategorin).
Kriterier: Kriterierna som du vill filtrera.
Värdecell: Det är cellen som innehåller ett numeriskt värde. Vi kommer att jämföra det med tredje minsta värdet i sin kategori, i värdeintervall.
Kriterier: Det är det absoluta intervallet som innehåller kriterierna.
Värdeintervall: Intervallet som innehåller alla värden.
Låt oss se ett exempel för att göra saker tydliga.
Exempel: Markera de tre nedersta försäljningarna som gjorts av avdelning 1
Här har jag ett exempel på en arbetsfil som innehåller försäljning utförd av olika avdelningar under olika månader.
Jag vill lyfta fram de tre nedersta försäljningarna som gjorts av avdelning 1.
Låt oss identifiera de variabler vi har här.
Kontrollera cell: $ B2 (vi vill kontrollera om B2 innehåller "återförsäljare 1". Eftersom vi inte vill att kolumnen ska ändras använder vi $ -tecknet. Detta gör att hela raden markeras.)
Kriterier: "Avdelning 1"
Värdecell: $ C2
Kriterier: $ B $ 2: $ B $ 12
Värdeintervall: $ C $ 2: $ C $ 12
Nu när vi känner till alla argument som vi behöver för att överföra till formeln, är det dags att implementera ovanstående generiska formel på datamängden.
- Välj data. Gå till hemmet ? villkorlig formatering ? Ny regel
- Välj alternativet "Använd en formel för att avgöra vilken cell som ska formateras".
- Skriv den här formeln i textrutan nedan
= AND ($ B2 = "Dealerhip 1", $ C2 <= SMALL (OM ($ B $ 2: $ B $ 12 = "Dealership 1", $ C $ 2: $ C $ 12), 3))
Tryck på OK -knappen. Och det är klart. Nedre tre eller säg minsta 3 försäljning från avdelning 1 markeras.
Hur fungerar det?
Låt oss bryta ner formeln inifrån.
Denna formel är en matrisformel men CTRL+SKIFT+ENTER behövs inte vid villkorlig formatering.
Eftersom vi har två villkor är det första Dealership 1 och det andra är värden som är mindre än eller lika med 3: e minsta värdet i Dealerhip 1. Båda måste vara SANT. För det använder vi OCH -funktionen.
$ B2 = "Återförsäljare 1"
Första kriterierna är lätta att kontrollera. Vi uttrycker helt enkelt detta uttalande $ B2 = "Återförsäljare 1". Det kommer att vara SANT om den aktuella cellen B2 innehåller Återförsäljare 1.
$ C2 <=SMÅ(OM($ B $ 2: $ B $ 12 = "Dealerhip 1", $ C $ 2: $ C $ 12), 3)
För det andra kriteriet måste vi filtrera återförsäljare 1 och hitta det tredje minsta värdet i det. Jämför sedan kriteriecellen med tredje minsta värdet. Detta görs med hjälp av uttalandet $ C2 <= SMALL (IF ($ B $ 2: $ B $ 12 = "Dealership 1", $ C $ 2: $ C $ 12), 3)
När vi utvärderar det kommer denna formel att lösa på detta sätt:
Vi kontrollerar intervallet $ B $ 2: $ B $ 12 och får celler som innehåller "Dealership 1" med hjälp av uttalande $ B $ 2: $ B $ 12 = "Dealership 1". Vi kommer att ha en uppsättning av SANT och FALSKT.
$ C2 <= SMÅ (OM ({SANT; FALSKT; SANT; FALSKT; FALSKT; FALSKT; SANT; FALSKT; SANT}, $ C $ 2: $ C $ 12), 3)
För varje TRUE returneras värdet från värdeintervallet $ C $ 2: $ C $ 12 och vi kommer att ha en annan array.
$ C2 <= Liten ({12; FALSK; 25; FALSK; FALSK; FALSK; 18; FALSK; FALSK; 14}, 3)
Nu returnerar SMALL Function det tredje minsta värdet från denna array. (FLASE ignoreras).
Nu har vi:
$ C2 <= 18
Slutligen är formeln
= AND ($ B2 = "Dealerhip 1", $ C2 <= 18)
Nu kontrolleras om B2 innehåller Återförsäljare 1. Det gör det. Därför är det SANT
= OCH (SANT, $ C2 <= 18)
Därefter kontrollerar den om C2 är mindre än eller lika med 18 (tredje minsta försäljningen i återförsäljare 1). Det är SANT (12).
= OCH (SANT, SANT)
Eftersom båda påståendena är SANT returnerar formeln SANT och vi får vår första RAD markerad med färg. Samma händer i varje rad.
Fåww !!! Det var svårt att förklara. Jag hoppas att jag var tillräckligt tydlig för att få dig att förstå metoden. I praktiken är det enkelt.
Här använde vi hårdkodade kriterier men vi kan också ge cellreferens också. Detta kommer att göra formeln mer flexibel.
Här för Kriterier Jag har använt cellreferens för E1. Oavsett vilken återförsäljare du har i E1 kommer det tre minsta värdet från den återförsäljaren att lyftas fram.
Formeln i villkorlig formatering är:
= AND ($ B2 = $ E $ 1, $ C2 <= SMÅ (OM ($ B $ 2: $ B $ 12 = $ E $ 1, $ C $ 2: $ C $ 12), 3))
Så ja, så här kan du markera de tre nedersta värdena med ett villkor i excel. Jag hoppas att artikeln var till hjälp. Om du har några frågor, använd kommentarsfältet nedan. Du kan ställa frågor relaterade till denna artikel eller någon annan Excel -relaterad artikel. Vi hjälper dig gärna.
Villkorlig formatering baserat på ett annat cellvärde
IF och villkorlig formatering
Villkorlig formatering med formel 2016
Villkorlig formatering med VBA i Microsoft Excel
Markera celler som innehåller specifik text
Hur man summerar flera kolumner med villkor
Populära artiklar:
50 Excel -genvägar för att öka din produktivitet
VLOOKUP -funktionen i Excel
COUNTIF i Excel 2016
Hur man använder SUMIF -funktionen i Excel
