No Image

Таблица продаж в excel примеры

СОДЕРЖАНИЕ
5 734 просмотров
10 марта 2020

За 10 минут я научу вас пользоваться сводными таблицами Excel

Кем бы вы ни были: начальником отдела продаж, маркетологом, аналитиком, руководителем компании т .д.

Если вы создаёте отчёты в Excel и не пользуетесь сводными таблицами. Вы поступаете плохо.

Сводные таблицы MS Excel это встроенный в Excel инструмент позволяющий обрабатывать и обобщать табличные данные.

Например
У вас есть огромная таблица с продажами за 10 лет в которой есть всего лишь 4 столбца:

  1. Дата сделки
  2. ФИО менеджера
  3. Тип клиента
  4. Сумма сделки

Благодаря сводным таблицам вы сможете за 2-3 минуты получить следующие обобщённые отчёты:

  1. Сумма продаж по каждому менеджеру
  2. Сумма продаж по типам клиентов
  3. Сумма продаж за каждый месяц
  4. Заработная плата менеджеров в зависимости от объёма продаж
  5. и т.д. и т.п.

Т.е. за 2-3 минуты вы можете переварить до миллиона строк и получить отчёт в нужном разрезе.

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

Дано
Таблица с итогами работы отдела продаж за 4 месяца 2017 года. Скачать

  1. Дата сделки
  2. ФИО менеджера
  3. Тип клиента
  4. Сумма сделок


Задача

Сформировать следующие отчёты.

  1. Сумма продаж по каждому менеджеру
  2. Сумма продаж по типам клиентов
  3. Заработная плата менеджеров в зависимости от объёма продаж

1. Откройте вашу таблицу в MS Excel.
2. Выделите всю таблицу находящуюся на листе Лист1. Нажав сочетание клавиш Ctr+A.
3. Выберите пункт Вставка, далее кнопку Сводная таблица. В появившемся диалоговом окне нажмите кнопку ОК.

В итоге появится новый лист на котором мы будем работать со сводными таблицами. При этом Лист1 будет являться источником данных для наших сводных таблиц.

А теперь сразу в бой. Попробуем создать нашу первую сводную таблицу.
Сумма продаж по каждому менеджеру.

1. Для этого перетащите Поле Менеджер в квадрат Строки, а поле Сумма в квадрат Значения.

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

2. Выделите столбец В, Сумма по полю Сумма и отформатируйте его как Денежный.
3. Нажмите правой кнопкой по любой сумме в столбце В и выберите пункт Сортировка – > Сортировка от Я до А.

Теперь наши менеджеры отсортированы по объёму продаж.

Усложним задачу. Добавим на этом же листе новую сводную таблицу.
Сумма продаж по типам клиентов.

1. Встаньте на любую ячейку сводной таблицы. Нажмите сочетание клавиш Ctr+A далее Ctr+C, так мы скопировали её в буфер обмена.

2. Встаньте в ячейку F3 и нажмите сочетание клавиш Ctr+V. Так мы создали копию сводной таблицы, которую будем переделывать.

3. Встаньте на любую ячейку второй сводной таблицы. И проделайте следующее.

Перетащите поле Тип клиента в квадрат Строки. А поле Менеджер из этого квадрата удалите, правой кнопкой.

В итоге мы получили вторую сводную таблицу. В этой таблице мы видим объём продаж по типам клиентов.

Доработаем нашу первую сводную таблицу. Для того чтобы она считала заработную плату менеджеров в зависимости от объёма продаж.

1. Для этого выделите любую ячейку первой сводной таблицы.

2. Выберите пункт Анализ -> Поля, Элементы и наборы -> Вычисляемое поле.

3. Заполним поля в диалоговом окне. В поле Имя введите _ЗП. В поле Формула введите вот такую простую формулу = Сумма* 0,035. И жмём ОК.

В видеоролике ниже я разобрал 2 примера создания сводных таблиц.

1. Анализ работы отдела продаж.
2. Анализ работы рекламных кампаний.

1. Представленный выше пример, показал лишь 1% от всех возможностей работы со сводными таблицами.

Читайте также:  Что значит ошибка при запуске приложения 0xc0000005

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

  • Прочитайте книгу https://www.ozon.ru/context/detail/id/139953683/ Возможно этого вам будет достаточно для освоения сводных таблиц.
  • Если книги было не достаточно и вы решили познать весь дзен сводных таблиц. Тогда пройдите курс Максима Увароваhttp://learn.needfordata.ru/excel После этого курса вы точно сможете сказать, что немного разбираетесь в Excel и в сводных таблицах в частности.

3. Любите Excel и да прибудут с вами красивые отчёты полные инсайтов.

Телефон: +79178888917 доступен пн – сб с 8-00 до 20-00

Адрес: г. Казань, ул. Космонавтов, д. 39А, офис 211

Для анализа выполнения плана продаж в Excel по сотрудникам фирмы рекомендуется использовать профессиональные средства визуализации данных.

Пример как сделать график выполнения плана продаж в Excel

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

  1. Установленный персональный план продаж в определенный месяц.
  2. Фактические продажи по состоянию на конец каждого месяца первого квартала оп каждому торговому представителю.

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

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

  1. Торговых агентов не выполнивших своих планов по продажам.
  2. Торговых с перевыполнением поставленных планов продаж.
  3. Рациональность постановки уровня плана продаж для каждого торгового агента.

Подготовка данных по статистическим показателям продаж

Статистика продаж за первый выгружена из ERP системы в файл Excel на листе «Данные» и выглядит следующим образом:

Как всегда перед созданием визуализации данных следует подготовить и обработать исходные показатели. Подготовку данных выполним прямо на этом же листе. Создайте дополнительный столбец с названием «Месяц» и его ячейки заполните формулой из двух функций для преобразования даты в название месяца в Excel:

Подготовка данных – закончена переходим к обработке. Создайте новый лист с названием «Обработка» и сделайте в нем таблицу как показано ниже на рисунке:

Сразу же забегая вперед создаем третий лист с названием «График» и создаем в нем презентационную таблицу, которая состоит из первых трех столбцов таблицы обработки. Только значение в ячейках презентационной таблицы – это внешние относительные ссылки на ячейки листа «Обработка» в диапазоне A2:B11:

На этом же листе в ячейке C12 создаем элемент управления будущим графиком в виде выпадающего списка. Для этого выберите инструмент: «ДАННЫЕ»-«Работа с данными»-«Проверка данных»:

В появившемся окне «Проверка вводимых значений» на вкладке «Параметры» из выпадающего списка «Тип данных:» выберите опцию «Список». Затем в поле ввода «Источник:» введите вручную значение из текстовой строки без пробелов: Январь;Февраль;Март. Названия месяцев в этой строке разделены только точкой с запятой (без пробелов).

Обработка данных по выполнению плана продаж для графика Excel

Теперь можно переходить к заполнению формулами таблицы на листе «Обработка». Перейдите на лист с названием «Обработка» и заполните в нем диапазон ячеек B2:C11 формулой выборки значений по нескольким условиям из листа «Данные» с исходными статистическими показателями продаж:

Как видно формула выборки в данном примере ссылается на все три листа. Кроме, того она использует все типы ссылок: внешние, внутренние, относительные, абсолютные и смешанные – будьте внимательны!

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

Читайте также:  Darkest dungeon новые герои

Для следующего ряда (красного) графика используем логическую формулу, которая выбирает только фактические значения не выполненных планов продаж или возвращает #Н/Д:

B2;C2-B2;НД())’ >

Также нам нужна обратно пропорциональная формула прядущей, чтобы отобрать только показатели перевыполнения плана для «зеленого» ряда:

C2;B2-C2;НД())’ >

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

Добавление числа +12 в этой формуле позволит нам немного приподнять шарики над столбиками гистограммы. Изменяя число +12 на любое другое, мы можем вручную настраивать положение шаров по оси Y на графике.

Создание графика выполнения планов продаж в Excel

Удерживая клавишу CTRL на клавиатуре выделите все столбцы таблицы на листе «Обработка», кроме одного – «Факт». После чего не снимая выделения выберите инструмент «ВСТАВКА»-«Диаграммы»-«Гистограмма с накоплением»:

Переходим к настройкам графика. В первую очередь убираем все лишнее кроме оси X. Для этого нажмите на кнопку плюс «+», справа от диаграммы и снимите галочки с опций выпадающего меню «ЭЛЕМЕНТЫ ДИАГРАММЫ» так как показано на рисунке:

Затем кликаем правой кнопкой мышки по любому ряду и из контекстного меню выбираем опцию «Формат ряда данных». Затем уменьшаем параметра ряда «Боковой зазор» до 40%.

Далее снова кликаем правой кнопкой мышки по любому ряду и из появившегося контекстного меню на этот раз выбираем опцию «Изменить тип диаграммы для ряда»:

В окне «Изменение типа диаграммы» изменяем типы только для первого и последнего ряда на «График с маркерами».

Нестандартное оформление гистограммы полезными фигурами

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

Выберите инструмент: «ВСТАВКА»-«Иллюстрации»-«Фигуры»-«Линия» и удерживая клавишу SHIFT на клавиатуре нарисуйте горизонтальный отрезок линии длинной 1,5 см:

Когда фигура «Линия» выделена, нам доступно ее дополнительное меню «СРЕДСТВА РИСОВАНИЯ»-«ФОРМАТ» где можно указать для нее длину 1,5см, черный цвет и толщину 1,5 пунктов.

Данная линия будет служить нам планкой на графике, установленной на уровне значений персональных планов продаж. Чтобы добавить линю на график сначала необходимо скопировать ее CTRL+C, а потом выделить нижний график с маркерами и вставить CTRL+V:

В результате в место маркеров на графике отображаются черные линии-планки. Кликаем правой кнопкой мышки по синим линиям нижнего графика скрываем его выбрав инструмент: «Формат ряда данных»-«ПАРАМЕТРЫ РЯДА»-«Заливка и границы»-«ЛИНИЯ»-«Нет линий».

Далее щелкаем правой кнопкой мышки по каждому ряду столбцов и задаем им градиентный цвет заливки: «Формат ряда данных»-«ПАРАМЕТРЫ РЯДА»-«Заливка и границы»-«ЗАЛИВКА»-«Градиентная заливка»:

Такие операции повторяем на всех 3-х рядах данных столбиков. Указываем только разные цвета для точек градиента:

  • факт график – темно-сизый с черным правый градиент;
  • меньше плана – красный с черным правый градиент;
  • больше плана – зеленый с черным правый градиент.

Пришло время добавить белые шары на график с показателями факта выполнения плана тяговыми. Для этого нам снова понадобится фигура – «Круг». Выберете ее через меню: «ВСТАВКА»-«Иллюстрации»-«Фигуры»-«Овал». Удерживая клавишу SHIFT на клавиатуре нарисуйте круг размером 1,3 x 1,3 см. После чего уберите контур и задайте ему градиентную радиальную заливку:

Радиальная градиентная заливка визуально придает кругу форму шара. В точках градиента можно использовать 2 цвета: одна точка с белым цветом и четыре точки с серым цветом – код RGB: 217, 217, 217.

Далее, как не сложно догадаться снова нужно скопировать фигуру CTRL+C выделить график с маркерами и вставить в график CTRL+V (также, как и с предыдущей фигурой):

Читайте также:  Драйвера для saitek x52

Как и предыдущий раз не забудьте скрыть синюю линию верхнего графика с маркерами.

Создание объемной 3D модели в Excel из двухмерной фигуры

Создаем последнюю фигуру для графика «Прямоугольник» выбрав его из: «ВСТАВКА»-«Иллюстрации»-«Фигуры»-«Прямоугольник». Нарисуйте черный прямоугольник с размером ширины немного шире графика, а высота 2,36 см. Но не рисуйте прямо в самом графике иначе фигуру нельзя будет сместить на задний план! Фигура должна быть немного шире графика:

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

Щелкните левой кнопкой мышки по черному прямоугольнику и выберите инструмент: «СРЕДСТВА РИСОВАНИЯ»-«ФОРМАТ»-«Стили фигур»-«Эффекты фигуры»-«Поворот объемной фигуры»-«Перспектива слабая»:

Здесь же придаем фигуре новый эффект: «Эффекты фигуры»-«Рельеф»-«Сглаживание»:

Теперь лепим объемную 3D модель из двухмерной модели дальше. Щелкните правой кнопкой мышки по фигуре из появившегося контекстного меню выберите опцию «Формат объекта». После вам будут доступны настройки фигуры, которые необходимо внести прямо сейчас. Сначала: «Формат фигуры»-«ПАРАМЕТРЫ ФИГУРЫ»-«Эффекты»-«Поворот объемной фигуры»-«Вращение вокруг оси Y» – 299 градусов. А потом: «Формат фигуры»-«ПАРАМЕТРЫ ФИГУРЫ»-«Эффекты»-«Формат объемной фигуры»-«Рельеф сверху»-«Высота» – 17 пунктов и здесь же «Глубина» – 12 пунктов. Все как показано ниже на рисунке:

А теперь щелкаем правой кнопкой мышки по 3D-фигуре и выбираем опцию «На задний план», чтобы она оказалась под графиком. Пока ее плохо видно, чтобы сделать график прозрачным делаем следующее. Правой кнопкой мышки щелкаем по пустой, белой области графика и из контекстного меню выбираем опцию: «Формат области диаграммы»-«ПАРАМЕТРЫ ДИАГРАММЫ»-«Заливка и Границы»-«ЗАЛИВКА»-«Нет заливки»:

График почти готов. Осталось его оформить подписями данных. Начнем с самых важных подписей верхнего ряда с шарами.

Добавление показателей выполнения планов продаж на график Excel

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

Вносим свои изменения в настройки подписей: «Формат подписей данных»-«ПАРАМЕТРЫ ПОДПИСЕЙ»-«Включать в подпись:»-«значения из ячеек»-«Выбрать диапазон» и указываем ссылку на ячейки столбца «Факт». Это тот столбец таблицы, который не был выбран в самом начале на этапе построения графика. Затем здесь же снимаем галочку на опции «значение». А в разделе опций «Положение метки» отмечаем пункт «В центре».

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

Далее таким же образом добавляем подписи на зеленый ряд. И на конец на вкладке «ГЛАВНАЯ» разделе инструментов «Шрифт» надстраиваем все шрифты чисел и текста на графике стандартными средствами: цвет, размер и т.п.:

Если есть желание добавить тень для объемной 3D фигуры снизу, следует выделить одним кликом фигуру и выбрать инструмент: «СРЕДСТВА РИСОВАНИЯ»-«ФОРМАТ»-«Стили фигур»-«Эффекты фигуры»-«Отражение»-«Полное отражение, смещение: 4пт.».

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

Комментировать
5 734 просмотров
Комментариев нет, будьте первым кто его оставит

Это интересно
Adblock
detector