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

Обновлено: 05.07.2024

В чем отличие условного форматирования сводной таблицы?

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

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

Обучающие материалы по условному форматированию сводной таблицы

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

Стартовый файл — если вы хотите продолжить. Однако, если вы хотите перейти в конец, вот готовый файл.

Лучший способ условного форматирования сводной таблицы

Почему условное форматирование в сводных таблицах не работает?

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

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

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

Поэтому, если вы хотите правильно выполнить условное форматирование в сводной таблице Microsoft Excel, вы можете применить форматирование к одной ячейке. После этого вы можете использовать меню параметров форматирования, чтобы применить форматирование ко всему полю. Однако есть одна вещь, в которой нужно быть осторожным. Прежде чем щелкнуть меню «Условное форматирование» на ленте в верхней части экрана, убедитесь, что вы выбрали ячейку в сводной таблице, которая находится в пределах уровня группировки, к которому вы хотите применить условное форматирование. Если вы хотите знать, о чем я говорю, попробуйте следующее. (Сначала загрузите файл упражнения.)

Теперь вы можете видеть, что все значения в столбце "% прибыли", которые ниже 90 %, выделены красным цветом. Более того, вы можете отфильтровать сводную таблицу с помощью слайсера, и она по-прежнему будет работать.

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

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

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

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

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

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

Сначала выберите ячейку E6.
Затем нажмите «Условное форматирование — новое правило».
После этого выберите Использовать формулу для определения форматируемых ячеек.
Далее выберите все ячейки, показывающие значения % прибыли для даты.
Затем в поле Формат значений, где эта формула верна, введите:
=$E6

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

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

Это руководство охватывает:

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

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

Предположим, у вас есть сводная таблица, как показано ниже:

В приведенном выше наборе данных дата указана в строках, а данные о продажах магазина — в столбцах.

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

При этом будет применено условное форматирование, как показано ниже:

Все точки данных, которые выше среднего значения всего набора данных, были выделены.

Проблема этого метода заключается в том, что он применяет условное форматирование к фиксированному диапазону ячеек (B5:D14). Если вы добавите данные в серверную часть и обновите эту сводную таблицу, условное форматирование не будет применено к ней.

Например, я возвращаюсь к набору данных и добавляю данные еще на одну дату (11 января 2015 г.). Вот что я получаю, когда обновляю сводную таблицу.

Как видно на рисунке выше, данные за 11 января 2015 года не выделены (в то время как должны, поскольку значения для Магазина 1 и Магазина 3 выше среднего).

Причина, как я упоминал выше, заключается в том, что условное форматирование было применено к фиксированному диапазону (B5:D14) и не распространяется на новые данные в сводной таблице.

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

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

Способ 1 – использование значка форматирования сводной таблицы

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

Вот как это сделать:

Выберите данные, к которым вы хотите применить условное форматирование.
На главную –> Условное форматирование –> Верхние/нижние правила –> Выше среднего.
Укажите формат (я использую «Зеленая заливка с темно-зеленым текстом»).
Нажмите "ОК".
- При выполнении описанных выше действий к набору данных применяется условное форматирование. В правом нижнем углу набора данных вы увидите значок "Параметры форматирования":
Теперь, когда вы добавляете какие-либо данные в серверной части и обновляете сводную таблицу, дополнительные данные автоматически охватываются условным форматированием.

Три варианта:
- Выбранные ячейки. Это параметр по умолчанию, при котором условное форматирование применяется только к выбранным ячейкам.
- Все ячейки, отображающие значения «Суммы дохода». В этом случае учитываются все ячейки, в которых отображаются значения суммы дохода (или любые другие данные, которые есть в разделе значений сводной таблицы). ).
  - Проблема с этим параметром заключается в том, что он также охватывает значения общей суммы и применяет к ним условное форматирование.
  Примечание :
  - Значок "Параметры форматирования" отображается сразу после применения условного форматирования к набору данных. Если вы делаете что-то еще (редактируете ячейку или меняете шрифт/выравнивание и т. д.), он исчезает.
  - Условное форматирование исчезнет, если вы измените поля строки/столбца. Например, если удалить поле «Дата» и применить его снова, условное форматирование будет потеряно.
  Способ 2 — Использование диспетчера правил условного форматирования
  
  Помимо значка "Параметры форматирования", вы также можете использовать диалоговое окно "Диспетчер правил условного форматирования", чтобы применить условное форматирование к сводной таблице.
  
  Этот метод удобен, если вы уже применили условное форматирование и хотите изменить правила.
  
  Вот как это сделать:
  
  Это применит условное форматирование ко всем ячейкам полей «Дата» и «Магазин». Даже если вы измените серверные данные (добавите больше данных магазина или даты), условное форматирование будет работать.
  
  Примечание. Условное форматирование исчезнет, если вы измените поля строки/столбца. Например, если вы удалите поле «Дата» и примените его снова, условное форматирование будет потеряно.
  
  Итог: узнайте, как применять условное форматирование к сводным таблицам, чтобы форматы динамически применялись повторно при изменении, фильтрации или обновлении сводной таблицы.
  
  Уровень квалификации: средний
  
  Загрузить файл Excel
  
  Вот файл, который я использую в видео. Вы можете использовать его, чтобы попрактиковаться в добавлении, удалении и изменении условного форматирования на различных примерах сводных таблиц.
  
  Условное форматирование сводной таблицы.xlsx (63,1 КБ)
  
  Что такое условное форматирование?
  
  Форматирование ячеек таким образом, чтобы они отличались от окружающих данных, помогает им выделиться и привлечь внимание. Условное форматирование позволяет настроить правила для форматирования ячеек на основе критериев, чтобы форматирование ячеек применялось автоматически.
  
  Это позволяет нам легко выявлять тенденции и аномалии в наших отчетах. Несколько примеров есть, но возможности безграничны:
  - Доход, превышающий определенную сумму в долларах
  - Продукты с низкой эффективностью
  - Тренды временных рядов
  - и многое другое.
  Условное форматирование позволяет изменить шрифт, заливку и цвет границ ячеек. Он также может добавлять в ячейки значки и гистограммы.
  
  Форматирование также будет применяться при изменении значений ячеек. Это отлично подходит для интерактивных сводных таблиц, где значения могут меняться в зависимости от фильтра или среза.
  
  Как настроить условное форматирование для сводных таблиц
  
  Настройка условного форматирования для сводных таблиц немного отличается от настройки обычных ячеек/диапазонов. Поэтому в этом посте я объясню, как применить условное форматирование к сводным таблицам.
  
  1. Выберите ячейку в области значений
  
  Первый шаг — выбрать ячейку в области значений сводной таблицы. Если ваша сводная таблица содержит несколько полей в области «Значения», выберите ячейку для поля, к которому вы хотите применить форматирование.
  
  2. Применить условное форматирование
  
  Вы можете найти меню «Условное форматирование» на вкладке «Главная» ленты. Это открывает множество отличных возможностей, когда дело доходит до установки правил или условий. В моем примере я использую правило «10 лучших элементов».
  
  В примере я изменил 10 на 3, чтобы выбрать только три ячейки с самыми высокими значениями. Затем я выбрал зеленую заливку для формата.
  
  Это новое правило форматирования найдет три самых высоких значения в выбранном диапазоне. Затем он изменит эти ячейки, чтобы они имели зеленую заливку с темно-зеленым текстом. Однако, поскольку я начал с выбора только одной ячейки вместо диапазона, изменилась только эта ячейка (по умолчанию это самое высокое значение).
  
  Это нормально, потому что нас интересует меню «Параметры форматирования». Это меню появляется рядом с только что отформатированной ячейкой. Это меню позволяет расширить правило форматирования до аналогичных окружающих данных.
  
  3. Использование меню параметров форматирования
  
  Вы можете использовать меню "Параметры форматирования", чтобы применить форматирование к полю в области "Значения".
  
  При выборе второго варианта в меню (см. изображение выше) правило форматирования применяется к любому из значений под заголовком «Сумма дохода», включая общий итог. На изображении ниже видно, что три самых высоких значения отмечены зеленым цветом.
  
  Но совершенно очевидно, что общий итог всегда будет самым большим значением. Поэтому довольно часто вы захотите исключить общий итог из своего форматирования.Таким образом, вы сравниваете похожие данные (в данном случае продукты с 1 по 7), когда называете тройку лучших. Если вы хотите исключить общий итог, вы можете выбрать третий вариант в меню «Параметры форматирования»: «Все ячейки, отображающие значения «Суммы дохода» для «Продукта».
  
  4. Доступ к параметрам форматирования после внесения изменений
  
  Кнопка «Параметры форматирования» появляется при первом применении условного форматирования. Он исчезает после внесения изменений в лист. Если вы хотите снова увидеть эти параметры, вы можете найти правила в окне «Редактировать правило форматирования». Вы можете получить к нему доступ, нажав кнопку «Условное форматирование», выбрав «Управление правилами», затем выбрав правило и нажав «Изменить правило».
  
  Появится окно "Редактировать правило форматирования". В верхней части окна перечислены те же параметры, что и в нашем меню.
  
  Условное форматирование с несколькими полями
  
  Если в сводной таблице есть несколько полей в области строк или столбцов, то, скорее всего, в ней также будут строки и столбцы промежуточных итогов. В этом случае мы должны убедиться, что применяем форматирование к нужным полям.
  
  Самый простой способ — выделить ячейку на этом пересечении ДО применения условного форматирования. Вы также можете изменить ячейку позже.
  
  Изменив ячейку, на которую направлено правило, вы увидите, что параметры применения форматирования также изменились. Например, когда я начинаю процесс с выбранной ячейкой B6, мои параметры включают Все ячейки, показывающие значения «Доход», а также Все ячейки, показывающие значения «Доход» для «Продукта» и «Qtr»:
  
  Но когда я изменяю ячейку фокуса на B7, поля промежуточных итогов (Продукт) больше не доступны. Вместо этого я выбираю Все ячейки, показывающие значения «Дохода», и Все ячейки, показывающие значения «Дохода» для «Региона» и «Квартала»:
  
  Разница, по сути, заключается в включении или исключении полей промежуточных итогов из диапазона ячеек, которые вы хотите отформатировать.
  
  Вы всегда можете изменить поля, к которым применяется правило, в окне "Редактировать правило форматирования".
  
  В поле «Применить правило к» отображается ячейка, пересекающая поля. Вы можете выбрать другое поле в сводной таблице, открыв это окно, чтобы изменить поля. Подробности смотрите в видео выше.
  
  Что происходит при изменении сводной таблицы?
  
  Замечательно то, что при изменении сводной таблицы Excel запоминает примененные вами правила. Поэтому он сохраняет соответствующее форматирование, несмотря на переупорядочение данных.
  
  Смысл сводной таблицы в том, что вы можете быстро и легко изменить представление о данных. Например, одним нажатием кнопки вы можете перейти от просмотра продаж по регионам к продажам на одного представителя и количеству представителей по регионам.
  
  Правила условного форматирования остаются неизменными независимо от того, как изменяются данные. Это включает в себя применение фильтров с помощью срезов, изменение макета и параметров или перемещение включенных полей в области строк или столбцов.
  
  Например, вот часть сводной таблицы, в которой перечислены продукты для каждого региона.
  
  < /p>
  А вот та же сводная таблица, в которой вместо этого разбиваются регионы по каждому продукту.
  
  < /p>
  Форматирование зеленой заливки для первых десяти записей остается нетронутым, даже несмотря на то, что я изменил порядок данных.
  
  Исключение
  
  Условное форматирование будет удалено, если любое из полей, включенных в форматирование, будет удалено из сводной таблицы. Это просто потому, что Excel не может применять правила к полям, которых больше не существует.
  
  Когда вы откроете Диспетчер правил условного форматирования после удаления одного из полей, вы заметите, что правило полностью исчезло.
  
  Дополнительная информация о сводных таблицах и условном форматировании
  
  Нужно напомнить о сводных таблицах? Прочтите этот пост о сводных таблицах и информационных панелях, чтобы посмотреть первый из трех видеороликов из серии, в которой рассказывается о сводных таблицах, сводных диаграммах, PivotPal и многом другом.
  
  Вот еще несколько сообщений об условном форматировании:
  
  Какой тип условных форматов вы хотите изучить?
  
  В этом посте и видео объясняется, как применять условное форматирование к сводным таблицам. В видео и сопутствующем файле Excel я показываю несколько примеров.
  
  Какие еще типы условного форматирования вы хотите изучить? Пожалуйста, оставьте комментарий ниже с типом форматирования или типом отчета, и я создам несколько дополнительных руководств.
  
  Чтобы применить условное форматирование к сводной таблице, создайте новое правило условного форматирования и обратите особое внимание на настройки «применить правило к», как описано ниже. В показанном примере применяются два правила. Зеленым цветом показаны первые 5 значений по следующему правилу:
  
  Подробнее
  
  Сводные таблицы являются динамическими и часто изменяются при обновлении данных. Если вы создали правила условного форматирования только на основе «выбранных ячеек», вы можете обнаружить, что условное форматирование теряется или не применяется ко всем данным при изменении сводной таблицы или при обновлении данных.
  
  Лучший вариант — правильно настроить правило с самого начала. Выберите любую ячейку в данных, которые вы хотите отформатировать, а затем выберите «Новое правило» в меню условного форматирования на вкладке «Главная» ленты. В верхней части окна вы увидите настройку, к каким ячейкам применить условное форматирование. Для показанного примера мы хотим:
  
  "Все ячейки, показывающие сумму "стоимости продаж" для имени и "даты"
  
  Примечание. Если выбрать "Все ячейки, отображающие сумму "стоимости продаж", будут также включены строки и столбцы общей суммы, которые обычно не нужны".
  
  Редактирование существующих правил для исправления нарушенного форматирования
  
  Если у вас уже настроено правило, которое неправильно форматирует все значения, отредактируйте правило и при необходимости измените параметр выбора ячеек. Вы можете получить доступ к существующим правилам в разделе Домашняя страница > Условное форматирование > Управление правилами.
  
  В показанном примере диспетчер правил отображает два правила, подобные этим:
  
  Чтобы изменить правило, выберите его и нажмите кнопку "Изменить правило". Затем настройте параметры в разделе "Применить правило к".
  
  Примечание. При удалении целевого поля из сводной таблицы условное форматирование теряется.
  
  Читайте также: