Условно форматиране в Excel: Най -доброто ръководство с примери

Условното форматиране е една от най -простите, но мощни функции в електронните таблици на Excel.

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

Той ви дава възможност бързо да добавите слой за визуален анализ към вашия набор от данни. Можете да създавате топлинни карти, да показвате увеличаващи се/намаляващи икони, мехурчета Харви и много други, като използвате условно форматиране в Excel.

Използване на условно форматиране в Excel (примери)

В този урок ще ви покажа седем невероятни примера за използване на условно форматиране в Excel:

• Бързо идентифициране на дубликати с помощта на условно форматиране в Excel.
• Маркирайте клетки с стойност по -голяма/по -малка от число в набор от данни.
• Осветяване на горните/долните 10 (или 10%) стойности в набор от данни.
• Осветяване на грешки/празни места с помощта на условно форматиране в Excel.
• Създаване на топлинни карти с помощта на условно форматиране в Excel.
• Маркирайте всеки N -ти ред/колона, като използвате условно форматиране.
• Търсете и маркирайте с помощта на условно форматиране в Excel.

1. Бързо идентифициране на дубликати

Условното форматиране в Excel може да се използва за идентифициране на дубликати в набор от данни.

Ето как можете да направите това:

• Изберете набора от данни, в който искате да маркирате дубликати.
• Отидете на Начало -> Условно форматиране -> Маркиране на клетъчни правила -> Дублирани стойности.
• В диалоговия прозорец Дублирани стойности се уверете, че в лявото падащо меню е избрано Duplicate. Можете да посочите формата, който да се прилага, като използвате дясното падащо меню. Има някои съществуващи формати, които можете да използвате или да посочите свой собствен формат, като използвате опцията Персонализиран формат.
• Щракнете върху OK.

Това незабавно ще подчертае всички клетки, които имат дубликат в избрания набор от данни. Вашият набор от данни може да бъде в една колона, няколко колони или в несвързан диапазон от клетки.

Вижте също: Най -доброто ръководство за намиране и премахване на дубликати в Excel.

2. Маркирайте клетки с стойност по -голяма/по -малка от число

Можете да използвате условно форматиране в Excel, за да маркирате бързо клетки, които съдържат стойности, по -големи/по -малки от определена стойност. Например, маркиране на всички клетки със стойност на продажбите по -малка от 100 милиона или подчертаване на клетки с маркировки по -малки от прага за преминаване.

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

• Изберете целия набор от данни.
• Отидете на Начало -> Условно форматиране -> Открояване на клетъчни правила -> По -голямо от … / По -малко от …
• Въз основа на избраната от вас опция (по -голяма или по -малка от) ще се отвори диалогов прозорец. Да речем, вие избирате опцията „По -голямо от“. В диалоговия прозорец въведете номера в полето отляво. Намерението е да се подчертаят клетки, които имат номер, по -голям от този посочен номер.

• Посочете формата, който да се прилага към клетките, които отговарят на условието, като използвате падащото меню вдясно. Има някои съществуващи формати, които можете да използвате или да посочите свой собствен формат, като използвате опцията Персонализиран формат.
• Щракнете върху OK.

Това незабавно ще подчертае всички клетки със стойности, по -големи от 5 в набор от данни.Забележка: Ако искате да подчертаете стойности, по -големи от 5, трябва да приложите условно форматиране отново с критерия „равно на“.

Същият процес може да бъде последван, за да се подчертаят клетки със стойност, по -малка от посочени стойности.

3. Открояване отгоре/отдолу 10 (или 10%)

Условното форматиране в Excel може бързо да идентифицира топ 10 елемента или топ 10% от набор от данни. Това може да бъде полезно в ситуации, в които искате бързо да видите най -добрите кандидати по резултати или стойности на най -добрите сделки в данните за продажбите.

По същия начин можете също бързо да идентифицирате долните 10 елемента или най -долните 10% в набор от данни.

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

• Изберете целия набор от данни.
• Отидете на Начало -> Условно форматиране -> Горни / Долни правила -> Топ 10 елемента (или %) / Долни 10 елемента (или %).
• Въз основа на това, което изберете, ще се отвори диалоговият прозорец. Да предположим, че сте избрали Топ 10 елемента, тогава той ще отвори диалогов прозорец, както е показано по -долу:
• Посочете формата, който да се прилага към клетките, които отговарят на условието, като използвате падащото меню вдясно. Има някои съществуващи формати, които можете да използвате или да посочите свой собствен формат, като използвате опцията Персонализиран формат.
• Щракнете върху OK.

Това незабавно ще подчертае първите 10 елемента в избрания набор от данни. Имайте предвид, че това работи само за клетки, които имат цифрова стойност в него.

Също така, ако имате по -малко от 10 клетки в набора от данни и изберете опциите, за да маркирате Топ 10 елемента/Долни 10 елемента, тогава всички клетки ще бъдат маркирани.

Ето няколко примера за това как би работило условното форматиране:

4. Осветяване на грешки/празни места

Ако работите с много числени данни и изчисления в Excel, ще знаете значението на идентифицирането и третирането на клетки, които имат грешки или са празни. Ако тези клетки се използват при по -нататъшни изчисления, това може да доведе до погрешни резултати.

Условното форматиране в Excel може да ви помогне бързо да идентифицирате и маркирате клетки, които имат грешки или са празни.

Да предположим, че имаме набор от данни, както е показано по -долу:

Този набор от данни има празна клетка (A4) и грешки (A5 и A6).

Ето стъпки за подчертаване на клетките, които са празни или имат грешки в тях:

• Изберете набора от данни, в който искате да маркирате празни клетки и клетки с грешки.
• Отидете на Начало -> Условно форматиране -> Ново правило.
• В диалоговия прозорец Ново правило за форматиране изберете Използвайте формула, за да определите кои клетки да форматирате.
• Въведете следната формула в полето в секцията „Редактиране на описанието на правилото“:
  = ИЛИ (ISBLANK (A1), ISERROR (A1))
  • Горната формула проверява всички клетки за две условия - дали е празно или не и дали има грешка или не. Ако някое от условията е TRUE, връща TRUE.
• Задайте формата, който искате да приложите към клетките, които са празни или имат грешки. За да направите това, кликнете върху бутона Форматиране. Ще се отвори диалоговият прозорец „Форматиране на клетки“, където можете да посочите формата.
• Щракнете върху OK.

Това незабавно ще подчертае всички клетки, които са празни или имат грешки в него.

Забележка: Не е необходимо да използвате целия диапазон A1: A7 във формулата при условно форматиране. Горепосочената формула използва само A1. Когато прилагате тази формула към целия диапазон, Excel проверява една по една клетка и коригира препратката. Например, когато проверява A1, той използва формулата = OR (ISBLANK (A1), ISERROR (A1)). Когато проверява клетка A2, тя използва формулата = ИЛИ (ISBLANK (A2), ISERROR (A2)). Той автоматично регулира препратката (тъй като това са относителни препратки) в зависимост от това коя клетка се анализира. Така че не е нужно да пишете отделна формула за всяка клетка. Excel е достатъчно умен, за да променя референтната клетка сама по себе си 🙂

Вижте също: Използване на IFERROR и ISERROR за обработка на грешки в Excel.

5. Създаване на топлинни карти

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

Нещо, както е показано по -долу:

Горният набор от данни има стойности между 1 и 100. Клетките се подчертават въз основа на стойността в него. 100 получава зелен цвят, 1 получава червен цвят.

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

• Изберете набора от данни.
• Отидете на Начало -> Условно форматиране -> Цветови скали и изберете една от цветовите схеми.

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

Забележка: По подобен начин можете също да приложите Data Bard и Icon.

6. Маркирайте всеки друг ред/колона

Може да искате да маркирате алтернативни редове, за да увеличите четливостта на данните.

Те се наричат ​​линии на зебра и могат да бъдат особено полезни, ако отпечатвате данните.

Сега има два начина за създаване на тези линии на зебра. Най -бързият начин е да преобразувате вашите таблични данни в таблица на Excel. Той автоматично прилага цвят към редуващи се редове. Можете да прочетете повече за това тук.

Друг начин е използването на условно форматиране.

Да предположим, че имате набор от данни, както е показано по -долу:

Ето стъпките за подчертаване на алтернативни редове с помощта на условно форматиране в Excel.

• Изберете набора от данни. В горния пример изберете A2: C13 (което изключва заглавката). Ако искате да включите и заглавката, изберете целия набор от данни.
• Отворете диалоговия прозорец Условно форматиране (Начало-> Условно форматиране-> Ново правило) [Клавишна комбинация - Alt + O + D].
• В диалоговия прозорец изберете „Използвайте формула, за да определите кои клетки да форматирате“.
• Въведете следната формула в полето в секцията „Редактиране на описанието на правилото“:
  = ISODD (ROW ())

• Горната формула проверява всички клетки и ако ROW номерът на клетка е нечетен, той връща TRUE. Посоченият условен формат ще бъде приложен към всички клетки, които връщат TRUE.
• Задайте формата, който искате да приложите към клетките, които са празни или имат грешки. За да направите това, щракнете върху бутона Форматиране. Ще се отвори диалоговият прозорец „Форматиране на клетки“, където можете да посочите формата.
• Щракнете върху OK.

Това е! Алтернативните редове в набора от данни ще бъдат маркирани.

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

• Маркирайте алтернативни четни редове: = ISEVEN (ROW ())
• Маркирайте алтернативни добавящи редове: = ISODD (ROW ())
• Маркирайте всеки 3 -ти ред: = MOD (ROW (), 3) = 0

7. Търсете и маркирайте данни, използвайки условно форматиране

Това е малко напреднало използване на условно форматиране. Това би ви накарало да изглеждате като рокзвезда на Excel.

Да предположим, че имате набор от данни, както е показано по -долу, с име на продукта, търговски представител и география. Идеята е да въведете низ в клетка C2 и ако той съвпада с данните във всяка клетка (и), това трябва да бъде подчертано. Нещо, както е показано по -долу:

Ето стъпките за създаване на тази функция за търсене и подчертаване:

• Изберете набора от данни.
• Отидете на Начало -> Условно форматиране -> Ново правило (Клавишна комбинация - Alt + O + D).
• В диалоговия прозорец Ново правило за форматиране изберете опцията „Използвайте формула, за да определите кои клетки да форматирате“.
• Въведете следната формула в полето в секцията „Редактиране на описанието на правилото“:
  = И ($ C $ 2 ””, $ C $ 2 = B5)
• Задайте формата, който искате да приложите към клетките, които са празни или имат грешки. За да направите това, кликнете върху бутона Форматиране. Ще се отвори диалоговият прозорец „Форматиране на клетки“, където можете да посочите формата.
• Щракнете върху OK.

Това е! Сега, когато въведете нещо в клетка C2 и натиснете enter, той ще маркира всички съвпадащи клетки.

Как работи това?

Формулата, използвана при условно форматиране, оценява всички клетки в набора от данни. Да предположим, че влизате в Япония в клетка С2. Сега Excel ще оцени формулата за всяка клетка.

Формулата ще върне TRUE за клетка, когато са изпълнени две условия:

• Клетка C2 не е празна.
• Съдържанието на клетка C2 съвпада точно със съдържанието на клетката в набора от данни.

Следователно всички клетки, които съдържат текста на Япония, се маркират.

Изтеглете примерния файл

Можете да използвате същата логика, за да създадете вариации като:

• Маркирайте целия ред вместо клетка.
• Маркирайте дори когато има частично съвпадение.
• Маркирайте клетките/редовете, докато пишете (динамично) [Ще ви хареса този трик :)].

Как да премахнете условното форматиране в Excel

След като се приложи, условното форматиране остава на място, освен ако не го премахнете ръчно. Като най -добра практика, поддържайте условното форматиране да се прилага само към онези клетки, където имате нужда от него.

Тъй като е нестабилен, това може да доведе до бавна работна книга на Excel.

За да премахнете условното форматиране:

• Изберете клетките, от които искате да премахнете условното форматиране.
• Отидете на Начало -> Условно форматиране -> Изчистване на правила -> Изчистване на правила от избрани клетки.
  • Ако искате да премахнете условното форматиране от целия работен лист, изберете Изчистване на правилата от целия лист.

Важни неща, които трябва да знаете за условното форматиране в Excel

• Условно форматиране в променливо състояние. Това може да доведе до бавна работна книга. Използвайте го само когато е необходимо.
• Когато копирате клетки за поставяне, които съдържат условно форматиране, условното форматиране също се копира.
• Ако приложите няколко правила към един и същ набор от клетки, всички правила остават активни. В случай на припокриване се дава предимство на правилото, приложено последно. Можете обаче да промените реда, като промените реда от диалоговия прозорец Управление на правилата.