Как Использовать Условное Форматирование в Microsoft Excel.
Электронные таблицы, иногда недолюбливают, из-за того, что их сложно читать. Создание таблицы «дружелюбной» для пользователя требует форматирования — основные элементы которого: добавление шапок таблицам, выравнивание и использование в таблицах правильных цветов.
Форматирование — это здорово, но условное форматирование — еще более мощный инструмент. Условное форматирование влияет на то как ячейка будет выглядеть в зависимости от ее содержимого.
Условное форматирование позволяет изменить фон в ячейке в зависимости от значения в ней.
На скриншоте ниже, вы можете видеть преимущества условного форматирования. Я использовал очень простое правило, благодаря которому фон ячейки меняется на зеленый, при положительной величине, и становится красным при отрицательном значении. Единственное, что меняется фон, в зависимости от значения.
Другие примеры использования условного форматирования:
- Делать все отрицательные числа в списке данных — красными.
- Создание гистограмм для визуального представления максимальных и минимальных значений в ваших данных.
- Использование иконок что бы показывать самые высокие и низкие величины в списке данных.
Когда вы делаете условное форматирование, то оно регулируется тем, что есть в ячейке. В этом уроке, вы узнаете, как легко использовать условное форматирование в Excel.
Как Использовать Условное Форматирование в Excel (Смотрите и Учитесь)
В этом видео, я пройдусь по некоторым простейшим способам создания условного форматирования. Вы получите представление о том, как я использую условное форматирование в Excel, чтобы выделить важные данные.
Остальная часть урока будет посвящена добавлению условного форматирования к вашим электронным таблицам. Прочитайте и подумайте, как вы можете использовать это при работе с вашими таблицами.
1. Использование Встроенного Условного Форматирования
Этот урок предполагает использование примеров электронных таблиц, которые вы можете загрузить и использовать по ходу урока.
Электронные таблицы могут порой утомлять. Хорошая новость заключается в том, что условное форматирование в Excel легко начать использовать с помощью встроенных Excel стилей.
Мы взглянем на встроенные стили Excel предназначенные для условного форматирования. Каждый из этих стилей вы можете использовать, если выделите данные которые вы хотите форматировать, а затем найдете кнопку Условное Форматирование на вкладке Главная, в верхней строке меню.
Примените условное форматирование выделив данные и нажав на кнопку Условное Форматирование во вкладке Главная в верхней строке меню.
Когда вы откроете меню условного форматирования в Excel, выберите один из вариантов, чтобы его применить.
Выделение Ячеек Цветом
Выбор варианта выделения ячеек позволит вам выполнять условное форматирование для ряда факторов. Кликните по Условное Форматирование и затем выберите в меню опцию Правила Выделения Ячеек, что бы начать работать с условным форматированием.
Выделите ячейки, чтобы применить стили.
Эти варианты как стили для условного форматирования. Вот как работает каждый из них:
- Больше. — Форматирует ячейку значение в которой больше чем вы установите.
- Меньше. — Форматирует ячейки значение в которых меньше чем вы установите.
- Между. — Позволяет задавать диапазон для условного форматирования, и выделяет ячейки, значения в которых находятся в этом диапазоне.
- Равно. — Форматирует ячейки значение в которых в точности совпадает с заданным.
- Текст Содержит. — Форматирует ячейки которые содержат текст.
- Дата. — Условное форматирование основанное на дате в ячейке.
- Повторяющиеся Значения. — Быстрое условное форматирование данных с повторяющимися значениями.
Давайте рассмотрим на примере. В данном случае, я выбрал Больше. . Затем я выбрал значение в выпадающем окне. На скриншоте ниже, я установил величину $22,000, и использовал опцию «Зеленая заливка».
Условное форматирование Больше. в Excel.
Как вы можете видеть, Excel применил условное форматирование в режиме просмотра (сразу по ходу задания значений) и ячейки, значения в которых выше $22,000 выделены зеленым цветом.
Это меню отличный способ настроить правила для условного форматирования. Вы можете легко создать правила, как будут форматировать ваши ячейки, зайдя в опцию «Выделения ячеек«.
Применение Правил Отбора Первых и Последних Значений
Другой отличный способ создать условное форматирование опция Правило Отбора Первых и Последних Значений. С его помощью вы можете легко изменить выделение первых и последних 10 ячеек, или создать другое правило.
Щелкните по кнопке Условное Форматирование, выберите Правило Отбора Первых и Последних Значений и выберите одну из опций форматирования.
Правило Отбора Первых и Последних Значений в Excel.
В моем случае, я выберу Выше Среднего что бы выделить все значения продаж, которые выше среднего значения в данных.
Выпадающее окно подобно тому, что было в предыдущем примере, и мы выберем Зеленую Заливку в выпадающем окне.
Я выбрал Зеленую Заливку, что бы выделить зеленым цветом все ячейки в которых значения выше среднего в списке.
Заметьте на скриншоте выше Excel автоматически выделил зеленым цветом ячейки.
2. Используйте Встроенные Стили в Excel
Опции которые мы рассмотрели требуют, что бы в Excel были заданы какие-то правила. Если вам нужен более быстрый способ добавить условное форматирование, попробуйте варианты, о которых мы поговорим ниже.
Использование Гистограмм
Один из самых быстрых способов показать какое значение имеют данные в ваших электронных таблицах — это использовать гистограммы. Опция гистограмм помещает столбики в ваши ячейки, которые показывают «размер» ваших данных.
Выберите градиентную или монотонную заливку для гистограмм.
Что бы применить условное форматирование с гистограммой, выделите сначала ваши данные. Потом найдите опцию Гистограммы и выберите вариант с градиентной или одноцветной заливкой.
На скриншоте ниже показана возможность гистограмм при демонстрации ваших данных. В колонке January 2017, длинной зеленой столбиков обозначается величина данных. Наименьшим значениям, соответствуют самые короткие столбики, в то время как наибольшим — длинные.
Условное форматирование с помощью гистограмм позволяет проиллюстрировать ваши данные прямо в ячейках.
Гистограммы -прекрасная иллюстрация возможностей условного форматирования в Excel. Обратите внимание на сколько легче найти самое большое значение в колонке для Февраля, просто сравнив длины столбиков.
Использование Цветовых Шкал
Цветовые шкалы отлично подходят, чтобы обратить внимание на самые высокие и низкие значения в списке данных. После того как вы выделили данные, вернитесь к Условному Форматированию, и выберите одну из опций из меню Цветовые Шкалы.
Цветовые Шкалы Условного Форматирования в Excel.
Эскизы Цветовых Шкал показывают какие цвета будет применены к вашим данным. Я выбрал первую иконку из списка, и ниже вы можете увидеть результаты моего выбора:
Цветовое условное форматирование выделяет фон ячеек цветом на основе из значений.
В примере выше, результаты с лучшими продажами имеют темно-зеленый фон, в то время как низкие величины — красный. Все другие величины, показывающие успешность продавца, имеют оттенки желтого и зеленого.
Цветовые шкалы в Excel имеют набор различных правил условного форматирования. Попробуйте применить к данным другие варианты, выбрав другие эскизы.
Добавьте Значки (Иконки)
Иконки — другой отличный способ добавить привлекательности вашим электронным таблицам. В моем случае, я собираюсь выделить данные, и выберу стрелки в опциях условного форматирования.
Набор иконок включает в себя стрелки, что бы показать изменения данных, а также другие иконки, такие как флажки, столбики, что бы проиллюстрировать значения прямо в ячейках.
Набор Значков в условном форматировании в Excel.
В данных ниже, у меня есть колонка percent channge ( процентных изменений), которая показывает изменения в производительности продавца по сравнению с предыдущим месяцем. Этот отличный способ представить ваши данные.
Значки в «percent change» помогают быстро оценить данные, и автоматически изменяются при изменении данных.
Все эти встроенные стили предназначены для того, что бы вы могли сделать заключение, быстро пробежавшись по данным.
Во всех случая при условном форматировании отображение меняется при изменении данных. Если данные в продажах изменятся, Excel автоматически изменит иконки, в соответствии с данными.
Обычно, я начинаю с одним из встроенных стилей и изменяю его по моему усмотрению. Гораздо проще использовать встроенное форматирование и потом настроить его, чем создавать свой вариант с нуля.
Подводим Итоги и Продолжаем Обучение
В этом уроке мы разобрали Условное Форматирование в Excel. Вы научились, как задавать стили для ячеек, которые динамически изменяются в зависимости от содержимого.
Суть в том, что: форматирование улучшает читабельность ваших электронных таблиц. А условное форматирование в Excel поможет изменить представление каждой ячейки. Это позволяет вам быстро пробежаться по данным и уловить суть.
Ниже список отличных дополнительных уроков по Excel, которые позволят вам повысить свой уровень владения программой:
- Если вам нужны уроки начального уровня по Excel, то посмотрите тьюториал для начинающих: Как Добавить Элементарную Формулу в Excel .
- Узнайте больше о форматировании в уроке: Как Использовать Формат по Образцу в Excel — Урок за 60 Секунд .
- В этом уроке мы рассматривали условное форматирование, которое изменяет стиль отображения в зависимости от содержания. Но мы также можем использовать условия, что бы изменить данные в ячейках, о чем вы можете узнать из урока: Как Использовать Выражение ЕСЛИ в 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:А11. Необходимо выделить красным числа, которые больше 6. Зеленым – больше 10. Желтым – больше 20.
- 1 способ. Выделяем диапазон А1:А11. Применяем к нему «Условное форматирование». «Правила выделения ячеек» — «Больше». В левое поле вводим число 6. В правом – «красная заливка». ОК. Снова выделяем диапазон А1:А11. Задаем условие форматирования «больше 10», способ – «заливка зеленым». По такому же принципу «заливаем» желтым числа больше 20.
- 2 способ. В меню инструмента «Условное форматирование выбираем «Создать правило».
Заполняем параметры форматирования по первому условию:
Нажимаем ОК. Аналогично задаем второе и третье условие форматирования.
Обратите внимание: значения некоторых ячеек соответствуют одновременно двум и более условиям. Приоритет обработки зависит от порядка перечисления правил в «Диспетчере»-«Управление правилами».
То есть к числу 24, которое одновременно больше 6, 10 и 20, применяется условие «=$А1>20» (первое в списке).
Условное форматирование даты в Excel
Выделяем диапазон с датами.
Применим к нему «УФ» — «Дата».
В открывшемся окне появляется перечень доступных условий (правил):
Выбираем нужное (например, за последние 7 дней) и жмем ОК.
Красным цветом выделены ячейки с датами последней недели (дата написания статьи – 02.02.2016).
Условное форматирование в Excel с использованием формул
Если стандартных правил недостаточно, пользователь может применить формулу. Практически любую: возможности данного инструмента безграничны. Рассмотрим простой вариант.
Есть столбец с числами. Необходимо выделить цветом ячейки с четными. Используем формулу: =ОСТАТ($А1;2)=0.
Выделяем диапазон с числами – открываем меню «Условного форматирования». Выбираем «Создать правило». Нажимаем «Использовать формулу для определения форматируемых ячеек». Заполняем следующим образом:
Для закрытия окна и отображения результата – ОК.
Условное форматирование строки по значению ячейки
Задача: выделить цветом строку, содержащую ячейку с определенным значением.
Таблица для примера:
Необходимо выделить красным цветом информацию по проекту, который находится еще в работе («Р»). Зеленым – завершен («З»).
Выделяем диапазон со значениями таблицы. Нажимаем «УФ» — «Создать правило». Тип правила – формула. Применим функцию ЕСЛИ.
Порядок заполнения условий для форматирования «завершенных проектов»:
Обратите внимание: ссылки на строку – абсолютные, на ячейку – смешанная («закрепили» только столбец).
Аналогично задаем правила форматирования для незавершенных проектов.
В «Диспетчере» условия выглядят так:
Когда заданы параметры форматирования для всего диапазона, условие будет выполняться одновременно с заполнением ячеек. К примеру, «завершим» проект Димитровой за 28.01 – поставим вместо «Р» «З».
«Раскраска» автоматически поменялась. Стандартными средствами Excel к таким результатам пришлось бы долго идти.
Условное форматирование в Microsoft Excel
Условное форматирование в Microsoft Excel – одна из базовых функций, управление которой должны освоить все пользователи, создающие или редактирующие электронные таблицы. С помощью условного форматирования вы можете менять оформление ячеек в зависимости от находящихся в них данных. Это может быть подсветка определенных значений, выделение только некоторых или цветной градиент, переходящий от меньшего к большему числу. Настраивается все это вручную, о чем и пойдет речь далее.
Правила выделения ячеек
«Правила выделения ячеек» отвечают за выделение только тех ячеек, которые соответствуют условию. Условие выбирает сам юзер, как и его диапазон.
Выделите группу ячеек, к которой хотите применить правило, разверните меню «Условное форматирование» и наведите курсор на «Правила выделения ячеек». Названия всех правил соответствуют их действию. Например, при выборе «Больше» правило затронет только те клетки, значение в которых будет больше указанного. Точно так же работают и остальные варианты.
После выбора укажите само число, от которого и должно отталкиваться правило.
Затем разверните список с вариантами подсветок и выберите подходящую. Если среди них нет подходящего цвета, всегда можно нажать на «Пользовательский формат» и выбрать другую заливку или цвет текста.
Как только изменения вступают в силу, вы сразу же видите результат условного форматирования. При этом правило имеет динамический режим работы, то есть при изменении значения в ячейке изменится и ее подсветка (если она подпадает под действие правила).
Если в меню выбора типа правила указать вариант «Другие правила», откроется новое окно, в котором вы можете более детально разобраться с форматированием, выбрать подходящий тип и задать условие. При этом ниже сразу же отобразится образец, от которого стоит отталкиваться при настройке.
С этим правилом все понятно – оно выделяет только конкретные ячейки из списка, попадающие под заданное пользователем условие. Удобно, когда нужно найти в диапазоне определенные числа, дату или текст.
Правила отбора первых и последних значений
Все числа в выбранном диапазоне ячеек имеют последовательность по возрастанию, которая и используется при выборе правила отбора первых и последних значений. Так, вы можете подсвечивать только самые большие или маленькие числа, те, которые выше либо ниже среднего, или попадающие под 10%.
Для этого в том же меню условного форматирования наведите курсор на второе правило и ознакомьтесь с его типами. В предыдущем абзаце я уже перечислил их все, поэтому вам остается выбрать только подходящее. Уточню, что под «Первые 10 элементов» подразумевается не их порядок в таблице, а сам результат, записанный в ячейке.
Хоть правила и называются «Первые 10…», «Последние 10…», количество ячеек для форматирования вы выбираете самостоятельно, указывая подходящий номер в счетчике. После этого нужно развернуть список с вариантами оформления и выбрать среди них подходящий.
Я настроил подсветку первых шести элементов, а это значит, что теперь в моем диапазоне будут подсвечены первые 6 самых больших чисел.
Вариант «Выше среднего» или «Ниже среднего» не имеет дополнительных настроек, поскольку среднее число из диапазона определяется автоматически. Вам остается выбрать только стиль форматирования.
Как видно на следующем изображении, программа сама определила, какие значения выше среднего в диапазоне, и подсветила их выбранным мною цветом.
Применяйте данные типы правил, когда нужно выделить максимальные значения в диапазоне, минимальные или только те, которые превышают среднее число. При финансовых операциях или при учете склада такой стиль условного форматирования будет особенно полезен.
Гистограммы
Рассмотрим следующее правило под названием «Гистограммы». Оно имеет два разных типа, обеспечивающих градиентную или сплошную заливку. Гистограммы появятся на всех ячейках, но их размер напрямую будет зависеть от величины значения в диапазоне.
Наведите курсор на правило «Гистограммы» и выберите подходящий тип оформления. По умолчанию предлагается 12 вариантов.
Никаких дополнительных настроек это правило не имеет, поэтому после применения вы сразу видите сформированные гистограммы – от минимального к максимальному значению диапазона.
Если выбрать вариант «Другое правило», то вы самостоятельно сможете настроить, какие значения стоит задеть и какую заливку к ним применить. Настройки интуитивно понятны, поэтому не стесняйтесь и экспериментируйте с различными стилями оформления.
Цветовые шкалы
По своему действию тип условного форматирования «Цветовые шкалы» имеет некоторые сходства с предыдущим правилом, однако обеспечивает совершенно другое оформление ячеек. Шкалы формируются из разных цветов и по градиенту можно быстро найти минимальное и максимальное значение в диапазоне.
Откройте список всех типов цветовых шкал и выберите среди них подходящий цветовой градиент.
После его применения вы сразу увидите результат. Я выбрал тип условного форматирования – от зеленого к красному цвету. Зеленым подсвечиваются минимальные значения, а красным – максимальные. Соответственно, чем выше число, тем краснее будет цвет, начиная от классического зеленого и переходя к салатовому, желтому и так далее.
На предложенных градиентах варианты оформления не заканчиваются. Вы можете открыть уже знакомое окно с другими правилами, самостоятельно выбрать шкалу и цвета для нее.
Наборы значков
Последний тип правил условного форматирования, довольно интересный в плане визуального оформления, связан с добавлением значков для диапазона значений. Например, если число больше среднего, появится зеленая стрелка вверх, а если меньше – красная вниз. Есть критерии оценок в виде шкал, круговых диаграмм и звездочек. Все это позволяет определить динамику роста и узнать другую полезную информацию в таблице.
Откройте наборы значков и ознакомьтесь со всеми доступными. Присутствуют различные направляющие в виде стрелок, фигуры разных цветов, индикаторы и оценки.
Настроек для этого правила по умолчанию нет, поэтому при выборе значки сразу же появляются в своих клетках. Я выбрал шкалу с несколькими делениями, которые заполняются в зависимости от величины значения в текущем диапазоне. Вы же можете протестировать абсолютно все наборы значков.
В окне настройки данного правила обязательно укажите тип, а затем выберите любой из типов значков и условия, при которых они будут появляться. Это позволит игнорировать некоторые значения, которые вы бы не хотели подсвечивать.
Создание своего правила
Окно создания правил условного форматирования уже фигурировало в предыдущих разделах статьи, однако затрагивались только определенные типы. Сейчас вкратце рассмотрим то, как осуществляется создание правила с нуля.
Разверните «Условное форматирование» и выберите «Создать правило».
Выберите из списка тип правила под ваши условия. Для этого достаточно ознакомиться с его описанием, чтобы найти подходящее.
Затем посмотрите на второй блок с настройками и настройте само условие форматирования. Например, можно указать значение между конкретными числами. 
Для определенных типов форматирования поддерживаются настройки шкалы, градиентов и гистограмм точно в таком же виде, как это было рассмотрено выше.
Примените свое правило и посмотрите, как теперь отображаются выбранные ячейки. Измените значения в некоторых из них для проверки динамики. В любой момент можно вернуться к настройкам и поменять тип условного форматирования.
Подводя итоги, следует отметить, что условное форматирование в Excel – крайне удобная функция, при помощи которой достигается желаемый вид диапазона ячеек с наглядной демонстрацией выбранного условия. Обязательно используйте эту возможность при оформлении таблицы, создавая свои правила или применяя уже существующие.
Как сделать условное форматирование в Excel
В этой статье мы узнаем, как сделать условное форматирование для ячейки, строки и столбца в ms Excel.
Условное форматирование — это автоматический способ выделения данных в зависимости от их значений. Пример условного форматирования показан на картинке ниже, где при помощи цвета сделан акцент на количестве проданных единиц товара — чем “краснее”, тем больше спрос на этот товар, и наоборот.
Условное форматирование можно использовать не только с числовыми, но и текстовыми данными и с датами. Также можно сделать условное форматирование в сводной таблице.
Создание условного форматирования в Excel
Где найти условное форматирование в Excel? Оно находится на вкладке Главная — блок Стили — Условное форматирование.
В выпадающем списке на кнопке Условное форматирование можно выбрать разные виды форматирования — далее в статье мы разберемся в них.
Правила выделения ячеек в Excel при помощи условного форматирования с примерами
Это самый простой способ сделать условное форматирование. Рассмотрим на примере.
Выделим данные, к которым нам нужно применить условное форматирование.
Перейдем в пункт меню Условное форматирование — Правила выделения ячеек. Откроется вложенное меню, в котором можно выбрать правило условного форматирования.
Выберем пункт Больше. “Больше” в данном случае означает, что будут выделены все значения в выделенном диапазоне , которые больше заданного числа.
Укажем пороговое значение (например, 250) и выберем из выпадающего списка справа один из предустановленных форматов. Также можно настроить свой формат в пункте Пользовательский формат .
Аналогично работают и другие стандартные Правила выделения ячеек в Excel:
- Больше — выделяет значения больше указанного значения
- Меньше — соответственно, меньше.
- Между — выделяем значения в диапазоне указанных значений. Причем оба значения входят в этот диапазон.
- Равно — соответственно, ячейки, значения которых равны некому значению.
- Текст содержит — очень удобный способ подсветить ячейки, текст которых содержит определенное слово (или часть слова). Обратите внимание, здесь именно содержит , а не равно.
- Дата — подсветить в списке определенные даты (рассмотрим ниже)
- Повторяющиеся значения — возможность выделить дубликаты
Цветовые шкалы в условном форматировании excel как сделать. Правила использования
Условное форматирование в excel при помощи цветовой шкалы можно сделать, если перейти в меню Условное форматирование — Цветовые шкалы , и далее выбрать нужную шкалу.
Для примера выделим в нашей таблице столбец “Цена за ед.” и перейдем в Условное форматирование — Цветовые шкалы — далее выбрать подходящую цветовую шкалу.
В примере мы выбрали шкалу Красный-Белый-Синий.
Как видите, excel выводит подсказку — оттенок цвета зависит от значения по сравнению с другими ячейками в диапазоне. У нас самые большие значения цены выделены красным, а самые маленькие — синим.
Чтобы не путаться в цветовых шкалах, смотрим на шкалу и понимаем — самые большие значения в выделенном диапазоне будут того цвета, который сверху. Самые маленькие — тем цветом, который снизу.
На примере трехцветной шкалы, самые большие значения будут зеленые, самые маленькие — красные, средние — желтые, а остальные — градиенты этих цветов.
Цветовые шкалы в условном форматировании excel можно настраивать самостоятельно. Для настройки цветовой шкалы нужно щелкнуть на любой ячейке с форматированием и перейти в Условное форматирование — Управление правилами. Далее нужно дважды щелкнуть на нужном правиле, чтобы “провалиться” в него.
И здесь в нижнем блоке можно выбирать из выпадающих списков нужные цвета.
Например, если вы хотите сделать акцент “кто лучше” — не выбирайте красные или красно-синие оттенки шкалы. Лучше выбрать зеленые.
Условное форматирование значками
Чтобы сделать условное форматирование значками (светофор, стрелки, флажки и т.д.), нужно выделить диапазон, перейти в меню Условное форматирование — Наборы значков — выбрать подходящий набор.
Как видите, возле значений появились значки выбранного набора (светофор). Чтобы понять, как работает это правило, нужно “провалиться” в него ( Условное форматирование — Управление правилами — двойной щелчок на привиле).
Что в данном случае означают 67 и 33 процента, если процентов в нашей таблице нет?
В стандартном варианте, зеленый значок присваивается значениям, величина которых составляется больше или равно 67% от максимума.
Красный значок — значениям, величина которых составляет 33% и меньше от максимума.
Желтый — значения между ними.
В формулах на картинке наглядно показано, как рассчитываются пороговые значения.
Пороговые значения по умолчанию можно менять. К тому же, вместо процентов можно выбрать другие величины, например число или процентиль.
Гистограммы в условном форматировании
Еще один вариант использования условного форматирования в Excel — гистограммы.
В примере выделим столбец Выручка, и перейдем в Условное форматирование — Гистограммы — выбрать нужный вид гистограммы.
Гистограммы бывают с градиентной (полупрозрачной) и сплошной заливкой.
Наиболее удачное использование гистограмм — когда данные имеют очень большой разброс.
Если числа находятся в одном небольшом диапазоне, использование гистограмм по умолчанию будет не показательно — в этом случае нужно настраивать пороговые значения вручную внутри правила (по аналоги со значками).
Условное форматирование по значению другой ячейки — как использовать формулу в условном форматировании
Если нужно сделать условное форматирование в Excel с использованием формулы, то можно воспользоваться следующими способами:
Способ 1. При помощи Правила выделения ячеек.
В первом пункте в Правилах выделения ячеек мы рассматривали вариант с фиксированным значением в качестве критерия условного форматирования. Но точно так же критерием можно задать и ссылку на ячейку.
Добавим в ячейку Е1 критерий для выручки — значение 50000, и выделим все ячейки, значение которых меньше 50000.
Для этого выделим столбец Выручка, перейдем в Правила выделения ячеек, в левом поле сотрем числовое значение и, не убирая курсор с этого поля, щелкнем по ячейке Е1 (где находится критерий).
Готово, выделены значения меньше 50000.
Теперь если изменить в ячейке Е1 значение, то условное форматирование автоматически перекрасит ячейки. Укажем в Е1 = 30000 — теперь выделена только одна ячейка, значения которой меньше 30000.
Способ 2. Форматирование в excel — использовать более сложную формулу
Иногда в качестве условия форматирования нужно задать более сложную конструкцию. В этом случае можно использовать формулу для форматирования в excel.
Для форматирования ячейки в excel формулой перейдем в пункт меню Условное форматирование — Создать правило — Использовать формулу для определения форматируемых ячеек — в поле ввода написать формулу.
Например, нужно выделить все наименования, у которых цена выше средней, и при этом выручка более 100 тыс. Должно одновременно выполняться оба условия.
Напишем следующую формулу:
После нажатия кнопки Ок, будут выделены только те наименования, у которых выполняются оба условия.
Условное форматирование по датам
Для примера возьмем таблицу с датами поставок. Задача сделать условное форматирование даты, соответствующей определенному критерию, цветом. Например, нужно выделить цветом поставки, у которых дата наступает завтра.
Для наглядности сверху таблицы выведена сегодняшняя дата (хотя в данном случае в условном форматировании она не участвует).
Выделяем столбец с датами и переходим в меню Условное форматирование — Правила выделения ячеек — Дата .
В окне из выпадающего списка выбираем критерий даты Завтра .
Критериев, которые заданы по умолчанию, иногда недостаточно, чтобы сделать условное форматирование даты. Например, если нужно выделить даты поставок, которые наступят в ближайшие 3 дня, придется воспользоваться формулой.
Меню Условное форматирование — Создать правило — Использовать формулу для определения форматируемых ячеек .
Напишем следующую формулу.
Теперь подсвечены даты, которые больше сегодняшней даты на 3 дня и меньше.
Условное форматирование несколько условий
Чтобы сделать условное форматирование и использовать несколько условий, можно воспользоваться способом Правила выделения ячеек или способом с Условным форматирование формулой.
Рассмотрим на примере Правила выделения ячеек.
Допустим, нужно выделить желтым цветом цены товаров выше 100 руб, и красным — цены выше 200 руб.
Выделяем диапазон ячеек и переходим в меню Условное форматирование — Правила выделения ячеек — Больше. Указываем значение 100 и выбираем формат.
Выделены все ячейки, т.к.все цены больше 100 руб.
Чтобы выделить цены выше 200 руб, снова идем в меню по тому же пути и указываем уже 200, выбрав красный формат.
Цены выше 200 руб. выделены красным.
А теперь перейдем к главной хитрости условного форматирования с несколькими условиями — очередность правил (здесь делают ошибки все новички).
Перейдем в меню Условное форматирование — Управление правилами .
Правило, которое выделяет ячейки более 200 находится сверху — это значит, что у него приоритет над правилом “более 100”.
Но что будет, если мы поменяем очередность правил? Это можно сделать, выделив правило и нажав стрелочку сверху.
Как видите, правила все те же, изменился только порядок — но теперь все значения выделены только желтым цветом.
Причина тому — правило “более 100” теперь имеет приоритет над правилом “более 200”, и отрабатывается в первую очередь. А правило “более 200” сработает только для тех значений, которые не попали под критерий “более 100”. А так как все значения в таблице попадают под первый критерий “более 100”, до второго правила алгоритм просто не доходит.
Именно поэтому, если вы делаете условное форматирование в Excel с несколькими условиями, нужно обязательно следить за очередностью правил.
Как скопировать условное форматирование
Скопировать условное форматирование можно при помощи инструмента Формат по образцу , который находится на вкладке Главная .
Если скопировать ячейки, к которым применено условное форматирование, то оно скопируется вместе с ячейками.
Как убрать условное форматирование из ячейки и со всего листа
Чтобы убрать условное форматирование только из определенных ячеек, нужно выделить эти ячейки и перейти в меню Условное форматирование — Удалить правила — Удалить правила из выделенных ячеек .
Если нужно убрать условное форматирование со всего листа, в том же пункте меню выбираем Удалить правила со всего листа .
Где исправить диапазон условного форматирования
Для примера возьмём ту же таблицу, в которой мы выделяли даты “плюс 3 дня от сегодняшней даты”. Добавим новую строку, дата которой также соответствует критерию условного форматирования.
Но для новой строки условное форматирование не сработало — дата не выделена цветом.
Причина — новая строка не попадает в ранее заданный диапазон.
Чтобы исправить диапазон условного форматирования:
— щелкнем на любой другой ячейке, для которой форматирование работает
— перейдем в меню Условное форматирование — Управление правилами
Если щелкнуть в поле Применяется к напротив нужного правила, то будет выделен бегущим выделением диапазон форматирования — новая ячейка в него не попала.
Чтобы это исправить, сотрем содержимое поля Применяется к и, не выходя из него, выделим новый диапазон, захватив новую ячейку.
Теперь для новой ячейки условное форматирование сработало.
В этой статье мы узнали, как сделать условное форматирование в Excel и познакомились с основными его видами.