Наскоро колега ме помоли да помогна за едно интересно казусче с Conditional Formatting в Ексел. Решението се оказа доста нестандартно, затова реших да го споделя. Може би ще ви бъде от полза и в други ситуации.
Казусът
Имаме някакъв отчет, който изглежда така (данните са абсолютно измислени)
В последната колона е приложен Conditional Formatting, който оцветява в различни цветове стойностите в нея. Задачката е да се оцвети всеки ред от таблицата със съответния цвят от последната колона.
Преди да видим решението няколко изречения за този вид Conditional Formatting, който се нарича
Color Scales
Това е така да се каже готова схема на Conditional Formatting, при която числата се оцветяват в различни цветове в зависимост от относителното им тегло спрямо останалите числа. В случая по-малки числа като 146 и 163 са на тъмно червен фон, малко по-големи числа като 272 и 282 са на по-светлочервен фон. И колкото по-големи стават числата фонът преминава от оранжев, през жълт, светло зелен и за най-големите числа като 893 е тъмно зелен.
Идеята на този вид Conditional Formatting е ползвателят на отчета лесно да забележи тези стойности, които не са достатъчно високи или ниски. Примерно, ако това са продажби, с тъмно червено да му „светнат“ редовете с най-ниските продажби, а с тъмно зелено – тези с най-високите продажби.
Да се постави такова условно форматиране е изключително лесно:
Отиваме на Home -> Conditional Formatting -> Color Scales и си избираме някой от предефинираните формати.
Ако тези стандартни формати в Ексел не ни удовлетворяват, можем да ги променим от Conditional Formatting -> Manage Rules
И след това Edit Rule
Менюто, което се появява предоставя разнообразни възможности за определяне на ниските, средните и високи стойности и на цветовата схема.
Можем например да си изберем изцяло синя цветова гама, при което колоната ще изглежда така:
Проблемът
Първоначално си мислех, че решението е свързано именно с промяната в настройките. Считах, че ще е напълно достатъчно да променя обхвата на действие на Conditional Formatting. Вместо да се прилага само за колона Е, да обхваща цялата таблица, т.е. областта $B$16:$E$37
Резултатът обаче е силно разочароващ.
Просто Ексел сравнява всички числа в областта на приложение на условното форматиране и им поставя цвят в зависимост от това колко са големи или малки. И тъй като датите са всъщност числа и са по-големи от 43,000 то те попадат в най-високата категория, а стойностите в колона Е, които са под 1,000 – в най-ниската. А текстовите данни пък въобще не получават форматиране, защото не са числа.
Но не важно какви са данните – текст или числа и колко големи са числата. Проблемът е, че Color Scales определят цвета като сравняват всички числа в областта, а нашата задача е да се определи цветът в колона Е и да се разнесе по другите колони.
И ето тук ударихме на камък, защото както и да го въртяхме, беше невъзможно да го излъжем без VBA.
Решението
С малко помощ от чичко Гугъл намерихме решение, което е доволно просто и свърши чудесна работа.
Трябва просто да се копира колона Е
След това да се отиде в … Word и да се пейстне там.
На следващата стъпка, копираме колоната в Word и я пействаме обратно в Ексел някъде в страни от таблицата. Сега тя вече е само стойности, оцветени в някакъв цвят, но без Conditional Formatting. Той се изгубва, когато пейстнем стойностите в Word, защото Word е програма за текстообработка, а не за обработка на таблици с числа.
Накрая ни остава просто да пейстнем формата в останалите колони от таблицата (с Paste Special Values или с Format Painter).
И резултатът обаче, не е точно каквото очакваме:
В колоната за датите, форматът на числата също се е загубил при прехвърлянето от Excel в Word и обратно. Така че трябва да се оправи, но все пак идеални неща няма, пък и алтернативата е писане на макрос, който със сигурност ще отнеме повече време.
Вместо заключение
Честно казано, аз не съм убеден, че като направим отчета шарен като родопска черга, той ще стане по-разбираем и полезен за тези, които ще го ползват.
Но решението на казуса е хубав пример за мислене извън кутията и затова с удоволствие го споделям.
По-интересни и практични проложения на Conditional Formatting може да намерите тук или тук.
А може би за вас украсяването и форматирането на отчетите са излишен лукс, защото събирането и обработката на данните ви отнемат прекалено много време? В такъв случай задължително се отбийте в раздела за Power query.