Расчет дисперсии, среднеквадратичного (стандартного) отклонения, коэффициента вариации в Excel

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

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

Начнем с формул максимума и минимума. Максимум – самое большое значение из анализируемого набора данных, минимум – самое маленькое. Это крайние значения в совокупности данных, обозначающие границы их вариации. Например, минимальные/максимальные цены на что-нибудь, выбор наилучшего или наихудшего решения задачи и т.д.

Для расчета этих показателей есть специальные функции — МАКС и МИН соответственно. Доступ есть прямо из ленты, в выпадающем списке авосумммы.

Максимум и минимум на ленте Excel

Если использовать вставку функций, то следует обратиться к категории «Статистические».

Функции максимум и минимум

 

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

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

Среднее линейное отклонение представляет собой среднее из абсолютных (по модулю) отклонений от средней арифметической в анализируемой совокупности данных. Математическая формула имеет вид:

Формула среднего линейного отклонения

где

a – среднее линейное отклонение,

X – анализируемый показатель,

– среднее значение показателя,

n – количество значений в анализируемой совокупности данных.

В Эксель эта функция называется СРОТКЛ.

Функция среднего линейного отклонения в Excel

После выбора функции СРОТКЛ указываем диапазон данных, по которому должен произойти расчет. Нажимаем «ОК».

Дисперсия

{module 111}

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

Формула дисперсии

где

s2 – выборочная дисперсия, рассчитанная по данным наблюдений,

X – отдельные значения,

– среднее арифметическое по выборке,

n – количество значений в анализируемой совокупности данных.

Соответствующая функция Excel — ДИСП.Г. При анализе относительно небольших выборок (примерно до 30-ти наблюдений) следует использовать несмещенную выборочную дисперсию, которая рассчитывается по следующей формуле.

Формула выборочной несмещенной дисперсии

Отличие, как видно, только в знаменателе. В Excel для расчета выборочной несмещенной дисперсии есть функция ДИСП.В.

Функции Excel для расчета дисперсии

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

Среднеквадратичное отклонение

Среднеквадратичное отклонение (СКО) – это корень из дисперсии. Этот показатель также называют стандартным отклонением и рассчитывают по формуле:

по генеральной совокупности

Среднеквадратичное отклонение по генеральной совокупности

по выборке

Среднеквадратичное отклонение по выборке

Можно просто извлечь корень из дисперсии, но в Excel для среднеквадратичного отклонения есть готовые функции: СТАНДОТКЛОН.Г и СТАНДОТКЛОН.В (по генеральной и выборочной совокупности соответственно).

Среднеквадратичное (стандартное) отклонение в Excel

Стандартное и среднеквадратичное отклонение, повторюсь, — синонимы.

Далее, как обычно, указываем нужный диапазон и нажимаем на «ОК». Среднеквадратическое отклонение имеет те же единицы измерения, что и анализируемый показатель, поэтому является сопоставимым с исходными данными. Об этом ниже.

Коэффициент вариации

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

Формула коэффициента вариации

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

=СТАНДОТКЛОН.Г()/СРЗНАЧ()

В скобках указывается диапазон данных. При необходимости используют среднее квадратичное отклонение по выборке (СТАНДОТКЛОН.В).

Коэффициент вариации обычно выражается в процентах, поэтому ячейку с формулой можно обрамить процентным форматом. Нужная кнопка находится на ленте на вкладке «Главная»:

Процентный формат

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

Коэффициент вариации, в отличие от других показателей разброса значений, используется как самостоятельный и весьма информативный индикатор вариации данных. В статистике принято считать, что если коэффициент вариации менее 33%, то совокупность данных является однородной, если более 33%, то – неоднородной. Эта информация может быть полезна для предварительного описания данных и определения возможностей проведения дальнейшего анализа. Кроме того, коэффициент вариации, измеряемый в процентах, позволяет сравнивать степень разброса различных данных независимо от их масштаба и единиц измерений. Полезное свойство.

Коэффициент осцилляции

Еще один показатель разброса данных на сегодня — коэффициент осцилляции. Это соотношение размаха вариации (разницы между максимальным и минимальным значением) к средней. Готовой формулы Excel нет, поэтому придется скомпоновать три функции: МАКС, МИН, СРЗНАЧ.

Коэффициент осцилляции в Excel

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

В целом, с помощью Excel многие статистические показатели рассчитываются очень просто. Если что-то непонятно, всегда можно воспользоваться окошком для поиска во вставке функций. Ну, и Гугл в помощь.

А сейчас предлагаю посмотреть видеоурок.

Легкой работы в Excel и до встречи на блоге statanaliz.info.

Поделиться в социальных сетях:
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •