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

Задачи по статистике – расчет дисперсии и среднего квадратического отклонения с решениями

Задача 1. Основные фонды предприятия города производственной и непроизводственной сферы характеризуются следующими данными:

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

Решение

Среднюю величину можно рассчитать по формуле средней арифметической взвешенной.

Для предприятий производственной сферы:

=(2*2,9+3*7,1+5*10,7+6*6,9+7*5,1)/23=6,9.

Для предприятий непроизводственной сферы:

= (0,9*2+1,2*3+2,2*5+3,2*6+4,2*7)/23=2,8.

Дисперсия.

Определим дисперсию.

Для предприятий производственной сферы

=127/23= 5,5.

Для предприятий непроизводственной сферы = 31,4 / 23=1,4.

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

Среднеквадратическое отклонение находят как корень квадратный из дисперсии.

Поэтому для предприятий производственной сферы Среднеквадратическое отклонение это корень квадратный из 5,5, т.е. 2,3.

Для предприятий непроизводственной сферы Среднеквадратическое отклонение это корень квадратный из 1,4, т.е. 1,2.

Определим коэффициент вариации по формуле:

Для предприятий производственной сферы = (2,3/6,85)*100%=33,3%.

Для предприятий непроизводственной сферы =(1,2/2,82)*100%= 41,3%.

Задача 2. Распределение подростковой преступности по одной из областей РФ за 1 полугодие 2014 года:

Среднюю величину найдем по формуле средней арифметической взвешенной.

= 2826/220=12,8.

Показатели вариации:

Размах вариации =16-7=9.

Среднее линейное отклонение = │xi — xcp│ *fi / сумму fi = 462,4/220=2,1.

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

=1420,7/220=6,5

Дисперсия 6,5

Среднеквадратическое отклонение рассчитывается как корень квадратный из дисперсии, т.е. из 6,5.

Среднее квадратическое отклонение равно 2,5.

Рассчитаем коэффициент вариации по формуле:

=(2,5/12,8)*100%=19,53%.

Коэффициент вариации=19,53%.

Следовательно, совокупность однородна, т.к. 19,53% меньше 33%.

Задача 3. Распределение фермерских хозяйств по посевной площади представлено следующими данными:

Найдите дисперсию и среднее квадратическое отклонение посевных площадей, применив для расчета средней арифметической и дисперсии метод моментов.

Средняя арифметическая (начальный момент первого порядка) = 244.

Дисперсия (центральный момент второго порядка)=16764.

Среднее квадратическое отклонение равно корню квадратному из дисперсии, т.е. из 16764=129,5.

Функция потерь

Квадратичная потеря ошибок — одна из наиболее широко используемых функций потерь в статистике, хотя ее широкое использование проистекает больше из математического удобства, чем из соображений фактических потерь в приложениях. Карл Фридрих Гаусс , который ввел использование среднеквадратичной ошибки, осознавал ее произвол и был согласен с возражениями против нее на этих основаниях. Математические преимущества среднеквадратичной ошибки особенно очевидны при ее использовании при анализе эффективности линейной регрессии , поскольку она позволяет разделить вариацию в наборе данных на вариации, объясняемые моделью, и вариации, объясняемые случайностью.

Критика

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

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

Применение XYZ анализа при подготовке данных к прогнозу

Работая с большим массивом данных при подготовке данных к прогнозу, необходим индикатор, который будет подсказывать, на какие временные ряды в первую очередь стоит обратить внимание. В качестве индикатора вы можете использовать «коэффициент вариации» или XYZ анализ. Если коэффициент вариации больше 10 — 25% или для Y и Z рядов, то изучаем данные (например, продажи товара по месяцам в разрезе направлений продаж) и определяем факторы, повлиявшие на отклонение

Если коэффициент вариации больше 10 — 25% или для Y и Z рядов, то изучаем данные (например, продажи товара по месяцам в разрезе направлений продаж) и определяем факторы, повлиявшие на отклонение.

Добавляем фильтр на столбец XYZ анализ и анализируем ряды.

Сначала отфильтруем ряды с коэффициентом вариации больше 25% или Z

Изучаем ряды с большими отклонениями фактических данных за последние 4-5 месяцев. Определяем причины провалов или резких подъёмов продаж. Готовим данные для прогноза.  Очищаем данные от влияния случайных факторов или корректируем дефицит. 

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

• Неоднородные продажи по месяцам свернуть до продаж по кварталам,
• Продажи по неделям свернуть до продаж по месяцам,
• Продажи по товарам свернуть до товарных групп…

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

О том, как сгруппировать временной ряд, читайте статью «Как сделать сводную и сгруппировать временные ряды?»

Затем выделяем ряды с коэффициентом вариации Y

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

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

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

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

1. Рассчитываем коэффициент вариации;
2. Делаем XYZ анализ;
3. Готовим данные для прогноза (очищаем от случайных факторов или группируем временные ряды);
4. Строим прогноз;
5. Учитываем дополнительные факторы в прогнозе;

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

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

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

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

Способ с помощью Мастера функций

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

1. Нажав левую кнопку мыши, выделите ячейку, в которой хотите видеть результат вычислений.
2. Откройте окно Мастера функций, нажав по кнопке «Вставить функцию», расположенной слева от строки формул либо использовав горячие клавиши Shift+F3.
3. В появившемся окне отыщите в списке строку «СРЗНАЧ», выделите ее и нажмите кнопку «ОК».
4. Появится новое окно для ввода аргументов функции. В нем вы увидите два поля: «Число1» и «Число2».
5. В первое поле введите адреса ячеек, в которых расположены числовые значения для расчета. Сделать это можно как вручную, так и с помощью специального инструмента. Во втором случае нажмите по кнопке, расположенной в правой части поля для ввода. Окно Мастера свернется и вам необходимо будет выделить мышкой ячейки для расчета.
6. Если другой диапазон ячеек с данными находится в другом месте листа, тогда укажите его в поле «Число2».
7. Проделайте ввод данных, пока не укажете все необходимые.
8. Нажмите кнопку «ОК».

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

Основная идея

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

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

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

На первый взгляд кажется, что лучше всех подходит Иван, т.к. у него средний бал максимальный. Но тут мы вовремя вспоминаем, что факультет-то наш называется “Программирование”, а у Ивана хорошие оценки только по рисованию, пению и прочей физкультуре, а по математике и информатике как раз не очень

Возникает вопрос: а как присвоить нашим предметам различную важность (ценность), чтобы учитывать ее при расчете среднего? И вот тут на помощь приходит средневзвешенное значение

Средневзвешенное – это среднее с учетом различной ценности (веса, важности) каждого из элементов. В бизнесе средневзвешенное часто используется в таких задачах, как:

В бизнесе средневзвешенное часто используется в таких задачах, как:

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

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

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

среднее с учетом веса каждого предмета. Нужная нам формула будет выглядеть так:

Функция СУММПРОИЗВ (SUMPRODUCT) попарно перемножает друг на друга ячейки в двух указанных диапазонах – оценки абитурента и вес каждого предмета – а затем суммирует все полученные произведения

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

Практическое применение

На практике среднеквадратическое отклонение позволяет оценить, насколько значения из множества могут отличаться от среднего значения.

Экономика и финансы

Среднее квадратическое отклонение доходности портфеля σ=DX{\displaystyle \sigma ={\sqrt {D}}} отождествляется с риском портфеля.

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

Климат

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

Спорт

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

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

Расчет в Excel

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

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

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

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

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

Для
того, чтобы показать меру разброса
данных нужно вначале определиться,
относительно чего этот самый разброс
будет считаться — jбычно
это средняя величина. Дальше нужно
посчитать, насколько значения анализируемой
совокупности данных находятся далеко
от средней. Понятное дело, что каждому
значению соответствует некоторая
величина отклонения, но нас же интересует
общая оценка, охватывающая всю
совокупность. Поэтому рассчитывают
среднее отклонение по формуле обычной
средней арифметической. Но! Но для того,
чтобы рассчитать среднее из отклонений,
их нужно вначале сложить. И если мы
сложим положительные и отрицательные
числа, то они взаимоуничтожатся и их
сумма будет стремиться к нулю. Чтобы
этого избежать, все отклонения берутся
по модулю, то есть все отрицательные
числа становятся положительными. Вот
теперь среднее отклонение будет
показывать обобщенную меру разброса
значений. В итоге, средне линейное
отклонение будет рассчитываться по
формуле:

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

x
–
анализируемый показатель, с черточкой
сверху – среднее значение показателя,

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

оператор
суммирования, надеюсь, никого не пугает.

Рассчитанное
по указанной формуле среднее линейное
отклонение отражает среднее абсолютное
отклонение от средней величины по данной
совокупности.

На
картинке красная линия — это среднее
значение. Отклонения каждого наблюдения
от среднего указаны маленькими
стрелочками. Именно они берутся по
модулю и суммируются. Потом все делится
на количество значений.

Для
полноты картины нужно привести еще и
пример. Допустим, имеется фирма по
производству черенков для лопат. Каждый
черенок должен быть 1,5 метра длиной, но,
что еще важней, все должны быть одинаковыми
или, по крайней мере, плюс-минус 5 см.
Однако нерадивые работники то 1,2 м
отпилят, то 1,8 м. Дачники недовольны.
Решил директор фирмы провести
статистический анализ длины черенков.
Отобрал 10 штук и замерял их длину, нашел
среднюю и рассчитал среднее линейное
отклонение. Средняя получилась как раз,
что надо – 1,5 м. А вот среднее линейное
отклонение вышло 0,16 м. Вот и получается,
что каждый черенок длиннее или короче,
чем нужно в среднем на 16 см. Есть, о чем
поговорить с работниками. На самом деле
я не встречал реального использования
данного показателя, поэтому пример
придумал сам. Тем не менее, в статистике
есть такой показатель.

Правило трёх сигм

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

Правило трёх сигм (3σ{\displaystyle 3\sigma }) гласит: вероятность того, что любая случайная величина отклонится от своего среднего значения менее чем на 3σ{\displaystyle 3\sigma }, — P(|ξ−Eξ∣<3σ)≥89{\displaystyle P(|\xi -E\xi \mid <3\sigma )\geq {\frac {8}{9}}}.

Практически все значения нормально распределённой случайной величины лежат в интервале (μ−3σ;μ+3σ){\displaystyle \left(\mu -3\sigma ;\mu +3\sigma \right)}, где μ=Eξ{\displaystyle \mu =E\xi } — математическое ожидание случайной величины. Более строго — приблизительно с вероятностью 0,9973 значение нормально распределённой случайной величины лежит в указанном интервале.

Что такое стандартное отклонение

Но… все будет немного иначе, если мы будем анализировать выборку
данных. В нашем примере мы рассматривали генеральную совокупность.
То есть наши 5 собак были единственными в мире собаками, которые нас интересовали.

Но если данные являются выборкой (значениями, которые выбрали из большой генеральной совокупности), тогда вычисления нужно вести иначе.

Если есть значений, то:

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

Например, если наших пять собак – только выборка из генеральной совокупности собак (всех собак на планете), мы должны делить на 4, а не на 5,
а именно:

Дисперсия выборки = мм 2 .

При этом стандартное отклонение по выборке равно мм (округлено до ближайшего целого значения).

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

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

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

Например, у нас есть три числовых множества: {0, 0, 14, 14}, {0, 6, 8, 14} и {6, 6, 8, 8}. У всех трёх множеств средние значения равны 7, а среднеквадратические отклонения, соответственно, равны 7, 5 и 1. У последнего множества среднеквадратическое отклонение маленькое, так как значения в множестве сгруппированы вокруг среднего значения; у первого множества самое большое значение среднеквадратического отклонения — значения внутри множества сильно расходятся со средним значением.

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

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

Как найти среднее арифметическое число в 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.

Функция: =СРЗНАЧЕСЛИ(A1:A8;”>=10″)

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

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

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

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

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

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

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

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

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

Формула: =СУММПРОИЗВ(C2:C12;B2:B12)/СУММ(C2:C12).

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

От: admin

Эта тема закрыта для публикации ответов.