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

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

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

К основным разделам данного меню относятся:

• правила выделения ячеек;

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

• гистограммы;

• цветовые шкалы;

• наборы значков;

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

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

В этом разделе также очень много различных вариантов форматирования. Разберем каждый из них.

Больше

1. Для начала выделите какую-нибудь строчку. В данном случае это будут пострадавшие в первой шахте.

2. Затем перейдите на вкладку «Главная» и нажмите на кнопку «Условное форматирование». В появившемся меню кликаем на пункт «Правила выделения ячеек». Затем выбираем вариант «Больше».

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

4. Сразу после этого ссылка на ячейку подставится автоматически (а она сама выделится пунктирной линией). Для вставки нажмите на кнопку «OK».

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

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

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

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

Меньше

1. Выделите какую-нибудь строчку. Нажмите на иконку «Условное форматирование», расположенную на вкладке «Главная». Выберите пункт «Правила выделения ячеек», а затем – «Меньше».

2. Вам снова предложат указать ячейку для сравнения. Для этого сделайте левый клик мыши по нужной клетке.

3. В результате этого произойдет подстановка нужного адреса. Для сохранения настроек нажмите на кнопку «OK».

4. В результате этого мы видим, что все ячейки, значение которых меньше 24, выделились другим цветом.

Между

1. Выделите какую-нибудь строку без правил форматирования. Заходим в тот же раздел меню, но на этот раз выбираем пункт «Между».

2. Затем редактор Эксель сам предложит какие-нибудь промежуточные значения. Можно оставить всё без изменений.

3. Либо подставить что-то своё, что удобнее вам. Например, больше 14, но меньше 17. Для сохранения нажмите на кнопку «OK».

4. В результате этого всё то, что находится между этими цифрами, выделилось другим цветом.

Равно

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

2. Нас попросят указать ссылку на ячейку для сравнения или же готовое числовое значение. Введем, например, цифру 18. Поскольку она встречается в выделенной строке. Для сохранения нажимаем на кнопку «OK».

3. Благодаря этому ячейка, которая соответствует указанному значению, стала подсвечена иным цветом.

4. Для проверки можно попробовать изменить что-нибудь. Для примера возьмем соседнюю клетку. Исправим там 19 на 18. После нажатия на клавишу [knopka]Enter[/knopka], вы увидите следующее.

Мы видим, что фон ячеек меняется полностью в автоматическом режиме.

Текст содержит

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

1. Первым делом выделите какую-нибудь строку с несколькими цифрами. Затем при помощи знакомого нам меню выберите пункт «Текст содержит…».

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

3. В результате этого выделились ячейки с числами 20 и 23, поскольку в обоих из них есть цифра 2.

Дата

Подобные манипуляции можно проделать и с временными значениями.

1. Для начала добавим строчку, в которой напишем несколько дат. Желательно, чтобы они шли подряд. Так проще будет сравнивать.

2. После этого выделяем всю эту строку. Затем переходим в меню «Условное форматирование» и выбираем пункт «Дата».

3. Сразу после этого появится окно, в котором можно будет выбрать несколько вариантов:
   • вчера;
   • сегодня;
   • завтра;
   • за последние 7 дней;
   • на прошлой неделе;
   • на текущей неделе;
   • на следующей неделе;
   • в прошлом месяце;
   • в этом месяце;
   • в следующем месяце.

4. В качестве примера выберем вариант «Завтра». Для сохранения нажмите на кнопку «OK».

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

6. Текущая дата на момент написания статьи – 25 февраля 2018 года.

Повторяющиеся значения

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

1. Выделите в таблице основные значения, которые нужно как-то анализировать.

2. Кликните на иконку «Условное форматирование» и в «Правилах выделения ячеек» выберите пункт «Повторяющиеся значения».

3. Сразу после этого появится окно, в котором вы сможете выбрать два значения:
   • повторяющиеся;

• уникальные.

В каждом случае будет доступен предварительный просмотр, чтобы вы могли точно определиться, что именно вам нужно. Для сохранения нужно кликнуть на кнопку «OK».

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

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

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

Рассмотрим каждый из них.

Первые 10 элементов

Выбрав этот пункт, вы увидите окно, в котором предложат указать количество первых ячеек. Для сохранения кликните на «OK».

Отсчет происходит от большего значения к меньшему.

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

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

Обратите внимание на то, что если будет две ячейки с одинаковым наибольшим числом, то выделятся оба!

Первые 10%

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

Если указать число 10 (оно используется по умолчанию), то вы увидите следующее.

Если данное правило форматирования вам понравилось, нужно нажать на кнопку «OK». В противном случае кликните на «Отмена».

Последние 10 элементов

Как было сказано выше, в данном случае выделяются те ячейки, в которых содержатся минимальные данные. Принцип ввода тот же самый – указываете нужное количество и кликаете на кнопку «OK».

Если вы укажете всего 1 клетку, но при этом минимальных цифр будет несколько, то произойдет выделение всех (в нашем случае — две).

Последние 10%

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

Выше среднего

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

Ниже среднего

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

В описанных выше методах сравнения данных использовался метод сплошной заливки элементов. Иногда это не совсем удобно.

Гистограммы

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

• градиентная;
• сплошная.

Рассмотрим каждый из предложенных вариантов.

Градиентная заливка

1. Первым делом необходимо выделить нужные строки и столбцы. Затем кликнуть на иконку «Условное форматирование». После этого перейти в раздел «Гистограммы» и выбрать любую из предложенных заливок.

К значениям по умолчанию относятся:

• зеленая;
• красная;
• оранжевая;
• голубая;
• фиолетовая.

При наведении на каждый из вариантов вам будет доступен предварительный просмотр.

Сплошная заливка

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

Цвета используются те же самые.

Другие правила

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

Здесь вы сможете настроить:

• стиль;

• минимальное и максимальное значение;

• внешний вид столбца.

Образец того, что вы настроили, можно увидеть в правом нижнем углу.

Цветовые шкалы

Если вы хотите чего-то более контрастного, нужно сделать следующие действия.

1. Выделите таблицу (основную информацию для анализа данных). Нажмите на иконку «Условное форматирование», которая расположена на «Главной» вкладке на панели инструментов. В появившемся меню выберите пункт «Цветовые шкалы». В результате этого появится большой список из 12 вариантов оформления.

2. При наведении на каждый шаблон вы увидите подобное пояснение.

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

Другие правила

Если ничего из предложенного редактором Excel вам не понравилось, вы всегда можете создать что-то своё. Для этого нужно в этом же разделе меню кликнуть на пункт «Другие правила».

Сразу после этого вы увидите следующее окно. Здесь можно указать начальный и конечный цвет. Для сохранения достаточно нажать на кнопку «OK».

Наборы значков

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

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

Стоит отметить, что редактор сам автоматически делит данные на несколько групп: минимальные, средние и максимальные.

К возможным вариантам относятся (каждый раз при наведении на любую иконку вы будете видеть предварительный просмотр без сохранения правила форматирования):

• направления (около больших чисел появится стрелка вверх; для средних – направо; минимальным цифрам соответствует направление вниз);

• фигуры (цвет зависит от числа в ячейке – серый цвет для самых больших значений);

• индикаторы (галочка – высокие, восклицательный знак – средние, а крестик – минимум);

• оценки (степень заполнения элемента зависит от числа в ячейки);

Другие правила

Если ни одна из иконок вам не понравилась, вы можете создать своё правило заполнения клеток.

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

• стиль значка;

• свой вариант значка;

• граничные значения для значков;

Для сохранения нажмите на кнопку «OK».

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

Если ваш эксперимент не удался и проделанные вами манипуляции только испортили внешний вид таблицы, то всё это можно отменить довольно простым способом.

1. Для начала нужно выделить те элементы, условное форматирование которых необходимо отключить.

2. Затем нажмите на вкладке «Главная» на иконку «Условное форматирование».
3. После этого выберите пункт «Удалить правила».
4. Далее кликаем на «Удалить правила из выделенных ячеек».

5. Если хотите удалить всё, то выделяем второй пункт – «Удалить правила со всего листа».

6. Результат будет следующим. Всё вернется к своему прежнему виду.

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

Набор способов форматирования можно менять по собственному желанию. Это делается следующим образом.

1. Кликните на кнопку «Условное форматирование».
2. Выберите пункт «Управление правилами».

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

4. Выберите пункт «Этот лист».

5. Вследствие этого вы увидите все правила, которые используются в документе на данный момент.

Удаление

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

Необходимо быть очень внимательным при выполнении таких действий, поскольку вас дополнительно не спросят, уверены ли вы в своем выборе.

Изменение

Редактировать правила довольно просто. Это делается следующим образом.

1. Выберите любую строчку.
2. Нажмите на кнопку «Изменить правило».

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

4. Здесь вы сможете указать что именно они содержат:
   • текст;
   • даты;
   • пустые;
   • непустые;
   • ошибки;
   • без ошибок.

Рассмотрим эти условия более детально.

Которые содержат

Как и было сказано выше, вам на выбор предоставляется большой список вариантов со своими дополнительными параметрами:

• даты:
  • вчера;
  • сегодня;
  • завтра;
  • за последние 7 дней;
  • на прошлой неделе;
  • на текущей неделе;
  • в прошлом месяце;
  • в этом месяце;
  • в следующем месяце;

• значение ячейки:
  • между;
  • вне;
  • равно;
  • не равно;
  • больше;
  • меньше;
  • больше или равно;
  • меньше или равно;

• текст:
  • содержит;
  • не содержит;
  • начинается с;
  • заканчивается на;

• пустые;

• непустые;

• ошибки;

• без ошибок.

Все ячейки

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

Сразу после этого изменятся поля для описания правила. Теперь вы сможете указать:

• стиль формата;

• минимальное значение;

• начальный цвет заливки;

• конечный цвет заливки;

Сразу после выбора этих параметров вы увидите предварительный просмотр цветового градиента.

Только первые или последние

При выборе этой категории описание снова изменится.

Здесь вы сможете выбрать, какие именно значения нужно форматировать:

• первые;
• последние.

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

Если нажать на кнопку «Формат», вы увидите следующее. Для сохранения параметров нужно нажать на кнопку «OK».

Выше или ниже среднего

При выборе этого типа маркировки данных вы увидите следующий список.

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

Уникальные или повторяющиеся

В данном случае разницы нет никакой. Всё то же самое, как и при помощи обычного меню.

Использование формулы

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

Создание

Для того чтобы добавить своё правило, нужно сделать следующее.

1. Нажмите на кнопку «Условное форматирование», которая расположена на вкладке «Главная».
2. В появившемся меню кликните на соответствующий пункт.

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

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

Описанные выше инструкции подходят для редактора Эксель 2007, 2010, 2013 и 2016 годов. В более старой версии 2003 года всё происходит немного иначе. Разумеется, возможностей там гораздо меньше.

Для того чтобы найти данный инструмент, нужно сделать следующее:

1. Кликнуть в главном меню на раздел «Формат».
2. Выбрать пункт «Условное форматирование».

3. В результате этого вы увидите следующее.

Функционал довольно скудный, но при этом существует возможность создания нескольких условий. Для этого нужно кликнуть на кнопку «А также». В результате этого вы увидите следующее окно.

Заключение

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

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

Видеоинструкция

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