Как сделать светофор в excel
Как сделать светофор в excel
Часто такое бывает, что сталкиваешься с большим количеством данных, которые нужно в сжатые сроки сравнить с плановыми. Допустим, у каждого вашего менеджера выручка должна составлять около 100 000 рублей. И Вы начинаете каждый показатель сравнивать в ручную, весь этот трудоемкий процесс отнимает кучу времени и сил. С новой версией Excel 2010 можно проблему решить при помощи нового встроенного компонента. Итак, давайте избавимся от этой трудоемкой работы! Вначале выделяем нашу область данных. Далее переходим во вкладку «Вставка», затем ищем «Условное форматирование» и «Набор значков»; в выпадающем меню определяемся с шаблоном, с которым в дальнейшем будем работать. Эксперты рекомендуют выбирать шаблон с названием «Светофор»: дело в том, что с ним очень удобно работать. После того, как шаблон выбран, на экране монитора появится меню с названием «Создание правил форматирования», тут нужно произвести следующие манипуляции: необходимо внести данные напротив значков. Если план сотрудника превышает указанный или недостает до него, то предлагается следующая оценка работника: отличная, удовлетворительная, неудовлетворительная. Данные вводятся в области «Значение» а опции параметров «Тип» нужно отредактировать и поменять с процентов на числа.
В данном уроке мы назначали следующие данные: 100-90 тысяч. Третьим шагом мы выставляем автоматически, например меньше «удовлетворительного» и жмем ОК.
В ячейках, где рассоложены данные, должны сразу появиться цветные кружочки (зеленый, красный, желтый). С помощью этой схемы мы сможем с Вами определить оценку о проделанной работе менеджера за определенное время. Также эта полезная функция дает нам выяснить, кто из работников достигает положительных результатов.
Дополнительно к этой функции разработчики добавили условное форматирование данных. Теперь можно создавать инфографику в виде цветовых шкал или гистограмм. Давайте попробуем разобраться с этой новой фишкой, которая очень будет нам полезна в работе. Заходим в выпадающие меню «список шаблонов», и, если мы наведем курсор мышки на любой понравившейся нам шаблон, то произойдет превью нашей работы, и мы получаем следующий результат. Мы видим, что все ячейки окрашены столбцами различной длины. И наш результат отображается в виде графика со значениями, которые находятся в ячейках. Если ваши данные Вы будете вносить со знаком минус, то Ваш график будет смещаться в совершенно противоположный край документа, что будет указывать на отрицательные значения ваших данных.
Как в excel сделать светофор
Как в excel сделать светофор
Часто возникает необходимость в отчётах акцентировать статус какого-либо процесса при помощи цвета. При этом довольно популярна метафора светофора. Если всё хорошо — зелёный статус, что-то настораживает — жёлтый, всё плохо — красный. Таким образом, тот, кто смотрит ваши отчёты, имеет возможность по цвету сразу обращаться к проблемным цифрам. Отчёты становятся более наглядными, интуитивно понятными и экономят время просматривающих.
Ограничения условного форматирования
В Excel есть стандартный инструмент, который решает эту задачу, — условное форматирование при помощи набора значков. Инструмент отличный, но в некоторых ситуациях вам его может быть недостаточно. Я, например, вижу следующую проблему: данные значки довольно мелкие и хорошо выглядят только в своём оригинальном размере. Если вам потребуется значок побольше и/или поинтересней, то придётся его делать самому при помощи фигур.
Фигуры
Мы хотим визуализировать соотношение фактических и плановых расходов по проектам при помощи наших светофоров. Вот так:
Пример
Скачать
Последовательность шагов
Формат данных диапазона E3:E5 устанавливаем в » ;;; «, что предотвращает появление значений ячеек на экране, чтобы цифры не выглядывали из-за светофоров, которые мы поместим над этими ячейками.
В редакторе VBA в лист Лист1 (Статусы) поместите код:
Условное форматирование Excel
Представьте себе монитор, где выведены рабочие узлы атомной электростанции, который отображает стабильность протекания всех процессов. Но вдруг один узел выходит из строя и сигнализирует диспетчеру о сбое, загораясь ярким красным светом. Согласитесь, очень удобно? Похожим целям служит функция условного форматирования в Excel – обеспечение наилучшей наглядности информации.
Располагается эта полезная возможность на вкладке «Главная» в области «Стили» под одноименной пиктограммой:
Создать правило
Для создания правила условного форматирования в Excel кликните по соответствующей кнопке на ленте, раскрыв следующее меню:
Выбрав пункт «Создать правило…», приложение отобразит окно:
В нем Вы можете выбрать тип правила и настроить его описание (подробнее читайте далее в статье).
Виды условного форматирования
Форматировать все ячейки на основании их значений
Этот вид правила применяется для сравнения числовых значений в диапазоне. В описании можно выбрать стиль формата и соответствующие этому стилю параметры.
Гистограмма
Данная возможность позволяет отобразить в каждой ячейке горизонтальный столбец, похожий на частичную заливку. Если Вы хоть раз использовали гистограмму при построении диаграмм, то Вам будет понятно, о чем идет речь.
Ширина ячейки принимается за 100%, что соответствует максимальному значению диапазона правила. Т.е. ячейка, содержащая максимальное значение будет залита полностью, а ячейка со значением в 2 раза меньшим максимальному – наполовину. В случае отрицательного значения, столбец будет окрашен другим цветом и иметь другую направленность (это можно изменить).
Цветовые шкалы
Как и гистограммы, шкалы в условном форматировании заливают цветом ячейку с числовым значением, но отличие заключается в том, что последние заливают ее полностью. Чем выше значение, тем более насыщенная заливка. Также можно использовать несколько цветов, где, например, меньшие числа залиты зеленым, средние желтым, а большие красным.
В качестве примера, рассмотрим настройку трехцветной шкалы, хотя она мало чем отличается от настройки двухцветной.
Здесь Вы можете установить, что считать минимальным значением, что средним, а что максимальным. Также возможно задать предпочтительный цвет и тип показателя.
Разберем установки, представленные на изображении:
Наборы значков (флажков)
Этот вид условного форматирования, в отличие от цвета заливки, использует различные значки в виде фигур, направлений, индикаторов и оценок.
Как и в случаях, описанных выше, за 100% принимается максимальное число, а остальные составляют от него какую-то долю. Весь диапазон разделяется на определенное количество частей, которое равно количеству значков в выбранном наборе. Каждой такой части соответствует свой флажок. Если диапазон нужно разделить не по долям, а по конкретным значениям, то поменяйте тип значения для значка.
Форматировать только ячейки, которые содержат
Этот вид условного форматирования отличается от первого тем, что он создает правило, которое должно соблюдаться, чтобы формат был применен к ячейке.
Рассмотрим правила, которые имеются в этом пункте:
Форматировать только первые и последние значения
Из названия понятно, что правило срабатывает для тех ячеек, которые идут первыми (наибольшими) или последними (наименьшими) в указанном диапазоне. Количество таких ячеек указывается в виде числа или процента.
Формула в условном форматировании
Когда имеющихся правил недостаточно, можно создать свое, задав ему практически любую логику, на основе формул, результатом выполнения которой должно быть логическое значение. Эти тип называется «Использовать формулу для определения форматируемых ячеек».
Для примера рассмотрим список заказа товаров, который необходимо сравнить с остатком на складе. Всего участвуют 2 таблицы: сам заказ и таблица остатков.
На изображении показан вариант, где уже применено условное форматирование ячеек. Рассмотрим, как его создать.
Используем 2 условия со следующими формулами:
Теперь необходимо выделить требуемый диапазон и создать нужные нам правила.
Остальные правила
Ничего не было сказано о еще двух видах правил, а именно:
По ним остается добавить только то, что в первом можно использовать стандартные отклонения. В остальном, они говорят сами за себя.
Управление правилами
Помимо умения создавать правила, условным форматированием также нужно корректно управлять. Особенно это важно, когда для одного диапазона применяется несколько условий. Но обо всем по порядку.
Диспетчер правил условного форматирования отображает список, состоящий из условия, формата и диапазона, к которому применено правило.
В самом верху окна можно выбрать, какие правила следует выводить в списке: из текущего диапазона, с этого листа, из любого другого листа открытой книги.
Первые три кнопки диспетчера должны быть понятны без дополнительных пояснений, а вот на последних двух (стрелки вверх и вниз) остановимся подробнее.
На изображение приведено 2 правила: значение равно трем и значение больше двух. Представьте, что они применены к ячейке со числом 3. Какое из них сработает? В этом случае оба, так как между ними нет конфликта в форматировании, одно отвечает за заливку, а второе за границу. Но если бы они оба отвечали за один и тот же стиль, то выполнилось правило, которое стоит выше, потому что имеет больший приоритет.
Так вот, стрелками окна можно менять положение отдельно выделенного правила и, соответственно, его значимость.
Рассмотрим еще один случай, когда требуется выполнить только одно условие. В конце каждого правила имеется флажок «Остановить, если истина». Выставив его, Вы отменяете выполнение всех последующих правил для текущего диапазона, при условии, что это оно выполняется. Исходя из рассматриваемого примера, если ячейка содержит значение 3, то проверка на условие «больше двух» произведена не будет.
Office 2010: Как визуализировать данные в Excel 2010
С выходом новой версии Microsoft Office появились и новые возможности. Разработчики доработали некоторые компоненты, сделали еще более удобным работу с программами. Нельзя обойти вниманием и Excel 2010 и новые возможности инфографики в нем. Поэтому в данной статье мы на примере расскажу вам, как работать с новыми компонентами Excel 2010.
Делаем сводную таблицу в Excel
В нашем распоряжении есть достаточно большая таблица. В ней огромное количество столбцов и строк. По этим данным нужно построить что-то в виде отчета, чтобы просмотреть результаты по какой-либо деятельности за определенный период. На вкладке «Вставка» нажимаем кнопку «Сводная таблица». Перед нами открывается диалоговое окно, в котором Excel в качестве диапазона данных выбрал всю таблицу. Нажимаем кнопку «Ок».
Теперь на новом листе появился макет сводной таблицы. В правой части окна перечислены все параметры, которые фигурировали в начальной таблице. Нам необходимо с помощью мыши перетащить их в поле «Название строк». В нашем случае это будут «Даты», «Менеджеры». Такие показатели как: «Объем продаж», «Выручка» и «Прибыль» мы перенесем в поле «Значения». Когда осуществляется перенос параметров в поле, таблица автоматически формируется и изменяется «налету». Расположение элементов в «Название строк» играет большую роль. Если «Даты» будут расположены выше «Менеджеры», то данные будут разбиты на отдельные блоки по датам. Если же «Менеджеры» будут расположены в списке первыми, то сортировка будет проходить по именам сотрудников.
Когда таблица построена, перейдем к ее оформлению. Для начала изменим цветовую схему, применив к ней шаблон. Переходим на вкладку «Главная» и нажимаем на кнопку «Форматировать как таблицу». На экране появится список различных шаблонов форматирования, выбираем понравившийся нам и нажимаем на него. Excel автоматически определит границы таблицы, если они заданы не верно, выделяем таблицу вручную и нажимаем кнопку «Ок». Таблица поменяла цветовую гамму и появилась возможность сортировки параметров.
Условное форматирование таблицы в Excel 2010
В ячейках всех значений появились кружки трех различных цветов. На основе, представленной в таком виде информации намного проще оценить работу менеджеров за определенный период времени. Мы можем сравнить качество работы сотрудников, определить кто из работников добивается наиболее выдающихся результатов, а кто наоборот требует к себе пристального внимания.
Компонент «Цветовые шкалы» заливает ячейки тем цветом, который соответствуют значению, введенному в нее. К примеру, наименьшие значения будет залиты в красный цвет, средние в желтый, а наибольшие в зеленый. Цветовая схема может быть подобрана вами индивидуально, но суть примерно остается тоже, что при использовании «Набор значков».
Срезы и не только
Но это еще не все способности визуализации данных, включенные в пакет Excel 2010. Рассмотрим еще такую удобную функцию как «Срезы». Выбранные работники отработали в компании весьма внушительный срок и сложно при формировании сводной таблицы выделить ту или иную дату. Есть два способа обращения к определённой дате. Когда мы строим сводную таблицу в правой части у нас расположены элементы, которые мы можем разместить в различные поля. Обращаемся к элементу «Даты» и вызываем выпадающее меню, путем нажатия на маркер со стрелочкой. Находим пункт «Фильтр по дате». Открывается огромный список с различными вариантами форматирования, но нам нужна помесячная сортировка. Открываем «Все даты за период» и выбираем «Октябрь». Сводная таблиц значительно сократилась, в ней остались значения только за октябрь. Это первый способ выборки данных.
Перетащим ее в любое удобно для нас место и отрегулируем ее размер таким образом, чтобы можно было видеть все представленные в ней значения. Так же можно изменить цвет среза, все шаблоны отображаются на верхней панели. Теперь мы можем одним кликом выбрать определенную дату, и увидеть каких результатов за эти дни добились сотрудники. Данная функция на порядок удобнее, чем «Фильтр по дате», так как она более гибкая. В ней вы можете выбрать сразу несколько значений, по которым будет идти выборка.
Инфокривые
Следующий способ визуального анализа данных – это инфокривые. Делаем активной свободную ячейку напротив строк с данными. Во вкладке «Вставка» находим раздел «Инфокривые» (в моей версии Excel 2010 они назывались почему-то «Сперклайны»). Выделяем диапазон данных – это будет наша строка, и нажимаем кнопку «Ок». Вы можете увидеть, как в выбранной нами ячейке построился мини график, это и есть инфокривая.
Растянем эту ячейку на все остальные строки, потянув за край с точкой или сделав двойной клик на нем. При желании вы можете изменить стиль инфокривой, его выбор осуществляется на верхней панели в режиме конструктора инфокривой. Полученный график позволяет увидеть тенденцию, тренд. При огромном количестве данных инфокривая дает общий визуальный анализ всего множества. По ней с легкостью можно определить пики и падения, начало роста или его замедление.
В данной статье мы не только научились быстро оформлять таблицу, но и проводить визуальный анализ данных. Также мы ознакомились с такими понятием как сводная таблица, научились производить фильтрацию значений и условное форматирование цифровых значений, составлять срезы. Кроме этого, мы наглядно разобрались с новой функцией под названием «Инфокривые». Нельзя не отметить, что усовершенствования в Excel 2010 видны на лицо, и практически все новые функции направлены на облегчение труда специалиста и наглядное представление данных. Если вас заинтересовала новая функциональность табличного редактора MS Excel 2010, то вы можете приобрести Microsoft Office 2010 у партнеров компании 1CSoft.
Все права защищены. По вопросам использования статьи обращайтесь к администраторам сайта
Хотите купить софт? Позвоните партнерам фирмы «1С», чтобы получить квалифицированную консультацию по выбору программ для ПК, а также информацию о наличии и цене лицензионного ПО.
Условное форматирование Excel
Представьте себе монитор, где выведены рабочие узлы атомной электростанции, который отображает стабильность протекания всех процессов. Но вдруг один узел выходит из строя и сигнализирует диспетчеру о сбое, загораясь ярким красным светом. Согласитесь, очень удобно? Похожим целям служит функция условного форматирования в Excel – обеспечение наилучшей наглядности информации.
Располагается эта полезная возможность на вкладке «Главная» в области «Стили» под одноименной пиктограммой:
Создать правило
Для создания правила условного форматирования в Excel кликните по соответствующей кнопке на ленте, раскрыв следующее меню:
Выбрав пункт «Создать правило…», приложение отобразит окно:
В нем Вы можете выбрать тип правила и настроить его описание (подробнее читайте далее в статье).
Виды условного форматирования
Форматировать все ячейки на основании их значений
Этот вид правила применяется для сравнения числовых значений в диапазоне. В описании можно выбрать стиль формата и соответствующие этому стилю параметры.
Гистограмма
Данная возможность позволяет отобразить в каждой ячейке горизонтальный столбец, похожий на частичную заливку. Если Вы хоть раз использовали гистограмму при построении диаграмм, то Вам будет понятно, о чем идет речь.
Ширина ячейки принимается за 100%, что соответствует максимальному значению диапазона правила. Т.е. ячейка, содержащая максимальное значение будет залита полностью, а ячейка со значением в 2 раза меньшим максимальному – наполовину. В случае отрицательного значения, столбец будет окрашен другим цветом и иметь другую направленность (это можно изменить).
Цветовые шкалы
Как и гистограммы, шкалы в условном форматировании заливают цветом ячейку с числовым значением, но отличие заключается в том, что последние заливают ее полностью. Чем выше значение, тем более насыщенная заливка. Также можно использовать несколько цветов, где, например, меньшие числа залиты зеленым, средние желтым, а большие красным.
В качестве примера, рассмотрим настройку трехцветной шкалы, хотя она мало чем отличается от настройки двухцветной.
Здесь Вы можете установить, что считать минимальным значением, что средним, а что максимальным. Также возможно задать предпочтительный цвет и тип показателя.
Разберем установки, представленные на изображении:
Наборы значков (флажков)
Этот вид условного форматирования, в отличие от цвета заливки, использует различные значки в виде фигур, направлений, индикаторов и оценок.
Как и в случаях, описанных выше, за 100% принимается максимальное число, а остальные составляют от него какую-то долю. Весь диапазон разделяется на определенное количество частей, которое равно количеству значков в выбранном наборе. Каждой такой части соответствует свой флажок. Если диапазон нужно разделить не по долям, а по конкретным значениям, то поменяйте тип значения для значка.
Форматировать только ячейки, которые содержат
Этот вид условного форматирования отличается от первого тем, что он создает правило, которое должно соблюдаться, чтобы формат был применен к ячейке.
Рассмотрим правила, которые имеются в этом пункте:
Форматировать только первые и последние значения
Из названия понятно, что правило срабатывает для тех ячеек, которые идут первыми (наибольшими) или последними (наименьшими) в указанном диапазоне. Количество таких ячеек указывается в виде числа или процента.
Формула в условном форматировании
Когда имеющихся правил недостаточно, можно создать свое, задав ему практически любую логику, на основе формул, результатом выполнения которой должно быть логическое значение. Эти тип называется «Использовать формулу для определения форматируемых ячеек».
Для примера рассмотрим список заказа товаров, который необходимо сравнить с остатком на складе. Всего участвуют 2 таблицы: сам заказ и таблица остатков.
На изображении показан вариант, где уже применено условное форматирование ячеек. Рассмотрим, как его создать.
Используем 2 условия со следующими формулами:
Теперь необходимо выделить требуемый диапазон и создать нужные нам правила.
Остальные правила
Ничего не было сказано о еще двух видах правил, а именно:
По ним остается добавить только то, что в первом можно использовать стандартные отклонения. В остальном, они говорят сами за себя.
Управление правилами
Помимо умения создавать правила, условным форматированием также нужно корректно управлять. Особенно это важно, когда для одного диапазона применяется несколько условий. Но обо всем по порядку.
Диспетчер правил условного форматирования отображает список, состоящий из условия, формата и диапазона, к которому применено правило.
В самом верху окна можно выбрать, какие правила следует выводить в списке: из текущего диапазона, с этого листа, из любого другого листа открытой книги.
Первые три кнопки диспетчера должны быть понятны без дополнительных пояснений, а вот на последних двух (стрелки вверх и вниз) остановимся подробнее.
На изображение приведено 2 правила: значение равно трем и значение больше двух. Представьте, что они применены к ячейке со числом 3. Какое из них сработает? В этом случае оба, так как между ними нет конфликта в форматировании, одно отвечает за заливку, а второе за границу. Но если бы они оба отвечали за один и тот же стиль, то выполнилось правило, которое стоит выше, потому что имеет больший приоритет.
Так вот, стрелками окна можно менять положение отдельно выделенного правила и, соответственно, его значимость.
Рассмотрим еще один случай, когда требуется выполнить только одно условие. В конце каждого правила имеется флажок «Остановить, если истина». Выставив его, Вы отменяете выполнение всех последующих правил для текущего диапазона, при условии, что это оно выполняется. Исходя из рассматриваемого примера, если ячейка содержит значение 3, то проверка на условие «больше двух» произведена не будет.