Половозрастная пирамида в Excel

Половозрастная пирамида представляет собой диаграмму распределения населения по полу и возрасту.
Разберем принципы построения половозрастной пирамиды в Excel.

Половозрастная пирамида (также встречаются названия демографическая пирамида, диаграмма торнадо (tornado chart) и диаграмма бабочка (butterfly chart), названные так за визуальную схожесть с видом торнадо или бабочки) — это диаграмма с графическим представлением распределения населения по полу и возрасту.
В общем же случае график показывает соотношение между двумя показателями по набору параметров.
К примеру, с помощью такого типа диаграммы можно показывать распределение по продуктам в двух магазинах, сравнивать двух конкурентов между собой по различным категориям товаров и т.д.

Как построить половозрастную пирамиду в Excel?

Рассмотрим построение графика на конкретном примере.
Предположим, что у нас имеется следующая таблица (диапазон A1:C16) с данными о составе населения в разрезе пола и возраста:

Таблица с исходными данными
Чтобы построить график создадим вспомогательную таблицу (диапазон E1:F16), в которой один из рядов данных изменим и сделаем отрицательным, другими словами умножим на -1, а другой ряд оставим без изменений.
В этом случае при создании диаграммы столбцы с данными будут расходиться в разные стороны от вертикальной оси.

Аналогичного результата также можно добиться и без создания вспомогательных таблиц, нужно лишь изменить сами исходные данные. Для этого в любую пустую ячейку введем -1, скопируем ее, и выделив диапазон с данными (B2:B16) с помощью специальной вставки умножим значения на -1, после чего все значения в выделенном диапазоне станут отрицательными.

Теперь на основе вспомогательной таблицы непосредственно перейдем к построению пирамиды.
Выделяем данные и переходим в панели вкладок на Вставка -> Диаграмма -> Линейчатая -> Линейчатая с накоплением:

Создание графика
В диаграмме уже узнаются очертания половозрастной пирамиды, однако она все еще не приобрела окончательный вид.
Для этого зададим боковой зазор для рядов равным 0 (по усмотрению можно задать и отличное от 0 значение) — нажимаем правой кнопкой мыши на любой ряд, выбираем Формат ряда данных -> Параметры ряда -> Боковой зазор:

Изменение бокового зазора диаграммы
Также для лучшего восприятия диаграммы перенесем вертикальную ось категорий в левую или правую часть графика.
Нажимаем правой кнопкой мыши на ось и выбираем Формат оси -> Подписи оси -> Вверху или Внизу:

Перенос вертикальной оси диаграммы
Далее настраиваем внешний вид диаграммы на свой вкус и на выходе получаем следующий вариант половозрастной пирамиды:

Половозрастная пирамида
Для завершения построения нам осталось добавить подписи данных, однако в этом шаге также есть определенные трудности.

Добавление подписей

При попытке добавить подписи данных к рядам на диаграмму перед нами встает проблема — в левой части графика данные отображаются со знаком минуса:

Добавление подписей данных
Это проблему легко можно устранить поменяв формат отображения ячеек с исходными данными, или же поменять формат отображения подписей данных на графике.
В Excel числовой формат отображения любой ячейки записывается в виде A;B;C;D (например #;-#;0), где A — вид записи, если число положительное (#), B — если число отрицательное (-#), С — в случае нуля (0), D — если в ячейке не число, а текст (обычно для чисел не используется).
Соответственно убрав минус из отображения отрицательного числа, мы получим требуемый результат.

Выделяем диапазон с данными B2:B16 (или подпись данных на графике) и в окне Формат ячейки (или Формат подписей данных) во вкладке Число прописываем новую маску отображения:

Задание маски отображения
В результате мы получаем окончательный вариант половозрастной пирамиды:

Половозрастная пирамида

Подробно ознакомиться с примером половозрастной пирамидыскачать пример.

Удачи вам и до скорой встречи на страницах блога Tutorexcel.ru!

Поделиться с друзьями:
Поиск по сайту:

3 идей о “Половозрастная пирамида в Excel

  1. Zulfiya

    Класс! искренне благодарю, получилось, но оформление параметров ниже 0 на выше нуля получилось в кодом #,#

  2. Алина

    Большое спасибо, видео очень понятное) Но я перед собой поставила задачу немного сложнее, а именно добавить группировку в данный график (то есть должны присутствовать элементы линейчатой диаграммы с группировкой и накоплением) но, к сожалению, при изменении типа диаграммы для нужного показателя, этот тип меняется для всех показателей. Помогите пожалуйста решить эту задачу! (Имеются данные по прибывшим и выбывшим с одной страны и с другой; предполагается с правой стороны группировка по прибывшим, а с левой — по выбывшим) Заранее спасибо)

Добавить комментарий

Ваш адрес email не будет опубликован.