Условное форматирование ячеек с ошибками

Основы

Все очень просто. Хотим, чтобы ячейка меняла свой цвет (заливка, шрифт, жирный-курсив, рамки и т.д.) если выполняется определенное условие. Отрицательный баланс заливать красным, а положительный — зеленым. Крупных клиентов делать полужирным синим шрифтом, а мелких — серым курсивом. Просроченные заказы выделять красным, а доставленные вовремя — зеленым. И так далее — насколько фантазии хватит. 

Чтобы сделать подобное, выделите ячейки, которые должны автоматически менять свой цвет, и выберите в меню Формат — Условное форматирование (Format — Conditional formatting).

В открывшемся окне можно задать условия и, нажав затем кнопку Формат (Format), параметры форматирования ячейки, если условие выполняется. В этом примере отличники и хорошисты заливаются зеленым, троечники — желтым, а неуспевающие — красным цветом:

Кнопка А также>> (Add) позволяет добавить дополнительные условия. В Excel 2003 их количество ограничено тремя, в Excel 2007 и более новых версиях — бесконечно.

Если вы задали для диапазона ячеек критерии условного форматирования, то больше не сможете отформатировать эти ячейки вручную. Чтобы вернуть себе эту возможность надо удалить условия при помощи кнопки Удалить (Delete) в нижней части окна.

Другой, гораздо более мощный и красивый вариант применения условного форматирования — это возможность проверять не значение выделенных ячеек, а заданную формулу: 

Если заданная формула верна (возвращает значение ИСТИНА), то срабатывает нужный формат. В этом случае можно задавать на порядок более сложные проверки с использованием функций и, кроме того, проверять одни ячейки, а форматировать — другие. 

Выделение цветом всей строки

Главный нюанс заключается в знаке доллара ($) перед буквой столбца в адресе — он фиксирует столбец, оставляя незафиксированной ссылку на строку — проверяемые значения берутся из столбца С, по очереди из каждой последующей строки:

Выделение максимальных и минимальных значений

Ну, здесь все достаточно очевидно — проверяем, равно ли значение ячейки максимальному или минимальному по диапазону — и заливаем соответствующим цветом:

В англоязычной версии это функции MIN и MAX, соответственно.

Выделение всех значений больше(меньше) среднего

Аналогично предыдущему примеру, но используется функция СРЗНАЧ (AVERAGE) для вычисления среднего:

Скрытие ячеек с ошибками

Чтобы скрыть ячейки, где образуется ошибка, можно использовать условное форматирование, чтобы сделать цвет шрифта в ячейке белым (цвет фона ячейки) и функцию ЕОШ (ISERROR), которая выдает значения ИСТИНА или ЛОЖЬ в зависимости от того, содержит данная ячейка ошибку или нет:

Скрытие данных при печати

Аналогично предыдущему примеру можно использовать условное форматирование, чтобы скрывать содержимое некоторых ячеек, например, при печати — делать цвет шрифта белым, если содержимое определенной ячейки имеет заданное значение («да», «нет»):

Заливка недопустимых значений

Сочетая условное форматирование с функцией СЧЁТЕСЛИ (COUNTIF), которая выдает количество найденных значений в диапазоне, можно подсвечивать, например, ячейки с недопустимыми или нежелательными значениями:

Проверка дат и сроков

Поскольку даты в Excel представляют собой те же числа (один день = 1), то можно легко использовать условное форматирование для проверки сроков выполнения задач. Например, для выделения просроченных элементов красным, а тех, что предстоят в ближайшую неделю — желтым:

P.S.

Счастливые обладатели последних версий Excel 2007-2010 получили в свое распоряжение гораздо более мощные средства условного форматирования — заливку ячеек цветовыми градиентами, миниграфики и значки:

Вот такое форматирование для таблицы сделано, буквально, за пару-тройку щелчков мышью…

Ссылки по теме

• Выделение дубликатов в списке цветом
• Сравнение двух списков и выделение цветом совпадающих элементов.
• Создание графиков проектов (дежурств, отпусков и т.д.) с помощью условного форматирования

Хитрости »

27 Август 2013              331941 просмотров

Сборник формул для условного форматирования

В данной статье собран список формул, которые можно использовать в условном форматировании ячеек, заданным при помощи формулы:

• Excel 2003: Формат(Format)—Условное форматирование(Conditional formatting)— формула;
• Excel 2007-2010: вкладка Главная(Home)—Условное форматирование(Conditional formatting)—Создать правило(New rule)—Использовать формулу для определения форматируемых ячеек(Use a formula to determine which cells to format)

Подробнее об условном форматировании можно прочитать в статье: Основные понятия условного форматирования и как его создать

Все условия приведены для диапазона A1:A20. Это означает, что для корректного выполнения условия необходимо выделить диапазон A1:A20(столбцов может быть больше), начиная с ячейки A1, после чего назначить условие.
Если выделять необходимо не с первой строки, а скажем, с 4-ой, то и выделить надо будет диапазон A4:A20 и в формуле для условия указывать в качестве критерия первую ячейку выделенного диапазона — A4.

Если необходимо выделять форматированием не только конкретную ячейку, удовлетворяющую условию, а всю строку таблицы на основе ячейки одного столбца, то перед установкой правила необходимо выделить всю таблицу, строки которой необходимо форматировать, а ссылку на столбец с критерием закрепить:
=$A1=МАКС($A$1:$A$20)
при выделенном диапазоне A1:F20(диапазон применения условного форматирования), будет выделена строка A7:F7, если в ячейке A7 будет максимальное число.

Так же можно применять не к конкретно одному столбцу, а к полностью диапазону. Но в этом случае надо знать принцип смещения ссылок в формулах, чтобы условия применялись именно к нужным ячейкам. Например, если задать условие для диапазона B1:D10 в виде формулы: =B1<A1, то цветом будут выделены ячейки столбца B, если значение ячейки столбца А в той же строке меньше(B1<A1, B3<A3). При этом если ячейки столбца D меньше ячеек столбца C в той же строке — они тоже будут выделены(D1<C1, D5<C5).

ЧИСЛОВЫЕ ЗНАЧЕНИЯ
1. Выделение ячеек с числами:
   =ЕЧИСЛО(A1)
2. Выделение ячеек с числами, но не учитывая нули:
   =И(ЕЧИСЛО(A1);A1<>0)
3. Выделение строк со значением больше 0:
   =A1>0
4. Выделение строк со значением в диапазоне от 3 до 10:
   =И(A1>=3;A1<=10)
5. Выделение в диапазоне $A$1:$A$20 ячейки с максимальным значением:
   =A1=МАКС($A$1:$A$20)
6. Выделение в диапазоне $A$1:$A$20 ячейки с минимальным значением:
   =И(ЕЧИСЛО(A1);A1=МИН($A$1:$A$20))
7. Выделение в диапазоне $A$1:$A$20 ячейки со вторым по величине числом. Т.е. из чисел 1,2,3,4,5,6,7 будет выделено число 6:
   =A1=НАИБОЛЬШИЙ($A$1:$A$20;2)

 
ТЕКСТОВЫЕ ЗНАЧЕНИЯ

8. Выделение ячеек с любым текстом:
   =ЕТЕКСТ(A1)
9. Выделение ячеек с текстом Итог:
   =A1=»Итог»
10. Выделение ячеек, содержащих текст Итог:
    =СЧЁТЕСЛИ(A1;»*итог*»)
    =НЕ(ЕОШ(ПОИСК(«итог»;A1)))
11. Выделение ячеек, не содержащих текст Итог:
    =СЧЁТЕСЛИ(A1;»*итог*»)=0
    =ЕОШ(ПОИСК(«итог»;A1))
12. Выделение ячеек, текст которых начинается со слова Итог:
    =ЛЕВСИМВ(A1;4)=»Итог»
13. Выделение ячеек, текст которых заканчивается на слово Итог:
    =ПРАВСИМВ(A1;4)=»Итог»

 
ДАТА / ВРЕМЯ

14. Выделение текущей даты:
    =A1=СЕГОДНЯ()
15. Выделение ячейки с датой, больше текущей:
    =A1>СЕГОДНЯ()
16. Выделение ячейки с датой, которая наступит через неделю:
    =A1=СЕГОДНЯ()+7
17. Выделение ячеек с датами текущего месяца(любого года):
    =МЕСЯЦ(A1)=МЕСЯЦ(СЕГОДНЯ())
18. Выделение ячеек с датами текущего месяца текущего года:
    =И(МЕСЯЦ(A1)=МЕСЯЦ(СЕГОДНЯ());ГОД(A1)=ГОД(СЕГОДНЯ()))
    или
    =ТЕКСТ(A1;»ГГГГММ»)=ТЕКСТ(СЕГОДНЯ();»ГГГГММ»)
19. Выделение ячеек с выходными днями:
    =ДЕНЬНЕД(A1;2)>5
20. Выделение ячеек с будними днями:
    =ДЕНЬНЕД(A1;2)<6
21. Выделение ячеек, входящих в указанный период(промежуток) дат:
    =И($A1>ДАТА(2015;9;1);$A1<ДАТА(2015;10;1))

 
ДРУГИЕ

22. Выделение различий в ячейках по условию:
    =A1<>$B1
23. Выделение ячейки, если ячейка следующего столбца(B) этой же строки меньше:
    =A1>B1
24. Выделение строк цветом через одну:
    =ОСТАТ(СТРОКА();2)
25. Выделение строк цветом, если значение ячейки столбца A присутствует в диапазоне $F$1:$H$5000:
    =СЧЁТЕСЛИ($F$1:$H$5000;A1)
26. Выделение строк цветом, если значение ячейки столбца A отсутствует в диапазоне $F$1:$H$5000:
    =СЧЁТЕСЛИ($F$1:$H$5000;A1)=0
27. Выделение цветом ячейки, если её значение в диапазоне A1:A20 второе по счету:
    =СЧЁТЕСЛИ($A$1:$A1;A1 )=2
28. Выделение ячеек, содержащих ошибки (#ЗНАЧ!; #Н/Д; #ССЫЛКА! и т.п.). Помимо просто выявления ячеек с ошибками можно применять, когда необходимо скрыть ошибочные значения в ячейках(назначив цвет шрифта таким же, как и цвет заливки):
    =ЕОШИБКА(A)
29. Выделение непустых ячеек в столбце A:
    =$A1<>»»

Статья помогла? Поделись ссылкой с друзьями!

    Видеоуроки

Поиск по меткам



Access
apple watch
Multex
Power Query и Power BI
VBA управление кодами
Бесплатные надстройки
Дата и время
Записки
ИП
Надстройки
Печать
Политика Конфиденциальности
Почта
Программы
Работа с приложениями
Разработка приложений
Росстат
Тренинги и вебинары
Финансовые
Форматирование
Функции Excel
акции MulTEx
ссылки
статистика

Используем

для подачи сигнала пользователю при вводе ошибочного значения. Под «ошибочным значением» здесь будем понимать значение, не принадлежащее заданному диапазону.

Предположим, что на листе EXCEL производятся некие вычисления. В качестве исходных данных пользователь должен ввести числовой параметр из определенного интервала (например, от 1 до 5). При вводе числа, не принадлежащего этому интервалу, подадим сигнал пользователю с помощью

Условного форматирования

(ячейка будет выделена красным фоном).

Для этого:

• выделите ячейку, в которую пользователем будет вводиться исходный параметр;
• вызовите инструмент

  Условное форматирование

  (

  );

• выберите
  
  Форматировать только ячейки, которые содержат;
  
• в полях раздела
  
  Форматировать только ячейки, для которых выполняется следующее условие:
  
  выберите
  
  значение ячейки
  
  ;
  
  вне
  
  ;
  
  и введите:
  
  1
  
  ;
  
  5
  
  ;
  
• выберите требуемый формат, например, красный цвет фона.

Теперь при вводе значения вне интервала [1, 5] ячейка будет выделена красным (см.

Файл примера

).

Также контроль ввода неверных данных можно организовать с помощью формул (см. статью

Контроль ввода ОШИБочных значений. Часть 3 — с применением формул

) или

Проверки данных

(см. статью

Контроль ввода ОШИБочных значений. Часть 2 — с применением Проверки данных

). В отличие от

Условного форматирования

и формул, которые лишь информируют о допущенных ошибках ввода, инструмент

Проверка данных

позволяет запретить сам ввод ошибочных значений.

СОВЕТ:

Чтобы найти все ячейки на листе, к которым применены правила

Условного форматирования

необходимо:

• на вкладке
  
  Главная
  
  в группе
  
  Редактирование
  
  щелкните стрелку рядом с командой
  
  Найти и выделить
  
  ;
• выберите в списке пункт
  
  Условное форматирование;
  
• будут выделены все ячейки, которым применены правила

  Условного форматирования

  .