Uvjetno oblikovanje programa Excel
Uvjetno oblikovanje programa Excel omogućuje vam definiranje pravila koja određuju oblikovanje ćelija.
Na primjer, možete stvoriti pravilo koje ističe ćelije koje zadovoljavaju određene kriterije. Primjeri uključuju:
- Brojevi koji spadaju u određeni raspon (npr. Manje od 0).
- 10 najboljih stavki na popisu.
- Izrada "toplinske karte".
- Pravila "temeljena na formulama" za gotovo svako uvjetno oblikovanje.
U Excelu se uvjetno oblikovanje može pronaći na vrpci pod Početna> Stilovi (ALT> H> L).
Da biste stvorili vlastito pravilo, kliknite na "Novo pravilo" i pojavit će se novi prozor:
Uvjetno oblikovanje u VBA
Svim ovim značajkama uvjetnog oblikovanja može se pristupiti pomoću VBA.
Imajte na umu da će se, kada postavite uvjetno oblikovanje iz VBA koda, vaši novi parametri pojaviti u prozoru uvjetnog oblikovanja u prednjem dijelu programa Excel i bit će vidljivi korisniku. Korisnik će ih moći uređivati ili brisati ako niste zaključali radni list.
Pravila uvjetnog oblikovanja spremaju se i kada se spremi radni list
Uvjetna pravila oblikovanja primjenjuju se posebno na određeni radni list i na određeni raspon ćelija. Ako su potrebne drugdje u radnoj knjizi, moraju se postaviti i na tom radnom listu.
Praktične uporabe uvjetnog oblikovanja u VBA
Možda imate veliki dio sirovih podataka uvezenih na vaš radni list iz CSV datoteke (vrijednosti odvojene zarezima) ili iz tablice baze podataka ili upita. To se može pretočiti u nadzornu ploču ili izvješće s promjenjivim brojevima uvezenim iz jednog razdoblja u drugo.
Tamo gdje se broj mijenja i nalazi se izvan prihvatljivog raspona, možda ćete to htjeti istaknuti npr. boja pozadine ćelije u crvenoj boji, a to možete učiniti postavljanjem uvjetnog oblikovanja. Na taj će način korisnik trenutno biti privučen ovom broju, a zatim može istražiti zašto se to događa.
Pomoću VBA možete uključiti ili isključiti uvjetno oblikovanje. Pomoću VBA možete izbrisati pravila za niz ćelija ili ih ponovo uključiti. Može doći do situacije u kojoj postoji savršeno dobar razlog za neobičan broj, ali kada korisnik predstavi nadzornu ploču ili se javi višoj razini upravljanja, želi moći ukloniti ‘zvona za uzbunu’.
Također, na neobrađenim uvezenim podacima možda ćete htjeti istaknuti gdje su brojevi smiješno veliki ili smiješno mali. Uvezeni raspon podataka obično je različite veličine za svako razdoblje, pa pomoću VBA možete procijeniti veličinu novog raspona podataka i umetnuti uvjetno oblikovanje samo za taj raspon.
Također možete imati situaciju da postoji poredan popis imena s numeričkim vrijednostima naspram svakog, npr. plaća zaposlenika, ispitne ocjene. Uz uvjetno oblikovanje, možete koristiti stupnjevane boje za prelazak od najvišeg do najnižeg, što izgleda vrlo impresivno za potrebe prezentacije.
Međutim, popis imena neće uvijek biti statičke veličine, a možete koristiti VBA kôd za osvježavanje ljestvice stupnjevanih boja prema promjenama veličine raspona.
Jednostavan primjer stvaranja uvjetnog formata na rasponu
Ovaj primjer postavlja uvjetno oblikovanje za niz ćelija (A1: A10) na radnom listu. Ako je broj u rasponu između 100 i 150, tada će boja pozadine ćelije biti crvena, inače neće imati boju.
| 1234567891011121314 | Poduslovno Primjer formatiranja ()‘Definiraj rasponDim MyRange As RangePostavi MyRange = Raspon ("A1: A10")‘Izbrišite postojeće uvjetno oblikovanje iz rasponaMyRange.FormatConditions.Delete‘Primijeni uvjetno oblikovanjeMyRange.FormatConditions.Add Type: = xlCellValue, Operator: = xlMed između, _Formula1: = "= 100", Formula2: = "= 150"MyRange.FormatConditions (1) .Interior.Color = RGB (255, 0, 0)Kraj podm |
Uočite da prvo definiramo raspon MyRange primijeniti uvjetno oblikovanje.
Zatim brišemo sve postojeće uvjetno oblikovanje raspona. Ovo je dobra ideja spriječiti dodavanje istog pravila pri svakom pokretanju koda (naravno da to neće biti prikladno u svim okolnostima).
Boje su date numeričkim vrijednostima. Za to je dobra upotreba RGB (crvena, zelena, plava) oznaka. Za to možete koristiti standardne konstante boja, npr. vbRed, vbBlue, ali ograničeni ste na osam boja.
Dostupno je više od 16,7 milijuna boja, a pomoću RGB -a možete im pristupiti svima. To je daleko lakše nego pokušavati zapamtiti koji broj ide uz koju boju. Svaki od tri RGB broja boja je od 0 do 255.
Imajte na umu da je parametar "xlBetween" uključiv pa će vrijednosti ćelija od 100 ili 150 zadovoljiti uvjet.
Više uvjetno oblikovanje
Možda ćete htjeti postaviti nekoliko uvjetnih pravila unutar raspona podataka tako da sve vrijednosti u rasponu budu pokrivene različitim uvjetima:
| 12345678910111213141516171819 | Sub MultipleConditionalFormattingExample ()Dim MyRange As Range'Izradi objekt rasponaPostavi MyRange = Raspon ("A1: A10")'Izbrišite prethodne uvjetne formateMyRange.FormatConditions.Delete'Dodajte prvo praviloMyRange.FormatConditions.Add Type: = xlCellValue, Operator: = xlMed između, _Formula1: = "= 100", Formula2: = "= 150"MyRange.FormatConditions (1) .Interior.Color = RGB (255, 0, 0)'Dodajte drugo praviloMyRange.FormatConditions.Add Type: = xlCellValue, Operator: = xlLess, _Formula1: = "= 100"MyRange.FormatConditions (2) .Interior.Color = vbBlue'Dodajte treće praviloMyRange.FormatConditions.Add Type: = xlCellValue, Operator: = xlGreater, _Formula1: = "= 150"MyRange.FormatConditions (3) .Interiors.Color = vbYellowKraj podm |
Ovaj primjer postavlja prvo pravilo kao i prije, s bojom ćelije crvenom bojom ako je vrijednost ćelije između 100 i 150.
Zatim se dodaju još dva pravila. Ako je vrijednost ćelije manja od 100, tada je boja ćelije plava, a ako je veća od 150, tada je boja ćelije žuta.
U ovom primjeru morate osigurati da su obuhvaćene sve mogućnosti brojeva i da se pravila ne preklapaju.
Ako su prazne ćelije u ovom rasponu, one će se prikazati kao plave, jer ih Excel i dalje smatra vrijednošću manjom od 100.
Zaobilaženje je dodavanje drugog uvjeta kao izraza. To je potrebno dodati kao prvo pravilo uvjeta u kodu. Vrlo je važno ako postoji više pravila, kako bi se ispravno odredio redoslijed izvođenja, inače rezultati mogu biti nepredvidljivi.
| 1234567891011121314151617181920212223 | Sub MultipleConditionalFormattingExample ()Dim MyRange As Range'Izradi objekt rasponaPostavi MyRange = Raspon ("A1: A10")'Izbrišite prethodne uvjetne formateMyRange.FormatConditions.Delete'Dodajte prvo praviloMyRange.FormatConditions.Add Type: = xlExpression, Formula1: = _"= LEN (TRIM (A1)) = 0"MyRange.FormatConditions (1) .Interior.Pattern = xlNone'Dodajte drugo praviloMyRange.FormatConditions.Add Type: = xlCellValue, Operator: = xlMed između, _Formula1: = "= 100", Formula2: = "= 150"MyRange.FormatConditions (2) .Interior.Color = RGB (255, 0, 0)'Dodajte treće praviloMyRange.FormatConditions.Add Type: = xlCellValue, Operator: = xlLess, _Formula1: = "= 100"MyRange.FormatConditions (3) .Interior.Color = vbBlue'Dodajte četvrto praviloMyRange.FormatConditions.Add Type: = xlCellValue, Operator: = xlGreater, _Formula1: = "= 150"MyRange.FormatConditions (4) .Interior.Color = RGB (0, 255, 0)Kraj podm |
Ovo koristi vrstu xlExpression, a zatim koristi standardnu formulu Excel za određivanje je li ćelija prazna umjesto numeričke vrijednosti.
Objekt FormatConditions dio je objekta Range. Djeluje na isti način kao zbirka s indeksom koji počinje od 1. Možete proći kroz ovaj objekt pomoću For … Next ili For … Svaka petlja.
Brisanje pravila
Ponekad ćete možda morati izbrisati pojedinačno pravilo u skupu više pravila ako ne odgovara zahtjevima podataka.
| 12345678910111213 | Sub DeleteConditionalFormattingExample ()Dim MyRange As Range'Izradi objekt rasponaPostavi MyRange = Raspon ("A1: A10")'Izbrišite prethodne uvjetne formateMyRange.FormatConditions.Delete'Dodajte prvo praviloMyRange.FormatConditions.Add Type: = xlCellValue, Operator: = xlMed između, _Formula1: = "= 100", Formula2: = "= 150"MyRange.FormatConditions (1) .Interior.Color = RGB (255, 0, 0)'Obriši praviloMyRange.FormatConditions (1) .BrisatiKraj podm |
Ovaj kôd stvara novo pravilo za raspon A1: A10, a zatim ga briše. Za brisanje morate koristiti točan broj indeksa, pa provjerite "Upravljanje pravilima" na prednjem dijelu programa Excel (ovo će pokazati pravila prema redoslijedu izvođenja) kako biste bili sigurni da ćete dobiti točan broj indeksa. Imajte na umu da nema mogućnosti poništavanja u Excelu ako izbrišete pravilo uvjetnog oblikovanja u VBA-u, za razliku od toga ako to učinite putem prednjeg dijela programa Excel.
Promjena pravila
Budući da su pravila zbirka objekata temeljenih na navedenom rasponu, možete jednostavno izmijeniti određena pravila pomoću VBA. Stvarna svojstva nakon dodavanja pravila samo su za čitanje, ali ih možete promijeniti pomoću metode Modify. Svojstva poput boja se čitaju / pišu.
| 123456789101112131415 | Sub ChangeConditionalFormattingExample ()Dim MyRange As Range'Izradi objekt rasponaPostavi MyRange = Raspon ("A1: A10")'Izbrišite prethodne uvjetne formateMyRange.FormatConditions.Delete'Dodajte prvo praviloMyRange.FormatConditions.Add Type: = xlCellValue, Operator: = xlMed između, _Formula1: = "= 100", Formula2: = "= 150"MyRange.FormatConditions (1) .Interior.Color = RGB (255, 0, 0)'Promijeni praviloMyRange.FormatConditions (1). Izmijeni xlCellValue, xlLess, "10"‘Promijeni boju pravilaMyRange.FormatConditions (1) .Interior.Color = vbGreenKraj podm |
Ovaj kôd stvara objekt raspona (A1: A10) i dodaje pravilo za brojeve između 100 i 150. Ako je uvjet istinit, boja ćelije se mijenja u crvenu.
Kôd zatim mijenja pravilo u brojeve manje od 10. Ako je uvjet istinit, boja ćelije sada postaje zelena.
Korištenje stupnjevane sheme boja
Uvjetno oblikovanje programa Excel ima mogućnost korištenja stupnjevanih boja na nizu brojeva koji se izvode uzlaznim ili silaznim redoslijedom.
Ovo je vrlo korisno ako imate podatke poput podataka o prodaji prema zemljopisnom području, temperaturi grada ili udaljenosti između gradova. Korištenjem VBA-a imate dodatnu prednost što možete odabrati vlastitu stupnjevitu shemu boja, a ne standardnu koja se nudi na prednjem dijelu programa Excel.
| 1234567891011121314151617181920212223242526272829 | Poddiplomirane boje ()Dim MyRange As Range'Izradi objekt rasponaPostavi MyRange = Raspon ("A1: A10")'Izbrišite prethodne uvjetne formateMyRange.FormatConditions.Delete'Odredite vrstu ljestviceMyRange.FormatConditions.AddColorScale ColorScaleType: = 3'Odaberite boju za najnižu vrijednost u rasponuMyRange.FormatConditions (1) .ColorScaleCriteria (1) .Type = _xlConditionValueLowestValueS MyRange.FormatConditions (1) .ColorScaleCriteria (1) .FormatColor.Boja = 7039480Završi s'Odaberite boju za srednje vrijednosti u rasponuMyRange.FormatConditions (1) .ColorScaleCriteria (2) .Type = _xlConditionValuePercentileMyRange.FormatConditions (1) .ColorScaleCriteria (2) .Vrijednost = 50'Odaberite boju za sredinu rasponaS MyRange.FormatConditions (1) .ColorScaleCriteria (2) .FormatColor.Boja = 8711167Završi s'Odaberite boju za najveću vrijednost u rasponuMyRange.FormatConditions (1) .ColorScaleCriteria (3) .Type = _xlConditionValueHighestValueS MyRange.FormatConditions (1) .ColorScaleCriteria (3) .FormatColor.Boja = 8109667Završi sKraj podm |
Kada se ovaj kôd pokrene, on će stupnjevati boje ćelija prema rastućim vrijednostima u rasponu A1: A10.
Ovo je vrlo impresivan način prikaza podataka i zasigurno će privući pozornost korisnika.
Uvjetno oblikovanje za vrijednosti pogrešaka
Kad imate ogromnu količinu podataka, lako možete propustiti vrijednost pogreške u različitim radnim listovima. Ako se to predoči korisniku bez rješavanja, moglo bi doći do velikih problema i korisnika izgubiti povjerenje u brojeve. Ovo koristi vrstu pravila xlExpression i Excel funkciju IsError za procjenu ćelije.
Možete stvoriti kôd tako da sve ćelije s pogreškama imaju crvenu boju ćelije:
| 1234567891011 | Pod ErrorConditionalFormattingExample ()Dim MyRange As Range'Izradi objekt rasponaPostavi MyRange = Raspon ("A1: A10")'Izbrišite prethodne uvjetne formateMyRange.FormatConditions.Delete'Dodaj pravilo pogreškeMyRange.FormatConditions.Add Type: = xlExpression, Formula1: = "= IsError (A1) = true"'Namjestite boju interijera na crvenuMyRange.FormatConditions (1) .Interior.Color = RGB (255, 0, 0)Kraj podm |
Uvjetno oblikovanje datuma u prošlosti
Možda ste uvezli podatke na kojima želite istaknuti datume koji su u prošlosti. Primjer toga može biti izvješće dužnika u kojem želite da se ističu svi stari računi stariji od 30 dana.
Ovaj kôd koristi vrstu pravila xlExpression i Excel funkciju za procjenu datuma.
| 1234567891011 | Sub DateInPastConditionalFormattingExample ()Dim MyRange As Range'Stvorite objekt raspona na temelju stupca datumaPostavi MyRange = Raspon ("A1: A10")'Izbrišite prethodne uvjetne formateMyRange.FormatConditions.Delete'Dodajte pravilo pogreške za datume u prošlostiMyRange.FormatConditions.Add Type: = xlExpression, Formula1: = "= Now ()-A1> 30"'Postavite boju interijera na crvenuMyRange.FormatConditions (1) .Interior.Color = RGB (255, 0, 0)Kraj podm |
Ovaj će kôd uzeti niz datuma u rasponu A1: A10, a boju ćelije će postaviti na crvenu za bilo koji datum koji je bio više od 30 dana u prošlosti.
U formuli koja se koristi u stanju, Now () daje trenutni datum i vrijeme. Ovo će se stalno preračunavati svaki put kada se radni list ponovno izračuna, pa će se oblikovanje mijenjati iz dana u dan.
Korištenje podatkovnih traka u VBA uvjetnom oblikovanju
Možete koristiti VBA za dodavanje podatkovnih traka u niz brojeva. Oni su gotovo poput mini grafikona i daju trenutni uvid u to koliko su brojke velike jedna u odnosu na drugu. Prihvaćanjem zadanih vrijednosti za podatkovne trake, kôd je vrlo jednostavan za pisanje.
| 123456 | Sub DataBarFormattingExample ()Dim MyRange As RangePostavi MyRange = Raspon ("A1: A10")MyRange.FormatConditions.DeleteMyRange.FormatConditions.AddDatabarKraj podm |
Vaši će podaci na radnom listu izgledati ovako:
Korištenje ikona u VBA uvjetnom oblikovanju
Uvjetno oblikovanje možete upotrijebiti za stavljanje ikona uz svoje brojeve na radni list. Ikone mogu biti strelice ili krugovi ili različiti drugi oblici. U ovom primjeru kôd dodaje ikone strelica brojevima na temelju njihovih postotnih vrijednosti:
| 12345678910111213141516171819202122232425 | Sub IconSetsExample ()Dim MyRange As Range'Izradi objekt rasponaPostavi MyRange = Raspon ("A1: A10")'Izbrišite prethodne uvjetne formateMyRange.FormatConditions.Delete'Dodaj skup ikona objektu FormatConditionsMyRange.FormatConditions.AddIconSetCondition'Postavite ikonu na strelice - uvjet 1S MyRange.FormatConditions (1).IconSet = ActiveWorkbook.IconSets (xl3Arrows)Završi s'postavite kriterije ikone za potrebnu postotnu vrijednost - uvjet 2S MyRange.FormatConditions (1) .IconCriteria (2).Tip = xlConditionValuePercent.Vrijednost = 33.Operator = xlVelikiJednakZavrši s'postavite kriterije ikone za potrebnu postotnu vrijednost - uvjet 3S MyRange.FormatConditions (1) .IconCriteria (3).Tip = xlConditionValuePercent.Vrijednost = 67.Operator = xlVelikiJednakZavrši sKraj podm |
To će omogućiti trenutni prikaz da li je broj visok ili nizak. Nakon pokretanja ovog koda, vaš će radni list izgledati ovako:
Upotreba uvjetnog oblikovanja za isticanje prvih pet
Možete koristiti VBA kôd za označavanje prvih 5 brojeva unutar raspona podataka. Upotrebljavate parametar pod nazivom "AddTop10", ali broj ranga u kodu možete prilagoditi na 5. Korisnik može vidjeti najviše brojeve u rasponu bez prethodnog sortiranja podataka.
| 1234567891011121314151617181920212223 | Pod top5Primjer ()Dim MyRange As Range'Izradi objekt rasponaPostavi MyRange = Raspon ("A1: A10")'Izbrišite prethodne uvjetne formateMyRange.FormatConditions.Delete'Dodajte uvjet Top10MyRange.FormatConditions.AddTop10S MyRange.FormatConditions (1)'Parametar od vrha do dna.TopBottom = xlTop10Top'Samo prvih 5.Rang = 5Završi sS MyRange.FormatConditions (1) .Font'Postavite boju fonta.Boja = -16383844Završi sS MyRange.FormatConditions (1) .Interijer'Postavite boju pozadine ćelije.Boja = 13551615Završi sKraj podm |
Podaci na vašem radnom listu izgledali bi ovako nakon pokretanja koda:
Imajte na umu da se vrijednost 145 pojavljuje dva puta pa je šest ćelija istaknuto.
Značaj parametara StopIfTrue i SetFirstPriority
StopIfTrue je važan ako raspon ćelija ima više pravila uvjetnog oblikovanja. Jedna ćelija unutar raspona može zadovoljiti prvo pravilo, ali može zadovoljiti i sljedeća pravila. Kao programer, možda želite da prikazuje oblikovanje samo za prvo pravilo do kojeg dolazi. Ostali kriteriji pravila mogu se preklapati i mogu napraviti nenamjerne promjene ako im se dopusti da nastave niz popis pravila.
Zadana vrijednost ovog parametra je True, ali možete ga promijeniti ako želite da se razmotre sva druga pravila za tu ćeliju:
| 1 | MyRange. FormatConditions (1) .StopIfTrue = Netačno |
Parametar SetFirstPriority određuje hoće li se pravilo uvjeta prvo procijeniti ako postoji više pravila za tu ćeliju.
| 1 | MyRange. FormatConditions (1) .SetFirstPriority |
Ovo pomiče položaj tog pravila na poziciju 1 unutar zbirke uvjeta formata, a sva druga pravila bit će pomaknuta prema dolje s promijenjenim brojevima indeksa. Pazite ako mijenjate pravila u kodu pomoću indeksnih brojeva. Morate biti sigurni da mijenjate ili brišete pravo pravilo.
Prioritet pravila možete promijeniti:
| 1 | MyRange. FormatUvjeti (1). Prioritet = 3 |
To će promijeniti relativne položaje bilo kojih drugih pravila na popisu uvjetnog formata.
Korištenje uvjetnog oblikovanja referencirajući druge vrijednosti ćelije
To je jedna stvar koju Excel uvjetno oblikovanje ne može učiniti. Međutim, za to možete izgraditi vlastiti VBA kôd.
Pretpostavimo da imate stupac podataka, a u susjednoj ćeliji uz svaki broj nalazi se neki tekst koji označava koje bi se oblikovanje trebalo dogoditi na svakom broju.
Sljedeći će kôd pokrenuti vaš popis brojeva, potražiti u susjednoj ćeliji formatiranje teksta, a zatim formatirati broj prema potrebi:
| 123456789101112131415161718192021 | Sub ReferToAnotherCellForConditionalFormatting ()'Izradite varijable za držanje broja redaka za tablične podatkeDim RRow As Long, N As Long'Snimite broj redaka unutar raspona tabličnih podatakaRRow = ActiveSheet.UsedRange.Rows.Count'Ponovite sve retke u rasponu tabličnih podatakaZa N = 1 do RRow'Pomoću izraza Select Case procijenite oblikovanje na temelju stupca 2Odaberite Case ActiveSheet.Cells (N, 2) .Vrijednost'Pretvorite unutrašnjost u plavuKućište "Plavo"ActiveSheet.Cells (N, 1) .Interiors.Color = vbBlue'Uključite boju interijera u crvenuKućište "Crveno"ActiveSheet.Cells (N, 1) .Interiors.Color = vbRed'Uključite boju interijera u zelenuKućište "Zeleno"ActiveSheet.Cells (N, 1) .Interiors.Color = vbGreenKraj OdaberiteSljedeći N.Kraj podm |
Nakon pokretanja ovog koda vaš radni list sada će izgledati ovako:
Ćelije na koje se odnosi oblikovanje mogu se nalaziti bilo gdje na radnom listu ili čak na drugom radnom listu unutar radne knjige. Možete upotrijebiti bilo koji oblik teksta da biste postavili uvjet za oblikovanje, a mašta vas ograničava samo u načinima na koje biste mogli primijeniti ovaj kôd.
Operatori koji se mogu koristiti u Izjavama o uvjetnom oblikovanju
Kao što ste vidjeli u prethodnim primjerima, operatori se koriste za određivanje načina vrednovanja uvjeta, npr. xlIzmeđu.
Postoji niz ovih operatora koji se mogu koristiti, ovisno o tome kako želite odrediti kriterije pravila.
| Ime | Vrijednost | Opis |
| xlIzmeđu | 1 | Između. Može se koristiti samo ako su navedene dvije formule. |
| xlJednako | 3 | Jednak. |
| xlVeći | 5 | Veći od. |
| xlVelikoJednako | 7 | Veće ili jednako. |
| xlManje | 6 | Manje od. |
| xlLessEqual | 8 | Manje ili jednako. |
| xlNotBetween | 2 | Ne između. Može se koristiti samo ako su navedene dvije formule. |
| xlNejednako | 4 | Nejednak. |
