Ehdollisen muotoilun käyttäminen Excel VBA: n kanssa

Excelin ehdollinen muotoilu

Excelin ehdollisen muotoilun avulla voit määrittää säännöt, jotka määrittävät solujen muotoilun.

Voit esimerkiksi luoda säännön, joka korostaa tietyt ehdot täyttävät solut. Esimerkkejä ovat:

• Numerot, jotka kuuluvat tietylle alueelle (esim. Alle 0).
• Luettelon kymmenen parasta kohdetta.
• "Lämpökartan" luominen.
• "Kaavapohjaiset" säännöt lähes kaikille ehdollisille muotoiluille.

Excelissä ehdollinen muotoilu löytyy valintanauhan kohdasta Koti> Tyylit (ALT> H> L).

Jos haluat luoda oman säännön, napsauta "Uusi sääntö" ja uusi ikkuna avautuu:

Ehdollinen muotoilu VBA: ssa

Kaikkiin näihin ehdollisen muotoilun ominaisuuksiin pääsee VBA: n avulla.

Huomaa, että kun määrität ehdollisen muotoilun VBA-koodista, uudet parametrit näkyvät Excelin käyttöliittymän ehdollisessa muotoiluikkunassa ja näkyvät käyttäjälle. Käyttäjä voi muokata tai poistaa niitä, ellet ole lukinnut laskentataulukkoa.

Ehdolliset muotoilusäännöt tallennetaan myös laskentataulukon tallennuksen yhteydessä

Ehdollisia muotoilusääntöjä sovelletaan erityisesti tiettyyn laskentataulukkoon ja tiettyyn solualueeseen. Jos niitä tarvitaan muualla työkirjassa, ne on määritettävä myös kyseiselle laskentataulukolle.

Ehdollisen muotoilun käytännön käyttö VBA: ssa

Laskentataulukkoosi voidaan tuoda suuri osa raakadataa CSV-tiedostosta (pilkuilla erotetut arvot) tai tietokantataulukosta tai -kyselystä. Tämä voi kulkea koontinäyttöön tai raporttiin, ja muuttuvat numerot tuodaan ajanjaksolta toiselle.

Jos luku muuttuu ja on hyväksyttävän alueen ulkopuolella, voit korostaa tämän esim. solun taustaväri punaisena, ja voit tehdä tämän asettamalla ehdollisen muotoilun. Tällä tavalla käyttäjä vetoaa välittömästi tähän numeroon ja voi sitten tutkia, miksi näin tapahtuu.

Voit ottaa ehdollisen muotoilun käyttöön tai poistaa sen käytöstä VBA: n avulla. VBA: n avulla voit tyhjentää säännöt eri soluista tai ottaa ne uudelleen käyttöön. Saattaa olla tilanne, jossa epätavalliseen numeroon on täysin hyvä syy, mutta kun käyttäjä esittelee kojelaudan tai raportin korkeammalle johdolle, hän haluaa pystyä poistamaan ”hälytyskellot”.

Voit myös korostaa tuoduista raakatiedoista, missä numerot ovat naurettavan suuria tai naurettavan pieniä. Tuodut tietoalueet ovat yleensä eri kokoisia kullekin ajanjaksolle, joten voit käyttää VBA: ta uuden dataluokan koon arvioimiseen ja lisätä ehdollisen muotoilun vain tälle alueelle.

Sinulla voi myös olla tilanne, jossa on lajiteltu luettelo nimistä, joissa on numeerisia arvoja, esim. työntekijän palkka, tentit. Ehdollisella muotoilulla voit siirtyä asteikkoväreistä korkeimmasta matalimpaan, mikä näyttää erittäin vaikuttavalta esitystarkoituksiin.

Nimiluettelo ei kuitenkaan aina ole staattinen, ja voit käyttää VBA -koodia päivittääksesi asteikkoväreiden asteikon alueen koon muutosten mukaan.

Yksinkertainen esimerkki ehdollisen muodon luomisesta alueelle

Tämä esimerkki määrittää ehdollisen muotoilun laskentataulukon solualueelle (A1: A10). Jos alueen arvo on välillä 100 ja 150, solun taustaväri on punainen, muuten sillä ei ole väriä.

1234567891011121314

Ehdollinen muotoiluEsimerkki ()"Määritä alueDim MyRange alueenaAseta MyRange = Range (“A1: A10”)"Poista olemassa oleva ehdollinen muotoilu alueeltaMyRange.FormatConditions.Delete"Käytä ehdollista muotoiluaMyRange.FormatConditions.Add Type: = xlCellValue, Operaattori: = xlBetween, _Kaava1: = "= 100", Kaava2: = "= 150"MyRange.FormatConditions (1) .Interior.Color = RGB (255, 0, 0)End Sub

Huomaa, että ensin määritämme alueen MyRange ehdollisen muotoilun käyttöön.

Seuraavaksi poistamme alueen kaikki ehdolliset muotoilut. Tämä on hyvä idea estää saman säännön lisääminen joka kerta, kun koodi suoritetaan (se ei tietenkään sovellu kaikissa olosuhteissa).

Värit annetaan numeroarvoina. Tätä varten on hyvä käyttää RGB -merkintöjä (punainen, vihreä, sininen). Voit käyttää tähän vakiovärivakioita esim. vbRed, vbBlue, mutta sinulla on vain kahdeksan värivaihtoehtoa.

Saatavilla on yli 16,7 miljoonaa väriä, ja RGB: n avulla voit käyttää niitä kaikkia. Tämä on paljon helpompaa kuin yrittää muistaa, mikä numero liittyy mihin tahansa väriin. Jokainen kolmesta RGB -värinumerosta on 0–255.

Huomaa, että "xlBetween" -parametri sisältää kaiken, joten solun arvot 100 tai 150 täyttävät ehdon.

Moniehtoinen muotoilu

Voit halutessasi määrittää useita ehdollisia sääntöjä tietoalueellesi, jotta kaikki alueen arvot kuuluvat eri ehtojen piiriin:

12345678910111213141516171819

Sub MultipleConditionalFormattingExample ()Dim MyRange alueena'Luo alueobjektiAseta MyRange = Range (“A1: A10”)'Poista aiemmat ehdolliset muodotMyRange.FormatConditions.Delete'Lisää ensimmäinen sääntöMyRange.FormatConditions.Add Type: = xlCellValue, Operaattori: = xlBetween, _Kaava1: = "= 100", Kaava2: = "= 150"MyRange.FormatConditions (1) .Interior.Color = RGB (255, 0, 0)'Lisää toinen sääntöMyRange.FormatConditions.Add Type: = xlCellValue, Operaattori: = xlLess, _Kaava1: = "= 100"MyRange.FormatConditions (2) .Interior.Color = vbBlue'Lisää kolmas sääntöMyRange.FormatConditions.Add Type: = xlCellValue, Operaattori: = xlGreater, _Kaava1: = "= 150"MyRange.FormatConditions (3) .Interior.Color = vbKeltainenEnd Sub

Tämä esimerkki määrittää ensimmäisen säännön kuten aiemmin, ja solun väri on punainen, jos solun arvo on välillä 100 ja 150.

Sitten lisätään vielä kaksi sääntöä. Jos solun arvo on alle 100, solun väri on sininen ja jos se on suurempi kuin 150, solun väri on keltainen.

Tässä esimerkissä sinun on varmistettava, että kaikki numeroiden mahdollisuudet on katettu ja että säännöt eivät ole päällekkäisiä.

Jos tyhjiä soluja on tällä alueella, ne näkyvät sinisinä, koska Excel pitää niiden arvoina edelleen alle 100.

Tapa kiertää on lisätä toinen ehto lausekkeeksi. Tämä on lisättävä koodin ensimmäisenä ehtosääntönä. On erittäin tärkeää, että jos on useita sääntöjä, on saatava oikea suoritusjärjestys, muuten tulokset voivat olla arvaamattomia.

1234567891011121314151617181920212223

Sub MultipleConditionalFormattingExample ()Dim MyRange alueena'Luo alueobjektiAseta MyRange = Range (“A1: A10”)'Poista aiemmat ehdolliset muodotMyRange.FormatConditions.Delete'Lisää ensimmäinen sääntöMyRange.FormatConditions.Add Type: = xlExpression, Formula1: = _"= LEN (TRIM (A1)) = 0"MyRange.FormatConditions (1) .Interior.Pattern = xlNone'Lisää toinen sääntöMyRange.FormatConditions.Add Type: = xlCellValue, Operaattori: = xlBetween, _Kaava1: = "= 100", Kaava2: = "= 150"MyRange.FormatConditions (2) .Interior.Color = RGB (255, 0, 0)'Lisää kolmas sääntöMyRange.FormatConditions.Add Type: = xlCellValue, Operaattori: = xlLess, _Kaava1: = "= 100"MyRange.FormatConditions (3) .Interior.Color = vbBlue"Lisää neljäs sääntöMyRange.FormatConditions.Add Type: = xlCellValue, Operaattori: = xlGreater, _Kaava1: = "= 150"MyRange.FormatConditions (4) .Interior.Color = RGB (0, 255, 0)End Sub

Tämä käyttää xlExpression -tyyppiä ja määrittää sen jälkeen Excel -vakiomallin avulla, onko solu tyhjä numeerisen arvon sijaan.

FormatConditions -objekti on osa Range -objektia. Se toimii samalla tavalla kuin kokoelma, jonka indeksi alkaa 1. Voit iteroida tämän objektin käyttämällä For… Next tai For… Jokainen silmukka.

Säännön poistaminen

Joskus sinun on ehkä poistettava yksittäinen sääntö useiden sääntöjen joukosta, jos se ei täytä tietovaatimuksia.

12345678910111213

Sub DeleteConditionalFormattingExample ()Dim MyRange alueena'Luo alueobjektiAseta MyRange = Range (“A1: A10”)'Poista aiemmat ehdolliset muodotMyRange.FormatConditions.Delete'Lisää ensimmäinen sääntöMyRange.FormatConditions.Add Type: = xlCellValue, Operaattori: = xlBetween, _Kaava1: = "= 100", Kaava2: = "= 150"MyRange.FormatConditions (1) .Interior.Color = RGB (255, 0, 0)'Poista sääntöMyRange.FormatConditions (1) PoistaEnd Sub

Tämä koodi luo uuden säännön alueelle A1: A10 ja poistaa sen. Poistaessasi sinun on käytettävä oikeaa hakemistonumeroa, joten tarkista Excelin käyttöliittymän Hallitse sääntöjä (tämä näyttää säännöt suoritusjärjestyksessä) varmistaaksesi, että saat oikean hakemistonumeron. Huomaa, että Excelissä ei ole peruutusmahdollisuutta, jos poistat ehdollisen muotoilusäännön VBA: ssa, toisin kuin jos teet sen Excelin käyttöliittymän kautta.

Säännön muuttaminen

Koska säännöt ovat kokoelma objekteja, jotka perustuvat tiettyyn alueeseen, voit helposti muuttaa tiettyjä sääntöjä VBA: n avulla. Todelliset ominaisuudet, kun sääntö on lisätty, ovat vain luku -muodossa, mutta voit muuttaa niitä Muokkaa-menetelmällä. Ominaisuudet, kuten värit, luetaan / kirjoitetaan.

123456789101112131415

AlamuutosConditionalFormattingExample ()Dim MyRange alueena'Luo alueobjektiAseta MyRange = Range (“A1: A10”)'Poista aiemmat ehdolliset muodotMyRange.FormatConditions.Delete'Lisää ensimmäinen sääntöMyRange.FormatConditions.Add Type: = xlCellValue, Operaattori: = xlBetween, _Kaava1: = "= 100", Kaava2: = "= 150"MyRange.FormatConditions (1) .Interior.Color = RGB (255, 0, 0)'Muuta sääntöäMyRange.FormatConditions (1) .Modify xlCellValue, xlLess, "10""Vaihda säännön väriMyRange.FormatConditions (1) .Interior.Color = vbVihreäEnd Sub

Tämä koodi luo alueobjektin (A1: A10) ja lisää säännön numeroille 100 ja 150. Jos ehto on tosi, solun väri muuttuu punaiseksi.

Sitten koodi muuttaa säännön numeroiksi, jotka ovat pienempiä kuin 10. Jos ehto on totta, solun väri muuttuu nyt vihreäksi.

Valmistetun värimallin käyttäminen

Excelin ehdollisessa muotoilussa voidaan käyttää asteikkovärejä nousevassa tai laskevassa järjestyksessä.

Tämä on erittäin hyödyllistä, jos sinulla on tietoja, kuten myyntiluvut maantieteellisen alueen, kaupunkilämpötilojen tai kaupunkien välisen etäisyyden mukaan. VBA: n avulla sinulla on lisäetu, että voit valita oman asteikkoväriisi Excel-käyttöliittymän vakiovärien sijasta.

1234567891011121314151617181920212223242526272829

Välttämättömät värit ()Dim MyRange alueena'Luo alueobjektiAseta MyRange = Range (“A1: A10”)'Poista aiemmat ehdolliset muodotMyRange.FormatConditions.Delete'Määrittele mittakaavan tyyppiMyRange.FormatConditions.AddColorScale ColorScaleType: = 3'Valitse väri alimmalle arvolle alueellaMyRange.FormatConditions (1) .ColorScaleCriteria (1) .Type = _xlConditionValueLowestValueMyRange.FormatConditions (1) .ColorScaleCriteria (1) .FormatColor.Väri = 7039480Lopeta'Valitse väri alueen keskiarvoilleMyRange.FormatConditions (1) .ColorScaleCriteria (2) .Type = _xlConditionValuePercentileMyRange.FormatConditions (1) .ColorScaleCriteria (2). Arvo = 50'Valitse alueen keskipisteen väriMyRange.FormatConditions (1) .ColorScaleCriteria (2) .FormatColor.Väri = 8711167Lopeta'Valitse väri, jolla on alueen suurin arvoMyRange.FormatConditions (1) .ColorScaleCriteria (3) .Type = _xlConditionValueHighestValueMyRange.FormatConditions (1) .ColorScaleCriteria (3) .FormatColor.Väri = 8109667LopetaEnd Sub

Kun tämä koodi suoritetaan, se siirtyy solun väreihin nousevien arvojen mukaan alueella A1: A10.

Tämä on erittäin vaikuttava tapa näyttää tiedot ja kiinnittää varmasti käyttäjien huomion.

Virhearvojen ehdollinen muotoilu

Kun sinulla on valtava määrä dataa, saatat helposti menettää virhearvon eri laskentataulukoissasi. Jos tämä esitetään käyttäjälle ratkaisematta, se voi johtaa suuriin ongelmiin ja käyttäjän luottamuksen menettämiseen numeroihin. Tämä käyttää solutyyppiä xlExpression ja IsErrorin Excel -funktiota solun arvioimiseen.

Voit luoda koodin siten, että kaikissa virheellisissä soluissa solun väri on punainen:

1234567891011

AlavirheConditionalFormattingExample ()Dim MyRange alueena'Luo alueobjektiAseta MyRange = Range (“A1: A10”)'Poista aiemmat ehdolliset muodotMyRange.FormatConditions.Delete'Lisää virhesääntöMyRange.FormatConditions.Add Type: = xlExpression, Formula1: = "= IsError (A1) = true"'Aseta sisätilojen väri punaiseksiMyRange.FormatConditions (1) .Interior.Color = RGB (255, 0, 0)End Sub

Ehdollinen muotoilu päivämäärille menneisyydessä

Saatat tuoda tietoja, joissa haluat korostaa menneitä päivämääriä. Esimerkki tästä voi olla velallisraportti, jossa haluat, että vanhat yli 30 päivän ikäiset laskujen päivämäärät erottuvat toisistaan.

Tämä koodi käyttää xlExpression sääntötyyppiä ja Excel -funktiota päivämäärien arvioimiseen.

1234567891011

Sub DateInPastConditionalFormattingExample ()Dim MyRange alueenaLuo alueobjekti päivämääräsarakkeen perusteellaAseta MyRange = Range (“A1: A10”)'Poista aiemmat ehdolliset muodotMyRange.FormatConditions.Delete'Lisää virhesääntö menneille päivämäärilleMyRange.FormatConditions.Add Type: = xlExpression, Formula1: = "= Now ()-A1> 30"'Aseta sisätilojen väri punaiseksiMyRange.FormatConditions (1) .Interior.Color = RGB (255, 0, 0)End Sub

Tämä koodi kestää päivämäärät alueella A1: A10 ja asettaa solun värin punaiseksi kaikille päivämäärille, jotka ovat yli 30 päivää aiemmin.

Ehdossa käytettävässä kaavassa Now () antaa nykyisen päivämäärän ja kellonajan. Tämä laskee jatkuvasti joka kerta, kun laskentataulukko lasketaan uudelleen, joten muotoilu muuttuu päivästä toiseen.

Tietopalkkien käyttö VBA: n ehdollisessa muotoilussa

VBA: n avulla voit lisätä tietopalkkeja numeroalueelle. Nämä ovat melkein kuin minikaavioita ja antavat välittömän kuvan siitä, kuinka suuret numerot ovat toisiinsa nähden. Hyväksymällä datapalkkien oletusarvot koodi on erittäin helppo kirjoittaa.

123456

AlidatapalkkiFormattingExample ()Dim MyRange alueenaAseta MyRange = Range (“A1: A10”)MyRange.FormatConditions.DeleteMyRange.FormatConditions.AddDatabarEnd Sub

Tiedot näyttävät tältä laskentataulukolta:

Kuvakkeiden käyttäminen ehdollisessa VBA -muotoilussa

Voit asettaa ehdollisen muotoilun avulla kuvakkeita numeroidesi viereen laskentataulukkoon. Kuvakkeet voivat olla nuolia tai ympyröitä tai erilaisia ​​muita muotoja. Tässä esimerkissä koodi lisää numeroihin nuolikuvakkeita niiden prosentuaalisten arvojen perusteella:

12345678910111213141516171819202122232425

AlakuvakeSetsExample ()Dim MyRange alueena'Luo alueobjektiAseta MyRange = Range (“A1: A10”)'Poista aiemmat ehdolliset muodotMyRange.FormatConditions.Delete'Lisää kuvakesarja FormatConditions -objektiinMyRange.FormatConditions.AddIconSetCondition'Aseta kuvakkeet nuoliksi - ehto 1MyRange.FormatConditions (1).IconSet = ActiveWorkbook.IconSets (xl3Arrows)Lopeta'aseta kuvakkeen ehdot vaaditulle prosenttiosuudelle - ehto 2MyRange.FormatConditions (1) .IconCriteria (2).Type = xlConditionValuePercent.Arvo = 33.Operator = xlGreaterEqualLopeta'aseta kuvakkeen ehdot vaaditulle prosenttiosuudelle - ehto 3MyRange.FormatConditions (1) .IconCriteria (3).Type = xlConditionValuePercentArvo = 67.Operator = xlGreaterEqualLopetaEnd Sub

Tämä antaa välittömän näkymän, joka osoittaa, onko luku suuri vai pieni. Tämän koodin suorittamisen jälkeen laskentataulukosi näyttää tältä:

Ehdollisen muotoilun käyttäminen korostaen viisi parasta

Voit käyttää VBA -koodia korostamaan 5 parasta numeroa tietoalueella. Käytät parametria nimeltä "AddTop10", mutta voit säätää koodin sijoituksen numeroksi 5. Käyttäjä saattaa haluta nähdä alueen suurimmat luvut ilman, että tietoja on ensin lajiteltava.

1234567891011121314151617181920212223

Sub Top5Example ()Dim MyRange alueena'Luo alueobjektiAseta MyRange = Range (“A1: A10”)'Poista aiemmat ehdolliset muodotMyRange.FormatConditions.Delete'Lisää Top10 -ehtoMyRange.FormatConditions.AddTop10MyRange.FormatConditions (1)'Aseta parametri ylhäältä alas.TopBottom = xlTop10Top'Aseta vain top 5Sijoitus = 5LopetaMyRange.FormatConditions (1)'Aseta fontin väri.Väri = -16383844LopetaMyRange.FormatConditions (1)'Aseta solun taustaväri.Väri = 13551615LopetaEnd Sub

Laskentataulukon tiedot näyttäisivät tältä koodin suorittamisen jälkeen:

Huomaa, että arvo 145 näkyy kahdesti, joten kuusi solua on korostettu.

StopIfTrue- ja SetFirstPriority -parametrien merkitys

StopIfTrue on tärkeä, jos solualueella on useita ehdollisia muotoilusääntöjä. Yksi alueen solu voi täyttää ensimmäisen säännön, mutta se voi täyttää myös seuraavat säännöt. Kehittäjänä saatat haluta sen näyttävän vain ensimmäisen säännön muotoilun. Muut sääntöehdot voivat olla päällekkäisiä ja tehdä tahattomia muutoksia, jos niiden sallitaan jatkaa sääntöjen luetteloa.

Tämän parametrin oletusarvo on True, mutta voit muuttaa sitä, jos haluat, että kaikki muut solun säännöt otetaan huomioon:

1	MyRange. FormatConditions (1) .StopIfTrue = False

SetFirstPriority -parametri määrää, arvioidaanko ehtoa koskeva sääntö ensin, kun kyseiselle solulle on useita sääntöjä.

1	MyRange. FormatConditions (1) .SetFirstPriority

Tämä siirtää kyseisen säännön sijainnin muotoehtojen kokoelman kohtaan 1, ja kaikki muut säännöt siirretään alaspäin muutetuilla indeksinumeroilla. Varo, jos teet muutoksia koodin sääntöihin indeksinumeroiden avulla. Sinun on varmistettava, että muutat tai poistat oikean säännön.

Voit muuttaa säännön prioriteettia:

1	MyRange. FormatConditions (1). Prioriteetti = 3

Tämä muuttaa ehdollisten muotojen luettelon muiden sääntöjen suhteellisia sijainteja.

Ehdollisen muotoilun käyttäminen viittaamalla muihin solun arvoihin

Tämä on yksi asia, jota Excelin ehdollinen muotoilu ei voi tehdä. Voit kuitenkin rakentaa oman VBA -koodisi tätä varten.

Oletetaan, että sinulla on tietosarake ja jokaisen numeron vieressä olevassa solussa on tekstiä, joka osoittaa, mitä muotoilua tulisi suorittaa jokaiselle numerolle.

Seuraava koodi tyhjentää numeroluettelosi, etsi viereisestä solusta tekstin muotoilua ja muotoile sitten numero tarpeen mukaan:

123456789101112131415161718192021

Sub ReferToAnotherCellForConditionalFormatting ()'Luo muuttujia, jotka säilyttävät taulukkotietojen rivien määränDim RR Niin kauan, N niin kauan'Kaappaa taulukon tietoalueen rivien määräRRow = ActiveSheet.UsedRange.Rows.Count'Toista kaikki taulukon tietoalueen rivitN = 1 RRow'Valitse Select Case -lausekkeen avulla muotoilu sarakkeen 2 perusteellaValitse Case ActiveSheet.Cells (N, 2)'Käännä sisätilojen väri siniseksiKotelo "sininen"ActiveSheet.Cells (N, 1) .Interior.Color = vbBlue'Käännä sisätilojen väri punaiseksiKotelo "Punainen"ActiveSheet.Cells (N, 1) .Interior.Color = vbRed'Käännä sisätilojen väri vihreäksiKotelo "Vihreä"ActiveSheet.Cells (N, 1) .Interior.Color = vbVihreäLopeta ValitseSeuraava N.End Sub

Kun tämä koodi on suoritettu, laskentataulukkosi näyttää nyt tältä:

Muotoiluun viitatut solut voivat olla missä tahansa laskentataulukossa tai jopa toisella työkirjan laskentataulukolla. Voit käyttää mitä tahansa tekstimuotoa muotoilun ehdoksi, ja vain mielikuvituksesi rajoittaa käyttötarkoituksia, joihin voit käyttää tätä koodia.

Operaattorit, joita voidaan käyttää ehdollisissa muotoilulausekkeissa

Kuten olet nähnyt edellisissä esimerkeissä, operaattoreita käytetään määrittämään, kuinka ehtoarvoja arvioidaan, esim. xlVäliin.

Näitä operaattoreita voidaan käyttää useita sen mukaan, miten haluat määrittää sääntöehdot.

Nimi	Arvo	Kuvaus
xlVäliin	1	Välillä. Voidaan käyttää vain, jos mukana on kaksi kaavaa.
xlTasainen	3	Yhtä suuri.
xlSuuri	5	Suurempi kuin.
xlGreaterEqual	7	Suurempi tai yhtä suuri kuin.
xlVähemmän	6	Vähemmän kuin.
xlLessEqual	8	Pienempi kuin tai yhtä suuri kuin.
xlNotBetween	2	Ei välillä. Voidaan käyttää vain, jos mukana on kaksi kaavaa.
xlEi yhtä	4	Ei tasa -arvoinen.