Excel betinget formatering
Excel betinget formatering giver dig mulighed for at definere regler, der bestemmer celleformatering.
For eksempel kan du oprette en regel, der fremhæver celler, der opfylder visse kriterier. Eksempler omfatter:
- Tal, der falder inden for et bestemt område (f.eks. Mindre end 0).
- De 10 bedste elementer på en liste.
- Oprettelse af et "varmekort".
- "Formelbaserede" regler for stort set enhver betinget formatering.
I Excel kan betinget formatering findes på båndet under Hjem> Stilarter (ALT> H> L).
For at oprette din egen regel skal du klikke på 'Ny regel', og et nyt vindue vises:
Betinget formatering i VBA
Alle disse betingede formateringsfunktioner kan tilgås ved hjælp af VBA.
Bemærk, at når du konfigurerer betinget formatering inde fra VBA-kode, vises dine nye parametre i Excel-frontend-vinduet med betinget formatering og vil være synlige for brugeren. Brugeren kan redigere eller slette disse, medmindre du har låst regnearket.
De betingede formateringsregler gemmes også, når regnearket gemmes
Betingede formateringsregler gælder specifikt for et bestemt regneark og for et bestemt område af celler. Hvis de er nødvendige andre steder i projektmappen, skal de også være konfigureret på dette regneark.
Praktiske anvendelser af betinget formatering i VBA
Du kan have en stor del rå data importeret til dit regneark fra en CSV-fil (kommaseparerede værdier) eller fra en databasetabel eller forespørgsel. Dette kan flyde igennem til et dashboard eller en rapport, hvor tal ændres fra en periode til en anden.
Hvor et tal ændrer sig og ligger uden for et acceptabelt område, kan du måske fremhæve dette f.eks. baggrundsfarve på cellen i rødt, og du kan gøre denne opsætning af betinget formatering. På denne måde vil brugeren øjeblikkeligt blive tiltrukket af dette nummer og derefter undersøge, hvorfor dette sker.
Du kan bruge VBA til at slå den betingede formatering til eller fra. Du kan bruge VBA til at rydde reglerne for en række celler eller tænde dem igen. Der kan være en situation, hvor der er en helt god grund til et usædvanligt antal, men når brugeren præsenterer instrumentbrættet eller rapporterer til et højere ledelsesniveau, vil de gerne kunne fjerne 'alarmklokkerne'.
Også på de rå importerede data vil du måske fremhæve, hvor tallene er latterligt store eller latterligt små. Det importerede datainterval er normalt en anden størrelse for hver periode, så du kan bruge VBA til at evaluere størrelsen på det nye dataområde og kun indsætte betinget formatering for dette område.
Du kan også have en situation, hvor der er en sorteret liste med navne med numeriske værdier mod hver enkelt f.eks. medarbejderløn, eksamenskarakterer. Med betinget formatering kan du bruge graduerede farver til at gå fra højeste til laveste, hvilket ser meget imponerende ud til præsentationsformål.
Listen over navne er dog ikke altid statisk i størrelse, og du kan bruge VBA -kode til at opdatere skalaen for graduerede farver i henhold til ændringer i størrelsen på intervallet.
Et enkelt eksempel på oprettelse af et betinget format på et område
Dette eksempel opretter betinget formatering for en række celler (A1: A10) på et regneark. Hvis tallet i intervallet er mellem 100 og 150, vil cellens baggrundsfarve være rød, ellers har den ingen farve.
| 1234567891011121314 | Sub ConditionalFormattingExample ()'Definer rækkeviddeDim MyRange As RangeIndstil MyRange = Range (“A1: A10”)'Slet eksisterende betinget formatering fra områdeMyRange.FormatConditions.Delete'Anvend betinget formateringMyRange.FormatConditions.Add Type: = xlCellValue, Operator: = xlBetween, _Formel1: = "= 100", Formel2: = "= 150"MyRange.FormatConditions (1) .Interior.Color = RGB (255, 0, 0)Afslut Sub |
Bemærk, at først definerer vi intervallet MyRange at anvende betinget formatering.
Dernæst sletter vi enhver eksisterende betinget formatering for området. Dette er en god idé for at forhindre, at den samme regel tilføjes hver gang koden køres (det er naturligvis ikke hensigtsmæssigt under alle omstændigheder).
Farver er givet med numeriske værdier. Det er en god idé at bruge RGB (rød, grøn, blå) notation til dette. Du kan bruge standard farvekonstanter til dette f.eks. vbRed, vbBlue, men du er begrænset til otte farvevalg.
Der er over 16,7 millioner farver tilgængelige, og ved hjælp af RGB kan du få adgang til dem alle. Dette er langt lettere end at prøve at huske, hvilket tal der passer til hvilken farve. Hvert af de tre RGB -farvetal er fra 0 til 255.
Bemærk, at parameteren 'xlBetween' er inklusiv, så celleværdier på 100 eller 150 opfylder betingelsen.
Formatering med flere betingelser
Du vil måske oprette flere betingede regler inden for dit dataområde, så alle værdierne i et område er dækket af forskellige betingelser:
| 12345678910111213141516171819 | Sub MultipleConditionalFormattingExample ()Dim MyRange As Range'Opret områdeobjektIndstil MyRange = Range (“A1: A10”)'Slet tidligere betingede formaterMyRange.FormatConditions.Delete'Tilføj den første regelMyRange.FormatConditions.Add Type: = xlCellValue, Operator: = xlBetween, _Formel1: = "= 100", Formel2: = "= 150"MyRange.FormatConditions (1) .Interior.Color = RGB (255, 0, 0)'Tilføj anden regelMyRange.FormatConditions.Add Type: = xlCellValue, Operator: = xlLess, _Formel1: = "= 100"MyRange.FormatConditions (2) .Interior.Color = vbBlue'Tilføj tredje regelMyRange.FormatConditions.Add Type: = xlCellValue, Operator: = xlGreater, _Formel1: = "= 150"MyRange.FormatConditions (3) .Interior.Color = vbGulAfslut Sub |
Dette eksempel opretter den første regel som før med cellefarven rød, hvis celleværdien er mellem 100 og 150.
Yderligere to regler tilføjes derefter. Hvis celleværdien er mindre end 100, er cellefarven blå, og hvis den er større end 150, er cellefarven gul.
I dette eksempel skal du sikre, at alle talmuligheder er dækket, og at reglerne ikke overlapper hinanden.
Hvis tomme celler er i dette område, vises de som blå, fordi Excel stadig anser dem for at have en værdi mindre end 100.
Vejen udenom dette er at tilføje en anden betingelse som et udtryk. Dette skal tilføjes som den første betingelsesregel i koden. Det er meget vigtigt, hvor der er flere regler, for at få rækkefølgen af eksekvering rigtigt ellers kan resultater være uforudsigelige.
| 1234567891011121314151617181920212223 | Sub MultipleConditionalFormattingExample ()Dim MyRange As Range'Opret områdeobjektIndstil MyRange = Range (“A1: A10”)'Slet tidligere betingede formaterMyRange.FormatConditions.Delete'Tilføj den første regelMyRange.FormatConditions.Add Type: = xlExpression, Formula1: = _"= LEN (TRIM (A1)) = 0"MyRange.FormatConditions (1) .Interior.Pattern = xlNone'Tilføj anden regelMyRange.FormatConditions.Add Type: = xlCellValue, Operator: = xlBetween, _Formel1: = "= 100", Formel2: = "= 150"MyRange.FormatConditions (2) .Interior.Color = RGB (255, 0, 0)'Tilføj tredje regelMyRange.FormatConditions.Add Type: = xlCellValue, Operator: = xlLess, _Formel1: = "= 100"MyRange.FormatConditions (3) .Interior.Color = vbBlue'Tilføj fjerde regelMyRange.FormatConditions.Add Type: = xlCellValue, Operator: = xlGreater, _Formel1: = "= 150"MyRange.FormatConditions (4) .Interior.Color = RGB (0, 255, 0)Afslut Sub |
Dette bruger typen xlExpression og bruger derefter en standard Excel -formel til at afgøre, om en celle er tom i stedet for en numerisk værdi.
FormatConditions -objektet er en del af Range -objektet. Det fungerer på samme måde som en samling med indekset fra 1. Du kan iterere gennem dette objekt ved hjælp af en For… Næste eller For… Hver sløjfe.
Sletning af en regel
Nogle gange skal du muligvis slette en individuel regel i et sæt med flere regler, hvis den ikke passer til datakravene.
| 12345678910111213 | Sub DeleteConditionalFormattingExample ()Dim MyRange As Range'Opret områdeobjektIndstil MyRange = Range (“A1: A10”)'Slet tidligere betingede formaterMyRange.FormatConditions.Delete'Tilføj den første regelMyRange.FormatConditions.Add Type: = xlCellValue, Operator: = xlBetween, _Formel1: = "= 100", Formel2: = "= 150"MyRange.FormatConditions (1) .Interior.Color = RGB (255, 0, 0)'Slet regelMyRange.FormatConditions (1) .SletAfslut Sub |
Denne kode opretter en ny regel for område A1: A10 og sletter den derefter. Du skal bruge det korrekte indeksnummer til sletningen, så tjek 'Administrer regler' på Excel-frontend (dette viser reglerne i rækkefølge for udførelse) for at sikre, at du får det korrekte indeksnummer. Bemærk, at der ikke er en fortrydelsesfunktion i Excel, hvis du sletter en betinget formateringsregel i VBA, i modsætning til hvis du gør det via Excel-frontend.
Ændring af en regel
Fordi reglerne er en samling af objekter baseret på et bestemt område, kan du nemt foretage ændringer af bestemte regler ved hjælp af VBA. De faktiske egenskaber, når reglen er tilføjet, er skrivebeskyttet, men du kan bruge metoden Rediger til at ændre dem. Egenskaber som farver læses / skrives.
| 123456789101112131415 | Sub ChangeConditionalFormattingExample ()Dim MyRange As Range'Opret områdeobjektIndstil MyRange = Range (“A1: A10”)'Slet tidligere betingede formaterMyRange.FormatConditions.Delete'Tilføj den første regelMyRange.FormatConditions.Add Type: = xlCellValue, Operator: = xlBetween, _Formel1: = "= 100", Formel2: = "= 150"MyRange.FormatConditions (1) .Interior.Color = RGB (255, 0, 0)'Skift regelMyRange.FormatConditions (1) .Modify xlCellValue, xlLess, "10"'Skift regelfarveMyRange.FormatConditions (1) .Interior.Color = vbGreenAfslut Sub |
Denne kode opretter et områdeobjekt (A1: A10) og tilføjer en regel for tal mellem 100 og 150. Hvis betingelsen er sand, ændres cellefarven til rød.
Koden ændrer derefter reglen til tal mindre end 10. Hvis betingelsen er sand, ændres cellefarven nu til grøn.
Brug af en gradueret farveskema
Betinget formatering i Excel har mulighed for at bruge graduerede farver på en række tal, der kører i stigende eller faldende rækkefølge.
Dette er meget nyttigt, når du har data som salgstal efter geografisk område, bytemperaturer eller afstande mellem byer. Ved hjælp af VBA har du den ekstra fordel, at du kan vælge dit eget graduerede farveskema frem for de standard, der tilbydes på Excel-frontend.
| 1234567891011121314151617181920212223242526272829 | Sub GraduatedColors ()Dim MyRange As Range'Opret områdeobjektIndstil MyRange = Range (“A1: A10”)'Slet tidligere betingede formaterMyRange.FormatConditions.Delete'Definer skalatypeMyRange.FormatConditions.AddColorScale ColorScaleType: = 3'Vælg farve for den laveste værdi i områdetMyRange.FormatConditions (1) .ColorScaleCriteria (1) .Type = _xlConditionValueLowestValueMed MyRange.FormatConditions (1) .ColorScaleCriteria (1) .FormatColor.Farve = 7039480Afslut med'Vælg farve for de midterste værdier i områdetMyRange.FormatConditions (1) .ColorScaleCriteria (2) .Type = _xlConditionValuePercentileMyRange.FormatConditions (1) .ColorScaleCriteria (2) .Værdi = 50'Vælg farven til midten af områdetMed MyRange.FormatConditions (1) .ColorScaleCriteria (2) .FormatColor.Farve = 8711167Afslut med'Vælg farve for den højeste værdi i områdetMyRange.FormatConditions (1) .ColorScaleCriteria (3) .Type = _xlConditionValueHighestValueMed MyRange.FormatConditions (1) .ColorScaleCriteria (3) .FormatColor.Farve = 8109667Afslut medAfslut Sub |
Når denne kode køres, graduerer den cellefarverne i henhold til de stigende værdier i området A1: A10.
Dette er en meget imponerende måde at vise dataene på og vil helt sikkert fange brugernes opmærksomhed.
Betinget formatering af fejlværdier
Når du har en enorm mængde data, kan du let gå glip af en fejlværdi i dine forskellige regneark. Hvis dette præsenteres for en bruger uden at blive løst, kan det føre til store problemer, og brugeren mister tilliden til tallene. Dette bruger en regeltype xlExpression og en Excel -funktion af IsError til at evaluere cellen.
Du kan oprette kode, så alle celler med fejl i har en cellefarve rød:
| 1234567891011 | UnderfejlConditionalFormattingExample ()Dim MyRange As Range'Opret områdeobjektIndstil MyRange = Range (“A1: A10”)'Slet tidligere betingede formaterMyRange.FormatConditions.Delete'Tilføj fejlregelMyRange.FormatConditions.Add Type: = xlExpression, Formula1: = "= IsError (A1) = true"'Indstil indvendig farve til rødMyRange.FormatConditions (1) .Interior.Color = RGB (255, 0, 0)Afslut Sub |
Betinget formatering til tidligere datoer
Du kan have importeret data, hvor du vil fremhæve datoer, der er tidligere. Et eksempel på dette kan være en debitorrapport, hvor du vil have en gammel fakturadato, der er over 30 dage gammel, til at skille sig ud.
Denne kode bruger regeltypen xlExpression og en Excel -funktion til at evaluere datoerne.
| 1234567891011 | Sub DateInPastConditionalFormattingExample ()Dim MyRange As Range'Opret områdeobjekt baseret på en kolonne med datoerIndstil MyRange = Range (“A1: A10”)'Slet tidligere betingede formaterMyRange.FormatConditions.Delete'Tilføj fejlregel for tidligere datoerMyRange.FormatConditions.Add Type: = xlExpression, Formula1: = "= Nu ()-A1> 30"'Indstil indvendig farve til rødMyRange.FormatConditions (1) .Interior.Color = RGB (255, 0, 0)Afslut Sub |
Denne kode vil omfatte en række datoer i intervallet A1: A10 og indstille cellefarven til rød for enhver dato, der er over 30 dage tidligere.
I formlen, der bruges i tilstanden, giver Now () den aktuelle dato og tid. Dette vil blive genberegnet hver gang regnearket genberegnes, så formateringen ændres fra den ene dag til den næste.
Brug af datalinjer i VBA betinget formatering
Du kan bruge VBA til at tilføje datastænger til en række numre. Disse er næsten som mini -diagrammer og giver et øjeblikkeligt overblik over, hvor store tallene er i forhold til hinanden. Ved at acceptere standardværdier for datastængerne er koden meget let at skrive.
| 123456 | Sub DataBarFormattingExample ()Dim MyRange As RangeIndstil MyRange = Range (“A1: A10”)MyRange.FormatConditions.DeleteMyRange.FormatConditions.AddDatabarAfslut Sub |
Dine data vil se sådan ud på regnearket:
Brug af ikoner i VBA betinget formatering
Du kan bruge betinget formatering til at sætte ikoner sammen med dine tal i et regneark. Ikonerne kan være pile eller cirkler eller forskellige andre former. I dette eksempel tilføjer koden pilikoner til tallene baseret på deres procentværdier:
| 12345678910111213141516171819202122232425 | Sub IconSetsExample ()Dim MyRange As Range'Opret områdeobjektIndstil MyRange = Range (“A1: A10”)'Slet tidligere betingede formaterMyRange.FormatConditions.Delete'Føj ikonsæt til FormatConditions -objektetMyRange.FormatConditions.AddIconSetCondition'Indstil ikonet til pile - betingelse 1Med MyRange.FormatConditions (1).IconSet = ActiveWorkbook.IconSets (xl3Arrows)Afslut med'indstil ikonkriterierne for den procentvise værdi, der kræves - betingelse 2Med MyRange.FormatConditions (1) .IconCriteria (2).Type = xlConditionValuePercent.Værdi = 33.Operator = xlGreaterEqualAfslut med'indstil ikonkriterierne for den procentvise værdi, der kræves - betingelse 3Med MyRange.FormatConditions (1) .IconCriteria (3).Type = xlConditionValuePercent.Værdi = 67.Operator = xlGreaterEqualAfslut medAfslut Sub |
Dette giver en øjeblikkelig visning, der viser, om et tal er højt eller lavt. Efter at have kørt denne kode vil dit regneark se sådan ud:
Brug af betinget formatering til at fremhæve top fem
Du kan bruge VBA -kode til at markere de 5 bedste tal inden for et dataområde. Du bruger en parameter kaldet 'AddTop10', men du kan justere rangnummeret inden for koden til 5. En bruger ønsker måske at se de højeste tal i et område uden først at skulle sortere dataene.
| 1234567891011121314151617181920212223 | Sub Top5Example ()Dim MyRange As Range'Opret områdeobjektIndstil MyRange = Range (“A1: A10”)'Slet tidligere betingede formaterMyRange.FormatConditions.Delete'Tilføj en Top10 -tilstandMyRange.FormatConditions.AddTop10Med MyRange.FormatConditions (1)'Indstil top til bund parameter.TopBottom = xlTop10Top'Kun top 5.Rang = 5Afslut medMed MyRange.FormatConditions (1) .Font'Indstil skrifttypefarven.Farve = -16383844Afslut medMed MyRange.FormatConditions (1) .Interiør'Indstil cellens baggrundsfarve.Farve = 13551615Afslut medAfslut Sub |
Dataene på dit regneark ville se sådan ud efter at koden var kørt:
Bemærk, at værdien på 145 vises to gange, så seks celler fremhæves.
Betydningen af parametrene StopIfTrue og SetFirstPriority
StopIfTrue er af betydning, hvis en række celler har flere regler for betinget formatering. En enkelt celle inden for området opfylder muligvis den første regel, men den kan også tilfredsstille efterfølgende regler. Som udvikler vil du måske have, at den kun viser formateringen for den første regel, den kommer til. Andre regelkriterier kan overlappe hinanden og kan foretage utilsigtede ændringer, hvis de får lov til at fortsætte ned på listen over regler.
Standardindstillingen på denne parameter er True, men du kan ændre den, hvis du vil have alle de andre regler for denne celle i betragtning:
| 1 | MyRange. FormatConditions (1) .StopIfTrue = Falsk |
Parameteren SetFirstPriority dikterer, om denne betingelsesregel først vil blive evalueret, når der er flere regler for denne celle.
| 1 | MyRange. FormatConditions (1) .SetFirstPriority |
Dette flytter positionen for denne regel til position 1 inden for samlingen af formatbetingelser, og eventuelle andre regler flyttes nedad med ændrede indeksnumre. Pas på, hvis du foretager ændringer i reglerne i koden ved hjælp af indeksnumrene. Du skal sikre dig, at du ændrer eller sletter den rigtige regel.
Du kan ændre prioriteten for en regel:
| 1 | MyRange. FormatConditions (1) .Prioritet = 3 |
Dette vil ændre de relative positioner for andre regler inden for listen med betinget format.
Brug af betinget formatering med henvisning til andre celleværdier
Dette er en ting, som Excel betinget formatering ikke kan gøre. Du kan dog opbygge din egen VBA -kode for at gøre dette.
Antag, at du har en kolonne med data, og i den tilstødende celle til hvert tal er der noget tekst, der angiver, hvilken formatering der skal finde sted på hvert nummer.
Følgende kode vil køre din liste over tal ned, se i den tilstødende celle for at formatere tekst og derefter formatere nummeret efter behov:
| 123456789101112131415161718192021 | Sub ReferToAnotherCellForConditionalFormatting ()'Opret variabler for at holde antallet af rækker for de tabelformede dataDim RRække så længe, N så længe'Fang antallet af rækker inden for det tabelformede dataområdeRRow = ActiveSheet.UsedRange.Rows.Count'Iterere gennem alle rækker i det tabelformede dataområdeFor N = 1 Til RRow'Brug en Select Case -erklæring til at evaluere formateringen baseret på kolonne 2Vælg Case ActiveSheet.Cells (N, 2). Værdi'Drej den indvendige farve til blåEtui "Blå"ActiveSheet.Cells (N, 1) .Interior.Color = vbBlue'Drej den indvendige farve til rødEtui "rød"ActiveSheet.Cells (N, 1) .Interior.Color = vbRed'Drej den indvendige farve til grønSag "grøn"ActiveSheet.Cells (N, 1) .Interior.Color = vbGreenAfslut VælgNæste NAfslut Sub |
Når denne kode er kørt, ser dit regneark nu sådan ud:
De celler, der henvises til til formateringen, kan være hvor som helst på regnearket eller endda på et andet regneark i projektmappen. Du kan bruge enhver form for tekst til at gøre en betingelse for formateringen, og du er kun begrænset af din fantasi i de anvendelser, du kan sætte denne kode til.
Operatører, der kan bruges i erklæringer om betinget formatering
Som du har set i de foregående eksempler, bruges operatorer til at bestemme, hvordan tilstandsværdierne vil blive evalueret f.eks. xlMellem.
Der er en række af disse operatører, der kan bruges, afhængigt af hvordan du ønsker at angive dine regelkriterier.
| Navn | Værdi | Beskrivelse |
| xlMellem | 1 | Mellem. Kan kun bruges, hvis der findes to formler. |
| xlLige | 3 | Lige. |
| xl Større | 5 | Bedre end. |
| xlGreaterEqual | 7 | Større end eller lig med. |
| xlMindre | 6 | Mindre end. |
| xlLessEqual | 8 | Mindre end eller lig med. |
| xlNotBetween | 2 | Ikke imellem. Kan kun bruges, hvis der findes to formler. |
| xlNotEqual | 4 | Ikke lige. |
