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

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

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

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

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

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

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

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

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

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

Программы

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

Цвета

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

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

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

Таблица для сравнения

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

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

Больше

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

Выделение строки

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

Правила выделения ячеек - больше

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

Среднее значение

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

Клик по ОК

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

Подсветка ячеек

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

Варианты раскрасок

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

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

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

Окно формата

Меньше

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

Меньше

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

Левый клик

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

Нажатие по OK

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

Другой цвет ячеек

Между

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

Между

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

Настройка значений

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

Клик по «OK»

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

Другой цвет

Равно

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

Равно

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

Задание диапазона

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

Новый цвет подсветки

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

Смена значений

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

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

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

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

Пункт Текст содержит…

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

Сохранение

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

Цифры

Дата

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

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

Добавление дат

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

Дата

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

Варианты

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

Сохранение данных

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

Другой цвет поля

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

Текущая дата

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

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

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

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

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

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

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

Повторяющиеся

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

Уникальные

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Первые 10%

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

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

Первые 10%

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

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

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

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

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

Указание клетки

Последние 10%

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

Последние 10%

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

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

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

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

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

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

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

Гистограммы

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

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

Гистограммы

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

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

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

«Гистограммы»

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

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

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

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

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

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

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

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

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

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

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

  • стиль;

Стиль

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

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

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

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

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

Образец

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

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

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

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

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

Пояснение

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

Выбор цветовой гаммы

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

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

Свои правила

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

Задание цвета

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

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

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

Список значков

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

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

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

Направления

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

Фигуры

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

Индикаторы

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

Оценки

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

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

Правило заполнения

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

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

Стиль значка

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

Вариант значка

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

Граничные значения

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

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

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

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

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

Выделение для отключения

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

Удалить правила из выделенных ячеек

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

Удалить правила со всего листа

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

Прежний вид

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

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

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

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

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

Текущий фрагмент

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

Этот лист

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

Правила

Удаление

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

Пункт Удалить правило

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

Изменение

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

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

Изменить правило

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

Форматировать только те ячейки, которые содержат

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

Содержание ячеек

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

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

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

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

Даты

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

Значение ячейки

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

Текст

  • пустые;

Пустые

  • непустые;

Непустые

  • ошибки;

Ошибки

  • без ошибок.

Без ошибок

Все ячейки

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

Все ячейки

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

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

Стиль формата

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

Минимальное значение

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

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

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

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

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

Предварительный просмотр цветового градиента

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

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

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

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

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

Первые и последние

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

Ввод числа

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

Формат

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

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

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

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

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

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

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

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

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

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

Создание

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

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

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

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

Новая форма

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

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

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

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

Поиск пункта Условное форматирование

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

Новый результат

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

А также

Заключение

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

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

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

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

Оцените статью
OS Helper
Добавить комментарий

  1. Татьяна

    Отличная статья!

    Ответить