Как построить гистограмму с интервалами в excel

Гистограмма распределения в EXCEL

history 15 ноября 2016 г.

Группы статей
• Описательная статистика

Гистограмма распределения — это инструмент, позволяющий визуально оценить величину и характер разброса данных. Создадим гистограмму для непрерывной случайной величины с помощью встроенных средств MS EXCEL из надстройки Пакет анализа и в ручную с помощью функции ЧАСТОТА() и диаграммы.

Гистограмма (frequency histogram) – это столбиковая диаграмма MS EXCEL , в каждый столбик представляет собой интервал значений (корзину, карман, class interval, bin, cell), а его высота пропорциональна количеству значений в ней (частоте наблюдений).

Гистограмма поможет визуально оценить распределение набора данных, если:

• в наборе данных как минимум 50 значений;
• ширина интервалов одинакова.

Построим гистограмму для набора данных, в котором содержатся значения непрерывной случайной величины . Набор данных (50 значений), а также рассмотренные примеры, можно взять на листе Гистограмма AT в файле примера. Данные содержатся в диапазоне А8:А57 .

Примечание : Для удобства написания формул для диапазона А8:А57 создан Именованный диапазон Исходные_данные.

Построение гистограммы с помощью надстройки Пакет анализа

Вызвав диалоговое окно надстройки Пакет анализа , выберите пункт Гистограмма и нажмите ОК.

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

• автоматически рассчитаны интервалы значений (карманы);
• подсчитано количество значений из указанного массива данных, попадающих в каждый интервал (построена таблица частот);
• если поставлена галочка напротив пункта Вывод графика , то вместе с таблицей частот будет выведена гистограмма.

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

Как видно из рисунка, первый интервал включает только одно минимальное значение 113 (точнее, включены все значения меньшие или равные минимальному). Если бы в массиве было 2 или более значения 113, то в первый интервал попало бы соответствующее количество чисел (2 или более).

Второй интервал (отмечен на картинке серым) включает значения больше 113 и меньше или равные 216,428571428571. Можно проверить, что таких значений 11. Предпоследний интервал, от 630,142857142857 (не включая) до 733,571428571429 (включая) содержит 0 значений, т.к. в этом диапазоне значений нет. Последний интервал (со странным названием Еще ) содержит значения больше 733,571428571429 (не включая). Таких значений всего одно — максимальное значение в массиве (837).

Размеры карманов одинаковы и равны 103,428571428571. Это значение можно получить так: =(МАКС( Исходные_данные )-МИН( Исходные_данные ))/7 где Исходные_данные – именованный диапазон , содержащий наши данные.

Почему 7? Дело в том, что количество интервалов гистограммы (карманов) зависит от количества данных и для его определения часто используется формула √n, где n – это количество данных в выборке. В нашем случае √n=√50=7,07 (всего 7 полноценных карманов, т.к. первый карман включает только значения равные минимальному).

Примечание : Похоже, что инструмент Гистограмма для подсчета общего количества интервалов (с учетом первого) использует формулу =ЦЕЛОЕ(КОРЕНЬ(СЧЕТ( Исходные_данные )))+1

Попробуйте, например, сравнить количество интервалов для диапазонов длиной 35 и 36 значений – оно будет отличаться на 1, а у 36 и 48 – будет одинаковым, т.к. функция ЦЕЛОЕ() округляет до ближайшего меньшего целого (ЦЕЛОЕ(КОРЕНЬ(35))=5 , а ЦЕЛОЕ(КОРЕНЬ(36))=6) .

Если установить галочку напротив поля Парето (отсортированная гистограмма) , то к таблице с частотами будет добавлена таблица с отсортированными по убыванию частотами.

Если установить галочку напротив поля Интегральный процент , то к таблице с частотами будет добавлен столбец с нарастающим итогом в % от общего количества значений в массиве.

Если выбор количества интервалов или их диапазонов не устраивает, то можно в диалоговом окне указать нужный массив интервалов (если интервал карманов включает текстовый заголовок, то нужно установить галочку напротив поля Метка ).

Для нашего набора данных установим размер кармана равным 100 и первый карман возьмем равным 150.

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

Как видно из рисунков выше, надстройка Пакет анализа не осуществляет никакого дополнительного форматирования диаграммы . Соответственно, вид такой гистограммы оставляет желать лучшего (столбцы диаграммы обычно располагают вплотную для непрерывных величин, кроме того подписи интервалов не информативны). О том, как придать диаграмме более презентабельный вид, покажем в следующем разделе при построении гистограммы с помощью функции ЧАСТОТА() без использовании надстройки Пакет анализа .

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

Порядок действий при построении гистограммы в этом случае следующий:

• определить количество интервалов у гистограммы;
• определить ширину интервала (с учетом округления);
• определить границу первого интервала;
• сформировать таблицу интервалов и рассчитать количество значений, попадающих в каждый интервал (частоту);
• построить гистограмму.

СОВЕТ : Часто рекомендуют, чтобы границы интервала были на один порядок точнее самих данных и оканчивались на 5. Например, если данные в массиве определены с точностью до десятых: 1,2; 2,3; 5,0; 6,1; 2,1, …, то границы интервалов должны быть округлены до сотых: 1,25-1,35; 1,35-1,45; … Для небольших наборов данных вид гистограммы сильно зависит количества интервалов и их ширины. Это приводит к тому, что сам метод гистограмм, как инструмент описательной статистики , может быть применен только для наборов данных состоящих, как минимум, из 50, а лучше из 100 значений.

В наших расчетах для определения количества интервалов мы будем пользоваться формулой =ЦЕЛОЕ(КОРЕНЬ(n))+1 .

Примечание : Кроме использованного выше правила (число карманов = √n), используется ряд других эмпирических правил, например, правило Стёрджеса (Sturges): число карманов =1+log2(n). Это обусловлено тем, что например, для n=5000, количество интервалов по формуле √n будет равно 70, а правило Стёрджеса рекомендует более приемлемое количество — 13.

Расчет ширины интервала и таблица интервалов приведены в файле примера на листе Гистограмма . Для вычисления количества значений, попадающих в каждый интервал, использована формула массива на основе функции ЧАСТОТА() . О вводе этой функции см. статью Функция ЧАСТОТА() — Подсчет ЧИСЛОвых значений в MS EXCEL .

В MS EXCEL имеется диаграмма типа Гистограмма с группировкой , которая обычно используется для построения Гистограмм распределения .

В итоге можно добиться вот такого результата.

Примечание : О построении и настройке макета диаграмм см. статью Основы построения диаграмм в MS EXCEL .

Одной из разновидностей гистограмм является график накопленной частоты (cumulative frequency plot).

На этом графике каждый столбец представляет собой число значений исходного массива, меньших или равных правой границе соответствующего интервала. Это очень удобно, т.к., например, из графика сразу видно, что 90% значений (45 из 50) меньше чем 495.

СОВЕТ : О построении двумерной гистограммы см. статью Двумерная гистограмма в MS EXCEL .

Примечание : Альтернативой графику накопленной частоты может служить Кривая процентилей , которая рассмотрена в статье про Процентили .

Примечание : Когда количество значений в выборке недостаточно для построения полноценной гистограммы может быть полезна Блочная диаграмма (иногда она называется Диаграмма размаха или Ящик с усами ).

Как построить гистограмму с интервалами в Excel?

Как сделать гистограмму с интервалами в Excel?

Вызвав диалоговое окно надстройки Пакет анализа , выберите пункт Гистограмма и нажмите ОК. В появившемся окне необходимо как минимум указать: входной интервал и левую верхнюю ячейку выходного интервала . После нажатия кнопки ОК будут: автоматически рассчитаны интервалы значений (карманы);

Как построить график в Excel с интервалами?

Вы можете выбрать данные вручную или кликните на любую ячейку в нужном диапазоне и нажмите Ctrl+A на клавиатуре.

1. Выбрав данные, перейдите во вкладку «Вставить» на панели ленты. …
2. В результате в вашу таблицу Excel будет вставлена гистограмма. …
3. В открывшемся окне справа «Формат оси» выберите интервалы «По категориям»:

Как построить гистограмму в Excel по данным таблицы?

Чтобы создать гистограмму, сделайте следующее:

1. В окне сообщения выберите Вставка > Диаграмма .
2. В диалоговом окне Вставка диаграммы щелкните Гистограмма , выберите нужный тип гистограммы и нажмите кнопку ОК . …
3. Замените их собственными данными. …
4. При необходимости вы можете сохранить лист:

Как построить гистограмму с группировкой?

Построим Гистограмму с группировкой на основе таблицы с двумя числовыми столбцами, близких по значениям. Выделите любую ячейку таблицы (см. файл примера ), на вкладке Вставка , в группе Диаграммы нажмите кнопку Гистограмма , в выпавшем меню выберите Гистограмма с группировкой .

Как в Excel построить гистограмму?

Чтобы создать гистограмму, сделайте следующее:

1. В окне сообщения выберите Вставка > Диаграмма.
2. В диалоговом окне Вставка диаграммы щелкните Гистограмма, выберите нужный тип гистограммы и нажмите кнопку ОК. …
3. Замените их собственными данными. …
4. При необходимости вы можете сохранить лист:

Как построить график распределения в Excel?

Способ 1-ый. Халявный.

1. Идем во вкладку «Анализ данных» и выбираем «Гистограмма».
2. Выбираем входной интервал.
3. Здесь же предлагается задать интервал карманов, т. …
4. Если хотим сразу же вывести график,то ставим галочку напротив «Вывод графика».
5. Нажимаем «ОК».
6. Вот, вроде бы, и все: гистограмма готова.

Как сделать интервал в Excel?

Щелкните правой кнопкой мыши необходимую ячейку и выберите пункт Формат ячеек в контекстном меню. На вкладке Выравнивание в раскрывающемся списке по вертикали выберите пункт Распределенное. Нажмите кнопку ОК. Текст будет равномерно распределен по ячейке.

Как построить диаграмму в Excel по данным таблицы?

Выделяем таблицу с данными. Переходим на вкладку «Вставка» — «Диаграммы». Выбираем тип «Круговая». Как только мы нажимаем на подходящее нам изображение, появляется готовая диаграмма.

Как сделать гистограмму на телефоне?

Как создать диаграмму или график

1. Откройте файл в приложении Google Таблицы на телефоне или планшете Android.
2. Выберите ячейки, которые вы хотите включить в диаграмму.
3. Нажмите на значок Вставка Диаграмма.
4. Чтобы изменить тип диаграммы, нажмите Тип и выберите нужный вариант.
5. Нажмите на значок Готово .

Как сделать гистограмму с накоплением и группировкой?

Создадим гистограмму с накоплением, а затем изменим ее.

1. Выделите все данные в третьей таблице и щелкните Вставка > Вставить гистограмму > Гистограмма с накоплением . …
2. Чтобы поменять местами оси диаграммы, щелкните ее правой кнопкой мыши и выберите Выбор данных .
3. Щелкните Строка/столбец . …
4. Нажмите кнопку ОК .

Как построить линейный график в Excel?

Нет ничего проще, чем построить линейчатую диаграмму в Excel. Сначала выделите данные, которые нужно показать на диаграмме, затем на вкладке Вставка (Insert) в разделе Диаграммы (Charts) кликните иконку линейчатой диаграммы и выберите, какой подтип нужно создать.

Как построить гистограмму распределения?

Строится гистограмма – на оси абсцисс (горизонтальной оси) отмечаются интервалы, а на оси ординат (вертикальной оси) отмечается частота попаданий результатов измерений в каждый интервал.