Как сделать таблицу в excel для графика в было стало

Точечная диаграмма со стрелками "было-стало"

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

Точечная диаграмма со стрелками

Само-собой, это подходит не только для товаров и затрат-прибыли. С ходу можно придумать много сценариев, где этот тип диаграммы будет "в тему", например:

  • Изменение уровня доходов (X) и продолжительности жизни (Y) для разных стран за последние два года.
  • Изменение количества клиентов (X) и среднего чека (Y) заказа ресторанов
  • Соотношение стоимости компании (X) и количества сотрудников в ней (Y)
  • .

Если что-то похожее встречается и в вашей практике, то имеет смысл разобраться, как такую красоту построить.

Про пузырьковые диаграммы (даже анимированные) я уже неоднократно писал. Точечная диаграмма (XY Scatter Chart) — это частный случай пузырьковой (Bubble Chart), но без третьего параметра — размера пузырьков. Т.е. каждая точка на графике описывается всего двумя параметрами: X и Y. Таким образом, построение начинается с подготовки исходных данных в виде двух таблиц:

Исходные данные

Сначала построим то, что "было". Для этого выделим диапазон A3:C8 и выберем на вкладке Вставка (Insert) команду Рекомендуемые диаграммы (Recommended Charts) , а затем перейдем на вкладку Все диаграммы (All charts) и выберем тип Точечная (XY Scatter Chart) :

Строим точечную диаграмму

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

Теперь добавим на неё данные из второй таблицы "Стало". Проще всего это сделать копированием. Для этого выделим диапазон E3:F8, скопируем его и, выделив диаграмму, выполним в неё специальную вставку через Главная — Вставить — Специальная вставка (Home — Paste — Paste Special) :

Специальная вставка

В появившемся окне выберем соответствующие опции вставки:

Специальная вставка в диаграмму

После нажатия на ОК на нашей диаграмме появится второй комплект точек ("стало"):

Два комплекта точек на диаграмме

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

Таблица для стрелок

Обратите внимание как она устроена:

  • строки из исходных таблиц попарно чередуются, фиксируя начало и конец каждой стрелки
  • каждая пара отделена от других пустой строкой, чтобы на выходе получились отдельные стрелки, а не одна большая
  • если данные могут изменяться в будущем, то имеет смысл использовать не числа, а ссылки на исходные таблицы, т.е. в ячейке H4 ввести формулу =B4, в ячейку H5 ввести формулу =E4 и т.д.

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

Добавляем ряд для стрелок

После нажатия на ОК на диаграмме появятся новые точки начала и конца каждой стрелки (у меня они серого цвета), закрывающие уже построенные синие и оранжевые. Щелкнем по ним правой кнопкой мыши и выберем команду Изменить тип диаграммы для ряда (Change Series Chart Type) . В открывшемся окне для исходных рядов "было" и "стало" оставим тип Точечная, а для ряда "стрелок" зададим Точечная с прямыми отрезками:

Настраиваем тип

После нажатия на ОК наши точки "было" и "стало" будут соединены прямыми отрезками. Останется щелкнуть по ним правой кнопкой мыши и выбрать команду Формат ряда данных (Format Data Series) , а затем настроить параметры линий: толщину, тип стрелки и их размеры:

Настраиваем вид стрелок

Для наглядности ещё неплохо бы добавить названия товаров. Для этого:

  1. Щелкните по любой точке и выберите в контекстном меню Добавить подписи данных (Add Data Labels) — добавятся числовые подписи точек
  2. Щелкните по подписям правой и выберите команду Формат подписей (Format Labels)
  3. В открывшейся панели включите флажок Значения из ячеек (Values from cells) , нажмите кнопку Выбрать диапазон и выделите названия товаров (A4:A8).

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

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

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

  1. Видеоурок
  2. Как построить простой график в Excel
  3. Как построить график в Excel на основе данных таблицы с двумя осями
  4. Как добавить название в график Эксель
  5. Как подписать оси в графике Excel
  6. Как добавить подписи данных на графике Excel

Видеоурок

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

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

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

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

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

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

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

Построение графика в Excel

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

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

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

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

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

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

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

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

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

График в Excel

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

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

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

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

Как изменить тип диаграммы в Excel

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

Как изменить тип диаграммы в Excel

  • Нажать “ОК”

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

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

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

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

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

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

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

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

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

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

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

как задать название осям в графике excel

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

добавить название двум осям на графике excel

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

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

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

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

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

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

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

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

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

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

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

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

Ссылка на основную публикацию