Додавањето условно форматирање во Excel ви овозможува да примените различни опции за форматирање во ќелија или опсег на ќелии што ги исполнуваат специфичните услови што ги поставивте.
Опциите за форматирање се применуваат само кога избраните ќелии ги исполнуваат овие поставени услови.
Опциите за форматирање што можат да се применат вклучуваат промени на фонт и боја на позадина, стилови на фонт, граници на ќелии и додавање на форматирање на броеви до податоците.
Од Excel 2007, Excel има голем број на вградени опции за најчесто користени услови како што се наоѓање на броеви кои се поголеми или помали од одредена вредност или наоѓање на броеви кои се над или под просечната вредност .
Покрај овие претходно поставени опции, исто така е можно да се создадат правила за правилно условно форматирање со помош на Excel формулите за тестирање за условите определени од корисникот.
Примена на повеќе правила
Може да се применат повеќе од едно правило за истите податоци за тестирање за различни услови. На пример, буџетските податоци може да имаат поставени услови со кои се применуваат промени во форматирањето кога се трошат одредени нивоа - како што се 50%, 75% и 100% - од вкупниот буџет.
Во такви околности, Excel прво одредува дали различните правила се во конфликт, и доколку е така, програмата следи утврден редослед на приоритет за да утврди кое правило за условно форматирање се применува на податоците.
Пример: Наоѓање податоци што надминуваат 25% и 50% се зголемува со условно форматирање
Во следниов пример, две опционални правила за условен форматирање ќе се применуваат на опсегот на ќелии B2 до B5.
- првото правило проверува дали податоците во ќелиите A2 до A5 се поголеми од соодветната вредност во B2 до B5 за повеќе од 25%.
- второто правило проверува дали истите податоци во A2: A5 ја надминуваат соодветната вредност во B2: B5 за повеќе од 50%.
Како што може да се види на сликата погоре, ако една од горенаведените услови е точна, бојата на позадината на ќелијата или ќелиите во опсегот Б1: Б4 ќе се промени.
- за податоци каде разликата е поголема од 25%, бојата на позадината на клетката ќе се смени во сина боја.
- ако разликата е поголема од 50%, бојата на позадината на клетката ќе се смени во црвена боја.
Правилата што се користат за остварување на оваа задача,
= (A2-B2) / A2> 25% = (A2-B2) / A2> 50%ќе бидат внесени со користење на дијалогот за условен форматирање New Formatting Rule .
Внесување на податоците за прирачникот
- Внесете ги податоците во ќелиите A1 до C5 како што се гледаат на сликата погоре
Забелешка: Чекор 3 од туториал ќе додаде формули во клетките C2: C4 кои покажуваат точна процентуална разлика помеѓу вредностите во ќелиите A2: A5 и B2: B5, со цел да се провери точноста на правилата за условен форматирање.
Поставување на условите за форматирање на условите
Како што рековме, условите правила за форматирање кои проверуваат за двата услови ќе бидат внесени со користење на дијалогот за ново форматирање Правило за форматирање.
Поставување условно форматирање за да најдете повеќе од 25% зголемување
- Означете ги клетките B2 до B5 во работниот лист.
- Кликнете на јазичето Дома на лентата.
- Кликнете на иконата за условен форматирање во лентата за да го отворите паѓачкото мени.
- Изберете ново правило за да го отворите дијалогот за ново форматирање како што е видено на сликата погоре.
- Во горната половина на дијалогот, кликнете на последната опција: Користете формула за да одредите кои ќелии ќе го форматираат.
- Во долниот дел од дијалогот, кликнете на вредностите Форми каде што оваа формула е точна: линија.
- Внесете ја формулата : = (A2-B2) / A2> 25% во дадениот простор
- Кликнете на копчето Формат за да го отворите дијалогот Форматирај ќелии.
- Во овој дијалог прозорецот, кликнете на јазичето Пополнете и изберете боја на сино полнење.
- Кликнете два пати за да ги затворите дијалозите и да се вратите на работниот лист.
- Во овој момент, бојата на позадината на клетките Б3 и Б5 треба да биде сина.
Поставување условно форматирање за да најдете повеќе од 50% зголемување
- Со клетките B2 до B5 сеуште селектирани, повторете ги чекорите од 1 до 6 погоре.
- Внесете ја формулата: = (A2-B2) / A2> 50% во обезбедениот простор.
- Кликнете на копчето Формат за да го отворите дијалогот Форматирај ќелии.
- Кликнете на јазичето Пополнете и одберете црвена боја за полнење.
- Кликнете два пати за да ги затворите дијалозите и да се вратите на работниот лист .
- Бојата на позадината на ќелијата Б3 се уште треба да биде сина што покажува дека разликата во процентот помеѓу броевите во А3 и Б3 клетките е поголема од 25%, но помала или еднаква на 50%.
- Бојата на позадината на ќелијата Б5 треба да се промени на црвено, што укажува дека процентот на разлика помеѓу броевите во ќелиите А5 и Б5 е поголем од 50%.
Проверка на правила за условно форматирање
Пресметување% Разлика
За да провериме дали условите за условен форматирање се внесени се точни, можеме да внесеме формули во клетките C2: C5 кои ќе ја пресметаат точната процентуална разлика помеѓу броевите во опсезите A2: A5 и B2: B5.
- Кликнете на ќелијата C2 за да ја направите активната ќелија.
- Внесете ја формулата = (A2-B2) / A2 и притиснете го копчето Enter на тастатурата.
- Одговорот 10% треба да се појави во ќелијата C2, што покажува дека бројот во ќелија А2 е 10% поголем од бројот во ќелијата Б2.
- Можеби ќе биде потребно да го смените форматирањето на ќелијата C2 за да го прикажете одговорот како процент.
- Користете ја рачката за пополнување за да ја копирате формулата од ќелијата C2 во клетките C3 до C5.
- Одговорите за клетките C3 до C5 треба да бидат: 30%, 25% и 60%.
- Одговорите во овие ќелии покажуваат дека правилата за условен форматирање се правилни бидејќи разликата помеѓу ќелиите A3 и B3 е поголема од 25%, а разликата помеѓу клетките A5 и B5 е поголема од 50%.
- Cell B4 не ја менува бојата, бидејќи разликата меѓу клетките А4 и Б4 е еднаква на 25%, а правилото за условно форматирање наведуваше дека процентот поголем од 25% е потребен за бојата на позадината да се смени во сино.
Редослед на претходен текст за условни правила за форматирање
Примена на правилата за условно форматирање во конфликт
Кога повеќе правила се применуваат на истиот опсег на податоци, Excel најпрво утврдува дали правилата се во конфликт.
Конфликтните правила се оние каде избраните опции за форматирање за секое правило не можат да се применат на истите податоци .
Во примерот користен во ова упатство, правилата се спротивставуваат бидејќи и двете правила ја користат истата опција за форматирање - промена на бојата на заднинската ќелија.
Во ситуација кога второто правило е точно (разликата во вредноста е поголема од 50% помеѓу две клетки), тогаш првото правило (разликата во вредност поголема од 25%) е исто така точно.
Орден на Прецеденција на Excel
Бидејќи една ќелија не може да има и црвена и сина позадина во исто време, Excel треба да знае што условно правило за форматирање треба да се примени.
Кое правило се применува, се одредува според редоследот на приоритет на Excel, во кој е наведено дека правилото што е повисоко во листата во дијалогот за правила на условите за форматирање има предност.
Како што е прикажано на сликата погоре, второто правило кое се користи во ова упатство (= (A2-B2) / A2> 50%) е повисоко во листата и затоа има предност во однос на првото правило.
Како резултат на тоа, бојата на позадината на ќелијата Б5 е променета во црвена боја.
Стандардно, новите правила се додаваат на врвот на листата и, според тоа, имаат повисок приоритет.
За да го промените редоследот на приоритет, користете ги копчињата со стрелки нагоре и надолу во дијалоговото поле како што е наведено на горната слика.
Примена на неконфликтни правила
Ако две или повеќе условни правила за форматирање не се во судир и двете се применуваат кога условот секое правило е тестирање станува точно.
Ако првото условно правило за форматирање во нашиот пример (= (A2-B2) / A2> 25%) го форматира опсегот на ќелии B2: B5 со сина граница наместо сина позадина, двете условни правила за форматирање нема да бидат во конфликт двата формати може да се применат без да се меша со другиот.
Како резултат на тоа, ќелијата Б5 ќе има и сина граница и црвена боја на позадината, бидејќи разликата помеѓу броевите во ќелиите А5 и Б5 е поголема од 25 и 50 проценти.
Условно форматирање наспроти регуларно форматирање
Во случај на конфликт меѓу правилата за условен форматирање и рачно применетите опции за форматирање, правилото за условно форматирање секогаш има приоритет и ќе се применува, наместо сите рачно додадени опции за форматирање.
Ако бојата на жолта боја првично била применета на клетките Б2 до Б5 во примерот, откако ќе се додадат условните правила за форматирање, само ќелиите B2 и B4 ќе останат жолти.
Бидејќи внесените условни правила за форматирање се однесуваат на ќелии Б3 и Б5, нивните бои во позадина ќе се променат од жолта до сина и црвена, соодветно.