Если вы выбрали Excel для создания панели мониторинга, попробуйте использовать таблицы Excel, если они служат цели. Таблицы Excel с условным форматированием и спарклайнами являются лучшим и простым выбором для вашей панели инструментов.
В Excel вы можете использовать условное форматирование для визуализации данных. Например, в таблице, содержащей данные о продажах за последний квартал по регионам, можно выделить верхние 5% -ые значения.
Вы можете указать любое количество условий форматирования, указав Правила. Вы можете выбрать встроенные в Excel правила, соответствующие вашим условиям, из правил Highlight Cells Rules или Top / Bottom Rules. Вы также можете определить свои собственные правила.
Вы выбираете параметры форматирования, подходящие для вашей визуализации данных — Панели данных, Цветовые шкалы или Наборы значков.
В этой главе вы изучите правила условного форматирования, параметры форматирования и добавление / управление правилами.
Подсветка клеток
Вы можете использовать правила выделения ячеек, чтобы назначить формат ячейкам, содержащим данные, удовлетворяющие любому из следующих критериев:
-
Числа в данном числовом диапазоне: больше чем, меньше, между и равно.
-
Значения, которые являются дубликатами или уникальными.
Числа в данном числовом диапазоне: больше чем, меньше, между и равно.
Значения, которые являются дубликатами или уникальными.
Рассмотрим следующую сводку результатов, которые вы хотите представить —
Предположим, вы хотите выделить значения общей суммы более 1000000.
- Выберите столбец — Общая сумма.
- Нажмите «Условное форматирование» в группе «Стили» на вкладке «Главная».
- Нажмите на Highlight Cells Rules в раскрывающемся списке.
- Нажмите Greater Than во втором выпадающем списке.
Откроется диалоговое окно «Больше, чем».
-
В поле «Форматировать ячейки БОЛЬШЕ ЧЕМ:» укажите условие как 1000000.
-
В поле с выберите вариант форматирования как «Зеленая заливка темно-зеленым текстом».
В поле «Форматировать ячейки БОЛЬШЕ ЧЕМ:» укажите условие как 1000000.
В поле с выберите вариант форматирования как «Зеленая заливка темно-зеленым текстом».
- Нажмите кнопку ОК.
Как вы можете видеть, значения, удовлетворяющие указанному условию, выделяются в указанном формате.
Верхние / нижние правила
Вы можете использовать верхние / нижние правила, чтобы назначить формат значениям, соответствующим любому из следующих критериев:
-
Top 10 Items — Клетки, которые ранжируются в верхнем N, где 1 <= N <= 1000.
-
Верхние 10% — ячейки, которые занимают место в верхнем n%, где 1 <= n <= 100.
-
Нижние 10 элементов — ячейки, которые ранжируются в нижней части N, где 1 <= N <= 1000.
-
Нижние 10% — ячейки, которые ранжируются в нижней части n%, где 1 <= n <= 100.
-
Выше среднего — клетки выше среднего для выбранного диапазона.
-
Ниже среднего — ячейки ниже среднего для выбранного диапазона.
Top 10 Items — Клетки, которые ранжируются в верхнем N, где 1 <= N <= 1000.
Верхние 10% — ячейки, которые занимают место в верхнем n%, где 1 <= n <= 100.
Нижние 10 элементов — ячейки, которые ранжируются в нижней части N, где 1 <= N <= 1000.
Нижние 10% — ячейки, которые ранжируются в нижней части n%, где 1 <= n <= 100.
Выше среднего — клетки выше среднего для выбранного диапазона.
Ниже среднего — ячейки ниже среднего для выбранного диапазона.
Предположим, вы хотите выделить значения общей суммы, которые находятся в верхних 5%.
- Выберите столбец — Общая сумма.
- Нажмите «Условное форматирование» в группе «Стили» на вкладке «Главная».
- Нажмите Top / Bottom Rules в раскрывающемся списке.
- Нажмите на десятку% во втором раскрывающемся списке.
Откроется диалоговое окно Top Ten%.
-
В ячейках «Формат», которые ранжируются в поле «TOP:», укажите условие как 5%.
-
В поле с выберите вариант форматирования как «Зеленая заливка темно-зеленым текстом».
В ячейках «Формат», которые ранжируются в поле «TOP:», укажите условие как 5%.
В поле с выберите вариант форматирования как «Зеленая заливка темно-зеленым текстом».
-
Нажмите кнопку ОК. Верхние значения 5% будут выделены в указанном формате.
Нажмите кнопку ОК. Верхние значения 5% будут выделены в указанном формате.
Панели данных
Вы можете использовать цветные панели данных, чтобы увидеть значение относительно других значений. Длина панели данных представляет значение. Более длинный столбец представляет более высокое значение, а более короткий столбец представляет более низкое значение. Вы можете использовать сплошные или градиентные цвета для панелей данных.
-
Выберите столбец — Общая сумма.
-
Нажмите «Условное форматирование» в группе «Стили» на вкладке «Главная».
-
Нажмите на панели данных в раскрывающемся списке.
-
Нажмите на синюю панель данных под градиентной заливкой во втором раскрывающемся списке.
Выберите столбец — Общая сумма.
Нажмите «Условное форматирование» в группе «Стили» на вкладке «Главная».
Нажмите на панели данных в раскрывающемся списке.
Нажмите на синюю панель данных под градиентной заливкой во втором раскрывающемся списке.
Значения в столбце будут выделены, показывая маленькие, промежуточные и большие значения с градиентными полосами заливки синего цвета.
-
Выберите столбец — Общая сумма.
-
Нажмите «Условное форматирование» в группе «Стили» на вкладке «Главная».
-
Нажмите на панели данных в раскрывающемся списке.
-
Нажмите на оранжевую панель данных под «Сплошная заливка» во втором раскрывающемся списке.
Выберите столбец — Общая сумма.
Нажмите «Условное форматирование» в группе «Стили» на вкладке «Главная».
Нажмите на панели данных в раскрывающемся списке.
Нажмите на оранжевую панель данных под «Сплошная заливка» во втором раскрывающемся списке.
Значения в столбце будут выделены, показывая маленькие, промежуточные и большие значения по высоте с оранжевыми полосами.
Предположим, вы хотите выделить продажи по сравнению с целевым показателем, скажем, 800000.
-
Создайте столбец со значениями = [@ [Total Amount]] — 800000.
-
Выберите новый столбец.
-
Нажмите «Условное форматирование» в группе «Стили» на вкладке «Главная».
-
Нажмите на панели данных в раскрывающемся списке.
-
Нажмите на зеленую панель данных под градиентной заливкой во втором раскрывающемся списке.
Создайте столбец со значениями = [@ [Total Amount]] — 800000.
Выберите новый столбец.
Нажмите «Условное форматирование» в группе «Стили» на вкладке «Главная».
Нажмите на панели данных в раскрывающемся списке.
Нажмите на зеленую панель данных под градиентной заливкой во втором раскрывающемся списке.
Панели данных начинаются в середине каждой ячейки и растягиваются влево для отрицательных значений и вправо для положительных значений.
Как вы можете видеть, полосы, растягивающиеся вправо, имеют зеленый цвет, обозначающий положительные значения, а полосы, растягивающиеся влево, имеют красный цвет, обозначающий отрицательные значения.
Цветовые шкалы
Вы можете использовать Цветовые шкалы, чтобы увидеть значение в ячейке относительно значений в других ячейках столбца. Цвет указывает, где значение каждой ячейки попадает в этот диапазон. Вы можете иметь либо 3-х цветную шкалу, либо 2-х цветную шкалу.
-
Выберите столбец — Общая сумма.
-
Нажмите «Условное форматирование» в группе «Стили» на вкладке «Главная».
-
Нажмите на шкалы цветов в раскрывающемся списке.
-
Нажмите на шкалу зелено-желто-красного цвета во втором раскрывающемся списке.
Выберите столбец — Общая сумма.
Нажмите «Условное форматирование» в группе «Стили» на вкладке «Главная».
Нажмите на шкалы цветов в раскрывающемся списке.
Нажмите на шкалу зелено-желто-красного цвета во втором раскрывающемся списке.
Как и в случае с правилами выделения ячеек, цветовая шкала использует затенение ячеек для отображения различий в значениях ячеек. Как вы можете видеть в предварительном просмотре, различия в оттенках не являются заметными для этого набора данных.
- Нажмите на Дополнительные правила во втором раскрывающемся списке.
Откроется диалоговое окно «Новое правило форматирования».
-
Нажмите «Форматировать все ячейки на основе их значений» в поле «Выбрать тип правила».
-
В поле Изменить описание правила выберите следующее —
-
Выберите 3-х цветную шкалу в поле Формат стиля.
-
Под Midpoint, для значения — введите 75.
-
Нажмите «Форматировать все ячейки на основе их значений» в поле «Выбрать тип правила».
В поле Изменить описание правила выберите следующее —
Выберите 3-х цветную шкалу в поле Формат стиля.
Под Midpoint, для значения — введите 75.
Нажмите кнопку ОК.
Как вы можете видеть, с определенной цветовой шкалой значения четко затенены, изображая диапазон данных.
Наборы иконок
Вы можете использовать наборы значков для визуализации числовых различий. В Excel у вас есть набор иконок —
| Тип набора иконок | Наборы иконок |
|---|---|
| направленный |  |
| Формы |  |
| индикаторы |  |
| Рейтинги |  |
Как вы можете заметить, набор иконок состоит из трех-пяти символов. Вы можете определить критерии, чтобы связать значок со значениями в диапазоне ячеек. Например, красная стрелка вниз для небольших чисел, зеленая стрелка вверх для больших чисел и желтая горизонтальная стрелка для промежуточных значений.
-
Выберите столбец — Общая сумма.
-
Нажмите «Условное форматирование» в группе «Стили» на вкладке «Главная».
-
Нажмите на наборы иконок в раскрывающемся списке.
-
Нажмите на 3 стрелки (цветные) в группе Направление во втором раскрывающемся списке.
Выберите столбец — Общая сумма.
Нажмите «Условное форматирование» в группе «Стили» на вкладке «Главная».
Нажмите на наборы иконок в раскрывающемся списке.
Нажмите на 3 стрелки (цветные) в группе Направление во втором раскрывающемся списке.
Цветные стрелки появляются в выбранном столбце в зависимости от значений.
Использование пользовательских правил
Вы можете определить свои собственные правила и отформатировать диапазон ячеек, удовлетворяющих определенному условию.
- Выберите столбец — Общая сумма.
- Нажмите «Условное форматирование» в группе «Стили» на вкладке «Главная».
- Нажмите на новое правило в раскрывающемся списке.
Откроется диалоговое окно «Новое правило форматирования».
-
Нажмите Использовать формулу, чтобы определить, какие ячейки форматировать, в поле «Выберите тип правила».
-
В окне «Редактировать описание правила» выполните следующие действия:
-
Введите формулу в поле — Отформатируйте значения, где эта формула истинна. Например, = PercentRank.INC ($ E $ 3: $ E $ 13, E3)> = 0,7
-
Нажмите на кнопку Формат.
-
Выберите формат. Например, шрифт — жирный, а заливка — оранжевый.
-
Нажмите на ОК.
-
-
Проверьте предварительный просмотр.
Нажмите Использовать формулу, чтобы определить, какие ячейки форматировать, в поле «Выберите тип правила».
В окне «Редактировать описание правила» выполните следующие действия:
Введите формулу в поле — Отформатируйте значения, где эта формула истинна. Например, = PercentRank.INC ($ E $ 3: $ E $ 13, E3)> = 0,7
Нажмите на кнопку Формат.
Выберите формат. Например, шрифт — жирный, а заливка — оранжевый.
Нажмите на ОК.
Проверьте предварительный просмотр.
Нажмите OK, если предварительный просмотр в порядке. Значения в наборе данных, удовлетворяющие формуле, будут выделены в выбранном вами формате.
Управление правилами условного форматирования
Вы можете управлять Правилами условного форматирования с помощью диалогового окна Диспетчер правил условного форматирования.
Нажмите « Условное форматирование» в группе «Стили» на вкладке «Главная». Нажмите Управление правилами в раскрывающемся списке.
Откроется диалоговое окно диспетчера правил условного форматирования. Вы можете просмотреть все существующие правила. Вы можете добавить новое правило, удалить правило и / или отредактировать правило, чтобы изменить его.