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

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

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

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

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

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

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

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

СРЗНАЧ(B2:B11)

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

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

СТАНДОТКЛОН.В(B2:B11)

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

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



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

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

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

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

СТАНДОТКЛОНА(A2:A11)

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

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

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

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

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

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

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

СРЗНАЧ(B2:B21)

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

СТАНДОТКЛОН.Г(B2:B21)

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

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

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

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

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

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

СТАНДОТКЛОНПА(B2:B21)

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

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

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

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

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

Описание:

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

Примечания:

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

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

ФУНКЦИЯ(число1;[число2];…)

Описание:

• ФУНКЦИЯ – любая из функций СТАНДОТКЛОН.В или СТАНДОТКЛОН.Г;
• число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. Если это делать самому вручную, уйдет очень много времени, можно допустить уйму ошибок. Сегодня мы рассмотрим, как разложить математические формулы на простые функции. Давайте разберем несколько самых простых, быстрых и удобных способов расчёта, которые позволят все сделать в считанные минуты.

Вычисляем дисперсию

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

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

Чтобы вычислить мат. ожидание в программе будет применяться функция ДИСП.Г, а ее синтаксис выглядит следующим образом «=ДИСП.Г(Число1;Число2;…)».

Возможно применить максимум 255 аргументов, не более. Аргументами могут быть простые числа или ссылки на ячейки, в которых они указаны. Давайте рассмотрим, как посчитать дисперсию в Microsoft Excel:

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

2. Откроется оболочка управления функциями. Там нужно искать функцию «ДИСП.Г», которая может быть в категории «Статистические» или «Полный алфавитный перечень». Когда она будет найдена, следует выделить ее и кликнуть «ОК».

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

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

Вот так несложно можно найти дисперсию в Excel.

Производим расчет по выборке

В данном случае выборочная дисперсия в Excel высчитывается с указанием в знаменателе не общего количества чисел, а на одно меньше. Это делается для более меньшей погрешности при помощи специальной функции ДИСП.В, синтаксис которой =ДИСП.В(Число1;Число2;…). Алгоритм действий:

• Как и в предыдущем методе нужно выделить ячейку для результата.
• В мастере функции следует найти «ДИСП.В» в категории «Полный алфавитный перечень» или «Статистические».

• Далее появится окно, и действовать следует также, как и в предыдущем методе.

Видео: Расчет дисперсии в Excel

Заключение

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

Функция стандартное отклонение это уже из разряда высшей математики относящейся к статистики. В Excel существует несколько вариантов использования Функции стандартного отклонения это:

• Функция СТАНДОТКЛОНП.
• Функция СТАНДОТКЛОН.
• Функция СТАНДОТКЛОНПА

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

Предисловие

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

Итак, Стандартное отклонение - это оценка среднеквадратического отклонения случайной величины x относительно её математического ожидания на основе несмещённой оценки её дисперсии)))) Не пугайтесь не понятных слов, потерпите и Вы все поймете!

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

Теперь стандартное отклонение - оценка среднеквадратического отклонения случайной величины x относительно её математического ожидания на основе несмещённой оценки её дисперсии:

Дисперсия;

- i -й элемент выборки;

Объём выборки;

Среднее арифметическое выборки:

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

Правило трёх сигм () - практически все значения нормально распределённой случайной величины лежат в интервале . Более строго - приблизительно с 0,9973 вероятностью значение нормально распределённой случайной величины лежит в указанном интервале (при условии, что величина истинная, а не полученная в результате обработки выборки). Мы же будем использовать округленный интервал 0,1

Если же истинная величина неизвестна, то следует пользоваться не , а s . Таким образом, правило трёх сигм преобразуется в правило трёх s . Именно это правило поможет нам определить стабильность продаж, но об этом чуть позже...

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

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

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

Функция СТАНДОТКЛОНП

СТАНДОТКЛОНП (число1 ;число2 ;... )

Число1, число2,.. - от 1 до 30 числовых аргументов, соответствующих генеральной совокупности.

Теперь разберем на примере:

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

Продолжение следует!!!

И снова здравствуйте. Ну что!? Выдалась свободная минутка. Давайте продолжим?

И так стабильность продаж при помощи Функции СТАНДОТКЛОНП

Для наглядности возьмем несколько импровизированных товаров:

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

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

И так у нас есть продажи, теперь нам нужно рассчитать средние значения продаж по периодам.

Формула среднего значения СРЗНАЧ(данные периода) в моем случае формула выглядит вот так =СРЗНАЧ(C6:E6)

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

Ctrl + Вниз курсор переместиться в коней списка.

Ctrl + Вправо, курсор переместиться в правую часть таблицы. Еще раз вправо и мы попадем на столбец с формулой.

Теперь зажимаем

Ctrl + Shift и нажимаем вверх. Так мы выделим область протягивания формулы.

И комбинация клавиш Ctrl + D протянет функцию там где нам надо.

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

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

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

Вот все расчеты и готовы. Но как понять, что продается стабильно, а что нет? Просто проставим условность XYZ где,

Х - это стабильно

Y - с не большими отклонениями

Z - не стабильно

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

Если в пределах от 10 до 25 процентов - это будет Y.

И если значения вариации превышает 25% - это не стабильность.

Что бы правильно задать буквы каждому товару, воспользуемся формулой ЕСЛИ подробнее про . В моей таблице данная функция будет выглядеть так:

ЕСЛИ(H6<0,1;"X";ЕСЛИ(H6<0,25;"Y";"Z"))

Соответственно все формулы протягиваем по всем наименованиям.

Постараюсь сразу ответить на вопрос, Почему интервалы 10% и 25%?

На самом деле интервалы могут быть иными, все зависит от конкретной задачи. Я специально показал Вам утрированные значения продаж, где разница видна на "глаз". Очевидно, что товар 1 продается не стабильно, но динамика показывает увеличение продаж. Такой товар оставляем в покое...

А вот товар 2, тут уже дистабилизация на лицо. И наши расчеты показывают Z, что говорит нам о не стабильности продаж. Товар 3 и Товар 5 показывают стабильные показатели, обратите внимание, Вариация в пределах 10%.

Т.е. Товар 5 с показателями 45, 46 и 45 показывает вариацию 1%, что является стабильным числовым рядом.

А вот Товар 2 с показателями 10, 50 и 5 показывают вариацию в 93%, что является НЕ стабильным числовым рядом.

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

В моей таблице не получилось "Y", я думаю для наглядности числового ряда, его нужно добавить. Пририсую Товар 6...

Вот видите, числовой ряд 40, 50 и 30 показывает 20% вариации. Вроде большой погрешности нет, но все же разброс существенный...

И так под итожим:

10,50,5 - Z не стабильность. Вариация более 25%

40,50,30 - Y на этот товар можно обратить внимание, и улучшить его продажи. Вариация меньше 25%, но больше 10%

45,46,45 - X это стабильность, с этим товаром пока ничего делать не надо. Вариация меньше 10%

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

Необходимо вмешательство менеджмента для выявления причин отклонений.

Для построения контрольной карты я использую исходные данные, среднее значение (μ) и стандартное отклонение (σ). В Excel: μ = СРЗНАЧ($F$3:$F$15), σ = СТАНДОТКЛОН($F$3:$F$15)

Сама контрольная карта включает: исходные данные, среднее значение (μ), нижнюю контрольную границу (μ – 2σ) и верхнюю контрольную границу (μ + 2σ):

Скачать заметку в формате , примеры в формате

Посмотрев на представленную карту, я заметил, что исходные данные демонстрируют вполне различимую линейную тенденцию к снижению доли накладных расходов:

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

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

Линию тренда Excel позволяет построить с помощью функции ПРЕДСКАЗ. Нам потребуется дополнительный ряд А3:А15, чтобы известные значения Х были непрерывным рядом (номера кварталов такой непрерывный ряд не образуют). Вместо среднего значения в столбце Н вводим функцию ПРЕДСКАЗ:

Стандартное отклонение σ (функция СТАНДОТКЛОН в Excel) вычисляется по формуле:

К сожалению, я не нашел в Excel функции для такого определения стандартного отклонения (по отношению к тренду). Задачу можно решить с помощью формулы массива. Кто не знаком с формулами массива, предлагаю сначала почитать .

Формула массива может возвращать одно значение или массив. В нашем случае формула массива вернет одно значение:

Давайте подробнее изучим, как работает формула массива в ячейке G3

СУММ(($F$3:$F$15-$H$3:$H$15)^2) определяет сумму квадратов разностей; фактически формула считает следующую сумму = (F3 – H3) 2 + (F4 – H4) 2 + … + (F15 – H15) 2

СЧЁТЗ($F$3:$F$15) – число значений в диапазоне F3:F15

КОРЕНЬ(СУММ(($F$3:$F$15-$H$3:$H$15)^2)/(СЧЁТЗ($F$3:$F$15)-1)) = σ

Значение 6,2% есть точка нижней контрольной границы = 8,3% – 2 σ

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

H4 – 2*КОРЕНЬ(СУММ(($F$3:$F$15-$H$3:$H$15)^2)/(СЧЁТЗ($F$3:$F$15)-1))

необходимо нажать не Enter, а Ctrl + Shift + Enter. Не пытайтесь ввести фигурные скобки с клавиатуры – формула массива не заработает. Если требуется отредактировать формулу массива, сделайте это так же, как и с обычной формулой, но опять же по окончании редактирования нажмите не Enter, а Ctrl + Shift + Enter.

Формулу массива, возвращающую одно значение, можно «протаскивать», как и обычную формулу.

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

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

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

Что такое коэффициент вариации и для чего он нужен?

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

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

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

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

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

Рассчитываем коэффициент в Экселе

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

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

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

1. Откройте вкладку «Главная».
2. Найдите в ней категорию «Формат ячеек » и выберите необходимый параметр.

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

Выберите «Процентный», а при необходимости укажите число десятичных знаков

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

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