Условное форматирование в сводной таблице
Вы знаете, что сводная таблица — один из важнейших инструментов анализа и обобщения данных.
Вы можете создавать мгновенные отчеты с помощью сводных таблиц. А чтобы сделать ее более привлекательной, вы можете применить к сводной таблице условное форматирование. Посмотрите ниже, где я применил условное форматирование в сводной таблице, чтобы выделить ячейки.
При фильтрации всех штатов с помощью среза будет выделен город с наибольшим количеством и город с наименьшим количеством. И сегодня в этой статье вы узнаете, как применить условное форматирование к сводной таблице и все возможные способы его использования.
Примечание. Сводные таблицы являются частью СРЕДНЕГО НАВЫКА EXCEL .
Действия по применению условного форматирования к сводной таблице
Применение условного форматирования в сводной таблице на первый взгляд кажется сложной задачей, но на самом деле это просто и легко. В сводной таблице ниже вам необходимо применить «3-цветные шкалы» к значениям «месяца». Давайте выполним следующие шаги.
- Сначала выберите одну из ячеек со значением месяца.
- Затем перейдите на вкладку «Главная» → «Стили» → «Условное форматирование» → «Новое правило».
- Здесь вы увидите всплывающее окно для применения условного форматирования к сводной таблице.
- В этом всплывающем окне у вас есть три различных варианта применения условного форматирования в сводной таблице.
- Выбранная ячейка: используйте этот параметр, чтобы применить условное форматирование только к выбранной ячейке.
- Все ячейки, показывающие значения «суммы»: все ячейки, содержащие значения суммы.
- Все ячейки, показывающие значения «суммы» для «месяца»: все ячейки со значениями суммы, но только для месяцев.
- Выделите все ячейки, показывающие значения «Сумма» для «Месяц».
- В «Редактировать описание правила» выберите 3-цветную шкалу.
- Измените тип минимума, медианы и максимума на проценты. После этого выберите цвет для всех трех.
- В конце нажмите ОК.
В сводной таблице выше вы применили трехцветное форматирование к ячейкам с ежемесячной суммой. Используя такое форматирование, вы можете легко проанализировать, в каком месяце выручка больше, чем в других.
Связанный: Полное руководство по условному форматированию в Excel
Примеры. Применение условного форматирования в сводной таблице.
Использование условного форматирования в сводной таблице — разумный шаг, и вы можете использовать его по-разному. Вот список некоторых примеров, которые вы можете изучить и мгновенно применить в своей работе.
1. Динамическое условное форматирование с фильтрами
Когда вы используете условное форматирование в сводной таблице, оно работает как динамическое форматирование. Каждый раз, когда вы фильтруете значения или меняете данные, они автоматически обновляются с учетом этих изменений.
В сводной таблице выше вы применили условное форматирование, чтобы выделить ячейку с наибольшим значением. Если вы нажмете на 2014 год, у Продукта B будет самый высокий объем продаж, а если в 2015 году, у Продукта C будет самый высокий объем продаж.
Каждый раз, когда вы фильтруете сводную таблицу, она автоматически учитывает текущие значения и применяет к ней правило форматирования.
Связанный: Срез Excel
2. Примените условное форматирование к одной строке сводной таблицы.
В сводной таблице ниже представлены данные о продажах по продуктам и неделям. И здесь вам нужно выделить ячейку, в которой указан наибольший объем продаж конкретного товара.
- Выберите одну из ячеек.
- Перейдите на вкладку «Главная» → «Стили» → «Условное форматирование» → «Новое правило».
- В разделе Правило выберите третий вариант.
- И в типе «правило выбора» выберите «Форматировать только значения с высоким или низким рейтингом».
- В поле «Изменить описание правила» введите 1 в поле ввода и в раскрывающемся меню выберите «каждая группа столбцов».
- Примените желаемое форматирование.
- Нажмите ОК.
При выборе «каждой группы столбцов» (Rows) условное форматирование сравнивает значения только в строке. Если вы хотите применить это правило к столбцам, а не к строкам, вы можете выбрать «каждая группа строк» в описании правила.
А если вы хотите применить правило ко всем ячейкам, просто выберите «все значения» из раскрывающегося списка.
3. Условное форматирование в сводной таблице на основе другой ячейки
В сводной таблице ниже вам необходимо применить гистограммы. Но вот в чем особенность: у вас есть целевое значение в другой ячейке, и вам нужно применить гистограммы относительно этого целевого значения.
Вот шаги, которые вам нужно выполнить.
- Сначала выберите ячейку и перейдите на вкладку «Главная» → «Стили» → «Условное форматирование» → «Новое правило».
- Затем выберите третий вариант в «Применить правило к» и выберите «Форматировать все ячейки на основе их значений» в типе правила.
- Теперь в описании правила выберите «Панель данных» и в раскрывающемся меню «Тип» выберите число для максимума и минимума.
- Отсюда в поле минимального значения введите 0, а в поле максимального ввода введите ссылку на ячейку E2.
- В конце укажите желаемое форматирование панели данных и нажмите «ОК».
4. Примените условное форматирование к промежуточным итогам сводной таблицы.
В сводной таблице ниже вы видите объем продаж за разные месяцы и кварталы, а также промежуточные итоги за каждый квартал. Здесь вам нужно применить условное форматирование (набор значков) к промежуточным итогам вместо ежемесячных значений.
Следуй этим шагам:
- Сначала выберите ячейку и перейдите на вкладку «Главная» → «Стили» → «Условное форматирование» → «Новое правило».
- Затем выберите третий вариант в «Применить правило к» и выберите «Форматировать все ячейки на основе их значений» в типе правила.
- В описании правила выберите «Наборы значков» и выберите «Стиль значков».
- Теперь в линейке отображения значков используйте проценты, чтобы применить условное форматирование к промежуточным итогам.
- В конце нажмите ОК.
Когда вы применяете эти наборы значков к промежуточным итогам, за основу для 100 % будет взято наибольшее значение. Ячейки со значениями выше 67% получат зеленый значок, ячейки со значениями от 33% до 67% получат желтый значок, а другие ячейки ниже получат красный значок.
При необходимости вы можете изменить значки и проценты.
5. Примените условное форматирование к пустым ячейкам сводной таблицы.
Лучший способ выделить пустые ячейки — использовать условное форматирование. Его также можно использовать в сводной таблице. В сводной таблице ниже некоторые ячейки пусты, и вам необходимо применить условное форматирование к ячейкам, в которых нет продаж.
Следуйте этим простым шагам.
- Щелкните новое правило в разделе «Условное форматирование» и выберите третий вариант в разделе «Применить к правилу».
- Выберите тип правила «Форматировать только те ячейки, которые содержат» и выберите «Пусто» в описании правила.
- Примените цвет ячейки для форматирования и нажмите «ОК».
