Создание диаграммы возрастно-половой пирамиды в Excel

Диаграмма возрастно-половой пирамиды — это специальная Гистограмма, которая показывает распределение населения по возрасту и полу. Она используется для отображения текущего состояния населения и тенденций его роста. С помощью такой диаграммы можно получить информацию о степени старения населения, количестве людей детородного возраста, будущих показателях рождаемости и смертности, а также общей численности населения в будущем.

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

• Создайте диаграмму возрастно-половой пирамиды с помощью Гистограмма в Excel
• Создайте диаграмму возрастно-половой пирамиды с помощью Использовать условное форматирование в Excel
• Скачать образец файла диаграммы возрастно-половой пирамиды
• Видео: Создание диаграммы возрастно-половой пирамиды в Excel

Создание диаграммы возрастно-половой пирамиды с помощью Гистограмма в Excel

Допустим, у вас есть таблица с данными о населении деревни, как в приведённом ниже примере. Чтобы построить диаграмму возрастно-половой пирамиды, выполните следующие шаги:

1. Сначала преобразуйте численность женщин в отрицательные значения. Введите формулу: =0-C2 в ячейку D2 и перетащите маркер заполнения вниз, чтобы применить формулу ко всем остальным ячейкам (см. скриншот):

2. Затем, удерживая клавишу Ctrl, выделите данные в столбцах A, B и D (см. скриншот):

3. Далее нажмите Вставка > Диаграммы / Гистограмма > Линейчатая с накоплением (см. скриншот):

4. Диаграмма будет вставлена немедленно, как показано на скриншоте ниже:

5. Щелкните правой кнопкой мыши вертикальную ось и выберите Формат оси в контекстном меню (см. скриншот):

6. В открывшейся области Формат оси перейдите на вкладку Параметры оси, разверните раздел Метки, затем выберите Внизу в раскрывающемся списке Расположение меток. Вертикальная ось переместится на левую сторону диаграммы (см. скриншот):

7. Щелкните правой кнопкой мыши по любому столбцу диаграммы и выберите Формат ряда данных (см. скриншот):

8. В области Формат ряда данных на вкладке Параметры ряда введите 100 % и 0 % в поля Перекрытие рядов и Ширина зазора соответственно (см. скриншот):

9. Теперь, если понадобится, вы можете удалить горизонтальную ось.

10. Чтобы диаграмма выглядела нагляднее, добавьте контур к столбцам: щелкните правой кнопкой мыши оранжевый столбец и выберите Контур, затем укажите цвет «Темно-оранжевый». Повторите то же самое для синих столбцов (см. скриншот):

11. Кроме того, вы можете добавить метки данных к диаграмме: выделите диаграмму и нажмите Элементы диаграммы, установите флажок Метки данных — после этого метки данных будут добавлены автоматически (см. скриншот):

12. На этом этапе нужно преобразовать отрицательные метки данных в положительные числа. Щелкните правой кнопкой мыши по метке данных и выберите Формат метки данных (см. скриншот):

13. В области Формат метки данных на вкладке Параметры меток разверните раздел Число, выберите Пользовательский в раскрывающемся списке Тип и введите ###0;###0 в поле Код формата, затем нажмите кнопку Добавить (см. скриншот):

14. В итоге у вас получится готовая диаграмма возрастно-половой пирамиды, как на скриншоте ниже:

Создание диаграммы возрастно-половой пирамиды с помощью Использовать условное форматирование в Excel

В этом разделе рассказывается, как создать диаграмму возрастно-половой пирамиды непосредственно в ячейках с использованием функции Использовать условное форматирование, как показано на скриншоте ниже:

1. Сначала выделите данные в столбце «Мужчины», затем нажмите Главная > Условное форматирование > Создать правило (см. скриншот):

2. В диалоговом окне Создание нового правила форматирования выполните следующие действия:

• Выберите Форматировать все ячейки на основе их значенийв списке Выбор типа правила:
• Выберите Графическая шкалав раскрывающемся списке Стиль формата, затем установите флажок Показывать только полосу:
• В разделе Внешний вид полосывыберите Сплошная заливкав раскрывающемся списке Заливка, а затем укажите понравившийся цвет в раскрывающемся списке Цвет:
• Выберите вариант Справа налево в раскрывающемся списке Направление полосы.

3. Нажмите кнопку ОК, чтобы применить форматирование.

4. Выделите данные в столбце «Женщины», затем перейдите на вкладку Главная → Условное форматирование → Создать правило, чтобы открыть диалоговое окно Создание нового правила форматирования.

5. В диалоговом окне Создание нового правила форматирования выполните следующие действия:

• Выберите Форматировать все ячейки на основе их значенийв списке Выбор типа правила:
• Выберите Графическая шкалав раскрывающемся списке Стиль формата, затем установите флажок Показывать только полосу:
• В разделе Внешний вид полосывыберите Сплошная заливкав раскрывающемся списке Заливка, а затем укажите другой цвет в раскрывающемся списке Цвет:
• Выберите вариант Слева направо в раскрывающемся списке Направление полосы.

6. Нажмите кнопку ОК, чтобы закрыть диалоговое окно. В результате прямо в ячейках появится следующая диаграмма:

7. Чтобы отобразить данные для столбцов, просто вставьте по два столбца с каждой стороны столбцов «Мужчины» и «Женщины», а затем добавьте исходные данные, как показано на снимке экрана ниже:

Скачать образец файла диаграммы возрастно-половой пирамиды

Видео: Создание диаграммы возрастно-половой пирамиды в Excel