Панели мониторинга Excel - условное форматирование

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

Если вы выбрали Excel для создания панели мониторинга, попробуйте использовать таблицы Excel, если они служат цели. Благодаря условному форматированию и спарклайнам, таблицы Excel - лучший и простой выбор для вашей информационной панели.

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

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

Вы выбираете параметры форматирования, которые подходят для визуализации данных - панели данных, цветовые шкалы или наборы значков.

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

Выделение ячеек

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

  • Числа в заданном числовом диапазоне: больше, меньше, между и равно.

  • Дублирующиеся или уникальные значения.

Рассмотрим следующее резюме результатов, которые вы хотите представить -

Предположим, вы хотите выделить значения общей суммы, превышающие 1000000.

  • Выберите столбец - Общая сумма.
  • Нажмите «Условное форматирование» в группе «Стили» на вкладке «Главная».
  • В раскрывающемся списке нажмите на Highlight Cells Rules.
  • Нажмите «Больше чем» во втором появившемся раскрывающемся списке.

Появится диалоговое окно «Больше чем».

  • В поле Формат ячеек БОЛЬШЕ ЧЕМ: укажите условие как 1000000.

  • В поле с выберите вариант форматирования: Зеленая заливка с темно-зеленым текстом.

  • Щелкните кнопку ОК.

Как видите, значения, удовлетворяющие указанному условию, выделяются в указанном формате.

Верхние / нижние правила

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

  • Top 10 Items - Ячейки, занимающие первые позиции N, где 1 <= N <= 1000.

  • Top 10% - Ячейки, входящие в верхние n%, где 1 <= n <= 100.

  • Bottom 10 Items - Ячейки, занимающие нижние позиции N, где 1 <= N <= 1000.

  • Bottom 10% - Ячейки, занимающие нижние n%, где 1 <= n <= 100.

  • Above Average - Ячейки выше среднего для выбранного диапазона.

  • Below Average - Ячейки ниже среднего для выбранного диапазона.

Предположим, вы хотите выделить значения общей суммы, которые входят в верхние 5%.

  • Выберите столбец - Общая сумма.
  • Нажмите «Условное форматирование» в группе «Стили» на вкладке «Главная».
  • В раскрывающемся списке выберите «Правила сверху / снизу».
  • Во втором появившемся раскрывающемся списке нажмите «Десять первых%».

Появится диалоговое окно Top Ten%.

  • В ячейках формата, занимающих место в поле TOP:, укажите условие как 5%.

  • В поле с выберите вариант форматирования: Зеленая заливка с темно-зеленым текстом.

  • Щелкните кнопку ОК. Верхние 5% значений будут выделены в указанном формате.

Панели данных

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

  • Выберите столбец - Общая сумма.

  • Нажмите «Условное форматирование» в группе «Стили» на вкладке «Главная».

  • Щелкните панели данных в раскрывающемся списке.

  • Нажмите на синюю панель данных под градиентной заливкой во втором появившемся раскрывающемся списке.

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

  • Выберите столбец - Общая сумма.

  • Нажмите «Условное форматирование» в группе «Стили» на вкладке «Главная».

  • Щелкните панели данных в раскрывающемся списке.

  • Нажмите на оранжевую панель данных под сплошной заливкой во втором появившемся раскрывающемся списке.

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

Предположим, вы хотите выделить продажи по сравнению с целевым показателем, скажем, 800000.

  • Создайте столбец со значениями = [@ [Total Amount]] - 800000.

  • Выберите новый столбец.

  • Нажмите «Условное форматирование» в группе «Стили» на вкладке «Главная».

  • Щелкните панели данных в раскрывающемся списке.

  • Щелкните зеленую панель данных под градиентной заливкой во втором появившемся раскрывающемся списке.

Полосы данных начинаются в середине каждой ячейки и растягиваются влево для отрицательных значений и вправо для положительных значений.

Как вы можете заметить, полосы, тянущиеся вправо, имеют зеленый цвет, указывая на положительные значения, а полосы, тянущиеся влево, имеют красный цвет, указывая на отрицательные значения.

Цветовые шкалы

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

  • Выберите столбец - Общая сумма.

  • Нажмите «Условное форматирование» в группе «Стили» на вкладке «Главная».

  • Щелкните Цветовые шкалы в раскрывающемся списке.

  • Во втором появившемся раскрывающемся списке щелкните «Шкала зеленого-желто-красного».

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

  • Щелкните Дополнительные правила во втором раскрывающемся списке.

Откроется диалоговое окно «Новое правило форматирования».

  • Щелкните Форматировать все ячейки на основе их значений в поле «Выбрать тип правила».

  • В поле Изменить описание правила выберите следующее -

    • В поле «Стиль формата» выберите «Трехцветная шкала».

    • В разделе Midpoint в поле Value введите 75.

Щелкните кнопку ОК.

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

Наборы иконок

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

Тип набора значков Наборы иконок
Направленный
Формы
Индикаторы
Рейтинги

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

  • Выберите столбец - Общая сумма.

  • Нажмите «Условное форматирование» в группе «Стили» на вкладке «Главная».

  • Щелкните Наборы значков в раскрывающемся списке.

  • Щелкните 3 стрелки (цветные) в группе «Направление» во втором появившемся раскрывающемся списке.

Цветные стрелки появляются в выбранном столбце в зависимости от значений.

Использование специальных правил

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

  • Выберите столбец - Общая сумма.
  • Нажмите «Условное форматирование» в группе «Стили» на вкладке «Главная».
  • Щелкните Новое правило в раскрывающемся списке.

Откроется диалоговое окно «Новое правило форматирования».

  • Щелкните Использовать формулу, чтобы определить, какие ячейки следует форматировать, в поле «Выбор типа правила».

  • В поле «Изменить описание правила» сделайте следующее:

    • Введите формулу в поле - Форматируйте значения, в которых эта формула верна. Например, =PercentRank.INC($E$3:$E$13,E3)>=0.7

    • Нажмите кнопку «Форматировать».

    • Выберите формат. Например, Font - полужирный и Fill - оранжевый.

    • Щелкните ОК.

  • Проверьте предварительный просмотр.

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

Управление правилами условного форматирования

Вы можете управлять правилами условного форматирования с помощью диалогового окна «Диспетчер правил условного форматирования».

Нажмите Conditional Formattingв группе Стили на вкладке Главная. В раскрывающемся списке щелкните Управление правилами.

Откроется диалоговое окно «Диспетчер правил условного форматирования». Вы можете просмотреть все существующие правила. Вы можете добавить новое правило, удалить правило и / или отредактировать правило, чтобы изменить его.