Меню

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

Сводные таблицы Excel

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

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

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

Рис 6.25. Создайте простую сводную таблицу

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

Сначала выделите все поле Объем продаж в области значений. После выделения объема для каждого периода Торговый период перейдите на вкладку ленты Главная и щелкните на кнопке Условное форматирование (Conditional Formatting), находящейся в группе Стили (Styles), как показано на рис. 6.26.

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

Как видно на рис. 6.27, в ячейки добавляется набор гистограмм, соответствующих хранящимся в них значениям. Несколько похоже на горизонтальную гистограмму, не правда ли? Самое удивительное, что при фильтрации данных (например, рынков сбыта), осуществляемой в области фильтра отчета, гистограммы динамически обновляются в соответствии с набором выбранных рынков сбыта.

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

Рис. 6.27. Условные гистограммы добавляются с помощью всего нескольких щелчков

В следующем списке приведены готовые сценарии условного форматирования:

  • 10 первых элементов (Top Nth Items);
  • первые 10% (Top Nth %);
  • 10 последних элементов (Bottom Nth Items);
  • последние 10% (Bottom Nth %);
  • выше среднего (Above Average);
  • ниже среднего (Below Average).

Как видите, Excel 2010 содержит сценарии с наиболее распространенными критериями условного форматирования.

Чтобы удалить примененное ранее условное форматирование, поместите курсор в сводную таблицу, перейдите на вкладку Главная (Ноmе), щелкните на кнопке Условное форматирование (Conditional Formatting) группы Стили (Styles) и выберите в раскрывающемся меню команду Удалить правила → Удалить правила из этой сводной таблицы (Clear Rules^Clear Rules from this PivotTable).

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

Рис. 6.28. В этой сводной таблице отображаются поля Объем продаж, Период продаж (в часах) и вычисляемое поле, определяющее значение выручки за час

В этом сценарии мы попытаемся отследить взаимосвязь между общим объемом продаж и выручкой за час. Основная идея заключается в применении условного форматирования, облегчающего поиск различий и совпадений. Поместите курсор в столбец Объем продаж. Перейдите на вкладку Главная (Ноmе) и щелкните на кнопке Условное форматирование (Conditional Formatting). Выберите команду Создать правило (New Rule). На экране появится диалоговое окно Создание правила форматирования (New Formatting Rule), показанное на рис. 6.29.

Рис. 6.29. Диалоговое окно Создание правила форматирования

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

  • к выделенным ячейкам (Selected Cells). Условное форматирование применяется ко всем выделенным ячейкам.
  • ко всем ячейкам, содержащим значения «Объем продаж» (All Cells Showing «Sales_Amount» Values). Условное форматирование применяется ко всем значениям в столбце Объем продаж, а также к промежуточным и общим итогам. Лучше всего применять этот вариант при анализе данных, для которых определяется среднее, процентное соотношение или другие величины, представляющие различные уровни одной и той же числовой величины.
  • ко всем ячейкам, содержащим значения «Объем продаж» для «Рынок сбыта» (All Cells Showing «Sales_Amount» Values for «Market»). Условное форматирование применяется ко всем значениям в столбце Объем продаж на уровне Рынок сбыта (исключая промежуточные и общие итоги). Его идеально использовать при анализе отдельных значений.
Читайте также:  Как настроить мой арбитр

Названия команд Объем продаж и Рынок сбыта диалогового окна Создание правила форматирования изменяются от одной таблицы к другой и отображают названия полей, содержащихся в области столбцов и активных элементов данных.

В нашем примере третий вариант кажется наиболее удачным, поэтому установите переключатель ко всем ячейкам, содержащим значения «Объем продаж» для «Рынок сбыта», как показано на рис. 6.30.

Рис. 6.30. Установите переключатель наиболее приемлемого варианта выделения ячеек, к которым будет применяться условное форматирование

В разделе Выберите тип правила (Select a Rule Туре) укажите правило, согласно которому будет применяться условное форматирование.

  • Форматировать все ячейки на основании их значений (Format All Cells Based on Their Values). Этот переключатель позволяет применять условное форматирование к ячейкам, содержимое которых соответствует заданному диапазону значений. Таким образом, значения в заданном диапазоне сравниваются с уже введенными в ячейках. Лучше всего применять его для определения отклонений в большом наборе данных.
  • Форматировать только ячейки, которые содержат (Format Only Cells That Contain). Данный переключатель применяется для условного форматирования ячеек, удовлетворяющих определенным условиям. Обратите внимание на то, что в этом случае значения в ячейках не сравниваются с другими значениями. Чаще всего используется при сравнении всего набора данных с заранее заданной характеристикой.
  • Форматировать только первые и последние значения (Format Only Top or Bottom Ranked Values). Играет важную роль при применении условного форматирования только к первым или последним нескольким значениям набора данных.
  • Форматировать только значения, которые находятся выше или ниже среднего (Format Only Values That Are Above or Below the Average). Этот переключатель позволяет применять условное форматирование к значениям, большим или меньшим среднего значения, рассчитанного для набора данных.
  • Использовать формулу для определения форматируемых ячеек (Use a Formula to Determine Which Cells to Format). В этом варианте определение значений, к которым применяется условное форматирование, осуществляется согласно пользовательской формуле. Если значение ячейки, подставленное в формулу, приводит к получению результата true (истина), то к такой ячейке применяется условное форматирование. Если же возвращается результат false (ложь), то условное форматирование к ячейке не применяется.

Гистограммы, цветовые шкалы и наборы значков применяются только в случае, если выделенные ячейки форматируются на основе введенных в них значений. Это означает, что для использования указанных индикаторов необходимо установить первый переключатель — Форматировать все ячейки на основании их значений (Format All Cells Based on Their Values).

В нашем сценарии мы будем обозначать проблемные области с помощью набора значков. Поэтому в качестве типа форматирования нужно выбрать параметр Форматировать все ячейки на основании их значений. Наконец, нужно определить детальные параметры условного форматирования, для чего используются настройки раздела Изменение описания правила (Edit the Ruie Description). Чтобы добавить в проблемные ячейки значки, выберите в раскрывающемся меню Стиль формата (Format Style) значение Наборы значков (Icon Sets).

В раскрывающемся списке Стиль значка (Icon Style) выберите значение 3 знака. Такой стиль значков идеально подходит в случаях, когда сводную таблицу невозможно полностью разукрасить разными цветами. В текущий момент диалоговое окно Создание правила форматирования должно выглядеть так, как показано на рис. 6.31.

Рис. 6.31. Выберите в раскрывающемся меню Стиль формата значение Наборы значков

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

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

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

Рис. 6.32. Условное форматирование позволяет добиться весьма познавательных и важных результатов

Не спешите закрывать сводную таблицу и внимательно проанализируйте ее. В полученном представлении любой менеджер может легко отследить взаимосвязь между обшим доходом и выручкой за час. Так, например, менеджер рынка сбыта Даллас будет видеть, что его объем продаж наименьший, а выручка в час — наибольшая. Владея такой информацией, менеджер наверняка определит, что выручка за час, скорее всего, слишком высокая для его рынка. В противоположность ему менеджер с рынка Нью-Йорк будет знать, что его доход максимальный при самых минимальных выручках за час. Последняя характеристика вряд ли обрадует его, даже несмотря на лидерство по общему доходу.

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

Источник



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

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

Читайте также:  Как настроить бухучет в общепите

то на первый взгляд всё сработает:

Правило будет выглядеть так

Однако, если вы обновите сводную таблицу, то форматирование исчезнет! А правило неожиданно станет таким:

В чём тут дело? Оказывается при создании правила УФ нельзя совместно выделять и обычные ячейки и ячейки сводной таблицы. Встаньте на любую ячейку сводной таблицы из области значений и начните создавать правило УФ:

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

Обратите внимание как выглядит правило УФ — добавляется иконка сводной таблицы.

Подобное УФ сохранит свою работоспособность даже, если вы поле Город перетащите из раздела СТРОКИ в раздел КОЛОННЫ сводной таблицы. Вот так:

Это всё, что я имел сказать про условное форматирование сводных таблиц 🙂

Источник

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

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

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

Рис. 1. Исходная сводная таблица

Скачать заметку в формате Word или pdf, примеры в формате Excel

Сначала выделите все ячейки, которые подлежат условному форматированию; в нашем примере С4:С17. Перейдите на вкладку ленты Главная в группу Стили и щелкните на кнопке Условное форматирование (рис. 2).

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

В области сводной таблицы появятся гистограммы наряду со значениями в поле Сумма по полю Объем продаж2. Чтобы отобразить одну лишь гистограмму, выполните следующие действия.

  1. Щелкните на кнопке раскрывающегося меню Условное форматирование, и выберите пункт Управление правилами.
  2. В диалоговом окне Диспетчер правил условного форматирования выберите только что созданное правило Гистограмма и щелкните на кнопке Изменить правило.
  3. Установите флажок Показывать только столбец (рис. 3).

Рис. 3. Установите флажок Показывать только столбец, чтобы отображать только гистограммы

В ячейки добавляется набор гистограмм, соответствующих хранящимся в них значениям (рис. 4). Немного напоминает горизонтальную гистограмму, не правда ли? Самое удивительное, что при фильтрации данных (например, рынков сбыта), осуществляемой в области ФИЛЬТРЫ, гистограммы динамически обновляются в соответствии с набором выбранных рынков сбыта. Вы можете переименовать заголовки столбцов сводной диаграммы, а также выбрать по своему усмотрению ширину столба Гистограмма.

Рис. 4. Гистограммы условного форматирования

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

В следующем списке приведены готовые сценарии условного форматирования:

  • 10 первых элементов;
  • первые 10%;
  • 10 последних элементов;
  • последние 10%;
  • выше среднего;
  • ниже среднего.

Чтобы удалить примененное ранее условное форматирование, поместите курсор в сводную таблицу, перейдите на вкладку Главная, щелкните на кнопке Условное форматирование и выберите в раскрывающемся меню команду Удалить правилаУдалить правила из этой сводной таблицы (см. рис. 2).

Создание пользовательских правил условного форматирования

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

Читайте также:  Как настроить уже установленный k lite codec pack

Рис. 5. Исходная сводная таблица

В этом сценарии мы попытаемся отследить связь между общим объемом продаж и выручкой за час. Основная идея заключается в применении условного форматирования, облегчающего поиск различий и совпадений. Сначала поместите курсор в столбец Объем продаж. Перейдите на вкладку Главная и щелкните на кнопке Условное форматирование. Выберите команду Создать правило. На экране появится диалоговое окно Создание правила форматирования (рис. 6).

Рис. 6. Диалоговое окно Создание правила форматирования; установки по умолчанию

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

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

Названия Объем продаж и Рынок сбыта диалогового окна Создание правила форматирования изменяются от одной таблицы к другой и отражают названия полей, содержащихся в области столбцов и активных элементов данных.

В рассматриваемом примере третий вариант кажется наиболее удачным, поэтому установите переключатель ко всем ячейкам, содержащим значения «Объем продаж» для «Рынок сбыта» (рис. 7). В разделе Выберите тип правила укажите правило, согласно которому будет применяться условное форматирование:

  • Форматировать все ячейки на основании их значений. Этот переключатель позволяет применять условное форматирование к ячейкам, содержимое которых соответствует заданному диапазону значений. Таким образом, значения в заданном диапазоне сравниваются с уже введенными в ячейках. Лучше всего применять его для определения отклонений в большом наборе данных.
  • Форматировать только ячейки, которые содержат. Данный переключатель применяется для условного форматирования ячеек, удовлетворяющих определенным условиям. Обратите внимание на то, что в этом случае значения в ячейках не сравниваются с другими значениями. Чаще всего используется при сравнении всего набора данных с заранее заданной характеристикой.
  • Форматировать только первые и последние значения. Играет важную роль при применении условного форматирования только к первым или последним нескольким значениям набора данных.
  • Форматировать только значения, которые находятся выше или ниже среднего. Этот переключатель позволяет применять условное форматирование к значениям, большим или меньшим среднего значения, рассчитанного для набора данных.
  • Использовать формулу для определения форматируемых ячеек. В случае выбора этого варианта идентификация значений, к которым применяется условное форматирование, осуществляется согласно пользовательской формуле. Если значение ячейки, подставленное в формулу, приводит к получению результата ИСТИНА, то к такой ячейке применяется условное форматирование. Если же возвращается результат ЛОЖЬ, то условное форматирование к ячейке не применяется.

Рис. 7. Диалоговое окно Создание правила форматирования; установки по умолчанию

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

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

Наконец, нужно определить детальные параметры условного форматирования, для чего используются настройки раздела Измените описание правила. Чтобы добавить в проблемные ячейки значки, выберите в раскрывающемся меню Стиль формата значение Наборы значков. В раскрывающемся списке Стиль значка выберите стиль, применяемый для выполнения анализа. Стиль, заданный на рис. 7, идеально подходит в случаях, когда сводную таблицу невозможно полностью разукрасить разными цветами. В заданной конфигурации настроек программа будет добавлять разные значки, распределяя значения в ячейках по трем следующим категориям: >=67, >=33 и Наталья 20.12.2018 в 20:31

Скажите, пожалуйста, для сводной таблицы в кубе olap я настроила сводную диаграмму, применила к ней условное форматирование, добавила значения. Ежемесячно в куб добавляется новый месяц с данными и если фильтровать по месяцу, то все мое условное форматирование исчезает. Как настроить так, чтобы оно сохранялось? Excel 1016

Наталья, сам недавно столкнулся с этой проблемой: в сводной таблице устанавливаю фильтр, и на три столбца накладываю условное форматирование. У меня в поле Применяется к =$J$4:$L$23. Затем устанавливаю новый фильтр, отражаются иные данные, но в том же диапазоне $J$4:$L$23. Условное форматирование «слетает». Захожу повторно в окно условного форматирования, вижу что теперь отражается иной диапазон, и опять выбираю =$J$4:$L$23. Не нашел как побороть 🙁

Источник