Диаграмма-шкала (bullet chart) для отображения KPI

Диаграмма-шкала (bullet chart) для отображения KPI

Если вы часто строите в Excel отчеты с финансовыми показателями (KPI), то вам должен понравится этот экзотический тип диаграммы — диаграмма-шкала или диаграмма-термометр (Bullet Chart):

  • Горизонтальная красная линия показывает целевое значение, к которому мы стремимся.
  • Трехцветная фоновая заливка шкалы наглядно отображает зоны «плохо-средне-хорошо», куда мы попадаем.
  • Черный центральный прямоугольник отображает текущее значение параметра.

Конечно, предыдущих значений параметра в такой диаграмме нет, т.е. ни динамики, ни трендов мы не увидим, но для точечного отображения достигнутых результатов vs целей на данный момент — вполне подойдет.

Этап 1. Гистограмма с накоплением

Начать придется с построения на основе наших данных стандартной гистограммы, которую мы потом за несколько шагов приведем к нужному нам виду. Выделяем исходные данные, открываем вкладку Вставка (Insert) и выбираем гистограмму с накоплением (Stacked Histogram):

  • Чтобы столбцы выстроились не в ряд, а друг на друга — меняем местами строки и столбцы с помощью кнопки Строка/столбец (Row/Column) на вкладке Конструктор (Design).
  • Легенду и название (если были) убираем — у нас тут минимализм.
  • Настраиваем цветовую заливку столбиков по их смыслу (выделить их по очереди, щелкнуть по выделенному правой кнопкой мыши и выбрать Формат точки данных).
  • Сужаем диаграмму по ширине

На выходе должно получиться что-то похожее:

Этап 2. Вторая ось

Выделяем ряд Значение (черный прямоугольник), открываем его свойства сочетанием Ctrl+1 или правой кнопкой мыши по нему Формат ряда (Format Data Point) и в окне параметров переключаем ряд на Вспомогательную ось (Secondary Axis).

Черный столбец уйдет по второй оси и станет закрывать все остальные цветные прямоугольники — не пугайтесь, все по плану 😉 Чтобы видеть шкалу увеличиваем для него Боковой зазор (Gap) до максимума, чтобы получить похожую картину:

Уже теплее, не так ли?

Этап 3. Ставим цель

Выделяем ряд Цель (красный прямоугольник), щелкаем по нему правой кнопкой мыши, выбираем команду Изменить тип диаграммы для ряда (Change chart type) и меняем тип на Точечную (Scatter). Красный прямоугольник должен превратиться в одиночный маркер (круглый или Ж-образный), т.е. в точку:

Не снимая выделения с этой точки, включаем для нее Планки погрешностей (Error Bars) на вкладке Макет (Layout). или на вкладке Конструктор (в Excel 2013). Последние версии Excel предлагают несколько вариантов таких планок — поэкспериментируйте с ними, при желании:

От нашей точки должны во все четыре стороны разойтись «усы» — обычно их используют для наглядного отображения допусков по точности или разброса (дисперсии) значений, например в статистике, но сейчас мы их используем с более прозаической целью. Вертикальные планки удаляем (выделить и нажать клавишу Delete), а горизонтальные настраиваем щелкнув по ним правой кнопкой мыши и выбрав команду Формат предела погрешностей (Format Error Bars):

В окне свойств горизонтальных планок погрешностей в разделе Величина погрешности выбираем Фиксированное значение или Пользовательская (Custom) и задаем положительное и отрицательное значение ошибки с клавиатуры равное 0,2 — 0,5 (подбирается на глаз). Здесь же можно увеличить толщину планки и поменять ее цвет на красный. Маркер можно отключить. В итоге должно получиться так:

Этап 4. Последние штрихи

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

Вот и все, диаграмма готова. Красиво, правда? 🙂

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

Источник:
http://www.planetaexcel.ru/techniques/4/204/

Создать шкалу

Данная функция является частью надстройки MulTEx

  • Описание, установка, удаление и обновление
  • Полный список команд и функций MulTEx
  • Часто задаваемые вопросы по MulTEx
  • Скачать MulTEx

Вызов команды:
MulTEx -группа Ячейки/ДиапазоныДиаграммыСоздать шкалу

Если вам часто приходится строить наглядные отчеты с отображением процента выполнения задач, отрисовки KPI, отображения целей по плану и текущего показателя выполнения плана — эта команда пригодится очень кстати, т.к. она за пару кликов построит наглядную и красивую диаграмму с отображением плана, текущего показателя и шкалы этапов выполнения. Ниже приведено описание на примере плана по прибыли предприятия, установленной цели по прибыли (План — 122 млн.руб.) и текущего показателя прибыли (Текущая прибыль — 84,6):

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

  • Процент выполнения по этапам (на примере проекта внедрения CRM на предприятии):
  • Отображение достижения в соответствии с установленной целью (три ключевых показателя: Плохо, Средне, Хорошо):
  • Процент выполнения одной задачи на основании только одного показателя (без этапов):

Что важно учитывать при подготовке исходных данных для такой диаграммы:

  1. Всегда должна быть хотя бы одна ячейка данных, отражающая «этап». Или иначе говоря — общий показатель для шкалы. На примере 3(рисунок выше) в качестве этапа выступает ячейка со значением «Всего %», равная 100%. Ячеек с этапами может быть сколько угодно, но не менее одной. Эти данные отвечают за отображение многоцветной фоновой широкой шкалы уровней (рис.1 и 2). Каждый цвет — отдельный этап. Высота этой шкалы зависит от суммарного числа всех этапов(процентов, сумм).
  2. Всегда должна быть ячейка, для записи текущего показателя(в примерах: Текущий процент, Значение, Текущее значение). На основании этой ячейки будет создан узкий вертикальный столб, отражающий текущий показатель выполнения плана(этапа).
  3. Всегда должна быть ячейка, для записи цели (в примерах Цель). Здесь есть нюанс: ячейка с целью всегда должна входить в диапазон для построения данных, но не обязательно должна содержать значение. Если ячейка содержит значение, то тогда на диаграмме будет отображена горизонтальная красная линия, показывающая значение, которого надо достичь. Если в ячейке с целью ничего не указывать, то горизонтальной линии не будет и будет отображена лишь шкала(вертикальный столб значения) текущего значения. Это наглядно продемонстрировано на рисунке 3. Процент выполнения одной задачи — диаграмма слева. При этом, если посмотреть на диаграмму рисунка, расположенную слева — то так же видно, что там нет широкой фоновой шкалы. Потому что шкалу можно сделать бесцветной. Для этого можно использовать следующие способы:
    • правая кнопка мыши на ряде данных — Формат ряда данныхЗаливкаБез заливки
    • убрать заливку из ячейки с показателем и установить флажок Назначить диаграмме цвета на основании ячеек (либо применить к диаграмме команду Цвет ряда из ячеек)

Хоть ячейки со значением и целью могут быть расположены где угодно, правильнее всего и удобнее в использовании располагать ячейки для Цели и Значения либо в конце основных исходных данных(пример 1 и 2), либо в самом начале(пример 3). Это обеспечит наглядность и упорядоченность исходных данных:

Какая именно ячейка(Цель или Значение) будет идти сначала, а какая после не имеет значения: сначала может идти цель, затем значение или наоборот.

Читайте также  Как в excel сделать надпись за текстом?

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

    Данные содержат заголовок — если исходные данные для построения диаграммы первой ячейкой содержат заголовок(Прибыль, млн), необходимо установить данный пункт. Если же данные не содержат заголовка — то обязательно необходимо снять галочку. Иначе диаграмма может быть построена неверно или возникнет ошибка приложения. По умолчанию включена.

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

    Указание параметров на примере скрина формы выше: указан диапазон ‘[bullet-chart.xlsm]Лист6’!C3:C8 , в который включен заголовок(ячейка С3 — Прибыль, млн ). Ячейка с данными цели расположена в ячейке С8 , а ячейка с данными значения — С7 .

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

Источник:
http://www.excel-vba.ru/multex/sozdat-shkalu/

Как создать интервальный график в Microsoft Excel

Интервальные графики (статистические диаграммы) представляют собой полезный инструмент для анализа частотных данных, предлагая пользователям возможность сортировать данные в группы (называемые рядами) на визуальном графике, аналогичном гистограмме. В этой статье пошагово описано как создать статистическую диаграмму и выполнить её настройку в Microsoft Excel.

Если вы хотите создавать статистические диаграммы в Excel, вам нужно будет использовать Excel 2016 или более позднюю версию. В более ранних версиях Office (Excel 2013 и до неё) эта функция отсутствует.

Как создать статистическую диаграмму в Excel

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

Статистические диаграммы позволяют легко получать данные такого рода и визуализировать их в диаграмме Excel.

Начните с того, что введите данные в Microsoft Excel и выделите данные, на основе которых будет строится интервальный график. Вы можете выбрать данные вручную или кликните на любую ячейку в нужном диапазоне и нажмите Ctrl+A на клавиатуре.

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

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

Форматирование гистограммы

Excel попытается определить интервалы и тип представления данных, но возможно вам придётся самостоятельно это настроить под ваши нужды. К примеру, в моём случае данные разбиты на 3 интервала, но я могу выбрать разбивку на интервалы с шагом в 10, либо отобразить данные по категориям. Рассмотрим это на конкретных примерах.

Кликните правой кнопкой мыши по надписям осей и выберите «Формат оси…»:

В открывшемся окне справа «Формат оси» выберите интервалы «По категориям»:

Теперь вы можете видеть каждое значение — в нашем случае это результаты тестов каждого из учеников.

Если нужен интервальный график, но с настраиваемой длиной интервала, то выберите вариант «Длина интервала» и установите нужную длину, например, 10:

Диапазоны нижней оси начинаются с наименьшего числа. Например, первая группа ячеек отображается как «[27, 37]», а самый большой диапазон заканчивается «[97, 107]», несмотря на то, что максимальный результат теста равен 100.

Вы можете выбрать определённое количество интервалов, в этом случае из максимального значения будет вычтено минимальное и полученный результат поделён на указанное количество интервалов — в результате интервалы могут заканчиваться на дробные числа:

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

Аналогично в один интервал можно собрать все значения, ниже определённой величины, для этого поставьте флажок «Выход за нижнюю границу интервала» и укажите значение, ниже которого все результаты будут помещены в один интервал:

Эти опции работают в сочетании с другими форматами группировки интервалов, такими как ширина или количество интервалов.

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

Стандартные параметры форматирования диаграммы, в том числе изменение границ и параметров столбцов, появятся в меню «Формат области диаграммы» справа.

Если вас интересуют вопросы редактирования внешнего вида, то они более подробно рассмотрены в статье «Как сделать гистограмму в Microsoft Excel», где показано, как применять готовые стили или вручную настроить любые параметры графиков, в том числе формат текста.

Источник:
http://zawindows.ru/%D0%BA%D0%B0%D0%BA-%D1%81%D0%BE%D0%B7%D0%B4%D0%B0%D1%82%D1%8C-%D0%B8%D0%BD%D1%82%D0%B5%D1%80%D0%B2%D0%B0%D0%BB%D1%8C%D0%BD%D1%8B%D0%B9-%D0%B3%D1%80%D0%B0%D1%84%D0%B8%D0%BA-%D0%B2-microsoft-excel/

Как сделать шкалу в excel?

На этом шаге мы рассмотрим вкладку Шкала диалогового окна Формат оси.

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

Рис. 1. Пример графиков

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

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

Excel автоматически определяет шкалу для диаграмм. Однако Вы можете изменить шкалу во вкладке Шкала диалогового окна Формат оси (рис. 2).

Рис. 2. Вкладка Шкала диалогового окна Формат оси

На вкладке Шкала имеются следующие опции:

  • Минимальное значение. Для ввода минимального значения оси. Если установлен флажок этой опции, минимальное значение определяется автоматически.
  • Максимальное значение. Для ввода максимального значения оси. Если установлен флажок этой опции, максимальное значение определяется автоматически.
  • Цена основных делений. Для ввода численного значения цены основных делений шкалы оси. Если установлен флажок этой опции, то цена деления шкалы определяется автоматически.
  • Цена промежуточных делений. Для ввода численного значения цены промежуточных делений шкалы. Если установлен фдажок этой опции, это значение определяется автоматически.
  • Ось тип оси пересекается в значении. Для помещения осей в различные положения. По умолчанию оси находятся по краям области построения. Точное текстовое название этой опции будет разным в зависимости от выбранной оси.
  • Логарифмическая шкала. Для создания логарифмической шкалы на осях. Логарифмическая шкала, как правило, удобна в научно-исследовательских приложениях, когда диапазон значений диаграммы очень велик. Вы получите сообщение об ошибке, если шкала включает отрицательные значения или 0.
  • Обратный порядок значений. Для изменения направления шкалы на обратное. Например, если отметить эту опцию для оси значений, тот наименьшее значение шкалы будет вверху, а наибольшее — внизу.
  • Пресечение с осью тип оси в максимальном значении. Для позиционирования осей в точке максимального значения (обычно ось позиционируется в точке минимального значения). Точное текстовое название этой опции зависит от выбранной оси.

На следующем шаге мы рассмотрим работу с рядами данных.

Источник:
http://it.kgsu.ru/MSExcel/excel102.html

Как создать диаграмму в Excel: пошаговая инструкция

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

Построение диаграммы на основе таблицы

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

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

Примечание:
В зависимости от версии программы количество разновидностей диаграмм может быть разным.

Также, существуют и другие типы диаграмм, но они не столь распространённые. Ознакомиться с полным списком можно через меню “Вставка” (в строке меню программы в самом верху), далее пункт – “Диаграмма”.

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

    Диаграмма в виде графика будет отображается следующим образом:

    А вот так выглядит круговая диаграмма:

    Как работать с диаграммами

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

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

    Нажав на кнопку “Добавить элемент диаграммы” можно раскрыть список действий, который поможет детально настроить вашу диаграмму.

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

    Довольно полезно, наряду со столбиками, иметь также конкретное значение данных для каждого из них. В этом нам поможет функция подписи данных. Открываем список, нажав кнопку “Добавить элемент диаграммы”, здесь выбираем пункт “Подписи данных” и далее – вариант, который нам нравится (в нашем случае – “У края, снаружи”).

    Готово, теперь наша диаграмма не только наглядна, но и информативна.

    Настройка размера шрифтов диаграммы

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

    Здесь можно внести требуемые изменения и сохранить их, нажав кнопку “OK”.

    Диаграмма с процентами

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

    1. По тому же принципу, который был описан выше, создайте таблицу и выделите участок, который необходимо преобразовать в диаграмму. Далее переходим во вкладку «Вставка» и выбираем, соответственно, тип диаграммы “Круговая”.
    2. По завершении предыдущего шага программа вас автоматически направит во вкладку по работе с вашей диаграммой – «Конструктор». Просмотрите предложенные макеты и остановите свой выбор на той диаграмме, где имеются значки процентов.
    3. Вот, собственно говоря, и все. Работа над круговой диаграммой с процентным отображением данных завершена.

    Диаграмма Парето — что это такое, и как ее построить в Экселе

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

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

    1. Создаем таблицу, например, с наименованиями товаров. В одном столбце будет указан объем закупки в денежном выражении, в другом – полученная прибыль. Цель данной таблицы вычислить — закупка какой продукции приносит максимальную выгоду при ее реализации.
    2. Строим обычную гистограмму. Для этого нужно выделить область таблицы, перейти во вкладку «Вставка» и далее выбирать тип диаграммы.
    3. После того как мы это сделали, сформируется диаграмма с 2-мя столбиками разного цвета, каждая из которых соответствует данным разных столбцов таблицы.
    4. Следующее, что нужно сделать – это изменить столбик, отвечающий за прибыль, на тип “График”. Для этого выделяем нужный столбик и идем в раздел «Конструктор». Там мы видим кнопку «Изменить тип диаграммы», нажимаем на нее. В открывшемся диалоговом окне переходим в раздел «График» и кликаем по подходящему типу графика.
    5. Вот и все, что требовалось сделать. Диаграмма Парето готова.Далее, ее можно отредактировать точно так же, как мы рассказывали выше, например, добавить значения столбиков и точек со значениями на графике.

    Заключение

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

    Источник:
    http://microexcel.ru/diagrammy-excel/

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

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

    Располагается эта полезная возможность на вкладке «Главная» в области «Стили» под одноименной пиктограммой:

    Создать правило

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

    Выбрав пункт «Создать правило…», приложение отобразит окно:

    В нем Вы можете выбрать тип правила и настроить его описание (подробнее читайте далее в статье).

    Виды условного форматирования

    Форматировать все ячейки на основании их значений

    Этот вид правила применяется для сравнения числовых значений в диапазоне. В описании можно выбрать стиль формата и соответствующие этому стилю параметры.

    Гистограмма

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

    Ширина ячейки принимается за 100%, что соответствует максимальному значению диапазона правила. Т.е. ячейка, содержащая максимальное значение будет залита полностью, а ячейка со значением в 2 раза меньшим максимальному – наполовину. В случае отрицательного значения, столбец будет окрашен другим цветом и иметь другую направленность (это можно изменить).

    • Показывать только столбец – установив флажок на данном поле, Вы сообщаете, что для диапазона ячеек правила необходимо скрывать содержимое и оставлять только формат;
    • Параметры значений – здесь устанавливаются максимальные и минимальные значения и их типы. В качестве типа может выступать число, процент, формула, процентиль либо по умолчанию (авто). Значение может быть только числовым. Все числа, меньше минимального (включая отрицательные), приравниваются к нулю, т.е. не содержат столбца. А те, которые больше максимального, приравниваются к 100% и закрашиваются полностью.
    • Внешний вид столбца – устанавливает способ заливки (сплошной или градиентный), границу и их цвета;
    • Направление столбца – определяет способ направленности (слева направо либо наоборот);
    • Кнопка «Отрицательные значения и ось…» – настройки отображения столбцов для отрицательных чисел. Что они позволяют:
      • Установить свой цвет заливки столбца и его границу или сделать их одинаковыми для всех значений (положительных и отрицательных. По умолчанию они различаются);
      • Задать положение оси или одинаковую направленность для всех значений.

    Цветовые шкалы

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

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

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

    • Минимальным числом задан ноль, а значения меньше его, будут иметь такие же цвет и насыщенность;
    • Средним значением указана единица и желтый цвет. Это значит, что переход шкалы от красного к желтому будет осуществлен между 0 и 1;
    • 4 является максимальным значением. Все, что превышает его, получает те же установки. Переход от желтого к зеленому происходит между 1 и 4.

    Наборы значков (флажков)

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

    Как и в случаях, описанных выше, за 100% принимается максимальное число, а остальные составляют от него какую-то долю. Весь диапазон разделяется на определенное количество частей, которое равно количеству значков в выбранном наборе. Каждой такой части соответствует свой флажок. Если диапазон нужно разделить не по долям, а по конкретным значениям, то поменяйте тип значения для значка.

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

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

    Рассмотрим правила, которые имеются в этом пункте:

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

    Форматировать только первые и последние значения

    Из названия понятно, что правило срабатывает для тех ячеек, которые идут первыми (наибольшими) или последними (наименьшими) в указанном диапазоне. Количество таких ячеек указывается в виде числа или процента.

    Формула в условном форматировании

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

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

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

    Используем 2 условия со следующими формулами:

    • Если на складе нет товара, т.е. равен 0, то подсвечиваем позицию заказа красным – =ВПР(D3;A:B;2;ЛОЖЬ)=0;
    • Если на складе есть товар, но его количество меньше, чем указано в позиции заказа, то последнюю подсвечиваем желтым – =И(ВПР(D3;$A:$B;2;ЛОЖЬ) 0).

    Теперь необходимо выделить требуемый диапазон и создать нужные нам правила.

    В функции, в качестве первого аргумента используется ссылка всего на одну ячейку. Вас это не должно смущать, так как приложение «понимает», что ее нужно сместить в соответствии с диапазоном правила. Главное, чтобы она была относительной, т.е. не закреплена символами доллара – $.

    Остальные правила

    Ничего не было сказано о еще двух видах правил, а именно:

    • Форматирование на основе среднего значения – полное название «Форматировать только значения, которые находятся выше или ниже среднего»;
    • Форматирование уникальных или повторяющихся значений.

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

    Управление правилами

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

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

    В самом верху окна можно выбрать, какие правила следует выводить в списке: из текущего диапазона, с этого листа, из любого другого листа открытой книги.

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

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

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

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

    Источник:
    http://office-menu.ru/uroki-excel/14-professionalnoe-ispolzovanie-excel/58-uslovnoe-formatirovanie-excel