Условное форматирование: инструмент Microsoft Excel для визуализации данных - TurboComputer.ru
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд (пока оценок нет)
Загрузка...

Условное форматирование: инструмент Microsoft Excel для визуализации данных

Обучение условному форматированию в Excel с примерами

Условное форматирование – удобный инструмент для анализа данных и наглядного представления результатов. Умение им пользоваться сэкономит массу времени и сил. Достаточно бегло взглянуть на документ – нужная информация получена.

Как сделать условное форматирование в Excel

Инструмент «Условное форматирование» находится на главной странице в разделе «Стили».

При нажатии на стрелочку справа открывается меню для условий форматирования.

Сравним числовые значения в диапазоне Excel с числовой константой. Чаще всего используются правила «больше / меньше / равно / между». Поэтому они вынесены в меню «Правила выделения ячеек».

Введем в диапазон А1:А11 ряд чисел:

Выделим диапазон значений. Открываем меню «Условного форматирования». Выбираем «Правила выделения ячеек». Зададим условие, например, «больше».

Введем в левое поле число 15. В правое – способ выделения значений, соответствующих заданному условию: «больше 15». Сразу виден результат:

Выходим из меню нажатием кнопки ОК.

Условное форматирование по значению другой ячейки

Сравним значения диапазона А1:А11 с числом в ячейке В2. Введем в нее цифру 20.

Выделяем исходный диапазон и открываем окно инструмента «Условное форматирование» (ниже сокращенно упоминается «УФ»). Для данного примера применим условие «меньше» («Правила выделения ячеек» – «Меньше»).

В левое поле вводим ссылку на ячейку В2 (щелкаем мышью по этой ячейке – ее имя появится автоматически). По умолчанию – абсолютную.

Результат форматирования сразу виден на листе Excel.

Значения диапазона А1:А11, которые меньше значения ячейки В2, залиты выбранным фоном.

Зададим условие форматирования: сравнить значения ячеек в разных диапазонах и показать одинаковые. Сравнивать будем столбец А1:А11 со столбцом В1:В11.

Выделим исходный диапазон (А1:А11). Нажмем «УФ» – «Правила выделения ячеек» – «Равно». В левом поле – ссылка на ячейку В1. Ссылка должна быть СМЕШАННАЯ или ОТНОСИТЕЛЬНАЯ! , а не абсолютная.

Каждое значение в столбце А программа сравнила с соответствующим значением в столбце В. Одинаковые значения выделены цветом.

Внимание! При использовании относительных ссылок нужно следить, какая ячейка была активна в момент вызова инструмента «Условного формата». Так как именно к активной ячейке «привязывается» ссылка в условии.

В нашем примере в момент вызова инструмента была активна ячейка А1. Ссылка $B1. Следовательно, Excel сравнивает значение ячейки А1 со значением В1. Если бы мы выделяли столбец не сверху вниз, а снизу вверх, то активной была бы ячейка А11. И программа сравнивала бы В1 с А11.

Чтобы инструмент «Условное форматирование» правильно выполнил задачу, следите за этим моментом.

Проверить правильность заданного условия можно следующим образом:

  1. Выделите первую ячейку диапазона с условным форматированим.
  2. Откройте меню инструмента, нажмите «Управление правилами».

В открывшемся окне видно, какое правило и к какому диапазону применяется.

Условное форматирование – несколько условий

Исходный диапазон – А1:А11. Необходимо выделить красным числа, которые больше 6. Зеленым – больше 10. Желтым – больше 20.

  • 1 способ. Выделяем диапазон А1:А11. Применяем к нему «Условное форматирование». «Правила выделения ячеек» – «Больше». В левое поле вводим число 6. В правом – «красная заливка». ОК. Снова выделяем диапазон А1:А11. Задаем условие форматирования «больше 10», способ – «заливка зеленым». По такому же принципу «заливаем» желтым числа больше 20.
  • 2 способ. В меню инструмента «Условное форматирование выбираем «Создать правило».

Заполняем параметры форматирования по первому условию:

Нажимаем ОК. Аналогично задаем второе и третье условие форматирования.

Обратите внимание: значения некоторых ячеек соответствуют одновременно двум и более условиям. Приоритет обработки зависит от порядка перечисления правил в «Диспетчере»-«Управление правилами».

То есть к числу 24, которое одновременно больше 6, 10 и 20, применяется условие «=$А1>20» (первое в списке).

Условное форматирование даты в Excel

Выделяем диапазон с датами.

Применим к нему «УФ» – «Дата».

В открывшемся окне появляется перечень доступных условий (правил):

Выбираем нужное (например, за последние 7 дней) и жмем ОК.

Красным цветом выделены ячейки с датами последней недели (дата написания статьи – 02.02.2016).

Условное форматирование в Excel с использованием формул

Если стандартных правил недостаточно, пользователь может применить формулу. Практически любую: возможности данного инструмента безграничны. Рассмотрим простой вариант.

Есть столбец с числами. Необходимо выделить цветом ячейки с четными. Используем формулу: =ОСТАТ($А1;2)=0.

Выделяем диапазон с числами – открываем меню «Условного форматирования». Выбираем «Создать правило». Нажимаем «Использовать формулу для определения форматируемых ячеек». Заполняем следующим образом:

Для закрытия окна и отображения результата – ОК.

Условное форматирование строки по значению ячейки

Задача: выделить цветом строку, содержащую ячейку с определенным значением.

Таблица для примера:

Необходимо выделить красным цветом информацию по проекту, который находится еще в работе («Р»). Зеленым – завершен («З»).

Выделяем диапазон со значениями таблицы. Нажимаем «УФ» – «Создать правило». Тип правила – формула. Применим функцию ЕСЛИ.

Порядок заполнения условий для форматирования «завершенных проектов»:

Обратите внимание: ссылки на строку – абсолютные, на ячейку – смешанная («закрепили» только столбец).

Аналогично задаем правила форматирования для незавершенных проектов.

В «Диспетчере» условия выглядят так:

Когда заданы параметры форматирования для всего диапазона, условие будет выполняться одновременно с заполнением ячеек. К примеру, «завершим» проект Димитровой за 28.01 – поставим вместо «Р» «З».

«Раскраска» автоматически поменялась. Стандартными средствами Excel к таким результатам пришлось бы долго идти.

Знакомство с возможностями Excel 2010 по визуализации данных

С выходом новой версии Microsoft Office появились и новые возможности. Разработчики доработали некоторые компоненты, сделали еще более удобным работу с программами. Нельзя обойти вниманием и Excel 2010 и новые возможности инфографики в нем. Поэтому в данной статье мы на примере расскажем, как работать с новыми компонентами Excel 2010.

Делаем сводную таблицу в Excel

В нашем распоряжении есть достаточно большая таблица. В ней огромное количество столбцов и строк. По этим данным нужно построить что-то вроде отчета, чтобы просмотреть результаты по какой-либо деятельности за определенный период. На вкладке «Вставка» нажимаем кнопку «Сводная таблица». Перед нами открывается диалоговое окно, в котором Excel в качестве диапазона данных выбрал всю таблицу. Нажимаем кнопку «ОК».

Теперь на новом листе появился макет сводной таблицы. В правой части окна перечислены все параметры, которые фигурировали в начальной таблице. Нам необходимо с помощью мыши перетащить их в поле «Название строк». В нашем случае это будут «Даты», «Менеджеры». Такие показатели как: «Объем продаж», «Выручка» и «Прибыль» мы перенесем в поле «Значения». Когда осуществляется перенос параметров в поле, таблица автоматически формируется и изменяется «на лету». Расположение элементов в «Название строк» играет большую роль. Если «Даты» будут расположены выше «Менеджеры», то данные будут разбиты на отдельные блоки по датам. Если же «Менеджеры» будут расположены в списке первыми, то сортировка будет проходить по именам сотрудников.

Когда таблица построена, перейдем к ее оформлению. Для начала изменим цветовую схему, применив к ней шаблон. Переходим на вкладку «Главная» и нажимаем на кнопку «Форматировать как таблицу». На экране появится список различных шаблонов форматирования, выбираем понравившийся нам и нажимаем на него. Excel автоматически определит границы таблицы, а если они окажутся заданы неверно, выделяем таблицу вручную и нажимаем кнопку «ОК». Таблица поменяла цветовую гамму и появилась возможность сортировки параметров.

Условное форматирование таблицы в Excel 2010

Не всегда удобно просматривать большое число значений и сравнивать их с плановыми. Предположим, что объем выручки на каждого менеджера в месяц должен составлять не менее 100 000 рублей. Но не обязательно оценивать показатели вручную, просматривая каждое значение: проще довериться встроенному компоненту Excel. Выделим область данных. Переходим на вкладку «Вставка — Условное форматирование — Набор значков» и из выпадающего меню выбираем понравившийся шаблон (допу́стим, светофор, так как с ним очень удобно работать). После выбора шаблона перед нами появится окно «Создание правил форматирования». Здесь необходимо напротив этих самых значков ввести показатели, при превышении которых работа сотрудника оценивается как: отличная, удовлетворительная и неудовлетворительная. Показатели вводятся в поле «Значение» напротив каждого из кружков, а параметр «Тип» в данном случае необходимо изменить с «Процент» на «Числа». В данном случае были заданы следующие показатели: 100 и 90 тысяч. (Третий параметр выставляется автоматически таким образом, чтобы включить все оставшиеся значения — в данном случае, меньше «удовлетворительного».) Нажимаем кнопку «ОК».

В ячейках всех значений появились кружки́ трех различных цветов. На основе представленной в таком виде информации намного проще оценить работу менеджеров за определенный период времени. Мы можем сравнить качество работы сотрудников, определить, кто из работников добивается наиболее выдающихся результатов, а кто, наоборот, требует к себе пристального внимания.

Но это еще не последний способ условного форматирования данных. В Excel 2010 появились такие инфографические элементы как «Гистограммы» и «Цветовые шкалы». Рассмотрим их более подробно. Выделим значения в ячейках и переедем «Вставка — Условное форматирование — Гистограммы». В выпадающем меню появится список шаблонов, при наведении на любой из них происходит предпросмотр результата. Выбираем понравившуюся цветовую схему и видим, что ячейки залиты горизонтальными столбцами разной величины. Они отображают в графическом виде те значения, которые присутствуют в ячейках. Если число будет введено со знаком минус, то график сместится в противоположную сторону от ячейки, указывая на отрицательные величины.

Компонент «Цветовые шкалы» заливает ячейку тем цветом, который соответствует введенному в нее значению. К примеру, ячейки с наименьшими значениями будет залиты красным цветом, со средними — желтым, а с наибольшими — зеленым. Цветовая схема может быть подобрана вами индивидуально, но суть примерно остается той же, что при использовании «Набора значков».

Срезы и не только

Но и это еще не все возможности визуализации данных, включенные в пакет Excel 2010. Рассмотрим еще такую удобную функцию, как «Срезы». Выбранные работники отработали в компании весьма внушительный срок и сложно при формировании сводной таблицы выделить ту или иную дату. Есть два способа добраться до определённой даты. Когда мы строим сводную таблицу, в правой части у нас расположены элементы, которые мы можем разместить в различные поля. Обращаемся к элементу «Даты» и вызываем выпадающее меню, путем нажатия на маркер со стрелочкой. Находим пункт «Фильтр по дате». Открывается огромный список с различными вариантами форматирования, нам нужна помесячная сортировка. Открываем «Все даты за период» и выбираем «Октябрь». Сводная таблица значительно сократилась, в ней остались значения только за октябрь. Это первый способ выборки данных.

Читайте также:  Защита ячеек от редактирования в Microsoft Excel

Второй способ организуется с помощью новой функции «Срез» — интересного инструмента анализа цифровых данных. Перейдем к «Вставка — Срез». Открывается окно «Вставка среза», в нем нужно отметить тот показатель, по которому будет производиться выборка значений, то есть колонку таблицы, по которой вы сможете посмотреть срезы своего отчета. Отмечаем «Даты» и нажимаем кнопку «ОК». На листе отобразится рамка с записанными в нее значениями.

Перетащим ее в любое удобное для нас место и отрегулируем ее размер таким образом, чтобы можно было видеть все представленные в ней значения. Также можно изменить цвет среза, все шаблоны отображаются на верхней панели. Теперь мы можем одним щелчком выбрать определенную дату и увидеть, каких результатов за эти дни добились сотрудники. Данная функция на порядок удобнее, чем «Фильтр по дате», так как она более гибкая. С помощью нее вы можете выбрать сразу несколько значений, по которым будет идти выборка.

Инфокривые

Следующий способ визуального анализа данных — инфокривые. Делаем активной свободную ячейку напротив строк с данными. Во вкладке «Вставка» находим раздел «Инфокривые» (в использованной версии Excel 2010 они назывались почему-то «Сперклайны»). Выделяем диапазон данных (это будет наша строка), и нажимаем кнопку «ОК». Вы можете увидеть, как в выбранной нами ячейке построился мини-график, это и есть инфокривая.

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

Инфокривые бывают трех типов: «График» — как раз его мы и рассматривали; «Столбец» — отображает данные в виде маленьких столбцов, наглядно показывая максимальные и минимальные значения; «Выигрыш/проигрыш» — ячейка как бы разделяется на две части, и в нижней размещаются квадраты с отрицательными значениями, а в верхней — с положительными (ноль не отображается вовсе).

Вывод

Отталкиваясь от материала данной статьи, можно научиться не только быстро оформлять таблицу, но и проводить визуальный анализ данных. Мы познакомились с таким режимом, как сводная таблица, научились производить фильтрацию значений и условное форматирование цифровых значений, составлять срезы. Кроме этого, мы наглядно разобрались с новой функцией под названием «Инфокривые». Нельзя не отметить, что в Excel 2010 добавлены усовершенствования, и практически все новые функции направлены на облегчение труда специалиста и наглядное представление данных. Если вас заинтересовала новая функциональность табличного редактора MS Excel 2010, то вы можете приобрести Microsoft Office 2010 у партнеров компании 1CSoft.

Условное форматирование в Excel

В этом уроке мы рассмотрим основы применения условного форматирования в Excel.

С его помощью мы можем выделять цветом значения таблиц по заданным критериям, искать дубликаты, а также графически “подсвечивать” важную информацию.

Основы условного форматирования в Excel

Используя условное форматирование, мы можем:

  • закрашивать значения цветом
  • менять шрифт
  • задавать формат границ

Применять его возможно как на одну, так и на несколько ячеек, строк и столбцов. Производить настройку формата мы можем с помощью условий. Далее мы на практике разберем как это делать.

Где находится условное форматирование в Эксель?

Кнопка “Условное форматирование” находится на панели инструментов, на вкладке “Главная”:

Как сделать условное форматирование в Excel?

При применении условного форматирования системе необходимо задать две настройки:

  • Каким ячейкам вы хотите задать формат;
  • По каким условиям будет присвоен формат.

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

  • В таблице с данными выделим диапазон, для которого мы хотим применить выделение цветом:

  • Перейдем на вкладку “Главная” на панели инструментов и кликнем на пункт “Условное форматирование”. В выпадающем списке вы увидите несколько типов формата на выбор:
    • Правила выделения
    • Правила отбора первых и последних значений
    • Гистограммы
    • Цветовые шкалы
    • Наборы значков
  • В нашем примере мы хотим выделить цветом данные с отрицательным значением. Для этого выберем тип “Правила выделения ячеек” => “Меньше”:

Также, доступны следующие условия:

  1. Значения больше или равны какому-либо значению;
  2. Выделять текст, содержащий определенные буквы или слова;
  3. Выделять цветом дубликаты;
  4. Выделять определенные даты.
  • Во всплывающем окне в поле “Форматировать ячейки которые МЕНЬШЕ” укажем значение “0”, так как нам нужно выделить цветом отрицательные значения. В выпадающем списке справа выберем формат отвечающих условиям:

  • Для присвоения формата вы можете использовать пред настроенные цветовые палитры, а также создать свою палитру. Для этого кликните по пункту:

  • Во всплывающем окне формата укажите:
    • цвет заливки
    • цвет шрифта
    • шрифт
    • границы ячеек

  • По завершении настроек нажмите кнопку “ОК”.

Ниже пример таблицы с применением условного форматирования по заданным нами параметрам. Данные с отрицательными значениями выделены красным цветом:

Как создать правило

Если пред настроенные условия не подходят, вы можете создавать свои правила. Для настройки проделаем следующие шаги:

  • Выделим диапазон данных. Кликнем на пункт “Условное форматирование” в панели инструментов. В выпадающем списке выберем пункт “Новое правило”:

  • Во всплывающем окне нам нужно выбрать тип применяемого правила. В нашем примере нам подойдет тип “Форматировать только ячейки, которые содержат”. После этого зададим условие выделять данные, значения которых больше “57”, но меньше “59”:

  • Кликнем на кнопку “Формат” и зададим формат, как мы это делали в примере выше. Нажмите кнопку “ОК”:

Условное форматирование по значению другой ячейки

На примерах выше мы задавали формат ячейкам, на основе их собственных значений. В Excel возможно задавать формат, на основе значений из других ячеек. Например, в таблице с данными курса доллара мы можем выделить цветом ячейки по правилу. Если курс доллара ниже чем в предыдущем месяце, то значение курса в текущем месяце будет выделено цветом.

Для создания условия по значению другой ячейки выполним следующие шаги:

  • Выделим первую ячейку для назначения правила. Кликнем на пункт “Условное форматирование” на панели инструментов. Выберем условие “Меньше”.
  • Во всплывающем окне указываем ссылку на ячейку, с которой будет сравниваться данная ячейка. Выбираем формат. Нажимаем кнопку “ОК”.

  • Повторно выделим левой клавишей мыши ячейку, которой мы присвоили формат. Кликнем на пункт “Условное форматирование”. Выберем в выпадающем меню “Управление правилами” => кликнем на кнопку “Изменить правило”:

  • В поле слева всплывающего окна “очистим” ссылку от знака “$”. Нажимаем кнопку “ОК”, а затем кнопку “Применить”.

  • Теперь нам нужно присвоить настроенный формат на остальные ячейки таблицы. Для этого выделим ячейку с присвоенным форматом, затем в левом верхнем углу панели инструментов нажмем на “валик” и присвоим формат остальным ячейкам:

На скриншоте ниже цветом выделены данные, в которых курс валюты стал ниже к предыдущему периоду:

Как применить несколько правил условного форматирования к одной ячейке

Возможно применять несколько правил к одной ячейке.

Например, в таблице с прогнозом погоды мы хотим закрасить разными цветами показатели температуры. Условия выделения цветом: если температура выше 10 градусов – зеленым цветом, если выше 20 градусов – желтый, если выше 30 градусов – красным.

Для применения нескольких условий к одной ячейке выполним следующие действия:

  • Выделим диапазон с данными, к которым мы хотим применить условное форматирование => кликнем по пункту “Условное форматирование” на панели инструментов => выберем условие выделения “Больше…” и укажем первое условие (если больше 10, то зеленая заливка). Такие же действия повторим для каждого из условий (больше 20 и больше 30). Не смотря на то, что мы применили три правила, данные в таблице закрашены зеленым цветом:

  • Кликнем на любую ячейку с присвоенным форматированием. Затем, снова кликнем по пункту “Условное форматирование” и перейдем в раздел “Управление правилами”. Во всплывающем окне, распределим правила от большего к меньшему и напротив первых двух поставим галочку “Остановить, если истина”. Этот пункт позволяет не применять остальные правила к ячейке, при соответствии первому. Затем кликнем кнопку “Применить” и “ОК”:

Применив их, наша таблица с данными температуры “подсвечена” корректными цветами, в соответствии с нашими условиями.

Как редактировать правило условного форматирования

Для редактирования присвоенного правила выполните следующие шаги:

  • Выделить левой клавишей мыши ячейку, правило которой вы хотите отредактировать.
  • Перейдите в пункт меню панели инструментов “Условное форматирование”. Затем, в пункт “Управление правилами”. Щелкните левой клавишей мыши по правилу, которое вы хотите отредактировать. Кликните на кнопку “Изменить правило”:

  • После внесения изменений нажмите кнопку “ОК”.

Как копировать правило условного форматирования

Для копирования формата на другие ячейки выполним следующие действия:

  • Выделим диапазон данных с примененным условным форматированием. Кликнем по пункту на панели инструментов “Формат по образцу”.
  • Левой клавишей мыши выделим диапазон, к которому хотим применить скопированные правила формата:

Как удалить условное форматирование

Для удаления формата проделайте следующие действия:

  • Выделите ячейки;
  • Нажмите на пункт меню “Условное форматирование” на панели инструментов. Кликните по пункту “Удалить правила”. В раскрывающемся меню выберите метод удаления:
Читайте также:  Точность округления как на экране в Microsoft Excel

–>Олимпиада “Наноэлектроника”
Неофициальный сайт –>

–>

–> –>Меню сайта –>

–> –>

–> –>Категории раздела –>

–> –>

–> –>Статистика –>

Национальный исследовательский ядерный университет «МИФИ»

Факультет: «Автоматика и электроника»
Кафедра: «Микро – и наноэлектроника»
Предмет: «Компьютерный практикум №13»

Конспект по теме
Условное форматирования, спарклайны – визуализация
в MS. Excel

Группа: А4-11
Подготовил: Назаренко А.Е.
Преподаватель: доц. Лапшинский В.А.

Спарклайны — это небольшие диаграммы в ячейках листа, визуально представляющие данные. С помощью спарклайнов можно показывать тенденции в рядах значений (например, сезонные повышения и спады или экономические циклы) и выделять максимальные и минимальные значения. Чтобы добиться максимального эффекта, спарклайны следует располагать рядом с соответствующими данными[1].
Условное форматирование – это изменение ячейки по определенным условиям. Ячейка может менять цвет заливки, рамку, начертание шрифта и тому подобное. Представьте, как будет удобно, если, к примеру, фамилии вашего списка окрашиваются в красный цвет, а зарплатные данные – в зеленый.
Microsoft Excel – программа для работы с электронными таблицами, созданная корпорацией Microsoft для Microsoft Windows, Windows NT и Mac OS. Она предоставляет возможности экономико-статистических расчетов, графические инструменты и, за исключением Excel 2008 под Mac OS X, язык макропрограммирования VBA (Visual Basic for Application).
Абсолютная ссылка – ссылки, которые при копировании в составе формулы в другую ячейку не изменяются. Абсолютные ссылки используются в формулах тогда, когда нежелательно автоматическое изменение ссылки при копировании
Относительная ссылка – ссылки, которые при копировании в составе формулы в другую ячейку автоматически изменяются

В Microsoft Office Excel 2010 значительно расширены и усовершенствованы приемы по визуализации данных. Это особенно актуально при подготовке информации для принятия управленческих решений. В частности, системное прогнозирование бюджетов предполагает формирование бюджетов в виде таблиц.
Безусловно, данные, представленные в таблицах бюджетов, полезны, однако распознать закономерности с первого взгляда бывает непросто. Благодаря новой функции инфокривых в приложении Excel 2010 можно создавать наглядные мини-диаграммы в пределах одной ячейки. Это быстрый и простой способ показать значимые изменения статей бюджета, характеризующую динамику за пять лет, с выявлением критических точек.
Чтобы добавить к числовым показателям контекст, можно вставить рядом с данными инфокривые. Занимая мало места, инфокривая позволяет продемонстрировать тенденцию в смежных с ней данных в понятном и компактном графическом виде. Инфокривую рекомендуется располагать в ячейке, смежной с используемыми ею данными.
Условное форматирование позволяет легко выделять необходимые ячейки или диапазоны, подчеркивать необычные значения и визуализировать данные, а новые возможности, делают форматирование в Excel 2010 еще более гибким.

В отличие от диаграмм на листе Excel, инфокривые не являются объектами: фактически, инфокривая – это небольшая диаграмма, являющаяся фоном ячейки. На рисунке 1 показаны инфогистограмма в ячейке F2 и инфографик в ячейке F3. Обе этих инфокривых получают данные из диапазона ячеек с A2 по E2 и отображают в ячейке диаграмму, иллюстрирующую динамику цен на акции [2].
На диаграммах (рисунок 1) показаны значения по кварталам, обозначены максимальное (31.03.2008) и минимальное (31.12.2008),значения, отображены все точки данных и показана нисходящая годовая тенденция.

Рисунок 1. Иллюстрация динамики цен на акции

Инфокривая в ячейке F6 иллюстрирует 5-летнюю динамику цен на тот же вид акций, однако она содержит линейчатую диаграмму, которая указывает лишь на тот факт, был ли в соответствующем году зарегистрирован рост (как в 2004—2007 годах) или спад (2008 год). Эта инфокривая использует значения в ячейках с A6 по E6.
Поскольку инфокривая – это небольшая диаграмма, встроенная в ячейку, в эту ячейку можно вводить текст, а инфокривая при этом будет использоваться в качестве фона (рис. 2)

Рисунок 2. Небольшая диаграмма

На этом спарклайне (рис. 1) маркер максимального значения обозначен зеленым цветом, а минимального – оранжевым. Все остальные маркеры обозначены черным цветом.
Чтобы применить к инфокривым цветовую схему, выберите один из встроенных форматов в коллекции стилей (вкладка Конструктор, которая становится доступной только при выборе ячейки, содержащей инфокривую). С помощью команд Цвет инфокривой и Цвет маркера можно выбрать цвет для максимального (например, зеленый), минимального (например, оранжевый) значений, значений открытия и закрытия.

Безусловно, данные, представленные в строках и столбцах, полезны, однако распознать закономерности с первого взгляда бывает непросто. Чтобы добавить к числовым показателям контекст, можно вставить рядом с данными инфокривые. Занимая мало места, инфокривая позволяет продемонстрировать тенденцию в смежных с ней данных в понятном и компактном графическом виде. Инфокривую рекомендуется располагать в ячейке, смежной с используемыми ею данными [3].
Можно быстро увидеть связь между инфокривой и используемыми ею данными, а при изменении данных
мгновенно увидеть соответствующие изменения на инфокривой. Помимо создания простой инфокривой на основе данных в строке или столбце, можно одновременно создавать

несколько инфокривых, выбирая несколько ячеек с соответствующими данными (рис. 3).
Кроме того, с помощью маркера заполнения смежной ячейки можно создавать инфокривые для строк данных, которые будут добавлены позднее.

Рисунок 3.
1 – Диапазон данных, используемых группой инфокривых
2 – Группа инфокривых

Одно из преимуществ инфокривых заключается в том, что их, в отличие от диаграмм, можно распечатать при печати листа, на котором они представлены.

Для построение инфокривой График выполните следующие действия:
1. Выберите пустую ячейку G3, в которую необходимо вставить инфокривую (рисунок 4);
2. На вкладке Вставка в группе Инфокривые выберите тип создаваемой инфокривой: График

Рисунок 4. Сводная таблица плановых статей бюджета

3. В поле Диапазон данных укажите диапазон ячеек с данными B3:F3, на основе которых будут создана инфокривая. Нажмите ОК и протяните ячейку G3 вниз, включая ячейку G10;
4. Чтобы включить показ максимальных и минимальных значений, в окне Работа с инфокривыми на вкладке Конструктор в группе, Показать установите флажки Максимальная точка и Минимальная точка;
5. Чтобы применить предопределенный стиль, на вкладке Конструктор в группе Стиль выберите Стиль 5;
6. На этой инфокривой маркер максимального значения обозначим зеленым цветом, а минимального – красным. Чтобы изменить цвет маркеров, выберите пункт Цвет маркера и укажите Максимальная точка Зеленый и Минимальная точка Красный
7. Поскольку инфокривая — это небольшая диаграмма, встроенная в ячейку, в эту ячейку можно вводить текст, а
инфокривая при этом будет использоваться в качестве фона. Введите текст оранжевого цвета Минимум 2004 г. или Минимум 2005 г., в зависимости от положения

красного маркера, характеризующего минимальное значение.

Для построение инфокривой Гистограмма выполните следующие действия:

1. Выберите группу пустых ячеек H3:H10, в которые необходимо вставить одну или несколько инфокривых (рис.5);

Рисунок 5. Показ тенденций изменения плановых статей бюджета с помощью инфокривой График

2. На вкладке Вставка в группе Инфокривые выберите тип создаваемой инфокривой: Гистограмма
3. В поле Диапазон данных укажите диапазон ячеек с данными B3:F10, на основе которых будут созданы инфокривые;

4. Чтобы включить показ максимальных и минимальных значений, в окне Работа с инфокривыми на

вкладке Конструктор в группе Показать установите флажки Максимальная точка и Минимальная точка;
5. Чтобы применить предопределенный стиль, на вкладке Конструктор в группе Стиль выберите Стиль 36;
6. На этой инфокривой маркер минимального значения обозначим красным цветом. Чтобы изменить цвет маркеров, выберите пункт Цвет маркера и укажите Минимальная точка – Красный;
7. Поскольку инфокривая — это небольшая диаграмма, встроенная в ячейку, в эту ячейку можно вводить текст, а инфокривая при этом будет использоваться в качестве фона. Введите текст светло-синего цвета Максимум 2008 г., в зависимости от положения красного маркера, характеризующего минимальное значение.

Можно быстро увидеть связь между инфокривой и используемыми ею данными, а при изменении данных мгновенно увидеть соответствующие изменения на инфокривой. Помимо создания простой инфокривой на основе данных в строке или столбце, можно одновременно создавать несколько инфокривых, выбирая несколько ячеек с соответствующими данными (рис.6)

Рисунок 6. Показ тенденций изменения плановых статей бюджета с помощью инфокривых График и Столбец

В отличие от диаграмм на листе Excel, инфокривые не являются объектами: фактически, инфокривая — это небольшая диаграмма, являющаяся фоном ячейки. На приведенном рисунке 4 показаны инфографик в ячейках G3:G10 и инфогистограмма в ячейке H3:H10. Обе этих инфокривых получают данные из диапазона ячеек с В3:F10 и отображают в ячейке диаграмму, иллюстрирующую динамику статей бюджета. На диаграммах показаны значения по годам, обозначены максимальное (2008 г.) и минимальное (2005 г.) значения, отображены все точки данных и показана восходящая годовая тенденция.
Инфокривые в ячейках G3:H10 иллюстрирует 5-летнюю динамику плановых статей бюджета, однако она содержит линейчатую диаграмму, которая указывает лишь на тот факт, был ли в соответствующем году зарегистрирован рост (как в 2006—2008 годах) или спад (2004-2005 годах). Эти инфокривые используют значения в ячейках В3:F10.

Одно из преимуществ инфокривых заключается в том, что их, в отличие от диаграмм, можно распечатать при печати листа, на котором они представлены.
В Excel 2010 доступны новые параметры форматирования гистограмм. Теперь можно применять сплошную заливку и границы, а также задавать направление столбцов “справа налево” вместо “слева направо”. Кроме того, столбцы для отрицательных значений теперь отображаются с противоположной стороны от оси относительно положительных.
.
Источники информации

Применение условного форматирования в Excel 2010

Автор: Леонид Радкевич · Опубликовано 11.12.2013 · Обновлено 06.12.2016

Каждый менеджер, бухгалтер или даже самый обычный пользователь не раз сталкивался с великолепными возможностями Excel 2010, позволяющей быстро и удобно работать с таблицами. Данная версия программы отличается расширенными возможностями условного форматирования сводных таблиц, что улучшает весь рабочий процесс.

Мы имеем функционал визуализации, предоставляющий возможность использовать в ячейках гистограммы, разнообразные значки и всевозможные цветовые шкалы. Теперь можно легко создать окошко в стиле управляющей консоли, что облегчает поиск информации. Также теперь этот тип форматирования начал успешно применятся в сводных таблицах, точнее говоря – к их структуре (а не только к данным). Описанный ниже способ подойдет даже тем, кто просто собирается купить новую стиральную машинку и выбирает модель.

Приведем простейший пример сводной таблицы, описывающий объемы продаж какого-то товара по регионам:

Теперь, опираясь на эти данные, создадим графический отчет объемов продаж за каждый временной период, чтобы облегчить восприятие информации. Естественно, можно воспользоваться сводной диаграммой, но хорошо подходит и условное форматирование в Excel 2010.

Самый простой вариант – использовать цветовые шкалы. Для этого выделяем поле «Объем продаж», охватывая все периоды. Осталось открыть вкладку «Главная», где нажимаем кнопку «Условное форматирование» (если вы вдруг используете английскую версию, то данная функция называется «Conditional Formatting»). Наведите курсор на «Гистограммы».

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

Excel 2010 уже имеет значительное количество полностью готовых сценариев форматирования, значительно экономящих рабочее время. Для создания описанных выше гистограмм, кстати, программа применяет алгоритм, самостоятельно определяющий максимальное и минимальное значения диапазона выбранных ячеек и только потом форматирует их, опираясь на обработанную информацию.

Итак – готовые сценарии, способные помочь в большинстве ситуаций:

— первые 10 элементов;

— последние 10 элементов;

Удаление уже используемого условного форматирования в Excel 2010 происходит по следующей схеме: в сводной таблице переходим на вкладку «Главная», нажимаем на «Условное форматирование», далее «Стили», и в выпадающем меню используем команду «Удалить правила» — «Удалить правила из этой сводной таблицы» (в английском варианте – «Clear Rules» и «Clear Rules from this PivotTable»).

Впрочем, можно легко создавать собственные правила форматирования.

Данная таблица – усложненный вариант первой, так что сразу переходим к примеру. Давайте отследим объем продаж и выручку за час. Мы будем использовать условное форматирование Excel 2010 для ускорения поиска совпадений и различий. Выделяем «Объем продаж». Далее по стандартной процедуре активируем сценарий («Главная» — «Условное форматирование»), но выбираем не готовый вариант, а функцию «Создать правило» (или «New Rule»).

Именно здесь можно определить ячейки, на которые будете применяться условное форматирование в Excel 2010, тип используемого правила и, собственно, параметры форматирования. Сначала задаются ячейки, и здесь есть достаточно простой выбор:

— выделенные («Selected Cells»);

— входящие в столбец «Объем продаж» («All Cells Showing «Sales_Amount» Values»), включая промежуточные и общие итоги. Данный вариант, кстати, хорошо подходит для анализа тех данных, которые требуют определения среднего, процентного соотношения или иными величинами, так или иначе являющимися разными уровнями одной величины;

— входящие в категорию «Объем продаж» только для «Рынка сбыта» («All Cells Showing «Sales_Amount» Values for «Market»»). Данный вариант полностью исключает общие и промежуточные итоги, что удобно для анализа некоторых отдельных значений.

Отметим, что команды «Объем продаж», «Рынок сбыта» при создании правил меняются в зависимости от имеющихся рабочих таблиц.

По нашему примеру самым выгодным является вариант «3», поэтому используется такой вариант:

При выборе правила (раздел «Выбрать правило» или «Select a Rule Туре») указываем именно то, которое и отвечает нашим требованиям.

— «Форматирование ячеек на основании значений» («Format All Cells Based on Their Values»). Используется для форматирования ячеек, которые соответствуют используемому диапазону значений. Лучше всего подходит для определения самых разных отклонений, если приходиться работать с огромным набором данных.

— «Форматирование ячеек содержащих» («Format Only Cells That Contain»). Форматирует ячейки, отвечающие подходящим условиям. В данном случае сравнение значений форматированных ячеек с обычными не происходит. Используется для сравнения общего набора данных с указанной ранее характеристикой.

— «Форматирование первых и последних значений» («Format Only Top or Bottom Ranked Values»).

— «Форматировать значения ниже или выше среднего» («Format Only Values That Are Above or Below the Average»).

— «Использовать формулу определения форматируемых ячеек» («Use a Formula to Determine Which Cells to Format»). Здесь уже условия условного форматирования опираются на формулу, заданную самим пользователем. Если значение ячейки (из подставленных в формулу) приходит со значением «true», то к ячейке применяют форматирование. В случае со значением «false» форматирование не применяется.

Применение гистограмм, наборов значков и цветовых шкал возможно только тогда, когда форматирование выделенных ячеек происходит на основании значений, занесенных в них. Для этого устанавливаем первый переключатель на «Форматирование всех ячеек на основании значений» («Format All Cells Based on Their Values»). Для обозначения проблемных областей можно использовать набор значков, что также хорошо подходит для данного сценария.

Ну и осталось определить точные параметры нашего форматирования. Здесь пригодится раздел «Изменения описания правила» («Edit the Ruie Description»). Для добавления значков в проблемные ячейки, мы используем выпадающее меню «Стиль формата» («Format Style») и выбираем «Наборы значков» («Icon Sets»).

В списке «Стиля значка» остается выбрать значение «3 знака» Это хорошо подойдет, если имеющуюся таблицу невозможно полностью раскрасить. В результате в окошке у нас должно получиться следующее:

При такой конфигурации Excel самостоятельно будет добавлять в ячейки значки, при этом следуя функции:

Условное форматирование в Excel: ничего сложного

В Excel вы можете использовать условное форматирование для реорганизации содержимого ячеек по своему усмотрению. Например, можно отобразить совершенно другой текст или изменить шрифт. Мощный набор инструментов включает множество опций, а также предопределенные правила. В этой статье мы расскажем о наиболее важных типах правил условного форматирования и рассмотрим их на примерах.

Условное форматирование может сделать работу в MS Excel 2010 значительно удобнее. Собственно, затем оно и нужно. О самых популярных его функциях мы рассказываем в данной заметке.

Отформатируйте все ячейки на основе их значений

Это правило Excel предоставляет пользователю графические параметры, например, для визуального оформления числовых значений. Оно облегчит анализ ваших данных.

  • Выберите область в электронной таблице Excel и нажмите вкладку «Главная».
  • Теперь кликните «Условное форматирование», а затем нажмите «Создать правило …».
  • В новом окне выберите тип «Форматировать все ячейки на основании их значений».
  • Выделите цветом те ячейки, значения которых на единицу больше или меньше среднего значения
  • Значения, которые не входят в определенную область, могут быть легко найдены с помощью Excel. Эта функция отмечает цветом ячейки, которые не соответствуют значению.
  • Выберите область в электронной таблице Excel и нажмите вкладку «Главная».
    Снова перейдите в «Условное форматирование», а затем в «Создать правило …».
  • Если вы теперь выберете «Стандарт», то зададите настройки, по которым ячейки со значениями выше или ниже среднего будут выделены цветом.

Двух- и трехцветная шкала в Excel

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

  • В нижней части окна теперь выберите стиль формата «двухцветная шкала».
  • В качестве типа выберите «минимальное значение» для минимального и «максимальное значение» для максимального.
  • При необходимости измените цвета и закройте окно нажатием на «ОК». Для трехцветной шкалы можно просто установить форматирование для среднего значения.

Гистограммы

Вы также можете использовать гистограммы при форматировании. Например, это нужно, чтобы сделать график ваших расходов за месяц.

  • Просто выберите «Гистограмма» в качестве стиля.
  • В качестве типа выберите «Минимум» и «Автоматический».
  • Оформите нижеуказанные столбцы и закройте, нажав на «ОК».

Символы

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

  • В нижней части окна выберите стиль.
  • В таблице выберите подходящий символ.
  • Для первого параметра «ЕСЛИ ЗНАЧЕНИЕ:» установите «>». Остальное можно оставить без изменений. Нажатие на «ОК» сохраняет правило Excel.

«Форматировать только ячейки, которые содержат…»

Этот тип форматирования особенно полезен для выделения отдельных специальных значений. Например, все числа ниже 0 могут быть отформатированы красным цветом.

  • Выберите область в таблице, для которой вы хотите применить форматирование, и создайте новое правило с типом «Форматировать только ячейки, содержащие».
  • Установите следующий параметр: «Значение ячейки меньше 0»
  • Нажмите кнопку «Формат …» и выберите красный цвет в новом окне как цвет выделения.
  • Дважды нажмите «ОК», чтобы создать правило.

Представить числовые данные в виде текста

Если у вас есть предопределенные повторяющиеся текстовые данные, этот тип форматирования идеален для них. Он не только экономит ваше время, но и предотвращает опечатки.

  • Выберите область в электронной таблице Excel, к которой вы хотите применить форматирование, и создайте новое правило с помощью «Форматировать только ячейки, содержащие».
  • Установите следующий параметр: «Значение ячейки, равное 1»,
  • Нажмите кнопку «Формат …» и выберите вкладку «Числа» в новом окне.
  • В левой части боковой панели нажмите «Пользовательский».
  • В текстовом поле под меткой «Тип:» введите следующее: «Hello World!» — кавычки при вводе обязательны.
  • Дважды нажмите «ОК», чтобы завершить действия.
  • Если вы вводите единицу в ячейку, находящуюся в определенной вами области, вместо этого появится текст «Hello World!». Таким же образом вы можете указать дополнительные тексты для других чисел.

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

  • Как следует из названия, данный тип отформатирует любое значение, к которому применяется конкретная формула. В этом примере все данные Excel, которые касаются будущих дат, будут выделены красным цветом.
  • Выберите область в таблице, для которой вы хотите применить форматирование, и создайте новое правило с типом «Использовать формулу для определения форматируемых ячеек».
  • Введите формулу = B2> СЕГОДНЯ ().
  • Нажмите кнопку «Формат …» и выберите красный цвет в новом окне.
  • Подтвердите выбор дважды нажав «ОК».

В этой статье показано лишь несколько вариантов условного форматирования. Функция может использоваться чрезвычайно универсально, и немного набив руку вы можете добиться отличных результатов.

Ссылка на основную публикацию
Adblock
detector