Как да оцветим клетки в Excel на базата на условия?

Как можем да оцветим клетки в Excel по зададено условие?

Това е един от въпросите, който получихме във форума на Aula. Казусът е интересен и доста често срещан в практиката. Поради тази причина днес решихме да покажем как можем да се справим с проблема.

Естествено, ако имаме таблица от 5 – 6 реда, няма проблема ръчно да оцветим редовете, които отговарят на дадено условие. Но какво правим при по-дълги таблици? Отново оцветяваме на ръка?

Не е ли по-бързо и лесно, когато искаме да проверим много големи и дълги таблици, да зададем едно условие и редовете, отговарящи на проверката, да се оцветят в някакъв цвят автоматично.

Ето видео как да оцветим клетки в Excel, отговарящи на дадено условие:

Оцветяване на редове в Excel по зададено условие

 

Примерът е следният – имаме една таблица, в която искаме определени редове, отговарящи на дадено условие, да бъдат маркирани с цвят.

Условието, което трябва да бъде изпълнено, за да се оцвети редът, е стойността на клетката АА да е по-голяма от тази в AC.

Има няколко варианта за оцветяване на редовете, които отговарят на условието.

Вариант 1: Ръчно намиране и стилизиране на тези редове

Можем да го направим, като маркираме реда. След което го оцветим посредством предефинираните стилове в Styles или чрез бутона Fill Color.

Този метод би бил подходящ ако имахме малката таблица в която трябва да оцветим 2-3 реда, тук обаче таблицата е с около 200 реда. Проверка  и оцветяване на ръка би ни отнело доста време. Освен това нямаме динамичност на това оцветяване, при промяна на данните  следва отново да правим проверка за изпълнено условие в определен ред. Съответно отново трябва ръчно да оцветим реда.

Вариант 2: Използване на функцията за условно форматиране (Conditional Formatting)

С последната колона от таблицата ясно виждаме дали е изпълнено условието. Ако стойността е над 100%, значи условието е изпълнено и редът трябва да бъде оцветен, затова го маркираме целия. Това става по следния начин:

1. Отиваме на раздела.
2. Кликваме на Conditional Formatting. (Условно форматиране)
3. Избираме Highlight Cells Rules. (Маркирай клетките на база определено условие)
4. Избираме Greater than. (По-големи от…)

В отворилия се прозорец:

1. Избираме стойност, спрямо която стойността в клетките трябва да бъде по-голяма.
2. На стъпка 2 избираме някакъв вид форматиране на клетките.

Натискаме ОК, за да приложим настройките. Всички клетки, стойността, на които е по-голяма от 100%, се оцветиха в червено.

Така виждаме много добре къде точно е изпълнено условието.

Как да оцветим целия ред, отговарящ на условието?

Отново отиваме на Conditional Formatting, НО този път не избираме предефинирано условие, а създадаване ново такова – New Rule (Ново правило/условие).

1. Избираме условието да бъде получено на база формула.
2. В раздела Format values where this formula is true: избираме стрелката.

Изписваме формулата „=$АА$5>$AC$5“, която определя условието.

Кликваме на бутона Format, за да се появи прозорец Format Cells, в който да настроим форматирането на клетките, изпълняващи това условие.

В разделa Fill избираме цвета, с който ще се оцветят клетките.

Натискаме ОК, за да запазим настройките.

Натискаме ОК, за да запазим настройките на дефинираното от нас ново условие.

Ето резултатът, който получихме. Оцвети се само една клетка.

Оцвети се само една-единствена клетка и това е клетката, за която записахме формулата „=$АА$5>$AC$5“, която определя условието.

Как да оцветим абсолютно всички редове, отговарящи на условието?

Отиваме отново в Conditional Formatting и избираме Manage Rules. Отваряме мениджъра, съдържащ условните форматирания, създадени от нас.

Не виждаме нито едно условие. Това е така, защото в раздела Show Formatting rules for: е избрано Current Selection, т.е. показват се условията само за настоящо селектирания обект в Excel. Кликваме върху стрелката на падащото меню в Current Selection.

1. Избираме This workbook, за да видим всички правила в настоящия документ на Excel.
2. Избираме условието, което създадохме.
3. Избираме Edit Rule, за да го коригираме.

Формулата, така написана, благодарение на абсолютната референция, се отнася единствено и само за клетка $АА$5 и клетка $AC$5.

Ние обаче искаме условието да бъде за всички редове в колоните АА и АС.

За да бъде валидно това условие не само за ред 5, а за всички редове, махаме абсолютната референция към редовете. Махаме символа $ пред 5. А след това с ОК потвърждаваме промените.

В раздела Applies to указваме за кои клетки да бъде приложено оцветяването, т.е. кои клетки да се оцветяват.

За да осветим целия ред, маркираме цялата таблица.

Как най – лесно става това може да си припомните тук: http://aula.bg/blog/klavish-shift-excel/

С Apply прилагаме направените промени.

Вече всички редове, изпълнили условието, са оцветени изцяло.

С ОК, потвърждаваме всички направени промени в Conditional Manger –a.

И така абсолютно всички редове, отговарящи на направеното от нас условие, са оцветени.

Примерът е малко специфичен, но тази функция в Excel на 99% е приложима и във вашата сфера на работа. Спомняте ли си кога за последно оцветявахте ръчно редове в Excel? Какво беше условието, на база на което маркирахте редовете, изпълнили Вашето изискване? Споделете отговора на тези два въпроса в коментарите под тази тема.

 

 

Ако тази статия Ви е била полезна и интересна, можете да я споделите с приятелите си във Facebook. 🙂