Как активировать функцию стандотклон в excel

Как работает стандартное отклонение в Excel

Добрый день!

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

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

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

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

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

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

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

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

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

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

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

• Число1, число2, … — являют собой генеральную совокупность значений и имеют только числовые значения или же ссылки на них. Формула поддерживает до 255 числовых значений.

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

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

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

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

Функции СТАНДОТКЛОН.В, СТАНДОТКЛОН.Г, СТАНДОТКЛОНА и СТАНДОТКЛОНПА в Excel

Функция СТАНДОТКЛОН.В возвращает значение стандартного отклонения, рассчитанного для определенного диапазона числовых значений.

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

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

Функция СТАНДОТЛОНПА возвращает значение стандартного отклонения для всей генеральной совокупности, переданной в качестве ее аргументов.

Примеры использования СТАНДОТКЛОН.В, СТАНДОТКЛОН.Г, СТАНДОТКЛОНА и СТАНДОТКЛОНПА

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

Таблица исходных данных:

Вначале рассчитаем среднее количество клиентов, с которыми работали менеджеры ежедневно:

Данная функция выполняет расчет среднего арифметического значения для диапазона B2:B11, содержащего данные о количестве клиентов, принимаемых ежедневно первым менеджером. Аналогично рассчитаем среднее количество клиентов за день у второго менеджера. Получим:

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

B2:B11 – диапазон исследуемых значений. Аналогично определим стандартное отклонение для второго менеджера и получим следующие результаты:

Как видно, показатели работы первого менеджера отличаются высокой вариабельностью (разбросом) значений, в связи с чем среднее арифметическое значение абсолютно не отражает реальную картину эффективности работы. Отклонение 1,2 свидетельствует о более стабильной, а, значит, и эффективной работе второго менеджера.

Пример использования функции СТАНДОТКЛОНА в Excel

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

Таблица исходных данных:

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

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

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

Пример функции СТАНДОТКЛОН.Г в Excel

Пример 3. Определить эффективность подготовки студентов к экзамену для всех групп университета.

Примечание: в отличие от предыдущего примера, будет анализироваться не выборка (несколько групп), а все число студентов – генеральная совокупность. Студенты, не сдавшие экзамен, не учтены.

Заполним таблицу данных:

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

Для определения отклонения введем формулу:

В результате получим:

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

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

В результате получим:

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

Особенности использования СТАНДОТКЛОН.В, СТАНДОТКЛОН.Г, СТАНДОТКЛОНА и СТАНДОТКЛОНПА

Функции СТАНДОТКЛОНА И СТАНДОТКЛОНПА имеют идентичную синтаксическую запись типа:

=ФУНКЦИЯ (значение1; [значение2];…)

• ФУНКЦИЯ – одна из двух рассмотренных выше функций;
• значение1 – обязательный аргумент, характеризующий одно из значений выборки (либо генеральной совокупности);
• [значени2] – необязательный аргумент, характеризующий второе значение исследуемого диапазона.

1. В качестве аргументов функций могут быть переданы имена, числовые значения, массивы, ссылки на диапазоны числовых данных, логические значения и ссылки на них.
2. Обе функции игнорируют пустые значения и текстовые данные, содержащиеся в диапазоне переданных данных.
3. Функции возвращают код ошибки #ЗНАЧ!, если в качестве аргументов были переданы значения ошибок или текстовые данные, которые не могут быть преобразованы в числовые значения.

Функции СТАНДОТКЛОН.В и СТАНДОТКЛОН.Г имеют следующую синтаксическую запись:

• ФУНКЦИЯ – любая из функций СТАНДОТКЛОН.В или СТАНДОТКЛОН.Г;
• число1 – обязательный аргумент, характеризующий числовое значение, взятое из выборки или всей генеральной совокупности;
• число2 – необязательный аргумент, характеризующий второе числовое значение исследуемого диапазона.

Примечание: обе функции не включают в процесс вычисления числа, представленные в виде текстовых данных, а также логические значения ИСТИНА и ЛОЖЬ.

1. Стандартное отклонение широко используется в статистических расчетах, когда нахождение среднего значения диапазона величин не дает верное представление о распределении данных. Оно демонстрирует принцип распределения величин относительно среднего значения в конкретной выборке или всей последовательности целиком. В Примере 1 будет наглядно рассмотрено практическое применение данного статистического параметра.
2. Функции СТАНДОТКЛОНА и СТАНДОТКЛОН.В следует использовать для анализа только части генеральной совокупности и производят расчет по первой формуле, а СТАНДОТКЛОН.Г и СТАНДОТКЛОНПА должны принимать на вход данные о всей генеральной совокупности и производят расчет по второй формуле.
3. В Excel содержатся встроенные функции СТАНДОТКЛОН и СТАНДОТКЛОНП, оставленные для совместимости с более старыми версиями Microsoft Office. Они могут быть не включены в более поздние версии программы, поэтому их использование не рекомендуется.
4. Для нахождения стандартного отклонения используются две распространенные формулы: S=√((∑_(i=1)^n▒(x_i-x_ср )^2 )/(n-1)) и S=√((∑_(i=1)^n▒(x_i-x_ср )^2 )/n), где:

• S – искомое значение стандартного отклонения;
• n – рассматриваемый диапазон значений (выборка);
• x_i – отдельно взятое значение из выборки;
• x_ср – среднее арифметическое значение для рассматриваемого диапазона.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Как активировать функцию стандотклон в excel

Поиск расхождений в учете (1С и другие) СТАНДОТКЛОН

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

Особенно это становится актуальным при закрытии месяца и, тем более, при закрытии квартала .

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

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

Такой функцией является СТАНДОТКЛОН , а именно ее вариация в EXCEL 2010 (и старше) СТАНДОТКЛОН.В

Для поиска расхождений и определения приоритета исправления ошибок рассмотрим пример:

1. Добавим вспомогательный столбец «ОТКЛОНЕНИЕ»
   
   
   
2. Введём в первую ячейку =СТАНД. выберем функцию СТАНДОТКЛОН.В
   
   
3. Выделим ряд со значениями остатков для первого товара и нажмем ENTER.
   
4. Протянем функцию вниз .
   
5. Для определения приоритета расхождений можно воспользоваться фильтром и сортировкой по убыванию . Наибольшие расхождения будут первыми.
   Обратите внимание, что результатом бубудт именно СТАНДАРТНЫЕ ОТКЛОНЕНИЯ, рассчитанные по статистической формуле, а не реальная разница. Именно поэтому данные являются сравнимыми.
   

Если материал Вам понравился или даже пригодился, Вы можете поблагодарить автора, переведя определенную сумму по кнопке ниже:
(для перевода по карте нажмите на VISA и далее «перевести»)