Трюк №56. Как в Excel создать гистограмму с переменными шириной и высотой?

Разве не удобно было бы создавать гистограммы, столбцы которых могли бы иметь переменную ширину и высоту? Ширина и высота столбцов одновременно регулировались бы при построении графика, по вашим данным.

В Excel эта возможность напрямую не предусмотрена, однако, применив трюк с точечной диаграммой в осях XY (XY scatter chart), можно создавать очень эффективные гистограммы с переменной шириной столбцов. Точечная диаграмма по осям XY применяется для сравнения значений; таким образом, она является прекрасной основой для создания гистограммы со столбцами переменной ширины.

На рис. 5.24 показана гистограмма со столбцами переменной ширины, которая демонстрирует соотношения между процентным содержанием и стоимостью следующих расходов: газ, электричество, вода, еда, поездки и прочие. На оси X (эта ось проходит внизу диаграммы) выводится процентное содержание (%), а на оси Y (ось слева на диаграмме) выводится стоимость ($).

Рис. 5.24. Точечная диаграмма в осях XY

Чтобы создать такую диаграмму, настройте таблицу с некоторыми данными (см. рис. 5.24) и при помощи мастера диаграмм выделите диапазон D2:E8. Затем на шаге 1 мастера диаграмм выберите диаграмму Точечная (XY Scatter). Примите настройки точечной диаграммы по умолчанию (выводятся только точки) и щелкните на кнопке Далее (Next), чтобы перейти к шагу 2 мастера. Удостоверьтесь, что установлен переключатель В столбцах (Columns). Щелкните кнопку Далее (Next), чтобы перейти к третьему шагу мастера, и в поле Ось Y (значений) (Value (Y) Axis) введите Cost ($). Щелкните кнопку Далее (Next) и удостоверьтесь, что диаграмма будет создана как объект на текущем, а не на новом листе. Щелкните на кнопке Готово (Finish), чтобы увидеть точечную диаграмму.
[stextbox id=»info»]Чтобы увидеть формулы в ячейках, можно нажать сочетание клавиш Ctrl+~ (то же сочетание используется на Macintosh) или команду Сервис → Параметры → Вид (Tools → Options → View) и в группе Параметры окна (Window) установить флажок Формулы (Formulas).[/stextbox]
Настало время повозиться с диаграммой и создать столбцы. Сначала удалите легенду и линии сетки (выделите их и нажмите клавишу Delete) и отформатируйте область построения диаграммы, убрав заливку. Для этого щелкните серый фон правой кнопкой мыши и в контекстном меню выберите команду Формат области построения (Format Plot Area). В группе Заливка (Area) выберите переключатель Прозрачная (None).

Дважды щелкните ось X, чтобы открыть диалоговое окно Формат оси (Format Axis). Щелкните вкладку Шкала (Scale). В области Шкала по оси X (категорий) (Value Axis Scale) для параметра Минимальное значение (Minimum) введите 0, а для параметра Максимальное значение (Maximum) введите 1. Перейдите на вкладку Вид (Pattern) и в группе Основные (Major Tick Mark type) выберите переключатель Нет (None). В группе Метки делений (Tick Mark Labels) также выберите переключатель Нет (None) и щелкните на кнопке ОК. Точечная диаграмма должна выглядеть, как на рис. 5.25.

Рис. 5.25. Модифицированная точечная диаграмма

На следующем шаге нужно создать линии для столбцов, поэтому дважды щелкните точку данных, чтобы открыть диалоговое окно Формат ряда данных (Format Data Series). Перейдите на вкладку Х-погрешности (X Error Bars). В группе Показать планки погрешностей по X (Display) выберите вариант Минус (Minus), затем перейдите в поле диапазона Пользовательская: — (Custom: — Range) и выделите диапазон F2:F8. Вы получите горизонтальную линию сверху столбца.

Теперь щелкните вкладку Y-погрешности (Y Error Bars) и в группе Показать планки погрешностей по Y (Display) выберите вариант Обе (Both). В поле Пользовательская: + (Custom + Range) выберите диапазон Н2:Н7, а в поле Пользовательская: — (Custom — Range) выберите диапазон G2:G8. Щелкните на кнопке ОК. Вы получите вертикальные линии по сторонам столбцов.

Теперь, когда вся трудная работа сделана, настало время немного убраться и добавить подписи. Сначала в диалоговом окне Формат ряда данных (Format Data Series) перейдите на вкладку Patterns (Вид) и в поле Marker (Маркер) выберите None (Отсутствует). Результат — гистограмма со столбцами переменной ширины, созданная на основе модифицированной точечной диаграммы по осям XY (рис. 5.26).

Рис. 5.26. Гистограмма со столбцами переменной ширины

Если вы хотите нанести на гистограмму подписи, стоит загрузить утилиты диаграмм Джона Волкенбаха (John Walkenbach’s Chart Tools). Часть этих надстроек предназначена для меток данных. Они позволяют показывать диапазон данных на рабочем листе, из которых будут создаваться подписи для данных в рядах диаграммы. (К сожалению, утилиты не работают в Excel для Macintosh, даже после выделения их из дистрибутивов ЕХЕ.)

Перед использованием Chart Tools необходимо добавить новый ряд данных, чтобы создать подписи по оси X для диаграммы. Выделите диаграмму, щелкните ее правой кнопкой мыши, в контекстном меню выберите команду Исходные данные (Source Data) и перейдите на вкладку Ряд (Series). Щелкните кнопку Добавить (Add), чтобы добавить новый ряд. В поле Значения X (X Values) выделите диапазон 13:18, а в поле Значения Y (Y Values) — диапазон J3:J8. Отформатируйте новый ряд данных, убрав маркеры. Для этого нужно открыть диалоговое окно Формат рядов данных (Format Data Series), перейти на вкладку Вид (Patterns) и в группе Маркер (Marker) выбрать переключатель Отсутствует (None).

Настало время применить надстройку. Удостоверьтесь, что выбран ряд Series 2 (Ряд 2), и выберите команду Chart → JWalk Chart Tools. В открывшемся диалоговом окне выберите диапазон меток данных КЗ:К8. Чтобы добавить еще один ряд данных, из которого будут взяты подписи для столбцов диаграммы, выделите диаграмму, щелкните ее правой кнопкой мыши и в контекстном меню выберите команду Исходные данные (Source Data) и перейдите на вкладку Ряд (Series). Щелкните кнопку Добавить (Add), чтобы добавить новый ряд. В поле Значения X (X Values) выделите диапазон 13:18, а в поле Значения Y (Y Values) выделите диапазон СЗ:С8. И снова отформатируйте новый ряд данных, убрав маркеры. Для этого откройте диалоговое окно Формат рядов данных (Format Data Series), перейдите на вкладку Вид (Patterns) и в группе Маркер (Marker) выберите Отсутствует (None).

Еще раз примените надстройку. В этот раз выделите Series 3 (Ряд 3) и свяжите подписи данных с диапазоном АЗ:А8. Результат должен выглядеть, как на рис. 5.27.

Рис. 5.27. Завершенная гистограмма со столбцами переменной ширины

Удивительное свойство этого типа диаграмм состоит в том, что полосы будут расширяться или сжиматься по оси X в сторону оси Y, когда значения в столбцах % Share или Cost ($) будут изменяться, Отлично!