Валентин Арьков - Анализ распределения в Excel

Статистические показатели

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

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

Названия показателей

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

Mean( Среднее значение) — среднее арифметическое исходных значений — см. формулу.

Расчётные формулы

Для нашего варианта среднее задано равным 250.

Standard Error( Стандартная ошибка) — погрешность оценки среднего значения по выборке. В данной работе не используем.

Sample Variance( Выборочная дисперсия) — рассчитывается по формуле «деление на n — 1» — см. формулу.

Для нашего варианта дисперсия равна квадрату «сигмы»:

D = 400

Standard Deviation ( Стандартное отклонение) — показатель разброса вокруг среднего значения. Определяется как квадратный корень из дисперсии. Другие названия: «сигма», среднее квадратическое отклонение (с.к.о.) — см. формулу.

Для нашего варианта сигма задана равной 20.

Kurtosis( Эксцесс) — характеристика «островершинности» распределения. Показывает, насколько острой или плоской является вершины кривой распределения.

Для нормального распределения эксцесс равен

Е = 1

Skewness( Асимметрия) — показатель несимметричности кривой распределения.

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

А = 0

Range ( Размах вариации) — диапазон значений от минимального до максимального значений, разность максимального и минимального значений:

R = max ( x ) — min ( x )

Minimum (Минимальное значение)

Maximum (Максимальное значение)

Возможный разброс значений оценим по «правилу трёх сигм»: среднее плюс-минус три сигмы:

min = 250 — 3*20 = 190

max = 250 +3*20 = 310

Range (Размах)

R = max — min = 310 — 190 = 120

Sum (Сумма значений)

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

Чтобы понять, почему именно так, — вспомните формулу для вычисления среднего арифметического.

Count (Количество значений)

объём выборки n .

«Правило трёх сигм» на самом деле очень приблизительное. Оно даёт хорошее приближение только для определённого объёма выборки. Конечно, есть теория, которая предлагает красивую многоэтажную формулу для распределения показателя размаха вариации. Мы поступим попроще и пойдём путём практического знакомства.

Нас интересует, как размах значений зависит от объёма выборки. Чем больше выборка, тем больше шанс, что может появиться очень редкое значение, которое сильно отклонится от среднего. Гораздо дальше, чем на три сигмы.

Попробуем оценить зависимость размаха от объёма выборки. Используем нормальное распределение с нашими параметрами среднего и сигмы. Сгенерируем выборку размером в миллион значений. Первое, что мы обнаруживаем, — ограничение встроенного генератора случайных чисел надстройки Excel: Integer is not valid. Миллион чисел сгенерировать в надстройке не удаётся.

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

Размах в сигмах

Построим график: объём выборки — размах в сигмах.

Размах и объём выборки

Рассмотрим начало графика поподробнее. Для этого используем логарифмический масштаб. Вместо объёма выборки используем его логарифм. Вставим новый столбец и вычислим lg ( n ). Здесь нам пригодится функция LOG10.

Логарифмический масштаб

На графике видно несколько ступенек. Скорее всего, это вызвано недостаточным качеством псевдослучайных чисел. Тем не менее, общая картина просматривается.

При выборке 10 размах равен трём сигмам. При выборке 100 размах 6 сигм. При выборке 10 000 размах равен 13 сигм.

Пользуясь случаем, проверим качество другого генератора случайных чисел Excel. Создадим новый лист и повторим наш эксперимент. Используем метод преобразования — возьмём равномерное распределение и пропустим его через обратное нормальное распределение.

Функция

RAND ()

СЛЧИС ()

позволяет сгенерировать случайное число с равномерным распределением в интервале от 0 до 1. Аргументов у функции нет.

Чтобы из равномерного распределения получить нормальное, вызываем функцию NORM.INV. Формат вызова:

=NORM.INV (probability, mean, standard_dev)

=НОРМ. ОБР (вероятность; среднее; стандартное_откл)

Функция работает по принципу x (p). Это обратное преобразование для функции распределения p (x).

probability — вероятность. В нашем случае это равномерно распределённая величина.

mean — среднее. В нашем примере это 250.

standard_dev — с.к. о. В нашем варианте это 20.

Таким образом, вызываем функция со следующими параметрами

=NORM.INV (B2,250,20)

Используем логарифмический масштаб, как в предыдущем варианте.

Размах в сигмах

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

Запуски генератора

Графики немного отличаются друг от друга. Но при этом общая картина зависимости сохраняется. Чем больше выборка, тем больше размах.

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

Многие статистические показатели можно получить с помощью функций Excel.

Добавим столбец Формулык нашей таблице показателей. Для каждой функции указываем диапазон ячеек исходной выборки. Добавим строки для новых функций.

Среднее значение:

AVERAGE

СРЗНАЧ

Генеральная дисперсия:

VAR. P

ДИСПP

Выборочная дисперсия:

VAR. S

ДИСП

Генеральное стандартное отклонение:

STDEV. P

СТАНДОТКЛОНP

Выборочное стандартное отклонение: