Добавянето на условно форматиране в Excel ви позволява да приложите различни опции за форматиране към клетка или диапазон от клетки, които отговарят на определени условия, които сте задали. Опциите за форматиране се прилагат само когато избраните клетки отговарят на тези зададени условия.
Опциите за форматиране, които могат да бъдат приложени, включват промени в шрифта и цвета на фона, стиловете на шрифта, границите на клетките и добавянето на форматиране на номера към данните.
Excel разполага с няколко вградени опции за често използвани условия, като намиране на числа, които са по-големи или по-малки от определена стойност или намират числа, които са над или под средната стойност.
В допълнение към тези предварително зададени опции е възможно също да се създадат персонализирани правила за условно форматиране, като се използват формули на Excel, за да се тестват условията, определени от потребителя.
01 от 04Прилагане на няколко правила в Excel
Повече от едно правило може да се приложи към същите данни, за да се тестват различни условия. Например, бюджетните данни могат да имат определени условия, които да прилагат промените в форматирането, когато се изразходват определени нива, като например 50%, 75% и 100% от общия бюджет.
При такива обстоятелства Excel първо определя дали различните правила са в конфликт и ако е така, програмата следва определен ред от предимства, за да определи кое условно форматиращо правило се прилага към данните.
Намиране на данни, които надвишават 25% и 50% увеличение
В следващия пример ще бъдат приложени две правила за форматиране по подразбиране за обхвата на клетки В2 да се B5.
- Първото правило проверява дали данните в клетки А2 да се A5 е по - голяма от съответната стойност в B2 да се B5 с повече от 25%.
- Второто правило проверява дали същите данни са в А2: А5 надвишава съответната стойност в B2: B5 с повече от 50%.
Както може да се види на изображението по-горе, ако някое от горните условия е вярно, цветът на фона на клетката или клетките в диапазона В1: В4 ще се промени.
- За данни, където разликата е по-голяма от 25%, цветът на фона на клетката ще се промени на зелено.
- Ако разликата е по-голяма от 50%, цветът на фона на клетката ще се промени на червено.
Правилата, използвани за изпълнение на тази задача, ще бъдат въведени при условното форматиране Ново правило за форматиране диалогов прозорец. Започнете, като въведете примерните данни в клетки A1 до C5, както е показано на изображението по-горе
В трета стъпка на урока ще добави формули към клетките С2: C4 които показват точната процентна разлика между стойностите в клетките А2: А5 и B2: B5 за да се провери точността на правилата за условно форматиране.
02 от 04Настройване на условните правила за форматиране
Както бе споменато, условните правила за форматиране, които проверяват за двете условия, ще бъдат въведени при условното форматиране Ново правило за форматиране диалогов прозорец. Нека да започнем, като зададем нашето персонализирано условно форматиране:
Условно форматиране, за да откриете повече от 25% увеличение
= (А2-В2) / А2> 25%
- връхна точка клетки В2 да се B5 в работния лист.
- Кликнете върху Начален раздел от панделка.
- Кликнете върху Условно форматиране икона в панделка за да отворите падащото меню.
- Избирам Ново правило за да отворите Ново правило за форматиране диалогов прозорец.
- От стил: , изберете класически опция.
- Във второто падащо меню кликнете върху последната опцияИзползвайте формула, за да определите кои клетки да се форматират.
- Въведете формула предоставени по-горе в предоставеното място.
- Кликнете върху Форматирайте с: падащо меню и след това изберете Светли червени попълнения с тъмен червен текст опция.
- Кликнете Добре за да затворите диалоговите прозорци и да се върнете в работния лист.
- На този етап цветът на фона клетки ВЗ и B5 трябва да е зелен.
Условно форматиране, за да откриете повече от 50% увеличение
= (А2-В2) / А2> 50%
връхна точка клетки В2 да се B5 в работния лист.
- Кликнете върху Начален раздел от панделка.
- Кликнете върху Условно форматиране икона в панделка за да отворите падащото меню.
- Избирам Ново правило за да отворите Ново правило за форматиране диалогов прозорец.
- От стил: , изберете класически опция.
- Във второто падащо меню кликнете върху последната опцияИзползвайте формула, за да определите кои клетки да се форматират.
- Въведете формула предоставени по-горе в предоставеното място.
- Кликнете върху Форматирайте с: падащо меню и след това изберете Светло зелено попълнете с тъмнозелен текст опция.
- Кликнете Добре за да затворите диалоговите прозорци и да се върнете в работния лист.
Цветът на фона на клетка ВЗ трябва да е зелено, което означава, че процентната разлика между числата в клетки АЗ и B3 е по-голяма от 25%, но по-малка или равна на 50%. Цветът на фона на клетка В5 трябва да се смени до червено, което означава, че процентната разлика между числата в клетки А5 и B5 е по-голяма от 50%.
03 от 04Проверка на условните правила за форматиране
За да проверите дали въведените правила за условно форматиране са правилни, може да въведете формули в клетки С2: C5 което ще изчисли точната разлика между цифрите в диапазоните А2: А5 и B2: B5.
= (А2-В2) / А2
- Кликнете върху клетка С2 за да стане активната клетка.
- Въведете горната формула и натиснете Въведете на клавиатурата.
- Отговорът от 10% трябва да се появи в клетка С2, което показва, че номерът е в клетка А2 е 10% по - голям от броя в клетка В2.
- Възможно е да се наложи да промените форматирането клетка С2 за да се покаже отговор като процент.
- Използвай попълнете ръкохватката да копирате формулата от клетка С2 да се клетки СЗ да се C5.
- Отговорите за клетки СЗ да се C5 трябва да бъде 30%, 25% и 60%.
Отговорите в тези клетки показват, че създадените условни правила за форматиране са верни, тъй като разликата между тях клетки АЗ и B3 е по-голяма от 25% и разликата между клетки А5 и B5 е по-голяма от 50%.
Клетка В4 не промени цвета, защото разликата между клетки А4 и B4 се равнява на 25%, а правилото за условното форматиране указва, че е необходим процент, по-голям от 25%, за да се промени цветът на фона на зелено.
04 от 04Ред на предимство при условно форматиране
Когато се прилагат множество правила за същия диапазон от данни, Excel първо определя дали правилата са в конфликт. Конфликтните правила са онези, при които избраните опции за форматиране за всяко правило не могат да бъдат приложени едновременно към едни и същи данни.
В примера, използван в този настойнически, конфликтът на правилата, тъй като и двата правила използват една и съща опция за форматиране - тази за промяна на цвета на фона на клетката.
В ситуация, при която второто правило е вярно (разликата в стойността е по-голяма от 50% между две клетки), то и първото правило (разликата в стойността е по-голяма от 25%) също е вярно.
Ред на предимство на Excel
Тъй като една клетка не може едновременно да има червен и зелен фон, Excel трябва да знае кое условно форматиращо правило трябва да се прилага.
Правилото, което се прилага, се определя от класацията на Excel, която гласи, че правилото, което е по-високо в списъка в диалоговия прозорец "Условни правила за форматиране", има предимство.
Както е показано на изображението по-горе, второто правило, използвано в този урок, е по-високо в списъка и следователно има предимство пред първото правило. В резултат на това цветът на фона на клетката B5 се променя на червено.
По подразбиране новите правила се добавят в горната част на списъка и следователно имат по-висок приоритет. За да промените реда на приоритета, използвайте Нагоре и надолу бутоните със стрелки в диалоговия прозорец, както е показано на изображението по-горе.
Прилагане на неконфликтни правила
Ако две или повече условни правила за форматиране не са в противоречие, и двете се прилагат, когато условието, което всяко правило тества, става истинно.
Ако първото правило за условно форматиране в нашия пример форматира обхвата на клетките B2: B5 със зелена граница вместо зелен цвят на фона, двете условни правила за форматиране няма да се окажат конфликтни, тъй като и двата формата могат да бъдат приложени, без да се намесва с другия.
Като резултат, клетка В5 ще има както зелена граница, така и червен цвят на фона, тъй като разликата между цифрите в клетки А5 и B5 е по-голямо от 25 и 50 процента.
Условно форматиране срещу регулярно форматиране
В случай на конфликт между правилата за условно форматиране и ръчно приложените опции за форматиране правилото за условно форматиране винаги има предимство и ще бъде приложено вместо ръчно добавени опции за форматиране.
Ако първоначално е приложен жълт цвят на фона клетки В2 да се B5 в примера, след като са добавени условните правила за форматиране само клетки В2 и B4 ще остане жълто. Тъй като въведените условни правила за форматиране се отнасят за клетките B3 и B5, техните цветове на фона се променят съответно от жълто на зелено и червено.
