Екцел условно обликовање
Екцел условно обликовање вам омогућава да дефинишете правила која одређују обликовање ћелија.
На пример, можете да креирате правило које истиче ћелије које испуњавају одређене критеријуме. Примери укључују:
- Бројеви који спадају у одређени опсег (нпр. Мање од 0).
- 10 најбољих ставки на листи.
- Креирање „топлотне карте“.
- Правила „заснована на формулама“ за практично свако условно обликовање.
У Екцелу се условно обликовање може наћи на траци под Почетна> Стилови (АЛТ> Х> Л).
Да бисте креирали своје правило, кликните на „Ново правило“ и појавиће се нови прозор:
Условно обликовање у ВБА
Свим овим функцијама условног обликовања може се приступити помоћу ВБА.
Имајте на уму да ће се, када подесите условно обликовање из ВБА кода, ваши нови параметри појавити у Екцел предњем прозору за условно обликовање и биће видљиви кориснику. Корисник ће их моћи уређивати или брисати ако нисте закључали радни лист.
Правила условног обликовања се такође чувају када се сачува радни лист
Правила условног обликовања примењују се посебно на одређени радни лист и на одређени распон ћелија. Ако су потребне негде другде у радној свесци, морају се поставити и на том радном листу.
Практичне употребе условног обликовања у ВБА
Можда имате велики део сирових података увезених у ваш радни лист из ЦСВ датотеке (вредности раздвојене зарезима) или из табеле базе података или упита. Ово може проћи кроз контролну таблу или извештај, са промењеним бројевима из једног периода у други.
Тамо где се број промени и налази се изван прихватљивог опсега, можда ћете желети да ово истакнете, нпр. боја позадине ћелије у црвеној боји, а то можете учинити постављањем условног обликовања. На овај начин, корисник ће одмах бити привучен овом броју, а затим може истражити зашто се то дешава.
Помоћу ВБА можете да укључите или искључите условно обликовање. Можете да користите ВБА за брисање правила за низ ћелија или да их поново укључите. Може доћи до ситуације у којој постоји савршено добар разлог за необичан број, али када корисник представи контролну таблу или се пријави вишем нивоу управљања, жели да може уклонити „звона за узбуну“.
Такође, на необрађеним увезеним подацима можда ћете желети да истакнете где су бројеви смешно велики или смешно мали. Увезени опсег података је обично различите величине за сваки период, па можете користити ВБА за процену величине новог опсега података и уметнути условно обликовање само за тај опсег.
Такође можете имати ситуацију да постоји сортирана листа имена са нумеричким вредностима наспрам сваког, нпр. плата запослених, испитне оцене. Уз условно обликовање, можете користити градуисане боје за прелазак од највишег до најнижег, што изгледа веома импресивно за потребе презентације.
Међутим, листа имена неће увек бити статичке величине и можете користити ВБА код за освежавање скале степенастих боја према променама величине опсега.
Једноставан пример стварања условног формата на опсегу
Овај пример поставља условно обликовање за низ ћелија (А1: А10) на радном листу. Ако је број у распону између 100 и 150, тада ће боја позадине ћелије бити црвена, иначе неће имати боју.
| 1234567891011121314 | Подусловни пример форматирања ()„Дефинишите дометДим МиРанге Ас РангеПодеси МиРанге = Распон („А1: А10“)„Избришите постојеће условно обликовање из опсегаМиРанге.ФорматЦондитионс.Делете„Примени условно обликовањеМиРанге.ФорматЦондитионс.Адд Типе: = клЦеллВалуе, Оператор: = клБетвеен, _Формула1: = "= 100", Формула2: = "= 150"МиРанге.ФорматЦондитионс (1) .Интериор.Цолор = РГБ (255, 0, 0)Енд Суб |
Приметите да прво дефинишемо опсег МиРанге да бисте применили условно обликовање.
Затим бришемо све постојеће условно обликовање опсега. Ово је добра идеја да се спречи додавање истог правила сваки пут када се код покрене (наравно да то неће бити прикладно у свим околностима).
Боје су дате нумеричким вредностима. За ово је добра идеја користити РГБ (црвену, зелену, плаву) нотацију. За ово можете користити стандардне константе боја, нпр. вбРед, вбБлуе, али ограничени сте на осам боја.
Доступно је више од 16,7 милиона боја, а помоћу РГБ -а можете им приступити свима. Ово је далеко лакше него покушати запамтити који број иде уз коју боју. Сваки од три РГБ броја боја је од 0 до 255.
Имајте на уму да је параметар „клБетвеен“ инклузиван па ће вредности ћелија од 100 или 150 задовољити услов.
Више условно обликовање
Можда ћете желети да поставите неколико условних правила у опсегу података тако да све вредности у опсегу буду покривене различитим условима:
| 12345678910111213141516171819 | Суб МултиплеЦондитионалФорматтингЕкампле ()Дим МиРанге Ас Ранге'Направи објекат опсегаПодеси МиРанге = Распон („А1: А10“)'Избришите претходне условне форматеМиРанге.ФорматЦондитионс.Делете„Додајте прво правилоМиРанге.ФорматЦондитионс.Адд Типе: = клЦеллВалуе, Оператор: = клБетвеен, _Формула1: = "= 100", Формула2: = "= 150"МиРанге.ФорматЦондитионс (1) .Интериор.Цолор = РГБ (255, 0, 0)„Додајте друго правилоМиРанге.ФорматЦондитионс.Адд Типе: = клЦеллВалуе, Оператор: = клЛесс, _Формула1: = "= 100"МиРанге.ФорматЦондитионс (2) .Интериор.Цолор = вбБлуе„Додајте треће правилоМиРанге.ФорматЦондитионс.Адд Типе: = клЦеллВалуе, Оператор: = клГреатер, _Формула1: = "= 150"МиРанге.ФорматЦондитионс (3) .Интериор.Цолор = вбИелловЕнд Суб |
Овај пример поставља прво правило као и раније, са бојом ћелије црвеном бојом ако је вредност ћелије између 100 и 150.
Затим се додају још два правила. Ако је вредност ћелије мања од 100, онда је боја ћелије плава, а ако је већа од 150, онда је боја ћелије жута.
У овом примеру морате осигурати да су обухваћене све могућности бројева и да се правила не преклапају.
Ако су празне ћелије у овом опсегу, оне ће се приказати као плаве, јер их Екцел и даље сматра вредностима мањим од 100.
Заобилажење је додавање другог услова као израза. Ово треба додати као прво правило услова у коду. Врло је важно ако постоји више правила, да бисте добили правилан редослед извршења, у супротном резултати могу бити непредвидљиви.
| 1234567891011121314151617181920212223 | Суб МултиплеЦондитионалФорматтингЕкампле ()Дим МиРанге Ас Ранге'Направи објекат опсегаПодеси МиРанге = Распон („А1: А10“)'Избришите претходне условне форматеМиРанге.ФорматЦондитионс.Делете„Додајте прво правилоМиРанге.ФорматЦондитионс.Адд Типе: = клЕкпрессион, Формула1: = _"= ЛЕН (ТРИМ (А1)) = 0"МиРанге.ФорматЦондитионс (1) .Интериор.Паттерн = клНоне„Додајте друго правилоМиРанге.ФорматЦондитионс.Адд Типе: = клЦеллВалуе, Оператор: = клБетвеен, _Формула1: = "= 100", Формула2: = "= 150"МиРанге.ФорматЦондитионс (2) .Интериор.Цолор = РГБ (255, 0, 0)„Додајте треће правилоМиРанге.ФорматЦондитионс.Адд Типе: = клЦеллВалуе, Оператор: = клЛесс, _Формула1: = "= 100"МиРанге.ФорматЦондитионс (3) .Интериор.Цолор = вбБлуе„Додајте четврто правилоМиРанге.ФорматЦондитионс.Адд Типе: = клЦеллВалуе, Оператор: = клГреатер, _Формула1: = "= 150"МиРанге.ФорматЦондитионс (4) .Интериор.Цолор = РГБ (0, 255, 0)Енд Суб |
Ово користи тип клЕкпрессион, а затим користи стандардну Екцел формулу да одреди да ли је ћелија празна уместо нумеричке вредности.
Објекат ФорматЦондитионс је део објекта Ранге. Делује на исти начин као збирка чији индекс почиње са 1. Можете да прођете кроз овај објекат помоћу Фор … Нект или Фор … Свака петља.
Брисање правила
Понекад ћете можда морати да избришете појединачно правило у скупу више правила ако не одговара захтевима за податке.
| 12345678910111213 | Суб ДелетеЦондитионалФорматтингЕкампле ()Дим МиРанге Ас Ранге'Направи објекат опсегаПодеси МиРанге = Распон („А1: А10“)'Избришите претходне условне форматеМиРанге.ФорматЦондитионс.Делете„Додајте прво правилоМиРанге.ФорматЦондитионс.Адд Типе: = клЦеллВалуе, Оператор: = клБетвеен, _Формула1: = "= 100", Формула2: = "= 150"МиРанге.ФорматЦондитионс (1) .Интериор.Цолор = РГБ (255, 0, 0)'Обриши правилоМиРанге.ФорматЦондитионс (1) .ДелетеЕнд Суб |
Овај код ствара ново правило за опсег А1: А10, а затим га брише. За брисање морате да користите исправан индексни број, па проверите „Управљај правилима“ на Екцел-овом предњем делу (ово ће показати правила по редоследу извршавања) да бисте били сигурни да ћете добити исправан индексни број. Имајте на уму да у Екцел-у нема могућности поништавања ако избришете правило условног обликовања у ВБА, за разлику од тога ако то учините преко Екцел-овог предњег дела.
Промена правила
Будући да су правила збирка објеката заснованих на наведеном распону, можете лако изменити одређена правила користећи ВБА. Стварна својства након додавања правила су само за читање, али можете их користити помоћу методе Модифи. Својства као што су боје се читају / пишу.
| 123456789101112131415 | Суб ЦхангеЦондитионалФорматтингЕкампле ()Дим МиРанге Ас Ранге'Направи објекат опсегаПодеси МиРанге = Распон („А1: А10“)'Избришите претходне условне форматеМиРанге.ФорматЦондитионс.Делете„Додајте прво правилоМиРанге.ФорматЦондитионс.Адд Типе: = клЦеллВалуе, Оператор: = клБетвеен, _Формула1: = "= 100", Формула2: = "= 150"МиРанге.ФорматЦондитионс (1) .Интериор.Цолор = РГБ (255, 0, 0)'Промените правилоМиРанге.ФорматЦондитионс (1). Измени клЦеллВалуе, клЛесс, "10"„Промените боју правилаМиРанге.ФорматЦондитионс (1) .Интериор.Цолор = вбГреенЕнд Суб |
Овај код ствара објекат опсега (А1: А10) и додаје правило за бројеве између 100 и 150. Ако је услов тачан, боја ћелије се мења у црвену.
Код затим мења правило у бројеве мање од 10. Ако је услов тачан, боја ћелије се сада мења у зелену.
Коришћење степенасте шеме боја
Екцел условно обликовање има могућност коришћења степенастих боја на низу бројева који се изводе у растућем или опадајућем редоследу.
Ово је веома корисно ако имате податке попут продајних података према географском подручју, температури града или удаљености између градова. Коришћењем ВБА имате додатну предност у могућности да изаберете сопствену градуирану шему боја, уместо стандардних који се нуде на предњем делу програма Екцел.
| 1234567891011121314151617181920212223242526272829 | Суб ГрадуатедЦолорс ()Дим МиРанге Ас Ранге'Направи објекат опсегаПодеси МиРанге = Распон („А1: А10“)'Избришите претходне условне форматеМиРанге.ФорматЦондитионс.Делете'Дефинишите тип скалеМиРанге.ФорматЦондитионс.АддЦолорСцале ЦолорСцалеТипе: = 3'Одаберите боју за најнижу вредност у опсегуМиРанге.ФорматЦондитионс (1) .ЦолорСцалеЦритериа (1) .Типе = _клЦондитионВалуеЛовестВалуеСа МиРанге.ФорматЦондитионс (1) .ЦолорСцалеЦритериа (1) .ФорматЦолор.Боја = 7039480Завршити'Одаберите боју за средње вредности у опсегуМиРанге.ФорматЦондитионс (1) .ЦолорСцалеЦритериа (2) .Типе = _клЦондитионВалуеПерцентилеМиРанге.ФорматЦондитионс (1) .ЦолорСцалеЦритериа (2) .Валуе = 50'Одаберите боју за средину распонаСа МиРанге.ФорматЦондитионс (1) .ЦолорСцалеЦритериа (2) .ФорматЦолор.Боја = 8711167Завршити'Одаберите боју за највећу вредност у опсегуМиРанге.ФорматЦондитионс (1) .ЦолорСцалеЦритериа (3) .Типе = _клЦондитионВалуеХигхестВалуеСа МиРанге.ФорматЦондитионс (1) .ЦолорСцалеЦритериа (3) .ФорматЦолор.Боја = 8109667ЗавршитиЕнд Суб |
Када се овај код покрене, он ће ступњевати боје ћелија према растућим вредностима у опсегу А1: А10.
Ово је врло импресиван начин приказивања података и свакако ће привући пажњу корисника.
Условно обликовање за вредности грешака
Када имате огромну количину података, лако можете пропустити вредност грешке у различитим радним листовима. Ако се ово прикаже кориснику без рјешавања, могло би доћи до великих проблема и корисника изгубити повјерење у бројеве. Ово користи тип правила клЕкпрессион и Екцел функцију ИсЕррор за процену ћелије.
Можете креирати код тако да све ћелије са грешкама имају црвену боју ћелије:
| 1234567891011 | Под ЕррорЦондитионалФорматтингЕкампле ()Дим МиРанге Ас Ранге'Направи објекат опсегаПодеси МиРанге = Распон („А1: А10“)'Избришите претходне условне форматеМиРанге.ФорматЦондитионс.Делете„Додајте правило грешкеМиРанге.ФорматЦондитионс.Адд Типе: = клЕкпрессион, Формула1: = "= ИсЕррор (А1) = труе"'Подесите боју ентеријера на црвенуМиРанге.ФорматЦондитионс (1) .Интериор.Цолор = РГБ (255, 0, 0)Енд Суб |
Условно обликовање датума у прошлости
Можда сте увезли податке тамо где желите да истакнете датуме који су у прошлости. Пример за ово може бити извештај дужника у коме желите да се истичу сви стари рачуни старији од 30 дана.
Овај код користи тип правила клЕкпрессион и Екцел функцију за процену датума.
| 1234567891011 | Суб ДатеИнПастЦондитионалФорматтингЕкампле ()Дим МиРанге Ас Ранге'Креирајте објекат опсега на основу колоне датумаПодеси МиРанге = Распон („А1: А10“)'Избришите претходне условне форматеМиРанге.ФорматЦондитионс.Делете„Додајте правило грешке за датуме у прошлостиМиРанге.ФорматЦондитионс.Адд Типе: = клЕкпрессион, Формула1: = "= Нов ()-А1> 30"'Подесите боју ентеријера на црвенуМиРанге.ФорматЦондитионс (1) .Интериор.Цолор = РГБ (255, 0, 0)Енд Суб |
Овај код ће имати низ датума у распону А1: А10 и поставиће боју ћелије на црвену за било који датум који је био више од 30 дана у прошлости.
У формули која се користи у услову, Нов () даје тренутни датум и време. Ово ће се стално поново израчунавати сваки пут када се радни лист поново израчуна, па ће се обликовање мењати из дана у дан.
Коришћење барова података у условном обликовању ВБА
Можете користити ВБА за додавање трака са подацима у низ бројева. Ово су скоро као мини графикони и дају тренутни увид у то колико су велики бројеви међусобно повезани. Прихватањем подразумеваних вредности за барове података, код се врло лако пише.
| 123456 | Суб ДатаБарФорматтингЕкампле ()Дим МиРанге Ас РангеПодеси МиРанге = Распон („А1: А10“)МиРанге.ФорматЦондитионс.ДелетеМиРанге.ФорматЦондитионс.АддДатабарЕнд Суб |
Ваши подаци ће изгледати овако на радном листу:
Коришћење икона у ВБА условном обликовању
Можете користити условно обликовање да бисте иконе ставили поред својих бројева на радни лист. Иконе могу бити стрелице или кругови или различити други облици. У овом примеру, код додаје иконе стрелица бројевима на основу њихових процентуалних вредности:
| 12345678910111213141516171819202122232425 | Суб ИцонСетсЕкампле ()Дим МиРанге Ас Ранге'Направи објекат опсегаПодеси МиРанге = Распон („А1: А10“)'Избришите претходне условне форматеМиРанге.ФорматЦондитионс.Делете'Додај скуп икона објекту ФорматЦондитионсМиРанге.ФорматЦондитионс.АддИцонСетЦондитион'Поставите икону на стрелице - услов 1Са МиРанге.ФорматЦондитионс (1).ИцонСет = АцтивеВоркбоок.ИцонСетс (кл3Арровс)Завршити'поставите критеријум иконе за потребну процентуалну вредност - услов 2Са МиРанге.ФорматЦондитионс (1) .ИцонЦритериа (2).Типе = клЦондитионВалуеПерцент.Вредност = 33.Оператор = клГреатерЕкуалЗавршити'поставите критеријум иконе за потребну процентуалну вредност - услов 3Са МиРанге.ФорматЦондитионс (1) .ИцонЦритериа (3).Типе = клЦондитионВалуеПерцент.Вредност = 67.Оператор = клГреатерЕкуалЗавршитиЕнд Суб |
Ово ће дати тренутни приказ који показује да ли је број висок или низак. Након покретања овог кода, ваш радни лист ће изгледати овако:
Употреба условног обликовања за истицање првих пет
Помоћу ВБА кода можете означити првих 5 бројева у опсегу података. Користите параметар под називом „АддТоп10“, али можете да прилагодите број ранга у коду на 5. Корисник би можда желео да види највише бројеве у опсегу без претходног сортирања података.
| 1234567891011121314151617181920212223 | Суб Топ5Екампле ()Дим МиРанге Ас Ранге'Направи објекат опсегаПодеси МиРанге = Распон („А1: А10“)'Избришите претходне условне форматеМиРанге.ФорматЦондитионс.Делете'Додајте услов Топ10МиРанге.ФорматЦондитионс.АддТоп10Са МиРанге.ФорматЦондитионс (1)'Параметар од врха до дна.ТопБоттом = клТоп10Топ'Само првих 5.Ранг = 5ЗавршитиСа МиРанге.ФорматЦондитионс (1) .Фонт'Подесите боју фонта.Боја = -16383844ЗавршитиСа МиРанге.ФорматЦондитионс (1) .Интеријер'Подесите боју позадине ћелије.Боја = 13551615ЗавршитиЕнд Суб |
Подаци на вашем радном листу би изгледали овако након покретања кода:
Имајте на уму да се вредност 145 појављује два пута, па је шест ћелија истакнуто.
Значај параметара СтопИфТруе и СетФирстПриорити
СтопИфТруе је важан ако низ ћелија има више правила условног обликовања. Једна ћелија унутар опсега може задовољити прво правило, али може задовољити и наредна правила. Као програмер, можда ћете желети да прикаже обликовање само за прво правило до којег долази. Остали критеријуми правила могу се преклапати и могу унети ненамерне измене ако им се дозволи да наставе низ листу правила.
Подразумевано за овај параметар је Тачно, али можете га променити ако желите да се размотре сва друга правила за ту ћелију:
| 1 | МиРанге. ФорматЦондитионс (1) .СтопИфТруе = Нетачно |
Параметар СетФирстПриорити одређује да ли ће се то правило услова прво оценити када постоји више правила за ту ћелију.
| 1 | МиРанге. ФорматЦондитионс (1) .СетФирстПриорити |
Ово помера положај тог правила на позицију 1 унутар збирке услова формата, а сва друга правила ће бити померена надоле са промењеним бројевима индекса. Пазите ако мењате правила у коду помоћу индексних бројева. Морате бити сигурни да мењате или бришете право правило.
Приоритет правила можете да промените:
| 1 | МиРанге. ФорматЦондитионс (1). Приоритет = 3 |
Ово ће променити релативне положаје било којих других правила на листи условног формата.
Коришћење условног обликовања референцирајући друге вредности ћелије
Ово је једна ствар коју условно обликовање програма Екцел не може учинити. Међутим, можете да направите сопствени ВБА код да бисте то урадили.
Претпоставимо да имате колону података, а у суседној ћелији поред сваког броја налази се неки текст који означава које обликовање треба да се догоди на сваком броју.
Следећи код ће покренути вашу листу бројева, потражити у суседној ћелији форматирање текста, а затим форматирати број према потреби:
| 123456789101112131415161718192021 | Суб РеферТоАнотхерЦеллФорЦондитионалФорматтинг ()'Креирајте променљиве које садрже број редова за табеларне податкеДим РРов Ас Лонг, Н Ас Лонг'Снимите број редова унутар опсега табеларних податакаРРов = АцтивеСхеет.УседРанге.Ровс.Цоунт„Поновите све редове у опсегу табеларних податакаЗа Н = 1 То РРов'Користите наредбу Селецт Цасе за процјену обликовања на основу колоне 2Изаберите Цасе АцтивеСхеет.Целлс (Н, 2) .Валуе'Укључите унутрашњу боју у плавуФутрола "Плава"АцтивеСхеет.Целлс (Н, 1) .Интериор.Цолор = вбБлуе'Укључите унутрашњу боју у црвенуКућиште "Црвено"АцтивеСхеет.Целлс (Н, 1) .Интериор.Цолор = вбРед'Укључите унутрашњу боју у зеленуКућиште "Зелено"АцтивеСхеет.Целлс (Н, 1) .Интериор.Цолор = вбГреенКрај ИзаберитеСледећи Н.Енд Суб |
Када се овај код покрене, ваш радни лист ће сада изгледати овако:
Ћелије на које се позива форматирање могу се налазити било где на радном листу или чак на другом радном листу у радној свесци. Можете користити било који облик текста да бисте поставили услов за форматирање, а машта вас ограничава само у начинима на које бисте могли да примените овај код.
Оператори који се могу користити у изјавама о условном обликовању
Као што сте видели у претходним примерима, оператори се користе да одреде како ће се вредности услова оцењивати нпр. клИзмеђу.
Постоји низ ових оператора који се могу користити, у зависности од тога како желите да наведете критеријуме правила.
| Име | Вредност | Опис |
| клИзмеђу | 1 | Између. Може се користити само ако су дате две формуле. |
| клЕкуал | 3 | Једнако. |
| клГреатер | 5 | Веће од. |
| клГреатерЕкуал | 7 | Већи или једнак. |
| клЛесс | 6 | Мање од. |
| клЛессЕкуал | 8 | Мање или једнако. |
| клНотБетвеен | 2 | Не између. Може се користити само ако су дате две формуле. |
| клНотЕкуал | 4 | Није једнако. |
