Панели мониторинга 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в группе Стили на вкладке Главная. В раскрывающемся списке щелкните Управление правилами.
Откроется диалоговое окно «Диспетчер правил условного форматирования». Вы можете просмотреть все существующие правила. Вы можете добавить новое правило, удалить правило и / или отредактировать правило, чтобы изменить его.