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

Гистограмма — это разновидность столбчатой диаграммы, которая наглядно отображает распределение числовых или категориальных данных. На приведённой ниже гистограмме показано распределение оценок по предмету: вы сразу увидите, сколько студентов получили оценки в диапазоне «48–60», сколько набрали от 60 до 78 и так далее. В этом руководстве мы пошагово покажем, как создать гистограмму в Excel.

Создание гистограммы в Excel 2019 и более ранних версиях

Допустим, вы хотите построить гистограмму на основе данных об оценках, как показано на снимке экрана ниже. Выполните следующие действия.

Примечание: Методы, описанные в этом разделе, работают не только в Excel 2019, но и в более ранних версиях — Excel 2016, 2013 и 2010.

Создание гистограммы в Excel 2019 и более ранних версиях с помощью формулы

1. Сначала необходимо разделить диапазон оценок на несколько интервалов (бинов) в соответствии с вашими потребностями. В данном случае я разделил оценки на 4 бина.

Совет: Обычно бины задаются как последовательные непересекающиеся интервалы переменной.

2. Подсчитайте, сколько оценок попадает в каждый интервал.

Выберите пустую ячейку для вывода результата первого бина, вставьте в неё приведённую ниже формулу и нажмите клавишу Enter.

=COUNTIFS($B$2:$B$28,«>,=48»,$B$2:$B$28,«<,60»)

Примечание: В формуле $B$2:$B$28 — это диапазон оценок, а 48 и 60 — границы значений для первого бина.

3. Примените ту же функцию СЧЁТЕСЛИ (COUNTIF), скорректировав диапазон значений для каждого отдельного бина, чтобы получить частоты для остальных бинов.

4. Выделите данные бинов и частот (включая заголовки), затем щёлкните Вставка > Вставка столбчатой или гистограммы > Групповая диаграмма.

Теперь гистограмма создана, как показано на снимке экрана ниже.

5. Щёлкните правой кнопкой мыши по ряду данных и выберите в контекстном меню пункт Формат ряда данных.

6. В области Формат ряда данных на вкладке Параметры ряда измените значение параметра Ширина зазора на 0 %.

7. Оставайтесь в области Формат ряда данных и выполните следующие действия.

8. Вернитесь к диаграмме и измените её заголовок в соответствии с вашими потребностями.

Теперь гистограмма полностью готова, как показано на снимке экрана ниже.

Создание гистограммы в Excel 2019 и более ранних версиях с помощью Пакета анализа

Рекомендуем использовать Пакет анализа для создания гистограммы в Excel — этот метод подходит также для версий Excel 2019, 2016, 2013 и 2010.

1. Сначала создайте вспомогательный столбец и задайте диапазоны бинов на основе исходного диапазона данных.

Примечание: Как показано на снимке экрана ниже, первый бин — 48 — указывает количество студентов, набравших менее 48 баллов, второй бин — 60 — показывает, сколько студентов получили оценки от 49 до 60, и так далее.

2. Нажмите Файл > Параметры, чтобы открыть окно Параметры Excel.

3. В окне Параметры Excel щёлкните по пункту Надстройки в левой панели, убедитесь, что в поле Надстройки Excel в раскрывающемся списке Управление выбран нужный параметр, и нажмите кнопку Перейти.

4. В диалоговом окне Надстройки установите флажок напротив элемента Пакет анализа – VBA и нажмите кнопку ОК.

Теперь группа Анализ добавлена на вкладку Данные в Excel.

5. Перейдите на вкладку Данные, щёлкните по кнопке Анализ данных в группе Анализ.

6. В диалоговом окне Анализ данных выберите пункт Гистограмма в разделе Инструменты анализа и нажмите кнопку ОК.

7. В появившемся диалоговом окне Гистограмма выполните следующую настройку.

Затем создаются новый диапазон и гистограмма, как показано на снимке экрана ниже.

8. Затем правильно настройте бины и внесите изменения в другие элементы диаграммы.

В итоге гистограмма отображается следующим образом.

Создание гистограммы в Excel 2019 и 2016 со встроенной функцией

Если вы используете Excel 2019 или 2016, воспользуйтесь встроенной функцией «Гистограмма» в разделе «Тип диаграммы», чтобы легко создать гистограмму в Excel. Выполните следующие действия.

1. Выделите весь диапазон таблицы и щёлкните Вставка > Вставка статистической диаграммы > Гистограмма.

Гистограмма создана, как показано на снимке экрана ниже.

Примечание: Количество бинов и их ширина рассчитываются автоматически на основе выбранных оценок.

Совет:

Чтобы настроить гистограмму, щёлкните правой кнопкой мыши по горизонтальной оси и выберите в контекстном меню пункт Формат оси.

В открывшейся области справа в Excel доступны несколько параметров, которые следует знать и при необходимости настроить самостоятельно:

Легко создавайте гистограммы с помощью удивительной функции

Скачать образец файла

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