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

29.06.2020

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

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

Автоформатирование

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

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

Переход к форматированию

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

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

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

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

Форматирование данных

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

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

Числовой;
Текстовый;
Время;
Дата;
Денежный;
Общий и т.д.

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

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

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

Аналогичные настройки имеются и у формата «Время» .

Если выбрать пункт «Все форматы» , то в одном списке будут показаны все доступные подтипы форматирования данных.

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

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

Выравнивание

Целый блок инструментов представлен во вкладке «Выравнивание» в окне «Формат ячеек» .

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

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

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

Блок инструментов «Выравнивание» имеется так же на ленте во вкладке «Главная» . Там представлены все те же возможности, что и в окне «Формат ячеек» , но в более усеченном варианте.

Шрифт

Во вкладке «Шрифт» окна форматирования имеются широкие возможности по настройке шрифта выделенного диапазона. К этим возможностям относятся изменение следующих параметров:

тип шрифта;
начертание (курсив, полужирный, обычный)
размер;
цвет;
видоизменение (подстрочный, надстрочный, зачеркнутый).

На ленте тоже имеется блок инструментов с аналогичными возможностями, который также называется «Шрифт» .

Граница

Во вкладке «Граница» окна форматирования можно настроить тип линии и её цвет. Тут же определяется, какой граница будет: внутренней или внешней. Можно вообще убрать границу, даже если она уже имеется в таблице.

А вот на ленте нет отдельного блока инструментов для настроек границы. Для этих целей во вкладке «Главная» выделена только одна кнопка, которая располагается в группе инструментов «Шрифт» .

Заливка

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

На ленте, как и для предыдущей функции для заливки выделена всего одна кнопка. Она также размещается в блоке инструментов «Шрифт» .

Если представленных стандартных цветов вам не хватает и вы хотите добавить оригинальности в окраску таблицы, тогда следует перейти по пункту «Другие цвета…» .

После этого открывается окно, предназначенное для более точного подбора цветов и оттенков.

Защита

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

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

.
Урок: Как защитить ячейку от изменений в 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

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

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

Введем в диапазон А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.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

При применении условного форматирования внешний вид ячеек изменяется на основании указанных вами условий. Если условия выполнены, то форматирование применяется к диапазону ячеек. Если условия не выполнены, форматирование не применяется. Существует множество встроенных условий, а также вы можете создавать собственные условия (в том числе и с помощью формулы, результатом которой становится значение "Истина" или "Ложь").

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

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

Условное форматирование можно применить к диапазону ячеек (выделенному или именованному), к таблице Excel и даже к отчету сводной таблицы в Excel для Windows. Обратите внимание на ряд дополнительных факторов, действующих на условное форматирование в отчете сводной таблицы - см. раздел Применение условного форматирования в отчете сводной таблицы ниже на вкладке Windows.

Скачивание образца книги

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

Форматирование ячеек с помощью двухцветной шкалы

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

Совет: ЕСТЬ или ЕСЛИОШИБКА для возврата значения, отличного от ошибки.

Быстрое форматирование

Параметры форматирования

Расширенное форматирование

На вкладке Главная в группе Стили и выберите пункт Управление правилами . Откроется диалоговое окно .

Создать правило . Откроется диалоговое окно .
1. Свернуть диалоговое окно в поле Применяется к Развернуть диалоговое окно .
  
  Изменить правило . Откроется диалоговое окно .

В разделе Применить правило

к выделенным ячейкам , чтобы выбрать ячейки по выделению;

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

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

В разделе Выберите тип правила выберите пункт (по умолчанию).

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

В полях Тип параметров Минимальное значение и Максимальное значение выберите их типы.

Выберите минимальное значение и максимальное значение .

В этом случае не вводите минимальное и максимальное значения .

Выберите элемент Число , а затем введите минимальное и максимальное значения .

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

Форматирование процентиля. Выберите элемент Процентиль Минимальное значение и Максимальное значение

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

Выберите элемент Формула , а затем введите минимальное и максимальное значения.
- Примечания:
  - Убедитесь, что минимальное значение меньше, чем максимальное .
    
    Для параметров Минимальное значение и Максимальное значение минимального значения и процентный тип для максимального значения .

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

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

Форматирование ячеек с помощью трехцветной шкалы

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

Быстрое форматирование

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

Расширенное форматирование

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

На вкладке Главная в группе Стили щелкните стрелку рядом с кнопкой и выберите пункт Управление правилами . Откроется диалоговое окно Диспетчер правил условного форматирования .

Выполните одно из указанных ниже действий.

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

Для изменения условного форматирования выполните указанные ниже действия.
1. Убедитесь, что в поле со списком Показать правила форматирования для выбран соответствующий лист, таблица или отчет сводной таблицы.
  
  При необходимости вы можете изменить диапазон ячеек. Для этого нажмите кнопку Свернуть диалоговое окно в поле Применяется к , чтобы временно скрыть диалоговое окно. Затем выделите новый диапазон ячеек на листе и нажмите кнопку Развернуть диалоговое окно .
  
  Выберите правило, а затем нажмите кнопку Изменить правило . Откроется диалоговое окно Изменение правила форматирования .

В разделе Применить правило выберите один из следующих вариантов, чтобы изменить выбор полей в области значений отчета сводной таблицы:

Выберите только эти ячейки ;

Выберите все ячейки <поле значения> с теми же полями ;

Выберите все ячейки <поле значения> .

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

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

Выберите тип параметров Минимальное значение , Среднее значение и Максимальное значение

Форматирование минимального и максимального значений. Выберите элемент Среднее значение .

В этом случае не вводите минимальное и максимальное значения .

Форматирование числового значения, значения даты или времени. Выберите элемент Число , а затем введите минимальное , среднее и максимальное значения.

Форматирование процентного значения. Выберите элемент Процент , а затем введите минимальное , среднее и максимальное значения. Допустимыми являются значения от 0 (нуля) до 100. Не вводите знак процента.

Форматирование процентиля. Выберите элемент Процентиль , а затем введите Минимальное значение , Среднее значение и Максимальное значение .

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

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

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

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

Для выбора цветовой шкалы Минимальное значение , Среднее значение и Максимальное значение щелкните элемент Цвет для каждого из них, а затем выберите цвет.

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

Выбранная цветовая шкала появится в окне предварительного просмотра .

Форматирование ячеек с помощью гистограмм

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

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

Совет: Если какая-либо из ячеек в диапазоне содержит формулу, возвращающую ошибку, условное форматирование к ней не применяется. Чтобы гарантировать применение условного форматирования к этим ячейкам, воспользуйтесь функцией ЕСТЬ или ЕСЛИОШИБКА

Быстрое форматирование

Расширенное форматирование

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

Выполните одно из указанных ниже действий.

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

Для изменения условного форматирования выполните указанные ниже действия.
1. Убедитесь, что в поле со списком Показать правила форматирования для выбран соответствующий лист, таблица или отчет сводной таблицы.
  
  При необходимости вы можете изменить диапазон ячеек. Для этого нажмите кнопку Свернуть диалоговое окно в поле Применяется к , чтобы временно скрыть диалоговое окно. Затем выделите новый диапазон ячеек на листе и нажмите кнопку Развернуть диалоговое окно .
  
  Выберите правило, а затем нажмите кнопку Изменить правило . Откроется диалоговое окно Изменение правила форматирования .

чтобы выбрать поля по выделению, выберите только эти ячейки ;

чтобы выбрать поля по соответствующему полю, выберите все ячейки <поле значения> с теми же полями ;

чтобы выбрать поля по полю значения, выберите все ячейки <поле значения> .

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

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

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

Форматирование минимального и максимального значений. Выберите Минимальное значение и Максимальное значение .

В этом случае не вводите Минимальное значение и Максимальное значение .

Форматирование числового значения, значения даты или времени. Выберите элемент Число , а затем введите минимальное и максимальное значения .

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

Допустимыми являются значения от 0 (нуля) до 100. Не вводите знак процента.

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

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

Допустимыми являются значения процентилей от 0 (нуля) до 100.

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

Форматирование результата формулы. Выберите элемент Формула , а затем введите значения параметров Минимальное значение и Максимальное значение .
- Формула должна возвращать число, дату или время.
  
  Начинайте ввод формулы со знака равенства (= ).
  
  Недопустимая формула не позволит применить форматирование.
  
  Проверьте формулу, чтобы убедиться, что она не возвращает значение ошибки.

Примечания:

Убедитесь, что минимальное значение меньше, чем максимальное .

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

Для выбора цветовых шкал Минимальное значение и Максимальное значение выберите пункт Цвет столбца .

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

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

Чтобы применить к столбцам гистограммы сплошную границу, выберите в списке Граница вариант Сплошная граница , а затем выберите цвет границы.

Чтобы сделать столбцы сплошными или градиентными, выберите в списке Заливка вариант Сплошная заливка или Градиентная заливка .

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

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

Форматирование ячеек с помощью набора значков

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

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

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

Совет: Если какие-либо из выделенных ячеек содержат формулу, возвращающую ошибку, условное форматирование не применяется к этим ячейкам. Чтобы гарантировать применение условного форматирования к этим ячейкам, воспользуйтесь функцией ЕСТЬ или ЕСЛИОШИБКА для возврата значения (например, 0 или "Н/Д"), отличного от ошибки.

Быстрое форматирование

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

Расширенное форматирование

Выполните одно из указанных ниже действий.

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

Для изменения условного форматирования выполните указанные ниже действия.
1. Убедитесь, что в поле со списком Показать правила форматирования для выбран соответствующий лист, таблица или отчет сводной таблицы.
  
  При необходимости вы можете изменить диапазон ячеек. Для этого нажмите кнопку Свернуть диалоговое окно в поле Применяется к , чтобы временно скрыть диалоговое окно. Затем выделите новый диапазон ячеек на листе и нажмите кнопку Развернуть диалоговое окно .
  
  Выберите правило, а затем нажмите кнопку Изменить правило . Откроется диалоговое окно Изменение правила форматирования .

чтобы выбрать поля по выделению, выберите только эти ячейки ;

чтобы выбрать поля по соответствующему полю, выберите все ячейки <поле значения> с теми же полями ;

чтобы выбрать поля по полю значения, выберите все ячейки <поле значения> .

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

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

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

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

Выполните одно из указанных ниже действий.
- Форматирование числового значения, значения даты или времени. Выберите элемент Число .
  
  Форматирование процентного значения. Выберите элемент Процент .
  
  Допустимыми являются значения от 0 (нуля) до 100. Не вводите знак процента.
  
  Используйте процентные значения, если необходимо визуализировать все значения пропорционально, поскольку распределение значений в этом случае является пропорциональным.
  
  Форматирование процентиля. Выберите элемент Процентиль . Допустимыми являются значения процентилей от 0 (нуля) до 100.
  
  Используйте процентили, если необходимо визуализировать группу максимальных значений (например, верхнюю 20ю процентиль) с помощью одного значка, а группу минимальных значений (например, нижнюю 20ю процентиль) - с помощью другого, поскольку они соответствуют экстремальным значениям, которые могут сместить визуализацию данных.
  
  Форматирование результата формулы. Выберите элемент Формула , а затем введите формулы в каждое поле Значение .
  - Формула должна возвращать число, дату или время.
    
    Начинайте ввод формулы со знака равенства (=).
    
    Недопустимая формула не позволит применить форматирование.
    
    Проверьте формулу, чтобы убедиться, что она не возвращает значение ошибки.

Чтобы первый значок соответствовал меньшим значениям, а последний - большим, выберите параметр Обратный порядок значков .

Для отображения только значка, но не значения в ячейке, выберите параметр Показать только значок .

Примечания:

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

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

Форматирование ячеек, содержащих текст, числовые значения, дату или время

Чтобы удобнее находить определенные ячейки, можно отформатировать их с помощью оператора сравнения. Например, в инвентарном перечне, отсортированном по категориям, можно выделить желтым цветом товары, которых осталось менее 10 штук. В сводном листе розничного магазина можно выделить все магазины с доходом более 10 %, с объемом продаж менее 100 000 долларов и с регионом "Юго-восток".

В приведенных здесь примерах демонстрируются встроенные критерии условного форматирования, такие как "больше чем" и "%_лучших". Города с численностью населения более 2 000 000 выделяются зеленым цветом фона, а 30 % лучших (самых высоких) показателей среднемесячной максимальной температуры - оранжевым.

Примечание: Невозможно применить условное форматирование к полям в области "Значение" отчета сводной таблицы по тексту или по дате (можно только по числам).

Быстрое форматирование

Если вы хотите посмотреть видео об этих методах, см. Видео: условное форматирование текста и Видео: условное форматирование дат .

Расширенное форматирование

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

На вкладке Главная в группе Стили щелкните стрелку рядом с кнопкой Условное форматирование и выберите пункт Управление правилами. Откроется диалоговое окно Диспетчер правил условного форматирования .

Выполните одно из указанных ниже действий.

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

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

чтобы выбрать поля по выделению, выберите только эти ячейки ;

чтобы выбрать поля по соответствующему полю, выберите все ячейки <поле значения> с теми же полями ;

чтобы выбрать поля по полю значения, выберите все ячейки <поле значения> .

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

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

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

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

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

Быстрое форматирование

Расширенное форматирование

Быстрое форматирование

Расширенное форматирование

Форматирование только уникальных или повторяющихся значений

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

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

Быстрое форматирование

Расширенное форматирование

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

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

Выбор ячеек для форматирования с помощью формулы

Если при создании собственного правила условного форматирования отсутствуют нужные варианты, можно использовать логическую формулу, чтобы задать условия форматирования. Например, можно сравнить значения в выбранных ячейках с результатом, возвращенным функцией, или оценить данные в ячейках за пределами выделенного диапазона, которые могут находиться на другом листе этой же книги. Формула должна возвращать значение Истина или Ложь (1 или 0), но вы можете использовать условную логику для объединения набора соответствующих условных форматов, таких как различные цвета для каждого из небольших наборов текстовых значений (например, названия категорий товаров).

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

Совет: Если какие-либо ячейки содержат формулу, возвращающую ошибку, условное форматирование не применяется к таким ячейкам. Для решения этой проблемы используйте в формуле функции ЯВЛЯЕТСЯ или функцию ЕСЛИОШИБКА , чтобы возвращать указанное вами значение (например, 0 или "Н/П") вместо значения ошибки.

На вкладке Главная в группе Стили щелкните стрелку рядом с кнопкой и выберите пункт Управление правилами .

Откроется диалоговое окно Диспетчер правил условного форматирования .

Выполните одно из указанных ниже действий.

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

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

В разделе Применить правило выполните одно из следующих действий, чтобы изменить выбор полей в области значений отчета сводной таблицы:

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

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

Чтобы определить область по полю "Значение" , выберите ко всем ячейкам, содержащим значения <поле значения> для <строка> .

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

В следующем видео показаны основы использования формул с условным форматированием.

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

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

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

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

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

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

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

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

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

Диспетчер правил условного форматирования . (Для этого на вкладке Главная выберите команду и выберите пункт Управление правилами .)

Откроется диалоговое окно Диспетчер правил условного форматирования .

При применении нескольких правил условного форматирования приоритет их проверки определяется порядком их расположения в этом диалоговом окне (сверху вниз).

СЕГОДНЯ =B2<СЕГОДНЯ () Применяется к =B2<СЕГОДНЯ()+60

Вверх и Вниз .

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

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

Диспетчер правил условного форматирования

Управление правилами установить флажок Остановить, если истина

Остановить, если истина
Установить или снять флажок Остановить, если истина

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

Очистка условного форматирования

Очистка условного форматирования на листе

На вкладке Главная щелкните > Удалить правила > .

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

На всем листе

На вкладке Главная щелкните > Удалить правила > Удалить правила со всего листа .

В диапазоне ячеек

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

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

На вкладке Главная щелкните стрелку рядом с кнопкой Найти и выделить и выберите команду Выделить группу ячеек .

Выберите параметр Условные форматы .

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

На вкладке Главная нажмите кнопку , выберите пункт Удалить правила , а затем - .

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

Можно найти максимальное и минимальное значения в диапазоне ячеек на основе указанного порогового значения. Например, можно найти 5 самых популярных продуктов в региональном отчете, 15 % наименее популярных продуктов в клиентском опросе или 25 самых высоких зарплат в отчете по персоналу отдела.

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

На вкладке Главная в группе Таблицы выберите .

Выберите нужную команду, например Первые 10 элементов или Последние 10 % .

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

Форматирование только значений, находящихся выше или ниже среднего

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

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

На вкладке Главная в группе Таблицы выберите пункт Правила отбора первых и последних значений .

Копирование и вставка условного форматирования Управление правилами условного форматирования
При использовании условного форматирования вы задаете правила, с помощью которых Excel определяет, когда следует применять условное форматирование. Для управления этими правилами важно понимать последовательность, в которой эти правила проверяются, и действия в случае конфликта нескольких из них. Также следует понимать, как копирование и вставка влияют на проверку правил, следует знать, как изменять последовательность этой проверки и когда ее следует останавливать.
Изменение порядка проверки правил условного форматирования

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

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

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

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

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