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

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

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

Стандартное отклонение – что это такое

Стандартное (или стандартное) отклонение представляет собой квадратный корень дисперсии. В свою очередь, последний термин относится к степени дисперсии ценностей. Для получения дисперсии, и, как следствие, ее производной в виде стандартного отклонения, существует специальная формула, которая, однако, для нас не столь важна. Он достаточно сложен по своей структуре, но при этом может быть полностью автоматизирован с помощью Excel. Главное — знать, какие параметры передавать в функцию. В целом, как для расчета дисперсии, так и для расчета стандартного отклонения аргументы одни и те же.

1. Сначала получаем среднее арифметическое.
2. После этого каждое начальное значение сравнивается со средним и определяется разница между ними.
3. После этого каждая разность возводится во вторую степень, после чего полученные результаты суммируются.
4. Наконец, последний шаг — деление полученного значения на общее количество элементов в данной выборке.

Получив разницу между одним значением и средним арифметическим всей выборки, мы можем узнать расстояние до него от определенной точки координатной линии. Для новичка вся логика понятна вплоть до третьего шага. Зачем возводить значение в квадрат? Дело в том, что иногда разница может быть отрицательной, а нам нужно получить положительное число. А, как известно, минус, помноженный на минус, дает плюс. А затем нам необходимо определить среднее арифметическое полученных значений. Дисперсия имеет несколько свойств:

1. Если вы выведете дисперсию из одного числа, то она всегда будет равна нулю.
2. Если случайное число умножить на константу А, то дисперсия увеличится в квадрате А. Проще говоря, константу можно вынести из знака дисперсии и возвести во вторую степень.
3. Если к произвольному числу прибавить константу А или вычесть из него, то дисперсия от этого не изменится.
4. Если два случайных числа, обозначаемые, например, переменными X и Y, не зависят друг от друга, то в этом случае для них формула справедлива. Д(Х+Y) = Д(Х) + Д(Y)
5. Если мы внесем изменения в предыдущую формулу и попытаемся определить дисперсию разницы между этими значениями, то она также будет суммой этих дисперсий.

Стандартное отклонение — это математический термин, полученный из дисперсии. Получить его очень просто: достаточно извлечь квадратный корень из дисперсии.

Разница между дисперсией и стандартным отклонением находится, так сказать, исключительно в плоскости единиц. Стандартное отклонение гораздо легче читать, поскольку оно отображается не в квадратах чисел, а непосредственно в значениях. Простыми словами, если в числовой последовательности 1,2,3,4,5 среднее арифметическое равно 3, то, соответственно, стандартным отклонением будет число 1,58. Это говорит нам о том, что в среднем одно число отклоняется от среднего числа (которое в нашем примере составляет 1,58) на XNUMX.

Дисперсия будет тем же числом, только в квадрате. В нашем примере оно чуть меньше 2,5. В принципе, для статистических расчетов можно использовать как дисперсию, так и стандартное отклонение, просто нужно точно знать, с каким индикатором работает пользователь.

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

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

Отличие выборочной совокупности от генеральной совокупности состоит в том, что в первом случае данные обрабатываются непосредственно, на основе чего вычисляется среднее арифметическое и стандартное отклонение. Если мы говорим об общей популяции, то это вся совокупность количественных данных, относящихся к изучаемому явлению. В идеале выборка должна быть полностью репрезентативной. То есть в исследовании должны участвовать люди, которых можно соотнести с общей популяцией в равных пропорциях. Например, если в условной стране 50% мужчин и 50% женщин, то и в выборке должны быть такие же пропорции.

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

Способ 1. Ручной ввод формулы

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

1. Делаем щелчок левой кнопкой мыши по ячейке, в которой будет записана формула получения стандартного отклонения. Вы также можете ввести его в качестве аргумента любой другой функции. В этом случае нужно нажать на строку ввода формулы, а затем начать вводить аргумент, в котором должен отображаться результат.
2. Общая формула выглядит следующим образом: =STDEV.Y(число1(адрес_ячейки1), число2(адрес_ячейки2),…). Если использовать второй вариант, то все делается точно так же, только буква G в названии функции меняется на B. Максимальное количество поддерживаемых аргументов — 255.
3. После того, как ввод формулы завершен, подтверждаем свои действия. Для этого нажмите клавишу ввода.

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

1. Ввод формулы вручную может сэкономить много времени. Пользователь Excel, помнящий формулу и ее синтаксис, имеет существенное преимущество перед человеком, который только начинает и ищет нужную функцию в списке в Мастере функций или на ленте. Кроме того, сам ввод с клавиатуры происходит гораздо быстрее, чем с помощью мыши.
2. Меньше устают глаза. Вам не придется постоянно переключать фокус со стола на окно, потом на другое окно, потом на клавиатуру и снова на стол. Это также помогает существенно сэкономить время и силы, которые затем можно потратить на обработку реальной информации, а не на ведение формул.
3. Ввод формул вручную гораздо более гибок, чем использование следующих двух методов. Пользователь может сразу указать нужные ячейки диапазона, не выделяя их напрямую, или просмотреть всю таблицу сразу, избегая риска, что диалоговое окно ее заблокирует.
4. Использование формул вручную — это своего рода мост к написанию макросов. Конечно, это не поможет вам выучить язык VBA, но формирует правильные привычки. Если человек привык подавать команды компьютеру с помощью клавиатуры, ему будет гораздо проще освоить любой другой язык программирования, в том числе разработку макросов для электронных таблиц.

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

Способ 2. Вкладка «Формулы»

Другой метод, доступный пользователю, желающему получить стандартное отклонение от диапазона, — это использование вкладки «Формулы» в главном меню. Опишем подробнее, что для этого нужно сделать:

1. Выделяем ячейку, в которую мы хотим записать результат.
2. После этого находим на ленте вкладку «Формулы» и переходим на нее.
3. Воспользуемся блоком «Библиотека функций». Есть кнопка «Дополнительные возможности». В списке, который будет, найдем пункт «Статистика». После этого мы выбираем, какую формулу мы собираемся использовать.
4. После этого появится окно для ввода аргументов. В нем указываем все числа, ссылки на ячейки или диапазоны, которые будут принимать участие в расчетах. После того, как мы закончим, нажмите кнопку «ОК».

Преимущества этого метода:

1. Скорость. Этот метод достаточно быстрый и позволяет ввести нужную формулу всего в несколько кликов.
2. Точность. Нет риска случайно написать не ту ячейку или написать не ту букву, а потом потратить время на переработку.

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

Способ 3: Мастер функций

Мастер функций — еще один удобный способ ввода формул для новичков, еще не выучивших названия и синтаксис функций. Кнопка запуска Мастера функций расположена рядом со строкой ввода формулы. Главное его преимущество для новичка на фоне предыдущих способов заключается в подробных подсказках программы, какая функция за что отвечает и какие аргументы в каком порядке вводить. Это две буквы – fx. Нажимаем на него.

После этого появится список функций. Вы можете либо попытаться найти его в полном алфавитном списке, либо открыть категорию «Статистика», где вы также сможете найти этого оператора.

В списке мы видим, что функция STDEV все еще присутствует. Это сделано для того, чтобы старые файлы были совместимы с новой версией Excel. Однако настоятельно рекомендуется использовать новые функции, перечисленные выше, поскольку в какой-то момент эта устаревшая функция может перестать поддерживаться.

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

Заключение

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