Маркиране на редове въз основа на стойност на клетката в Excel (условно форматиране)

Гледайте видео - Маркирайте редове въз основа на стойностите на клетките в Excel

В случай, че предпочитате да четете писмени инструкции, по -долу е урокът.

Условното форматиране ви позволява да форматирате клетка (или диапазон от клетки) въз основа на стойността в нея.

Но понякога, вместо просто да маркирате клетката, може да искате да маркирате целия ред (или колона) въз основа на стойността в една клетка.

За да ви дам пример, по -долу имам набор от данни, където съм подчертал всички редове, където името на търговския представител е Боб.

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

Натисни тук за да изтеглите файла с пример и следвайте.

Маркирайте редовете въз основа на текстови критерии

Да предположим, че имате набор от данни, както е показано по -долу и искате да подчертаете всички записи, където името на търговския представител е Боб.

Ето стъпките за това:

1. Изберете целия набор от данни (A2: F17 в този пример).
2. Щракнете върху раздела Начало.
3. В групата Стили щракнете върху Условно форматиране.
4. Кликнете върху „Нови правила“.
5. В диалоговия прозорец „Ново правило за форматиране“ щракнете върху „Използвайте формула, за да определите кои клетки да форматирате“.
6. В полето за формула въведете следната формула: = $ C2 = "Боб"
7. Щракнете върху бутона „Форматиране“.
8. В диалоговия прозорец, който се отваря, задайте цвета, в който искате редът да бъде подчертан.
9. Щракнете върху OK.

Това ще подчертае всички редове, където името на търговския представител е „Боб“.

Натисни тук за да изтеглите файла с пример и следвайте.

Как работи?

Условното форматиране проверява всяка клетка за условието, което сме посочили, което е = $ C2 = "Боб"

Така че, когато анализира всяка клетка в ред A2, тя ще провери дали клетката C2 има името Боб или не. Ако това стане, тази клетка се маркира, в противен случай не.

Обърнете внимание, че трикът тук е да използвате знак за долар ($) преди азбуката на колоната ($ C1). По този начин ние сме заключили колоната винаги да бъде C. Така че дори когато клетка A2 се проверява за формулата, тя ще провери C2, а когато A3 се провери за условието, ще провери C3.

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

Маркирайте редовете въз основа на критерии за брой

В горния пример видяхме как да проверим за име и да маркираме целия ред.

Можем да използваме същия метод, за да проверим и за числови стойности и да подчертаем редове въз основа на условие.

Да предположим, че имам същите данни (както е показано по -долу) и искам да подчертая всички редове, където количеството е повече от 15.

Ето стъпките за това:

1. Изберете целия набор от данни (A2: F17 в този пример).
2. Щракнете върху раздела Начало.
3. В групата Стили щракнете върху Условно форматиране.
4. Кликнете върху „Нови правила“.
5. В диалоговия прозорец „Ново правило за форматиране“ щракнете върху „Използвайте формула, за да определите кои клетки да форматирате“.
6. В полето за формула въведете следната формула: = $ D2> = 15
7. Щракнете върху бутона „Форматиране“. В диалоговия прозорец, който се отваря, задайте цвета, в който искате редът да бъде подчертан.
8. Щракнете върху OK.

Това ще подчертае всички редове, където количеството е повече или равно на 15.

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

Например, ако искате да маркирате всички редове, където датата е след 10 юли 2021-2022 г., можете да използвате формулата за дата по-долу:

= $ A2> ДАТА (2018,7,10)

Открояване на редове въз основа на множество критерии (И/ИЛИ)

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

Например, ако искате да маркирате всички редове, където името на търговския представител е „Bob“ и количеството е повече от 10, можете да направите това, като използвате следните стъпки:

1. Изберете целия набор от данни (A2: F17 в този пример).
2. Щракнете върху раздела Начало.
3. В групата Стили щракнете върху Условно форматиране.
4. Кликнете върху „Нови правила“.
5. В диалоговия прозорец „Ново правило за форматиране“ щракнете върху „Използвайте формула, за да определите кои клетки да форматирате“.
6. В полето за формула въведете следната формула: = И ($ C2 = ”Боб”, $ D2> 10)
7. Щракнете върху бутона „Форматиране“. В диалоговия прозорец, който се отваря, задайте цвета, в който искате редът да бъде подчертан.
8. Щракнете върху OK.

В този пример само тези редове се подчертават, когато и двете условия са изпълнени (това се прави с помощта на формулата AND).

По същия начин можете да използвате и условието OR. Например, ако искате да маркирате редове, където или търговският представител е Боб, или количеството е повече от 15, можете да използвате формулата по -долу:

= ИЛИ ($ C2 = "Боб", $ D2> 15)

Натисни тук за да изтеглите файла с пример и следвайте.

Маркирайте редовете в различен цвят въз основа на множество условия

Понякога може да искате да маркирате редове в цвят въз основа на условието.

Например, може да искате да маркирате всички редове, където количеството е повече от 20 в зелено и където количеството е повече от 15 (но по -малко от 20) в оранжево.

За да направите това, трябва да създадете две правила за условно форматиране и да зададете приоритета.

Ето стъпките за това:

1. Изберете целия набор от данни (A2: F17 в този пример).
2. Щракнете върху раздела Начало.
3. В групата Стили щракнете върху Условно форматиране.
4. Кликнете върху „Нови правила“.
5. В диалоговия прозорец „Ново правило за форматиране“ щракнете върху „Използвайте формула, за да определите кои клетки да форматирате“.
6. В полето за формула въведете следната формула: = $ D2> 15
7. Щракнете върху бутона „Форматиране“. В диалоговия прозорец, който се отваря, задайте цвета на Orange.
8. Щракнете върху OK.
9. В диалоговия прозорец „Мениджър на правила за условно форматиране“ щракнете върху „Ново правило“.
10. В диалоговия прозорец „Ново правило за форматиране“ щракнете върху „Използвайте формула, за да определите кои клетки да форматирате“.
11. В полето за формула въведете следната формула: = $ D2> 20
12. Щракнете върху бутона „Форматиране“. В диалоговия прозорец, който се отваря, задайте цвета на Зелен.
13. Щракнете върху OK.
14. Щракнете върху Приложи (или OK).

Горните стъпки ще направят всички редове с количество повече от 20 в зелено и тези с повече от 15 (но по -малко от равно на 20 в оранжево).

Разбиране на реда на правилата:

Когато използвате множество условия, важно е да се уверите, че редът на условията е правилен.

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

Ако е обратното, всички редове ще бъдат оцветени само в оранжево.

Защо?

Тъй като ред, където количеството е повече от 20 (да речем 23), удовлетворява и нашите условия (= $ D2> 15 и = $ D2> 20). И тъй като оранжевото състояние е най -отгоре, то получава предпочитание.

Можете да промените реда на условията, като използвате бутоните Преместване нагоре/надолу.

Натисни тук за да изтеглите файла с пример и следвайте.

Маркирайте редовете, където всяка клетка е празна

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

Ето стъпките за това:

1. Изберете целия набор от данни (A2: F17 в този пример).
2. Щракнете върху раздела Начало.
3. В групата Стили щракнете върху Условно форматиране.
4. Кликнете върху „Нови правила“.
5. В диалоговия прозорец „Ново правило за форматиране“ щракнете върху „Използвайте формула, за да определите кои клетки да форматирате“.
6. В полето за формула въведете следната формула: = COUNTIF ($ A2: $ F2, ””)> 0
7. Щракнете върху бутона „Форматиране“. В диалоговия прозорец, който се отваря, задайте цвета на Orange.
8. Щракнете върху OK.

Горната формула брои броя на празни клетки. Ако резултатът е повече от 0, това означава, че в този ред има празни клетки.

Ако някоя от клетките е празна, тя маркира целия ред.

Маркирайте редовете въз основа на падащото меню

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

В тази част на урока ще ви покажа как да го направите динамичен (така че да можете да въведете условието в клетка в Excel и тя автоматично да маркира редовете въз основа на него).

По-долу е даден пример, при който избирам име от падащото меню и всички редове с това име се осветяват:

Ето стъпките за създаване на това:

1. Създайте падащ списък в клетка А2. Тук използвах имената на търговския представител, за да създам падащия списък. Ето подробно ръководство за това как да създадете падащ списък в Excel.
2. Изберете целия набор от данни (C2: H17 в този пример).
3. Щракнете върху раздела Начало.
4. В групата Стили щракнете върху Условно форматиране.
5. Кликнете върху „Нови правила“.
6. В диалоговия прозорец „Ново правило за форматиране“ щракнете върху „Използвайте формула, за да определите кои клетки да форматирате“.
7. В полето за формула въведете следната формула: = $ E2 = $ A $ 2
8. Щракнете върху бутона „Форматиране“. В диалоговия прозорец, който се отваря, задайте цвета на Orange.
9. Щракнете върху OK.

Сега, когато изберете произволно име от падащото меню, той автоматично ще маркира редовете, където името е същото, което сте избрали от падащото меню.

Интересувате ли се да научите повече за това как да търсите и маркирате в Excel? Вижте видеоклиповете по -долу.