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

Как сделать столбчатый график в excel?

1. На основе структурной группировки построить вариационный частотный и кумулятивный ряды распределения, используя равные закрытые интервалы, приняв число групп равным 6. Результаты представить в виде таблицы и изобразить графически.
2. Проанализировать вариационный ряд распределения, вычислив:
   • среднее арифметическое значение признака;
   • моду, медиану, 1-ый квартиль, 1-ый и 9-тый дециль;
   • среднее квадратичное отклонение;
   • коэффициент вариации.
3. Сделать выводы.

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

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

Очень давно не писал блог. Расслабился совсем. Ну ничего, исправляюсь.

Продолжаю новую рубрику блога, посвященную анализу данных с помощью всем известного Microsoft Excel.

Статистический анализ в Excel можно осуществлять двумя способами: • С помощью функций • С помощью средств надстройки «Пакет анализа». Ее, как правило, еще необходимо установить.

Чтобы установить пакет анализа в Excel, выберите вкладку «Файл» (а в Excel 2007 это круглая цветная кнопка слева сверху), далее — «Параметры», затем выберите раздел «Надстройки». Нажмите «Перейти» и поставьте галочку напротив «Пакет анализа».

А теперь — к построению гистограмм распределения по частоте и их анализу.

Речь пойдет именно о частотных гистограммах, где каждый столбец соответствует частоте появления* значения в пределах границ интервалов. Например, мы хотим посмотреть, как у нас выглядит распределение значения предела текучести стали S355J2 в прокате толщиной 20 мм за несколько месяцев. В общем, хотим посмотреть, похоже ли наше распределение на нормальное (а оно должно быть таким).

*Примечание: для металловедческих целей типа оценки размера зерна или оценки объемной доли частиц этот вид гистограмм не пойдет, т.к. там высота столбика соответствует не частоте появления частиц определенного размера, а доле объема (а в плоскости шлифа — площади), которую эти частицы занимают.

График нормального распределения выглядит следующим образом:

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

Построение гистограмм с помощью программ типа Excel является очень быстрым способом проверки стабильности работы оборудования и добросовестности коллектива: если получим «кривую» гистограмму, значит, либо прибор не исправен или мы данные неверно собрали, либо кто-то где-то преднамеренно мухлюет или же просто неверно использует оборудование.

1. Идем во вкладку «Анализ данных» и выбираем «Гистограмма».
2. Выбираем входной интервал.
3. Здесь же предлагается задать интервал карманов, т.е. те диапазоны, в пределах которых будут лежать наши значения. Чем больше значений в интервале — тем выше столбик гистограммы. Если мы оставим поле «Интервалы карманов» пустым, то программа вычислит границы интервалов за нас.
4. Если хотим сразу же вывести график,то ставим галочку напротив «Вывод графика».
5. Нажимаем «ОК».
6. Вот, вроде бы, и все: гистограмма готова. Теперь нужно сделать так, чтобы по вертикальной оси отображалась не абсолютная частота, а относительная.
7. Под появившейся таблицей со столбцами «Карман» и «Частота» под столбцом «Частота» введем формулу «=СУММ» и сложим все абсолютные частоты.
8. К появившейся таблице со столбцами «Карман» и «Частота» добавим еще один столбец и назовем его «Относительная частота».
9. Во всех ячейках нового столбца введем формулу, которая будет рассчитывать относительную частоту: 100 умножить на абсолютную частоту (ячейка из столбца «частота») и разделить на сумму, которую мы вычислил в п. 7.

Будет полезен тому, кто по каким-либо причинам не смог установить Пакет анализа.

Если вы нашли ошибку, пожалуйста, выделите фрагмент текста и нажмите Ctrl+Enter.

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

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

Диаграмма нормального распределения (Гаусса) в Excel

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

1. В ячейку A3 введем символ х, а в ячейку ВЗ — символ функции плотности вероятности f(x).

2. Вычислим нижнюю М — За границу диапазона значений х, для чего установим курсор в ячейку С2 и введем формулу =0-3*1, а также верхнюю границу — в ячейку Е2 введем формулу =0+3*1.

3. Скопируем формулу из ячейки С2 в ячейку А4, полученное в ячейке А4 значение нижней границы будет началом последовательности арифметической прогрессии.

4. Создадим последовательность значений х в требуемом диапазоне, для чего установим курсор в ячейку А4 и выполним команду меню Правка/Заполнить/Прогрессия.

5. В открывшемся окне диалога Прогрессия установим переключатели арифметическая, по столбцам, в поле Шаг введем значение 0,5, а в поле Предельное значение — число, равное верхней границе диапазона.

Функция НОРМРАСПР в EXCEL

6. Щелкнем на кнопке ОК. В диапазоне А4:А16 будет сформирована последовательность значений х.

7. Установим курсор в ячейку В4 и выполним команду меню Вставка/Функция. В открывшемся окне Мастер функций выберем категорию Статистические, а в списке функций — НОРМРАСП.

8. Установим значения параметров функции НОРМРАСП: для параметра х установим ссылку на ячейку А4, для параметра Среднее — введем число 0, для параметра Стандартное_откл — число 1, для параметра Интегральное — число 0 (весовая).

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

9. Используя маркер буксировки, скопируем полученную формулу в диапазон ячеек В5:В16.

10. Выделим диапазон полученных табличных значений функции f(х) (ВЗ:В16) и выполним команду меню Вставка/Диаграмма. В окне Мастер диаграмм во вкладке Стандартные выберем График, а в поле Вид — вид графика, щелкнем на кнопке Далее.

11. В окне Мастер диаграмм (шаг 2) выберем закладку Ряд. В поле Подписи оси х укажем ссылку на диапазон, содержащий значения х (А4:А16). Щелкнем на кнопке Далее.В окне Мастер диаграмм (шаг 3) введем подписи: Название диаграммы, Ось х, Ось у. Щелкнем на кнопке Готово. На рабочий лист будет выведена диаграмма плотности вероятности .

Определение границ интервала с двух сторон

Для расчета границ интервала необходимо узнать, каково среднее значение по нему, с помощью функции СРЗНАЧ.

1. Откроем «Менеджер функций» и выберем нужный оператор в разделе «Статистические».

14

2. Добавляем группу ячеек, содержащих значения, в поле первого аргумента и жмем кнопку «ОК».

15

3. Теперь можно определить правую и левую границу. Понадобятся простые математические действия. Расчет правой границы: выбираем пустую ячейку, проводим в ней сложение ячеек с доверительным интервалом и средним значением.

16

4. Для определения левой границы из среднего значения нужно вычесть доверительный интервал.

17

5. Такие же операции проделываем с доверительным интервалом по Стьюденту. В результате получаем границы интервала в двух вариантах.

18

Шаг № 1: Найдите среднее значение.

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

Поскольку среднее значение указывает среднее значение выборки или совокупности данных, вы можете найти стандартное измерение, используя функцию СРЕДНЕЕ.

Введите следующую формулу в любую пустую ячейку (F1 в этом примере) рядом с вашими фактическими данными (столбцы A а также B), чтобы вычислить среднее значение экзаменационных баллов в наборе данных:

1	= СРЕДНИЙ (B2: B201)

Небольшое примечание: чаще всего вам может потребоваться округлить вывод формулы в большую сторону. Для этого просто оберните его функцией ROUND следующим образом:

1	= ОКРУГЛ (СРЕДНИЙ (B2: B201); 0)

Шаг № 9: Измените масштаб горизонтальной оси.

Отцентрируйте диаграмму на колоколообразной кривой, отрегулировав масштаб горизонтальной оси. Щелкните правой кнопкой мыши горизонтальную ось и выберите «Ось формата»Из меню.

Когда появится панель задач, сделайте следующее:

• Перейти к Параметры оси таб.
• Установить Минимальные границы значение «15.”
• Установить Максимальные границы значение «125.”

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

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

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

Коэффициент асимметрии
численно характеризует симметрию эмпирического распределения относительно среднего. Если коэффициент
асимметрии равен нулю, то среднее арифметрического значение, медиана и мода равны:
и кривая плотности
распределения симметрична относительно среднего. Если коэффициент асимметрии меньше нуля (),
то среднее арифметическое меньше медианы, а медиана, в свою очередь, меньше моды
() и кривая сдвинута
вправо (по сравнению с нормальным распределением). Если коэффициент асимметрии больше нуля (),
то среднее арифметическое больше медианы, а медиана, в свою очередь, больше моды
() и кривая сдвинута
влево (по сравнению с нормальным распределением).

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

Коэффициент асимметрии можно вычислить с помощью функции MS Excel СКОС. Если вы
проверяете один массив данных, то требуется ввести диапазон данных в одно окошко «Число».

Коэффициент эксцесса можно вычислить с помощью функции MS Excel ЭКСЦЕСС. При проверке
одного массива данных также достаточно ввести диапазон данных в одно окошко «Число».

Итак, как мы уже знаем, при нормальном распределении коэффициенты асимметрии и эксцесса
равны нулю. Но что, если мы получили коэффициенты асимметрии, равные -0,14, 0,22, 0,43, а коэффициенты
эксцесса, равные 0,17, -0,31, 0,55? Вопрос вполне справедливый, так как практически мы имеем дело лишь с
приближенными, выборочными значениями асимметрии и эксцесса, которые подвержены некоторому неизбежному,
неконтролируемому разбросу. Поэтому нельзя требовать строгого равенства этих коэффициентов нулю, они
должны лишь быть достаточно близкими к нулю. Но что значит — достаточно?

Требуется сравнить полученные эмпирические значения с
допустимыми значениями. Для этого нужно проверить следующие неравенства (сравнить значения коэффициентов
по модулю с критическими значениями — границами области проверки гипотезы).

Для коэффициента асимметрии :

,

где

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

—
среднеквадратическое отклонение для выборки с числом наблюдений .

Для коэффициента эксцесса :

,

где

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

—
среднеквадратическое отклонение для выборки с числом наблюдений .

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

• 0,90: 1,645
• 0,95: 1,960
• 0,975: 2,241
• 0,98: 2,326
• 0,99: 2,576
• 0,995: 2,807
• 0,999: 3,291
• 0,9995: 3,481
• 0,9999: 3,891

Например, для выборки с числом наблюдений и
,
пользуясь этими значениями и ранее приведёнными формулами, можно получить границу области принятия гипотезы для
коэффициента асимметрии 0,62 и для коэффициента эксцесса 1,15. Поэтому приведённые ранее примеры эмпирических
значений коэффициента асимметрии -0,14, 0,22, 0,43 попадают в область принятия гипотезы. То же самое
относится к значениям коэффициента эксцесса 0,17, -0,31, 0,55. Следовательно, если получены такие
эмпирические значения, то с вероятностью 95% данные выборки подчиняются нормальному закону распределения.

Нормальное распределение в статистике

История закона насчитывает 300 лет. Первым открывателем стал Абрахам де Муавр, который придумал аппроксимацию биномиального распределения еще 1733 году. Через много лет Карл Фридрих Гаусс (1809 г.) и Пьер-Симон Лаплас (1812 г.) вывели математические функции.

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

Нормальный закон не является фиксированным уравнением зависимости одной переменной от другой. Фиксируется только характер этой зависимости. Конкретная форма распределения задается специальными параметрами. Например, у = аx + b – это уравнение прямой. Однако где конкретно она проходит и под каким наклоном, определяется параметрами а и b. Также и с нормальным распределением. Ясно, что это функция, которая описывает тенденцию высокой концентрации значений около центра, но ее точная форма задается специальными параметрами.

Кривая нормального распределения Гаусса имеет следующий вид.

График нормального распределения напоминает колокол, поэтому можно встретить название колоколообразная кривая. У графика имеется «горб» в середине и резкое снижение плотности по краям. В этом заключается суть нормального распределения. Вероятность того, что случайная величина окажется около центра гораздо выше, чем то, что она сильно отклонится от середины.

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

Формула нормального распределения (плотности) следующая.

Формула состоит из двух математических констант:

π – число пи 3,142;

е – основание натурального логарифма 2,718;

двух изменяемых параметров, которые задают форму конкретной кривой:

m – математическое ожидание (в различных источниках могут использоваться другие обозначения, например, µ или a);

ну и сама переменная x, для которой высчитывается плотность вероятности.

Конкретная форма нормального распределения зависит от 2-х параметров: математического ожидания (m) и дисперсии (σ 2 ). Кратко обозначается N(m, σ 2 ) или N(m, σ). Параметр m (матожидание) определяет центр распределения, которому соответствует максимальная высота графика. Дисперсия σ 2 характеризует размах вариации, то есть «размазанность» данных.

Параметр математического ожидания смещает центр распределения вправо или влево, не влияя на саму форму кривой плотности.

А вот дисперсия определяет остроконечность кривой. Когда данные имеют малый разброс, то вся их масса концентрируется у центра. Если же у данных большой разброс, то они «размазываются» по широкому диапазону.

Плотность распределения не имеет прямого практического применения. Для расчета вероятностей нужно проинтегрировать функцию плотности.

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

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

P(a ≤ X 0 =1 и остается рассчитать только соотношение 1 на корень из 2 пи.

Таким образом, по графику хорошо видно, что значения, имеющие маленькие отклонения от средней, выпадают чаще других, а те, которые сильно отдалены от центра, встречаются значительно реже. Шкала оси абсцисс измеряется в стандартных отклонениях, что позволяет отвязаться от единиц измерения и получить универсальную структуру нормального распределения. Кривая Гаусса для нормированных данных отлично демонстрирует и другие свойства нормального распределения. Например, что оно является симметричным относительно оси ординат. В пределах ±1σ от средней арифметической сконцентрирована большая часть всех значений (прикидываем пока на глазок). В пределах ±2σ находятся большинство данных. В пределах ±3σ находятся почти все данные. Последнее свойство широко известно под названием правило трех сигм для нормального распределения.

Функция стандартного нормального распределения позволяет рассчитывать вероятности.

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