Как сделать светофор в экселе

Как сделать светофор в экселе

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

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

В 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. Проверьте как всё работает.

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

Как сделать светофор в экселе

Argument ‘Topic id’ is null or empty

Сейчас на форуме

© Николай Павлов, Planetaexcel, 2006-2023
info@planetaexcel.ru

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

ООО «Планета Эксел» ИНН 7735603520 ОГРН 1147746834949

ИП Павлов Николай Владимирович ИНН 633015842586 ОГРНИП 310633031600071

я не понимаю как реализовать светофор в эксель

Короче здравствуйте, мне на самом деле нужно только понять одно. Я приложил вам фотку и там обведено. Как реализовать так, чтобы если я ввел допустим в одну из ячеек «1», другие 2 ячейки принимали значение «0». А с остальным сам разберусь.

p.s. сори за качество.
94731 / 64177 / 26122
Регистрация: 12.04.2006
Сообщений: 116,782
Ответы с готовыми решениями:

Как реализовать такой «светофор»?
Доброго времени суток! Идея в том, что бы к компу прицепить 3 tid, красный, желтый, зеленый. Когда.

Реализовать светофор
Доброго времени суток. С javascript не работал, учу Java. Появилась задача написать простой.

Реализовать светофор с таймером
помогите откомпилировать или сказать какие компоненты надо using System; using.

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

Справочник туриста , не понимаю как реализовать
Здраствуйте! У меня горит курсовая а я только сажусь за её написание( Задания звучит как.

Светофор Excel 2010

Здравствуйте! Помогите пожалуйста с макросом: На Листе есть три круга (типа светофор). При нажатии на кнопку круги должны закрашиваться поочерёдно как на светофоре. Но макрос не закрашивает. Надо, чтобы макрос работал до нажатия клавиши Escape (Esc). Подскажите, что там не так. Спасибо.

Вложения

светофор.xls (45.5 Кб, 97 просмотров)

94731 / 64177 / 26122
Регистрация: 12.04.2006
Сообщений: 116,782
Ответы с готовыми решениями:

Передача данных из Excel 2010 в Word 2010
Помогите пожалуйста. С помощью макроса, нужно передать данные из ячеек excel в определённое место.

Таблицы из Word 2010 в Excel 2010
Есть несколько вордовских файлов, которые помимо текста содержат таблицы. Эти таблицы необходимо.

Запуск Excel 2010 происходит через Excel 2003
Всем привет! У меня есть кнопка, при нажатии на которую открывается определенный файл в новой.

Перенос старых данных в БД MS Access 2010 из MS Excel 2010
Здравствуйте ! Создаю БД да работы сотрудников. Вроде с основными вопросами справился, но как.

15142 / 6415 / 1731
Регистрация: 24.09.2011
Сообщений: 9,999
Полу-ОФФ: несколько «светофоров» с другого форума.
Вложения

post_217300.xls (25.5 Кб, 177 просмотров)

post_217257.xls (49.5 Кб, 104 просмотров)

post_217281.xls (42.0 Кб, 88 просмотров)

post_217216.zip (61.4 Кб, 64 просмотров)

15142 / 6415 / 1731
Регистрация: 24.09.2011
Сообщений: 9,999

Сообщение от timsc1

Но макрос не закрашивает
DoEvents после команды изменения цвета.
Регистрация: 27.11.2011
Сообщений: 697
Не пойму, как вставлять. Не подскажите поподробнее.
15142 / 6415 / 1731
Регистрация: 24.09.2011
Сообщений: 9,999

Лучше назначить цвета овалам вручную, а в коде только управлять прозрачностью. Реализован выход по Esc.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26

Private Declare Sub WaitMessage Lib "user32" () Const DUR! = 1 'продолжительность свечения одного цвета, сек Const TR_IN = 0.8 'прозрачность цвета в неактивном состоянии Sub Svet() Dim t! Application.EnableCancelKey = xlErrorHandler On Error GoTo 1 Do For i = 1 To 3 With ActiveSheet.Shapes(i).Fill .Transparency = 0 t = Timer + DUR While Timer < t DoEvents WaitMessage Wend .Transparency = TR_IN End With Next Loop 1 ActiveSheet.Shapes.Range(Array(1, 2, 3)).Fill.Transparency = TR_IN Application.EnableCancelKey = xlDisabled End Sub

Вложения

светофор.xls (41.5 Кб, 83 просмотров)