Стандартное отклонение - Excel и Google Таблицы

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

СТАНДАРТНОЕ ОТКЛОНЕНИЕ Обзор функции

Функция СТАНДАРТНОЕ ОТКЛОНЕНИЕ Рассчитывает стандартное отклонение для всей генеральной совокупности.

Чтобы использовать функцию рабочего листа Excel СТАНДАРТНОЕ ОТКЛОНЕНИЕ, выберите ячейку и введите:

(Обратите внимание, как появляются входные данные формулы)

Функция СТАНДАРТНОЕ ОТКЛОНЕНИЕ Синтаксис и входы:

1	= СТАНДОТКЛОН (число1; [число2];…)

числа- Значения для получения стандартной дисперсии

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

Всякий раз, когда вы имеете дело с данными, вам нужно выполнить несколько базовых тестов, чтобы лучше понять их. Обычно вы начинаете с вычисления среднего значения с помощью функции Excel AVERAGE <>.

Это даст вам представление о том, где находится «середина» данных. А оттуда вы захотите посмотреть, насколько разбросаны данные вокруг этой средней точки. Вот здесь и появляется стандартное отклонение.

В Excel есть несколько функций для вычисления стандартного отклонения - СТАНДОТКЛОН, СТАНДОТКЛОН.P, СТАНДОТКЛОН.S и DSTDEV. Мы рассмотрим все из них, но сначала давайте узнаем, какое стандартное отклонение является, точно.

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

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

1	48,49,50,51,52

Среднее значение этого набора данных равно 50 (сложите все числа и разделите на n, где n - количество значений в диапазоне).

Теперь посмотрим на следующий набор данных:

1	10,25,50,75,90

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

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

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

Как рассчитывается стандартное отклонение

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

Чтобы вычислить стандартное отклонение, выполните следующий процесс:

1) Рассчитайте среднее значение

Давайте возьмем наш первый набор данных выше: 48,49,50,51,52

Нам уже известно среднее значение (50), которое я подтвердил здесь с помощью функции Excel AVERAGE <>:

1	= СРЕДНИЙ (C4: C8)

2) Вычтите среднее значение из каждого значения в наборе данных

Я сделал это по следующей формуле:

1	= C4- $ H $ 4

Наше среднее значение находится в H4, и я «заблокировал» ссылку на ячейку, поставив знаки доллара перед столбцом и строкой (нажав F4). Это означает, что я могу скопировать формулу вниз по столбцу без обновления ссылки на ячейку.

Результат:

А теперь давайте сделаем паузу на секунду. Если вы посмотрите на новый столбец - вы увидите, что числа здесь равны нулю. Среднее значение этих чисел также равно нулю.

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

3) Возвести различия в квадрат

Мы можем добиться этого, возведя различия в квадрат. Итак, давайте добавим новый столбец и возведем в квадрат числа в столбце D:

1

= D4 * D4

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

4) Рассчитайте дисперсию - среднее значение квадратов разностей.

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

• Если вы используете данные о населении, вы просто берете среднее (суммируете значения и делите на n)
• Если вы используете образец данных, вы суммируете значения и делите их на п-1

Данные о населении означают, что у вас есть «полный набор» ваших данных, например, у вас есть данные о каждом ученике в данном классе.

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

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

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

1	= СРЕДНИЙ (E4: E8)

Итак, у нас есть 2. Эта оценка известна как «дисперсия», и это базовая точка для многих статистических тестов, включая стандартное отклонение. Вы можете прочитать больше о дисперсии на его главной странице: как рассчитать дисперсию в Excel <>.

5) Получите квадратный корень из дисперсии

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

1	= КОРЕНЬ (H4)

И вот результат: стандартное отклонение 1,414.

Поскольку мы извлекли квадратный корень из наших ранее возведенных в квадрат чисел, стандартное отклонение указано в тех же единицах, что и исходные данные. Таким образом, стандартное отклонение здесь составляет 1,414 контрольных точек.

Стандартное отклонение при большем разбросе данных

Ранее у нас был второй пример диапазона данных: 10,25,50,75,90

Ради интереса, давайте посмотрим, что произойдет, когда мы вычислим стандартное отклонение этих данных:

Все формулы точно такие же, как и раньше (обратите внимание, что общее среднее значение по-прежнему равно 50).

Единственное, что изменилось, - это разброс оценок в столбце C. Но теперь наше стандартное отклонение намного выше и составляет 29,832 тестовых балла.

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

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

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

Для этого в Excel есть несколько функций:

• п вычисляет стандартное отклонение для данных о населении (используя точный метод, который мы использовали в приведенном выше примере)
• S вычисляет стандартное отклонение для выборочных данных (используя метод n-1, о котором мы говорили ранее)
• СТАНДОТКЛОН точно так же, как STDEV.S. Это более старая функция, которую заменили STDEV.S и STDEV.P.
• STDEVA очень похож на STDEV.S, за исключением того, что при вычислении он включает текстовые ячейки и логические (ИСТИНА / ЛОЖЬ) ячейки.
• STDEVPA очень похож на STDEV.P, за исключением того, что он включает текстовые ячейки и логические (ИСТИНА / ЛОЖЬ) ячейки при вычислении.

Ух, тут много вариантов! Не пугайтесь - в подавляющем большинстве случаев вы будете использовать либо STDEV.P, либо STDEV.S.

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

Функция Excel STDEV.P

STDEV.P вычисляет стандартное отклонение для данных о населении. Вы используете это так:

1	= СТАНДОТКЛОН.P (C4: C8)

Вы определяете один аргумент в STDEV.P: диапазон данных, для которого вы хотите вычислить стандартное отклонение.

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

Примечание STDEV.P игнорирует любые ячейки, содержащие текст или логические (ИСТИНА / ЛОЖЬ) значения. Если вам нужно включить их, используйте STDEVPA.

Функция Excel STDEV.S

STDEV.S вычисляет стандартное отклонение для выборочных данных. Используйте это так:

1	= СТАНДОТКЛОН.S (C4: C8)

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

Прежде чем перейти к примеру, давайте обсудим разницу между STDEV.S и STDEV.P.

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

Теперь расчет отличается от шага (4) выше, когда мы вычисляем дисперсию - среднее значение квадрата разницы каждой оценки от общего среднего.

Вместо того, чтобы использовать обычный метод - просуммировать все значения и разделить на n, мы суммируем все значения и разделим на п-1:

1	= СУММ (E4: E8) / (СЧЁТ (E4: E8) -1)

В этой формуле:

• СУММ получает сумму квадратов разностей.
• COUNT возвращает наше n, которое мы вычитаем из 1
• Затем мы просто делим нашу сумму на n-1.

На этот раз среднее значение квадратов разностей составляет 2,5 (как вы помните, раньше оно было равно 2, поэтому оно немного выше).

Так почему мы делим на n-1 вместо n при работе с выборочными данными?

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

Если вам действительно интересно узнать, почему, см. Главную страницу о том, как рассчитать дисперсию в Excel <>.

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

1	= КОРЕНЬ (H4)

Получаем 1,581.

STDEV.S выполняет все вышеперечисленные вычисления за нас и возвращает стандартное отклонение выборки всего в одной ячейке. Итак, давайте посмотрим, что из этого получится …

1	= СТАНДОТКЛОН.S (C4: C8)

Ага, снова 1.581.

Функция Excel STDEV

Функция STDEV в Excel работает точно так же, как STDEV.S, то есть вычисляет стандартное отклонение для выборки данных.

Вы используете его таким же образом:

1	= СТАНДОТКЛОН (C4: C8)

Снова получаем тот же результат.

Важная заметка: STDEV - это «функция совместимости», что означает, что Microsoft избавляется от нее. Он по-прежнему работает, поэтому все старые электронные таблицы будут работать в обычном режиме. Но в будущих версиях Excel Microsoft может полностью отказаться от него, поэтому по возможности следует использовать STDEV.S вместо STDEV.

Функция Excel STDEVA

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

• ИСТИННЫЕ значения считаются как 1
• Значения FALSE считаются как 0
• Текстовые строки считаются как 0

Используйте его следующим образом:

1	= СТАНДОТКЛОН (C4: C8)

Еще четыре друга и члена семьи дали в своих тестах оценки. Они показаны в столбце C, а столбец D указывает, как STDEVA интерпретирует эти данные.

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

Функция Excel STDEVPA

STDEVPA вычисляет стандартное отклонение для генеральной совокупности так же, как STDEV.P. Однако он также включает в расчет логические значения и текстовые строки, которые интерпретируются следующим образом:

• ИСТИННЫЕ значения считаются как 1
• Значения FALSE считаются как 0
• Текстовые строки считаются как 0

Вы используете это так:

1	= СТАНДОТКЛОНПА (C4: C12)

Фильтрация данных перед вычислением стандартного отклонения

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

Вы можете сделать это очень легко с помощью функций базы данных Excel: DSTDEV (для образцов) и DSTDEVP (для популяций).

Эти функции позволяют вам создать таблицу критериев, в которой вы можете определить все необходимые фильтры. Функции применяют эти фильтры «за кадром» перед возвратом стандартного отклонения. Таким образом, вам не нужно прикасаться к автофильтру или выводить данные на отдельный лист - все это могут сделать DSTDEV и SDTDEVP.

Дополнительные сведения о функциях Excel DSTDEV и DSTDEVP <> см. На главной странице.

СТАНДАРТНОЕ ОТКЛОНЕНИЕ Функция в Google Таблицах

Функция СТАНДАРТНОГО ОТКЛОНЕНИЯ работает в Google Таблицах точно так же, как и в Excel: