Как сделать светофор в Excel
Обновлено: 21.11.2024
В Excel 2010 появилось несколько интересных новых способов форматирования ячеек и использования цвета и значков для выделения значений. В качестве альтернативы простому затенению целых ячеек можно использовать различные цветные фигуры, стрелки и флажки.
В этом примере индикаторы светофора будут добавлены в ячейки S19:S22 в соответствии с их значением. Каждая ячейка содержит формулу =N19/M19 (с поправкой на каждую строку). Выделите диапазон ячеек и выберите [Главная] Стили | Условное форматирование | Наборы иконок | и выберите набор из трех цветных кругов. Это не будет иметь явного эффекта, и изначально круги не появятся, потому что правила не установлены. Для этого выберите [Главная] Стили | Условное форматирование | Управление правилами |.
Excel предлагает только «Больше чем» (> или >=) для определения полос, поэтому необходимо убедиться, что цветные фигуры расположены в правильном порядке. В этой таблице высокие значения плохие (красные), поэтому нажмите кнопку [Обратный порядок значков], чтобы переключить их.
Должны быть указаны значения, определяющие диапазоны, в которых будет отображаться каждый из цветов. В этом примере зеленый — от 0 до 0,95, желтый – от 0,95 до 0,999, а красный – больше 0,999.
Excel может быть немного капризным при внесении изменений в формулу или форматирование определенных ячеек в пределах диапазона. Может потребоваться очистить форматирование в определенной ячейке, а затем повторно ввести правила условного форматирования только для этой ячейки.
Невозможно установить эти правила для значков на основе формулы, т. е. значок не может быть определен значениями в других ячейках. Однако в диалоговом окне правила условного форматирования есть флажок «Показывать только значок». Это скроет значение в ячейке, содержащей значок.
Другие инструменты условного форматирования
Есть два других варианта этого инструмента в более поздних версиях Excel.
Столбцы данных (столбец C) – в каждой ячейке отображается столбец, показывающий относительные размеры значений.
Цветовые шкалы (столбец D) — закрашивает каждую ячейку в диапазоне в соответствии с их относительным положением между самым высоким и самым низким значением. Можно настроить параметры Excel по умолчанию с помощью параметра «Управление правилами».
Нажмите здесь для получения подробной информации о расчетах, зависящих от цвета ячеек.
Нажмите здесь, чтобы увидеть пример приложения с условным форматированием — планировщик отпусков для офиса.
Вчера я научился новому крутому приему в PowerPoint. Я узнал об объединении фигур.
Как и Excel, PowerPoint позволяет рисовать различные фигуры на слайдах (или листах в Excel). Мы можем рисовать линии, стрелки, прямоугольники, круги и многие другие фигуры.
Но в PowerPoint есть дополнительная функция с фигурами, которой нет в Excel. В PowerPoint у нас есть возможность объединять фигуры для создания новых фигур.
Мы могли бы объединить длинный тонкий прямоугольник со звездой, чтобы создать волшебную палочку. Возможности безграничны!
Это натолкнуло меня на идею причудливой визуальной панели. Итак, в этом посте мы узнаем, как создать динамический светофор, который меняет цвета в зависимости от значения некоторого ключевого показателя эффективности (KPI) на нашей информационной панели.
В отличие от волшебной палочки, наш светофор действительно будет работать! Когда значение нашего KPI изменится, наш светофор тоже изменится, в зависимости от того, в каком диапазоне значений мы его установим.
Видеоруководство
Создание фигуры светофора
Если вы добавите фигуру в PowerPoint или Excel, а затем выберете ее, на ленте появится новая вкладка Формат средств рисования. Но в PowerPoint есть дополнительный параметр «Объединить фигуры», которого нет в Excel.
В Excel есть много скрытых команд, которые не отображаются на вкладках ленты, и их можно использовать, добавив на панель быстрого доступа. Но форм слияния там даже нет.
Поэтому, чтобы создать фигуру светофора, нам нужно зайти в PowerPoint, чтобы использовать параметры объединения фигур.
Объединение фигур в PowerPoint
Сначала нам нужно добавить пару фигур. Сначала добавьте фигуру прямоугольника, таким образом, она будет наложена как нижняя фигура.
Затем добавьте форму круга. На самом деле нет формы круга, только овал, но мы можем использовать его для создания круга.Удерживая нажатой клавишу Shift, рисуйте овал, чтобы зафиксировать пропорции и создать идеальный круг.
Мы можем отрегулировать размер по мере необходимости, чтобы круг хорошо помещался внутри прямоугольника. Затем мы можем скопировать и вставить круг, всего три круга.
В PowerPoint есть встроенный инструмент выравнивания, который поможет выровнять все фигуры, что является еще одним преимуществом выполнения этой части в PowerPoint по сравнению с Excel.
Мы также можем использовать инструменты выравнивания на вкладке Формат инструментов рисования ➜ Выровнять ➜ Выровнять по центру и/или Распределить по вертикали.
После того, как мы все правильно выровняем и отцентрируем, мы готовы объединить фигуры. Мы можем перейти на вкладку Формат инструментов рисования ➜ Объединить фигуры ➜ Объединить. Это, по сути, вырежет круги из прямоугольника и оставит нам одну форму (прямоугольник с тремя отверстиями в нем).
Я не знаю ни одного синего светофора, поэтому мы также изменим цвет с синего по умолчанию.
- Вкладка «Формат» инструментов рисования ➜ «Заливка формы», затем измените цвет на темно-серый.
- Вкладка «Формат» инструментов рисования ➜ Контур формы, затем измените цвет на черный.
Скопируйте и вставьте эту новую фигуру в Excel, и мы закончили с PowerPoint.
Условные правила форматирования для изменения цвета заливки
В этом примере значение нашего ключевого показателя эффективности, которое управляет светофором, будет на Листе 1, а закулисные действия, которые фактически меняют свет, будут на Листе 2.
Это базовая настройка в Sheet2. В ячейках A2 и A3 у нас есть некоторые числовые значения, а в ячейках B2:B4 есть условное форматирование, основанное на этих значениях и нашем KPI.
- Когда наш KPI меньше значения в A2, наш светофор загорается красным цветом.
- Когда наш KPI находится между значениями в A2 и A3, наш светофор загорается желтым цветом.
- Когда наш ключевой показатель эффективности превышает значение в A3, наш светофор загорается зеленым цветом.
Для этого мы настроим условное форматирование в ячейках B2:B4.
Перед настройкой условного форматирования мы можем применить светло-серое форматирование к ячейкам в B2:B4. Это будет цвет, который отображается, когда светофор выключен.
Ячейка B2 будет нашим красным светом. Выберите эту ячейку и перейдите на вкладку «Главная» ➜ «Условное форматирование» ➜ «Новое правило».
Мы будем использовать формулу Тип правила с приведенной выше формулой, а также зададим для формата красный цвет заливки. Здесь Sheet1!$B$2 — это значение нашего KPI.
В ячейке B3 нам нужно аналогичное условное форматирование с приведенной выше формулой и желтым цветом заливки Format.
В ячейке B4 нам нужно наше последнее условное форматирование с приведенной выше формулой и зеленым цветом заливки Format.
Нам также потребуется изменить ширину столбца и высоту строки ячеек B2:B4, чтобы каждая ячейка хорошо помещалась под отверстиями нашего прямоугольника.
Вставить диапазон как связанное изображение
Мы собираемся использовать диапазон ячеек с условным форматированием в B2:B4 в качестве световых сигналов для нашего визуального элемента светофора.
Для этого мы воспользуемся отличной опцией вставки под названием Linked Picture. Это позволяет нам создать изображение ячеек в B2:B4, но это изображение является динамическим и будет меняться при изменении этих ячеек.
Мы можем скопировать диапазон B2:B4 и перейти к Sheet1, где находится наша фигура светофора.
На вкладке "Главная" нажмите маленькую стрелку под командой "Вставить", затем выберите параметр "Связанное изображение".
Упорядочить и сгруппировать фигуру и изображение
Мы можем изменить размер полученного связанного изображения так, чтобы каждая ячейка совпадала с каждым отверстием в нашей фигуре светофора.
Поместите связанное изображение поверх фигуры светофора так, чтобы каждая ячейка закрывала каждое отверстие, но изображение все равно было меньше, чем весь прямоугольник.
Затем мы можем поместить изображение за фигуру, перейдя на вкладку Формат инструментов рисования ➜ Отправить назад ➜ Отправить на задний план.
Как только нас устроит положение фигуры и изображения, мы можем сгруппировать их вместе. Вкладка Формат средств рисования ➜ Группа ➜ Группа. Это позволит нам перемещать и изменять размер светофора как одного объекта.
Теперь у нас есть симпатичный маленький светофор для наших сводок!
Выводы
С небольшой помощью PowerPoint мы можем комбинировать фигуры для создания более сложных фигур. Хотя в Excel нет этих возможностей, мы можем легко скопировать результаты PowerPoint в Excel.
Мы использовали эти новые формы, создающие сверхспособности, чтобы вырезать несколько отверстий в прямоугольнике.В сочетании со связанным изображением с некоторыми условно отформатированными ячейками мы можем создать классный визуальный эффект светофора.
Какие визуальные эффекты вы могли бы придумать? Дайте мне знать в комментариях ниже!
Об авторе
Джон МакДугалл
Джон — Microsoft MVP, внештатный консультант и тренер, специализирующийся на Excel, Power BI, Power Automate, Power Apps и SharePoint. Другие интересные статьи Джона можно найти в его блоге или на канале YouTube.
Вот один из способов выделить исключительную эффективность в отчетах об управлении Excel. Вам просто нужна диаграмма Excel, ее функция ВЫБОР и связанные изображения.
«Светофоры» — это обычная функция, предлагаемая программным обеспечением, предназначенным для управленческой отчетности и анализа.
Светофоры дают как минимум два преимущества. Во-первых, они предупреждают читателей об исключительных результатах, которые в противном случае читатели могли бы пропустить. Во-вторых, они обеспечивают визуальное замораживание, которое может сделать отчеты более интересными.
Приведенные ниже три примера иллюстрируют простую форму светофора.
На всех трех изображениях показана одна и та же ячейка на листе Excel. Когда продажи растут, Excel отображает зеленое изображение. Когда продажи падают, Excel отображает красное изображение. В противном случае Excel отображает желтое изображение.
Эти изображения переключаются автоматически. Никаких макросов не нужно. А простые формулы определяют, какое изображение используется.
В этой статье объясняется, как настроить светофор в собственных отчетах Excel.
Знакомство с объектом камеры
Excel представил объект Camera не менее 20 лет назад. Но несколько поколений назад Microsoft изменила свое название на Linked Picture. Несмотря на это, я обычно называю его объектом «Камера».
(Аналогично Microsoft изменила название Power Query в Excel 2016 на Data, Get & Transform Data. Но большинство людей по-прежнему называют его Power Query или сокращенно PQ.)
Объект камеры Excel часто является ключом к созданию отчетов о светофоре. Объект Camera возвращает изображение заданного диапазона, и формула может указать этот диапазон. Это простое описание дает подсказку о том, как я создал вышеприведенные светофоры.
Сначала я настроил три разные ячейки с цветными фоновыми объектами, показанными выше. Затем я написал формулы, которые сообщали объекту Camera, изображение какой из этих трех ячеек следует вернуть, на основе значения в назначенной ячейке.
Давайте выполним эти шаги по одному.
Поэкспериментируйте с объектом камеры
Первый шаг — поместить значок объекта "Камера" в одну из строк формул в Excel.
Для этого сначала выберите Инструменты, Настройка в Excel. На вкладке «Команды» выберите категорию «Инструменты». В списке команд в правой части диалогового окна вы найдете значок камеры чуть ниже середины списка. Нажмите и перетащите значок на удобную панель инструментов в рабочей области Excel.
В новом листе введите текст в ячейку C2. Добавьте цвет и другое форматирование, как показано ниже. Затем, выбрав ячейку C2, нажмите Ctrl + C, чтобы скопировать эту ячейку.
Выделите ячейку C4 и в группе Главная, Буфер обмена выберите Вставить, Связанное изображение.
При этом Excel возвращает связанный объект изображения, как показано здесь. Вы можете переместить этот объект в любое место на листе.
Если вы измените форматирование или текст в ячейке C2, вы увидите, что связанное изображение немедленно обновится.
Теперь, выбрав связанное изображение, посмотрите на формулу в строке формул. Это диапазон, изображение которого возвращает связанное изображение. Этот диапазон может состоять из одной или нескольких смежных ячеек, и, как вы увидите, вы можете изменить его вручную.
Вы можете вырезать или скопировать и вставить связанный объект изображения на любой другой рабочий лист. Однако, когда вы это сделаете, вы заметите одну небольшую проблему. Когда вы вставляете ссылку на обычную ячейку на какой-либо другой лист, Excel автоматически изменяет ссылку на форму внешней ссылки. Для иллюстрации предположим, что у вас есть эта формула на Листе 1:
Если бы вы вырезали и вставили эту ячейку на Лист2, Excel вставил бы следующую формулу:
То есть Excel автоматически корректирует ссылку, чтобы она продолжала указывать на ячейку, которую вы изначально указали.
К сожалению, когда вы вырезаете и вставляете связанное изображение между листами или книгами, ссылка на ячейку изменяется не так, как вы ожидаете. Но это не проблема. После того, как вы вставите связанное изображение в новое место, просто выберите формулу объекта в строке формул, а затем выберите диапазон, на который вы хотите ссылаться связанное изображение. Это изменит формулу объекта с чего-то вроде…
…что-то вроде…
Небольшой экскурс в диапазоны Excel
До сих пор мы использовали объект Camera только с простыми ссылками, например =$B$2. Однако для светофора нам нужно использовать формулу, которая возвращает разные ссылки при разных условиях. То есть нам нужно, чтобы у объекта Camera была ссылка примерно такого вида:
Здесь, если ячейка A1 содержит значение 1, мы хотим, чтобы объект Camera возвращал изображение ячейки B2; в противном случае мы хотим, чтобы он возвращал изображение ячейки B3.
К сожалению, если вы попытаетесь ввести подобную формулу для объекта "Камера", вы получите сообщение об ошибке Excel. Но это не проблема, потому что у нас есть простой обходной путь: имена диапазонов.
Большинство имен диапазонов Excel в большинстве электронных таблиц Excel очень простые. Имена определяются с помощью простых ссылок, например…
Однако имена Excel можно задавать с помощью формул. Например, вы можете определить имя Test как:
Попробуйте. Выберите «Вставить», «Имя», «Определить». Введите Test в качестве имени. Затем в поле Ссылается введите приведенную выше формулу. (Excel добавит имя активного листа к каждой ссылке на ячейку, но все равно примет формулу.)
Чтобы проверить, работает ли названная формула должным образом, введите значение 1 в ячейку A1. Затем перейдите к диапазону, определенному Test. Для этого нажмите функциональную клавишу F5, которая запускает диалоговое окно «Перейти». Введите «Тест» (без кавычек) в поле «Ссылка». Затем выберите ОК. Excel должен выбрать ячейку B2. Затем введите любое другое значение в ячейку A1. На этот раз, когда вы перейдете к тесту, Excel выберет ячейку B3.
(Кстати, при нажатии функциональной клавиши F5 Excel не будет отображать имена, такие как Test, которые были определены с помощью формул. Это не проблема. Просто введите имя в поле «Ссылка».)
Мы отклонились от имен диапазонов, потому что объект Camera может использовать имена динамических диапазонов, имена, которые используют формулы для возврата ссылок. Для иллюстрации создайте объект Camera, который ссылается на любой диапазон, скажем, на ячейку C5. Затем, выбрав объект «Камера», измените его формулу в строке формул с =$C$5 на =Test.
Если вы определили Test, как показано выше, объект Camera должен возвращать изображение ячейки B2, когда ячейка A1 содержит значение 1. В противном случае камера должна возвращать изображение ячейки B3.
Теперь, имея в своем наборе инструментов имена объекта "Камера" и динамического диапазона, давайте создадим несколько светофоров.
Настройте формулы светофора
На этом рисунке показано простое отображение светофора.
Ячейка B2 получила название Score. Обычно он содержит формулу, которая просматривает данные, которые мы отображаем, а затем оценивает результаты. Оценка 1 возвращает зеленый цвет вокруг диаграммы; 2 возвращает желтый цвет; а 3 возвращает красный цвет.
Формула оценки может быть любой. Предположим, например, что ячейка с именем Рост содержит процент роста за последний квартал по сравнению с предыдущим кварталом.
Следующая формула возвращает 1, если продажи выросли более чем на 2 %, 3, если продажи упали более чем на 2 %, и 2 в противном случае.
Как условно форматировать красный янтарный зеленый на основе даты в Excel?
Если у вас есть список дат на листе, вы хотели бы выделить дату красным, желтым и зеленым цветами с условным форматированием. Например, если дата прошла, то показать красный; если будущие даты в течение 3 месяцев с настоящего момента окрашены в янтарный цвет; и если будущая дата наступит более чем через 3 месяца, зеленый цвет отображается, как показано на следующем снимке экрана. Как бы вы могли решить эту задачу в Excel как можно быстрее?
Условное форматирование красный янтарный зеленый на основе даты в Excel
Для условного форматирования списка дат красным, желтым и зеленым цветами, как в системе светофора, выполните следующие действия:
<р>1. Выберите столбец даты и нажмите «Главная» > «Условное форматирование» > «Новое правило», см. снимок экрана:<р>2. В диалоговом окне «Новое правило форматирования» нажмите «Использовать формулу, чтобы определить, какие ячейки следует форматировать» в поле со списком «Выбор типа правила», а затем введите следующую формулу: = A2 в текстовое поле «Значения формата», где эта формула является истинной, см. снимок экрана :
<р>3. Затем нажмите кнопку «Формат», чтобы перейти в диалоговое окно «Формат ячеек», на вкладке «Заливка» выберите красный цвет, см. снимок экрана:
<р>4. Затем нажмите кнопки «ОК» > «ОК», чтобы закрыть диалоговые окна, и теперь прошлые даты в столбце выделены красным цветом, см. снимок экрана:
<р>5. Чтобы выделить будущие даты в течение 3 месяцев, перейдите в диалоговое окно «Новое правило форматирования» и введите следующую формулу: =И(A2>=сегодня(),A2 в значения формата, где эта формула является истинным текстовым полем, см. снимок экрана) :
<р>6. Затем нажмите «Формат» и выберите желтый цвет в диалоговом окне «Формат ячеек», нажмите «ОК» > «ОК», и даты в течение 3 месяцев будут выделены, как показано ниже:
<р>7. Чтобы выделить дату более чем через 3 месяца, вам просто нужно применить эту формулу: =A2>ДАТА(СЕГОДНЯ(),3) в диалоговом окне «Новое правило форматирования», см. снимок экрана:
<р>8. И выберите зеленый цвет для условного форматирования даты более 3 месяцев в диалоговом окне «Формат ячеек», и вы получите результат, как показано на следующем снимке экрана:
Читайте также: