Некоторое время назад с удивлением обнаружил, что довольно много посетителей с поисковых систем попадают сюда с запросами типа "построить график с двумя осями y excel". А там речь идет про R :)
Поэтому специально решил написать про то, как все же построить такой график именно в Excel, и заодно поделиться другими тонкостями, которые накопились за 10 лет работы с этой программой.
По умолчанию графики вставляются как объекты Office (первая строка в окне наверху). Это приводит к тому, что их можно бы потом редактировать в Word или Powerpoint. Но в результате, увеличиваются размеры файлов (при больших файлах Excel), а также то, что весь ваш рабочий лист становится виден читателям Word/Powerpoint (возможно, это не тот результат, который вы желаете получить). Если использовать вставку в растровом формате (gif/png/jpeg), сильно ухудшается качество графики. Это особенно заметно при печати на бумаге - графики становятся "смазанными". Поэтому удобным является формат EMF/WMF. Он векторный, поэтому качество графики не уменьшается при изменении масштабов, а файлы занимают не очень много места. Единственная проблема в том, что его поддерживают в основном только программное обеспечение Microsoft. К сожалению, даже новейший MS Office 2013 не поддерживает альтернативные векторные форматы - тот же SVG.
Вот и все, что я вспомнил. В Excel можно делать очень многое, в том числе и с точки зрения графических возможностей. Ограничения стандартных представлений можно расширить с помощью собственных макросов VBA или отдельных хаков. К примеру, вот хак как строить waterfall charts в Excel.
Но имейте в виду следующее. Если вы уже достигли хорошего уровня в Excel - не имеет смысл на нем зацикливаться. Есть и другие пути. Графические возможности того же R на порядок выше того, что может дать Excel, и, как правило, требуют меньше мороки.
Поэтому специально решил написать про то, как все же построить такой график именно в 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, сохраняем как шаблон.
Заходим в раздел "Другие цвета" - вкладки "Цвет линии" (или другой аналогичной вкладки), забиваем свои коды по RGB или HSL, сохраняем как шаблон.
Вот еще один полезный бесплатный ресурс, с помощью которого можно "позаимствовать" палитры цветов. Изначально эти палитры создавались для карт, но ничто не мешает их использовать и для обычных графиков.
Еще один рецепт - если вы увидели понравившуюся вам палитру цветов в Сети, вы легко можете посмотреть точные коды цветов, которые в ней используются. Для этого подойдет специальная надстройка для браузера. Я пользуюсь Eye Dropper для Chrome.
4. Обязательно убирайте лишние знаки после запятой
При создании графика Excel часто оставляет лишние знаки после запятой (к примеру, повторяющиеся нули). Эти знаки не несут никакой смысловой нагрузки и только "засоряют" пространство графика лишней информацией.
Поэтому в обязательном порядке убирайте лишние знаки после запятой. Делается это очень просто. Заходите в "Формат оси" - вкладка "Число". Выбираете нужный числовой формат и правильно число десятичных знаков (в данном случае это 0). Это относится и к единицам измерениям - переводите тонны в миллионы тонн, если у вас шестизначные цифры. В той же вкладке "Формат оси" - "Параметры оси", если удобная опция "Цена деления". Можно изменить размерность единицы измерения, не меняя исходные данные на рабочем листе.
Поэтому в обязательном порядке убирайте лишние знаки после запятой. Делается это очень просто. Заходите в "Формат оси" - вкладка "Число". Выбираете нужный числовой формат и правильно число десятичных знаков (в данном случае это 0). Это относится и к единицам измерениям - переводите тонны в миллионы тонн, если у вас шестизначные цифры. В той же вкладке "Формат оси" - "Параметры оси", если удобная опция "Цена деления". Можно изменить размерность единицы измерения, не меняя исходные данные на рабочем листе.
5. Корректируйте максимальное/минимальное значение по оси при необходимости
Excel пытается по умолчанию автоматически определить оптимальное максимальное и минимальное значение по оси. Часто у него получается вполне нормально, но иногда он ошибается. Если такое произошло, скорректируйте эти значения вручную.
К примеру, для графика с ценами на нефть имеет смысл увеличить минимальное значение. Excel всегда начинает его от 0 в подобных случаях. В данном случае из-за этого появляется пустое пространство и изменения цен становятся мало различимыми.
К примеру, для графика с ценами на нефть имеет смысл увеличить минимальное значение. 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, и, как правило, требуют меньше мороки.
1 комментарий:
Спасибо за интересную статью
Отправить комментарий