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

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

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

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

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

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

Пошаговое применение условного форматирования к сводной таблице

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

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

Исходная сводная таблица
  • Прежде всего, выберите любую из ячеек, которые имеют значение месяца.
Выберите ячейку для условного форматирования в сводной таблице
  • Затем перейдите на вкладку «Главная» → «Стили» → «Условное форматирование» → «Создать правило».
Создать правило условного форматирования
  • Вы увидите диалоговое окно для применения условного форматирования к сводной таблице.
Всплывающее окно для применения условного форматирования в сводной таблице
  • В этом всплывающем окне у вас есть три различных варианта применения условного форматирования в сводной таблице.
  1. К выделенным ячейкам: используйте этот параметр, чтобы применить условное форматирование только к выбранной ячейке.
  2. Ко всем ячейкам, содержащим значения «Сумма»: все ячейки, которые имеют значения суммы.
  3. Ко всем ячейкам, содержащим «Сумма» для «Месяц»: все ячейки, значения которых имеют сумму, но только за месяцы.
Три варианта применения условного форматирования в сводной таблице
  • Выберите «Ко всем ячейкам, содержащим значения «Сумма» для «Месяц».
  • В «Измените описание правила» выберите 3-х цветную шкалу.
  • Измените тип минимума, среднего значения и максимума на проценты. После этого выберите цвет для всех трех.
Выберите параметры для применения условного форматирования в сводной таблице
  • В конце нажмите ОК.
Сводная таблица с условным форматированием в сводной таблице

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

Примеры применения условного форматирования в сводной таблице.

Использование условного форматирования в сводной таблице — разумный шаг, и вы можете использовать его по-разному.

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

1. Динамическое условное форматирование с фильтрами

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

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

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

2. Применение условного форматирования к отдельной строке в сводной таблице

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

применить условное форматирование в сводной таблице в строках
  • Выберите любую из ячеек.
Выберите ячейку, чтобы применить условное форматирование в сводной таблице в строке
  • Перейдите на вкладку «Главная» → «Стили» → «Условное форматирование» → «Создать правило».
  • Применить правило: третий вариант.
  • Из типов правила выберите «Форматировать только первые или последние значения».
  • В описании правила введите 1 в поле ввода и в раскрывающемся меню выберите «Каждой группы столбцов».
Выделить верхние значения из строки, используя условное форматирование в сводной таблице
  • Примените форматирование, которое вы хотите.
  • Нажмите ОК.
Условное форматирование в сводной таблице для выделения строк

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

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

3. Условное форматирование в сводной таблице на основе другой ячейки

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

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

Для реализации нужно сделать следующие шаги:

  • Выберите ячейку и перейдите на вкладку «Главная» → «Стили» → «Условное форматирование» → «Создать правило».
  • Затем выберите третий вариант из «Применить правило к» и выберите «Форматировать все ячейки на основании их значений» из типа правила.
  • Теперь в описании правила выберите «Гистограмма», в раскрывающемся меню «Тип» выберите число для максимального и минимального значений.
  • В поле минимального значения введите 0, а в поле максимального значения введите ссылку на ячейку D4.
Применить условное форматирование гистограмма в сводной таблице
  • В конце укажите цвета заливки и нажмите кнопку «ОК».
Условное форматирование гистрограмма

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

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

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

Действуем по шагам:

применить значки к промежуточной сумме, чтобы использовать условное форматирование в сводной таблице
  • Выберите ячейку и перейдите на вкладку «Главная» → «Стили» → «Условное форматирование» → «Создать правило».
  • Затем выберите третий вариант из «Применить правило к» и выберите «Форматировать все ячейки на основании их значений» из типа правила.
  • В описании правила выберите «Наборы значков» и стиль значков.
  • Теперь в правиле отображения значков используйте процент для применения условного форматирования к промежуточным итогам.
  • В конце нажмите ОК.

Когда вы применяете эти наборы значков для промежуточных итогов, они будут рассматривать самое высокое значение как 100%. Ячейки со значениями выше 67% получат зеленый значок, ячейки со значениями от 33% до 67% получат желтый значок, а другие ячейки ниже — красный значок.

Вы можете изменить значки и процентные значения в соответствии с вашими потребностями.

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

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

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

Выделите пустые ячейки, используя условное форматирование в сводной таблице
  • Нажмите на «Создать правило» в условном форматировании и выберите третий вариант в «Применить правило к».
  • Выберите тип правила «Форматировать только ячейки, которые содержат» и выберите «Пустые» в описании правила.
Применить условное форматирование в сводной таблице для пустых ячеек
  • Определите цвет ячейки для форматирования и нажмите ОК.
Условное форматирование в сводной таблице с выделением пустых ячеек


Оцените статью
Добавить комментарий