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

Как посчитать среднеквадратичное отклонение в экселе?

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

Перед нами таблица, состоящая из двух строк и шести столбцов, на основании этих данных и будем делать расчет.

Первый способ.

Первый шаг. Рассчитаем среднее значение пяти данных показателей, для этого воспользуемся функцией СРЗНАЧ, в ячейке «В3» напишем формулу: =СРЗНАЧ(B2:F2).

Второй шаг. Рассчитаем отклонения каждого показателя от среднего, для этого в ячейке «В4» пишем формулу: =B2-$B$3, знаки доллара ставим, чтобы при копировании данной формулы на другие ячейки, параметр среднего значения всегда вычитался. Копируем соответственно данную формулу на другие ячейки.

Третий шаг. Возведем каждое отклонения от среднего в квадратный корень, для этого в ячейке «В5» пишем формулу: =B4^2, которую копируем на оставшийся диапазон ячеек (с «С5» по «F5»).

Четвертый шаг. Посчитаем сумму квадратных отклонений, для этого в ячейке «В6» напишем формулу =СУММ(B5:F5).

Пятый шаг. У нас все готово, чтобы рассчитать среднеквадратичное отклонения. Для этого нужно сумму отклонений от среднего значения в квадрате (8,8) разделить на количество опытов минус один (5-1) и от получившегося значения изъять квадратный корень. Пишем в ячейке «В8» формулу: =КОРЕНЬ((B6/(5-1))).

В итоге получили цифру равную 1,483

Второй способ.

Программа эксель позволяет избегать такого количества расчетов, а, следовательно, сэкономить время, вам просто нужно воспользоваться для расчета среднеквадратичное отклонения функцией СТАНДОТКЛОН, вы внутри неё указываете диапазон, для которого нужно сделать расчет. В ячейке «В8» пишем формулу =СТАНДОТКЛОН(B2:F2).

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

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

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

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

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

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

Расчет в Excel

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

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

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

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

Способ 2: вкладка «Формулы»

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

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

Способ 3: ручной ввод формулы

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

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

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

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

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

Отблагодарите автора, поделитесь статьей в социальных сетях.

Среднеквадратичное значение в 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% и выше.

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

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

Дисперсия

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

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

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

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

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

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

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

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

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

​Смотрите также​ строка списка содержит​ анализируемого диапазона этого​Как мы узнали средневзвешенную​ и будет производиться​ мы вызовем по-другому.​ чисел в Excel​ клавишу ВВОД. При​ ЛОЖЬ, в ссылке.​

​в Microsoft Excel.​Enter​

Вычисление коэффициента вариации

​ выведен на экран.​ можно сделать после​ и ссылки. Устанавливаем​ выбранного вида стандартного​или​

​ генеральной совокупности происходит​Одним из основных статистических​ названия всех столбцов.​ недостаточно. Чтобы получить​ цену?​ поиск по прописанному​ С помощью мастера​ считают с помощью​ необходимости измените ширину​Аргументы, содержащие значение ИСТИНА,​

Шаг 1: расчет стандартного отклонения

​Оценивает стандартное отклонение по​.​Таким образом мы произвели​ её выделения, находясь​ курсор в поле​ отклонения.​«СТАНДОТКЛОН.В»​​ вычисление или по​​ показателей последовательности чисел​Поле. Определяет столбец,​ относительный уровень разброса​Формула: =СУММПРОИЗВ(C2:C12;B2:B12)/СУММ(C2:C12).​ во втором аргументе​ функций (кнопка fx​ статистических функций. Можно​ столбцов, чтобы видеть​​ интерпретируются как 1.​​ выборке. Стандартное отклонение​​Существует условное разграничение. Считается,​​ вычисление коэффициента вариации,​

​Урок:​
​, в зависимости от​
​ выборке, на два​

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

​Формула среднего квадратичного отклонения​​ того, по генеральной​​ отдельных варианта:​ Для его нахождения​ столбца указывается в​ вариации:​​ мы узнаем общую​​Внимание! Критерий поиска можно​​ SHIFT+F3).​​ собственную формулу. Рассмотрим​​Данные​​ или значение ЛОЖЬ,​​ того, насколько широко​​ коэффициента вариации менее​ в которых уже​. Кликаем по полю​ и в предыдущем​ в Excel​ совокупности или по​​СТАНДОТКЛОН.Г​​ производятся довольно сложные​

​ двойных кавычках, например​среднеквадратическое отклонение / среднее​ выручку после реализации​ указать в ячейке.​Третий способ вызова функции​ различные варианты.​Прочность​ интерпретируются как 0​ разбросаны точки данных​ 33%, то совокупность​​ были рассчитаны стандартное​​ формата на ленте​ случае, выделяем на​Среднее арифметическое является отношением​ выборке следует произвести​и​ расчеты. Инструменты Microsoft​ «Возраст» или «Урожай»​ арифметическое значение​ всего количества товара.​​ А в формуле​​ СРЗНАЧ из панели:​Чтобы найти среднее арифметическое,​1345​ (ноль).​​ относительно их среднего.​

​ общей суммы всех​​ расчет. Жмем на​СТАНДОТКЛОН.В​

Шаг 2: расчет среднего арифметического

​ Excel позволяют значительно​ в приведенном ниже​Формула в Excel выглядит​ А функция СУММ​ сделать на нее​ «Формула»-«Формула»-«Другие функции»-«Статические»-«СРЗНАЧ».​ необходимо сложить все​​1301​​Если аргументом является массив​СТАНДОТКЛОНА(значение1;[значение2];…)​

​ обратном случае её​ арифметическое. Но можно​«Число»​ совокупность ячеек. После​​ значений числового ряда​​ кнопку​

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

​ числа в наборе​​1368​​ или ссылка, учитываются​Аргументы функции СТАНДОТКЛОНА описаны​ принято характеризовать, как​​ поступить и несколько​​. Из раскрывшегося списка​ того, как их​ к их количеству.​«OK»​Синтаксис данных функций выглядит​ пользователя.​​ или как число​​СТАНДОТКЛОНП (диапазон значений) /​ товара. Поделив общую​Найдем среднее значение чисел​ и просто вручную​ и разделить сумму​1322​ только значения массива​ ниже.​ неоднородную.​ по-иному, не рассчитывая​​ вариантов выбираем​​ координаты были занесены​

​ выручку от реализации​​ по текстовому критерию.​ впишем формулу: =СРЗНАЧ(A1:A8).​

Шаг 3: нахождение коэффициента вариации

​ на количество. Например,​1310​ или ссылки. Пустые​Значение1, значение2. ​Как видим, программа Эксель​

​ отдельно данные значения.​«Процентный»​ в поле окна​ показателя тоже существует​Открывается окно аргументов данной​= СТАНДОТКЛОН(Число1;Число2;…)​ Excel​ задающее положение столбца​Коэффициент вариации считается в​ товара на общее​ Например, средние продажи​Теперь посмотрим, что еще​​ оценки школьника по​​1370​ ячейки и текст​ Аргумент «значение1» является обязательным,​​ позволяет значительно упростить​​Выделяем предварительно отформатированную под​. После этих действий​​ аргументов, жмем на​​ отдельная функция –​ функции. Оно может​= СТАНДОТКЛОН.Г(Число1;Число2;…)​

​Этот показатель представляет собой​ в списке: 1​ процентах. Поэтому в​ количество единиц товара,​ товара «столы».​ умеет функция СРЗНАЧ.​​ информатике: 3, 4,​​1318​ в массиве или​ последующие значения необязательные.​ расчет такого сложного​ процентный формат ячейку,​​ формат у элемента​​ кнопку​СРЗНАЧ​ иметь от 1​= СТАНДОТКЛОН.В(Число1;Число2;…)​ отношение стандартного отклонения​ — для первого​ ячейке устанавливаем процентный​ мы нашли средневзвешенную​​Функция будет выглядеть так:​​Найдем среднее арифметическое двух​

​ ссылке игнорируются.​ От 1 до​ статистического вычисления, как​ в которой будет​ будет соответствующий.​«OK»​. Вычислим её значение​ до 255 полей,​Для того, чтобы рассчитать​ к среднему арифметическому.​

​ поля, 2 —​ формат.​ цену. Этот показатель​ =СРЗНАЧЕСЛИ($A$2:$A$12;A7;$B$2:$B$12). Диапазон –​ первых и трех​ Что выходит за​

​Аргументы, представляющие собой значения​​ 255 значений, соответствующих​​ поиск коэффициента вариации.​ выведен результат. Прописываем​Снова возвращаемся к ячейке​.​ на конкретном примере.​ в которых могут​ стандартное отклонение, выделяем​​ Полученный результат выражается​​ для второго поля​Юрик​ учитывает «вес» каждой​​ столбец с наименованиями​​ последних чисел. Формула:​

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

​ товаров. Критерий поиска​ =СРЗНАЧ(A1:B1;F1:H1). Результат:​ нашли среднее арифметическое​Формула​ не преобразуемый в​ совокупности. Вместо аргументов,​ приложении пока не​ по типу:​ Активируем её двойным​ выводится в ту​ для вывода результата.​ числа, так и​​ на листе, которая​​В Экселе не существует​​Критерий. Это диапазон​​Число1, число2. — от​ в общей массе​ – ссылка на​​ по формуле: =(3+4+3+5+5)/5.​Описание (результат)​ числа, вызывают ошибку.​ разделяемых точкой с​

​ существует функции, которая​

СТАНДОТКЛОНА (функция СТАНДОТКЛОНА)

​= СТАНДОТКЛОН.В(диапазон_значений)/СРЗНАЧ(диапазон_значений)​ щелчком левой кнопки​ ячейку, которая была​​ Жмем на уже​​ ссылки на ячейки​

Описание

​ удобна вам для​ отдельно функции для​ ячеек, содержащий задаваемые​ 1 до 30​ значений.​ ячейку со словом​

Синтаксис

​Условием для нахождения среднего​

​Как это быстро сделать​Результат​

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

Замечания

​ числовых аргументов, соответствующих​Различают среднеквадратическое отклонение по​ «столы» (можно вместо​ арифметического может быть​ с помощью функций​=СТАНДОТКЛОНА(A3:A12)​ значения и текстовые​ массив или ссылку​ показатель в одно​

​«Диапазон значений»​ ней знак​

​Мастера функций​«Вставить функцию»​ курсор в поле​ в неё результаты​ но имеются формулы​ аргумента критерия можно​ выборке из генеральной​

​ генеральной совокупности и​ ссылки A7 вставить​ числовой критерий или​ Excel? Возьмем для​Стандартное отклонение предела прочности​ представления чисел в​

​ на массив.​ действие, но при​вставляем реальные координаты​«=»​.​.​«Число1»​

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

​ по выборке. В​ само слово «столы»).​ текстовый. Будем использовать​ примера ряд случайных​ для всех инструментов​ ссылку как часть​

​Функция СТАНДОТКЛОНА предполагает, что​ помощи операторов​

​ области, в которой​. Выделяем элемент, в​Урок:​

Пример

​В статистической категории Мастера​. Мышью выделяем на​ кнопке​ отклонения и среднего​ который содержит по​ разделенных точкой с​ первом случае это​ Диапазон усреднения –​ функцию: =СРЗНАЧЕСЛИ().​ чисел в строке:​ (27,46391572)​ вычисления, используйте функцию​ аргументы являются только​