Анализ распределения в Excel

Попробуем оценить зависимость размаха от объёма выборки. Используем нормальное распределение с нашими параметрами среднего и сигмы. Сгенерируем выборку размером в миллион значений. Первое, что мы обнаруживаем, — ограничение встроенного генератора случайных чисел надстройки 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

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

STDEV. S

СТАНДОТКЛОН

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

MAX

МАКС

Минимальное значение

MIN

МАКС

Размах вариации — разность максимального и минимального значений: =F14-F13.

Коэффициент асимметрии:

SKEW

СКОС

Коэффицииент эксцесса:

KURT

ЭКСЦЕСС

Среднее линейное отклонение:

AVEDEV

СРОТКЛ

Объем выборки:

COUNT

СЧЕТ

Медиана:

MEDIAN

МЕДИАНА

Мода:

MODE

МОДА

Сумма:

SUM

СУММ

Здесь нужно дать небольшой комментарий про генеральные и выборочные характеристики. Генеральные показатели относятся к генеральной совокупности. Это всё множество значений. Можно даже скачать: бесконечное множество. Здесь расчёты делают по формуле «деление на n». Генеральная совокупность по-английски называется POPULATION — буквально «население страны». Поэтому в названии функций ставится английская буква P. Она же случайно перекочевала в русское название соответствующих функций.

Выборочные показатели относятся к выборочной совокупности. Это выборка значений из генеральной совокупности. Здесь расчёты делают по формуле «деление на n — 1». Выборка по-английски называется SAMPLE — «образец, проба, замер, выборка». Поэтому в названии функций есть буква S. В русском переводе эта буква потерялась, что немного сбивает с толку. Так что следите за названиями и за смыслом.

«Минус один» в знаменателе — это поправка на ограниченный объём выборки. Подробности объясняются в курсе математической статистики. Для нас важно одно: при небольшой выборке эта разница будет заметной, и нам нужно использовать деление на n — 1.