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

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

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

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

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

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

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

01_fv_33table_with_cond 

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

02_fv_33_what_we_want

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

03_2_fv_33_by_hand

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

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

03_fv_33_by_hand

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

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

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

04_fv_33_conditional

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

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

05_fv_33_conditional_set_up_window

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

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

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

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

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

06_fv_33_new_rule

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

07_fv_33_range

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

08_fv_33_select_formula

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

09_fv_33_select_formula

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

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

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

10_fv_33_conditiona_ok

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

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

11_fv_33_conditiona_only_one_cell

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

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

12_fv_33_manage_rulls_con

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

13_fv_33_current_selection

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

правила за форматиране

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

15_fv_33_remove_dollarl

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

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

excel условно форматиране

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

17_fv_33_apply_to

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

оцветим клетки в excel

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

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

оцветим клетки в ексел

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

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

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

20_fv_33_ready_all

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

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

 

 

buton_20_bezplatni_uroka

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

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

  1. Здравейте!Имам следния проблем:искам да си направя работен график за следващата година-графикът е 2 на 2 раб.дни.Направила съм таблица с месеците и искам да оцветя в червено раб.дни,т.е. през 2 клетки 2 да са в червено.Каква формула трябва да напиша,за да постигна това?

  2. Отново изчерпателно представен материал. Всеки ден научавам все повече за EXEL,. Благодаря за труда ви.

Вашият коментар

Вашият имейл адрес няма да бъде публикуван. Задължителните полета са отбелязани с *