Валентин Арьков - Анализ распределения в 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 — вероятность. В нашем случае это равномерно распределённая величина.