Как настроить светофор в excel
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 с числовой константой. Чаще всего используются правила «больше / меньше / равно / между». Поэтому они вынесены в меню «Правила выделения ячеек».
Введем в диапазон А1:А11 ряд чисел:
Выделим диапазон значений. Открываем меню «Условного форматирования». Выбираем «Правила выделения ячеек». Зададим условие, например, «больше».
Введем в левое поле число 15. В правое – способ выделения значений, соответствующих заданному условию: «больше 15». Сразу виден результат:
Выходим из меню нажатием кнопки ОК.
Условное форматирование по значению другой ячейки
Сравним значения диапазона А1:А11 с числом в ячейке В2. Введем в нее цифру 20.
В левое поле вводим ссылку на ячейку В2 (щелкаем мышью по этой ячейке – ее имя появится автоматически). По умолчанию – абсолютную.
Результат форматирования сразу виден на листе Excel.
Значения диапазона А1:А11, которые меньше значения ячейки В2, залиты выбранным фоном.
Зададим условие форматирования: сравнить значения ячеек в разных диапазонах и показать одинаковые. Сравнивать будем столбец А1:А11 со столбцом В1:В11.
Каждое значение в столбце А программа сравнила с соответствующим значением в столбце В. Одинаковые значения выделены цветом.
Внимание! При использовании относительных ссылок нужно следить, какая ячейка была активна в момент вызова инструмента «Условного формата». Так как именно к активной ячейке «привязывается» ссылка в условии.
Чтобы инструмент «Условное форматирование» правильно выполнил задачу, следите за этим моментом.
Проверить правильность заданного условия можно следующим образом:
В открывшемся окне видно, какое правило и к какому диапазону применяется.
Условное форматирование – несколько условий
Исходный диапазон – А1:А11. Необходимо выделить красным числа, которые больше 6. Зеленым – больше 10. Желтым – больше 20.
Заполняем параметры форматирования по первому условию:
Нажимаем ОК. Аналогично задаем второе и третье условие форматирования.
Обратите внимание: значения некоторых ячеек соответствуют одновременно двум и более условиям. Приоритет обработки зависит от порядка перечисления правил в «Диспетчере»-«Управление правилами».
То есть к числу 24, которое одновременно больше 6, 10 и 20, применяется условие «=$А1>20» (первое в списке).
Условное форматирование даты в Excel
Выделяем диапазон с датами.
В открывшемся окне появляется перечень доступных условий (правил):
Выбираем нужное (например, за последние 7 дней) и жмем ОК.
Красным цветом выделены ячейки с датами последней недели (дата написания статьи – 02.02.2016).
Условное форматирование в Excel с использованием формул
Если стандартных правил недостаточно, пользователь может применить формулу. Практически любую: возможности данного инструмента безграничны. Рассмотрим простой вариант.
Есть столбец с числами. Необходимо выделить цветом ячейки с четными. Используем формулу: =ОСТАТ($А1;2)=0.
Выделяем диапазон с числами – открываем меню «Условного форматирования». Выбираем «Создать правило». Нажимаем «Использовать формулу для определения форматируемых ячеек». Заполняем следующим образом:
Для закрытия окна и отображения результата – ОК.
Условное форматирование строки по значению ячейки
Задача: выделить цветом строку, содержащую ячейку с определенным значением.
Таблица для примера:
Необходимо выделить красным цветом информацию по проекту, который находится еще в работе («Р»). Зеленым – завершен («З»).
Порядок заполнения условий для форматирования «завершенных проектов»:
Обратите внимание: ссылки на строку – абсолютные, на ячейку – смешанная («закрепили» только столбец).
Аналогично задаем правила форматирования для незавершенных проектов.
В «Диспетчере» условия выглядят так:
Когда заданы параметры форматирования для всего диапазона, условие будет выполняться одновременно с заполнением ячеек. К примеру, «завершим» проект Димитровой за 28.01 – поставим вместо «Р» «З».
«Раскраска» автоматически поменялась. Стандартными средствами Excel к таким результатам пришлось бы долго идти.
Условное форматирование в Excel
В этом уроке мы рассмотрим основы применения условного форматирования в Excel.
С его помощью мы можем выделять цветом значения таблиц по заданным критериям, искать дубликаты, а также графически «подсвечивать» важную информацию.
Основы условного форматирования в Excel
Используя условное форматирование, мы можем:
Применять его возможно как на одну, так и на несколько ячеек, строк и столбцов. Производить настройку формата мы можем с помощью условий. Далее мы на практике разберем как это делать.
Где находится условное форматирование в Эксель?
Кнопка «Условное форматирование» находится на панели инструментов, на вкладке «Главная»:
Как сделать условное форматирование в Excel?
При применении условного форматирования системе необходимо задать две настройки:
Ниже, мы рассмотрим как применить условное форматирование. Представим, что у нас есть таблица с динамикой курса доллара в рублях за год. Наша задача выделить красным цветом те данные, в которых курс снижался предыдущему месяцу. Итак, выполним следующие шаги:
Также, доступны следующие условия:
Ниже пример таблицы с применением условного форматирования по заданным нами параметрам. Данные с отрицательными значениями выделены красным цветом:
Как создать правило
Если пред настроенные условия не подходят, вы можете создавать свои правила. Для настройки проделаем следующие шаги:
Условное форматирование по значению другой ячейки
На примерах выше мы задавали формат ячейкам, на основе их собственных значений. В Excel возможно задавать формат, на основе значений из других ячеек. Например, в таблице с данными курса доллара мы можем выделить цветом ячейки по правилу. Если курс доллара ниже чем в предыдущем месяце, то значение курса в текущем месяце будет выделено цветом.
Для создания условия по значению другой ячейки выполним следующие шаги:
На скриншоте ниже цветом выделены данные, в которых курс валюты стал ниже к предыдущему периоду:
Как применить несколько правил условного форматирования к одной ячейке
Возможно применять несколько правил к одной ячейке.
Например, в таблице с прогнозом погоды мы хотим закрасить разными цветами показатели температуры. Условия выделения цветом: если температура выше 10 градусов — зеленым цветом, если выше 20 градусов — желтый, если выше 30 градусов — красным.
Для применения нескольких условий к одной ячейке выполним следующие действия:
Применив их, наша таблица с данными температуры «подсвечена» корректными цветами, в соответствии с нашими условиями.
Как редактировать правило условного форматирования
Для редактирования присвоенного правила выполните следующие шаги:
Как копировать правило условного форматирования
Для копирования формата на другие ячейки выполним следующие действия:
Как удалить условное форматирование
Для удаления формата проделайте следующие действия:
Еще больше полезных приемов в работе со списками данных и функциями в Excel вы узнаете в практическом курсе «От новичка до мастера Excel«. Успей зарегистрироваться по ссылке!
Как настроить светофор в excel
Условное форматирование Excel
Представьте себе монитор, где выведены рабочие узлы атомной электростанции, который отображает стабильность протекания всех процессов. Но вдруг один узел выходит из строя и сигнализирует диспетчеру о сбое, загораясь ярким красным светом. Согласитесь, очень удобно? Похожим целям служит функция условного форматирования в Excel — обеспечение наилучшей наглядности информации.
Располагается эта полезная возможность на вкладке «Главная» в области «Стили» под одноименной пиктограммой:
Для создания правила условного форматирования в Excel кликните по соответствующей кнопке на ленте, раскрыв следующее меню:
Выбрав пункт «Создать правило…», приложение отобразит окно:
В нем Вы можете выбрать тип правила и настроить его описание (подробнее читайте далее в статье).
Виды условного форматирования
Форматировать все ячейки на основании их значений
Этот вид правила применяется для сравнения числовых значений в диапазоне. В описании можно выбрать стиль формата и соответствующие этому стилю параметры.
Гистограмма
Данная возможность позволяет отобразить в каждой ячейке горизонтальный столбец, похожий на частичную заливку. Если Вы хоть раз использовали гистограмму при построении диаграмм, то Вам будет понятно, о чем идет речь.
Ширина ячейки принимается за 100%, что соответствует максимальному значению диапазона правила. Т.е. ячейка, содержащая максимальное значение будет залита полностью, а ячейка со значением в 2 раза меньшим максимальному — наполовину. В случае отрицательного значения, столбец будет окрашен другим цветом и иметь другую направленность (это можно изменить).
Цветовые шкалы
Как и гистограммы, шкалы в условном форматировании заливают цветом ячейку с числовым значением, но отличие заключается в том, что последние заливают ее полностью. Чем выше значение, тем более насыщенная заливка. Также можно использовать несколько цветов, где, например, меньшие числа залиты зеленым, средние желтым, а большие красным.
В качестве примера, рассмотрим настройку трехцветной шкалы, хотя она мало чем отличается от настройки двухцветной.
Здесь Вы можете установить, что считать минимальным значением, что средним, а что максимальным. Также возможно задать предпочтительный цвет и тип показателя.
Разберем установки, представленные на изображении:
Наборы значков (флажков)
Этот вид условного форматирования, в отличие от цвета заливки, использует различные значки в виде фигур, направлений, индикаторов и оценок.
Как и в случаях, описанных выше, за 100% принимается максимальное число, а остальные составляют от него какую-то долю. Весь диапазон разделяется на определенное количество частей, которое равно количеству значков в выбранном наборе. Каждой такой части соответствует свой флажок. Если диапазон нужно разделить не по долям, а по конкретным значениям, то поменяйте тип значения для значка.
Форматировать только ячейки, которые содержат
Этот вид условного форматирования отличается от первого тем, что он создает правило, которое должно соблюдаться, чтобы формат был применен к ячейке.
Рассмотрим правила, которые имеются в этом пункте:
Форматировать только первые и последние значения
Из названия понятно, что правило срабатывает для тех ячеек, которые идут первыми (наибольшими) или последними (наименьшими) в указанном диапазоне. Количество таких ячеек указывается в виде числа или процента.
Формула в условном форматировании
Когда имеющихся правил недостаточно, можно создать свое, задав ему практически любую логику, на основе формул, результатом выполнения которой должно быть логическое значение. Эти тип называется «Использовать формулу для определения форматируемых ячеек».
Для примера рассмотрим список заказа товаров, который необходимо сравнить с остатком на складе. Всего участвуют 2 таблицы: сам заказ и таблица остатков.
На изображении показан вариант, где уже применено условное форматирование ячеек. Рассмотрим, как его создать.
Используем 2 условия со следующими формулами:
Теперь необходимо выделить требуемый диапазон и создать нужные нам правила.
Остальные правила
Ничего не было сказано о еще двух видах правил, а именно:
По ним остается добавить только то, что в первом можно использовать стандартные отклонения. В остальном, они говорят сами за себя.
Управление правилами
Помимо умения создавать правила, условным форматированием также нужно корректно управлять. Особенно это важно, когда для одного диапазона применяется несколько условий. Но обо всем по порядку.
Диспетчер правил условного форматирования отображает список, состоящий из условия, формата и диапазона, к которому применено правило.
В самом верху окна можно выбрать, какие правила следует выводить в списке: из текущего диапазона, с этого листа, из любого другого листа открытой книги.
Первые три кнопки диспетчера должны быть понятны без дополнительных пояснений, а вот на последних двух (стрелки вверх и вниз) остановимся подробнее.
На изображение приведено 2 правила: значение равно трем и значение больше двух. Представьте, что они применены к ячейке со числом 3. Какое из них сработает? В этом случае оба, так как между ними нет конфликта в форматировании, одно отвечает за заливку, а второе за границу. Но если бы они оба отвечали за один и тот же стиль, то выполнилось правило, которое стоит выше, потому что имеет больший приоритет.
Так вот, стрелками окна можно менять положение отдельно выделенного правила и, соответственно, его значимость.
Рассмотрим еще один случай, когда требуется выполнить только одно условие. В конце каждого правила имеется флажок «Остановить, если истина». Выставив его, Вы отменяете выполнение всех последующих правил для текущего диапазона, при условии, что это оно выполняется. Исходя из рассматриваемого примера, если ячейка содержит значение 3, то проверка на условие «больше двух» произведена не будет.
Если материалы office-.ru Вам помогли, то поддержите, пожалуйста, проект, чтобы я мог развивать его дальше.
Обучение условному форматированию в Excel с примерами
Условное форматирование — удобный инструмент для анализа данных и наглядного представления результатов. Умение им пользоваться сэкономит массу времени и сил. Достаточно бегло взглянуть на документ — нужная информация получена.
Как сделать условное форматирование в Excel
Инструмент «Условное форматирование» находится на главной странице в разделе «Стили».
При нажатии на стрелочку справа открывается меню для условий форматирования.
Сравним числовые значения в диапазоне Excel с числовой константой. Чаще всего используются правила «больше / меньше / равно / между». Поэтому они вынесены в меню «Правила выделения ячеек».
Введем в диапазон А1:А11 ряд чисел:
Выделим диапазон значений. Открываем меню «Условного форматирования». Выбираем «Правила выделения ячеек». Зададим условие, например, «больше».
Введем в левое поле число 15. В правое — способ выделения значений, соответствующих заданному условию: «больше 15». Сразу виден результат:
Выходим из меню нажатием кнопки ОК.
Условное форматирование по значению другой ячейки
Сравним значения диапазона А1:А11 с числом в ячейке В2. Введем в нее цифру 20.
Выделяем исходный диапазон и открываем окно инструмента «Условное форматирование» (ниже сокращенно упоминается «УФ»). Для данного примера применим условие «меньше» («Правила выделения ячеек» — «Меньше»).
В левое поле вводим ссылку на ячейку В2 (щелкаем мышью по этой ячейке — ее имя появится автоматически). По умолчанию — абсолютную.
Результат форматирования сразу виден на листе Excel.
Значения диапазона А1:А11, которые меньше значения ячейки В2, залиты выбранным фоном.
Зададим условие форматирования: сравнить значения ячеек в разных диапазонах и показать одинаковые. Сравнивать будем столбец А1:А11 со столбцом В1:В11.
Выделим исходный диапазон (А1:А11). Нажмем «УФ» — «Правила выделения ячеек» — «Равно». В левом поле — ссылка на ячейку В1. Ссылка должна быть СМЕШАННАЯ или ОТНОСИТЕЛЬНАЯ!, а не абсолютная.
Каждое значение в столбце А программа сравнила с соответствующим значением в столбце В. Одинаковые значения выделены цветом.
Внимание! При использовании относительных ссылок нужно следить, какая ячейка была активна в момент вызова инструмента «Условного формата». Так как именно к активной ячейке «привязывается» ссылка в условии.
Чтобы инструмент «Условное форматирование» правильно выполнил задачу, следите за этим моментом.
Проверить правильность заданного условия можно следующим образом:
В открывшемся окне видно, какое правило и к какому диапазону применяется.
Условное форматирование — несколько условий
Исходный диапазон — А1:А11. Необходимо выделить красным числа, которые больше 6. Зеленым — больше 10. Желтым — больше 20.
Заполняем параметры форматирования по первому условию:
Нажимаем ОК. Аналогично задаем второе и третье условие форматирования.
Обратите внимание: значения некоторых ячеек соответствуют одновременно двум и более условиям. Приоритет обработки зависит от порядка перечисления правил в «Диспетчере»-«Управление правилами».
То есть к числу 24, которое одновременно больше 6, 10 и 20, применяется условие «=$А1>20» (первое в списке).
Условное форматирование даты в Excel
Выделяем диапазон с датами.
Применим к нему «УФ» — «Дата».
В открывшемся окне появляется перечень доступных условий (правил):
Выбираем нужное (например, за последние 7 дней) и жмем ОК.
Красным цветом выделены ячейки с датами последней недели (дата написания статьи — 02.02.2016).
Условное форматирование в Excel с использованием формул
Если стандартных правил недостаточно, пользователь может применить формулу. Практически любую: возможности данного инструмента безграничны. Рассмотрим простой вариант.
Есть столбец с числами. Необходимо выделить цветом ячейки с четными. Используем формулу: =ОСТАТ($А1;2)=0.
Выделяем диапазон с числами — открываем меню «Условного форматирования». Выбираем «Создать правило». Нажимаем «Использовать формулу для определения форматируемых ячеек». Заполняем следующим образом:
Для закрытия окна и отображения результата — ОК.
Условное форматирование строки по значению ячейки
Задача: выделить цветом строку, содержащую ячейку с определенным значением.
Таблица для примера:
Необходимо выделить красным цветом информацию по проекту, который находится еще в работе («Р»). Зеленым — завершен («З»).
Выделяем диапазон со значениями таблицы. Нажимаем «УФ» — «Создать правило». Тип правила — формула. Применим функцию ЕСЛИ.
Порядок заполнения условий для форматирования «завершенных проектов»:
Обратите внимание: ссылки на строку — абсолютные, на ячейку — смешанная («закрепили» только столбец).
Аналогично задаем правила форматирования для незавершенных проектов.
В «Диспетчере» условия выглядят так:
Когда заданы параметры форматирования для всего диапазона, условие будет выполняться одновременно с заполнением ячеек. К примеру, «завершим» проект Димитровой за 28.01 — поставим вместо «Р» «З».
«Раскраска» автоматически поменялась. Стандартными средствами Excel к таким результатам пришлось бы долго идти.
Как представить отклонения от бюджета в виде светофора с помощью Excel
Решение подскажет, как с помощью Excel оформить план-факт отклонения в виде светофора и тем самым повысить наглядность отчета об исполнении бюджета. Рекомендации пригодятся компаниям из любой отрасли независимо от масштаба деятельности.
Для наглядного предоставления результатов исполнения бюджета по окончании отчетного периода можно создать в Excel модель «Светофор». Для этого необходимо:
Рисунок 1. Таблица анализа план-факт отклонений Скачать
Как подготовить исходные данные для анализа отклонений в Excel
Исходные данные, которые обычно представляют собой выгрузку из учетной базы (бухгалтерской или управленческой) и имеют большой объем информации (за несколько лет, по нескольким направлениям деятельности и т. д.), для простоты последующего анализа необходимо оформить в виде простой плоской таблицы, представленной на рисунке 2.
Рисунок 2. Исходные данные для анализа план-факт отклонений Скачать
Если исходные данные изначально содержатся в нескольких таблицах, их следует объединить в одну, увеличив число строк основной таблицы план-фактов или добавив в нее новые вычисляемые столбцы.
Затем на базе такой таблицы следует создать сводную и добавить в нее те показатели, которые далее планируется использовать в качестве фильтров и аналитик выходной отчетной формы. Их взаиморасположение на осях таблицы может быть произвольным, но для удобства последующей работы лучше придерживаться стандартного матричного формата. К примеру, на вертикальной оси отчета перечислить подразделения компании и направления деятельности, а на горизонтальной — детализацию доходов по типам, временные интервалы.
Допустим, в компании «Альфа» существует три дирекции, каждая из которых работает в трех регионах продаж. Задача финансовой службы — наглядно показать исполнение плана руководству, предоставив пользователю отчета возможность самостоятельного выбора интересующей его дирекции и региона. В этом случае в макет сводной таблицы необходимо поместить следующие аналитики:
Таким образом, исходные данные, показанные на рисунке 2, представляются сводной таблицей с рисунка 3.
Рисунок 3. Сводная таблица данных для анализа продаж
Как задать диапазон отклонений для анализа бюджета в Excel
Чтобы оформить план-факт отклонения от бюджета в виде индикатора-светофора, необходимо свести характеризующие его числовые значения в три группы в зависимости от степени критичности отклонения (см. рисунок 4. Группирование показателей исполнения плана):
Рисунок 4. Группирование показателей исполнения плана Скачать
Первый вариант шкалы (столбец E) подходит для тех случаев, когда любое перевыполнение плана считается положительным событием. Тогда диапазон значений достаточно разделить всего на три смежных интервала:
Второй вариант (столбец C) предназначен для модели поведения, в которой существенное перевыполнение плана также интерпретируется как событие, требующее вмешательства ответственного лица. В такой шкале присутствуют уже пять диапазонов.
Правило пересчета определяется в два этапа.
1. Каждому состоянию присваивается числовой номер — код состояния:
2. Коды состояний сопоставляются с границами интервалов, как это представлено в таблице ниже.
Таблица. Пороговые уровни шкалы отклонений
Как создать индикатор-светофор для иллюстрации отклонений от бюджета в Excel
Индикатор-светофор можно добавить в виде дополнительного столбца в стандартную таблицу управленческого отчета вместе с набором элементов управления им. Такая таблица представляет собой информационную панель, характеризующую исполнение бюджета в целом (см. рисунок 1. Таблица анализа план-факт отклонений).
Элементы управления на панели позволяют пользователю отчета настроить выходную таблицу для отображения нужных ему данных. Они организованы в виде выпадающих списков. Отобранные значения из списков впоследствии используются для определения пути к одной из ячеек сводной таблицы.
Выпадающий список. Для каждого выпадающего списка сначала необходимо составить справочник значений (подробнее см. Как исключить ошибки при вводе данных в Excel). Важно, чтобы элементы, перечисленные в каждом из справочников, соответствовали множеству уникальных значений одного из столбцов исходной таблицы. Для компании «Альфа» в информационной панели (см. рисунок 1. Таблица анализа план-факт отклонений) можно выбрать:
Рисунок 5. Справочники для создания списков
Элементы, соотнесенные с выпадающими списками, могут изменяться пользователем, а значения аналитик, размещенных на осях отчетной таблицы, фиксируются и остаются неизменными.
Распределение аналитик сводной таблицы между списками и осями отчета определяется потребностями пользователя. Иначе говоря, если отчет составляется в разрезе отдельных дирекций (наименования подразделений перечисляются как заголовки строк таблицы), пользователь может выбирать годы, кварталы и регионы. Если же важно оценить ситуацию по всей компании в динамике по периодам, отчет можно перенастроить — перенести в заголовки строк годы и кварталы, а все остальные параметры сделать варьируемыми.
Работу с отчетом можно существенно упростить, если:
Плановые и фактические данные. Готовая форма должна автоматически заполняться данными в соответствии с выбранными значениями из выпадающих списков. Данные извлекаются из сводной таблицы — плановые (столбец I) и фактические (столбец J) — при помощи функции
ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ(поле_данных, сводная_таблица, [поле1, элем1, поле2, элем2],…), где:
Эту функцию необходимо прописать в ячейке I5 в следующем виде:
В ячейку J5 заносится аналогичная формула, но в третьем элементе (координате) значение «План» заменяется на «Факт»:
Рисунок 6. Порядок заполнения расчетной формулы для сводной таблицы
Рисунок 7. Выбор года из сводной таблицы
План-факт отклонения. Для отображения отклонений в таблице в формате индикатора «Светофор» нужно воспользоваться двумя функциями:
Итоговое выражение будет представлено следующим образом:
Теперь, выделив ячейки столбца К, нужно перейти во вкладку меню «Условное форматирование» и создать новое правило «Форматировать все ячейки на основании их значений». В качестве стиля выбрать «Набор значков», а правила задать для пороговых уровней отклонений, отмеченных в таблице.
Рисунок 8. Форматирование таблицы план-факт отклонений
Совет: не забудьте, что данные для отчетов в Excel можно загружать из внешних источников
Павел Сухарев, начальник отдела планирования, бюджетного контроля и финансовой отчетности сервисной компании
Если речь идет о больших объемах информации, то исходные данные удобнее размещать не внутри книги MS Excel, а настроить соответствующее представление в системе управления базами данных. В меню «Создание сводной таблицы» нужно перейти к вкладке «Использовать внешний источник данных». Затем выбрать подходящий источник из набора существующих или создать новый. Перед созданием нового источника необходимо установить соответствующий провайдер на локальный компьютер. Доступ к данным из системы MS Office либо уже встроен в операционную систему, либо приобретается отдельно с пакетом Microsoft Data Access Components (MDAC).
Ситуация: как упорядочить временные периоды в отчете Excel
В отчете о план-факт отклонениях, например, типа модели «Светофор», и исходной (сводной) таблице обозначения временных периодов могут различаться.
В сводной таблице кварталы могут быть представлены кодами — 1Q, 2Q, 3Q и 4Q. Именно на них будет ссылаться формула: ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ().
А пользователь в своей форме отчета должен видеть полный формат:
Чтобы этого добиться, в отчетной форме необходимо детализировать временные периоды в двух столбцах: в одном отражать их в виде кодов, соответствующих сводной таблице, и ссылаться на них в расчетах, в другом предоставить полное наименование кварталов. Далее столбец с кодами можно скрыть, и тогда пользователю будет доступна информация в удобном для него формате, а расчеты будут выполняться корректно.
MBA для финдира: блажь или необходимость
Редакция журнала «Финансовый директор» провела честный опрос на тему «Дает ли преимущества финдиру бизнес-образование?». Мы расспросили ваших коллег и выяснили, как помогла им MBA-подготовка в карьере.