Сводная таблица — один из самых крутых инструментов для анализа и обобщения данных. Вы можете создавать мгновенные отчеты, используя сводные таблицы. Чтобы прокачать отчет и сделать его более привлекательным, можно применить условное форматирование к сводной таблице.
Ниже пример, где я применил условное форматирование в сводной таблице, чтобы выделить ячейки.
Excel выделяет город с наибольшими и наименьшими продажами, причем правило форматирования сохраняется при смене фильтра с помощью среза.
Сегодня в этой статье вы узнаете, как применять условное форматирование к сводной таблице, и все возможные способы ее использования.
Пошаговое применение условного форматирования к сводной таблице
Применение условного форматирования в сводной таблице на первый взгляд кажется сложным, но на самом деле это проще простого.
Задача: В приведенной ниже сводной таблице необходимо применить «3-цветную шкалу» к значениям «Месяц».
- Прежде всего, выберите любую из ячеек, которые имеют значение месяца.
- Затем перейдите на вкладку «Главная» → «Стили» → «Условное форматирование» → «Создать правило».
- Вы увидите диалоговое окно для применения условного форматирования к сводной таблице.
- В этом всплывающем окне у вас есть три различных варианта применения условного форматирования в сводной таблице.
- К выделенным ячейкам: используйте этот параметр, чтобы применить условное форматирование только к выбранной ячейке.
- Ко всем ячейкам, содержащим значения «Сумма»: все ячейки, которые имеют значения суммы.
- Ко всем ячейкам, содержащим «Сумма» для «Месяц»: все ячейки, значения которых имеют сумму, но только за месяцы.
- Выберите «Ко всем ячейкам, содержащим значения «Сумма» для «Месяц».
- В «Измените описание правила» выберите 3-х цветную шкалу.
- Измените тип минимума, среднего значения и максимума на проценты. После этого выберите цвет для всех трех.
- В конце нажмите ОК.
В приведенной выше сводной таблице мы применили трехцветное форматирование к ячейкам, имеющим месячную сумму. Используя это форматирование, вы можете легко проанализировать, какой месяц приносит больше дохода, а какой меньше.
Примеры применения условного форматирования в сводной таблице.
Использование условного форматирования в сводной таблице — разумный шаг, и вы можете использовать его по-разному.
Вот список примеров, которые вы можете сразу же применять в своей работе.
1. Динамическое условное форматирование с фильтрами
Когда вы используете условное форматирование в своей сводной, оно работает как динамическое форматирование. Всякий раз, когда вы фильтруете значения или изменяете данные, они автоматически обновляются с этим изменением.
В примере выше мы применили условное форматирование, чтобы выделить ячейку с наибольшим значением. Каждый раз, когда вы фильтруете вашу сводную таблицу, она автоматически учитывает текущие значения и применяет к ней правило форматирования.
2. Применение условного форматирования к отдельной строке в сводной таблице
В приведенной ниже сводной таблице представлены данные о продажах по продуктам и месяцам за 2019 год. Задача выделить ячейку, которая имеет наибольшие продажи для конкретного продукта.
- Выберите любую из ячеек.
- Перейдите на вкладку «Главная» → «Стили» → «Условное форматирование» → «Создать правило».
- Применить правило: третий вариант.
- Из типов правила выберите «Форматировать только первые или последние значения».
- В описании правила введите 1 в поле ввода и в раскрывающемся меню выберите «Каждой группы столбцов».
- Примените форматирование, которое вы хотите.
- Нажмите ОК.
При выборе «каждой группы столбцов» условное форматирование будет сравнивать значения только из строки. Если вы хотите применить это правило к столбцам вместо строк, вы можете выбрать «каждой группы строк» в описании правила.
Если же вы хотите применить правило ко всем ячейкам, просто выберите «всех значений» из выпадающего списка.
3. Условное форматирование в сводной таблице на основе другой ячейки
В приведенной ниже сводной таблице необходимо применить условное форматирование — гистограмма. Но вот в чем задача: у нас есть целевое значение в другой ячейке, и нам нужно применить условное форматирование по сравнению с этим целевым значением.
Для реализации нужно сделать следующие шаги:
- Выберите ячейку и перейдите на вкладку «Главная» → «Стили» → «Условное форматирование» → «Создать правило».
- Затем выберите третий вариант из «Применить правило к» и выберите «Форматировать все ячейки на основании их значений» из типа правила.
- Теперь в описании правила выберите «Гистограмма», в раскрывающемся меню «Тип» выберите число для максимального и минимального значений.
- В поле минимального значения введите 0, а в поле максимального значения введите ссылку на ячейку D4.
- В конце укажите цвета заливки и нажмите кнопку «ОК».
4. Применение условного форматирования к промежуточным итогам сводной таблицы.
В приведенной ниже сводной таблице указана сумма продаж за разные месяцы, кварталы и промежуточные итоги за каждый квартал. Задача: нужно применить условное форматирование к промежуточным итогам вместо значений месяца.
Действуем по шагам:
- Выберите ячейку и перейдите на вкладку «Главная» → «Стили» → «Условное форматирование» → «Создать правило».
- Затем выберите третий вариант из «Применить правило к» и выберите «Форматировать все ячейки на основании их значений» из типа правила.
- В описании правила выберите «Наборы значков» и стиль значков.
- Теперь в правиле отображения значков используйте процент для применения условного форматирования к промежуточным итогам.
- В конце нажмите ОК.
Когда вы применяете эти наборы значков для промежуточных итогов, они будут рассматривать самое высокое значение как 100%. Ячейки со значениями выше 67% получат зеленый значок, ячейки со значениями от 33% до 67% получат желтый значок, а другие ячейки ниже — красный значок.
Вы можете изменить значки и процентные значения в соответствии с вашими потребностями.
5. Применение условное форматирование к пустым ячейкам в сводной таблице
Лучший способ выделить пустые ячейки — использовать условное форматирование, используя его в сводной таблице.
В приведенной ниже сводной таблице есть пустые ячейки, и нам необходимо применить условное форматирование к тем ячейкам, где нет продажи.
- Нажмите на «Создать правило» в условном форматировании и выберите третий вариант в «Применить правило к».
- Выберите тип правила «Форматировать только ячейки, которые содержат» и выберите «Пустые» в описании правила.
- Определите цвет ячейки для форматирования и нажмите ОК.
