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

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

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

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

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

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

Расчет в Excel

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

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

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

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

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

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

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

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

После этого запускается окно аргументов. Все дальнейшие действия нужно производить так же, как и в первом варианте.

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

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

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

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

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

После того, как запись сделана, нажмите на кнопку Enter на клавиатуре.

Как видим, механизм расчета среднеквадратичного отклонения в 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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Как рассчитать коэффициент вариации в Эксель

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

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

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

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

= СТАНДОТКЛОН(Число1;Число2;…) — Для старой версии

= СТАНДОТКЛОН.В(Число1;Число2;…) — Для новой версии соответственно.

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

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

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

4. В ранее выделенной ячейке отобразится итоговый расчет стандартного отклонения.

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

Среднее арифметическое отражает общую сумму значений числового ряда, поделенных на их количество. Для этого используем функцию СРЗНАЧ.

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

2. Перейдите в категорию «Статистические» и выберите поле с наименованием «СРЗНАЧ», после этого нажмите «ОК».

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

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

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

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

2. Снова вернитесь к ранее выбранной ячейке и выделите ее двойным щелчком левой кнопки мыши. Поставьте в ней знак «=», затем выделите ячейку с результатом вычислений стандартного отклонения. Теперь нажмите кнопку «/»(разделить) на клавиатуре и выберите ячейку со средним арифметическим. После ввода данных нажмите клавишу Enter.

3. Результат будет автоматически выведен на экран.

Также существует способ рассчитать коэффициент вариации без предварительных шагов, который мы рассмотрим ниже:

1. Аналогично выделите ячейку, затем придайте ей процентный формат. Впишите в нее следующую формулу:

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

2. После занесения всех параметров нажмите клавишу Enter, чтобы получить конечный результат.

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

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

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

Появится список, в котором нужно найти и выбрать СТАНДОТКЛОН.В или СТАНДОТКЛОН.Г, и нажать ОК.

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

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

Следует выделить ячейку для занесения результата расчета. Захотите во вкладку «Формулы.

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

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

Ручной ввод формулы

Выделяете ячейку, в которую будет выводиться результат. Далее прописываете в ней или в строке формул выражение по такому шаблону — =СТАНДОТКЛОН.Г(число1(адрес_ячейки1); число2(адрес_ячейки2);…)или =СТАНДОТКЛОН.В(число1(адрес_ячейки1); число2(адрес_ячейки2);…). Если нужно, то можно записать максимально 255 аргументов.

Когда запись будет совершена, следует кликнуть Enter на клавиатуре.