Главная » Статьи » Excel » Макросы и программы VBA

Цветовые статусы

Цветовой статус

Часто возникает необходимость в отчётах акцентировать статус какого-либо процесса при помощи цвета. При этом довольно популярна метафора светофора. Если всё хорошо - зелёный статус, что-то настораживает - жёлтый, всё плохо - красный. Таким образом, тот, кто смотрит ваши отчёты, имеет возможность по цвету сразу обращаться к проблемным цифрам. Отчёты становятся более наглядными, интуитивно понятными и экономят время просматривающих.

Ограничения условного форматирования

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

Фигуры

Фигурами в MS Office можно нарисовать всё, что угодно. Серьёзно. Любой сложный рисунок "собирается" из простых элементов. Это вопрос только времени и стараний. В этой статье мы будем управлять вот такими несложными, но достаточно привлекательными светофорами, которые легко делаются из фигур овал (круг - частный случай овала/эллипса) и кольцо.

Цель

Мы хотим визуализировать соотношение фактических и плановых расходов по проектам при помощи наших светофоров. Вот так:

Пример

Скачать

Последовательность шагов

  1. Для начала подготовим данные, на основе которых будем вычислять статусы. На нашем примере это столбцы: Проект, Бюджет, Факт.

  2. Подготовим вспомогательную таблицу, на основе которой будем присваивать значения статусов. В нашем случае эта таблица располагается на листе Настройки, оформлена в виде умной таблицы с названием Шкала. Статус G означает Green (зеленый), Y - Yellow (жёлтый), R - Red (красный).

  3. В ячейку E3 листа Статусы введена формула
    =ЕСЛИОШИБКА(ВПР((D3-C3)/C3;Шкала;2);"D").
    Как видите, мы находим разницу между фактом и бюджетом и делим её на бюджет. Минимальное значение этого соотношения -1 (минус единица) достигается при нулевых фактических затратах. Этот факт определяет пороговое значение (-1 = -100%) для статуса G в таблице Шкала. Порог начала жёлтого цвета вы определяете сами - у меня он 0%. То есть зелёный цвет должен быть у всего, что в диапазоне от -100% до 0%. Жёлтый - от 0% до 15%. Красный - 15% и выше. Для выбора значения из Шкалы идеально подходит формула ВПР в своей диапазонной версии, которая ищёт диапазон, в который попадает значение ((D3-C3)/C3) в справочнике (Шкала), и возвращает из справочника содержимое ячейки на пересечении найденной строки и указанного столбца (2). Если вычисление функции ВПР (VLOOKUP) оканчивается ошибкой (например, когда Бюджет=0), то формула ЕСЛИОШИБКА (IFERROR) её перехватывает и возвращает в ячейку значение D, что будет означать, что светофор не горит (серый). Формулу из E3 распространяем на E4:E5.

  4. Формат данных диапазона E3:E5 устанавливаем в ";;;", что предотвращает появление значений ячеек на экране, чтобы цифры не выглядывали из-за светофоров, которые мы поместим над этими ячейками.

  5. Создаём именованный диапазон rngTrafLight для ячеек E3:E5.

  6. Создаём из фигур наши светофоры. Круги, цвет которых мы будем менять, называем именами figTL1 для E3, figTL2 для E4 и figTL3 для E5. Располагаем фигуры, там где они должны находиться.

  7. В редакторе Visual Basic for Application (Alt+F11) вставляем module с любым именем (у меня TL). Для этого щёлкните правой кнопкой по папке Modules и выберите Insert -> Module. Вставьте в модуль этот код:

  8. В редакторе VBA в лист Лист1 (Статусы) поместите код:

  9. Проверьте как всё работает.

Попробуйте! Это не сложно, но эффектно.

Категория: Макросы и программы VBA | Добавил: dsb75 (20.08.2014) | Автор: Батьянов Денис E W
Просмотров: 3483 | Теги: фигуры, цвет, VBA | Рейтинг: 0.0/0
Всего комментариев: 0
Добавлять комментарии могут только зарегистрированные пользователи.
[ Регистрация | Вход ]
Яндекс.Метрика