Что такое условное форматирование в эксель и как его использовать

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

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

Попробуйте попрактиковаться

Вы можете скопировать приведенную ниже таблицу на лист в Excel — обязательно вставьте ее в ячейку A1. Затем выделите ячейки D2: D11 и создайте правило условного форматирования, в котором используется приведенная ниже формула.

=СЧЁТЕСЛИ($D$2:$D$11;D2)>1

При создании правила убедитесь в том, что оно применяется к ячейкам D2:D11. Задайте цветовое форматирование, которое должно применяться к ячейкам, удовлетворяющим условию (т. е. если название города встречается в столбце D более одного раза, а это — Москва и Мурманск).

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

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

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

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

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

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

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

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

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

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

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

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

1. Выделите ячейки от A2 до A7 (для этого щелкните и перетащите указатель мыши с ячейки A2 в ячейку A7).

2. Затем выберите вкладку Главная > Условное форматирование > Создать правило.

3. В диалоговом окне Создание правила форматирования выберите пункт Использовать формулу для определения форматируемых ячеек.

4. В разделе Форматировать значения, для которых следующая формула является истинной: введите формулу: =A2>СЕГОДНЯ().

   Функция СЕГОДНЯ используется в формуле для определения значения дат в столбце A, превышающих значение сегодняшней даты (будущих дат). Ячейки, удовлетворяющие этому условию, форматируются.

5. Нажмите кнопку Формат.

6. В раскрывающемся списке Цвет выберите Красный. В списке раздела Начертание выберите пункт Полужирный.

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

   Теперь форматирование применено к столбцу A.

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

1. Выделите ячейки от C2 до C7.

2. Повторите действия 2–4, указанные выше, и введите следующую формулу: =C2=»Y».

   Формула определяет ячейки в столбце C, содержащие символ «Y» (прямые кавычки вокруг символа «Y» указывают, что это текст). Ячейки, удовлетворяющие этому условию, форматируются.

3. В раскрывающемся списке Цвет выберите Белый. В списке раздела Начертание выберите пункт Полужирный.

4. Перейдите на вкладку Заливка и выберите Зеленый.

   Теперь форматирование применено к столбцу C.

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

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

Больше

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. После нажатия на клавишу Enter, вы увидите следующее.

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

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

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

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

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

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

Дата

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

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

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

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

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

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

6. Текущая дата на момент написания статьи – года.

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

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

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

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

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

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

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

Гистограммы

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

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

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

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

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

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

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

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

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

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

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

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

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

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

• стиль;

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

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

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

Дополнительные сведения

Вы всегда можете задать вопрос специалисту Excel Tech Community, попросить помощи в сообществе Answers community, а также предложить новую функцию или улучшение на веб-сайте Excel User Voice.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Мы рады, что смогли помочь Вам в решении проблемы.

Опишите, что у вас не получилось. Наши специалисты постараются ответить максимально быстро.

Помогла ли вам эта статья?

ДА НЕТ

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

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

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

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

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

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

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

Заключение

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Поделитесь в соц.сетях:

Оцените статью: (Пока оценок нет) Загрузка...

Читайте также:

Не заряжается айфон – что делать... Что такое буфер обмена на андроиде и где... Зачем нужен wifi репитер: рассмотрим по... Создание загрузочной флешки из iso образ...