Что стандартное отклонение в excel. Как посчитать процент отклонения в Excel по двум формулам

Добрый день!

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

В Excel используются несколько вариантов этой функции отклонения:

Математическая теория

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

Что же собственно делает стандартное отклонение? Оно производит оценку среднеквадратического отклонения случайной величины Х относительно её математического ожидания на основе несмещённой оценки её дисперсии. Согласитесь, звучит запутанно, но я думаю учащиеся поймут о чём собственно идет речь!

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

Теперь можно дать определение и стандартному отклонению – это анализ среднеквадратического отклонения случайной величины Х сравнительно её математической перспективы на основе несмещённой оценки её дисперсии. Формула записывается так:
Отмечу, что все две оценки предоставляются смещёнными. При общих случаях построить несмещённую оценку не является возможным. Но оценка на основе оценки несмещённой дисперсии будет состоятельной.

Практическое воплощение в Excel

Ну а теперь отойдём от скучной теории и на практике посмотрим, как работает функция СТАНДОТКЛОН. Я не буду рассматривать все вариации функции стандартного отклонения в Excel, достаточно и одной, но в примерах. А для примера рассмотрим, как определяется статистика стабильности продаж.

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

СТАНДОТКЛОН.Г(_число1_;_число2_; ….), где:

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

Для начала нам необходимо посчитать среднее значение по месяцам. Будем использовать для этого функцию СРЗНАЧ и получится формула: =СРЗНАЧ(C4:E4).
Теперь собственно мы и можем найти стандартное отклонение с помощью функции СТАНДОТКЛОН.Г в значении которой нужно проставить продажи товара каждого периода. Получится формула следующего вида: =СТАНДОТКЛОН.Г(C4;D4;E4).
Ну вот и сделана половина дел. Следующим шагом мы формируем «Вариацию», это получается делением на среднее значение, стандартного отклонения и результат переводим в проценты. Получаем такую таблицу:
Ну вот основные расчёты окончены, осталось разобраться как идут продажи стабильно или нет. Возьмем как условие что отклонения в 10% это считается стабильно, от 10 до 25% это небольшие отклонения, а вот всё что выше 25% это уже не стабильно. Для получения результата по условиям воспользуемся логической функцией ЕСЛИ и для получения результата напишем формулу:

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

Что измеряет стандартное отклонение?

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

Но если внимательнее изучить деятельность второго магазина, можно убедится, что хотя среднее значение корректно, вариабельность стока очень высокая (от 10 до 58 у.е.). Таким образом, можно сделать вывод, что среднее значение не всегда правильно оценивает данные. Вот где на выручку приходит стандартное отклонение.

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

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

В случае с первым менеджером, стандартное отклонение составило 2. Это говорит нам о том, что каждое значение в выборке в среднем откланяется на 2 от среднего значения. Хорошо ли это? Давайте рассмотрим вопрос под другим углом – стандартное отклонение равное 0, говорит нам о том, что каждое значение в выборке равно его среднему значению (в нашем случае, 32,2). Так, стандартное отклонение 2 ненамного отличается от 0, и указывает на то, что большинство значений находятся рядом со средним значением. Чем ближе стандартное отклонение к 0, тем надежнее среднее. Более того, стандартное отклонение близкое к 0, говорит о маленькой вариабельности данных. То есть, величина стока со стандартным отклонением 2, указывает на невероятную последовательность первого менеджера.

В случае со вторым магазином, стандартное отклонение составило 18,9. То есть стоимость стока в среднем отклоняется на величину 18,9 от среднего значения от недели к неделе. Сумасшедший разброс! Чем дальше стандартное отклонение от 0, тем менее точно среднее значение. В нашем случае, цифра 18,9 указывает на то, что среднему значению (32,8 у.е. в неделю) просто нельзя доверять. Оно также говорит нам о том, что еженедельная величина стока обладает большой вариабельностью.

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

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

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

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

Ниже описаны шаги, которые предпринимает Excel, чтобы прийти к цифре 21,6.

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

Для начала Excel находит среднее значение выборки. В нашем случае, среднее получилось равным 40, которое на следующем шаге отнимают от каждого значения выборки. Каждую полученную разницу возводят в квадрат и суммируют. У нас получилась сумма равная 2800, которую необходимо разделить на количество элементов выборки минус 1. Так как у нас 7 элементов, получается необходимо 2800 разделить на 6. Из полученного результата находим квадратный корень, это цифра будет стандартным отклонением.

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

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

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

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

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

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

Метод 1 Подготовка данных

Метод 2 Таблица данных

Метод 3 Вычисление стандартного отклонения

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

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

Наберите «СТАНДОТКЛОН».
Это формула Excel для стандартного отклонения. При использовании этой формулы Excel будет автоматически вычислять среднее значение и стандартное отклонение.
- Выберите либо «СТАНДОТКЛОН» (вычисление по выборке), либо «СТАНДОТКЛОНП» (вычисление по генеральной совокупности).
Укажите диапазон данных.
- В Excel диапазоны данных указываются в следующем виде: (C2:C15). Вся формула будет выглядеть как:»=СТАНДОТКЛОН(C2:C15)».
Нажмите кнопку «Enter «.
Стандартное отклонение отобразится в ячейке.
- Вы можете также использовать функцию Excel для выбора формулы стандартного отклонения. Нажмите на «Вставить функцию» в строке формул. Затем выберите «Статистические » и выберите «СТАНДОТКЛОН». Введите свой диапазон данных в открывшемся окне. Нажмите «OK».

Что вам понадобится

Microsoft Excel
Данные
Таблица данных
Формула для вычисления стандартного отклонения
Диапазон данных

Информация о статье

Эту страницу просматривали 67 780 раза.

Была ли эта статья полезной?

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

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

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

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

Расчет в Excel

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

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

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

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

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

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

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

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

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

Урок: Работа с формулами в Excel

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

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

Задайте свой вопрос в комментариях, подробно расписав суть проблемы. Наши специалисты постараются ответить максимально быстро.

Среднее квадратичное отклонение (или стандартное отклонение) - вторая по значению константа вариаци-онного ряда. Она является мерой разнообразия входящих в груп-пу объектов и показывает, на сколько в среднем отклоняются варианты от средней арифметической изучаемой совокупности. Чем сильнее раз-бросаны варианты вокруг средней, чем чаще встречаются край-ние или другие отдаленные классы отклонений от средней ва-риационного ряда, тем большим оказывается и среднее квад-ратичное отклонение. Стандартное отклонение есть мера изменчивости признаков, обусловлен-ная влиянием на них случайных факторов. Квадрат стандартного отклонения (S ²) называется дисперсией .

Что такое «случайное» при детальном рассмотрении? В формуле модели вариант случайный компонент предстает в виде некой «добавки» к доле варианты, сформированной под действием систематических факторов, ± x случ . . Она, в свою очередь, складывается из эффектов влияния неопределенно большого числа факторов: x случ . = Σ x случ. k .

Каждый из этих факторов может обнаружить свое сильное действие (дать большой вклад), а может почти не участвовать в становлении конкретной варианты (слабое действие, незначительный вклад). Причем доля случайной «прибавки» для каждой варианты оказывается различной! Рассматривая, например, размеры дафний , можно увидеть, что одна особь крупнее, другая мельче, поскольку одна родилась на несколько часов раньше, другая позже, или одна генетически не вполне идентична прочим, а третья росла в более прогреваемой зоне аквариума и т. д.

Если эти частные факторы не входят в число контролируемых при сборе вариант, то они, индивидуально проявляясь в разной степени, обеспечивают случайное варьирование вариант. Чем больше случайных факторов, чем они сильнее, тем дальше будут раз-бросаны варианты вокруг средней и тем большим оказывается характеристика варьирования, среднее квад-ратичное отклонение. В контексте нашей книги термин «случайное» есть синоним слова «неизвестное», «неподконтрольное». Пока мы каким-либо способом не выразим интенсивность фактора (группировкой, градацией, числом), до тех пор он останется фактором, вызывающим случайную изменчивость.

Смысл стандартного отклонения (вариант от средней) выражает формула:

где x - значение признака у каждого объекта в группе,

М - средняя арифметическая признака,

п - число вари-ант выборки.

Выполнять расчеты удобнее с помощью рабочей формулы :

где Σ x ² - сумма квадратов значений признака для всех вариант,

Σ x - сумма значений признака,

n - объем вы-борки.

Для примера с массой тела бурозубок стандартное отклонение будет равно: S = 0.897216496, а после необходимого округления S = 0.897 г.

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

где S Σ - усредненная величина среднего квадратичного откло-нения для суммарного распределения,

S --- усредняемые значе-ния стандартного отклонения,

п - объемы отдельных выборок,

k - число усредняе-мых стандартных отклонений.

Рассмотрим такой пример. Четыре независимых определе-ния веса печени (мг) у землероек-бурозубок в июне, июле, ав-густе и сентябре дали следующие величины стандартных отклонений: 93, 83, 50, 71 (при n = 17, 115, 132, 140). Подставив в вышеприведенную фор-мулу нужные значения, получим стандартные отклонения для суммарной выбор-ки (для всего бесснежного периода):

В случае, если требуется первичная статистическая обработка большого числа выборок, но необязательно с большой точностью, для оценки стандартного отклонения можно воспользоваться экспресс-методом , основанным на знании закона нормального распределения. Как уже отмечалось, крайние значения для выборки (с вероятностью P = 95%) можно считать границами, удаленными от средней на расстояние 2S : x min = M − 2S , x max = M + 2S . Это значит, что в лимите (Lim), в диапазоне от максимального до минимального выборочного значения, укладываются четыре стандартных отклонения:

Lim = (M + 2S ) − (M − 2S ) = 4S .

Однако этот вывод справедлив только по отношению к выборкам большого размера, тогда как для небольших выборок необходимо делать поправки. Рекомендуется следующая формула приблизительного расчета стандартного отклонения (Ашмарин и др., 1975):

где величина d взята из таблицы 3 (против соответствую-щего объема выборки, n ).

Таблица 3

Выборочное стандартное отклонение веса тела бурозубок (n = 63), рассчитанное по приведенной формуле, составляет:

S = (11.9 − 7.3) / 4 = 1.15 г,

что достаточно близко к точному значению, S = 0.89 г.

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

Стандартное отклонение - величина именованная, поэтому с ее помощью можно сравнивать характер варьирования лишь одних и тех же признаков. Чтобы сопоставить изменчивость разнородных признаков, выраженных в различных единицах измерения, а также нивелировать влияние мас-штаба измерений, используют так называемый коэффициент вариации (СV) , безразмерную величину, отношение выборочной оценки S к собственной средней M :

В нашем примере с весом тела бурозубок:

9.6%.

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

Практика показывает, что для многих биологических признаков наблюдается увеличение изменчивости (стандартного отклонения) с ростом их величины (средней арифметической). При этом коэффициент вариации остается примерно на одном и том же уровне - 8-15%. За увеличение коэффициента вариации ответственны, как правило, растущие отличия распределения признака от нормального закона.

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

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

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

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

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

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

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

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

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

Дисперсия

{module 111}

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

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

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

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

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

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

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

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

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

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

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

по выборке

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Добрый день!

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

В Excel используются несколько вариантов этой функции отклонения:

Математическая теория

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

Практическое воплощение в Excel

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

СТАНДОТКЛОН.Г(_число1_;_число2_; ….), где:

Для начала нам необходимо посчитать среднее значение по месяцам. Будем использовать для этого функцию СРЗНАЧ и получится формула: =СРЗНАЧ(C4:E4).
Теперь собственно мы и можем найти стандартное отклонение с помощью функции СТАНДОТКЛОН.Г в значении которой нужно проставить продажи товара каждого периода. Получится формула следующего вида: =СТАНДОТКЛОН.Г(C4;D4;E4).
Ну вот и сделана половина дел. Следующим шагом мы формируем «Вариацию», это получается делением на среднее значение, стандартного отклонения и результат переводим в проценты. Получаем такую таблицу:
Ну вот основные расчёты окончены, осталось разобраться как идут продажи стабильно или нет. Возьмем как условие что отклонения в 10% это считается стабильно, от 10 до 25% это небольшие отклонения, а вот всё что выше 25% это уже не стабильно. Для получения результата по условиям воспользуемся логической и для получения результата напишем формулу:

ЕСЛИ(H4<0,1;"стабильно";ЕСЛИ(H4<0,25;"нормально";"не стабильно"))

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

Для начала выделяете , для которых будете применяться условное форматирование. В панели управления «Главная» выбираете «Условное форматирование» и в выпадающем меню пункт «Правила выделения ячеек» и следующим нажимаете пункт меню «Текст содержит…». Появляется диалоговое окно в которое вы вписываете свои условия.

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

Использование VBA для функции СТАНДОТКЛОН.Г

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

Function MyStDevP(Arr) Dim x, aCnt&, aSum#, aAver#, tmp# For Each x In Arr aSum = aSum + x "вычисляем сумму элементов массива aCnt = aCnt + 1 "вычисляем кол-во элементов Next x aAver = aSum / aCnt "среднее значение For Each x In Arr tmp = tmp + (x - aAver) ^ 2 "вычисляем сумму квадратов разницы элементов массива и среднего значения Next x MyStDevP = Sqr(tmp / aCnt) "вычисляем СТАНДОТКЛОН.Г() End Function

Function MyStDevP (Arr )

Dim x , aCnt & , aSum #, aAver#, tmp#

For Each x In Arr

aSum = aSum + x "вычисляем сумму элементов массива

ОПРЕДЕЛЕНИЕ ГЕНЕРАЛЬНОЙ СОВОКУПНОСТИ И

ПАРАМЕТРОВ НА ОСНОВЕ ВЫБОРОЧНЫХ СТАТИСТИК;

СРЕДНЕЕ И СТАНДАРТНОЕ ОТКЛОНЕНИЕ

Определение среднего в популяции

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

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

Мы используем среднее для ограниченной выборки проб, чтобы сделать вывод о достаточно большой (вплоть до неограниченной) популяции проб. Такая популяция называется генеральной совокупностью. Среднее по генеральной совокупности таких, например, данных, как ВР, обозначается М х. Такую характеристику генеральной совокупности называют параметром. Среднее, действительно вычисленное нами для данной выборки, называется статистикой, и обозначается М х. Является ли статистика М х наилучшей оценкой параметра М х, которую мы можем получить на основе нашей выборки проб? Ответ - без доказательства - да. Но прежде чем вы решите, что это всегда так, давайте перейдем, к стандартному отклонению, где дело обстоит иначе.

Вычисление стандартного отклонения

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

Для этого, вы определяете, насколько каждая оценка (т. е. X ) больше или меньше среднего (М х ). Затем вы возводите в квадрат каждую разность (X-М х ) и складываете их. Вслед за этим вы делите эту сумму на N число проб. Наконец, вы извлекаете квадратный корень из этого среднего.

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

90Эту формулу можно сократить, введя маленькое х для обозначения (X-М х ). Тогда формула выглядит так:

(2.1A)

Давайте выпишем данные по условию А из приложения к главе I и одновременно произведем по ним вычисления, указываемые формулой для σ х

Проба	М х	X - М X	x 2
Проба	М х	или х	x 2

















		Σx 2

Поскольку

мс.

91Оценка стандартного отклонения

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

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

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

(2.2)

(2.2А)

Для наших числовых данных:

мс.

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

Таблица, которая приводится ниже, поможет вам запомнить эти положения и формулы.92

	Среднее	Стандартное отклонение
Параметрические характеристики генеральной совокупности (г. с.)
Статистические характеристики выборки
Оцениваемый параметр генеральной совокупности

Задача: Вычислитеσ х иS х для условия Б.

Ответ: σ Б = 15,9;σ Б = 16,4.