Гистограмма с переменной шириной столбцов
Имеем, в качестве примера, вот такую табличку с информацией по количеству сотрудников и стоимостью четырех известных IT-компаний:
Задача: необходимо наглядно отобразить оба параметра для сравнения по всем компаниям.
Можно попытаться построить типичную в таких случаях пузырьковую (точечную) диаграмму. Можно попробовать поиграть с трехмерными диаграммами или торнадо. А можно немного "пошаманить" и сделать плоскую гистограмму со столбцами переменной ширины.
Этап 1. Подготовка данных
Половина успеха при построении любых нестандартных типов диаграмм в Excel, заключается в правильной подготовке таблицы с данными для диаграммы. В нашем случае, исходную табличку придется ощутимо переделать и превратить в нечто похожее на:
Давайте разберем все изменения, которые были сделаны.
- Каждая компания превратилась из строки в столбец.
-
По каждой компании должен быть блок из 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):
На выходе должно получиться что-то похожее:
Основная проблема в том, что Excel на автомате интерпретирует подписи к оси X не как числа, а как текст - интервалы между подписями не соответствуют математической разнице между числами, да и сами подписи повторяются. Именно поэтому наша диаграмма еще не похожа на желаемую, но это, на самом деле, легко исправить. Щелкните правой кнопкой мыши по горизонтальной оси и выберите в контекстном меню команду Формат оси (Axis Format). В открывшемся окне переключите Тип оси с Автовыбора на Ось дат (Date Axis) и все сразу изменится!
В этом же окне можно настроить шаг делений по горизонтальной оси - для нашего примера я поменял параметр Единицы измерения с месяцев на дни и задал значение 50.
Этап 3. Добавляем подписи
Теперь неплохо бы добавить в нашу диаграмму подписи с названиями компаний. Скопируйте столбец Пустышка (выделить диапазон G11:G25 и нажать Ctrl+C), выделите диаграмму и вставьте скопированные данные прямо в нее (нажать Ctrl+V) - к нашей диаграмме должен добавиться новый ряд, выглядящий как крыши домов над нашими прямоугольниками:
Теперь щелкните правой кнопкой мыши по любой "крыше" и выберите команду Изменить тип диаграммы для ряда (Change series chart type). В открывшемся окне измените тип диаграммы для добавленного ряда на обычный график с маркерами:
Затем щелкните правой кнопкой мыши по графику и выберите Добавить подписи данных (Add Data Labels). Excel подпишет значения стоимости компании на каждом "пике".
Дальнейший ход действий зависит от того, какая у вас версия Excel. Если у вас последние Excel 2013 или 2016, то все будет проще. Щелкните правой кнопкой мыши по подписям, выберите команду Формат подписей (Format Data Labels) и затем включите флажок Значения из ячеек (Values from cells). Останется выделить данные из последнего столбца в нашей таблице, чтобы они попали на диаграмму в подписи к столбикам вместо чисел:
Если же у вас более древние версии Excel, то придется либо изменять подписи вручную (несколько одиночных щелчков по подписи, пока не начнет мигать курсор, а потом вбить название компании), либо использовать специальную бесплатную надстройку XYChartLabeler (дай бог здоровьичка Rob Bovey - ее автору, спасшему много-много моих нервных клеток и времени).
P.S.
Excel 2016 стал первой версией за последние 20 лет, где Microsoft, наконец-то, добавила новые типы диаграмм (водопад, Парето, Маримекко и др.) в стандартный набор. Очень хочется верить, что "лед тронулся" и в следующих обновлениях мы увидим еще больше красивых и наглядных вариантов. Возможно, когда-нибудь и описанная выше гистограмма с переменной шириной столбцов будет строиться без "шаманских танцев" из этой статьи, а за два движения. Будем надеяться :)
Ссылки по теме
- Как построить пузырьковую диаграмму в Excel
- Что такое диаграмма Парето и как построить ее в Excel
- Сравнение двух наборов данных с помощью диаграммы-торнадо
Подскажите, пожалуйста, каким образом Вы сделали сетку на заднем фоне диаграммы?
Это один из стандартных стилей диаграмм в Excel 2013-2016 (вкладка Конструктор - Стили).
А что за вопрос на форуме? Решение нашлось?
Да, нашлось, подсказали где посмотреть, сделал сам)
Как можно разделить данные в столбчатой гистограмме с накоплением, чтобы между разными цветами была пустота?
Спасибо.
Не знаю как вставить файл с примером.
Подскажите а можно сделать диаграмму с переменой шириной столбцов но только чтобы все столбцы располагались на одной оси? Типа телескопа.
Подскажите, как быть с легендой в данном случае? Она задваивается.
Я понял свою ошибку, ищу как удалить часть значений в легенде.
Не сразу понял что удаляется обычным выделением и нажатием клавиши DELET
А еще у Вас классное информирование по почте, если ответ на комментарий появился.
Спасибо за Ваш сайт и Вашу надстройку!
Может быть и мне поможете с этим "шаманским" графиком ))
В моем варианте ее еще нужно повернуть на 90 градусов и добавить значения долей в формате stacked bar
сама задачка здесь
А реально построить в Excel аналогичным образом гистограмму с накоплением?
Т.е. кроме разной ширины столбцов, необходимо еще чтобы каждый столбец делился на доли.
Подскажите, пожалуйста.
Сработает ли это решение в случае если у нас и на оси X и на Y - % число/или число меньше 1?
Хочется наглядно построить диаграмму на оси Х - доля в закупках, на оси Y - рост...
Я правильно понимаю, что ось дат подразумевает работу только с целыми значениями?