Информация в учебном портале постоянно обновляется!

Функция стандартное отклонение

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

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

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

 Предисловие

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

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

Чтобы рассчитать Стандартное отклонение, нам нужно выяснить Среднеквадратическое отклонение по формуле

Функция стандартное отклонение

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

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

Функция стандартное отклонение

Где,

 \sigma^2\,\! — дисперсия;

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

n\,\! — объём выборки;

\bar{x}\,\! — среднее арифметическое выборки:

Функция стандартное отклонение

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

Правило трёх сигм (3\sigma\,\!) — практически все значения нормально распределённой случайной величины лежат в интервале \left(\bar{x}-3\sigma;\bar{x}+3\sigma\right). Более строго — приблизительно с 0,9973 вероятностью значение нормально распределённой случайной величины лежит в указанном интервале (при условии, что величина \bar{x} истинная, а не полученная в результате обработки выборки). Мы же будем использовать округленный интервал 0,1

Если же истинная величина \bar{x} неизвестна, то следует пользоваться не \sigma, а 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%

 

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

 

Ах, да чуть не забыл :) Вот ссылка на файл, который представлен в статье. Скачивайте смотрите формулы... прямая ссылка.

 

Понравилась статья? Отблагодари автора ;)

 

Комментарии   

0 #11 heightgain.myblog.de 09.05.2017 02:26
What's up i am kavin, its my first occasion to commenting anyplace,
when i read this paragraph i thought i could also create comment
due to this brilliant paragraph.
Цитировать
0 #10 manicure 05.05.2017 08:53
Really no matter if someone doesn't be aware of afterward its up to other
users that they will assist, so here it occurs.
Цитировать
+1 #9 manicure 27.04.2017 15:20
Your mode of telling everything in this piece of writing
is actually nice, all can effortlessly be aware of it, Thanks
a lot.
Цитировать
+3 #8 Наталья 07.12.2016 21:43
Спасибо, самый лучший сайт. Приходится учиться на ходу. Все понятно.
Цитировать
+3 #7 Николай 25.10.2016 18:52
Спасибо, очень доходчиво и спасибо за файлик
Цитировать
+3 #6 Владимир 15.09.2016 16:55
Огромное спасибо за статью!!!
Цитировать
+4 #5 Ульянкин Дмитрий 15.04.2016 16:51
Ну вот, продолжение готово. Надеюсь я успел помочь 30 аналитикам :)
Буду рад вашим комментариям по поводу этой работы.
Цитировать
+3 #4 Дмитрий Ульянкин 03.03.2016 05:04
Цитирую ferdinand:
Ну и где Вы потерялись??:( Жизнь будущих 30 аналитиков зависит от Вас!

Спасибо, что следите за моим блогом!!! Вас интересует именно эта тема?

Цитирую ferdinand:
У Вас самый крутой учебник в сети - наш единственный шанс сдать экзамен!


Спасибо, очень приятно :)
Цитировать
+3 #3 ferdinand 02.03.2016 21:50
Ну и где Вы потерялись??:( Жизнь будущих 30 аналитиков зависит от Вас! У Вас самый крутой учебник в сети - наш единственный шанс сдать экзамен!
Цитировать
+3 #2 Дмитрий Ульянкин 11.08.2015 09:49
Цитирую Anton:
Дмитрий, а когда Вы напишите продолжение статьи. Жду с нетерпением!!!


Уже скоро )))
Спасибо Вам за интерес к моей работе. Надеюсь Всем читателям данная информация является полезной.
Цитировать
+4 #1 Anton 11.08.2015 09:16
Дмитрий, а когда Вы напишите продолжение статьи. Жду с нетерпением!!!
Цитировать

Добавить комментарий


Защитный код
Обновить

КАК ПЛАНИРОВАТЬ ПРОМО АКЦИИ ЗА 5 МИН.

планирование промо акции

ЭКОНОМЬ СВОЕ ВРЕМЯ

БУДЬ В КУРСЕ!

Получайте новые уроки по аналитике продаж в Excel БЕСПЛАТНО!!!

email рассылки Конфиденциальность гарантирована
email рассылки

Яндекс.Метрика