Как да оцветим клетки в 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. 🙂

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

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

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

  3. Здравейте! Поздравления за подробния и полезен урок. Бихте ли ми казали как мога да задам дадена клетка да се оцветява ако в нея е записана дата, която е уикенд?

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

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