Условно форматиране с формули в Excel
По подразбиране Excel вече ни е предоставил много видове условно форматиране, което може да се направи с нашите данни, но ако някой потребител иска форматирането да се извършва по конкретни критерии или формула, можем да го направим в началния раздел на раздела за условно форматиране, когато щракнете върху новото правило ще намерим опция за условно форматиране с формули, където можем да напишем формули, които ще определят клетките за форматиране.
Условното форматиране с Formula не е нищо друго освен промяна на формата на клетките въз основа на условието или критериите, дадени от потребителя. Може да сте използвали условно форматиране, за да подчертаете най-горната стойност в диапазона, да маркирате и дублиращи се стойности.
Хубавото е, че имаме формули в условно форматиране в Excel. Всички формули трябва да са логични; резултатът ще бъде ИСТИНЕН или НЕВЯРЕН. Ако логическият тест премине, тогава ще получим условно форматиране, а ако логическият тест на Excel не успее, тогава няма да получим нищо.
Общ преглед
Позволете ми да ви запозная с условното форматиране с прозореца на формулата.
В раздела по-долу на статията ще видим голямо разнообразие от примери за прилагане на условно форматиране с формули.
Как да използвам условното форматиране на Excel с формули?
# 1 - Акцентирайте клетки, които имат стойности под 500
Да приемем, че по-долу са номерата на продажбите, с които работите, а по-долу е продажната цена за единица.
- Стъпка 1: Отидете на Условно форматиране и кликнете върху Ново правило.
- Стъпка 2: Сега изберете „ Използвайте формула, за да определите кои клетки да форматирате.“
- Стъпка 3: Сега, под „ Форматиране на стойности, където формулата е вярна“, приложете формулата като A2 <500 и след това кликнете върху Форматиране, за да приложите форматирането на Excel.
- Стъпка 4: Изберете формата според вашето желание.
- Стъпка 5: Сега можете да видите предварителен преглед на формата в полето за предварителен преглед.
- Стъпка 6: Сега кликнете върху OK, за да завършите форматирането. Сега той подчерта всички клетки, които имат номер <500.
# 2 - Маркирайте една клетка въз основа на друга клетка
Можем да подчертаем едната клетка въз основа на друга стойност на клетката. Да приемем, че имаме данни за име на продукт и цена на продукта в първите две колони.
От горната таблица трябва да подчертаем продуктите, ако продажната цена е> 220.
- Стъпка 1: Изберете продуктовата гама и след това отидете на условно форматиране и щракнете върху Ново правило.
- Стъпка 2: Във формулата приложете формулата като B2> 220.
- Стъпка 3: Кликнете върху бутона Форматиране и приложете формата според вашето желание.
- Стъпка 5: Щракнете върху OK; имаме готово форматиране.
# 3 - Маркирайте всички празни клетки в диапазона
Да приемем по-долу данните, с които разполагате.
В горните данни трябва да маркирате всички клетки, които са празни или празни. Трябва да използваме формулата ISBLANK в условното форматиране на Excel.
Приложете формулата по-долу в раздела за формули.
Изберете форматирането според вашето желание.
Щракнете върху ok, ще маркира всички празни клетки в избрания диапазон.
# 4 - Използвайте И Функция за подчертаване на клетки
Помислете за данните по-долу за тази формула.
Ако регионът е централен и стойността на продажбите е> 500, трябва да маркираме целия ред.
- Стъпка 1: Изберете първо всички данни.
- Стъпка 2: Тук трябва да използваме функцията AND excel, защото тук трябва да тестваме две условия и двете условия трябва да са ИСТИНСКИ. Приложете формулата по-долу.
- Стъпка 3: Щракнете върху Формат.
- Стъпка 4: Отидете на FILL и изберете необходимия цвят и ефекти.
- Стъпка 5: Щракнете, OK. Ще видим маркираните редове, ако и двете условия са ИСТИНСКИ.
# 5- Използвайте ИЛИ функцията, за да подчертаете клетките
Да разгледаме данните по-долу за пример.
В горния диапазон от данни искам да подчертая имената на градовете Бангалор и Майсур.
- Стъпка 1: Първо изберете данните и отидете на Условно форматиране, след което кликнете върху Ново правило.
- Стъпка 2: Тъй като трябва да маркираме някоя от двете клетки със стойности, приложете функцията OR excel.
- Стъпка 3: Кликнете върху формата и изберете необходимия формат.
- Стъпка 4: След като форматирането е приложено, щракнете върху OK, за да завършите задачата. Формула ще подчертае всички клетки, които имат стойности Bangalore & Mysore.
# 6 - Използвайте функцията COUNTIF, за да подчертаете клетките
Да приемем, че работите с базата данни с клиенти. Трябва да идентифицирате всички клиенти, чиито имена се появяват повече от пет пъти в списъка. По-долу са данните.
Изберете данните и приложете формулата COUNTIF в раздела за формули.
Кликнете върху формата и приложете необходимото форматиране.
Сега формулата ще подчертае всички имена, ако броят е повече от 5.
Амбър е единственото име, което се появява повече от пет пъти в списъка.
# 7 - Маркирайте всеки алтернативен ред
Можем също така да имаме формулата, за да подчертаем всеки алтернативен ред в Excel на данните. Да приемем по-долу данните, по които работите.
Изберете данните и приложете формулата по-долу.
= MOD (РЯД (), 2)
Кандидатствайте за форматиране по ваше желание и кликнете върху OK; ще имаме всеки алтернативен ред, подчертан от формулата.
# 8 - Маркирайте всяка алтернативна колона
Подобно на това как сме маркирали всеки алтернативен ред по подобен начин, можем да подчертаем всяка алтернативна колона. Помислете за същите данни за примера.
Приложете формулата по-долу.
= MOD (КОЛОНА (), 2)
Копирайте горната формула и я приложете в раздела за формули на условното форматиране.
Щракнете върху OK; ще подчертае алтернативния ред.
Ако искате да оставите първата колона и да маркирате от втората колона, трябва да използвате формулата по-долу.
Това ще подчертае алтернативната колона, започвайки от втората колона.
Неща за запомняне
- Условното форматиране приема само логически формули, чиито резултати са само TRUE или FALSE.
- Предварителен преглед на условното форматиране е само индикация за това как изглежда форматирането.
- Никога не използвайте абсолютна референция, както е във формулата. Когато прилагате формулата и ако изберете клетката директно, тя ще я направи като абсолютна препратка, трябва да я направите към относителна препратка в Excel.
