Расчет среднего квадратичного отклонения в Microsoft Excel

Среднее квадратичное отклонение формула Excel

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

Среднее квадратичное отклонение — это величина квадратного корня, вычисляемого из среднего арифметического числа квадратов разности всех величин и их среднего арифметического. Также, формулу называют стандартным отклонением.

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

Метод 1: мастер функций

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

Появится окно «Мастер функций», где в списке под «Выберите функцию» отметьте СТАНДОТКЛОН.В или СТАНДОТКЛОН.Г. Кликните на «ОК».

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

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

Метод 2: формулы

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

В интерфейсе настроек кликните на «Библиотеку функций», которая обозначена двумя оранжевыми книжками. Выберите в перечне «Статистические». Появится ещё один список, где кликните на СТАНДОТКЛОН.В или СТАНДОТКЛОН.Г.

Примечание: если в списке библиотек нет статистических, то нажмите «Другие функции», где уже будет находиться нужный раздел.

Появится окно «Аргументы функции», где вводим координаты ячеек для расчета.

Метод 3: самостоятельный ввод формулы

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

=СТАНДОТКЛОН.Г(число1(адрес_ячейки1); число2(адрес_ячейки2);…)
или
=СТАНДОТКЛОН.Г(число1(адрес_ячейки1); число2(адрес_ячейки2);…)

После введения выбранной вами формулы, кликните «ENTER» и результат выведется автоматически.

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

Разбираем формулы среднеквадратического отклонения и дисперсии в Excel

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

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

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

Рассматривая формулы моделей прогнозирования, мы встретимся со следующими показателями:

Например, у нас есть временной ряд — продажи по неделям в шт.

Для этого временного ряда i=1, n=10 , ,

Рассмотрим формулу среднего значения:

Для нашего временного ряда определим среднее значение

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

Формула расчета среднеквадратического отклонение для выборки следующая:

Разложим формулу на составные части и рассчитаем среднеквадратическое отклонение в Excel на примере нашего временного ряда.

1. Рассчитаем среднее значение для этого воспользуемся формулой Excel =СРЗНАЧ(B11:K11)

= СРЗНАЧ(ссылка на диапазон) = 100/10=10

2. Определим отклонение каждого значения ряда относительно среднего

для первой недели = 6-10=-4

для второй недели = 10-10=0

для третей = 7-1=-3 и т.д.

3. Для каждого значения ряда определим квадрат разницы отклонения значений ряда относительно среднего

для первой недели = (-4)^2=16

для второй недели = 0^2=0

для третей = (-3)^2=9 и т.д.

4. Рассчитаем сумму квадратов отклонений значений относительно среднего с помощью формулы =СУММ(ссылка на диапазон (ссылка на диапазон с )

=16+0+9+4+16+16+4+9+0+16=90

5. , для этого сумму квадратов отклонений значений относительно среднего разделим на количество значений минус единица (Сумма((Xi-Xср)^2))/(n-1)

= 90/(10-1)=10

6. Среднеквадратическое отклонение равно = корень(10)=3,2

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

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

Как рассчитать дисперсию в Excel?

Дисперсия — квадрат среднеквадратического отклонения и отражает разброс данных относительно среднего.

Рассчитаем дисперсию:

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

Точных вам прогнозов!

Присоединяйтесь к нам!

Скачивайте бесплатные приложения для прогнозирования и бизнес-анализа:

  • Novo Forecast Lite — автоматический расчет прогноза в Excel .
  • 4analytics — ABC-XYZ-анализ и анализ выбросов в Excel.
  • Qlik Sense Desktop и QlikView Personal Edition — BI-системы для анализа и визуализации данных.

Тестируйте возможности платных решений:

  • Novo Forecast PRO — прогнозирование в Excel для больших массивов данных.

Получите 10 рекомендаций по повышению точности прогнозов до 90% и выше.

Расчет среднего квадратичного отклонения в Microsoft Excel

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

Определение среднего квадратичного отклонения

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

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

Расчет в Excel

Рассчитать указанную величину в Экселе можно с помощью двух специальных функций СТАНДОТКЛОН.В (по выборочной совокупности) и СТАНДОТКЛОН.Г (по генеральной совокупности). Принцип их действия абсолютно одинаков, но вызвать их можно тремя способами, о которых мы поговорим ниже.

Способ 1: мастер функций

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

В открывшемся списке ищем запись СТАНДОТКЛОН.В или СТАНДОТКЛОН.Г. В списке имеется также функция СТАНДОТКЛОН, но она оставлена из предыдущих версий Excel в целях совместимости. После того, как запись выбрана, жмем на кнопку «OK».

  • Результат расчета будет выведен в ту ячейку, которая была выделена в самом начале процедуры поиска среднего квадратичного отклонения.
  • Способ 2: вкладка «Формулы»

    Также рассчитать значение среднеквадратичного отклонения можно через вкладку «Формулы».

      Выделяем ячейку для вывода результата и переходим во вкладку «Формулы».

    Читайте также  Применение функции СЧЕТ в Microsoft Excel

  • После этого запускается окно аргументов. Все дальнейшие действия нужно производить так же, как и в первом варианте.
  • Способ 3: ручной ввод формулы

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

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

    =СТАНДОТКЛОН.Г(число1(адрес_ячейки1); число2(адрес_ячейки2);…)
    или
    =СТАНДОТКЛОН.В(число1(адрес_ячейки1); число2(адрес_ячейки2);…).

    Всего можно записать при необходимости до 255 аргументов.

  • После того, как запись сделана, нажмите на кнопку Enter на клавиатуре.
  • Как видим, механизм расчета среднеквадратичного отклонения в Excel очень простой. Пользователю нужно только ввести числа из совокупности или ссылки на ячейки, которые их содержат. Все расчеты выполняет сама программа. Намного сложнее осознать, что же собой представляет рассчитываемый показатель и как результаты расчета можно применить на практике. Но постижение этого уже относится больше к сфере статистики, чем к обучению работе с программным обеспечением.

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

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

    Дисперсия

    Дисперсия случайной величины – это один из основных показателей в статистике. Он отражает меру разброса данных вокруг средней арифметической.

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

    Формула дисперсии в теории вероятностей имеет вид:

    То есть дисперсия — это математическое ожидание отклонений от математического ожидания.

    На практике при анализе выборок математическое ожидание, как правило, не известно. Поэтому вместо него используют оценку – среднее арифметическое. Расчет дисперсии производят по формуле:

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

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

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

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

    Простыми словами дисперсия – это средний квадрат отклонений. То есть вначале рассчитывается среднее значение, затем берется разница между каждым исходным и средним значением, возводится в квадрат, складывается и затем делится на количество значений в данной совокупности. Разница между отдельным значением и средней отражает меру отклонения. В квадрат возводится для того, чтобы все отклонения стали исключительно положительными числами и чтобы избежать взаимоуничтожения положительных и отрицательных отклонений при их суммировании. Затем, имея квадраты отклонений, просто рассчитываем среднюю арифметическую. Средний – квадрат – отклонений. Отклонения возводятся в квадрат, и считается средняя. Теперь вы знаете, как найти дисперсию.

    Расчет дисперсии в Excel

    Генеральную и выборочную дисперсии легко рассчитать в Excel. Есть специальные функции: ДИСП.Г и ДИСП.В соответственно.

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

    Свойства дисперсии

    Свойство 1. Дисперсия постоянной величины A равна (нулю).

    Свойство 2. Если случайную величину умножить на постоянную А, то дисперсия этой случайной величины увеличится в А 2 раз. Другими словами, постоянный множитель можно вынести за знак дисперсии, возведя его в квадрат.

    Свойство 3. Если к случайной величине добавить (или отнять) постоянную А, то дисперсия останется неизменной.

    Свойство 4. Если случайные величины X и Y независимы, то дисперсия их суммы равна сумме их дисперсий.

    Свойство 5. Если случайные величины X и Y независимы, то дисперсия их разницы также равна сумме дисперсий.

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

    Если из дисперсии извлечь квадратный корень, получится среднеквадратичное (стандартное) отклонение (сокращенно СКО). Встречается название среднее квадратичное отклонение и сигма (от названия греческой буквы). Общая формула стандартного отклонения в математике следующая:

    На практике формула стандартного отклонения следующая:

    Как и с дисперсией, есть и немного другой вариант расчета. Но с ростом выборки разница исчезает.

    Расчет cреднеквадратичного (стандартного) отклонения в Excel

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

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

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

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

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

    Расчет коэффициента вариации в Excel

    Расчет коэффициента вариации в Excel также производится делением стандартного отклонения на среднее арифметическое:

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

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

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

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

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

    Ниже видео о том, как посчитать коэффициент вариации, дисперсию, стандартное (среднеквадратичное) отклонение и другие показатели вариации в Excel.

    Как найти среднее арифметическое число в Excel

    Для того чтобы найти среднее значение в Excel (при том неважно числовое, текстовое, процентное или другое значение) существует много функций. И каждая из них обладает своими особенностями и преимуществами. Ведь в данной задаче могут быть поставлены определенные условия.

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

    Как найти среднее арифметическое чисел?

    Чтобы найти среднее арифметическое, необходимо сложить все числа в наборе и разделить сумму на количество. Например, оценки школьника по информатике: 3, 4, 3, 5, 5. Что выходит за четверть: 4. Мы нашли среднее арифметическое по формуле: =(3+4+3+5+5)/5.

    Как это быстро сделать с помощью функций Excel? Возьмем для примера ряд случайных чисел в строке:

    1. Ставим курсор в ячейку А2 (под набором чисел). В главном меню – инструмент «Редактирование» — кнопка «Сумма». Выбираем опцию «Среднее». После нажатия в активной ячейке появляется формула. Выделяем диапазон: A1:H1 и нажимаем ВВОД.
    2. В основе второго метода тот же принцип нахождения среднего арифметического. Но функцию СРЗНАЧ мы вызовем по-другому. С помощью мастера функций (кнопка fx или комбинация клавиш SHIFT+F3).
    3. Третий способ вызова функции СРЗНАЧ из панели: «Формула»-«Формула»-«Другие функции»-«Статические»-«СРЗНАЧ».
    Читайте также  Составление сметы в Microsoft Excel

    Или: сделаем активной ячейку и просто вручную впишем формулу: =СРЗНАЧ(A1:A8).

    Теперь посмотрим, что еще умеет функция СРЗНАЧ.

    Найдем среднее арифметическое двух первых и трех последних чисел. Формула: =СРЗНАЧ(A1:B1;F1:H1). Результат:

    Среднее значение по условию

    Условием для нахождения среднего арифметического может быть числовой критерий или текстовый. Будем использовать функцию: =СРЗНАЧЕСЛИ().

    Найти среднее арифметическое чисел, которые больше или равны 10.

    Результат использования функции СРЗНАЧЕСЛИ по условию «>=10»:

    Третий аргумент – «Диапазон усреднения» — опущен. Во-первых, он не обязателен. Во-вторых, анализируемый программой диапазон содержит ТОЛЬКО числовые значения. В ячейках, указанных в первом аргументе, и будет производиться поиск по прописанному во втором аргументе условию.

    Внимание! Критерий поиска можно указать в ячейке. А в формуле сделать на нее ссылку.

    Найдем среднее значение чисел по текстовому критерию. Например, средние продажи товара «столы».

    Функция будет выглядеть так: =СРЗНАЧЕСЛИ($A$2:$A$12;A7;$B$2:$B$12). Диапазон – столбец с наименованиями товаров. Критерий поиска – ссылка на ячейку со словом «столы» (можно вместо ссылки A7 вставить само слово «столы»). Диапазон усреднения – те ячейки, из которых будут браться данные для расчета среднего значения.

    В результате вычисления функции получаем следующее значение:

    Внимание! Для текстового критерия (условия) диапазон усреднения указывать обязательно.

    Как посчитать средневзвешенную цену в Excel?

    Как посчитать средний процент в Excel? Для этой цели подойдут функции СУММПРОИЗВ и СУММ. Таблица для примера:

    Как мы узнали средневзвешенную цену?

    С помощью формулы СУММПРОИЗВ мы узнаем общую выручку после реализации всего количества товара. А функция СУММ — сумирует количесвто товара. Поделив общую выручку от реализации товара на общее количество единиц товара, мы нашли средневзвешенную цену. Этот показатель учитывает «вес» каждой цены. Ее долю в общей массе значений.

    Среднее квадратическое отклонение: формула в Excel

    Различают среднеквадратическое отклонение по генеральной совокупности и по выборке. В первом случае это корень из генеральной дисперсии. Во втором – из выборочной дисперсии.

    Для расчета этого статистического показателя составляется формула дисперсии. Из нее извлекается корень. Но в Excel существует готовая функция для нахождения среднеквадратического отклонения.

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

    среднеквадратическое отклонение / среднее арифметическое значение

    Формула в Excel выглядит следующим образом:

    СТАНДОТКЛОНП (диапазон значений) / СРЗНАЧ (диапазон значений).

    Коэффициент вариации считается в процентах. Поэтому в ячейке устанавливаем процентный формат.

    Дисперсия и стандартное отклонение в EXCEL

    Вычислим в MS EXCEL дисперсию и стандартное отклонение выборки. Также вычислим дисперсию случайной величины, если известно ее распределение.

    Сначала рассмотрим дисперсию , затем стандартное отклонение .

    Дисперсия выборки

    Дисперсия выборки ( выборочная дисперсия, sample variance ) характеризует разброс значений в массиве относительно среднего .

    Все 3 формулы математически эквивалентны.

    Из первой формулы видно, что дисперсия выборки это сумма квадратов отклонений каждого значения в массиве от среднего , деленная на размер выборки минус 1.

    В MS EXCEL 2007 и более ранних версиях для вычисления дисперсии выборки используется функция ДИСП() , англ. название VAR, т.е. VARiance. С версии MS EXCEL 2010 рекомендуется использовать ее аналог ДИСП.В() , англ. название VARS, т.е. Sample VARiance. Кроме того, начиная с версии MS EXCEL 2010 присутствует функция ДИСП.Г(), англ. название VARP, т.е. Population VARiance, которая вычисляет дисперсию для генеральной совокупности . Все отличие сводится к знаменателю: вместо n-1 как у ДИСП.В() , у ДИСП.Г() в знаменателе просто n. До MS EXCEL 2010 для вычисления дисперсии генеральной совокупности использовалась функция ДИСПР() .

    Дисперсию выборки можно также вычислить непосредственно по нижеуказанным формулам (см. файл примера ) =КВАДРОТКЛ(Выборка)/(СЧЁТ(Выборка)-1) =(СУММКВ(Выборка)-СЧЁТ(Выборка)*СРЗНАЧ(Выборка)^2)/ (СЧЁТ(Выборка)-1) – обычная формула =СУММ((Выборка -СРЗНАЧ(Выборка))^2)/ (СЧЁТ(Выборка)-1 ) – формула массива

    Дисперсия выборки равна 0, только в том случае, если все значения равны между собой и, соответственно, равны среднему значению . Обычно, чем больше величина дисперсии , тем больше разброс значений в массиве.

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

    Дисперсия случайной величины

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

    Для дисперсии случайной величины Х часто используют обозначение Var(Х). Дисперсия равна математическому ожиданию квадрата отклонения от среднего E(X): Var(Х)=E[(X-E(X)) 2 ]

    Если случайная величина имеет дискретное распределение , то дисперсия вычисляется по формуле:

    где x i – значение, которое может принимать случайная величина, а μ – среднее значение ( математическое ожидание случайной величины ), р(x) – вероятность, что случайная величина примет значение х.

    Если случайная величина имеет непрерывное распределение , то дисперсия вычисляется по формуле:

    Для распределений, представленных в MS EXCEL , дисперсию можно вычислить аналитически, как функцию от параметров распределения. Например, для Биномиального распределения дисперсия равна произведению его параметров: n*p*q.

    Примечание : Дисперсия, является вторым центральным моментом , обозначается D[X], VAR(х), V(x). Второй центральный момент — числовая характеристика распределения случайной величины, которая является мерой разброса случайной величины относительно математического ожидания .

    Примечание : О распределениях в MS EXCEL можно прочитать в статье Распределения случайной величины в MS EXCEL .

    Размерность дисперсии соответствует квадрату единицы измерения исходных значений. Например, если значения в выборке представляют собой измерения веса детали (в кг), то размерность дисперсии будет кг 2 . Это бывает сложно интерпретировать, поэтому для характеристики разброса значений чаще используют величину равную квадратному корню из дисперсиистандартное отклонение .

    Некоторые свойства дисперсии :

    Var(Х+a)=Var(Х), где Х — случайная величина, а — константа.

    Var(Х)=E[(X-E(X)) 2 ]=E[X 2 -2*X*E(X)+(E(X)) 2 ]=E(X 2 )-E(2*X*E(X))+(E(X)) 2 =E(X 2 )-2*E(X)*E(X)+(E(X)) 2 =E(X 2 )-(E(X)) 2

    Это свойство дисперсии используется в статье про линейную регрессию .

    Var(Х+Y)=Var(Х) + Var(Y) + 2*Cov(Х;Y), где Х и Y — случайные величины, Cov(Х;Y) — ковариация этих случайных величин.

    Если случайные величины независимы (independent), то их ковариация равна 0, и, следовательно, Var(Х+Y)=Var(Х)+Var(Y). Это свойство дисперсии используется при выводе стандартной ошибки среднего .

    Покажем, что для независимых величин Var(Х-Y)=Var(Х+Y). Действительно, Var(Х-Y)= Var(Х-Y)= Var(Х+(-Y))= Var(Х)+Var(-Y)= Var(Х)+Var(-Y)= Var(Х)+(-1) 2 Var(Y)= Var(Х)+Var(Y)= Var(Х+Y). Это свойство дисперсии используется для построения доверительного интервала для разницы 2х средних .

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

    Стандартное отклонение выборки — это мера того, насколько широко разбросаны значения в выборке относительно их среднего .

    По определению, стандартное отклонение равно квадратному корню из дисперсии :

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

    Вычислим стандартное отклонение для 2-х выборок: (1; 5; 9) и (1001; 1005; 1009). В обоих случаях, s=4. Очевидно, что отношение величины стандартного отклонения к значениям массива у выборок существенно отличается. Для таких случаев используется Коэффициент вариации (Coefficient of Variation, CV) — отношение Стандартного отклонения к среднему арифметическому , выраженного в процентах.

    В MS EXCEL 2007 и более ранних версиях для вычисления Стандартного отклонения выборки используется функция =СТАНДОТКЛОН() , англ. название STDEV, т.е. STandard DEViation. С версии MS EXCEL 2010 рекомендуется использовать ее аналог =СТАНДОТКЛОН.В() , англ. название STDEV.S, т.е. Sample STandard DEViation.

    Читайте также  Подсчет суммы в строке таблицы в Microsoft Excel

    Кроме того, начиная с версии MS EXCEL 2010 присутствует функция СТАНДОТКЛОН.Г() , англ. название STDEV.P, т.е. Population STandard DEViation, которая вычисляет стандартное отклонение для генеральной совокупности . Все отличие сводится к знаменателю: вместо n-1 как у СТАНДОТКЛОН.В() , у СТАНДОТКЛОН.Г() в знаменателе просто n.

    Стандартное отклонение можно также вычислить непосредственно по нижеуказанным формулам (см. файл примера ) =КОРЕНЬ(КВАДРОТКЛ(Выборка)/(СЧЁТ(Выборка)-1)) =КОРЕНЬ((СУММКВ(Выборка)-СЧЁТ(Выборка)*СРЗНАЧ(Выборка)^2)/(СЧЁТ(Выборка)-1))

    Другие меры разброса

    Функция КВАДРОТКЛ() вычисляет с умму квадратов отклонений значений от их среднего . Эта функция вернет тот же результат, что и формула =ДИСП.Г( Выборка )*СЧЁТ( Выборка ) , где Выборка — ссылка на диапазон, содержащий массив значений выборки ( именованный диапазон ). Вычисления в функции КВАДРОТКЛ() производятся по формуле:

    Функция СРОТКЛ() является также мерой разброса множества данных. Функция СРОТКЛ() вычисляет среднее абсолютных значений отклонений значений от среднего . Эта функция вернет тот же результат, что и формула =СУММПРОИЗВ(ABS(Выборка-СРЗНАЧ(Выборка)))/СЧЁТ(Выборка) , где Выборка — ссылка на диапазон, содержащий массив значений выборки.

    Вычисления в функции СРОТКЛ () производятся по формуле: