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

Требуется построить диаграмму стандартного нормального распределения Гаусса (стандартное нормальное распределение имеет М = 0 и = 1), используя функцию

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

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

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

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

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

Как видно из рисунка, первый интервал включает только одно минимальное значение 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.

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

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

Задания для самостоятельной работы

1. Для данных из примера 1 построить выборочные функции распределения, воспользовавшись процедурой Гистограмма из пакета Анализа.

2.  Построить выборочные функции распределения(относительные и накопленные частоты) для ростав см. 20 студентов: 181, 169, 178, 178, 171, 179, 172, 181, 179, 168, 174, 167, 169, 171, 179, 181, 181,183, 172, 176.

3. Найдите распределение по абсолютным частотам дляследующих результатов тестирования вбаллах: 79, 85, 78, 85, 83, 81, 95, 88, 97, 85 (используйте границы интервалов 70, 80, 90).

Обозначим этот параметр через х. Тогда в процессе ответа на вопрос величина хпримет дискретное значение х, принадлежащее определенному интервалу значений.Поставим в соответствие каждому из ответов определенное числовое значениепараметра х (см. табл. 1).

Табл. 1 Критериальный вопрос: успешное решение задач обучения и воспитания

№ п/п

Варианты ответов

Х

1

Абсолютно неуспешно

0,1

2

Неуспешно

0,2

3

Успешно в очень малой степени

0,3

4

В определенной степени успешно, но еще много недостатков

0,4

5

В среднем успешно, но недостатки имеются

0,5

6

Успешно с некоторыми оговорками

0,6

7

Успешно, но хотелось бы улучшить результат

0,7

8

Достаточно успешно

0,8

9

Очень успешно

0,9

10

Абсолютно успешно

1

При проведении анкетирования в каждой отдельнойанкете параметр х принимает случайное значение, но только в пределах числовогоинтервала от 0,1 до 1.

Тогда в результате измерений мы получаемнеранжированный ряд случайных значений (см. табл. 2).

Таблица 2.Результаты опроса ста учителей

tab.gif

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

f.gif

где

W(x) – относительная частота наступления события;

f1.gif

         

– стандартноеотклонение;

f2.gif

         

=3,14.

Постройте график функции f(x) и сравните его снормальным распределением Гаусса.

Решение математических задачсредствами Excel: Практикум/ В.Я. Гельман. – СПб.: Питер, 2003 – с. 168-172

Создание графиков

График – та же зависимость одной величины от другой, но представленная в виде точек, соединенных между собой линией. Точки могут быть видны или линия может быть ровной и сплошной. Создание и оформление графика аналогично гистограмме. Вкладка «Вставка» -> выделяем таблицу с данными -> «График».

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

Круговые диаграммы для иллюстрации распределения

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

С помощью любой круговой диаграммы можно показать распределение в том случае, если

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

На основании имеющихся данных о количестве осадков построим круговую диаграмму.

Количество осадков.

Доля «каждого месяца» в общем количестве осадков за год:

Доли.

Круговая диаграмма распределения осадков по сезонам года лучше смотрится, если данных меньше. Найдем среднее количество осадков в каждом сезоне, используя функцию СРЗНАЧ. На основании полученных данных построим диаграмму:

Пример3.

Получили количество выпавших осадков в процентном выражении по сезонам.

Что такое гистограмма или график распределения частот?

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

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

12 комментариев

Ренат, добрый день.
Все несколько проще:
Данные->Анализ данных->Генерация случайных чисел (Распределение=Нормальное)
+
Данные->Анализ данных->Гистограмма->Галка на «вывод графика» («Карманы» можно даже не задавать)

fit_757_530_false_crop_915_640_0_0_q90_100042_2ac1003946.jpeg

Нормальное распределение для введенного среднего значения и стандартного отклонения можно легко рассчитать и отобразить в таблицах Excel, например, для тестирования гипотезы.

Звучит заумно, но на деле все просто. Заполните ячейки от А1 до А11 исходными данными — в примере числами от 0 до 100 с шагом в десять. Выделите ячейку В1, откройте вкладку «Формулы» и щелкните по кнопке «Вставить функцию».

2016-02-09-2-e1455016442198.jpgВыбор статистической формулы. Для отображения нормального распределения в Excel предусмотрена функция «НОРМ.РАСП».

В качестве категории выберите значение «Статистические», в качестве функции — «НОРМ.РАСП». Подтвердите выбор, нажав кнопку ОК. Откроется новое окно. В строку «Х» введите значение «A1», в строку «Интегральная» — значение «0». Среднее составит «50», стандартное отклонение же можно свободно выбирать.

2016-02-09-4-e1455016461602.jpg

Когда вы закроете окно, Excel отобразит первое значение в ячейке B1. Теперь потяните за правый нижний угол ячейки вниз, затем выделите все значения — то есть ячейки от A1 до B11.

2016-02-09-6-e1455016483359.jpg

На вкладке «Вставка» в группе «Диаграммы» в разделе «Точечная» вы найдете несколько диаграмм, на которых можно отобразить нормальное распределение.

Фото: компания-производитель

Диаграмма онлайн

Для тех, кто не может правильно построить гистограмму, на помощь приходят онлайн-сервисы. Например, сайт OnlineCharts.

Нажав на кнопку «Создайте Вашу диаграмму», вы увидите огромное количество различных настроек, благодаря которым сможете нарисовать что хотите.

Полученный результат легко скачать себе на компьютер.

Рейтинг
( 1 оценка, среднее 5 из 5 )
Загрузка ...