Стандартное отклонение Excel: функция и расчет

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

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

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

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

Стандартное отклонение применяется в статистике, экономике, производстве, логистике - везде, где нужно оценить вариативность процессов .

Например, посчитаем стандартное отклонение вручную для следующих данных о продажах:

1. Находим среднее значение = (15 + 18 + 16) / 3 = 16.
2. Вычисляем отклонение каждого значения от среднего.
3. Возводим квадраты отклонений: (-1)² = 1; 2² = 4; 0² = 0.
4. Суммируем квадраты отклонений: 1 + 4 + 0 = 5.
5. Делим сумму на количество значений минус один: 5 / (3 - 1) = 5 / 2 = 2,5.
6. Извлекаем корень квадратный: √2,5 = 1,58.

Получили стандартное отклонение выборки ≈ 1,58. Это и есть типичное отклонение данных от среднего 16.

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

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

СТАНДОТКЛОН считает стандартное отклонение выборки . Подходит, если у вас не вся совокупность данных, а только часть. Использует формулу на основе n-1.

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

СТАНДОТКЛОНА аналогична СТАНДОТКЛОН, но еще учитывает логические значения и текст.

При выборе функции ориентируйтесь на ваши данные и цели анализа. Также обращайте внимание на возможные ошибки.

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

Расчет для одного ряда данных

Давайте посчитаем стандартное отклонение в Excel для одного ряда чисел - объемов продаж за 3 месяца.

1. Вводим исходные данные в столбец, например в диапазон B2:B4.
2. В соседнюю ячейку B5 пишем формулу =СТАНДОТКЛОН(B2:B4).
3. Получаем значение стандартного отклонения для нашего ряда.

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

Это говорит о том, что в среднем значения отклоняются от среднего примерно на 1,58. Чем меньше стандартное отклонение, тем выше стабильность продаж.

Расчет для нескольких рядов данных

Теперь посчитаем стандартное отклонение сразу для 3 видов продукции:

1. Данные по продажам заносим в столбцы B, C и D.
2. В ячейки B21, C21 и D21 пишем формулы =СТАНДОТКЛОН для каждого столбца.
3. Копируем формулы вниз, например, в B22 и C22.
4. Сравниваем стандартные отклонения разных продуктов.

Copy code

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

Расчет показателей стабильности

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

Для этого стандартное отклонение делим на среднее значение и умножаем на 100%. Получаем, на сколько процентов в среднем данные отклоняются от среднего.

Например, формула вариации для продукта 1:

=СТАНДОТКЛОН(B2:B10) / СРЗНАЧ(B2:B10) * 100%

Далее можно ввести условные пороги для определения стабильности продаж:

• Вариация менее 10% - стабильный спрос
• От 10% до 25% - небольшие колебания
• Более 25% - нестабильный спрос

Интерпретация показателей стабильности

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

Теперь видно, что спрос на продукт 1 более изменчив.

Посчитать стандартное отклонение для неполных данных

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

=СТАНДОТКЛОН(B2:B10;1)

Цифра 1 означает, что при отсутствии значения не выдавать ошибку, а считать по имеющимся данным.

Автоматизация расчетов с помощью макросов

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

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

Визуализация стандартного отклонения на графике

Для наглядности строим гистограмму распределения значений и добавляем на нее:

• Линию со средним значением
• Линии на уровне среднего плюс-минус стандартное отклонение

Так видно, в каком диапазоне концентрируются основные значения данных.

Построение гистограммы распределения данных

Гистограмма - наглядный способ показать распределение значений исследуемой величины. Построим гистограмму для наших данных по объемам продаж:

1. Выделяем исходные данные по продажам, например диапазон B2:B10.
2. Переходим на вкладку "Вставка" и выбираем тип диаграммы "Гистограмма".
3. Получаем гистограмму распределения продаж по месяцам.

По горизонтали - значения продаж, по вертикали - сколько раз встречалось данное значение. Видно пики и спады продаж в разные месяцы.

Добавление линий значимых уровней

Чтобы нагляднее отобразить вариативность продаж, добавим на гистограмму дополнительные линии:

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

Эти линии показывают "коридор стабильности". Если значения выходят за него - это аномалии, требующие внимания.

Автоматическое построение гистограммы

Чтобы Excel строил гистограмму и линии значимых уровней автоматически, можно создать обобщенную макрокоманду.

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

Сравнение динамики стандартного отклонения

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

Например, за каждый год рассчитаем стандартное отклонение, а затем построим линейчатую диаграмму этих значений.

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

Сравнение стандартного отклонения между группами

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

• Разные продукты
• Разные регионы
• Отдельные клиенты

Это позволит выявить наименее стабильные направления и своевременно скорректировать бизнес-процессы.