Tingimusvormingu kasutamine Excel VBA -ga

Exceli tingimusvorming

Exceli tingimusvorming võimaldab teil määrata reeglid, mis määravad lahtrite vormindamise.

Näiteks saate luua reegli, mis tõstab esile teatud kriteeriumidele vastavaid lahtreid. Näiteks:

• Arvud, mis jäävad teatud vahemikku (nt alla 0).
• Loendi kümme parimat üksust.
• Soojuskaardi koostamine.
• „Valemipõhised” reeglid peaaegu iga tingimusliku vormindamise jaoks.

Excelis leiate tingimusvormingu lindilt jaotise Avaleht> Stiilid (ALT> H> L) alt.

Oma reegli loomiseks klõpsake "Uus reegel" ja ilmub uus aken:

Tingimuslik vormindamine VBA -s

Kõiki neid tingimusliku vormindamise funktsioone saab kasutada VBA abil.

Pange tähele, et kui seadistate VBA-koodist tingimusvormingu, kuvatakse teie uued parameetrid Exceli kasutajaliidese tingimusvormingu aknas ja need on kasutajale nähtavad. Kasutaja saab neid muuta või kustutada, kui te pole töölehte lukustanud.

Tingimusvormingu reeglid salvestatakse ka töölehe salvestamisel

Tingimuslikud vormindamisreeglid kehtivad konkreetselt konkreetse töölehe ja teatud lahtrivahemiku kohta. Kui neid on mujal töövihikus vaja, siis tuleb need ka sellele töölehele üles seada.

Tingimusliku vormindamise praktilised kasutusalad VBA -s

Võimalik, et teie töölehele on imporditud suur osa töötlemata andmeid CSV-failist (komaeraldusega väärtused) või andmebaasi tabelist või päringust. See võib juhtuda armatuurlauale või aruandesse, muutuvaid numbreid imporditakse ühest perioodist teise.

Kui number muutub ja jääb vastuvõetavast vahemikust välja, võiksite selle esile tõsta, nt. lahtri taustavärv punane ja saate seda teha tingimusvormingu seadistamisel. Sel moel tõmmatakse kasutaja sellele numbrile koheselt ja saab seejärel uurida, miks see nii on.

Tingimusvormingu sisse- või väljalülitamiseks saate kasutada VBA -d. Saate kasutada VBA -d lahtrite vahemiku reeglite tühjendamiseks või uuesti sisse lülitamiseks. Võib esineda olukord, kus ebatavalise arvu jaoks on täiesti mõjuv põhjus, kuid kui kasutaja esitab armatuurlaua või aruande kõrgemale juhtimistasandile, soovib ta „häirekellad” eemaldada.

Samuti võiksite toores imporditud andmetel esile tõsta, kus numbrid on naeruväärselt suured või naeruväärselt väikesed. Imporditud andmevahemik on tavaliselt iga perioodi jaoks erineva suurusega, nii et saate VBA abil hinnata uue andmevahemiku suurust ja sisestada tingimusvormingu ainult selle vahemiku jaoks.

Teil võib olla ka olukord, kus on olemas sorteeritud nimede loend numbriliste väärtustega igaühe vastu nt. töötaja palk, eksamipunktid. Tingimusliku vormindamise korral saate kasutada gradueeritud värve, et minna kõrgeimast madalaimale, mis tundub esitluse jaoks väga muljetavaldav.

Nimede loendi suurus ei ole aga alati staatiline ning VBA -koodi abil saate astmeliste värvide skaalat värskendada vastavalt vahemiku suuruse muutustele.

Lihtne näide vahemikus tingimusliku vormingu loomise kohta

See näide seab töölehel lahtrivahemiku (A1: A10) tingimusvormingu. Kui arv vahemikus on vahemikus 100 kuni 150, on lahtri taustavärv punane, vastasel juhul pole sellel värvi.

1234567891011121314

Alamtingimuslik vormindamine Näide ()„Määrake vahemikDim MyRange vahemikunaMäära MyRange = vahemik ("A1: A10")„Kustuta vahemikust olemasolev tingimusvormingMyRange.FormatConditions.Delete„Rakenda tingimusvormingutMyRange.FormatConditions.Add Type: = xlCellValue, Operaator: = xlBetween, _Valem1: = "= 100", Valem2: = "= 150"MyRange.FormatConditions (1). Interjöör. Värv = RGB (255, 0, 0)End Sub

Pange tähele, et kõigepealt määratleme vahemiku MyRange tingimusvormingu rakendamiseks.

Seejärel kustutame kõik olemasolevad vahemiku tingimusvormingud. See on hea mõte, et vältida sama reegli lisamist iga kord, kui kood käivitatakse (muidugi ei sobi see igal juhul).

Värvid on antud numbriliste väärtustega. Selleks on hea kasutada RGB (punane, roheline, sinine) märget. Selleks võite kasutada standardseid värvikonstante, nt. vbRed, vbBlue, kuid teil on piiratud kaheksa värvivalikuga.

Saadaval on üle 16,7 miljoni värvi ja RGB abil saate neile kõigile juurde pääseda. See on palju lihtsam kui üritada meeles pidada, milline number millise värviga sobib. Kõik kolm RGB värvinumbrit on vahemikus 0 kuni 255.

Pange tähele, et parameeter „xlBetween” on kaasav, nii et lahtri väärtused 100 või 150 vastavad tingimusele.

Mitme tingimusega vormindamine

Võimalik, et soovite oma andmevahemikus seadistada mitu tingimusreeglit, et kõik vahemiku väärtused oleksid kaetud erinevate tingimustega.

12345678910111213141516171819

Alamkordne Tingimuslik vormindamine Näide ()Dim MyRange vahemikuna'Loo vahemiku objektMäära MyRange = vahemik ("A1: A10")"Kustutage varasemad tingimusvormingudMyRange.FormatConditions.Delete'Lisa esimene reegelMyRange.FormatConditions.Add Type: = xlCellValue, Operaator: = xlBetween, _Valem1: = "= 100", Valem2: = "= 150"MyRange.FormatConditions (1). Interjöör. Värv = RGB (255, 0, 0)"Lisage teine ​​reegelMyRange.FormatConditions.Add Type: = xlCellValue, Operaator: = xlLess, _Valem1: = "= 100"MyRange.FormatConditions (2). Interjöör.Värv = vbBlue"Lisage kolmas reegelMyRange.FormatConditions.Add Type: = xlCellValue, Operaator: = xlGreater, _Valem1: = "= 150"MyRange.FormatConditions (3). Interjöör.Värv = vbKollaneEnd Sub

See näide seab esimese reegli nagu varem, kusjuures lahtri värvus on punane, kui lahtri väärtus on vahemikus 100 kuni 150.

Seejärel lisatakse veel kaks reeglit. Kui lahtri väärtus on väiksem kui 100, on lahtri värv sinine ja kui see on suurem kui 150, siis on lahtri värvus kollane.

Selles näites peate tagama, et kõik numbrite võimalused oleksid kaetud ja reeglid ei kattuks.

Kui selles vahemikus on tühjad lahtrid, kuvatakse need sinisena, sest Excel peab neid siiski väärtuseks alla 100.

Seda saab muuta väljendina teise tingimuse lisamiseks. See tuleb koodi esimese tingimuse reegliks lisada. Kui reegleid on mitu, on väga oluline, et täitmise järjekord oleks õige, vastasel juhul võivad tulemused olla ettearvamatud.

1234567891011121314151617181920212223

Alamkordne Tingimuslik vormindamine Näide ()Dim MyRange vahemikuna'Loo vahemiku objektMäära MyRange = vahemik ("A1: A10")"Kustutage varasemad tingimusvormingudMyRange.FormatConditions.Delete'Lisa esimene reegelMyRange.FormatConditions.Add Type: = xlExpression, valem1: = _"= LEN (TRIM (A1)) = 0"MyRange.FormatConditions (1). Interior.Pattern = xlNone"Lisage teine ​​reegelMyRange.FormatConditions.Add Type: = xlCellValue, Operaator: = xlBetween, _Valem1: = "= 100", Valem2: = "= 150"MyRange.FormatConditions (2). Interjöör. Värv = RGB (255, 0, 0)"Lisage kolmas reegelMyRange.FormatConditions.Add Type: = xlCellValue, Operaator: = xlLess, _Valem1: = "= 100"MyRange.FormatConditions (3). Interjöör.Värv = vbBlue"Lisage neljas reegelMyRange.FormatConditions.Add Type: = xlCellValue, Operaator: = xlGreater, _Valem1: = "= 150"MyRange.FormatConditions (4). Interjöör. Värv = RGB (0, 255, 0)End Sub

See kasutab xlExpression tüüpi ja seejärel kasutab Exceli standardvalemit, et teha kindlaks, kas lahter on numbrilise väärtuse asemel tühi.

Objekt FormatConditions on osa objektist Range. See toimib samamoodi nagu kogumik, mille indeks algab 1. Saate seda objekti itereerida, kasutades nuppu For… Next või For … Every loop.

Reegli kustutamine

Mõnikord peate võib -olla kustutama ühe reegli mitmest reeglist, kui see ei vasta andmenõuetele.

12345678910111213

Alam KustutaConditionalFormattingExample ()Dim MyRange vahemikuna'Loo vahemiku objektMäära MyRange = vahemik ("A1: A10")"Kustutage varasemad tingimusvormingudMyRange.FormatConditions.Delete'Lisa esimene reegelMyRange.FormatConditions.Add Type: = xlCellValue, Operaator: = xlBetween, _Valem1: = "= 100", Valem2: = "= 150"MyRange.FormatConditions (1). Interjöör. Värv = RGB (255, 0, 0)'Kustuta reegelMyRange.FormatConditions (1). KustutaEnd Sub

See kood loob uue reegli vahemikule A1: A10 ja kustutab selle. Kustutamiseks peate kasutama õiget registrinumbrit, nii et kontrollige Exceli kasutajaliideses nuppu „Reeglite haldamine” (see näitab reegleid täitmise järjekorras), et tagada õige indeksinumbri saamine. Pange tähele, et Excelis ei ole tühistamisvõimalust, kui kustutate VBA-s tingimusliku vormindamise reegli, erinevalt sellest, kui teete seda Exceli kasutajaliidese kaudu.

Reegli muutmine

Kuna reeglid on teatud vahemikul põhinevate objektide kogum, saate VBA abil teatud reegleid hõlpsalt muuta. Reegli lisamisel on tegelikud atribuudid kirjutuskaitstud, kuid nende muutmiseks saate kasutada meetodit Muuda. Selliseid atribuute nagu värvid loetakse / kirjutatakse.

123456789101112131415

Sub ChangeConditionalFormattingExample ()Dim MyRange vahemikuna'Loo vahemiku objektMäära MyRange = vahemik ("A1: A10")"Kustutage varasemad tingimusvormingudMyRange.FormatConditions.Delete'Lisa esimene reegelMyRange.FormatConditions.Add Type: = xlCellValue, Operaator: = xlBetween, _Valem1: = "= 100", Valem2: = "= 150"MyRange.FormatConditions (1). Interjöör. Värv = RGB (255, 0, 0)'Muuda reeglitMyRange.FormatConditions (1) .Modify xlCellValue, xlLess, "10"„Muuda reegli värviMyRange.FormatConditions (1). Interjöör.Värv = vbGreenEnd Sub

See kood loob vahemiku objekti (A1: A10) ja lisab reegli numbritele vahemikus 100 kuni 150. Kui tingimus on tõene, muutub lahtri värv punaseks.

Seejärel muudab kood reegli numbriteks alla 10. Kui tingimus on tõene, muutub lahtri värv nüüd roheliseks.

Astmelise värviskeemi kasutamine

Exceli tingimusvormingus saab kasutada astmelisi värve kasvavas või kahanevas järjekorras.

See on väga kasulik, kui teil on selliseid andmeid nagu müüginäitajad geograafilise piirkonna, linnatemperatuuri või linnadevahelise kauguse järgi. Kasutades VBA-d, on teil täiendav eelis, et saate valida oma astmelise värviskeemi, mitte standardseid, mida Exceli kasutajaliideses pakutakse.

1234567891011121314151617181920212223242526272829

Sub GraduatedColors ()Dim MyRange vahemikuna'Loo vahemiku objektMäära MyRange = vahemik ("A1: A10")"Kustutage varasemad tingimusvormingudMyRange.FormatConditions.Delete'Määrake skaala tüüpMyRange.FormatConditions.AddColorScale ColorScaleType: = 3'Valige vahemiku madalaima väärtuse jaoks värvMyRange.FormatConditions (1). ColorScaleCriteria (1). Tüüp = _xlConditionValueLowestValueMyRange.FormatConditions (1) .ColorScaleCriteria (1) .FormatColor.Värv = 7039480Lõpeta'Valige vahemiku keskmiste väärtuste jaoks värvMyRange.FormatConditions (1). ColorScaleCriteria (2). Tüüp = _xlConditionValuePercentileMyRange.FormatConditions (1). ColorScaleCriteria (2). Väärtus = 50'Valige vahemiku keskpunkti värvMyRange.FormatConditions (1) .ColorScaleCriteria (2) .FormatColor.Värv = 8711167Lõpeta'Valige vahemiku kõrgeima väärtuse jaoks värvMyRange.FormatConditions (1). ColorScaleCriteria (3). Tüüp = _xlConditionValueHighestValueMyRange.FormatConditions (1) .ColorScaleCriteria (3) .FormatColor.Värv = 8109667LõpetaEnd Sub

Selle koodi käivitamisel liigub see lahtrite värvid vastavalt kasvavatele väärtustele vahemikus A1: A10.

See on väga muljetavaldav viis andmete kuvamiseks ja köidab kindlasti kasutajate tähelepanu.

Tingimuslik vormindamine veaväärtuste jaoks

Kui teil on tohutul hulgal andmeid, võite oma erinevatel töölehtedel vea väärtusest lihtsalt ilma jääda. Kui see esitatakse kasutajale lahendamata, võib see põhjustada suuri probleeme ja kasutaja kaotab usalduse numbrite vastu. See kasutab lahtri hindamiseks reeglitüüpi xlExpression ja Exceli funktsiooni IsError.

Saate luua koodi nii, et kõigi vigadega lahtrite lahtrivärv oleks punane:

1234567891011

AlamvigaConditionalFormattingExample ()Dim MyRange vahemikuna'Loo vahemiku objektMäära MyRange = vahemik ("A1: A10")"Kustutage varasemad tingimusvormingudMyRange.FormatConditions.Delete'Lisa veareegelMyRange.FormatConditions.Add Type: = xlExpression, Formula1: = "= IsError (A1) = true"'Seadke interjööri värv punaseksMyRange.FormatConditions (1). Interjöör. Värv = RGB (255, 0, 0)End Sub

Tingimuslik vormindamine mineviku kuupäevade jaoks

Võimalik, et impordite andmeid, kus soovite esile tõsta mineviku kuupäevi. Selle näiteks võib olla võlgnike aruanne, kus soovite, et kõik vanad üle 30 päeva vanad arve kuupäevad oleksid silmapaistvad.

See kood kasutab kuupäevade hindamiseks reeglitüüpi xlExpression ja Exceli funktsiooni.

1234567891011

AlamkuupäevInPastConditionalFormattingExample ()Dim MyRange vahemikuna„Looge vahemiku objekt kuupäevade veeru põhjalMäära MyRange = vahemik ("A1: A10")"Kustutage varasemad tingimusvormingudMyRange.FormatConditions.Delete„Lisage mineviku kuupäevade veareegelMyRange.FormatConditions.Add Type: = xlExpression, Formula1: = "= Now ()-A1> 30"'Seadke interjööri värv punaseksMyRange.FormatConditions (1). Interjöör. Värv = RGB (255, 0, 0)End Sub

See kood võtab vahemikus A1: A10 mitu kuupäeva ja määrab lahtri värvi punaseks iga kuupäeva puhul, mis on möödunud üle 30 päeva.

Tingimuses kasutatavas valemis annab Now () praeguse kuupäeva ja kellaaja. Nii arvutatakse arvutit uuesti iga kord töölehe ümberarvutamisel, nii et vorming muutub ühest päevast teise.

Andmeribade kasutamine VBA tingimusvormingus

VBA abil saate numbrivahemikule lisada andmeribasid. Need on peaaegu nagu minidiagrammid ja annavad kohe ülevaate sellest, kui suured numbrid on üksteise suhtes. Andmeribade vaikeväärtuste aktsepteerimisega on koodi kirjutamine väga lihtne.

123456

AlamandribaFormattingExample ()Dim MyRange vahemikunaMäära MyRange = vahemik ("A1: A10")MyRange.FormatConditions.DeleteMyRange.FormatConditions.AddatabarEnd Sub

Teie andmed näevad töölehel välja järgmised:

Ikoonide kasutamine VBA tingimusvormingus

Tingimusvormingu abil saate töölehele oma numbrite kõrvale ikoone panna. Ikoonid võivad olla nooled või ringid või mitmesugused muud kujundid. Selles näites lisab kood numbritele nooleikoone nende protsentuaalsete väärtuste põhjal:

12345678910111213141516171819202122232425

AlamikoonikomplektidExample ()Dim MyRange vahemikuna'Loo vahemiku objektMäära MyRange = vahemik ("A1: A10")"Kustutage varasemad tingimusvormingudMyRange.FormatConditions.Delete'Lisa ikoonikomplekt objektile FormatConditionsMyRange.FormatConditions.AddIconSetCondition„Seadke ikoonikomplekt nooltele - tingimus 1Rakendusega MyRange.FormatConditions (1).IconSet = ActiveWorkbook.IconSets (xl3Arrows)Lõpeta"määrake vajaliku protsentuaalse väärtuse jaoks ikoonikriteeriumid - tingimus 2MyRange.FormatConditions (1). IconCriteria (2).Type = xlConditionValuePercent.Väärtus = 33.Operator = xlGreaterEqualLõpeta"määrake nõutava protsentuaalse väärtuse jaoks ikoonikriteeriumid - tingimus 3MyRange.FormatConditions (1). IconCriteria (3).Type = xlConditionValuePercent.Väärtus = 67.Operator = xlGreaterEqualLõpetaEnd Sub

See annab kohe ülevaate, kas number on kõrge või madal. Pärast selle koodi käivitamist näeb teie tööleht välja selline:

Tingimusvormingu kasutamine viie parima esiletoomiseks

VBA -koodi abil saate andmevahemiku 5 parimat numbrit esile tõsta. Kasutate parameetrit nimega „AddTop10”, kuid saate koodis olevale järjekohale määrata väärtuse 5. Kasutaja võib soovida näha vahemiku kõrgeimaid numbreid, ilma et peaks andmeid esmalt sorteerima.

1234567891011121314151617181920212223

Sub Top5Example ()Dim MyRange vahemikuna'Loo vahemiku objektMäära MyRange = vahemik ("A1: A10")"Kustutage varasemad tingimusvormingudMyRange.FormatConditions.Delete„Lisage Top10 tingimusMyRange.FormatConditions.AddTop10Rakendusega MyRange.FormatConditions (1)'Määra parameeter ülalt alla.TopBottom = xlTop10Top„Määrake ainult top 5.Rank = 5LõpetaMyRange.FormatConditions (1). Font'Määrake fondi värv.Värv = -16383844LõpetaMyRange.FormatConditions (1). Sisemine'Määrake lahtri taustavärv.Värv = 13551615LõpetaEnd Sub

Pärast koodi käivitamist näeksid teie töölehe andmed välja järgmised:

Pange tähele, et väärtus 145 kuvatakse kaks korda, nii et kuus lahtrit on esile tõstetud.

StopIfTrue ja SetFirstPriority parameetrite tähtsus

StopIfTrue on oluline, kui lahtrivahemikul on mitu tingimusliku vormindamise reeglit. Üks vahemik lahtris võib täita esimest reeglit, kuid see võib rahuldada ka järgmisi reegleid. Arendajana soovite võib -olla kuvada ainult esimese reegli vorminduse. Muud reeglite kriteeriumid võivad kattuda ja teha soovimatuid muudatusi, kui reeglite loendis jätkatakse.

Selle parameetri vaikeväärtus on Tõene, kuid saate seda muuta, kui soovite selle lahtri kõiki muid reegleid arvesse võtta.

1	MyRange. FormatConditions (1) .StopIfTrue = Vale

Parameeter SetFirstPriority määrab, kas seda tingimuse reeglit hinnatakse kõigepealt, kui selle lahtri jaoks on mitu reeglit.

1	MyRange. FormatConditions (1). SetFirstPriority

See nihutab selle reegli positsiooni vormingutingimuste kogumi 1. positsioonile ja kõik muud reeglid nihutatakse allapoole koos muudetud indeksinumbritega. Ettevaatust, kui muudate koodireegleid indeksnumbreid kasutades. Peate veenduma, et muudate või kustutate õige reegli.

Saate muuta reegli prioriteeti.

1	MyRange. Formaat Tingimused (1). Prioriteet = 3

See muudab tingimusvormingu loendis teiste reeglite suhtelisi positsioone.

Tingimusliku vormindamise kasutamine, viidates muudele lahtriväärtustele

See on üks asi, mida Exceli tingimusvorming ei saa teha. Selle jaoks saate siiski luua oma VBA -koodi.

Oletame, et teil on andmete veerg ja iga numbri kõrvalolevas lahtris on tekst, mis näitab, milline vormindamine peaks iga numbri puhul toimuma.

Järgmine kood jookseb teie numbrite loendist alla, otsige teksti vormindamiseks kõrvalolevast lahtrist ja vormindage seejärel number vastavalt vajadusele:

123456789101112131415161718192021

AlamviideToAnotherCellForConditionalFormatting ()„Looge muutujad, et hoida tabeli andmete ridade arvuDim RR Kui kaua, N nii kaua'Jäädvustage tabelite andmevahemiku ridade arvRRow = ActiveSheet.UsedRange.Rows.Count„Korda kõiki tabeliandmete vahemiku riduN = 1 jaoks RRow„Kasutage lauset Vali juhtum, et hinnata vormindamist veeru 2 aluselValige Case ActiveSheet.Cells (N, 2) .Väärtus"Muutke sisemuse värv siniseksKott "Sinine"ActiveSheet.Cells (N, 1). Interjöör.Värv = vbSinine'Muutke sisemus punaseksJuhtum "Punane"ActiveSheet.Cells (N, 1). Interjöör.Värv = vbRed'Muutke sisemus roheliseksJuhtum "Roheline"ActiveSheet. Lahtrid (N, 1). Interjöör. Värv = vbrohelineLõpp ValiJärgmine N.End Sub

Kui see kood on käivitatud, näeb teie tööleht välja järgmine:

Vormindamiseks viidatud lahtrid võivad olla töölehel kõikjal või isegi mõnel teisel töövihiku töölehel. Vormindustingimuste seadmiseks võite kasutada mis tahes vormis teksti ja teie kujutlusvõime piirab teid ainult selle koodi kasutamisel.

Operaatorid, mida saab kasutada tingimusliku vormindamise avaldustes

Nagu eelmistes näidetes nägite, kasutatakse operaatoreid, et määrata kindlaks, kuidas tingimuste väärtusi hinnatakse, nt. xlVahelt.

Sõltuvalt sellest, kuidas soovite oma reeglikriteeriume määrata, saab kasutada mitmeid neid operaatoreid.

Nimi	Väärtus	Kirjeldus
xlVahelt	1	Vahel. Saab kasutada ainult kahe valemi korral.
xlVõrdne	3	Võrdne.
xl Suurem	5	Suurem kui.
xlGreaterEqual	7	Suurem või võrdne.
xlVähem	6	Vähem kui.
xlLessEqual	8	Vähem või võrdne.
xlNotBetween	2	Mitte vahel. Saab kasutada ainult kahe valemi korral.
xlNotEqual	4	Pole võrdne.