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

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.

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

Сделаем ещё одно замечание для общего понимания. В английском языке есть два названия для среднего значения: MEAN и AVERAGE. В надстройке используется слово MEAN, а в названиях функций AVERAGE. За этим тоже придётся следить, чтобы не запутаться. Скорее всего, разные модули программы разрабатывали разные специалисты и в разное время. Потому и появились разные названия для одного и того же показателя в разных частях одного и того же пакета программ.

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

Скопируем столбец исходной выборки и вставим его на новый лист. При вставке из буфера выбираем вариант

Values

Значения

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

Вставка значений из буфера

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