Как построить график в Excel по данным таблицы
При работе в Excel табличных данных часто не достаточно для наглядности информации. Для повышения информативности ваших данных советуем использовать графики и диаграммы в Эксель. В этой статье рассмотрим пример того, как построить график в Excel по данным таблицы.
Отправьте файл с с примером графика, построенного по данным таблицы себе на почту ��
Какой-то текст ошибки
Какой-то текст ошибки
Спасибо, файл успешно отправлен на указанный адрес почты. Проверьте папки «Рассылки», «Реклама» или «Спам».
Одно или несколько полей содержат ошибку. Пожалуйста проверьте и попробуйте снова.
При отправке запроса произошла ошибка. Пожалуйста, попробуйте позже.
Видеоурок
Как построить простой график в Excel
Представим, что у нас есть таблица с ежемесячными данными среднего курса Доллара в течении года:
На основе этих данных нам нужно нарисовать график. Для этого нам потребуется:
- Выделить данные таблицы, включая даты и курсы валют левой клавишей мыши:
- На панели инструментов перейти во вкладку «Вставка» и в разделе «Диаграммы» выбрать «График»:
- Во всплывающем окне выбрать подходящий стиль графика. В нашем случае, мы выбираем график с маркерами:
- Система построила нам график:
Как построить график в Excel на основе данных таблицы с двумя осями
Представим, что у нас есть данные не только курса Доллара, но и Евро, которые мы хотим уместить на одном графике:
Для добавления данных курса Евро на наш график необходимо сделать следующее:
- Выделить созданный нами график в Excel левой клавишей мыши и перейти на вкладку «Конструктор» на панели инструментов и нажать «Выбрать данные»:
- Изменить диапазон данных для созданного графика. Вы можете поменять значения в ручную или выделить область ячеек зажав левую клавишу мыши:
- Готово. График для курсов валют Евро и Доллара построен:
Если вы хотите отразить данные графика в разных форматах по двум осям X и Y, то для этого нужно:
- Перейти в раздел «Конструктор» на панели инструментов и выбрать пункт «Изменить тип диаграммы»:
- Перейти в раздел «Комбинированная» и для каждой оси в разделе «Тип диаграммы» выбрать подходящий тип отображения данных:
- Нажать «ОК»
Ниже мы рассмотрим как улучшить информативность полученных графиков.
Больше лайфхаков в нашем Telegram Подписаться
Как добавить название в график Эксель
На примерах выше мы строили графики курсов Доллара и Евро, без заголовка сложно понять про что он и к чему относится. Чтобы решить эту проблему нам нужно:
- Нажать на графике левой клавишей мыши;
- Нажать на «зеленый крестик» в правом верхнем углу графика;
- Во всплывающем окне поставить галочку напротив пункта «Название диаграммы»:
- Над графиком появится поле с названием графика. Кликните по нему левой клавишей мыши и внесите свое название:
Как подписать оси в графике Excel
Для лучше информативности нашего графика в Excel есть возможность подписать оси. Для этого:
- Щелкните левой клавишей мыши по графику. В правом верхнем углу графика появится «зеленый крестик», нажав на который раскроются настройки элементов диаграммы:
- Щелкните левой клавишей мыши на пункте «Названия осей». На графике под каждой осью появятся заголовки, в которые вы можете внести свой текст:
Как добавить подписи данных на графике Excel
Ваш график может стать еще более информативным с помощью подписи отображаемых данных.
На примере курсов валют мы хотим отобразить на графике стоимость курса Доллара и Евро помесячно. Для этого нам потребуется:
- Щелкнуть правой кнопкой мыши по линии графика, на которую хотим добавить данные. В раскрывающемся меню выбрать пункт «Добавить подписи данных»:
Система отобразила курс Доллара на линии графика, но наглядность данных это не улучшило, так как значения сливаются с графиком. Для настройки отображения подписи данных потребуется сделать следующие шаги:
- Кликните правой кнопкой мыши на любом значении линии графика. В всплывающем окне выберите пункт «Формат подписей данных»:
В этом меню вы сможете настроить положение подписи, а также из чего будет состоять подпись данных: из имени ряда, категории, значения и.т.д.
Помимо настроек расположения, в том же меню вы сможете настроить размер подписей, эффекты, заливку и.т.д:
Настроив все параметры у нас получился такой график курса валют:
Как в excel построить график с двумя осями x и y
Для этой функции сделаем таблицу с двумя колонками. В первой колонки будут находится произвольные значения X от 1 до 20, в данном случае шаг у нас единица. Если функция сложная, то шаг лучше взять меньше. Чем меньше шаг, тем точность построения графика выше. Во второй колонки – расчёт значений Y в зависимости от значения x.
Формула для определения Y первой ячейки C4 в нашем случае будет иметь вид:
=2*B4+7
Для остальных ячеек формула аналогичная. Переходим на вкладку Вставка -> Точечная (можно выбрать точечная с гладкими кривыми и маркерами)
Появится белая прямоугольная область, кликаем на неё правым указателем мыши и из пункта меню выбираем Выбрать данные, появляется окошко Выбора источника данных и выбираем весь диапазон данных в ячейках.
В итоги получается вот такой точечный график.
Чтобы добавить линию, необходимо два раза быстро кликнуть на точку графика. Справа появится окошко Формат ряда данных -> Заливка и границы. Здесь можно настроить наш график, укать тип линии (в нашем случае сплошная), цвет, штрихи и т.д.
Таблица исходных данных для построения линейной функции в Excel.
| x | y=2x+7 |
| 1 | 9 |
| 2 | 11 |
| 3 | 13 |
| 4 | 15 |
| 5 | 17 |
| 6 | 19 |
| 7 | 21 |
| 8 | 23 |
| 9 | 25 |
| 10 | 27 |
| 11 | 29 |
| 12 | 31 |
| 13 | 33 |
| 14 | 35 |
| 15 | 37 |
| 16 | 39 |
| 17 | 41 |
| 18 | 43 |
| 19 | 45 |
| 20 | 47 |
Пример построение графика для разного шага по оси Х.
Возьмём данные для первого столбца c разным шагом (ось X — первый столбец)
Далее в Excell переходим на вкладку Вставка -> Точечная с прямыми отрезками и маркерами
Далее появляется график, правой клавишей мыши нажимаем на него и в меню выбираем Выбрать данные
Далее выбираем Диапазон данных для диаграммы и жмём Ок
Получаем график c разным шагом в Excel.
43993
2 комментария
Злобный Пёс :
Дурацкий пример. В первом столбце числа идут с одинаковым шагом. Поэтому автор и не видит своего косяка. И вот такие статьи «ни о чём» и засирают поисковую выдачу, когда нужно быстро найти решение возникшей задачи.
Как построить график (диаграмму) с двумя осями «Y» в программе «Excel».
Очень часто при проведении аналитики различных показателей для наглядности нужно уместить на одном графике или диаграмме показатели, выраженные в единицах и показатели в долях или процентах (%).
Например: существуют данные о часовом потреблении электроэнергии двух различных электрических печей. Эти показатели выражаются в тысячах ватт.
Необходимо отобразить на одном графике (диаграмме) потребление этих печей и экономичность одной из печей в процентах.
| Время | Печь №1, Вт | Печь №2 Вт | Экономия в % |
| 0:00 | 5600 | 3800 | 32% |
| 1:00 | 5600 | 3920 | 30% |
| 2:00 | 5400 | 3789 | 30% |
| 3:00 | 5421 | 3724 | 31% |
| 4:00 | 5203 | 3800 | 27% |
| 5:00 | 5400 | 3920 | 27% |
| 6:00 | 5560 | 3789 | 32% |
| 7:00 | 5523 | 3800 | 31% |
| 8:00 | 4950 | 3920 | 21% |
| 9:00 | 5525 | 3789 | 31% |
| 10:00 | 5123 | 3724 | 27% |
| 11:00 | 5842 | 3800 | 35% |
| 12:00 | 5422 | 3920 | 28% |
| 13:00 | 5523 | 3789 | 31% |
| 14:00 | 4950 | 3800 | 23% |
| 15:00 | 5525 | 3789 | 31% |
| 16:00 | 5123 | 3800 | 26% |
| 17:00 | 5842 | 3789 | 35% |
| 18:00 | 5560 | 3800 | 32% |
| 19:00 | 5523 | 3654 | 34% |
| 20:00 | 4950 | 3245 | 34% |
| 21:00 | 5525 | 3658 | 34% |
| 22:00 | 5123 | 3952 | 23% |
| 23:00 | 5525 | 3698 | 33% |
Если мы отобразим на одной диаграмме и потребление в ваттах и процент экономии, получится неинформативная картина — проценты будут теряется на фоне остальных показателей.
Что же сделать, чтобы проценты на графике отображались в другом масштабе?
Необходимо построить две оси в графике (вставить дополнительную вертикальную ось «Y»).
Рассмотрим, как создается диаграмма с двумя вертикальными осями в «Excel».
Шаг 1. Для начала следует построить обычную диаграмму или график.
Шаг 2. Кликнуть левой кнопкой мыши по нужной колонке (в нашем случае это колонка с % процентами).
Хитрость: если колонка с процентами очень мала (неразличима на графике) и Вы не можете по ней попасть курсором, выделите соседнюю колонку (столбик) и при помощи кнопок навигации на клавиатуре переставьте выделение на невидимый столбик.
Шаг 3. Кликните правой кнопкой мыши по выделению и выберете в появившемся меню «Изменитьтип диаграммы для ряда»и поставьте флажек (галочку) напротив пункта «Вспомогательная ось» (как показано на рисунке).
Теперь справа появилась дополнительная (вторая) ось «игрек» с шагом пропорциональным показателям процентов.
Чтобы проценты выделялись и визуально отличались от потребления, можно показатели потребления отобразить в виде колонок диаграммы, а проценты в виде линии графика.
quantviews
Поэтому специально решил написать про то, как все же построить такой график именно в Excel, и заодно поделиться другими тонкостями, которые накопились за 10 лет работы с этой программой.
1. Как построить график с двумя осями Y в Excel?
По всей видимости, корни недоразумений кроются в том, что в разных версиях Excel это может происходить по разному. Поэтому пользователи, которые все еще переходят с Excel 2003 могут столкнуться с тем, что в новых версиях (2007 и позднее), известная методика не работает. На самом деле все просто — нужно сначала построить необходимый график с одной осью Y. А потом уже задать вторую ось, она называется «вспомогательная».
К примеру, имеется такой набор данных (это объемы экспорта нефти из России):
| Год | Объем экспорта сырой нефти, млн т | Средняя экспортная цена, $/баррель |
| 2000 | 144.4 | 23.9 |
| 2001 | 164.5 | 20.8 |
| 2002 | 189.5 | 21.0 |
| 2003 | 228.0 | 23.8 |
| 2004 | 260.3 | 31.0 |
| 2005 | 252.5 | 45.2 |
| 2006 | 248.4 | 56.3 |
| 2007 | 258.6 | 64.3 |
| 2008 | 243.1 | 90.7 |
| 2009 | 247.5 | 55.6 |
| 2010 | 250.7 | 74.1 |
| 2011 | 244.5 | 101.7 |
| 2012 | 240.0 | 103.1 |
Логично построить график с двумя осями. Одна для — млн тонн, вторая — для цен.
Сначала строим просто график с линиями:
Щелкаем мышкой на нижний ряд. Появляется окно «Формат ряда данных». Во вкладке параметры ряда выбираем «По вспомогательной оси» и получаем итоговый результат (с некоторыми дополнительными манипуляциями, о которых речь пойдет ниже).
2. Используйте собственные шаблоны графиков в Excel
Нет ничего страшнее экселевских графиков, которые создаются «по умолчанию». Никогда их не используйте, если хотите, чтобы ваши результаты воспринимались серьезно. Честно. Вот, как к примеру, выглядит график по умолчанию с другим набором данных (это цены на нефть разных сортов).
Для рабочего использования — посмотреть на данные подойдет, но для использования в отчетах/презентациях — нет.
В Excel есть отличный инструмент отказаться от стандартных настроек построения графиков — собственные шаблоны. Пользоваться ими очень просто. Создайте типовой график — выставьте необходимые шрифты, цвета линий и заполнения, оформите оси и прочие параметры. Обратите внимание, что эти параметры не должны являться уникальными для этого конкретного графика, а подходить ко всему классу графиков подобного вида. Еще обратите внимание, что можно выставить типовые размеры для графика (к примеру 8 х 6 см). Потом идете в меню Конструктор — Сохранить как шаблон. Появляется новое окно «Сохранение шаблона диаграмма».
И сохраняете свой шаблон в формате .crtx Как видно по рисунку, у меня сохранено около 10 шаблонов — на разные варианты оформления, тип графиков и используемую цветовую палитру.
Теперь, чтобы создать график на новых данных с тем же оформлением необходимо сделать лишь одно дополнительное движение: Вставка — маленькая стрелка в нижем правом углу меню диалогового меню «Диаграммы». Появляется окно «Вставка диаграммы», которое выглядит вот так:
Самая верхняя вкладка — «Шаблоны» — показывает как раз сохраненные вами шаблоны. Выбираете нужный и строите график. Обратите внимание на нижнюю кнопку «Управлением шаблонами». Если нажать на нее откроется новое окно Проводника с папкой, содержащей сохраненные шаблоны в виде отдельных файлов. Вы можете их скопировать, чтобы потом вставить в аналогичную папку на другом компьютере (к примеру, ноутбук/домашний компьютер или поделиться с коллегами с тем, чтобы вы использовали идентичные шаблоны). Тогда на всех ваших компьютерах будут предлагаться одинаковые к использованию шаблоны графиков. Вуаля!
3. Используйте нестандартные палитры цветов.
Стандартная палитра цветов, которая используется в Excel по умолчанию всем хорошо известна и порядком надоела. Создайте свою палитру (или несколько палитр — для разных типов данных) или позаимствуйте. Если в вашей компании существует корпоративная палитра цветов, вы можете внести ее коды RGB и удобно использовать ее для построения графиков. Вам не придется каждый раз менять цвета для каждого отдельного графика.
Заходим в раздел «Другие цвета» — вкладки «Цвет линии» (или другой аналогичной вкладки), забиваем свои коды по RGB или HSL, сохраняем как шаблон.
Вот еще один полезный бесплатный ресурс, с помощью которого можно «позаимствовать» палитры цветов. Изначально эти палитры создавались для карт, но ничто не мешает их использовать и для обычных графиков.
Еще один рецепт — если вы увидели понравившуюся вам палитру цветов в Сети, вы легко можете посмотреть точные коды цветов, которые в ней используются. Для этого подойдет специальная надстройка для браузера. Я пользуюсь Eye Dropper для Chrome.
4. Обязательно убирайте лишние знаки после запятой
При создании графика Excel часто оставляет лишние знаки после запятой (к примеру, повторяющиеся нули). Эти знаки не несут никакой смысловой нагрузки и только «засоряют» пространство графика лишней информацией.
Поэтому в обязательном порядке убирайте лишние знаки после запятой. Делается это очень просто. Заходите в «Формат оси» — вкладка «Число». Выбираете нужный числовой формат и правильно число десятичных знаков (в данном случае это 0). Это относится и к единицам измерениям — переводите тонны в миллионы тонн, если у вас шестизначные цифры. В той же вкладке «Формат оси» — «Параметры оси», если удобная опция «Цена деления». Можно изменить размерность единицы измерения, не меняя исходные данные на рабочем листе.
5. Корректируйте максимальное/минимальное значение по оси при необходимости
Excel пытается по умолчанию автоматически определить оптимальное максимальное и минимальное значение по оси. Часто у него получается вполне нормально, но иногда он ошибается. Если такое произошло, скорректируйте эти значения вручную.
К примеру, для графика с ценами на нефть имеет смысл увеличить минимальное значение. Excel всегда начинает его от 0 в подобных случаях. В данном случае из-за этого появляется пустое пространство и изменения цен становятся мало различимыми.
Формат оси — вкладка «Параметры оси» — меняем минимальное значение с «авто» на фиксированное = 60.
Изменения стали более выраженными. Хотя верхние три линии все равно сильно сливаются, возможно, надо уменьшить толщину линий или убрать одну из них.
6. Не засоряйте ось X на временных рядах
В экономике и финансах один из наиболее часто встречающихся типов графиков — временные ряды. По оси Х — время, по оси Y — какое-то значение. Можно по разному представлять ось времени, выбирайте то представление, которое наилучшим образом отражает ваши цели.
К примеру, можно нарисовать вот вот так:
или так (для подобной двойной оси, нужно выбрать две строки/столбца в качестве подписи по горизонтальной оси).
7. Вставляйте графики Excel в векторном виде в Word/Powerpoint
Зачастую графики Excel создаются для того, чтобы использовать их в других программах — документах Word, либо презентациях PowerPoint. В большинстве случаев наиболее оптимальный вариант вставки — векторный формат emf или wmf.
По умолчанию графики вставляются как объекты Office (первая строка в окне наверху). Это приводит к тому, что их можно бы потом редактировать в Word или Powerpoint. Но в результате, увеличиваются размеры файлов (при больших файлах Excel), а также то, что весь ваш рабочий лист становится виден читателям Word/Powerpoint (возможно, это не тот результат, который вы желаете получить). Если использовать вставку в растровом формате (gif/png/jpeg), сильно ухудшается качество графики. Это особенно заметно при печати на бумаге — графики становятся «смазанными». Поэтому удобным является формат EMF/WMF. Он векторный, поэтому качество графики не уменьшается при изменении масштабов, а файлы занимают не очень много места. Единственная проблема в том, что его поддерживают в основном только программное обеспечение Microsoft. К сожалению, даже новейший MS Office 2013 не поддерживает альтернативные векторные форматы — тот же SVG.
8. Подгоняйте правильные размеры графиков в Excel
Удобство иногда идет не на пользу. После вставки в Word размеры графиков можно поменять с помощью мыши. Вроде бы удобно, но в результате могут получиться вот такие вот графики:
Смотрятся они некрасиво. Поэтому лучше выставить «правильные» размеры графиков непосредственно в Excel. К примеру, в Word вы можете точно оценить необходимую ширину графика (с учетом всех отступов) и задать эту ширину сразу в Excel. При вставке в Word график будет точно подогнан по ширине.
Вот и все, что я вспомнил. В Excel можно делать очень многое, в том числе и с точки зрения графических возможностей. Ограничения стандартных представлений можно расширить с помощью собственных макросов VBA или отдельных хаков. К примеру, вот хак как строить waterfall charts в Excel.
Но имейте в виду следующее. Если вы уже достигли хорошего уровня в Excel — не имеет смысл на нем зацикливаться. Есть и другие пути. Графические возможности того же R на порядок выше того, что может дать Excel, и, как правило, требуют меньше мороки.