Условное форматирование в сводной таблице excel

Обновлено: 08.07.2024

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

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

Числовой формат поля задается в диалоговом окне Параметры поля значений (Data Field Settings), перейти в которое можно следующими тремя способами.

Щелкните правой кнопкой мыши в области значений сводной таблицы и выберите команду Параметры полей значений.
Щелкните на кнопке открытия меню Сумма по полю Доход области Значения (Values) диалогового окна Список полей сводной таблицы (PivotTable Field List). Выберите в этом меню команду Параметры полей значений.
Выберите любую ячейку в области данных сводной таблицы. На контекстной вкладке ленты Параметры (Options) в группе Активное поле (Active Field) щелкните на кнопке Параметры поля (Field Settings). Как показано на рис. 3.3, на экране появится диалоговое окно Параметры поля значений (Value Field Settings). Чтобы изменить в нем числовой формат поля, щелкните в левом нижнем углу окна на кнопке Числовой формат (Number Format).

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

В диалоговом окне Формат ячеек (Format Cells) можно выбрать один из заранее созданных форматов или же создать произвольный пользовательский числовой формат, который впоследствии можно будет применить в сводной таблице. В рассматриваемом случае (рис. 3.4) выбран формат Денежный (Currency).

Рис 3.4. Выберите в этом диалоговом окне один из доступных числовых форматов

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

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

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

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

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

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

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

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

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

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

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

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

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

В левое поле вводим ссылку на ячейку В2 (щелкаем мышью по этой ячейке – ее имя появится автоматически). По умолчанию – абсолютную.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Рис 6.25. Создайте простую сводную таблицу

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

Сначала выделите все поле Объем продаж в области значений. После выделения объема для каждого периода Торговый период перейдите на вкладку ленты Главная и щелкните на кнопке Условное форматирование (Conditional Formatting), находящейся в группе Стили (Styles), как показано на рис. 6.26.

Рис. 6.26. Для значений сводной таблицы выберите условное форматирование в виде гистограммы

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

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

Рис. 6.27. Условные гистограммы добавляются с помощью всего нескольких щелчков

В следующем списке приведены готовые сценарии условного форматирования:

10 первых элементов (Top Nth Items);
первые 10% (Top Nth %);
10 последних элементов (Bottom Nth Items);
последние 10% (Bottom Nth %);
выше среднего (Above Average);
ниже среднего (Below Average).

Как видите, Excel 2010 содержит сценарии с наиболее распространенными критериями условного форматирования.

Чтобы удалить примененное ранее условное форматирование, поместите курсор в сводную таблицу, перейдите на вкладку Главная (Ноmе), щелкните на кнопке Условное форматирование (Conditional Formatting) группы Стили (Styles) и выберите в раскрывающемся меню команду Удалить правила → Удалить правила из этой сводной таблицы (Clear Rules^Clear Rules from this PivotTable).

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

Рис. 6.28. В этой сводной таблице отображаются поля Объем продаж, Период продаж (в часах) и вычисляемое поле, определяющее значение выручки за час

В этом сценарии мы попытаемся отследить взаимосвязь между общим объемом продаж и выручкой за час. Основная идея заключается в применении условного форматирования, облегчающего поиск различий и совпадений. Поместите курсор в столбец Объем продаж. Перейдите на вкладку Главная (Ноmе) и щелкните на кнопке Условное форматирование (Conditional Formatting). Выберите команду Создать правило (New Rule). На экране появится диалоговое окно Создание правила форматирования (New Formatting Rule), показанное на рис. 6.29.

Рис. 6.29. Диалоговое окно Создание правила форматирования

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

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

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

В разделе Выберите тип правила (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 (ложь), то условное форматирование к ячейке не применяется.

В нашем сценарии мы будем обозначать проблемные области с помощью набора значков. Поэтому в качестве типа форматирования нужно выбрать параметр Форматировать все ячейки на основании их значений. Наконец, нужно определить детальные параметры условного форматирования, для чего используются настройки раздела Изменение описания правила (Edit the Ruie Description). Чтобы добавить в проблемные ячейки значки, выберите в раскрывающемся меню Стиль формата (Format Style) значение Наборы значков (Icon Sets).

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

Рис. 6.31. Выберите в раскрывающемся меню Стиль формата значение Наборы значков

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

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

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

Рис. 6.32. Условное форматирование позволяет добиться весьма познавательных и важных результатов

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

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

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

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

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

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

Вы можете скачать это условное форматирование в шаблоне Excel сводной таблицы здесь - Условное форматирование в шаблоне Excel сводной таблицы

Пример № 1

Ниже приведены данные розничного магазина (данные за 2 месяца).

Выполните следующие шаги, чтобы создать сводную таблицу:

Нажмите на любую ячейку в данных. Перейдите на вкладку INSERT.
Нажмите на сводную таблицу в разделе «Таблицы» и создайте сводную таблицу. Смотрите скриншот ниже.

Перетащите поле «Продукт» в раздел «Строки», раздел «Продажи в значение», а в поле «Месяц - в столбец».

Теперь у нас есть сводный отчет по месячным продажам. Мы хотим выделить продукты, чья продажа меньше 1500.

Для применения условного форматирования в этой сводной таблице выполните следующие шаги:

Выберите диапазон ячеек, для которого вы хотите применить условное форматирование в Excel. Мы выбрали диапазон B5: C14 здесь.
Перейдите на вкладку ДОМОЙ > Выберите параметр « Условное форматирование» в разделе «Стили»> выберите параметр « Выделить элементы ячеек» > нажмите « меньше чем» .

Откроется диалоговое окно Less Than.
Введите 1500 в поле «Формат ячеек» и выберите цвет «Желтая заливка темно-желтым текстом». Смотрите скриншот ниже.

Сводный отчет будет выглядеть следующим образом.

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

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

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

Решение для преодоления проблемы:

Для преодоления этой проблемы выполните следующие шаги после применения условного форматирования в сводной таблице Excel:

Нажмите на любую ячейку в сводной таблице> Перейдите на вкладку HOME > Выберите параметр « Условное форматирование» в разделе «Стили»> «Выберите пункт« Управление правилами »» .

Откроется диалоговое окно диспетчера правил. Нажмите на вкладку Edit Rule, как показано на скриншоте ниже.

Откроется окно форматирования правила редактирования. Смотрите скриншот ниже.

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

Выбранные ячейки: эта опция неприменима, когда вы вносите какие-либо изменения в данные Pivot, такие как добавление или удаление данных.
Все ячейки, в которых отображаются значения «Сумма продажи»: этот параметр может включать дополнительные поля, такие как общие итоги и т. Д., Которые мы можем не включать в наши отчеты.
Все ячейки, отображающие значения «Сумма продаж» для «Продукта» и «Месяца»: этот параметр ограничивается данными и выполняет форматирование с ячейками, в которых отображаются наши обязательные ячейки. Он исключает дополнительные ячейки, такие как общие итоги и т. Д. Этот вариант является лучшим вариантом для форматирования.

Нажмите на 3- й вариант « Все ячейки», отображающий значения «Сумма продаж» для «продукта» и «Месяца», как показано на скриншоте ниже, а затем нажмите « ОК» .

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

То, что нужно запомнить

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