Что такое стандартное отклонение — использование функции СТАНДОТКЛОН для расчета стандартного отклонения в Excel

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

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

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

86-1-стандартное отклонение excel

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

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

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

86-2-стандартное отклонение excel

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

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

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

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

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

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

86-3-стандартное отклонение excel

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

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

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

86-4-стандартное отклонение excel

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

Стандартное отклонение формула

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

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

86-6-стандартное отклонение excel

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

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

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


23 комментария

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

  2. Спасибо. Недавно читал об этом на английском. А эта статья помогла закрепить материал. Отдельное спасибо за визуализацию рассчетов!

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

  4. Вот именно в такой визуальной форме и надо для чайников, как я, писать. Тут никакого текста даже и не надо. А вот по логике я так и не понял. возведение в квадрат разницы от средней, суммирование этих квадратов, деление на кол-во, и вычисление корня. В чем логика — я честно не могу допереть. Какая логика заложена в квадрат разницы и в последующем извлечении корня? Что дает аналитику эта схема вычисления? Я не говорю про общее объяснение, которое приводится в сети, типа «среднее квадратичное отклонение», «максимальный разброс от среднего и т.д.», мне бы логику формулы бы

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

  5. Здравствуйте. В наглядной иллюстрации в пункте 6 разве не ошибка? 21,6 это «не вся» погрешность, а только половина от нее; нужно отложить 21,6 как в плюс, так и в минус, т.е. если объединять фигурной скобкой, то должно быть 43,2 ?

  6. Друзья, подскажите, пожалуйста,
    1. чем отличается стандартное отклонение от среднего
    2. а можно стандартное отклонение выразить в %? к примеру, мы рассчитали, что стандотклон 18,9. Понятно, что это много…. а насколько? проценты так хорошо… Но я никогда не видела, чтобы отклонение выдавали так: стандартное отклонение стока магазина составило 57,6 % (18,9/32,8 * 100%) Теперь действительно видно: «Ого, как много!» Хотя мне для работы удобно говорить, что разница между тремя/пяти результатов лежит в пределах погрешности 6%. Т.е. объясню ситуацию: у меня есть три числа х1=34240, х2=29673, х3=35086. Стандотклон = 2912. Могу я для каждого результата сказать: что отклонение будет % стандотклон/х1 для х1, /х2 для х2 и т.д.?

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

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

Ваш e-mail не будет опубликован. Обязательные поля помечены *