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

Имеем, в качестве примера, вот такую табличку с информацией по количеству сотрудников и стоимостью четырех известных 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) :

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

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

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

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

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

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

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

Как построить гистограмму в MS 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 и т.д.

Тип подгонки (вкладка «Дополнительно») оставим по умолчанию «Нормальное» (подгонка под нормальное распределение), предусмотрим вывод статистики Колмогорова-Смирнова для проверки соответствия фактического распределения нормальному: