Pogojno oblikovanje programa Excel
Excel Pogojno oblikovanje vam omogoča, da določite pravila, ki določajo oblikovanje celic.
Ustvarite lahko na primer pravilo, ki označuje celice, ki izpolnjujejo določena merila. Primeri vključujejo:
- Številke, ki spadajo v določeno območje (npr. Manj kot 0).
- Prvih 10 elementov na seznamu.
- Ustvarjanje "toplotnega zemljevida".
- Pravila "na osnovi formul" za skoraj vsako pogojno oblikovanje.
V Excelu lahko pogojno oblikovanje najdete na traku pod Domov> Slogi (ALT> H> L).
Če želite ustvariti svoje pravilo, kliknite »Novo pravilo« in prikazalo se bo novo okno:
Pogojno oblikovanje v VBA
Do vseh teh funkcij pogojnega oblikovanja lahko dostopate z uporabo VBA.
Upoštevajte, da ko nastavite pogojno oblikovanje znotraj kode VBA, bodo vaši novi parametri prikazani v oknu pogojnega oblikovanja v prednjem delu Excela in bodo uporabniku vidni. Uporabnik jih bo lahko urejal ali izbrisal, razen če ste zaklenili delovni list.
Pravila pogojnega oblikovanja se shranijo tudi pri shranjevanju delovnega lista
Pravila pogojnega oblikovanja veljajo posebej za določen delovni list in za določen obseg celic. Če so potrebni drugje v delovnem zvezku, jih je treba nastaviti tudi na tem delovnem listu.
Praktične uporabe pogojnega oblikovanja v VBA
Morda imate na delovnem listu uvožen velik kos surovih podatkov iz datoteke CSV (vrednosti, ločene z vejicami) ali iz tabele baze podatkov ali poizvedbe. To se lahko pretaka v nadzorno ploščo ali poročilo s spreminjanjem številk, uvoženih iz enega obdobja v drugo.
Kadar se število spremeni in je zunaj sprejemljivega območja, lahko to označite npr. barvo ozadja celice v rdeči barvi, to pa lahko nastavite tudi s pogojnim oblikovanjem. Na ta način bo uporabnik takoj pritegnil to številko in nato lahko razišče, zakaj se to dogaja.
VBA lahko uporabite za vklop ali izklop pogojnega oblikovanja. Z VBA lahko počistite pravila za vrsto celic ali jih znova vklopite. Lahko pride do situacije, ko obstaja povsem dober razlog za nenavadno številko, toda ko uporabnik predstavi nadzorno ploščo ali se javi na višjo raven upravljanja, želijo odstraniti 'alarmne zvonce'.
Na surovih uvoženih podatkih boste morda želeli poudariti, kje so številke smešno velike ali smešno majhne. Uvoženi obseg podatkov je običajno za vsako obdobje drugačne velikosti, zato lahko z VBA ocenite velikost novega obsega podatkov in vstavite pogojno oblikovanje samo za to območje.
Morda imate tudi situacijo, ko obstaja razvrščen seznam imen s številskimi vrednostmi proti vsakemu, npr. plača zaposlenih, izpitne ocene. S pogojnim oblikovanjem lahko uporabite graduirane barve za premikanje od najvišjega do najnižjega, kar je za predstavitvene namene videti zelo impresivno.
Seznam imen pa ne bo vedno statične velikosti in s kodo VBA lahko osvežite lestvico graduiranih barv glede na spremembe velikosti obsega.
Preprost primer ustvarjanja pogojne oblike na območju
Ta primer nastavlja pogojno oblikovanje za vrsto celic (A1: A10) na delovnem listu. Če je število v razponu med 100 in 150, bo barva ozadja celice rdeča, sicer ne bo imela barve.
| 1234567891011121314 | Podpogojno formatiranjePrimer ()'Določite obsegZatemni MyRange As RangeNastavi MyRange = Obseg (“A1: A10”)'Izbriši obstoječe pogojno oblikovanje iz obsegaMyRange.FormatConditions.Delete„Uporabi pogojno oblikovanjeMyRange.FormatConditions.Add Type: = xlCellValue, Operator: = xlBetween, _Formula1: = "= 100", Formula2: = "= 150"MyRange.FormatConditions (1) .Interior.Color = RGB (255, 0, 0)End Sub |
Upoštevajte, da najprej določimo obseg MyRange uporabiti pogojno oblikovanje.
Nato izbrišemo obstoječe pogojno oblikovanje obsega. To je dobra ideja, da preprečite dodajanje istega pravila ob vsakem zagonu kode (seveda ne bo primerno v vseh okoliščinah).
Barve so podane s številskimi vrednostmi. Za to je dobro uporabiti zapis RGB (rdeča, zelena, modra). Za to lahko uporabite standardne barvne konstante, npr. vbRed, vbBlue, vendar ste omejeni na osem barvnih izbir.
Na voljo je več kot 16,7 milijona barv in z uporabo RGB lahko dostopate do vseh. To je veliko lažje kot poskušati zapomniti, katera številka ustreza kateri barvi. Vsaka od treh barvnih številk RGB je od 0 do 255.
Upoštevajte, da je parameter „xlBetween“ vključujoč, zato bodo vrednosti celic 100 ali 150 izpolnile pogoj.
Več pogojno oblikovanje
V svojem obsegu podatkov boste morda želeli nastaviti več pogojnih pravil, tako da bodo vse vrednosti v obsegu zajete v različnih pogojih:
| 12345678910111213141516171819 | Sub MultipleConditionalFormattingExample ()Zatemni MyRange As Range'Ustvari objekt obsegaNastavi MyRange = Obseg (“A1: A10”)'Izbrišite prejšnje pogojne oblikeMyRange.FormatConditions.Delete'Dodaj prvo praviloMyRange.FormatConditions.Add Type: = xlCellValue, Operator: = xlBetween, _Formula1: = "= 100", Formula2: = "= 150"MyRange.FormatConditions (1) .Interior.Color = RGB (255, 0, 0)'Dodaj drugo praviloMyRange.FormatConditions.Add Type: = xlCellValue, Operator: = xlLess, _Formula1: = "= 100"MyRange.FormatConditions (2) .Interior.Color = vbBlue'Dodaj tretje praviloMyRange.FormatConditions.Add Type: = xlCellValue, Operator: = xlGreater, _Formula1: = "= 150"MyRange.FormatConditions (3) .Interior.Color = vbYellowEnd Sub |
Ta primer nastavlja prvo pravilo kot prej, z barvo celice rdeče, če je vrednost celice med 100 in 150.
Nato se dodata še dve pravili. Če je vrednost celice manjša od 100, je barva celice modra, če pa je večja od 150, potem je barva celice rumena.
V tem primeru morate zagotoviti, da so zajete vse možnosti številk in da se pravila ne prekrivajo.
Če so v tem razponu prazne celice, bodo prikazane kot modre, ker jih Excel še vedno šteje za vrednost, manjšo od 100.
Pot do tega je, da kot izraz dodate še eno stanje. To je treba dodati kot prvo pravilo pogoja v kodi. Če obstaja več pravil, je zelo pomembno, da pravilno izvedete vrstni red izvedbe, sicer so lahko rezultati nepredvidljivi.
| 1234567891011121314151617181920212223 | Sub MultipleConditionalFormattingExample ()Zatemni MyRange As Range'Ustvari objekt obsegaNastavi MyRange = Obseg (“A1: A10”)'Izbrišite prejšnje pogojne oblikeMyRange.FormatConditions.Delete'Dodaj prvo praviloMyRange.FormatConditions.Ad Type: = xlExpression, Formula1: = _"= LEN (TRIM (A1)) = 0"MyRange.FormatConditions (1) .Interior.Pattern = xlNone'Dodaj drugo praviloMyRange.FormatConditions.Add Type: = xlCellValue, Operator: = xlBetween, _Formula1: = "= 100", Formula2: = "= 150"MyRange.FormatConditions (2) .Interior.Color = RGB (255, 0, 0)'Dodaj tretje praviloMyRange.FormatConditions.Add Type: = xlCellValue, Operator: = xlLess, _Formula1: = "= 100"MyRange.FormatConditions (3) .Interior.Color = vbBlue"Dodajte četrto praviloMyRange.FormatConditions.Add Type: = xlCellValue, Operator: = xlGreater, _Formula1: = "= 150"MyRange.FormatConditions (4) .Interior.Color = RGB (0, 255, 0)End Sub |
Ta uporablja vrsto xlExpression in nato uporabi standardno formulo Excel, da ugotovi, ali je celica prazna namesto numerične vrednosti.
Objekt FormatConditions je del predmeta Range. Deluje na enak način kot zbirka z indeksom, ki se začne na 1. Po tem objektu lahko potujete s tipko For … Next ali For … Vsaka zanka.
Brisanje pravila
Včasih boste morda morali izbrisati posamezno pravilo v nizu več pravil, če ne ustreza zahtevam po podatkih.
| 12345678910111213 | Sub DeleteConditionalFormattingExample ()Zatemni MyRange As Range'Ustvari objekt obsegaNastavi MyRange = Obseg (“A1: A10”)'Izbrišite prejšnje pogojne oblikeMyRange.FormatConditions.Delete'Dodaj prvo praviloMyRange.FormatConditions.Add Type: = xlCellValue, Operator: = xlBetween, _Formula1: = "= 100", Formula2: = "= 150"MyRange.FormatConditions (1) .Interior.Color = RGB (255, 0, 0)'Izbriši praviloMyRange.FormatConditions (1) .DeleteEnd Sub |
Ta koda ustvari novo pravilo za obseg A1: A10 in ga nato izbriše. Za brisanje morate uporabiti pravilno številko indeksa, zato preverite "Upravljanje pravil" na sprednjem delu Excela (to bo prikazalo pravila po vrstnem redu izvajanja), da zagotovite, da boste dobili pravilno številko indeksa. Upoštevajte, da v Excelu ni možnosti razveljavitve, če izbrišete pravilo pogojnega oblikovanja v VBA, za razliko od tega, če to storite prek vmesnika Excel.
Spreminjanje pravila
Ker so pravila zbirka predmetov, ki temeljijo na določenem obsegu, lahko preprosto spremenite določena pravila z uporabo VBA. Dejanske lastnosti, ko je pravilo dodano, so samo za branje, vendar jih lahko spremenite z metodo Modify. Lastnosti, kot so barve, se berejo / pišejo.
| 123456789101112131415 | Sub ChangeConditionalFormattingExample ()Zatemni MyRange As Range'Ustvari objekt obsegaNastavi MyRange = Obseg (“A1: A10”)'Izbrišite prejšnje pogojne oblikeMyRange.FormatConditions.Delete'Dodaj prvo praviloMyRange.FormatConditions.Add Type: = xlCellValue, Operator: = xlBetween, _Formula1: = "= 100", Formula2: = "= 150"MyRange.FormatConditions (1) .Interior.Color = RGB (255, 0, 0)'Spremeni praviloMyRange.FormatConditions (1). Spremeni xlCellValue, xlLess, "10""Spremeni barvo pravilaMyRange.FormatConditions (1) .Interior.Color = vbGreenEnd Sub |
Ta koda ustvari objekt obsega (A1: A10) in doda pravilo za številke med 100 in 150. Če je pogoj res, se barva celice spremeni v rdečo.
Koda nato spremeni pravilo v številke, manjše od 10. Če je pogoj resničen, se barva celice spremeni v zeleno.
Uporaba graduirane barvne sheme
Pogojno oblikovanje v Excelu ima možnost uporabe graduiranih barv za vrsto števil, ki se izvajajo v naraščajočem ali padajočem vrstnem redu.
To je zelo koristno, če imate podatke, kot so podatki o prodaji glede na geografsko območje, temperaturo mesta ali razdalje med mesti. Z uporabo VBA imate dodatno prednost, da lahko izberete lastno graduirano barvno shemo in ne standardne, ki jih ponuja sprednji del programa Excel.
| 1234567891011121314151617181920212223242526272829 | Poddiplomirane barve ()Zatemni MyRange As Range'Ustvari objekt obsegaNastavi MyRange = Obseg (“A1: A10”)'Izbrišite prejšnje pogojne oblikeMyRange.FormatConditions.Delete'Določite vrsto lestviceMyRange.FormatConditions.AddColorScale ColorScaleType: = 3'Izberite barvo za najnižjo vrednost v razponuMyRange.FormatConditions (1) .ColorScaleCriteria (1) .Type = _xlConditionValueLowestValueZ MyRange.FormatConditions (1) .ColorScaleCriteria (1). FormatColor.Barva = 7039480Končaj s'Izberite barvo za srednje vrednosti v razponuMyRange.FormatConditions (1) .ColorScaleCriteria (2) .Type = _xlConditionValuePercentileMyRange.FormatConditions (1) .ColorScaleCriteria (2) .Vrednost = 50'Izberite barvo za sredino območjaZ MyRange.FormatConditions (1) .ColorScaleCriteria (2). FormatColor.Barva = 8711167Končaj s'Izberite barvo za najvišjo vrednost v razponuMyRange.FormatConditions (1) .ColorScaleCriteria (3) .Type = _xlConditionValueHighestValueZ MyRange.FormatConditions (1) .ColorScaleCriteria (3). FormatColor.Barva = 8109667Končaj sEnd Sub |
Ko se zažene ta koda, bo barve celic razvrstila glede na naraščajoče vrednosti v območju A1: A10.
To je zelo impresiven način prikaza podatkov in bo zagotovo pritegnil pozornost uporabnikov.
Pogojno oblikovanje za vrednosti napak
Ko imate ogromno podatkov, lahko zlahka zgrešite vrednost napake na različnih delovnih listih. Če je to uporabniku predstavljeno, ne da bi ga rešili, bi to lahko povzročilo velike težave in uporabnik izgubil zaupanje v številke. Ta za oceno celice uporablja vrsto pravila xlExpression in Excelovo funkcijo IsError.
Kodo lahko ustvarite tako, da imajo vse celice z napakami rdečo barvo celice:
| 1234567891011 | Sub ErrorConditionalFormattingExample ()Zatemni MyRange As Range'Ustvari objekt obsegaNastavi MyRange = Obseg (“A1: A10”)'Izbrišite prejšnje pogojne oblikeMyRange.FormatConditions.Delete'Dodaj pravilo napakeMyRange.FormatConditions.Add Type: = xlExpression, Formula1: = "= IsError (A1) = true"'Barvo notranjosti nastavite na rdečoMyRange.FormatConditions (1) .Interior.Color = RGB (255, 0, 0)End Sub |
Pogojno oblikovanje datumov v preteklosti
Morda imate uvožene podatke, kjer želite označiti pretekle datume. Primer tega je lahko poročilo dolžnikov, v katerem želite, da izstopajo vsi stari računi, starejši od 30 dni.
Ta koda uporablja vrsto pravila xlExpression in funkcijo Excel za oceno datumov.
| 1234567891011 | Sub DateInPastConditionalFormattingExample ()Zatemni MyRange As Range'Ustvari objekt obsega na podlagi stolpca datumovNastavi MyRange = Obseg (“A1: A10”)'Izbrišite prejšnje pogojne oblikeMyRange.FormatConditions.Delete'Dodajte pravilo napak za pretekle datumeMyRange.FormatConditions.Add Type: = xlExpression, Formula1: = "= Zdaj ()-A1> 30"'Barvo notranjosti nastavite na rdečoMyRange.FormatConditions (1) .Interior.Color = RGB (255, 0, 0)End Sub |
Ta koda bo zajemala različne datume v razponu A1: A10 in bo barvo celice nastavila na rdečo za kateri koli datum, ki je bil v preteklosti več kot 30 dni.
V formuli, ki se uporablja v pogoju, Now () poda trenutni datum in čas. To se bo znova izračunalo vsakič, ko se delovni list ponovno izračuna, zato se bo oblikovanje spreminjalo od enega dne do naslednjega.
Uporaba podatkovnih vrstic v pogojnem oblikovanju VBA
VBA lahko uporabite za dodajanje podatkovnih vrstic v vrsto številk. To so skoraj kot mini grafikoni in omogočajo takojšen vpogled v to, kako velike so številke med seboj. S sprejetjem privzetih vrednosti za podatkovne vrstice je kodo zelo enostavno zapisati.
| 123456 | Sub DataBarFormattingExample ()Zatemni MyRange As RangeNastavi MyRange = Obseg (“A1: A10”)MyRange.FormatConditions.DeleteMyRange.FormatConditions.AddDatabarEnd Sub |
Na delovnem listu bodo vaši podatki videti tako:
Uporaba ikon v pogojnem oblikovanju VBA
Pogojno oblikovanje lahko uporabite, če želite ikone postaviti skupaj s številkami na delovni list. Ikone so lahko puščice ali krogi ali različne oblike. V tem primeru koda doda številke puščic številkam glede na njihove odstotne vrednosti:
| 12345678910111213141516171819202122232425 | Sub IconSetsExample ()Zatemni MyRange As Range'Ustvari objekt obsegaNastavi MyRange = Obseg (“A1: A10”)'Izbrišite prejšnje pogojne oblikeMyRange.FormatConditions.Delete'Dodaj nabor ikon predmetu FormatConditionsMyRange.FormatConditions.AddIconSetCondition'Nastavite ikono na puščice - pogoj 1Z MyRange.FormatConditions (1).IconSet = ActiveWorkbook.IconSets (xl3Arrows)Končaj s'nastavite merila ikone za zahtevano odstotno vrednost - pogoj 2Z MyRange.FormatConditions (1). IconCriteria (2).Type = xlConditionValuePercent.Vrednost = 33.Operator = xlGreaterEqualKončaj s'nastavite merila ikone za zahtevano odstotno vrednost - pogoj 3Z MyRange.FormatConditions (1). IconCriteria (3).Type = xlConditionValuePercent.Vrednost = 67.Operator = xlGreaterEqualKončaj sEnd Sub |
Tako boste imeli takojšen pogled, ali je število visoko ali nizko. Po zagonu te kode bo vaš delovni list videti tako:
Uporaba pogojnega oblikovanja za označevanje prvih pet
S kodo VBA lahko označite prvih 5 številk v obsegu podatkov. Uporabite parameter, imenovan "AddTop10", vendar lahko številko ranga v kodi prilagodite na 5. Uporabnik bo morda želel videti najvišje številke v obsegu, ne da bi mu bilo treba najprej razvrstiti podatke.
| 1234567891011121314151617181920212223 | Sub Top5Example ()Zatemni MyRange As Range'Ustvari objekt obsegaNastavi MyRange = Obseg (“A1: A10”)'Izbrišite prejšnje pogojne oblikeMyRange.FormatConditions.Delete"Dodajte pogoj Top10MyRange.FormatConditions.AddTop10Z MyRange.FormatConditions (1)'Nastavi parameter od zgoraj navzdol.TopBottom = xlTop10Top'Samo top 5.Rank = 5Končaj sZ MyRange.FormatConditions (1). Font'Nastavite barvo pisave.Barva = -16383844Končaj sZ MyRange.FormatConditions (1). Notranjost'Nastavite barvo ozadja celice.Barva = 13551615Končaj sEnd Sub |
Podatki na vašem delovnem listu bi po zagonu kode izgledali tako:
Upoštevajte, da se vrednost 145 pojavi dvakrat, zato je označenih šest celic.
Pomen parametrov StopIfTrue in SetFirstPriority
StopIfTrue je pomemben, če ima niz celic več pravil pogojnega oblikovanja. Ena sama celica v razponu lahko izpolnjuje prvo pravilo, lahko pa tudi naslednja. Kot razvijalec boste morda želeli, da prikaže oblikovanje samo za prvo pravilo, do katerega pride. Druga merila pravil se lahko prekrivajo in lahko povzročijo nenamerne spremembe, če je dovoljeno nadaljevanje po seznamu pravil.
Privzeto za ta parameter je True, vendar ga lahko spremenite, če želite, da se upoštevajo vsa druga pravila za to celico:
| 1 | MyRange. FormatConditions (1). StopIfTrue = False |
Parameter SetFirstPriority določa, ali bo pravilo pogoja najprej ovrednoteno, če je za to celico več pravil.
| 1 | MyRange. FormatConditions (1). SetFirstPriority |
S tem se položaj tega pravila premakne na položaj 1 v zbirki pogojev oblike, vsa druga pravila pa bodo premaknjena navzdol s spremenjenimi številkami indeksa. Pazite, če spreminjate pravila v kodi z uporabo indeksnih številk. Prepričati se morate, da spreminjate ali brišete pravo pravilo.
Prednost pravila lahko spremenite:
| 1 | MyRange. FormatConditions (1). Prednost = 3 |
To bo spremenilo relativne položaje vseh drugih pravil na seznamu pogojne oblike.
Uporaba pogojnega oblikovanja s sklicevanjem na druge vrednosti celic
To je nekaj, česar pogojno oblikovanje v Excelu ne more narediti. Za to pa lahko ustvarite svojo kodo VBA.
Recimo, da imate stolpec podatkov, v sosednji celici ob vsaki številki pa je nekaj besedila, ki označuje, kakšno oblikovanje mora potekati pri vsaki številki.
Naslednja koda bo pognala vaš seznam številk, v sosednji celici poiskala oblikovanje besedila in nato po potrebi oblikovala številko:
| 123456789101112131415161718192021 | Sub ReferToAnotherCellForConditionalFormatting ()'Ustvarite spremenljivke za shranjevanje števila vrstic za tabelarne podatkeDim RRow As Long, N As Long'Zajemite število vrstic v obsegu tabelarnih podatkovRRow = ActiveSheet.UsedRange.Rows.Count'Ponovite vse vrstice v obsegu tabelarnih podatkovZa N = 1 Za RRow"Uporabite stavek Select Case za oceno oblikovanja na podlagi stolpca 2Izberite Case ActiveSheet.Cells (N, 2) .Vrednost'Barvo notranjosti spremenite v modroEtui "Modra"ActiveSheet.Cells (N, 1) .Interior.Color = vbBlue'Obrnite notranjo barvo v rdečoEtui "rdeča"ActiveSheet.Cells (N, 1) .Interiors.Color = vbRed'Barvo notranjosti spremenite v zelenoEtui "zelena"ActiveSheet.Cells (N, 1) .Interiors.Color = vbGreenKonec IzberiteNaslednji N.End Sub |
Ko bo ta koda zagnana, bo vaš delovni list zdaj videti tako:
Celice, na katere se nanaša oblikovanje, so lahko kjer koli na delovnem listu ali celo na drugem delovnem listu v delovnem zvezku. Za oblikovanje lahko uporabite katero koli obliko besedila, pri uporabi te kode pa ste omejeni le z domišljijo.
Operaterji, ki jih je mogoče uporabiti v izjavah o pogojnem oblikovanju
Kot ste videli v prejšnjih primerih, se operaterji uporabljajo za določanje, kako bodo vrednosti pogojev ovrednotene npr. xlVmes.
Te operaterje je mogoče uporabiti več, odvisno od tega, kako želite določiti merila pravila.
| Ime | Vrednost | Opis |
| xlVmes | 1 | Med. Uporablja se lahko le, če sta podani dve formuli. |
| xl Enako | 3 | Enako. |
| xlVečji | 5 | Večji kot. |
| xlGreaterEqual | 7 | Večji ali enak. |
| xlManje | 6 | Manj kot. |
| xlLessEqual | 8 | Manj ali enako. |
| xlNotBetween | 2 | Ne vmes. Uporablja se lahko le, če sta podani dve formuli. |
| xlNotEqual | 4 | Ni enako. |
