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

Обновлено: 21.11.2024

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

В этом уроке я покажу вам, как рассчитать стандартное отклонение в Excel (используя простые формулы)

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

Это руководство охватывает:

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

Значение стандартного отклонения покажет, насколько набор данных отличается от среднего значения набора данных.

Например, предположим, что у вас есть группа из 50 человек, и вы записываете их вес (в килограммах).

В этом наборе данных средний вес составляет 60 кг, а стандартное отклонение – 4 кг. Это означает, что вес большинства людей находится в пределах 4 кг от среднего веса (который будет составлять 56-64 кг).

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

  • Меньшее значение указывает на то, что точки данных, как правило, ближе к среднему (среднему) значению.
  • Более высокое значение указывает на широкое разнообразие точек данных. Это также может быть в случае, когда в наборе данных много выбросов.

Вычисление стандартного отклонения в Excel

Несмотря на то, что рассчитать стандартное отклонение несложно, вам нужно знать, какую формулу использовать в Excel.

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

Эти шесть формул можно разделить на две группы:

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

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

Опять же, с точки зрения непрофессионала, вы используете термин "население", когда хотите рассмотреть все наборы данных во всем населении. С другой стороны, вы используете термин «выборка», когда использование генеральной совокупности невозможно (или нереально). В таком случае вы выбираете выборку из генеральной совокупности.

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

Итак. это сужает количество формул до трех (функция СТАНДОТКЛОН.С, СТАНДОТКЛОН и СТАНДОТКЛОН)

Теперь давайте разберемся с этими тремя формулами:

  • СТАНДОТКЛОН.С — используйте этот параметр, если ваши данные являются числовыми. Он игнорирует текст и логические значения.
  • STDEVA — используйте это, если хотите включить в расчет текст и логические значения (наряду с числами). Text и FALSE принимаются за 0, а TRUE за 1.
  • СТАНДОТКЛОН – СТАНДОТКЛОН.С был введен в Excel 2010. До этого использовалась функция СТАНДОТКЛОН. Он по-прежнему включен для совместимости с предыдущими версиями.

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

Теперь давайте посмотрим, как использовать его в Excel.

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

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

Вот синтаксис функции СТАНДОТКЛОН.С:

  • Число1 — это обязательный аргумент в формуле. Первый числовой аргумент соответствует первому элементу выборки совокупности. Вы также можете использовать именованный диапазон, один массив или ссылку на массив вместо аргументов, разделенных запятыми.
  • Число2, … [Необязательный аргумент в формуле] Можно использовать до 254 дополнительных аргументов. Они могут относиться к точке данных, именованному диапазону, отдельному массиву или ссылке на массив.

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

Пример. Расчет стандартного отклонения для данных о весе

Предположим, у вас есть набор данных, как показано ниже:

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

Если вы используете Excel 2007 или более ранние версии, у вас не будет функции СТАНДОТКЛОН.С. В этом случае вы можете использовать приведенную ниже формулу:

Приведенная выше формула возвращает значение 2.81, что указывает на то, что большинство людей в группе будут иметь вес в диапазоне 69,2–2,81 и 69,2+2,81.

Обратите внимание: когда я говорю «большинство людей», я имею в виду нормальное распределение выборки (то есть 68 % выборки находится в пределах одного стандартного отклонения от среднего).

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

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

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

Стандартное отклонение — это число, показывающее, насколько далеко число от среднего.

<р>1. Например, приведенные ниже числа имеют среднее значение 10.

Объяснение: все числа одинаковы, что означает отсутствие различий. В результате числа имеют стандартное отклонение, равное нулю. Функция СТАНДОТКЛОН — старая функция. Microsoft Excel рекомендует использовать новую функцию STEDV.S, которая дает точно такой же результат.

<р>2. Приведенные ниже числа также имеют среднее значение 10.

Объяснение: цифры близки к среднему. В результате цифры имеют низкое стандартное отклонение.

<р>3. Приведенные ниже числа также имеют среднее значение 10.

Объяснение: цифры разбросаны. В результате цифры имеют высокое стандартное отклонение.

СТАНДОТКЛОН.P

Функция СТАНДОТКЛОН.П (P означает "Население") в Excel вычисляет стандартное отклонение на основе всей совокупности. Например, вы обучаете группу из 5 студентов. У вас есть результаты тестов всех учащихся. Вся популяция состоит из 5 точек данных. Функция СТАНДОТКЛОН.П использует следующую формулу:

В этом примере x1 = 5, x2 = 1, x3 = 4, x4 = 6, x5 = 9, μ = 5 (среднее), N = 5 (количество точек данных).

<р>1. Вычислите среднее значение (μ).

<р>2. Для каждого числа рассчитайте расстояние до среднего.

<р>3. Для каждого числа возведите это расстояние в квадрат.

<р>4. Суммируйте (∑) эти значения.

<р>5. Разделите на количество точек данных (N = 5).

<р>7. К счастью, функция СТАНДОТКЛОН.П в Excel может выполнить все эти шаги за вас.

СТАНДОТКЛОН.С

Функция СТАНДОТКЛОН.С (S означает выборка) в Excel оценивает стандартное отклонение на основе выборки. Например, вы обучаете большую группу студентов. У вас только результаты тестов пяти учащихся. Размер выборки равен 5. Функция СТАНДОТКЛОН.С использует следующую формулу:

В этом примере x1=5, x2=1, x3=4, x4=6, x5=9 (те же числа, что и выше), x̄=5 (среднее значение выборки), n=5 (размер выборки).

<р>1. Повторите шаги 1–5 выше, но на шаге 5 разделите на n–1 вместо N.

<р>3. К счастью, функция СТАНДОТКЛОН.С в Excel может выполнить все эти шаги за вас.

Примечание: почему мы делим на n - 1, а не на n, когда оцениваем стандартное отклонение на основе выборки? Поправка Бесселя утверждает, что деление на n-1 вместо n дает лучшую оценку стандартного отклонения.

Дисперсия

Дисперсия — это квадрат стандартного отклонения. Это так просто. Иногда проще использовать дисперсию при решении статистических задач.

<р>1. Приведенная ниже функция VAR.P вычисляет дисперсию на основе всей совокупности.

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

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

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

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

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

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

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

Просто чтобы вы знали

Зарегистрируйте бесплатную учетную запись Jotform, чтобы создавать мощные онлайн-формы за считанные минуты — без кодирования.

Использование стандартного отклонения

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

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

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

Excel упрощает расчет стандартного отклонения. Но сначала важно понять шесть формул стандартного отклонения в Excel.

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

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

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

  • СТДЕВ.С. Эта формула используется, когда данные являются числовыми, что означает, что текст и логические значения игнорируются.
  • СТДЕВА. Эта формула используется, когда текст и логические значения включаются в расчет вместе с числами. Текст и «ЛОЖЬ» читаются как 0, а ИСТИНА понимается как 1.
  • СТАНДОТКЛОН. Эта формула совместима со старыми версиями Excel (2007 или более ранними), но выполняет ту же функцию, что и СТАНДОТКЛОН.S (используется в любой программе Excel после 2007 г.).

Использование функции СТАНДОТКЛОН.С

Повторюсь, STDEV.S использует числовые значения и игнорирует текстовые и логические значения.

Синтаксис функции СТАНДОТКЛОН.С, используемый в Excel, следующий: СТАНДОТКЛОН.С(число1,[число 2],…).

  • Номер1. Это число является обязательным аргументом в формуле. Первое число соответствует первому элементу выборки. Вместо аргументов, разделенных запятыми, здесь можно использовать именованный диапазон, отдельный массив или ссылку на массив.
  • Номер2. Это необязательный аргумент в формуле. Они могут ссылаться на точку данных, именованный диапазон, отдельный массив или ссылку на массив. Можно использовать до 254 дополнительных аргументов.

На практике

Допустим, есть набор данных для диапазона весов из выборки населения. Используя числа, перечисленные в столбце A, формула при применении будет выглядеть следующим образом: =СТАНДОТКЛОН.С(A2:A10).

Взамен Excel предоставит стандартное отклонение примененных данных, а также среднее значение. Если бы среднее значение равнялось 150, а стандартное отклонение равно 2, это означало бы, что большинство людей в группе имели вес в диапазоне 150–2 или 150+2.

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

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

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

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

Поправка Бесселя, STDEV.P и STDEV.S

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

Коррекция Бесселя – это корректировка, вносимая для устранения систематической ошибки, возникающей при работе с выборочными данными. Он появляется в формулах как n-1, где n — количество. При работе с выборочной совокупностью поправка Бесселя может дать более точную оценку стандартного отклонения.

В контексте Excel и стандартного отклонения важно знать следующее:

  • Функция СТАНДОТКЛОН.С использует поправку Бесселя.
  • Функция СТАНДОТКЛОН.П не работает.

Когда следует использовать STDEV.S, который включает поправку Бесселя? Это зависит.

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

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

Вычисление стандартного отклонения вручную

На приведенном ниже экране показано, как вручную рассчитать стандартное отклонение в Excel.

В столбце D вычисляется отклонение, которое представляет собой значение минус среднее значение. Формула в D5, скопированная ниже:

В столбце E показаны квадраты отклонений. Формула в E5, скопированная ниже:

В H5 мы рассчитываем стандартное отклонение для генеральной совокупности по следующей формуле:

В H6 мы рассчитываем стандартное отклонение для выборки по формуле, использующей поправку Бесселя:

Старые функции

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

  • СТАНДОТКЛОН.P заменяет функцию СТАНДОТКЛОН с идентичным поведением.
  • СТАНДОТКЛОН.S заменяет функцию СТАНДОТКЛОН с таким же поведением.

Несмотря на то, что STDEVP и STDEV по-прежнему существуют для обеспечения обратной совместимости, Microsoft рекомендует вместо них использовать более новые функции STDEV.P и STDEV.S.

Читайте также: