Как сделать светофор в экселе
Часто возникает необходимость в отчётах акцентировать статус какого-либо процесса при помощи цвета. При этом довольно популярна метафора светофора. Если всё хорошо — зелёный статус, что-то настораживает — жёлтый, всё плохо — красный. Таким образом, тот, кто смотрит ваши отчёты, имеет возможность по цвету сразу обращаться к проблемным цифрам. Отчёты становятся более наглядными, интуитивно понятными и экономят время просматривающих.
Ограничения условного форматирования
В Excel есть стандартный инструмент, который решает эту задачу, — условное форматирование при помощи набора значков. Инструмент отличный, но в некоторых ситуациях вам его может быть недостаточно. Я, например, вижу следующую проблему: данные значки довольно мелкие и хорошо выглядят только в своём оригинальном размере. Если вам потребуется значок побольше и/или поинтересней, то придётся его делать самому при помощи фигур.
Фигуры
Фигурами в MS Office можно нарисовать всё, что угодно. Серьёзно. Любой сложный рисунок «собирается» из простых элементов. Это вопрос только времени и стараний. В этой статье мы будем управлять вот такими несложными, но достаточно привлекательными светофорами, которые легко делаются из фигур овал (круг — частный случай овала/эллипса) и кольцо . 
Мы хотим визуализировать соотношение фактических и плановых расходов по проектам при помощи наших светофоров. Вот так:
Пример
Скачать
Последовательность шагов
Для начала подготовим данные, на основе которых будем вычислять статусы. На нашем примере это столбцы: Проект , Бюджет , Факт .
Подготовим вспомогательную таблицу, на основе которой будем присваивать значения статусов. В нашем случае эта таблица располагается на листе Настройки , оформлена в виде умной таблицы с названием Шкала . Статус G означает Green (зеленый), Y — Yellow (жёлтый), R — Red (красный).
В ячейку 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 .
Формат данных диапазона E3:E5 устанавливаем в » ;;; «, что предотвращает появление значений ячеек на экране, чтобы цифры не выглядывали из-за светофоров, которые мы поместим над этими ячейками.
Создаём именованный диапазон rngTrafLight для ячеек E3:E5 .
Создаём из фигур наши светофоры. Круги, цвет которых мы будем менять, называем именами figTL1 для E3 , figTL2 для E4 и figTL3 для E5 . Располагаем фигуры, там где они должны находиться.
В редакторе Visual Basic for Application ( Alt + F11 ) вставляем module с любым именем (у меня TL ). Для этого щёлкните правой кнопкой по папке Modules и выберите Insert -> Module . Вставьте в модуль этот код:
Как сделать светофор в 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, то проверка на условие «больше двух» произведена не будет.
Условное форматирование в Excel
Условное форматирование ячеек листа MS Excel или OOo Calc позволяет автору электронной таблицы существенно улучшить визуальное представление информации. Пользователи кроме повышенного эстетического восприятия получают инструмент контроля.
. изменений информации в ячейках, происходящих в результате пересчета.
Термин «условное» не означает, что форматирование «как бы есть, и как бы его нет». «Условное» – это форматирование по условиям, которые задал автор таблицы для определенных ячеек рабочего листа. Этим инструментом почему-то не очень часто пользуются, хотя он очень и эффективен, и эффектен! (Почти как «масло — масляное»!)
При обычном форматировании вид, размер, цвет шрифта, обрамления и поля ячейки задаются один раз и навсегда или до тех пор, пока автор не решит их изменить. При назначении ячейкам условного форматирования происходит вот что: внешний вид ячеек изменяется автоматически, но только при наступлении определенных автором таблицы условий. Измененный внешний вид сохраняется исключительно в течение времени, пока выполняются назначенные условия! Как только условия перестают выполняться, внешний вид ячеек становится таким, каким он был изначально. Изменение вида ячеек в зависимости от результатов выполнения заданных условий – это и есть условное форматирование!
Предлагаю перейти к рассмотрению примера, который более наглядно, нежели пространные определения, поможет раскрыть нашу тему.
Пример условного форматирования.
В качестве примера будем использовать файл с расчетной программой из статьи «Расчет усилия листогиба».
Работать с файлом-примером будем в программе MS Excel 2003. Аналогичного результата можно достичь, работая в программе OOo Calc из пакета Open Office. Условное форматирование в MS Excel 2007 имеет гораздо больше интересных и разнообразных возможностей. Мы их немного коснемся в конце статьи.
Наша основная задача – разобраться с понятием «условное форматирование» и усвоить, что дает пользователю применение этого инструмента.
В файле примера выполняется расчет усилия развиваемого листогибочным прессом при свободной гибке деталей из листового металлопроката в «V»-образной матрице. Расчет ведется по двум различным методикам, и результаты сравниваются в конце. В качестве результатов расчета представлена таблица, показывающая зависимость усилия гибки от угла.
Предположим, у нас имеется в наличии листогиб, развивающий максимальное усилие 100 тонн. С помощью условного форматирования ячеек мы сможем при расчетах акцентировать внимание пользователя программы на возникновении недопустимых перегрузок листогибочного пресса.
Применять условное форматирование будем только к результатам, полученным по формуле №1 для визуального сравнения с результатами формулы №2, которые форматировать не будем.
Формулировка условий:
1. Максимальное значение усилия гибки должно быть выделено жирным шрифтом белого цвета на оранжевом фоне.
2. Если усилие гибки превысит 80 тонн, ячейка должна стать залитой розовым цветом.
3. Если усилие гибки превысит 100 тонн, ячейка должна стать залитой красным цветом.
Назначение условий форматирования:
1. Становимся курсором мыши на ячейку G12 (активируем ячейку).
2. В строке меню нажимаем «Формат» > «Условное форматирование…».
3. В выпавшем окне «Условное форматирование» назначаем условия, которые мы сформулировали чуть выше. На скриншоте ниже показан результат, который необходимо достичь! Я уверен, что затруднений ни у кого не должно возникнуть. Все интуитивно достаточно понятно!
Функция «НАИБОЛЬШИЙ($G$12:$P$12;1)» находит в указанном диапазоне G12:P12 максимальное значение. (Если в конце выражения в скобках поставить 2 вместо 1, функция найдет второе по величине значение в заданном массиве.)
4. Закрываем окно «Условное форматирование» нажатием на кнопку «ОК».
5. Для распространения форматирования на другие ячейки диапазона, копируем содержимое вместе с форматированием ячейки G12 в ячейки H12…P12. (Условное форматирование можно назначать так же, как и обычное, выделив необходимый диапазон ячеек или при помощи специальной вставки, выбрав для копирования только форматы.)
Результаты работы условного форматирования:
Сразу после окончания назначения условий мы можем видеть, что ячейка J12 окрасилась в оранжевый цвет, а шрифт стал жирным и белым. Именно в этой ячейке находится максимальное значение усилия листогиба.
Изменим длину сгибаемого листа в ячейке D3 с 1000 мм на 1700 мм. Заливка ячеек I12 и K12 стала розовой, усилие пресса превзошло 80 тонн! Программа цветом ячеек предупреждает: «Внимание. Осторожно. »
Увеличим еще длину сгибаемого листа в ячейке D3 с 1700 мм до 2140 мм. Заливка ячеек I12 и K12 автоматически тут же превратилась в красную, усилие пресса превысило 100 тонн! Программа, как бы, кричит пользователю: «Внимание. Недопустимая операция. »
Итоги.
В Excel 2003 возможности условного форматирования многими считаются весьма скудными по сегодняшним меркам, даже размер шрифта нельзя поменять. К ячейке можно применить всего три условия, причем приоритет первого будет выше второго и третьего. Однако основную идею условного форматирования этот простой набор возможностей успешно реализует. Абсолютно аналогичные возможности предоставляет программа OOo Calc при почти полной идентичности интерфейса.
В Excel 2007 все выглядит красивее, изящнее, разнообразнее, но суть остается той же! Кроме возможности создания своих правил, Excel 2007 предлагает пользователю целый ряд встроенных правил форматирования. В ячейках вместо заливки можно поместить маленькие гистограммы, оформленные цветовыми градиентами или применить цветовые шкалы с плавным переходом от одного цвета к другому, где конечные цвета – это соответственно максимальное и минимальное значения форматируемого диапазона. В ячейки с числовыми значениями могут быть добавлены различные значки – стрелки, «огни светофора», разноцветные флажки, столбчатые маленькие диаграммы и другие дополнительные визуальные эффекты. Правил-условий форматирования может быть не три, а сколь угодно много. Приоритет «верхних» правил над «нижними» сохранен так, как и в Excel 2003.
Выглядеть условное форматирование в Excel 2007 может, например, так, как на рисунке ниже этого текста, где показана таблица выпуска и реализации некой продукции в разные годы.
Иногда, разбираясь в возможностях различных программ, начинаешь понимать, как сделать то или иное действие, но не понимаешь, для чего этот результат может быть с пользой применен. В этой статье рассмотрен лишь один из десятков или даже сотен вариантов использования условного форматирования при работе с файлами Excel. Жизненные ситуации и опыт помогут вам постепенно лучше освоить и использовать эту одну из массы возможностей Excel! Уверен, что инструмент «Условное форматирование» станет вашим повседневным удобным и мощным помощником при анализе табличных данных!
Ссылка на скачивание файла с примером: uslovnoe-formatirovanie (xls 56,5KB).
Как сделать светофор в экселе
12.03.2011 23:07 | Автор: Автор | | |
Часто такое бывает, что сталкиваешься с большим количеством данных, которые нужно в сжатые сроки сравнить с плановыми. Допустим, у каждого вашего менеджера выручка должна составлять около 100 000 рублей. И Вы начинаете каждый показатель сравнивать в ручную, весь этот трудоемкий процесс отнимает кучу времени и сил. С новой версией Excel 2010 можно проблему решить при помощи нового встроенного компонента. Итак, давайте избавимся от этой трудоемкой работы! Вначале выделяем нашу область данных. Далее переходим во вкладку «Вставка», затем ищем «Условное форматирование» и «Набор значков»; в выпадающем меню определяемся с шаблоном, с которым в дальнейшем будем работать. Эксперты рекомендуют выбирать шаблон с названием «Светофор»: дело в том, что с ним очень удобно работать. После того, как шаблон выбран, на экране монитора появится меню с названием «Создание правил форматирования», тут нужно произвести следующие манипуляции: необходимо внести данные напротив значков. Если план сотрудника превышает указанный или недостает до него, то предлагается следующая оценка работника: отличная, удовлетворительная, неудовлетворительная. Данные вводятся в области «Значение» а опции параметров «Тип» нужно отредактировать и поменять с процентов на числа.
В данном уроке мы назначали следующие данные: 100-90 тысяч. Третьим шагом мы выставляем автоматически, например меньше «удовлетворительного» и жмем ОК.
В ячейках, где рассоложены данные, должны сразу появиться цветные кружочки (зеленый, красный, желтый). С помощью этой схемы мы сможем с Вами определить оценку о проделанной работе менеджера за определенное время. Также эта полезная функция дает нам выяснить, кто из работников достигает положительных результатов.
Дополнительно к этой функции разработчики добавили условное форматирование данных. Теперь можно создавать инфографику в виде цветовых шкал или гистограмм. Давайте попробуем разобраться с этой новой фишкой, которая очень будет нам полезна в работе. Заходим в выпадающие меню «список шаблонов», и, если мы наведем курсор мышки на любой понравившейся нам шаблон, то произойдет превью нашей работы, и мы получаем следующий результат. Мы видим, что все ячейки окрашены столбцами различной длины. И наш результат отображается в виде графика со значениями, которые находятся в ячейках. Если ваши данные Вы будете вносить со знаком минус, то Ваш график будет смещаться в совершенно противоположный край документа, что будет указывать на отрицательные значения ваших данных.
