Правила условного форматирования в Excel. Чтобы изменить выравнивание текста по вертикали

Смотря на сухие цифры таблиц, трудно с первого взгляда уловить общую картину, которую они представляют. Но, в программе 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 с числовой константой. Чаще всего используются правила «больше / меньше / равно / между». Поэтому они вынесены в меню «Правила выделения ячеек».

Введем в диапазон А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 2007 они вынесены в отдельное меню Правила выделения ячеек .

Эти правила также же доступны через меню Главная/ Стили/ Условное форматирование/ Создать правило, Форматировать только ячейки, которые содержат .

Рассмотрим несколько задач:

СРАВНЕНИЕ С ПОСТОЯННЫМ ЗНАЧЕНИЕМ (КОНСТАНТОЙ)

Задача1 A1:D1 с числом 4.

введем в диапазон A1:D1 значения 1, 3, 5, 7
выделим этот диапазон;
Условное форматирование на значение Меньше ();
в левом поле появившегося окна введем 4 – сразу же увидим результат применения Условного форматирования .
Нажмем ОК.

Задача1 .

СРАВНЕНИЕ СО ЗНАЧЕНИЕМ В ЯЧЕЙКЕ (АБСОЛЮТНАЯ ССЫЛКА)

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

Задача2 . Сравним значения из диапазона A1:D1 с числом из ячейки А2 .

введем в ячейку А2 число 4;
выделим диапазон A1:D1 ;
применим к выделенному диапазону Условное форматирование на значение Меньше (Главная/ Стили/ Условное форматирование/ Правила выделения ячеек/ Меньше );
в левом поле появившегося окна введем ссылку на ячейку A2 нажав на кнопочку, расположенную в правой части окна (EXCEL по умолчанию использует ссылку $А$2 ).

Нажмите ОК.

В результате, все значения из выделенного диапазона A 1: D 1 будут сравниваться с одной ячейкой $А$2 . Те значения из A 1: D 1 , которые меньше A 2 будут выделены заливкой фона ячейки.

Результат можно увидеть в файле примера на листе Задача2 .

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

ПОПАРНОЕ СРАВНЕНИЕ СТРОК/ СТОЛБЦОВ (ОТНОСИТЕЛЬНЫЕ ССЫЛКИ)

введем в ячейки диапазона A 2: D 2 числовые значения (можно считать их критериями);
выделим диапазон A 1: D 1 ;
применим к выделенному диапазону Условное форматирование на значение Меньше (Главная/ Стили/ Условное форматирование/ Правила выделения ячеек/ Меньше )
в левом поле появившегося окна введем относительную ссылку на ячейку A 2 (т.е. просто А2 или смешанную ссылку А$2 ). Убедитесь, что знак $ отсутствует перед названием столбца А.

Теперь каждое значение в строке 1 будет сравниваться с соответствующим ему значением из строки 2 в том же столбце! Выделены будут значения 1 и 5, т.к. они меньше соответственно 2 и 6, расположенных в строке 2.

Результат можно увидеть в файле примера на листе Задача3 .

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

Примечание-отступление : О важности фиксирования активной ячейки при создании правил Условного форматирования с относительными ссылками

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

СОВЕТ : Чтобы узнать адрес активной ячейки (она всегда одна на листе) можно посмотреть в поле Имя (находится слева от ). В задаче 3, после выделения диапазона A1:D1 (клавиша мыши должна быть отпущена), в , там будет отображен адрес активной ячейки A1 или D 1 . Почему возможно 2 вырианта и в чем разница для правил условного форматирования?

Посмотрим внимательно на второй шаг решения предыдущей задачи3 - выделение диапазона A 1: D 1 . Указанный диапазон можно выделить двумя способами: выделить ячейку А1 , затем, не отпуская клавиши мыши, выделить весь диапазон, двигаясь вправо к D1 ; либо, выделить ячейку D1 , затем, не отпуская клавиши мыши, выделить весь диапазон, двигаясь влево к А1 . Разница между этими двумя способами принципиальная: в первом случае, после завершения выделения диапазона, активной ячейкой будет А1 , а во втором D 1 !

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

Если мы выделили диапазон первым способом, то, введя в правило Условного форматирования относительную ссылку на ячейку А2 , мы тем самым сказали EXCEL сравнивать значение активной ячейки А1 со значением в А2 . Т.к. правило распространяется на диапазон A 1: D 1 , то B 1 будет сравниваться с В2 и т.д. Задача будет корректно решена.

Если при создании правила Условного форматирования активной была ячейка D1 , то именно ее значение будет сравниваться со значением ячейки А2 . А значение из A 1 будет теперь сравниваться со значением из ячейки XFB2 (не найдя ячеек левее A 2 , EXCEL выберет самую последнюю ячейку XFD для С1 , затем предпоследнюю для B 1 и, наконец XFB2 для А1 ). Убедиться в этом можно, посмотрев созданное правило:

выделите ячейку A1 ;
нажмите Главная/ Стили/ Условное форматирование/ Управление правилами ;
теперь видно, что применительно к диапазону $A$1:$D$1 применяется правило Значение ячейки <XFB2 (или <XFB$2 ).

EXCEL отображает правило форматирования (Значение ячейки ) применительно к активной ячейке, т.е. к A1 . Правильно примененное правило, в нашем случае, выглядит так:

ВЫДЕЛЕНИЕ СТРОК

ВЫДЕЛЕНИЕ ЯЧЕЕК С ЧИСЛАМИ

ПРИОРИТЕТ ПРАВИЛ

Для проверки примененных к диапазону правил используйте Диспетчер правил условного форматирования (Главная/ Стили/ Условное форматирование/ Управление правилами ).

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

Например, в ячейке находится число 9 и к ней применено два правила Значение ячейки >6 (задан формат: красный фон) и Значение ячейки >7 (задан формат: зеленый фон), см. рисунок выше. Т.к. правило Значение ячейки >6 (задан формат: красный фон) располагается выше, то оно имеет более высокий приоритет, и поэтому ячейка со значением 9 будет иметь красный фон. На Флажок Остановить, если истина можно не обращать внимание, он устанавливается для обеспечения обратной совместимости с предыдущими версиями EXCEL, не поддерживающими одновременное применение нескольких правил условного форматирования. Хотя его можно использовать для отмены одного или нескольких правил при одновременном использовании нескольких правил, установленных для диапазона (когда между правилами нет конфликта). Подробнее можно .

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

УСЛОВНОЕ ФОРМАТИРОВАНИЕ и ФОРМАТ ЯЧЕЕК

Условное форматирование не изменяет примененный к данной ячейке Формат (вкладка Главная группа Шрифт, или нажать CTRL+SHIFT+F ). Например, если в Формате ячейки установлена красная заливка ячейки, и сработало правило Условного форматирования, согласно которого заливкая этой ячейки должна быть желтой, то заливка Условного форматирования "победит" - ячейка будет выделены желтым. Хотя заливка Условного форматирования наносится поверх заливки Формата ячейки, она не изменяет (не отменяет ее), а ее просто не видно.

ОТЛАДКА ПРАВИЛ УСЛОВНОГО ФОРМАТИРОВАНИЯ

Чтобы проверить правильно ли выполняется правила Условного форматирования, скопируйте формулу из правила в любую пустую ячейку (например, в ячейку справа от ячейки с Условным форматированием). Если формула вернет ИСТИНА, то правило сработало, если ЛОЖЬ, то условие не выполнено и форматирование ячейки не должно быть изменено.

Вернемся к задаче 3 (см. выше раздел об относительных ссылках). В строке 4 напишем формулу из правила условного форматирования =A1

В тех столбцах, где результат формулы равен ИСТИНА, условное форматирование будет применено, а где ЛОЖЬ - нет.

ИСПОЛЬЗОВАНИЕ В ПРАВИЛАХ ССЫЛОК НА ДРУГИЕ ЛИСТЫ

До MS Excel 2010 для правил Условного форматирования нельзя было напрямую использовать ссылки на другие листы или книги. Обойти это ограничение можно было с помощью использования . Если в Условном форматирования нужно сделать, например, ссылку на ячейку А2 другого листа, то нужно сначала определить имя для этой ячейки, а затем сослаться на это имя в правиле Условного форматирования . Как это реализовано См. файл примера на листе Ссылка с другого листа .

ПОИСК ЯЧЕЕК С УСЛОВНЫМ ФОРМАТИРОВАНИЕМ

на вкладке Главная в группе Редактирование щелкните стрелку рядом с командой Найти и выделить ,

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

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

ДРУГИЕ ПРЕДОПРЕДЕЛЕННЫЕ ПРАВИЛА

В меню Главная/ Стили/ Условное форматирование/ Правила выделения ячеек разработчиками EXCEL созданы разнообразные правила форматирования.

Чтобы заново не изобретать велосипед, посмотрим на некоторые их них внимательнее.

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

Теперь посмотрим на только что созданное правило через меню Главная/ Стили/ Условное форматирование/ Управление правилами...

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

Пусть снова в ячейке имеется слово Дрель . Выделим ячейку и применим правило Текст содержит … Если в качестве критерия запишем р?, то слово Дрель будет выделено. Критерий означает: выделить слова, в которых содержатся слога ре, ра, ре и т.д. Надо понимать, что также будут выделены слова с фразами р2, рм, рQ , т.к. знак? означает любой символ. Если в качестве критерия запишем ?????? (выделить слова, в которых не менее 6 букв), то, соответственно, слово Дрель не будет выделено. Можно, конечно подобного результата добиться с помощью формул с функциями ПСТР() , ЛЕВСИМВ() , ДЛСТР() , но этот подход, согласитесь, быстрее.

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

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

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

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

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

Задача4 . Пусть имеется 21 значение, для удобства . Применим правило Последние 10 элементов и установим, чтобы было выделено 3 значения (элемента). См. файл примера , лист Задача4 .

Слова "Последние 3 значения" означают 3 наименьших значения. Если в списке есть повторы, то будут выделены все соответствующие повторы. Например, в нашем случае 3-м наименьшим является третье сверху значение 10. Т.к. в списке есть еще повторы 10 (их всего 6), то будут выделены и они.

Соответственно, правила, примененные к нашему списку: "Последнее 1 значение", "Последние 2 значения", ... "Последние 6 значений" будут приводить к одинаковому результату - выделению 6 значений равных 10.

Применение правила "Последние 7 значений" приведет к выделению дополнительно всех значений равных 11, .т.к. 7-м минимальным значением является первое сверху значение 11.

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

Последние 10%

Рассмотрим другое родственное правило Последние 10% .

Обратите внимание, что на картинке выше не установлена галочка "% от выделенного диапазона". Эта галочка устанавливается либо в ручную или при применении правила Последние 10% .

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

Попробуем задать 20% последних в нашем списке из 21 значения: будет выделено шесть значений 10 (См. файл примера , лист Задача4) . 10 - минимальное значение в списке, поэтому в любом случае будут выделены все его повторы.

Задавая проценты от 1 до 33% получим, что выделение не изменится. Почему? Задав, например, 33%, получим, что необходимо выделить 6,93 значения. Т.к. можно выделить только целое количество значений, Условное форматирование округляет до целого, отбрасывая дробную часть. А вот при 34% уже нужно выделить 7,14 значений, т.е. 7, а с учетом повторов следующего за 10-ю значения 11, будет выделено 6+3=9 значений.

ПРАВИЛА С ИСПОЛЬЗОВАНИЕМ ФОРМУЛ

Создание правил форматирования на основе формул ограничено только фантазией пользователя. Здесь рассмотрим только один пример, остальные примеры использования Условного форматирования можно найти в этих статьях: ; ; ; .

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

Выделите ячейки, к которым нужно применить Условное форматирование (пусть это ячейка А1 ).

Вызовите инструмент Условное форматирование (Главная/ Стили/ Условное форматирование/ Создать правило )

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

В поле «Форматировать значения, для которых следующая формула является истинной » введите =ЕОШ(A1) – если хотим, чтобы выделялись ячейки, содержащие ошибочные значения, т.е. будут выделены #ЗНАЧ!, #ССЫЛКА!, #ДЕЛ/0!, #ЧИСЛО!, #ИМЯ? или #ПУСТО! (кроме #Н/Д)

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

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

Вызовите инструмент Условное форматирование (Главная/ Стили/ Условное форматирование/ Создать правило )

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

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

Комментарии

Татьяна (не проверено)

Татьяна (не проверено)

Creator

Опять я. (не проверено)

Creator

Татьяна (не проверено)

Creator

Александр 555 (не проверено)

Creator

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Создать правило . Откроется диалоговое окно .

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Примечания:

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Примечания:

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Начинайте ввод формулы со знака равенства (= ).

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

Проверьте формулу, чтобы убедиться, что она не возвращает значение ошибки.

Примечания:

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Начинайте ввод формулы со знака равенства (=).

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

Проверьте формулу, чтобы убедиться, что она не возвращает значение ошибки.

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

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

Примечания:

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

При необходимости вы можете изменить диапазон ячеек. Для этого нажмите кнопку Свернуть диалоговое окно в поле Применяется к Развернуть диалоговое окно .

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Вверх и Вниз .

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

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

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

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

Остановить, если истина

Остановить, если истина для первого правила;

Остановить, если истина для второго правила;

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

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

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

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

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

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

На всем листе

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Первое правило (которое задает красный цвет фона ячейки, если значение - "истина") проверяет значение даты в столбце B напротив текущей даты (вычисленной с помощью функции СЕГОДНЯ в формуле). Формула для этого правила назначается первому значению в столбце B - B2 и выглядит так: =B2<СЕГОДНЯ () . Эта формула проверяет ячейки в столбце B (ячейки B2:B15). Если формула для какой-либо ячейки в столбце B оценивается как "истина", ее соответствующая ячейка в столбце A (например, A5 соответствует B5, а A11 - B11) форматируется красным цветом фона. После оценивания с помощью этого первого правила всех ячеек, указанных в группе Применяется к , проверяется второе правило. Данная формула проверяет, отстают ли значения в столбце B на 60 дней от текущей даты (предположим, сегодняшняя дата - 11.08.2010 г.). Ячейка в столбце B4 "4.10.2010" отстает от сегодняшней даты более чем на 60 дней, поэтому оценивается как "истина" и форматируется желтым цветом фона. Формула для данного правила выглядит так: =B2<СЕГОДНЯ()+60 . Все ячейки, сначала отформатированные красным по правилу с наивысшим приоритетом в списке, остаются без изменений.

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

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

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

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

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

Влияние операций вставки, заполнения и применения формата по образцу на правила условного форматирования

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

При копировании и вставке значений ячеек с условным форматированием в лист, открытый в другом экземпляре Excel (другой процесс Excel.exe одновременно выполняется на компьютере), правило условного форматирования не создается в другом экземпляре и форматирование не копируется в этот экземпляр.

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

Если к диапазону ячеек применимо правило форматирования, то оно имеет приоритет над форматированием вручную. Форматирование вручную можно выполнить с помощью кнопок из группы Шрифт на вкладке Главная в Excel 2016 или 2013 либо из группы Ячейки на вкладке Главная в Excel 2010 или 2007. При удалении правила условного форматирования форматирование, вручную примененное к диапазону ячеек, остается.

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

Контроль остановки обработки правил с помощью флажка "Остановить, если истина"

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

Например, если в более ранних версиях Excel, предшествующих версии Excel 2007, к диапазону ячеек применяется более трех правил условного форматирования, в данной версии Excel:

выполняется оценивание только трех первых правил;

применяется первое истинное правило в последовательности;

пропускаются истинные правила с более низким приоритетом.

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

Можно установить или снять флажок Остановить, если истина для изменения поведения по умолчанию:

для обработки только первого правила установите флажок Остановить, если истина для первого правила;

для обработки только первого и второго правил установите флажок Остановить, если истина для второго правила;

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

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

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

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

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

Чтобы удалить условное форматирование в выделенных ячейках:

Выделите ячейки на листе.

Щелкните на вкладке Главная > > Удалить правила > Удалить правила из выделенных ячеек .

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

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

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

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

Элен Брэдли объясняет некоторые различия в форматировании сводных таблиц в Excel 2010.

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

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

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

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

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

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

Чтобы сделать данные более наглядными, я выбираю значения Farm (Ферма) и Surgery (Хирургия) первого ветеринара с фамилией Browning , т.е. ячейки от B6 до E7 . Выделив этот диапазон, я захожу на вкладку Home (Главная), нажимаю Conditional Formatting > Data Bars (Условное форматирование > Гистограммы) и выбираю, какой использовать цвет. Эти действия форматируют выбранный диапазон таким образом, что в каждой ячейке появляется гистограмма, которая показывает относительное количество клиентов в каждом квартале и для каждого места приема.

На следующем рисунке мы применяем формат Data Bars (Гистограмма) к первому диапазону данных:

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

Таким образом, правило будет применено к аналогичным данным всех остальных ветеринаров в сводной таблице, без необходимости применять это правило к каждому диапазону по отдельности. Эти же опции Вы сможете найти, если решите создать новое правило форматирования в диалоговом окне New Formatting Rule (Создание правила форматирования).

На этом рисунке показано, как применить одинаковое условное форматирование ко всем данным одного типа в нашей сводной таблице:

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

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

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

Как и в прошлый раз появляется иконка Formatting Options (Параметры форматирования), при помощи которой мы сможем применить это же условное форматирование к итоговым данным по клиентам каждого ветеринара.

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

Другие параметры

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

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

Для этого выделим диапазон B6:E6 , перейдем Conditional Formatting > Data Bars (Условное форматирование > Гистограммы) и выберем зелёный цвет для гистограмм. Далее в параметрах форматирования выберем второй вариант (см. рисунок ниже). Теперь условное форматирование охватывает, как итоговые, так и общие итоговые (Grand Total) значения, которые, как и все данные в сводной таблице, являются средними значениями. Поэтому не будет нарушением сравнить их одинаковым образом.

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

Перемещаем данные

Вернёмся к нашей исходной сводной таблице и начнем перемещать данные. Делая это, Вы заметите, что форматирование сохраняется на нужных местах. Мы передвинули поле Office (Место приема) в область Report Filter (Фильтры) и перенесли поле Quarter (Квартал) в область Row Labels (Строки), и при этом все фиолетовые гистограммы остались на своих местах.

Даже если структура таблицы изменена и поля передвинуты, условное форматирование сохраняет свое положение:

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

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

Мы создадим отдельное правило условного форматирования для квартальных итогов каждого ветеринара, то есть нам нужно выделить и применить форматирование к ячейкам D7:D10 (потом D12:D15, потом D17:D19 и так далее). Затем используем правило Conditional Formatting > Top 10 Items (Условное форматирование > Первые 10 элементов), устанавливаем условие для 1 ячейки , формат оставляем предложенный по умолчанию. Вы можете либо скопировать это правило в соседний диапазон, либо создать для каждого индивидуально.

Чтобы скопировать форматирование, выделите одну или несколько ячеек с нужным форматом и нажмите Copy (Копировать). Затем выделите диапазон, в который нужно скопировать форматирование, и на вкладке Home (Главная) выберите Paste Special > Formats (Специальная вставка > Форматы).

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

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