Excel условное форматирование если ошибка

Иногда требуется скрыть в ячейке значения ошибки: #ЗНАЧ!, #ССЫЛКА!, #ДЕЛ/0!, #ЧИСЛО!, #ИМЯ? Сделаем это несколькими способами.

Скрыть значения ошибки можно используя

пользовательский формат,

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

или значение

Пустой текст («»)

.

Пользовательский формат

Пользовательский формат вводим через диалоговое окно

Формат ячеек

(см.

файл примера

).

• для вызова окна
  
  Формат ячеек
  
  нажмите
  
  CTRL
  
  +
  
  1
  
  ;
• выберите (все форматы).
• в поле
  
  Тип
  
  введите формат
  
  [Черный]Основной
  
• нажмите ОК
• цвет шрифта ячейки установите таким же как и ее фон (обычно белый)

На рисунке ниже пользовательский формат применен к ячейкам

А6

и

B6

. Как видно, на значения, которые не являются ошибкой, форматирование не повлияло.

Применение вышеуказанного формата не влияет на вычисления. В

Строке формул

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

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

Используя

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

, также можно добиться такого же результата.

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

Теперь ошибочные значения отображаются белым шрифтом и не видны на стандартном белом фоне. Если фон поменять, то ошибки станут снова видны. Даже если просто выделить диапазон, то ошибки будут слегка видны.

Значение Пустой текст («»)

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

=»»

). Записав, например, в ячейке

А14

формулу

=ЕСЛИОШИБКА(1/0;»»)

получим пустую ячейку

А14

.

Хитрости »

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

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

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

• 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
ссылки
статистика

Основы

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

P.S.

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

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

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

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

В этом уроке мы рассмотрим основы применения условного форматирования в Excel.

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

Основы условного форматирования в Excel

Используя условное форматирование, мы можем:

• закрашивать значения цветом
• менять шрифт
• задавать формат границ

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

Где находится условное форматирование в Эксель?

Кнопка «Условное форматирование» находится на панели инструментов, на вкладке «Главная»:

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

При применении условного форматирования системе необходимо задать две настройки:

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

Ниже, мы рассмотрим как применить условное форматирование. Представим, что у нас есть таблица с динамикой курса доллара в рублях за год. Наша задача выделить красным цветом те данные, в которых курс снижался предыдущему месяцу. Итак, выполним следующие шаги:

• В таблице с данными выделим диапазон, для которого мы хотим применить выделение цветом:

• Перейдем на вкладку «Главная» на панели инструментов и кликнем на пункт «Условное форматирование». В выпадающем списке вы увидите несколько типов формата на выбор:
  • Правила выделения
  • Правила отбора первых и последних значений
  • Гистограммы
  • Цветовые шкалы
  • Наборы значков
• В нашем примере мы хотим выделить цветом данные с отрицательным значением. Для этого выберем тип «Правила выделения ячеек» => «Меньше»:

Также, доступны следующие условия:

1. Значения больше или равны какому-либо значению;
2. Выделять текст, содержащий определенные буквы или слова;
3. Выделять цветом дубликаты;
4. Выделять определенные даты.

• Во всплывающем окне в поле «Форматировать ячейки которые МЕНЬШЕ» укажем значение «0», так как нам нужно выделить цветом отрицательные значения. В выпадающем списке справа выберем формат отвечающих условиям:

• Для присвоения формата вы можете использовать пред настроенные цветовые палитры, а также создать свою палитру. Для этого кликните по пункту:

• Во всплывающем окне формата укажите:
  • цвет заливки
  • цвет шрифта
  • шрифт
  • границы ячеек

• По завершении настроек нажмите кнопку «ОК».

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

Как создать правило

Если пред настроенные условия не подходят, вы можете создавать свои правила. Для настройки проделаем следующие шаги:

• Выделим диапазон данных. Кликнем на пункт «Условное форматирование» в панели инструментов. В выпадающем списке выберем пункт «Новое правило»:

• Во всплывающем окне нам нужно выбрать тип применяемого правила. В нашем примере нам подойдет тип «Форматировать только ячейки, которые содержат». После этого зададим условие выделять данные, значения которых больше «57», но меньше «59»:

• Кликнем на кнопку «Формат» и зададим формат, как мы это делали в примере выше. Нажмите кнопку «ОК»:

Условное форматирование по значению другой ячейки

На примерах выше мы задавали формат ячейкам, на основе их собственных значений. В Excel возможно задавать формат, на основе значений из других ячеек. Например, в таблице с данными курса доллара мы можем выделить цветом ячейки по правилу. Если курс доллара ниже чем в предыдущем месяце, то значение курса в текущем месяце будет выделено цветом.

Для создания условия по значению другой ячейки выполним следующие шаги:

• Выделим первую ячейку для назначения правила. Кликнем на пункт «Условное форматирование» на панели инструментов. Выберем условие «Меньше».
• Во всплывающем окне указываем ссылку на ячейку, с которой будет сравниваться данная ячейка. Выбираем формат. Нажимаем кнопку «ОК».

• Повторно выделим левой клавишей мыши ячейку, которой мы присвоили формат. Кликнем на пункт «Условное форматирование». Выберем в выпадающем меню «Управление правилами» => кликнем на кнопку «Изменить правило»:

• В поле слева всплывающего окна «очистим» ссылку от знака «$». Нажимаем кнопку «ОК», а затем кнопку «Применить».

• Теперь нам нужно присвоить настроенный формат на остальные ячейки таблицы. Для этого выделим ячейку с присвоенным форматом, затем в левом верхнем углу панели инструментов нажмем на «валик» и присвоим формат остальным ячейкам:

На скриншоте ниже цветом выделены данные, в которых курс валюты стал ниже к предыдущему периоду:

Как применить несколько правил условного форматирования к одной ячейке

Возможно применять несколько правил к одной ячейке.

Например, в таблице с прогнозом погоды мы хотим закрасить разными цветами показатели температуры. Условия выделения цветом: если температура выше 10 градусов — зеленым цветом, если выше 20 градусов — желтый, если выше 30 градусов — красным.

Для применения нескольких условий к одной ячейке выполним следующие действия:

• Выделим диапазон с данными, к которым мы хотим применить условное форматирование => кликнем по пункту «Условное форматирование» на панели инструментов => выберем условие выделения «Больше…» и укажем первое условие (если больше 10, то зеленая заливка). Такие же действия повторим для каждого из условий (больше 20 и больше 30). Не смотря на то, что мы применили три правила, данные в таблице закрашены зеленым цветом:

• Кликнем на любую ячейку с присвоенным форматированием. Затем, снова кликнем по пункту «Условное форматирование» и перейдем в раздел «Управление правилами». Во всплывающем окне, распределим правила от большего к меньшему и напротив первых двух поставим галочку «Остановить, если истина». Этот пункт позволяет не применять остальные правила к ячейке, при соответствии первому. Затем кликнем кнопку «Применить» и «ОК»:

Применив их, наша таблица с данными температуры «подсвечена» корректными цветами, в соответствии с нашими условиями.

Как редактировать правило условного форматирования

Для редактирования присвоенного правила выполните следующие шаги:

• Выделить левой клавишей мыши ячейку, правило которой вы хотите отредактировать.
• Перейдите в пункт меню панели инструментов «Условное форматирование». Затем, в пункт «Управление правилами». Щелкните левой клавишей мыши по правилу, которое вы хотите отредактировать. Кликните на кнопку «Изменить правило»:

• После внесения изменений нажмите кнопку «ОК».

Как копировать правило условного форматирования

Для копирования формата на другие ячейки выполним следующие действия:

• Выделим диапазон данных с примененным условным форматированием. Кликнем по пункту на панели инструментов «Формат по образцу».
• Левой клавишей мыши выделим диапазон, к которому хотим применить скопированные правила формата:

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

Для удаления формата проделайте следующие действия:

• Выделите ячейки;
• Нажмите на пункт меню «Условное форматирование» на панели инструментов. Кликните по пункту «Удалить правила». В раскрывающемся меню выберите метод удаления:

Условное форматирование – удобный инструмент для анализа данных и наглядного представления результатов. Умение им пользоваться сэкономит массу времени и сил. Достаточно бегло взглянуть на документ – нужная информация получена.

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

Инструмент «Условное форматирование» находится на главной странице в разделе «Стили».

При нажатии на стрелочку справа открывается меню для условий форматирования.

Сравним числовые значения в диапазоне Excel с числовой константой. Чаще всего используются правила «больше / меньше / равно / между». Поэтому они вынесены в меню «Правила выделения ячеек».

Введем в диапазон А1:А11 ряд чисел:

Выделим диапазон значений. Открываем меню «Условного форматирования». Выбираем «Правила выделения ячеек». Зададим условие, например, «больше».

Введем в левое поле число 15. В правое – способ выделения значений, соответствующих заданному условию: «больше 15». Сразу виден результат:

Выходим из меню нажатием кнопки ОК.



Условное форматирование по значению другой ячейки

Сравним значения диапазона А1:А11 с числом в ячейке В2. Введем в нее цифру 20.

Выделяем исходный диапазон и открываем окно инструмента «Условное форматирование» (ниже сокращенно упоминается «УФ»). Для данного примера применим условие «меньше» («Правила выделения ячеек» — «Меньше»).

В левое поле вводим ссылку на ячейку В2 (щелкаем мышью по этой ячейке – ее имя появится автоматически). По умолчанию – абсолютную.

Результат форматирования сразу виден на листе Excel.

Значения диапазона А1:А11, которые меньше значения ячейки В2, залиты выбранным фоном.

Зададим условие форматирования: сравнить значения ячеек в разных диапазонах и показать одинаковые. Сравнивать будем столбец А1:А11 со столбцом В1:В11.

Выделим исходный диапазон (А1:А11). Нажмем «УФ» — «Правила выделения ячеек» — «Равно». В левом поле – ссылка на ячейку В1. Ссылка должна быть СМЕШАННАЯ или ОТНОСИТЕЛЬНАЯ!, а не абсолютная.

Каждое значение в столбце А программа сравнила с соответствующим значением в столбце В. Одинаковые значения выделены цветом.

Внимание! При использовании относительных ссылок нужно следить, какая ячейка была активна в момент вызова инструмента «Условного формата». Так как именно к активной ячейке «привязывается» ссылка в условии.

В нашем примере в момент вызова инструмента была активна ячейка А1. Ссылка $B1. Следовательно, Excel сравнивает значение ячейки А1 со значением В1. Если бы мы выделяли столбец не сверху вниз, а снизу вверх, то активной была бы ячейка А11. И программа сравнивала бы В1 с А11.

Сравните:

Чтобы инструмент «Условное форматирование» правильно выполнил задачу, следите за этим моментом.

Проверить правильность заданного условия можно следующим образом:

1. Выделите первую ячейку диапазона с условным форматированим.
2. Откройте меню инструмента, нажмите «Управление правилами».

В открывшемся окне видно, какое правило и к какому диапазону применяется.

Условное форматирование – несколько условий

Исходный диапазон – А1:А11. Необходимо выделить красным числа, которые больше 6. Зеленым – больше 10. Желтым – больше 20.

• 1 способ. Выделяем диапазон А1:А11. Применяем к нему «Условное форматирование». «Правила выделения ячеек» — «Больше». В левое поле вводим число 6. В правом – «красная заливка». ОК. Снова выделяем диапазон А1:А11. Задаем условие форматирования «больше 10», способ – «заливка зеленым». По такому же принципу «заливаем» желтым числа больше 20.



• 2 способ. В меню инструмента «Условное форматирование выбираем «Создать правило».

Заполняем параметры форматирования по первому условию:

Нажимаем ОК. Аналогично задаем второе и третье условие форматирования.

Обратите внимание: значения некоторых ячеек соответствуют одновременно двум и более условиям. Приоритет обработки зависит от порядка перечисления правил в «Диспетчере»-«Управление правилами».

То есть к числу 24, которое одновременно больше 6, 10 и 20, применяется условие «=$А1>20» (первое в списке).

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

Выделяем диапазон с датами.

Применим к нему «УФ» — «Дата».

В открывшемся окне появляется перечень доступных условий (правил):

Выбираем нужное (например, за последние 7 дней) и жмем ОК.

Красным цветом выделены ячейки с датами последней недели (дата написания статьи – 02.02.2016).

Условное форматирование в Excel с использованием формул

Если стандартных правил недостаточно, пользователь может применить формулу. Практически любую: возможности данного инструмента безграничны. Рассмотрим простой вариант.

Есть столбец с числами. Необходимо выделить цветом ячейки с четными. Используем формулу: =ОСТАТ($А1;2)=0.

Выделяем диапазон с числами – открываем меню «Условного форматирования». Выбираем «Создать правило». Нажимаем «Использовать формулу для определения форматируемых ячеек». Заполняем следующим образом:

Для закрытия окна и отображения результата – ОК.

Условное форматирование строки по значению ячейки

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

Таблица для примера:

Необходимо выделить красным цветом информацию по проекту, который находится еще в работе («Р»). Зеленым – завершен («З»).

Выделяем диапазон со значениями таблицы. Нажимаем «УФ» — «Создать правило». Тип правила – формула. Применим функцию ЕСЛИ.

Порядок заполнения условий для форматирования «завершенных проектов»:

Обратите внимание: ссылки на строку – абсолютные, на ячейку – смешанная («закрепили» только столбец).

Аналогично задаем правила форматирования для незавершенных проектов.

В «Диспетчере» условия выглядят так:

Получаем результат:

Когда заданы параметры форматирования для всего диапазона, условие будет выполняться одновременно с заполнением ячеек. К примеру, «завершим» проект Димитровой за 28.01 – поставим вместо «Р» «З».

«Раскраска» автоматически поменялась. Стандартными средствами Excel к таким результатам пришлось бы долго идти.

Смотря на сухие цифры таблиц, трудно с первого взгляда уловить общую картину, которую они представляют. Но, в программе Microsoft Excel имеется инструмент графической визуализации, с помощью которого можно наглядно представить данные, содержащиеся в таблицах. Это позволяет более легко и быстро усвоить информацию. Данный инструмент называется условным форматированием. Давайте разберемся, как использовать условное форматирование в программе Microsoft Excel.

Простейшие варианты условного форматирования

Для того, чтобы произвести форматирование определенной области ячеек, нужно выделить эту область (чаще всего столбец), и находясь во вкладке «Главная», кликнуть по кнопке «Условное форматирование», которая расположена на ленте в блоке инструментов «Стили».

После этого, открывается меню условного форматирования. Тут представляется три основных вида форматирования:

• Гистограммы;
• Цифровые шкалы;
• Значки.

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

Как видим, гистограммы появились в выделенных ячейках столбца. Чем большее числовое значение в ячейках, тем гистограмма длиннее. Кроме того, в версиях Excel 2010, 2013 и 2016 годов, имеется возможность корректного отображения отрицательных значений в гистограмме. А вот, у версии 2007 года такой возможности нет.

При использовании вместо гистограммы цветовой шкалы, также существует возможность выбрать различные варианты данного инструмента. При этом, как правило, чем большее значение расположено в ячейке, тем насыщеннее цвет шкалы.

Наиболее интересным и сложным инструментом среди данного набора функций форматирования являются значки. Существует четыре основные группы значков: направления, фигуры, индикаторы и оценки. Каждый выбранный пользователем вариант предполагает использование разных значков при оценке содержимого ячейки. Вся выделенная область сканируется Excel, и все значения ячеек разделяются на части, согласно величинам, указанным в них. К самым большим величинам применяются значки зеленого цвета, к величинам среднего диапазона – желтого, и величины, располагающиеся в самой меньшей трети – помечаются значками красного цвета.

При выборе стрелок, в качестве значков, кроме цветового оформления, используется ещё сигнализирование в виде направлений. Так, стрелка, повернутая указателем вверх, применяется к большим величинам, влево – к средним, вниз – к малым. При использовании фигур, кругом помечаются самые большие величины, треугольником – средние, ромбом – малые.

Правила выделения ячеек

По умолчанию, используется правило, при котором все ячейки выделенного фрагмента обозначаются определенным цветом или значком, согласно расположенным в них величинам. Но, используя меню, о котором мы уже говорили выше, можно применять и другие правила обозначения.

Кликаем по пункту меню «Правила выделения ячеек». Как видим, существует семь основных правил:

• Больше;
• Меньше;
• Равно;
• Между;
• Дата;
• Повторяющиеся значения.

Рассмотрим применение этих действий на примерах. Выделим диапазон ячеек, и кликнем по пункту «Больше…».

Открывается окно, в котором нужно установить, значения больше какого числа будут выделяться. Делается это в поле «Форматировать ячейки, которые больше». По умолчанию, сюда автоматически вписывается среднее значение диапазона, но можно установить любое другое, либо же указать адрес ячейки, в которой содержится это число. Последний вариант подойдёт для динамических таблиц, данные в которых постоянно изменяются, или для ячейки, где применяется формула. Мы для примера установили значение в 20000.

В следующем поле, нужно определиться, как будут выделяться ячейки: светло-красная заливка и темно-красный цвет (по умолчанию); желтая заливка и темно-желтый текст; красный текст, и т.д. Кроме того, существует пользовательский формат.

При переходе на этот пункт, открывается окно, в котором можно редактировать выделения, практически, как угодно, применяя различные варианты шрифта, заливки, и границы.

После того, как мы определились, со значениями в окне настройки правил выделения, жмём на кнопку «OK».

Как видим, ячейки выделены, согласно установленному правилу.

По такому же принципу выделяются значения при применении правил «Меньше», «Между» и «Равно». Только в первом случае, выделяются ячейки меньше значения, установленного вами; во втором случае, устанавливается интервал чисел, ячейки с которыми будут выделяться; в третьем случае задаётся конкретное число, а выделяться будут ячейки только содержащие его.

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

Правило «Дата» применяется к ячейкам, которые содержат значения в формате даты. При этом, в настройках можно установить выделение ячеек по тому, когда произошло или произойдёт событие: сегодня, вчера, завтра, за последние 7 дней, и т.д.

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

Правила отбора первых и последних значений

Кроме того, в меню условного форматирования имеется ещё один интересный пункт – «Правила отбора первых и последних значений». Тут можно установить выделение только самых больших или самых маленьких значений в диапазоне ячеек. При этом, можно использовать отбор, как по порядковым величинам, так и по процентным. Существуют следующие критерии отбора, которые указаны в соответствующих пунктах меню:

• Первые 10 элементов;
• Первые 10%;
• Последние 10 элементов;
• Последние 10%;
• Выше среднего;
• Ниже среднего.

Но, после того, как вы кликнули по соответствующему пункту, можно немного изменить правила. Открывается окно, в котором производится выбор типа выделения, а также, при желании, можно установить другую границу отбора. Например, мы, перейдя по пункту «Первые 10 элементов», в открывшемся окне, в поле «Форматировать первые ячейки» заменили число 10 на 7. Таким образом, после нажатия на кнопку «OK», будут выделяться не 10 самых больших значений, а только 7.

Создание правил

Выше мы говорили о правилах, которые уже установлены в программе Excel, и пользователь может просто выбрать любое из них. Но, кроме того, при желании, пользователь может создавать свои правила.

Для этого, нужно нажать в любом подразделе меню условного форматирования на пункт «Другие правила…», расположенный в самом низу списка». Или же кликнуть по пункту «Создать правило…», который расположен в нижней части основного меню условного форматирования.

Открывается окно, где нужно выбрать один из шести типов правил:

1. Форматировать все ячейки на основании их значений;
2. Форматировать только ячейки, которые содержат;
3. Форматировать только первые и последние значения;
4. Форматировать только значения, которые находятся выше или ниже среднего;
5. Форматировать только уникальные или повторяющиеся значения;
6. Использовать формулу для определения форматируемых ячеек.

Согласно выбранному типу правил, в нижней части окна нужно настроить изменение описания правил, установив величины, интервалы и другие значения, о которых мы уже говорили ниже. Только в данном случае, установка этих значений будет более гибкая. Тут же задаётся, при помощи изменения шрифта, границ и заливки, как именно будет выглядеть выделение. После того, как все настройки выполнены, нужно нажать на кнопку «OK», для сохранения проведенных изменений.

Управление правилами

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

Открывается окно, где представлены все правила, которые относятся к выделенному диапазону ячеек. Правила применяются сверху вниз, так как они размещены в списке. Таким образом, если правила противоречат друг другу, то по факту на экране отображается выполнение только самого последнего из них.

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

Есть и другой вариант. Нужно установить галочку в колонке с наименованием «Остановить, если истина» напротив нужного нам правила. Таким образом, перебирая правила сверху вниз, программа остановится именно на правиле, около которого стоит данная пометка, и не будет опускаться ниже, а значит, именно это правило будет фактически выполнятся.

В этом же окне имеются кнопки создания и изменения выделенного правила. После нажатия на эти кнопки, запускаются окна создания и изменения правил, о которых мы уже вели речь выше.

Для того, чтобы удалить правило, нужно его выделить, и нажать на кнопку «Удалить правило».

Кроме того, можно удалить правила и через основное меню условного форматирования. Для этого, кликаем по пункту «Удалить правила». Открывается подменю, где можно выбрать один из вариантов удаления: либо удалить правила только на выделенном диапазоне ячеек, либо удалить абсолютно все правила, которые имеются на открытом листе Excel.

Как видим, условное форматирование является очень мощным инструментом для визуализации данных в таблице. С его помощью, можно настроить таблицу таким образом, что общая информация на ней будет усваиваться пользователем с первого взгляда. Кроме того, условное форматирование придаёт большую эстетическую привлекательность документу.