График продаж в excel

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

Кликните по кнопке ниже и мы пришлем Вам Excel файл с примером графика, построенного по данным таблицы на E-mail:

Видеоурок

Как построить простой график в Excel

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

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

• Выделить данные таблицы, включая даты и курсы валют левой клавишей мыши:

• На панели инструментов перейти во вкладку “Вставка” и в разделе “Диаграммы” выбрать “График”:

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

• Система построила нам график:

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

Представим, что у нас есть данные не только курса Доллара, но и Евро, которые мы хотим уместить на одном графике:

Для добавления данных курса Евро на наш график необходимо сделать следующее:

• Выделить созданный нами график в Excel левой клавишей мыши и перейти на вкладку “Конструктор” на панели инструментов и нажать “Выбрать данные”:

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

• Готово. График для курсов валют Евро и Доллара построен:

Если вы хотите отразить данные графика в разных форматах по двум осям X и Y, то для этого нужно:

• Перейти в раздел “Конструктор” на панели инструментов и выбрать пункт “Изменить тип диаграммы”:

• Перейти в раздел “Комбинированная” и для каждой оси в разделе “Тип диаграммы” выбрать подходящий тип отображения данных:

• Нажать “ОК”

Ниже мы рассмотрим как улучшить информативность полученных графиков.

Как добавить название в график Эксель

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

• Нажать на графике левой клавишей мыши;
• Нажать на “зеленый крестик” в правом верхнем углу графика;
• Во всплывающем окне поставить галочку напротив пункта “Название диаграммы”:

• Над графиком появится поле с названием графика. Кликните по нему левой клавишей мыши и внесите свое название:

Как подписать оси в графике Excel

Для лучше информативности нашего графика в Excel есть возможность подписать оси. Для этого:

• Щелкните левой клавишей мыши по графику. В правом верхнем углу графика появится “зеленый крестик”, нажав на который раскроются настройки элементов диаграммы:

• Щелкните левой клавишей мыши на пункте “Названия осей”. На графике под каждой осью появятся заголовки, в которые вы можете внести свой текст:

Как добавить подписи данных на графике Excel

Ваш график может стать еще более информативным с помощью подписи отображаемых данных.

На примере курсов валют мы хотим отобразить на графике стоимость курса Доллара и Евро помесячно. Для этого нам потребуется:

• Щелкнуть правой кнопкой мыши по линии графика, на которую хотим добавить данные. В раскрывающемся меню выбрать пункт “Добавить подписи данных”:

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

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

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

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

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

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

К сожалению, такой настройки детальной осуществить пока нельзя.

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

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

Если у Вас есть данные, которые изменяются во времени, то лучше использовать обычный «График» . Для того чтобы построить график функции в Excel – используйте «Точечную диаграмму» . Она позволит отобразить зависимость одного значения от другого.

В этой статье мы рассмотрим, как построить простой график изменений в Экселе.

График будем строить на примере следующей таблицы. У нас есть данные про сотрудников одной из компаний – кто сколько продал единиц товара за определенный месяц.

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

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

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

Дальше выделяем мышкой имена сотрудников и нажимаем «ОК» в этом окне и в предыдущем.

Теперь понятно, какой сотрудник сколько продал единиц товара за Январь.

Добавим на график, количество проданных единиц товара и за остальные месяцы. В окне «Выбор источника данных» смотрим в раздел «Элементы легенды» и кликаем на кнопочку «Добавить» .

Ставим курсор в поле «Имя ряда» и выделяем в таблице месяц – Февраль, переходим в поле «Значения» и выделяем количество проданных товаров за Февраль. Изменения можно сразу увидеть и на графике: теперь в окне два графика и новое поле в легенде. Нажмите «ОК» .

Проделайте те же действия и для остальных месяцев.

Чтобы изменить надпись в легенде вместо «Ряд 1» на «Январь» , в окне «Выбор источника данных» в разделе «Элементы легенды» выделяем поле «Ряд 1» и кликаем «Изменить» .

В следующем окне в поле «Имя ряда» ставим курсор и выделяем на листе мышкой ячейку «Январь» . Нажмите «ОК» .

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

При выделении графика на ленте появляются новые вкладки для работы с диаграммами: «Конструктор» , «Макет» и «Формат» .

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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 (также, как и с предыдущей фигурой):

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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