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

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

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

Инструмент «Условное форматирование» находится на главной странице в разделе «Стили».

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

Сравним числовые значения в диапазоне Excel с числовой константой. Чаще всего используются правила «больше / меньше / равно / между». Поэтому они вынесены в меню «Правила выделения ячеек».

Введем в диапазон А1:А11 ряд чисел:

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

Введем в левое поле число 15. В правое – способ выделения значений, соответствующих заданному условию: «больше 15». Сразу виден результат:

Выходим из меню нажатием кнопки ОК.

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

Сравним значения диапазона А1:А11 с числом в ячейке В2. Введем в нее цифру 20.

Выделяем исходный диапазон и открываем окно инструмента «Условное форматирование» (ниже сокращенно упоминается «УФ»). Для данного примера применим условие «меньше» («Правила выделения ячеек» - «Меньше»).

Результат форматирования сразу виден на листе Excel.

Значения диапазона А1:А11, которые меньше значения ячейки В2, залиты выбранным фоном.

Зададим условие форматирования: сравнить значения ячеек в разных диапазонах и показать одинаковые. Сравнивать будем столбец А1:А11 со столбцом В1:В11.

Выделим исходный диапазон (А1:А11). Нажмем «УФ» - «Правила выделения ячеек» - «Равно». В левом поле – ссылка на ячейку В1. Ссылка должна быть СМЕШАННАЯ или ОТНОСИТЕЛЬНАЯ! , а не абсолютная.

Каждое значение в столбце А программа сравнила с соответствующим значением в столбце В. Одинаковые значения выделены цветом.

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

В нашем примере в момент вызова инструмента была активна ячейка А1. Ссылка $B1. Следовательно, Excel сравнивает значение ячейки А1 со значением В1. Если бы мы выделяли столбец не сверху вниз, а снизу вверх, то активной была бы ячейка А11. И программа сравнивала бы В1 с А11.

Сравните:

Чтобы инструмент «Условное форматирование» правильно выполнил задачу, следите за этим моментом.

Проверить правильность заданного условия можно следующим образом:

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

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

Условное форматирование – несколько условий

Исходный диапазон – А1:А11. Необходимо выделить красным числа, которые больше 6. Зеленым – больше 10. Желтым – больше 20.

Заполняем параметры форматирования по первому условию:

Нажимаем ОК. Аналогично задаем второе и третье условие форматирования.

Обратите внимание: значения некоторых ячеек соответствуют одновременно двум и более условиям. Приоритет обработки зависит от порядка перечисления правил в «Диспетчере»-«Управление правилами».

То есть к числу 24, которое одновременно больше 6, 10 и 20, применяется условие «=$А1>20» (первое в списке).

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Правила отбора первых и последних значений.
Цветовые шкалы.
Дополнительно: создать, удалить, управление правилами.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Инструмент «Условное форматирование» находится на главной странице в разделе «Стили».

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

Введем в диапазон А1:А11 ряд чисел:

Выходим из меню нажатием кнопки ОК.

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

Сравним значения диапазона А1:А11 с числом в ячейке В2. Введем в нее цифру 20.

Результат форматирования сразу виден на листе Excel.

Значения диапазона А1:А11, которые меньше значения ячейки В2, залиты выбранным фоном.

Сравните:

Чтобы инструмент «Условное форматирование» правильно выполнил задачу, следите за этим моментом.

Проверить правильность заданного условия можно следующим образом:

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

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

Условное форматирование – несколько условий

1 способ. Выделяем диапазон А1:А11. Применяем к нему «Условное форматирование». «Правила выделения ячеек» - «Больше». В левое поле вводим число 6. В правом – «красная заливка». ОК. Снова выделяем диапазон А1:А11. Задаем условие форматирования «больше 10», способ – «заливка зеленым». По такому же принципу «заливаем» желтым числа больше 20.
2 способ. В меню инструмента «Условное форматирование выбираем «Создать правило».

Заполняем параметры форматирования по первому условию:

Нажимаем ОК. Аналогично задаем второе и третье условие форматирования.

То есть к числу 24, которое одновременно больше 6, 10 и 20, применяется условие «=$А1>20» (первое в списке).

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Приведем простейший пример сводной таблицы, описывающий объемы продаж какого-то товара по регионам:

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

Самый простой вариант – использовать цветовые шкалы. Для этого выделяем поле «Объем продаж», охватывая все периоды. Осталось открыть вкладку «Главная», где нажимаем кнопку «Условное форматирование» (если вы вдруг используете английскую версию, то данная функция называется «Conditional Formatting»). Наведите курсор на «Гистограммы».

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

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

Итак – готовые сценарии, способные помочь в большинстве ситуаций:

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

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

Первые 10%;

Последние 10%;

Больше среднего;

Меньше среднего.

Удаление уже используемого условного форматирования в Excel 2010 происходит по следующей схеме: в сводной таблице переходим на вкладку «Главная», нажимаем на «Условное форматирование», далее «Стили», и в выпадающем меню используем команду «Удалить правила» - «Удалить правила из этой сводной таблицы» (в английском варианте – «Clear Rules» и «Clear Rules from this PivotTable»).

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

Данная таблица – усложненный вариант первой, так что сразу переходим к примеру. Давайте отследим объем продаж и выручку за час. Мы будем использовать условное форматирование Excel 2010 для ускорения поиска совпадений и различий. Выделяем «Объем продаж». Далее по стандартной процедуре активируем сценарий («Главная» - «Условное форматирование»), но выбираем не готовый вариант, а функцию «Создать правило» (или «New Rule»).

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

Выделенные («Selected Cells»);

Входящие в столбец «Объем продаж» («All Cells Showing «Sales_Amount» Values»), включая промежуточные и общие итоги. Данный вариант, кстати, хорошо подходит для анализа тех данных, которые требуют определения среднего, процентного соотношения или иными величинами, так или иначе являющимися разными уровнями одной величины;

Входящие в категорию «Объем продаж» только для «Рынка сбыта» («All Cells Showing «Sales_Amount» Values for «Market»»). Данный вариант полностью исключает общие и промежуточные итоги, что удобно для анализа некоторых отдельных значений.

Отметим, что команды «Объем продаж», «Рынок сбыта» при создании правил меняются в зависимости от имеющихся рабочих таблиц.

По нашему примеру самым выгодным является вариант «3», поэтому используется такой вариант:

При выборе правила (раздел «Выбрать правило» или «Select a Rule Туре») указываем именно то, которое и отвечает нашим требованиям.

Это может быть:

- «Форматирование ячеек на основании значений» («Format All Cells Based on Their Values»). Используется для форматирования ячеек, которые соответствуют используемому диапазону значений. Лучше всего подходит для определения самых разных отклонений, если приходиться работать с огромным набором данных.

- «Форматирование ячеек содержащих» («Format Only Cells That Contain»). Форматирует ячейки, отвечающие подходящим условиям. В данном случае сравнение значений форматированных ячеек с обычными не происходит. Используется для сравнения общего набора данных с указанной ранее характеристикой.

- «Форматирование первых и последних значений» («Format Only Top or Bottom Ranked Values»).

- «Форматировать значения ниже или выше среднего» («Format Only Values That Are Above or Below the Average»).

- «Использовать формулу определения форматируемых ячеек» («Use a Formula to Determine Which Cells to Format»). Здесь уже условия условного форматирования опираются на формулу, заданную самим пользователем. Если значение ячейки (из подставленных в формулу) приходит со значением «true», то к ячейке применяют форматирование. В случае со значением «false» форматирование не применяется.

Применение гистограмм, наборов значков и цветовых шкал возможно только тогда, когда форматирование выделенных ячеек происходит на основании значений, занесенных в них. Для этого устанавливаем первый переключатель на «Форматирование всех ячеек на основании значений» («Format All Cells Based on Their Values»). Для обозначения проблемных областей можно использовать набор значков, что также хорошо подходит для данного сценария.

Ну и осталось определить точные параметры нашего форматирования. Здесь пригодится раздел «Изменения описания правила» («Edit the Ruie Description»). Для добавления значков в проблемные ячейки, мы используем выпадающее меню «Стиль формата» («Format Style») и выбираем «Наборы значков» («Icon Sets»).

В списке «Стиля значка» остается выбрать значение «3 знака» Это хорошо подойдет, если имеющуюся таблицу невозможно полностью раскрасить. В результате в окошке у нас должно получиться следующее:

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

>=67, >=33 и

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

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

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

После этого, открывается меню условного форматирования. Тут представляется три основных вида форматирования:

Гистограммы;
Цифровые шкалы;
Значки.

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

Как видим, гистограммы появились в выделенных ячейках столбца. Чем большее числовое значение в ячейках, тем гистограмма длиннее. Кроме того, в версиях Excel 2010, 2013 и 2016 годов, имеется возможность корректного отображения отрицательных значений в гистограмме. А вот, у версии 2007 года такой возможности нет.

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

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

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

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

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

Кликаем по пункту меню «Правила выделения ячеек». Как видим, существует семь основных правил:

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

Рассмотрим применение этих действий на примерах. Выделим диапазон ячеек, и кликнем по пункту «Больше…».

Открывается окно, в котором нужно установить, значения больше какого числа будут выделяться. Делается это в поле «Форматировать ячейки, которые больше». По умолчанию, сюда автоматически вписывается среднее значение диапазона, но можно установить любое другое, либо же указать адрес ячейки, в которой содержится это число. Последний вариант подойдёт для динамических таблиц, данные в которых постоянно изменяются, или для ячейки, где применяется формула. Мы для примера установили значение в 20000.

В следующем поле, нужно определиться, как будут выделяться ячейки: светло-красная заливка и темно-красный цвет (по умолчанию); желтая заливка и темно-желтый текст; красный текст, и т.д. Кроме того, существует пользовательский формат.

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

После того, как мы определились, со значениями в окне настройки правил выделения, жмём на кнопку «OK».

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

По такому же принципу выделяются значения при применении правил «Меньше», «Между» и «Равно». Только в первом случае, выделяются ячейки меньше значения, установленного вами; во втором случае, устанавливается интервал чисел, ячейки с которыми будут выделяться; в третьем случае задаётся конкретное число, а выделяться будут ячейки только содержащие его.

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

Правило «Дата» применяется к ячейкам, которые содержат значения в формате даты. При этом, в настройках можно установить выделение ячеек по тому, когда произошло или произойдёт событие: сегодня, вчера, завтра, за последние 7 дней, и т.д.

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

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

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

Первые 10 элементов;
Первые 10%;
Последние 10 элементов;
Последние 10%;
Выше среднего;
Ниже среднего.

Но, после того, как вы кликнули по соответствующему пункту, можно немного изменить правила. Открывается окно, в котором производится выбор типа выделения, а также, при желании, можно установить другую границу отбора. Например, мы, перейдя по пункту «Первые 10 элементов», в открывшемся окне, в поле «Форматировать первые ячейки» заменили число 10 на 7. Таким образом, после нажатия на кнопку «OK», будут выделяться не 10 самых больших значений, а только 7.

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

Выше мы говорили о правилах, которые уже установлены в программе Excel, и пользователь может просто выбрать любое из них. Но, кроме того, при желании, пользователь может создавать свои правила.

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

Открывается окно, где нужно выбрать один из шести типов правил:

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Кнопка «Условное форматирование» находится на вкладке «Главная» в группе «Стили» .

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

Выделение ячеек

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

Пример

Сравним все числа в выбранном диапазоне и если есть повторы, закрасим блоки с ними в определенный цвет. Нажимаем «Условное форматирование» – «Правила выделения ячеек» – «Повторяющиеся значения» . В списке выбираем «повторяющиеся» и тип заливки. Теперь все повторы в столбце выделены цветом. Как видите, в примере несколько раз встречаются шестерки и восьмерки.

Теперь давайте сравним данные в первом диапазоне со вторым, и если число в первом будет меньше, выделим прямоугольничек цветом. Выбираем из списка «Меньше» . Дальше делаем относительную ссылку на второй столбец: кликаем мышкой по первому числу. Доллар перед F значит, что сравнивать будем именно с этим столбцом, но в разных ячейках. В результате, все блоки в первом столбце, где числа записаны меньше, чем во втором, выделены цветом.

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

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

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

Пример

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

Гистограммы

Они показывают информацию в блоке в виде гистограммы. Ячейка принимается за 100%, которому соответствует максимальное число в выбранном диапазоне. Если значение в блоке будет отрицательное – гистограмма делится на половину, имеет другую направленность и цвет.

Пример

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

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

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

Внизу окна можно изменить описание для него. Ставим «Минимальное значение» – «Число» , и в поле «Значение» пишем «5» . Если Вы не хотите, чтобы в ячейках отображались числа, поставьте галочку в пункте «Показывать только столбец» . Здесь же можно изменить цвет и тип заливки.

В результате минимальное число для выделенных ячеек «5» , а максимальное выбирается автоматически. Как видно в примере, в блоках, где число меньше пяти: 4, -7, -8, или равно ему гистограмма просто не отображается.

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

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

Если открыть окно «Изменение правила форматирования» , как описано в предыдущем пункте, можно выбрать «Стиль формата» , «Цвет» заливки, максимальное и минимальное значение для выбранного диапазона.

Например, в поле «Минимальное значение» я поставила «3» . Выбранная область будет выглядеть следующим образом – блоки, значения в которых ниже 4-ох будут просто не закрашены.

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

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

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

Как удалить

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

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

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

Пример

Предположим, есть небольшая табличка, которая представлена на рисунке выше. Создадим для нее различные правила. Если числа в диапазоне выше «0» – закрасим блоки в желтый цвет, выше «10» – в зеленый, выше «18» – в красный.

Для начала, нужно выбрать тип – «Форматировать только ячейки, которые содержат» . Теперь в поле «Измените описание правила» задаем значение, выбираем цвет ячейки и нажимаем «ОК» . Создаем, таким образом, три правила для выделенного диапазона.

Таблица в примере отформатирована следующим образом.

Как управлять правилами

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