Условное форматирование: инструмент Microsoft Excel для визуализации данных

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

Условное форматирование в Эксель – этот тот инструмент, который делит работу на до и после его изучения. Суть в том, что при наступлении некоторого условия ячейки форматируются автоматически. Например, если число превышает значение 100, шрифт становится красным полужирным курсивом; когда до наступления платежа остается 2 дня, ячейка с датой подсвечивается желтым цветом; перевыполнение плана продаж на 5% и более окрашивается в зеленый цвет и т.д. и т.п.

Вот упрощенный, но реальный пример. Есть отчет о товарных запасах.

Менеджер по закупкам отслеживает те позиции, которые требуют пополнения. Для этого он смотрит в последнюю колонку, где рассчитывается товарный запас (ТЗ) в неделях. Если ТЗ меньше, скажем, 3-х, то нужно готовить заказ. Если меньше 2-х, то возникает риск дефицита и заказ нужно размещать срочно. Если в таблице десятки позиций, то просмотр каждой строки займет довольно много времени. А теперь та же таблица, где после применения условного форматирования значения ниже пороговых подсвечиваются некоторым цветом.

Согласитесь, так гораздо нагляднее. В реальности условия сложнее, а данные постоянно меняются. Поэтому эффект от применения условного форматирования – это многочасовая экономия времени ежедневно! Теперь для оценки запасов достаточно взглянуть на таблицу, а не анализировать каждую ячейку. Много желтого – пора действовать, много красного – ситуация критическая!

Для настройки условного формата следует воспользоваться соответствующей командой на вкладке Главная.

При ее нажатии открывается меню.

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

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

Все сценарии разбиты на категории:

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

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

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

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

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

Меньше… Форматируются ячейки, у которых значение меньше заданного порога.

Между… Форматирование наступает, если содержимое ячейки находится внутри заданных границ.

Равно… если значение или текст в ячейке совпадает с условием.

Текст содержит… Если совпадает только часть текста (слово, код, комбинация символов и т.д).

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

Повторяющиеся значения… выделяются ячейки с одинаковым содержимым. Отличный способ найти дубликаты (повторы). В настройках можно выбрать и обратный вариант – выделить только уникальные значения.

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

Первые 10 элементов… Выделяются первые топ–10 ячеек. Количество регулируется в диалоговом окне (можно сделать топ-5, топ-20 и др.).

Первые 10%… Выделяются 10% наибольших значений. Долю можно изменить.

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

Последние 10%… Наименьшие 10% или другая доля от всех элементов.

Выше среднего… Форматируются все значения, которые больше средней арифметической.

Ниже среднего… Ниже средней арифметической.

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

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

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

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

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

В ячейках Excel выглядит так.

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

Откроется диалоговое окно, где можно создать новое, изменить или удалить правило. Часто используют сразу несколько правил.

После нажатия кнопки «Изменить правило…» откроется окно, вид которого зависит от редактируемого правила.

Здесь также есть куча настроек, но мы их пока опустим. В целом там все интуитивно понятно. Нужно только поэкспериментировать. Практика – лучший учитель.

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

Условное форматирование – это три шага вперед на пути к профессиональному использованию Excel. Поэтому рекомендую незамедлительно внедрить в практику.

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

Старайтесь также, чтобы количество используемых цветов было не больше трех. Иначе внимание рассеивается и может стать еще хуже.

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

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

Как сделать условное форматирование в 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» (первое в списке).

Читайте также  Открытие файла формата CSV в Microsoft Excel

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Как визуализировать данные в Excel. Часть 2

Продолжаем рассказывать о полезных инструментах для визуализации данных в Excel. В прошлой статье мы говорили об основах режима Power View (читать >>). Сегодня разберем еще один прием для работы с этим инструментом — составление карт с данными. Также в качестве бонуса поделимся небольшим лайфхаком по построению гистограмм. Больше возможностей, чтобы визуализировать результаты работы и презентовать выводы — на продвинутом курсе «Excel для бизнеса» от Changellenge >> ToolKit.

Карта в Power View

В прошлом материале мы работали с данными о целевой аудитории. Предположим, мы хотим понять, в каких регионах люди готовы платить за наши продукты больше, а в каких — меньше. Кроме того, мы хотим привязать эти результаты к опыту работы нашей ЦА. Как сделать подходящую карту в Power View?

Для начала создадим новый лист Power View, как мы делали это в первой задаче (смотреть >>). После этого перетащим в раздел «Поля» столбец, который отвечает за географическое положение участников. В нашем случае — город проживания. Наконец, в панели инструментов выберем Map (Карта).

Теперь будем работать с разделом «Поля». В поле Location (Локация) необходимо переместить столбец с городами. В поле Size (Размер) — информацию о цене, которую готовы заплатить покупатели, изменив суммирование столбца на среднее. Для получения данных в разрезе различного опыта работы, перетащим соответствующий столбец в поле Vertical Multiples (Вертикальные Множества).

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

Нарисовать «на салфетке»: гистограмма

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

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

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

  1. Выделить нужные данные (должны быть числовыми).
  2. Перейти в меню Conditional Formatting (Условное Форматирование) во вкладке Home (Главная).
  3. Выбрать Data Bars (Гистограммы) и понравившийся стиль графика.

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

Хочешь узнать о других возможностях MS Excel? Присоединяйся к онлайн-курсу «Excel для бизнеса» от Changellenge >> ToolKit. Он даст инструменты для автоматизации рутины, построения бизнес-моделей, анализа большого объема данных и визуализации результатов.

Получите карьерную поддержку

Если вы не знаете, с чего начать карьеру, зашли в тупик или считаете, что совершили какие-то ошибки, спросите совета у специалистов. Заполните заявку и консультанты Changellenge >> окажут вам помощь. Это отличный шанс вместе экспертом проработать проблемные вопросы и составить карьерный план.

Подписаться на карьерную рассылку

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

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

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

Располагается эта полезная возможность на вкладке «Главная» в области «Стили» под одноименной пиктограммой:

Создать правило

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

Выбрав пункт «Создать правило…», приложение отобразит окно:

В нем Вы можете выбрать тип правила и настроить его описание (подробнее читайте далее в статье).

Виды условного форматирования

Форматировать все ячейки на основании их значений

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

Гистограмма

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

Ширина ячейки принимается за 100%, что соответствует максимальному значению диапазона правила. Т.е. ячейка, содержащая максимальное значение будет залита полностью, а ячейка со значением в 2 раза меньшим максимальному – наполовину. В случае отрицательного значения, столбец будет окрашен другим цветом и иметь другую направленность (это можно изменить).

  • Показывать только столбец – установив флажок на данном поле, Вы сообщаете, что для диапазона ячеек правила необходимо скрывать содержимое и оставлять только формат;
  • Параметры значений – здесь устанавливаются максимальные и минимальные значения и их типы. В качестве типа может выступать число, процент, формула, процентиль либо по умолчанию (авто). Значение может быть только числовым. Все числа, меньше минимального (включая отрицательные), приравниваются к нулю, т.е. не содержат столбца. А те, которые больше максимального, приравниваются к 100% и закрашиваются полностью.
  • Внешний вид столбца – устанавливает способ заливки (сплошной или градиентный), границу и их цвета;
  • Направление столбца – определяет способ направленности (слева направо либо наоборот);
  • Кнопка «Отрицательные значения и ось…» – настройки отображения столбцов для отрицательных чисел. Что они позволяют:
    • Установить свой цвет заливки столбца и его границу или сделать их одинаковыми для всех значений (положительных и отрицательных. По умолчанию они различаются);
    • Задать положение оси или одинаковую направленность для всех значений.

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

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

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

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

  • Минимальным числом задан ноль, а значения меньше его, будут иметь такие же цвет и насыщенность;
  • Средним значением указана единица и желтый цвет. Это значит, что переход шкалы от красного к желтому будет осуществлен между 0 и 1;
  • 4 является максимальным значением. Все, что превышает его, получает те же установки. Переход от желтого к зеленому происходит между 1 и 4.

Наборы значков (флажков)

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

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

Читайте также  Удаление ячеек в Microsoft Excel

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

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

Рассмотрим правила, которые имеются в этом пункте:

  • Значение ячейки. Предполагает работу с числами и текстом. Сравнение производится по шкале сортировки.
  • Текст. Позволяет проверить наличие или отсутствие подстроки в тексте.
  • Даты. С его помощью легко создать правила типа «вчера», «сегодня», «завтра», «на прошлой неделе», «в следующем месяце» и т.п.
  • Пустые. Форматирует пустые ячейки. Пробелы не учитываются.
  • Непустые. Противоположное предыдущему правилу.
  • Ошибки. Истинно, когда значением ячейки является ошибка.
  • Без ошибки. Противоположное предыдущему правилу.

Форматировать только первые и последние значения

Из названия понятно, что правило срабатывает для тех ячеек, которые идут первыми (наибольшими) или последними (наименьшими) в указанном диапазоне. Количество таких ячеек указывается в виде числа или процента.

Формула в условном форматировании

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

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

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

Используем 2 условия со следующими формулами:

  • Если на складе нет товара, т.е. равен 0, то подсвечиваем позицию заказа красным – =ВПР(D3;A:B;2;ЛОЖЬ)=0;
  • Если на складе есть товар, но его количество меньше, чем указано в позиции заказа, то последнюю подсвечиваем желтым – =И(ВПР(D3;$A:$B;2;ЛОЖЬ) 0).

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

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

Остальные правила

Ничего не было сказано о еще двух видах правил, а именно:

  • Форматирование на основе среднего значения – полное название «Форматировать только значения, которые находятся выше или ниже среднего»;
  • Форматирование уникальных или повторяющихся значений.

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

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

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

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

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

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

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

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

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

Exceltip

Блог о программе Microsoft Excel: приемы, хитрости, секреты, трюки

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

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

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

Виды условного форматирования

Когда вы нажимаете на кнопку Условное форматирование, которая находится в группе Стили вкладки Главная, вы увидите выпадающее меню со следующими опциями:

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

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

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

Цветовые шкалы позволяет задавать двух- и трехцветовые шкалы для цвета фона ячейки на основе ее значения относительно других ячеек в диапазоне

Наборы значков отображает значок в ячейке. Какой именно значок отображается, зависит от значения ячейки относительно других ячеек. Excel 2013 предоставляет 20 наборов значков на выбор (при этом вы можете смешивать и сочетать значки из разных наборов). Количество значков в наборах колеблется от трех до пяти.

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

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

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

Графическое условное форматирование

Вероятно, самое крутое (и конечно, простое) условное форматирование, которое можно применить к диапазону ячеек – это форматирование с применением графических элементов – Гистограммы, Цветовые шкалы и Наборы значков.

На рисунке изображено применение двух различных правил для форматирования для диапазона от 6 до 1 и наоборот. В первом случае применялись Цветовые шкалы, где мы видим, как изменяется формат при изменении значения от 6 до 1, во втором – 3 цветные стрелки.

Определение конкретных значений в диапазоне ячеек

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

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

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

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

Выделяем диапазон ячеек, к которому мы хотим применить условное форматирование. Переходим по вкладке Главная в группу Стили, щелкаем кнопку Условное форматирование -> Правила выделения ячеек -> Текст содержит.

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

Щелкаем ОК, чтобы наше правило вступило в силу.

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

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

Выделяем диапазон ячеек, к которому мы хотим применить три различных правила условного форматирования. Начнем с создания правила для ячеек, содержащих значение равное 95. Переходим по вкладке Главная в группу Стили, щелкаем кнопку Условное форматирование -> Правила выделения ячеек -> Равно. Excel откроет диалоговое окно Равно, где в левом текстовом поле необходимо указать условие 95, а в правом выпадающем списке выбрать формат для этого условия Желая заливка с темно-желтым текстом.

Далее задаем условное форматирование для значений больше 95. Из меню Условное форматирование -> Правила выделения ячеек выбираем Больше, в появившемся диалоговом окне Больше указываем значение, выше которого ячейка будет закрашиваться в зеленый цвет, и сам формат.

Читайте также  Решение проблемы с исчезновением строки формул в Excel

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

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

Формулы в условном форматировании

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

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

В поле Измените описание правила задаем условия и формат для нашего правила. В нашем случае, условием будет формула =ИЛИ(ДЕНЬНЕД($A2;2)=6;ДЕНЬНЕД($A2;2)=7). В качестве формата я выбрал темно красную заливку.

Послесловие

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

Вам также могут быть интересны следующие статьи

31 комментарий

А можно ли сделать так чтобы значение в ячейке менялось в зависимости от цвета другой ячейки? Например если ячейка залита красным цветом то 0, если зеленым цветом то 1.

Эльнур, такую штуку можно реализовать с помощью создания пользовательской функции, например, такой:

Пример с формулой можно упростить (если, конечно, не было цели продемонстрировать именно то, как работает функция ИЛИ). Формула ниже будет делать то же самое:
=ДЕНЬНЕД($A2;2)>5

Скажите пож-та,мне необходимо что бы при определенном значении,в ячейку тянулся заранее готовый текст!Уже два часа читаю функции,но к сожелению ничего подходящего!Заранее спасибо!

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

Добрый день!
Не могу разобраться какое правило выбрать.
Условие следующее: в одной колонке указан планируемый срок реализации, в следующей — фактический. Если фактический срок превышает плановый (дата более поздняя), то выделение одним цветом, если дата более ранняя или равна дате по плану — другим.

Сделала сравнение по функции ЕСЛИ. Но если растягиваю формулу, то все остальные ячейки столбца «срок факт» ссылаются на первую ячейку столбца «срок план.»

Скажите, а можно окрасить строку, на основании одной из ячеек, которая в свою очередь принимает цвет в соответствии с УФ «цветовая шкала» ()

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

Сегодня мы рассмотрим:

Мало кто в мире не знает название программы Excel. Этот урок с примерами и видео мы посвятим условному форматированию – одному из самых интересных и полезных средств Excel.

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


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

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

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

Итак, зона работы выделена, кнопка нажата – что дальше? Перед вами откроется меню условного форматирования, где будут такие пункты:

  1. Правила выделения ячеек.
  2. Правила отбора первых и последних значений.
  3. Гистограммы.
  4. Цветовые шкалы.
  5. Наборы значков.
  6. Дополнительно: создать, удалить, управление правилами.

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

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

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

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

  1. Нажимаем «Между» и в новом открывшемся окне в соответствующих ячейках вводим параметры от и до.
  2. Потом укажите цвет, которым хотите выделить подходящие вам варианты (пусть у нас это будет «Светло-красная заливка и темно-красный текст»). То есть если вы работаете со столбиком цен на мобильные телефоны, то введите цифры минимальной и максимальной стоимости, что вам подходит (пусть у нас это будет 50 и 100).
  3. После того как вы подтвердили, что именно МЕЖДУ этими значениями хотите начать поиск, в таблице ячейки подсветятся соответствующим образом и мы увидим ВСЕ ячейки с ценой от 50 до 10 долларов окрашенными в светло-красный цвет и с темно-красным текстом.

Все это совсем несложно, когда на практике приступить к работе с программой.

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

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

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

  1. Нажав «Первые 10 элементов» мы вызовем окно, где можно управлять этим форматированием.
  2. Здесь укажем количество ячеек, которые нам нужно выделить: изначально было названо 10, но нам надо только 5, поэтому исправляем это в соответствующем поле.
  3. Потом выбираем цвет форматирования: пусть у нас это будет «Красная граница».
  4. Тогда 5 ячеек с самыми большими значениями буду выделены красной рамкой.

Гистограммы

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

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

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

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

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

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

  • выбираем «Наборы значков» и в разделе «Направления» кликаем на «5 цветных стрелок». Таким образом, в каждой ячейке поля, в котором мы работаем, появится один из 5 типов стрелки.
  • Объясним, как они работают: весь диапазон значений в выделенных нами ячейках составляет 100%, а каждая по очереди стрелочка отвечает за числа, которые входят в каждые 20% по порядку. Пусть у нас в столбце количества покупок телефона есть значения от 0 до 100. Тогда первая стрелка (зеленая вверх) будет стоять возле каждого значения от 80 до 100, а последняя (красная вниз) – возле каждого от 0 до 20. Соответственно и все промежуточные стрелки.

Процентное соотношение или весь диапазон можно настроить в меню «Управление правилами», здесь же можно поиграться с настройками остальных правил.