Как в Excel показать сравнить рост двух различных показателей? Можно «просто» сравнить данные. Правда, если они не сопоставимы, то особо ничего не увидишь. Например, можно построить гистограмму с двумя столбиками. Или показать данные на объединенных графиках с помощью вспомогательной оси.
Но есть еще один вариант — пожалуй, самый наглядный: методом базисной подстановки, который любит журнал The Economist. Как это сделать в Excel, смотрите в инструкции.
Разберем по шагам, как построить такой график. Исходные данные – суммы розничных продаж и продаж на рынках за несколько лет.
2008 | 2009 | 2010 | 2011 | 2012 | |
оборот розничной торговли | 13 853 | 14 599 | 16 499 | 19 083 | 21 395 |
продажа товаров на рынках | 1 837 | 1 986 | 2 095 | 2 385 | 2 268 |
Шаг 1. Подготовьте данные для диаграммы. Рассчитайте рост продаж в процентах по отношению к первому году. А в столбце с первым годом (в таблице – 2008 год) поставьте 100%.
Добавьте строку, которая будет показана на графике вместо оси Х. Указываем значения в строке – 100% для всех ячеек и добавляем один «лишний» столбец, чтобы на графике появилось место для подписей.
Следующие строки – маркеры для последнего года и их подписи. В первом столбце укажите названия направлений и сумму продаж за последний год: «розница 21,4трлн.р.», «рынки 2,3трлн.р». Значения всех столбцов периодов, кроме последнего — #Н/Д. В последнем столбце – процент фактического роста.
И последнее – это рамка графика, где можно разместить подписи данных. В двух последних столбцах введите значение чуть больше максимального процента роста.
В итоге получится такая таблица:
Шаг 2. Постройте график: выделите данные и нажмите Вставка → График.
Шаг 3. Настраиваем график. Удалите легенду.
Измените тип диаграммы в меню Конструктор → Изменить тип диаграммы. Выберите комбинированную диаграмму и настройте тип диаграмм, как на рисунке.
Выделите столбцы гистограммы правой кнопкой мышки, перейдите к Формату ряда данных. Задайте боковой зазор 0%.
Щелкните по подписи оси Х правой кнопкой мышки, перейдите к Формату оси. В параметрах оси укажите ее положение – на делениях. Чтобы на оси легче читалось название последнего года, введите формулу в названии последнего столбца =" "
&2012
Настройте график «псевдооси» — выделите линию, задайте для нее темно-серый цвет и ширину 0,75 пт.
Добавьте подписи для маркеров – выделите маркеры, перейдите в меню Конструктор → Добавить элемент диаграммы → Метки данных → Справа.
Настройте отображение меток данных. Щелкните по метке мышкой, перейдите к формату подписей данных и включите в подписи только имя ряда.
Шаг 4. Настройте цвета на диаграмме, её название и отображение осей. Диаграмма готова.
Больше интересных диаграмм в статье:
12 нестандартных диаграмм Excel
В этой статье мы собрали 12 необычных диаграммам Excel со ссылками на краткие инструкции по их построению.