Добавянето на условно форматиране в Excel ви позволява да приложите различни опции за форматиране към клетка или диапазон от клетки, които отговарят на определени условия, които сте задали. Задаването на такива условия може да ви помогне да организирате вашата електронна таблица и да улесните сканирането. Опциите за форматиране, които можете да използвате, включват промени в цвета на шрифта и фона, стилове на шрифта, граници на клетки и добавяне на форматиране на числа към данните.
Excel има вградени опции за често използвани условия като намиране на числа, които са по-големи или по-малки от определена стойност или намиране на числа, които са над или под средната стойност. В допълнение към тези предварително зададени опции можете също да създавате персонализирани правила за условно форматиране, като използвате формули на Excel.
Тези инструкции се отнасят за Excel 2019, 2016, 2013, 2010 и Excel за Microsoft 365.
Прилагане на множество условия в Excel
Можете да приложите повече от едно правило към едни и същи данни, за да тествате за различни условия. Например данните за бюджета може да имат определени условия, които прилагат промени във форматирането, когато се достигнат определени нива на разходи, като 50%, 75% и 100% от общия бюджет.
При такива обстоятелства Excel първо определя дали различните правила са в конфликт и ако е така, програмата следва зададен ред на приоритет, за да определи кое правило за условно форматиране да приложи към данните.
Намиране на данни, които надвишават 25% и 50% увеличения
В следния пример две персонализирани правила за условно форматиране ще бъдат приложени към диапазона от клетки B2 до B5. йени
- Първото правило проверява дали данните в клетки A2:A5 са по-големи от съответната стойност в B2:B5 с повече от 25%.
- Второто правило проверява дали същите данни в A2:A5 превишават съответната стойност в B2:B5 с повече от 50%.
Както може да се види на изображението по-горе, ако някое от горните условия е вярно, цветът на фона на клетката или клетките в диапазона B1:B4 ще се промени.
- За данни, където разликата е повече от 25%, цветът на фона на клетката ще се промени на зелен.
- Ако разликата е по-голяма от 50%, цветът на фона на клетката ще се промени на червен.
Правилата, използвани за изпълнение на тази задача, ще бъдат въведени с помощта на диалоговия прозорец Ново правило за форматиране. Започнете с въвеждане на примерните данни в клетки A1 до C5, както се вижда на изображението по-горе.
В последната част на урока ще добавим формули към клетки C2:C4, които показват точната процентна разлика между стойностите в клетки A2:A5 и B2:B5; това ще ни позволи да проверим точността на правилата за условно форматиране.
Задаване на правила за условно форматиране
Първо, ще приложим условно форматиране, за да намерим значително увеличение от 25 процента или повече.
Функцията ще изглежда така:
=(A2-B2)/A2>25%
- Маркирайте клетки B2 до B5 в работния лист.
- Щракнете върху раздела Начало на лентата.
- Щракнете върху иконата Условно форматиране в лентата, за да отворите падащото меню.
-
Изберете Ново правило, за да отворите диалоговия прозорец Ново правило за форматиране.
- Под Изберете тип правило щракнете върху последната опция: Използвайте формула, за да определите кои клетки да форматирате.
- Въведете формула отбелязана по-горе в пространството по-долу Форматирайте стойности, където тази формула е вярна:
- Щракнете върху бутона Форматиране, за да отворите диалоговия прозорец. Щракнете върху раздела Попълване и изберете цвят.
- Щракнете върху OK, за да затворите диалоговите прозорци и да се върнете към работния лист.
- Цветът на фона на клетки B3 и B5 трябва да се промени до цвета, който сте избрали.
Сега ще приложим условно форматиране, за да намерим увеличение от 50 процента или повече. Формулата ще изглежда така:
- Повторете първите пет стъпки по-горе.
-
Въведете формула предоставена по-горе в пространството по-долу Форматирайте стойности, където тази формула е вярна:
- Щракнете върху бутона Форматиране, за да отворите диалоговия прозорец. Щракнете върху раздела Попълване и изберете цвят, различен от този, който сте направили в предишния набор от стъпки.
- Щракнете върху OK, за да затворите диалоговите прозорци и да се върнете към работния лист.
Цветът на фона на клетка B3 трябва да остане същият, което показва, че процентната разлика между числата в клетки A3 иB3 е по-голямо от 25 процента, но по-малко или равно на 50 процента. Цветът на фона на клетка B5 трябва да се промени на новия цвят, който сте избрали, което показва, че процентната разлика между числата в клетки A5 и B5 е по-голямо от 50 процента.
Проверка на правилата за условно форматиране
За да проверим дали въведените правила за условно форматиране са правилни, можем да въведем формули в клетки C2:C5, които ще изчислят точната процентна разлика между числата в диапазонитеA2:A5 и B2:B5.
Формулата в клетка C2 изглежда така:
=(A2-B2)/A2
- Щракнете върху клетка C2, за да я направите активната клетка.
- Въведете горната формула и натиснете клавиша Enter на клавиатурата.
- Отговорът 10% трябва да се появи в клетка C2, което показва, че числото в клетка A2 е 10% по-голямо от числото в клетка B2.
- Може да се наложи да промените форматирането на клетка C2, за да се покаже отговорът като процент.
- Използвайте манипулатора за попълване, за да копирате формулата от клетка C2 до клетки C3 до C5.
- Отговорите за клетки C3 до C5 трябва да бъдат 30%, 25% и 60%.
Отговорите в тези клетки показват, че правилата за условно форматиране са точни, тъй като разликата между клетки A3 и B3 е по-голяма от 25 процента и разликата между клетки A5 и B5 е по-голяма от 50 процента.
Клетка B4 не промени цвета си, защото разликата между клетки A4 и B4 е равна 25 процента, а нашето правило за условно форматиране посочва, че е необходим процент, по-голям от 25 процента, за да се промени цвета на фона.
Ред на предимство за условно форматиране
Когато приложите няколко правила към един и същи диапазон от данни, Excel първо определя дали правилата са в конфликт. Конфликтни правила са тези, при които опциите за форматиране не могат да бъдат приложени към едни и същи данни.
В нашия пример правилата са в конфликт, тъй като и двете използват една и съща опция за форматиране - промяна на цвета на фона на клетката.
В ситуация, в която второто правило е вярно (разликата в стойността е повече от 50 процента между две клетки), тогава първото правило (разликата в стойността е по-голяма от 25 процента) също е вярно.йени
Тъй като една клетка не може да има два различни цветни фона едновременно, Excel трябва да знае кое правило за условно форматиране трябва да приложи.
Поредността на приоритета на Excel гласи, че първо се прилага правилото, което е по-високо в списъка в диалоговия прозорец Диспечер на правила за условно форматиране.
Както е показано на изображението по-горе, второто правило, използвано в този урок, е по-високо в списъка и следователно има предимство пред първото правило. В резултат фоновият цвят на клетка B5 е зелен.
По подразбиране новите правила отиват в горната част на списъка; за да промените реда, използвайте бутоните стрелки нагоре и надолу в диалоговия прозорец.
Прилагане на неконфликтни правила
Ако две или повече правила за условно форматиране не са в конфликт, и двете се прилагат, когато условието, което всяко правило тества, стане вярно.
Ако първото правило за условно форматиране в нашия пример форматира диапазона от клетки B2:B5 с оранжева рамка вместо оранжев цвят на фона, двете правила за условно форматиране няма да конфликт, тъй като и двата формата могат да се прилагат, без да пречат на другия.
Условно форматиране срещу редовно форматиране
В случай на конфликт между правилата за условно форматиране и ръчно приложените опции за форматиране, правилото за условно форматиране винаги има предимство и ще се прилага вместо всички ръчно добавени опции за форматиране.
