Гистограмма с переменной шириной столбцов

Имеем, в качестве примера, вот такую табличку с информацией по количеству сотрудников и стоимостью четырех известных IT-компаний:

2d-histogram1.png

Задача: необходимо наглядно отобразить оба параметра для сравнения по всем компаниям.

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

Этап 1. Подготовка данных

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

2d-histogram3.png

Давайте разберем все изменения, которые были сделаны.

  • Каждая компания превратилась из строки в столбец.
  • По каждой компании должен быть блок из 5 ячеек, где первая и последняя ячейки с нулями, а три центральных содержат одинаковые значения - стоимость каждой компании.
  • Добавлены вспомогательные столбцы Пустышка и Подписи, в которых напротив центральных значений каждого блока размещены стоимости и названия компаний. Эти колонки понадобятся нам чуть позже для правильного размещения подписей к столбикам.
  • Первый (неподписанный) столбец представляет из себя чуть более хитрую штуку. Не залитые голубым цветом в нем ячейки - это количества сотрудников по каждой компании (92, 126, 54, 380), но взятые последовательно с нарастающим итогом. Т.е. для Apple это 92, для Microsoft уже 92+126=218, для Google 92+126+54=272 и т.д. Голубые же ячейки будут впоследствии, своего рода, "переходниками" и содержат среднее арифметическое соседних ячеек, т.е. для Apple это (0+92)/2=46, для Microsoft (92+218)/2=155 и т.д.
Когда таблица подготовлена, то можно облегченно выдохнуть - самая муторная часть процесса уже позади. Осталось построить саму диаграмму,.

Этап 2. Строим диаграмму

Выделим в нашей таблице все столбцы кроме последних двух и на вкладке Вставка (Insert) выберем для построения вариант диаграммы С областями с накоплением (Stacked Area):

2d-histogram4.png

На выходе должно получиться что-то похожее:

2d-histogram5.png

Основная проблема в том, что Excel на автомате интерпретирует подписи к оси X не как числа, а как текст - интервалы между подписями не соответствуют математической разнице между числами, да и сами подписи повторяются. Именно поэтому наша диаграмма еще не похожа на желаемую, но это, на самом деле, легко исправить. Щелкните правой кнопкой мыши по горизонтальной оси и выберите в контекстном меню команду Формат оси (Axis Format). В открывшемся окне переключите Тип оси с Автовыбора на Ось дат (Date Axis) и все сразу изменится!

2d-histogram6.png

В этом же окне можно настроить шаг делений по горизонтальной оси - для нашего примера я поменял параметр Единицы измерения с месяцев на дни и задал значение 50.

Этап 3. Добавляем подписи

Теперь неплохо бы добавить в нашу диаграмму подписи с названиями компаний. Скопируйте столбец Пустышка (выделить диапазон G11:G25 и нажать Ctrl+C), выделите диаграмму и вставьте скопированные данные прямо в нее (нажать Ctrl+V) - к нашей диаграмме должен добавиться новый ряд, выглядящий как крыши домов над нашими прямоугольниками:

2d-histogram7.png

Теперь щелкните правой кнопкой мыши по любой "крыше" и выберите команду Изменить тип диаграммы для ряда (Change series chart type). В открывшемся окне измените тип диаграммы для добавленного ряда на обычный график с маркерами:

2d-histogram8.png

Затем щелкните правой кнопкой мыши по графику и выберите Добавить подписи данных (Add Data Labels). Excel подпишет значения стоимости компании на каждом "пике". 

Дальнейший ход действий зависит от того, какая у вас версия Excel. Если у вас последние Excel 2013 или 2016, то все будет проще. Щелкните правой кнопкой мыши по подписям, выберите команду Формат подписей (Format Data Labels) и затем включите флажок Значения из ячеек (Values from cells). Останется выделить данные из последнего столбца в нашей таблице, чтобы они попали на диаграмму в подписи к столбикам вместо чисел:

2d-histogram9.png

Если же у вас более древние версии Excel, то придется либо изменять подписи вручную (несколько одиночных щелчков по подписи, пока не начнет мигать курсор, а потом вбить название компании), либо использовать специальную бесплатную надстройку XYChartLabeler (дай бог здоровьичка Rob Bovey - ее автору, спасшему много-много моих нервных клеток и времени).

P.S.

Excel 2016 стал первой версией за последние 20 лет, где Microsoft, наконец-то, добавила новые типы диаграмм (водопад, Парето, Маримекко и др.) в стандартный набор. Очень хочется верить, что "лед тронулся" и в следующих обновлениях мы увидим еще больше красивых и наглядных вариантов. Возможно, когда-нибудь и описанная выше гистограмма с переменной шириной столбцов будет строиться без "шаманских танцев" из этой статьи, а за два движения. Будем надеяться :)

Ссылки по теме



21.03.2016 13:09:22
Здравствуйте!
Подскажите, пожалуйста, каким образом Вы сделали сетку на заднем фоне диаграммы?
23.03.2016 10:26:30
День добрый!
Это один из стандартных стилей диаграмм в Excel 2013-2016 (вкладка Конструктор - Стили).
23.03.2016 15:31:04
Спасибо, что-то не додумалась туда посмотреть.
31.03.2016 21:48:06
Тема видимо появилась после моего вопроса на эту тему здесь, на форуме)
01.04.2016 09:48:07
Да нет, давно запланированная статья была.
А что за вопрос на форуме? Решение нашлось?
17.10.2016 13:11:56
Скажите, а примеры построения круговых диаграмм на сайте есть?
25.11.2016 13:55:56
Николай, добрый день!
Как можно разделить данные в столбчатой гистограмме с накоплением, чтобы между разными цветами была пустота?
Спасибо.
Не знаю как вставить файл с примером.
08.02.2017 10:59:02
День добрый Николай!
Подскажите а можно сделать диаграмму с переменой шириной столбцов но только чтобы все столбцы располагались на одной оси? Типа телескопа.
03.05.2017 17:11:07
Вам воронка продаж нужна? :)
07.05.2017 04:33:02
День добрый! Подскажите по воронке продаж?? А где ее можно посмотреть??))
03.05.2017 16:52:24
Добрый день.
Подскажите, как быть с легендой в данном случае? Она задваивается.
03.05.2017 17:11:27
Почему задваивается? Где?
03.05.2017 17:14:16
В Вашем варианте это легенда "Пустышка".  После Этапа 3. Делал по подобию Вашего примера. После добавления графика с маркерами он он отобразился в легенде.
Я понял свою ошибку, ищу как удалить часть значений в легенде.
Не сразу понял что удаляется обычным выделением и нажатием клавиши DELET :(

А еще у Вас классное информирование по почте, если ответ на комментарий появился.
Спасибо за Ваш сайт и Вашу надстройку!
18.05.2017 14:37:28
Николай, добрый день!

Может быть и мне поможете с этим "шаманским" графиком ))

В моем варианте ее еще нужно повернуть на 90 градусов и добавить значения долей в формате stacked bar

сама задачка здесь
http://www.planetaexcel.ru/forum/index.php?PAGE_NAME=message&FID=1&TID=91614&TITLE_SEO=91614-carpet-chart&MID=758109&result=edit#message758109
22.05.2017 12:50:28
Добрый день! Очень интересная наглядная диаграмма, все получилось сделать, довольно подробная инструкция. Возник вопрос - а если показатели в штуках, и необходимо, чтобы гистограмма была пропорционально и в горизонтальной, и в вертиакльной осях, как это можно сделать? Т.е., грубо говоря, если и по горизонтали, и вертикали одинаковое число, должен получиться квадрат.
10.07.2018 12:09:11
Добрый день.
А реально построить в Excel аналогичным образом гистограмму с накоплением?
Т.е. кроме разной ширины столбцов, необходимо еще чтобы каждый столбец делился на доли.
12.11.2018 17:13:24
Николай, добрый день.
Подскажите, пожалуйста.
Сработает ли это решение в случае если у нас и на оси X и на Y - % число/или число меньше 1?
Хочется наглядно построить диаграмму на оси Х - доля в закупках, на оси Y - рост...
Я правильно понимаю, что ось дат подразумевает работу только с целыми значениями?

 
Наверх