Условно форматиране в Excel – подробно с примери

Ако трябва визуално да представите информация, тогава е много удобно да направите това с помощта на условно форматиране. Потребителят, който използва тази услуга, спестява огромно количество енергия и време. За да получите необходимата информация, е достатъчен бърз преглед на файла.

Как да направите условно форматиране в Excel

Можете да намерите „Условно форматиране“ в първия раздел на лентата, като отидете в секцията „Стилове“. 

След това трябва да намерите иконата със стрелка малко вдясно с окото си, преместете курсора към нея. След това ще се отворят настройките, където можете гъвкаво да конфигурирате всички необходими параметри. 

След това трябва да изберете подходящия оператор, за да сравните желаната променлива с число. Има четири оператора за сравнение – по-голямо от, по-малко от, равно на и между. Те са изброени в менюто с правила. 

След това посочихме поредица от числа в диапазона A1:A11.

Нека форматираме подходящ набор от данни. След това отваряме менюто с настройки „Условно форматиране“ и задаваме необходимите критерии за избор на данни. В нашия случай, например, ще изберем критерия „Още“. 

Ще се отвори прозорец с набор от опции. В лявата част на прозореца има поле, в което трябва да посочите числото 15. В дясната част е посочен методът за маркиране на информация, при условие че отговаря на зададения по-рано критерий. Резултатът ще се покаже веднага.

След това завършете конфигурацията с бутона OK.

За какво е условното форматиране?

До известна степен можем да кажем, че това е инструмент, след като научите, че работата с Excel никога няма да бъде същата. Това е така, защото прави живота много по-лесен. Вместо ръчно да задавате форматирането на клетка, която отговаря на определено условие всеки път, и също така да се опитвате да я проверите сами за съответствие с този критерий, просто трябва да зададете настройките веднъж и тогава Excel ще направи всичко сам.

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

Да вземем пример.

Тази таблица показва инвентара, който е на склад. Има колони. като продукт, средни продажби (измерени в единици на седмица), наличност и колко седмици остават от този продукт.

Освен това задачата на мениджъра по покупките е да определи онези позиции, чието попълване е необходимо. За да направите това, трябва да погледнете четвъртата колона отляво, където се записва наличността на стоките по седмици.

Да предположим, че критерият за определяне на причина за паника е по-малко от 3 седмици инвентаризация. Това показва, че трябва да подготвим поръчка. Ако запасът от стоки е по-малък от две седмици, това показва необходимостта от спешно подаване на поръчка. Ако таблицата съдържа огромен брой позиции, тогава ръчната проверка колко седмици остават е доста проблематична. Дори и да използвате търсачката. А сега нека да видим как изглежда таблицата, която подчертава дефицитните стоки в червено.

И наистина, ориентирането е много по-лесно. 

Вярно, този пример е образователен, който е малко опростен спрямо реалната картина. А спестените секунди и минути с редовното използване на такива таблици се превръщат в часове. Сега е достатъчно просто да погледнете таблицата, за да разберете кои стоки са в недостиг, а не да прекарвате часове в анализиране на всяка клетка (ако има хиляди такива стокови позиции).

Ако има „жълти“ стоки, тогава трябва да започнете да ги купувате. Ако съответната позиция е червена, тогава трябва да го направите незабавно. 

По стойност на друга клетка

Сега нека да разгледаме следния практически пример.

Да предположим, че имаме такава таблица и сме изправени пред задачата да маркираме редове, съдържащи определени стойности. 

Така че, ако проектът все още се изпълнява (т.е. все още не е завършен, следователно е маркиран с буквата „P“), тогава трябва да направим фона му червен. Завършените проекти са маркирани в зелено.

Нашата последователност от действия в тази ситуация ще бъде следната:

1. Изберете диапазон от стойности. 
2. Кликнете върху бутона „Условно форматиране“ – „Създаване на правило“.
3. Моля, обърнете внимание, че в нашия случай трябва да приложите формулата под формата на правило. След това използваме функцията IFза маркиране на съвпадащи редове. 

След това попълнете редовете, както е показано на тази снимка.

Важно! Низовете трябва да се реферират абсолютно. Ако говорим за клетка, тогава в този случай тя е смесена (с фиксиране на колона).

По същия начин се създава правило за тези работни проекти, които не са завършени до момента. 

Ето как изглеждат нашите критерии.

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

Множество условия

За по-голяма яснота нека вземем диапазона A1:A11 от таблицата, за да демонстрираме как в реален практически пример можете да използвате няколко условия, за да определите форматирането на клетката.

Самите условия са следните: ако числото в клетката надвишава стойността 6, то се маркира в червено. Ако е зелено, значи цветът е зелен. И накрая, най-големите числа, повече от 20, ще бъдат подчертани в жълто. 

Има няколко начина за извършване на условно форматиране според няколко правила.

1. Метод 1. Избираме нашия диапазон, след което в менюто с настройки за условно форматиране изберете такова правило за избор на клетка като „Още“. Числото 6 е изписано отляво, а форматирането е зададено отдясно. В нашия случай запълването трябва да е червено. След това цикълът се повтаря два пъти, но вече са зададени други параметри – съответно повече от 10 и зелен цвят и над 20 и жълт цвят. Ще получите такъв резултат.
   

   

2. Метод 2. Отидете в главното меню с настройки на инструмента „Условно форматиране“ на Excel. Там намираме менюто „Създаване на правило“ и щракнете с левия бутон върху този елемент.
   

   

    След това След това изберете елемента „Използване на формула …“ (маркиран на фигурата с червена рамка) и задайте първото условие. След това щракнете върху OK. След това цикълът се повтаря за следващите условия по същия начин, както е описано по-горе, но се използва формулата.

   

Важно е да вземете предвид това. В нашия пример някои клетки отговарят на няколко критерия едновременно. Excel разрешава този конфликт по следния начин: правилото, което е по-горе, се прилага първо. 

Ето и екранна снимка за по-добро разбиране.

Например, имаме числото 24. То отговаря едновременно на трите условия. В този случай ще има запълване, което отговаря на първото условие, въпреки факта, че попада повече под третото. Следователно, ако е важно числата над 20 да бъдат запълнени с жълто, третото условие трябва да бъде поставено на първо място. 

Условно форматиране на дата

Сега нека видим как условното форматиране работи с датите. Имаме толкова готина гама.

В този случай трябва да зададете правило за условно форматиране като „Дата“.

След това се отваря диалогов прозорец с цял набор от условия. Можете да се запознаете с тях подробно в тази екранна снимка (изброени са в лявата част на екрана под формата на списък).

Просто трябва да изберем подходящия и да кликнете върху бутона „OK“.

Виждаме, че онези дати, които се отнасят за последната седмица по времето, когато този диапазон е бил изчертан, са маркирани в червено. 

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

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

За да направите това, щракнете върху бутона „Създаване на правило“ в менюто за условно форматиране и след това изберете елемента, показан на екранната снимка.

Редове (по стойност на клетка)

Да предположим, че трябва да маркираме реда, който съдържа клетка с определена стойност. В този случай трябва да извършим същата последователност от действия, както по-горе, но за низове. Тоест можете да изберете към кой диапазон да се прилага условното форматиране. 

Как да създадете правило

За да създадете правило, трябва да използвате подходящата опция в секцията „Условно форматиране“. Нека разгледаме конкретна последователност от действия. 

Първо трябва да намерите елемента „Условно форматиране“ на лентата в раздела „Начало“. Разпознава се по характерните разноцветни клетки с различни размери с картинка до нея със зачеркнат знак за равенство. 

Има бутон „Създаване на правило“. Можете също да щракнете върху бутона „Управление на правилата“, след което ще се отвори диалогов прозорец, чрез който също можете да създадете правило, като щракнете върху съответния бутон в горния ляв ъгъл. 

Можете също така да видите в този прозорец онези правила, които вече са приложени.

Правила за избор на клетки

Добре, говорим толкова много за това как да създаваме правила, но какво е това? Правилото за избор на клетка се отнася до функциите, които се вземат предвид от програмата, за да реши как да форматира клетките, които им пасват. Например условията могат да бъдат по-големи от определено число, по-малко от определено число, формула и т.н. Има цял набор от тях. Можете да се запознаете с тях и да практикувате използването им „в пясъчника“. 

Форматиране на всички клетки въз основа на техните стойности

Човек може самостоятелно да зададе диапазона: цял ред, целия документ или една клетка. Програмата може да се фокусира само върху стойността на една клетка. Това добавя гъвкавост към процеса. 

Форматирайте само уникални или дублирани клетки

Има голям брой правила, директно свързани в програмата. Можете да се запознаете с тях в менюто „Условно форматиране“. По-специално, за да може форматирането да се прилага изключително за дублирани стойности или само за уникални, има специална опция - „Дублирани стойности“.

Ако изберете този елемент, се отваря прозорец с настройки, където можете да изберете и обратната опция – изберете само уникални стойности. 

Форматирайте стойности в диапазона под и над средното

За да форматирате стойности под или над средното, има и специална опция в същото меню. Но трябва да изберете друго подменю – „Правила за избор на първа и последна стойност“. 

Форматиране само на първата и последната стойност

В същото подменю е възможно да маркирате само първата и последната стойност със специален цвят, шрифт и други методи за форматиране. Съгласно стандарта има възможност за избор на първите и последните десет елемента, както и 10% от общия брой клетки, включени в този диапазон. Но потребителят може самостоятелно да определи колко клетки трябва да избере.

Форматиране само на клетки с конкретно съдържание

За да форматирате само клетки с конкретно съдържание, трябва да изберете правилото за форматиране Равно на или Текстът съдържа. Разликата между тях е, че в първия случай низът трябва да отговаря напълно на критерия, а във втория - само частично. 

Както можете да видите, условното форматиране е многофункционална функция на програмата Excel, която дава огромен брой предимства на човека, който го е овладял. Знаем, че на пръв поглед всичко това може да изглежда сложно. Но всъщност ръцете се привличат веднага щом се появи, за да подчертае определен текст в червено (или да го форматира по друг начин), въз основа на съдържанието му или в съответствие с друг критерий. Тази функция е включена в основния набор от знания на Excel, без нея дори аматьорската работа с електронни таблици и бази данни е невъзможна.