Включение надстройки; Анализ данных; в Excel

Включение надстройки “Анализ данных” в Excel

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

Активация пакета инструментов
Применение функции “Анализ данных”
Заключение

Активация пакета инструментов

“Анализ данных” размещен в блоке инструментов “Пакет анализа”. Для его включения исполняем последующие деяния (начиная с редакции программки 2010 года шаги фактически схожи):

Открываем меню “Файл”.
В списке слева щелкаем по разделу “Характеристики” в самом низу.
На дисплее отобразится окно для опции программки. Тут избираем пункт “Надстройки”. Потом в правой части окна отыскиваем параметр “Управление”, в выпадающем перечне избираем для него вариант – “Надстройки Excel”, опосля что нажимаем клавишу “Перейти”.

Применение функции “Анализ данных”

Итак, мы разглядели процесс активации функции “Анализ данных”. Давайте сейчас поглядим, как ее отыскать и применить.

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

Заключение

Невзирая на то, что функция “Анализ данных” не доступна по дефлоту в программке Excel, процесс ее включения, по мере необходимости, не займет много времени и не просит определенных познаний, в отличие от умения ею воспользоваться.

Анализ данных Excel — Визуализация данных

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

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

Визуализация данных при помощи диаграмм

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

Обратитесь к учебным таблицам Excel для получения доборной инфы о типах диаграмм.

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

Создание комбинационных диаграмм

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

Мы сделаем кластеризованную диаграмму столбцов для этих результатов.

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

Наилучший метод различения 2-ух типов данных для сопоставления значений — внедрение комбинированных диаграмм. В Excel 2013 и вышеприведенных версиях вы сможете употреблять комбинированные диаграммы для той же цели.

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

Перейдите на вкладку «ДИЗАЙН» под вкладкой «ИНСТРУМЕНТЫ ДИАГРАММ» на ленте.
Нажмите Поменять тип диаграммы в группе Тип. Раскроется диалоговое окно «Поменять тип диаграммы».

Измените Тип диаграммы для серии Фактическая на Линия с маркерами. Подготовительный просмотр отображается в разделе «Пользовательская композиция».

Ваша персональная комбинационная таблица будет отображена.

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

Но этот тип представления не работает, когда спектры данных 2-ух ваших значений данных существенно различаются.

Создание комбинированной диаграммы со вторичной осью

Представим, у вас есть данные о количестве отгруженных единиц вашего продукта и о фактической прибыли за денежный год 2015-2016, которую вы получили из различных регионов.

Если вы используете ту же комбинацию, что и ранее, вы получите последующее —

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

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

Нажмите вкладку INSERT.
Нажмите Combo в группе диаграмм.
Нажмите Create Custom Combo Chart из выпадающего перечня.

Покажется диалоговое окно «Вставка диаграммы» с выделенным перечнем.

Для Типа диаграммы изберите —

Линия с маркерами для серии № единиц

Кластерный столбец для фактической прибыли серии

Установите флаг «Вторичная ось» справа от номера серии и нажмите «ОК».

Подготовительный просмотр вашей диаграммы покажется под Custom Combination.

Ваша комбо-диаграмма отображается с Secondary Axis.

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

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

Различающие серии и оси категорий

Представим, вы желаете спроектировать фактическую прибыль, полученную в 2013-2016 годах.

Сделайте кластеризованный столбец для этих данных.

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

Удалить заголовок года в спектре данных.

Сейчас год считается группой, а не серией. Ваша диаграмма смотрится последующим образом —

Элементы диаграммы и стили диаграммы

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

Нажмите на диаграмму

Три клавиши возникают рядом с правым верхним углом графика —

Элементы диаграммы
Стили диаграммы
Фильтры диаграммы

Для подробного разъяснения этого обратитесь к учебнику Excel Charts.

Нажмите Элементы диаграммы.
Нажмите Метки данных.

Нажмите Стили диаграммы
Изберите стиль и цвет, которые соответствуют вашим данным.

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

Метки данных

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

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

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

Резвый макет

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

Нажмите на график.
Перейдите на вкладку «ДИЗАЙН» под «ИНСТРУМЕНТЫ»
Нажмите Резвый макет.

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

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

Внедрение изображений в столбчатых диаграммах

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

Нажмите на столбец на диаграмме столбца.

В Серии данных формата нажмите Заполнить.

В разделе «Вставить картину из» укажите название файла либо, если нужно, буфер обмена, если вы ранее скопировали изображение.

Нажмите на столбец на диаграмме столбца.

В Серии данных формата нажмите Заполнить.

Избранная вами картина покажется заместо столбцов на диаграмме.

Диаграмма группы

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

Представим, ваши результаты опроса клиентов из восточных и западных регионов, по месяцам, —

Шаблон для анализа продаж и прибыли в компании

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

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

Вводные моменты по анализу продаж

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

Показатель	Комменты
Реализации в штуках и рублях	Сбор статистики продаж в штуках и рублях лучше вести раздельно по каждой товарной позиции на каждомесячной базе. Данная статистика дозволяет отыскать отправную точку понижения / роста продаж и стремительно найти причину такового конфигурации. Также таковая статистика дозволяет выслеживать изменение средней цены отгрузки продукта при наличии разных призов либо скидок партнерам.
Себестоимость единицы продукции	Себестоимость продукта является принципиальным нюансом хоть какого анализа продаж. Зная уровень себестоимости продукта, для вас проще будет разрабатывать трейд-маркетинговые акции и управлять ценообразованием в компании. На базе себестоимости можно высчитать среднюю рентабельность продукта и найти более прибыльные с точки прибыли позиции для стимулирования продаж. Статистику по себестоимости можно вести на каждомесячной базе, но если нет таковой способности, то лучше выслеживать квартальную динамику данного показателя.
Реализации по фронтам сбыта либо регионам продаж	Если ваша компания работает с различными регионами / городками либо имеет несколько подразделений в отделе продаж, то целенаправлено вести статистику продаж по данным регионам и фронтам. При наличии таковой статистики вы можете осознавать, за счет каких направлений в первую очередь обеспечен рост / падение продаж и резвее узнать предпосылки отклонений. Реализации по фронтам отслеживаются на каждомесячной базе.
Дистрибуция продукта	Дистрибуция продукта впрямую связана с ростом либо понижением продаж. Если у компании есть возможность мониторинга присутствия продукта в РТ, то лучше такую статистику собирать минимум 1 раз в квартал. Зная количество точек, в которых конкретно представлена отгружаемая позиция, вы сможете высчитать показатель оборачиваемости продукта в розничной точке (реализации / кол-во РТ) и осознать реальный уровень спроса на продукцию компании. Дистрибуцию можно надзирать на каждомесячной базе, но удобнее всего проводить квартальный мониторинг данного показателя.
Количество клиентов	Если компания работает c дилерским звеном либо на B2B рынке, целенаправлено выслеживать статистику по количеству клиентов. В таком случае вы можете оценить свойство роста продаж. К примеру, источником роста продаж является повышение спроса на продукт либо просто географическая экспансия на рынке.

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

Динамика продаж по товарам и фронтам, составляющим 80% продаж компании
Динамика продаж и прибыли по отношению к аналогичному периоду прошедшего года
Изменение цены, себестоимости и рентабельности продаж по отдельным позициям, группам продуктов
Свойство роста: динамика продаж в расчете на 1 РТ, в расчете на 1 клиента

Сбор статистики по продажам и прибыли

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

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

Рис.1 Пример сбора статистики продаж по товарным позициям

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

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

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

Рис.2 Пример сбора статистики продаж по фронтам и регионам продаж

Процесс анализа продаж

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

Анализ выполнения плана продаж

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

Рис.3 Пример анализа выполнения плана продаж по товарным группам

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

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

Анализ динамики продаж по фронтам

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

Рис.4 Пример анализа продаж по фронтам

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

Анализ структуры продаж

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

Рис.5 Пример анализа структуры продаж ассортимента компании

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

Рис.6 Пример анализа себестоимости и рентабельности продаж

АВС анализ

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

Рис.7 Пример АВС анализа ассортимента

АВС анализ проводится в разрезе продаж и прибыли 1 раз в квартал.

Контроль остатков

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

Рис.8 Пример анализа остатков продукции

Отчет по продажам

Нередко в компаниях отел маркетинга отчитывается за выполнение планов по продажам. Для еженедельного отчета довольно выслеживать уровень выполнения плана продаж накопительным итогом и указывать прогноз выполнения плана продаж по текущему уровню отгрузок. Таковой отчет дозволяет вовремя найти опасности невыполнения плана продаж и создать корректирующие меры.

Рис.9 Еженедельный отчет о продажах

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

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