Применение условного форматирования в сводной таблице может быть немного сложным.
Учитывая, что сводные таблицы настолько динамичны, а данные в серверной части могут часто меняться, вам необходимо знать, как правильно использовать условное форматирование в сводной таблице в Excel.
Неправильный способ применения условного форматирования к сводной таблице
Давайте сначала рассмотрим обычный способ применения условного форматирования в сводной таблице.
Предположим, у вас есть сводная таблица, как показано ниже:
В приведенном выше наборе данных дата находится в строках, а данные о продажах хранятся в столбцах.
Вот обычный способ применения условного форматирования к любому набору данных:
- Выберите данные (в данном случае мы применяем условное форматирование к B5: D14).
- Перейдите на главную -> Условное форматирование -> Правила сверху / снизу -> Выше среднего.
- Укажите формат (я использую «Зеленая заливка с черным зеленым текстом»).
- Щелкните ОК.
Это применит условное форматирование, как показано ниже:
Были выделены все точки данных, которые выше среднего для всего набора данных.
Проблема с этим методом заключается в том, что он применил условный формат к фиксированному диапазону ячеек (B5: D14). Если вы добавите данные в бэкэнд и обновите эту сводную таблицу, условное форматирование к ней не применяется.
Например, я возвращаюсь к набору данных и добавляю данные еще для одной даты (11 января 2015 г.). Это то, что я получаю, когда обновляю сводную таблицу.
Как видно на картинке выше, данные за 11 января 2015 года не выделены (хотя должны, поскольку значения для Магазина 1 и Магазина 3 выше среднего).
Причина, как я упоминал выше, заключается в том, что условное форматирование было применено к фиксированному диапазону (B5: D14), и оно не распространяется на новые данные в сводной таблице.
Правильный способ применения условного форматирования к сводной таблице
Вот два метода, чтобы убедиться, что условное форматирование работает даже при наличии новых данных в серверной части.
Метод 1. Использование значка форматирования сводной таблицы
В этом методе используется значок параметров форматирования сводной таблицы, который появляется, как только вы применяете условное форматирование к сводной таблице.
Вот как это сделать:
- Выберите данные, к которым вы хотите применить условное форматирование.
- Перейдите на главную -> Условное форматирование -> Правила сверху / снизу -> Выше среднего.
- Укажите формат (я использую «Зеленая заливка с черным зеленым текстом»).
- Щелкните ОК.
- Когда вы выполните вышеуказанные шаги, он применит условное форматирование к набору данных. В правом нижнем углу набора данных вы увидите значок Параметры форматирования:
- Щелкните значок. В раскрывающемся списке отобразятся три варианта:
- Выбранные ячейки (которые будут выбраны по умолчанию).
- Все ячейки показывают значения «Сумма дохода».
- Все ячейки показывают значения «Сумма дохода» для «Дата» и «Магазин».
- Выберите третий вариант - все ячейки, показывающие значения «Сумма дохода» для «Дата» и «Магазин».
Теперь, когда вы добавляете какие-либо данные в серверную часть и обновляете сводную таблицу, дополнительные данные автоматически покрываются условным форматированием.
Понимание трех вариантов:
- Выбранные ячейки: Это вариант по умолчанию, при котором условное форматирование применяется только к выбранным ячейкам.
- Все ячейки со значениями «Сумма дохода»: В этом варианте он учитывает все ячейки, которые показывают значения суммы дохода (или любые данные, которые у вас есть в разделе значений сводной таблицы).
- Проблема с этой опцией заключается в том, что она также охватывает значения общей суммы и применяет к ней условное форматирование.
- Все ячейки показывают значения «Сумма дохода» для «Дата» и «Магазин».: Это лучший вариант в данном случае. Он применяет условное форматирование ко всем значениям (за исключением общих итогов) для комбинации даты и сохранения. Даже если вы добавите больше данных в серверную часть, эта опция позаботится об этом.
Примечание:
- Значок «Параметры форматирования» отображается сразу после применения условного форматирования к набору данных. Если исчезнет, если вы сделаете что-то еще (отредактируете ячейку или измените шрифт / выравнивание и т. Д.).
- Условное форматирование исчезнет, если вы измените поля строки / столбца. Например, если вы удалите поле «Дата» и примените его снова, условное форматирование будет потеряно.
Метод 2 - Использование диспетчера правил условного форматирования
Помимо использования значка «Параметры форматирования», вы также можете использовать диалоговое окно «Диспетчер правил условного форматирования» для применения условного форматирования в сводной таблице.
Этот метод полезен, если вы уже применили условное форматирование и хотите изменить правила.
Вот как это сделать:
- Выберите данные, к которым вы хотите применить условное форматирование.
- Перейдите на главную -> Условное форматирование -> Правила сверху / снизу -> Выше среднего.
- Укажите формат (я использую «Зеленая заливка с черным зеленым текстом»).
- Щелкните ОК. Это применит условное форматирование к выбранным ячейкам.
- Перейдите на главную -> Условное форматирование -> Управление правилами.
- В Диспетчере правил условного форматирования выберите правило, которое вы хотите изменить, и нажмите кнопку «Изменить правило».
- В диалоговом окне Edit Rule вы увидите те же три варианта:
- Выбранные ячейки.
- Все ячейки показывают значения «Сумма дохода».
- Все ячейки показывают значения «Сумма дохода» для «Дата» и «Магазин».
- Выберите третий вариант и нажмите ОК.
Это применит условное форматирование ко всем ячейкам для полей «Дата» и «Магазин». Даже если вы измените данные серверной части (добавите больше данных магазина или даты), условное форматирование будет работать.
Примечание: Условное форматирование исчезнет, если вы измените поля строки / столбца. Например, если вы удалите поле «Дата» и примените его снова, условное форматирование будет потеряно.
