Учебник по условному форматированию Excel с примерами

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

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

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

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

Условное форматирование Excel используется для применения определенного форматирования к данным, которые соответствуют одному или нескольким условиям. Как и обычное форматирование ячеек, оно позволяет выделять и различать данные различными способами, изменяя цвет заливки ячеек, цвет шрифта, стили границ и т. д. Отличие в том, что оно более гибкое и динамичное — при изменении данных условные форматы автоматически обновляться, чтобы отражать изменения.

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

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

Во всех версиях Excel 2010–Excel 365 условное форматирование находится в одном месте: вкладка «Главная» > группа «Стили» > «Условное форматирование».

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

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

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

Чтобы по-настоящему использовать возможности условного форматирования, вам нужно научиться использовать различные типы правил. Хорошая новость заключается в том, что какое бы правило вы ни применяли, оно определяет две ключевые вещи:

• Какие ячейки покрываются правилом.
• Какое условие должно быть соблюдено.

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

1. В электронной таблице выберите ячейки, которые вы хотите отформатировать.
2. На вкладке Главная в группе Стили щелкните Условное форматирование.
3. Из набора встроенных правил выберите то, которое соответствует вашей цели.

   Например, мы собираемся выделить значения меньше 0, поэтому мы нажимаем «Правила выделения ячеек» > «Меньше чем…».
   

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

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

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

• Больше или равно
• Между двумя значениями
• Текст, содержащий определенные слова или символы
• Дата, попадающая в определенный диапазон
• Повторяющиеся значения
• Верхние/нижние N чисел

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

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

1. В диалоговом окне предустановленного правила в раскрывающемся списке справа выберите «Пользовательский формат…».
   
2. В диалоговом окне «Формат ячеек» переключайтесь между вкладками «Шрифт», «Граница» и «Заливка», чтобы выбрать нужный стиль шрифта, стиль границы и цвет фона соответственно. Как только вы это сделаете, вы сразу же увидите предварительный просмотр выбранного формата. Когда закончите, нажмите OK.
   
3. Нажмите OK еще раз, чтобы закрыть предыдущее диалоговое окно и применить пользовательское форматирование по вашему выбору.
   

Советы:

• Если вам нужно больше цветов, чем предусмотрено стандартной палитрой, нажмите кнопку «Дополнительные цвета…» на вкладке «Заливка» или «Шрифт».
• Если вы хотите применить градиентный цвет фона, нажмите кнопку «Эффекты заливки» на вкладке «Заливка» и выберите нужные параметры.

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

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

1. Выберите ячейки для форматирования и нажмите «Условное форматирование» > «Новое правило».
   

2. В открывшемся диалоговом окне «Новое правило форматирования» выберите тип правила.

   Например, чтобы отформатировать ячейки с процентным изменением менее 5% в любом направлении, мы выбираем «Форматировать только те ячейки, которые содержат», а затем настраиваем правило, как показано на снимке экрана ниже:
   

3. Нажмите кнопку «Формат…», а затем выберите нужный цвет заливки и/или шрифта.
4. Нажмите OK дважды, чтобы закрыть оба диалоговых окна, и ваше условное форматирование выполнено!
   

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

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

В качестве примера выделим в столбце B цены, превышающие пороговую цену в ячейке D2. Для этого необходимо выполнить следующие шаги:

1. Нажмите «Условное форматирование»> «Правила выделения ячеек»> «Больше чем…».
2. В появившемся диалоговом окне поместите курсор в текстовое поле слева (или щелкните значок «Свернуть диалоговое окно») и выберите ячейку D2.
3. Когда закончите, нажмите OK.

В результате все цены выше значения в D2 будут выделены выбранным цветом:

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

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

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

Например, вы можете создать 3 правила, чтобы выделять цены выше 105 долларов США красным, выше 100 долларов – оранжевым и выше 99 долларов – желтым. Чтобы правила работали корректно, нужно расположить их в правильном порядке. Если правило «больше 99» помещено первым, то будет применено только желтое форматирование, потому что два других правила не смогут сработать — очевидно, что любое число, превышающее 100 или 105, также больше, чем 99 🙂

Чтобы изменить правила, вам нужно сделать следующее:

1. Выберите любую ячейку в вашем наборе данных, на которую распространяются правила.
2. Откройте диспетчер правил, нажав «Условное форматирование» > «Управление правилами…».
3. Щелкните правило, которое необходимо применить первым, а затем с помощью стрелки вверх переместите его наверх. Сделайте то же самое для второго по приоритету правила.
4. Установите флажок Stop If True рядом со всеми правилами, кроме последнего, поскольку вы не хотите, чтобы последующие правила применялись при выполнении предыдущего условия.

Что такое Stop if True в условном форматировании Excel?

Параметр Stop If True в условном форматировании запрещает Excel обрабатывать другие правила при выполнении условия в текущем правиле. Другими словами, если для одной и той же ячейки задано два или более правил, а для первого правила установлено значение «Остановить», если для первого правила установлено значение «Истина», последующие правила игнорируются после активации первого правила.

В приведенном выше примере мы уже использовали эту опцию, чтобы игнорировать последующие правила, когда применяется правило первого приоритета. Это использование вполне очевидно. А вот еще пара примеров, где использование функции Stop If True не столь очевидно, но крайне полезно:

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

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

1. Выберите любую ячейку, к которой применяется правило, и нажмите «Условное форматирование» > «Управление правилами…».
2. В диалоговом окне «Диспетчер правил» выберите правило, которое хотите изменить, а затем нажмите кнопку «Изменить правило…».
   
3. В диалоговом окне «Редактировать правило форматирования» внесите необходимые изменения и нажмите «ОК», чтобы сохранить изменения.

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

Кончик. Если вы не видите правило, которое хотите изменить, выберите «Этот рабочий лист» в раскрывающемся списке «Показать правила форматирования для» в верхней части диалогового окна «Диспетчер правил». Это отобразит список всех правил на вашем листе.

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

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

1. Щелкните любую ячейку с форматированием, которое вы хотите скопировать.
2. Щелкните Главная > Формат по образцу. Это изменит указатель мыши на кисть.

   Кончик. Чтобы скопировать форматирование в несколько несмежных ячеек или диапазонов, дважды щелкните Формат по образцу.

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

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

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

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

Я оставил самую простую часть напоследок 🙂 Чтобы удалить правило, вы можете:

• Откройте диспетчер правил условного форматирования, выберите правило и нажмите кнопку «Удалить правило».
  

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

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

Практическая рабочая тетрадь для скачивания

Условное форматирование Excel — примеры (файл .xlsx)

Вас также могут заинтересовать