Свойства дисперсии
Свойство 1. Дисперсия постоянной величины A равна (нулю).
Свойство 2. Если случайную величину умножить на постоянную А, то дисперсия этой случайной величины увеличится в А 2 раз. Другими словами, постоянный множитель можно вынести за знак дисперсии, возведя его в квадрат.
Свойство 3. Если к случайной величине добавить (или отнять) постоянную А, то дисперсия останется неизменной.
Свойство 4. Если случайные величины X и Y независимы, то дисперсия их суммы равна сумме их дисперсий.
Свойство 5. Если случайные величины X и Y независимы, то дисперсия их разницы также равна сумме дисперсий.
Формула
Помогает рассчитать среднее квадратичное отклонение в excel формула, которая автоматически предусмотрена в программе Excel. Чтобы ее найти, необходимо найти в Экселе раздел формулы, а уже там выбрать ту, которая имеет название СТАНДОТКЛОН, так что очень просто.
После этого перед вами появится окошко, в котором нужно будет ввести данные для вычисления. В частности, в специальные поля следует вписать два числа, после чего программа сама высчитает стандартное отклонение по выборке.
Бесспорно, математические формулы и расчеты – вопрос достаточно сложный, и не все пользователи с ходу могут с ним справиться. Тем не менее, если копнуть немного глубже и чуть более детально разобраться в вопросе, оказывается, что не все так уж и печально. Надеюсь, на примере вычисления среднеквадратичного отклонения вы в этом убедились.
Коэффициент вариации в статистике: примеры расчета
Как доказать, что закономерность, полученная при изучении экспериментальных данных, не является результатом совпадения или ошибки экспериментатора, что она достоверна? С таким вопросом сталкиваются начинающие исследователи.Описательная статистика предоставляет инструменты для решения этих задач. Она имеет два больших раздела – описание данных и их сопоставление в группах или в ряду между собой.
- Показатели описательной статистики
- Среднее арифметическое
- Стандартное отклонение
- Коэффициент вариации
- Расчёты в Microsoft Ecxel 2016
Среднее арифметическое
Итак, представим, что перед нами стоит задача описать рост всех студентов в группе из десяти человек. Вооружившись линейкой и проведя измерения, мы получаем маленький ряд из десяти чисел (рост в сантиметрах):
168, 171, 175, 177, 179, 187, 174, 176, 179, 169.
Если внимательно посмотреть на этот линейный ряд, то можно обнаружить несколько закономерностей:
- Ширина интервала, куда попадает рост всех студентов, – 18 см.
- В распределении рост наиболее близок к середине этого интервала.
- Встречаются и исключения, которые наиболее близко расположены к верхней или нижней границе интервала.
Совершенно очевидно, что для выполнения задачи по описанию роста студентов в группе нет необходимости приводить все значения, которые будут измеряться.
Для этой цели достаточно привести всего два, которые в статистике называются параметрами распределения. Это среднеарифметическое и стандартное отклонение от среднего арифметического.
Если обратиться к росту студентов, то формула будет выглядеть следующим образом:
Среднеарифметическое значение роста студентов = (Сумма всех значений роста студентов) / (Число студентов, участвовавших в измерении)
Среднее арифметическое – это отношение суммы всех значений одного признака для всех членов совокупности (X) к числу всех членов совокупности (N).
Если применить эту формулу к нашим измерениям, то получаем, что μ для роста студентов в группе 175,5 см.
Стандартное отклонение
Если присмотреться к росту студентов, который мы измерили в предыдущем примере, то понятно, что рост каждого на сколько-то отличается от вычисленного среднего (175,5 см). Для полноты описания нужно понять, какой является разница между средним ростом каждого студента и средним значением.
На первом этапе вычислим параметр дисперсии. Дисперсия в статистике (обозначается σ2 (сигма в квадрате)) – это отношение суммы квадратов разности среднего арифметического (μ) и значения члена ряда (Х) к числу всех членов совокупности (N). В виде формулы это рассчитывается понятнее:
Значения, которые мы получим в результате вычислений по этой формуле, мы будем представлять в виде квадрата величины (в нашем случае – квадратные сантиметры). Характеризовать рост в сантиметрах квадратными сантиметрами, согласитесь, нелепо. Поэтому мы можем исправить, точнее, упростить это выражение и получим среднеквадратичное отклонение формулу и расчёт, пример:
Таким образом, мы получили величину стандартного отклонения (или среднего квадратичного отклонения) – квадратный корень из дисперсии. С единицами измерения тоже теперь все в порядке, можем посчитать стандартное отклонение для группы:
Получается, что наша группа студентов исчисляется по росту таким образом: 175,50±5,25 см.
Расчёты в Microsoft Ecxel 2016
Можно рассчитать описанные в статье статистические показатели в программе Microsoft Excel 2016, через специальные функции в программе. Необходимая информация приведена в таблице:
| Наименование показателя | Расчёт в Excel 2016* |
| Среднее арифметическое | =СРГАРМ(A1:A10) |
| Дисперсия | =ДИСП.В(A1:A10) |
| Среднеквадратический показатель | =СТАНДОТКЛОН.В(A1:A10) |
| Коэффициент вариации | =СТАНДОТКЛОН.Г(A1:A10)/СРЗНАЧ(A1:A10) |
| Коэффициент осцилляции | =(МАКС(A1:A10)-МИН(A1:A10))/СРЗНАЧ(A1:A10) |
* — в таблице указан диапазон A1:A10 для примера, при расчётах нужно указать требуемый диапазон.
Итак, обобщим информацию:
- Среднее арифметическое – это значение, позволяющее найти среднее значение показателя в ряду данных.
- Дисперсия – это среднее значение отклонений возведенное в квадрат.
- Стандартное отклонение (среднеквадратичное отклонение) – это корень квадратный из дисперсии, для приведения единиц измерения к одинаковым со среднеарифметическим.
- Коэффициент вариации – значение отклонений от среднего, выраженное в относительных величинах (%).
Отдельно следует отметить, что все приведённые в статье показатели, как правило, не имеют собственного смысла и используются для того, чтобы составлять более сложную схему анализа данных. Исключение из этого правила — коэффициент вариации, который является мерой однородности данных.
Коэффициент вариации
Все показатели, рассмотренные выше, имеют привязку к масштабу исходных данных и не позволяют получить образное представление о вариации анализируемой совокупности. Для получения относительной меры разброса данных используют коэффициент вариации, который рассчитывается путем деления среднего квадартического отклонения на среднее арифметическое значение. Математическая формула такова:
В Экселе нет готовой функции для расчета коэффициента вариации, что не есть большая проблема. Расчет можно произвести простым делением стандартного отклонения на среднее значение. Для этого в строке формул пишем:
В скобках должен быть указан диапазон данных. При необходимости используется среднее квадратическое отклонение по выборке (СТАНДОТКЛОН.В).
Коэффициент вариации обычно выражается в процентах, поэтому ячейку с формулой можно обрамить процентным форматом. Нужная кнопка находится на ленте на закладке «Главная»:
Изменить формат также можно, выбрав «Формат ячеек» из выпадающего списка после выделения нужной ячейки правой кнопкой мышки.
Коэффициент вариации, в отличие от других показателей разброса значений, используется как самостоятельный и весьма информативный индикатор вариации данных. В статистике принято считать, что если коэффициент вариации менее 33%, то совокупность данных является однородной, если более 33%, то – неоднородной. Эта информация может быть полезна для предварительного описания данных и определения возможностей проведения дальнейшего анализа. Кроме того, коэффициент вариации, измеряемый в процентах, позволяет сравнивать степень разброса различных данных независимо от их масштаба и единиц измерений. Полезное свойство.
В целом, с помощью Excel все, или почти все, статистические показатели рассчитываются очень просто. Если что-то непонятно, всегда можно воспользоваться окошком для поиска в Мастере функций. Ну, и Гугл в помощь.
Расчет показателей вариации в Excel
Добрый день, уважаемые любители статистического анализа данных, а сегодня еще и программы Excel.
Проведение любого статанализа немыслимо без расчетов. И сегодня в рамках рубрики «Работаем в Excel» мы научимся рассчитывать показатели вариации. Теоретическая основа была рассмотрена ранее в ряде статей о вариации данных. Кстати, на этом указанная тема не закончилась, к выпуску планируются новые статьи – следите за рекламой! Однако сухая теория без инструментов реализации – вещь не сильно полезная. Поэтому по мере появления теоретических выкладок, я стараюсь не отставать с заметками о соответствующих расчетах в программе Excel.
Сегодняшняя публикация будет посвящена расчету в Excel следующих показателей вариации:
— максимальное и минимальное значение
— среднее линейное отклонение
— дисперсия (по генеральной совокупности и по выборке)
— среднее квадратическое отклонение (по генеральной совокупности и по выборке)
Факт возможности расчета упомянутых показателей в Excel свидетельствует о практическом их использовании. И, несмотря на очевидность некоторых моментов, я постараюсь расписать все подробно.
Понятие коэффициента вариации
В статистике под вариацией величин того или иного показателя в совокупности понимается различие его уровней у тех или иных единиц анализируемого состава в один и тот же период либо момент исследования. В том случае, когда выполняется анализ отличий величин показателя у одного и того же предмета, у одной и той же единицы совокупности в различные периоды или моменты времени, то это будет уже именоваться не вариацией, а колебаниями или изменениями в течении определенного периода.
Размещено на www.rnz.ru
Для изучения таких колебаний применяются свои методы анализа, имеющие отличия от методов анализа вариации. Объективным фактором возникновения явления вариации выступает различие в условиях деятельности тех или иных исследуемых объектов совокупности. Например, на работу торгового предприятия оказывает влияние уровень конкуренции, налогов, применение передовых технологий в своей деятельности, состояние оборудования и т.п. Колеблемость характерна практически для всех природный явлений и граней общественной жизни. Однако имеются и неварьируемые показатели, которые образуются в случае фиксации тех или иных явлений в правовых актах. Например, не может варьировать количество генеральных директоров у предприятия, согласно законодательству он должен быть один. Такие неварьирующие объекты, как правило, не являются предметом или объектом статистического исследования. В нашей жизни колеблемость признаков выступает важным фактором, оказывающим на нее влияние. Например, изменение гаммы типоразмеров деталей позволяет сформировать оптимальный ассортимент, но при этом высокий уровень вариации в рамках одного типоразмера говорит о высоком уровне брака и необходимости внедрения соответствующих мероприятий. Значительный уровень вариации товарооборота или цен может свидетельствовать о монополизации рынка или о плохом управлении запасами и требовать соответствующих мер и т.п. Сказанное позволяет утверждать, что в общественной жизни, которая с точки зрения статистики выступает массовой совокупностью, объективно присутствует изменчивость различных признаков и элементов, что диктует актуальность исследования данного явления с применением специальных показателей для формирования оптимальных методов управления им. Коэффициент вариации является одним из таких показателей. При этом он относится к группе относительных показателей вариации. Рассматриваемый коэффициент — это относительный показатель, характеризующий отношение среднего квадратического отклонения к средней величине изучаемого признака, и выражается, как правило, в процентах. В указанном критерии отражается соотношение уровня влияния факторов, которые приводят к возникновению колеблемости, и общих условий всех элементов совокупности, которые порождают типическую величину признака — его среднее значение. Коэффициент вариации применяется для изучения степени изменчивости различных признаков одной и той же совокупности и изменчивости в различных совокупностях, которые обладают разными значениями средних величин.
Расчет в Excel
Рассчитать указанную величину в Экселе можно с помощью двух специальных функций СТАНДОТКЛОН.В (по выборочной совокупности) и СТАНДОТКЛОН.Г (по генеральной совокупности). Принцип их действия абсолютно одинаков, но вызвать их можно тремя способами, о которых мы поговорим ниже.
Способ 1: мастер функций
- Выделяем на листе ячейку, куда будет выводиться готовый результат. Кликаем на кнопку «Вставить функцию», расположенную слева от строки функций.
В открывшемся списке ищем запись СТАНДОТКЛОН.В или СТАНДОТКЛОН.Г. В списке имеется также функция СТАНДОТКЛОН, но она оставлена из предыдущих версий Excel в целях совместимости. После того, как запись выбрана, жмем на кнопку «OK».
Результат расчета будет выведен в ту ячейку, которая была выделена в самом начале процедуры поиска среднего квадратичного отклонения.
Способ 2: вкладка «Формулы»
Также рассчитать значение среднеквадратичного отклонения можно через вкладку «Формулы».
- Выделяем ячейку для вывода результата и переходим во вкладку «Формулы».
После этого запускается окно аргументов. Все дальнейшие действия нужно производить так же, как и в первом варианте.
Способ 3: ручной ввод формулы
Существует также способ, при котором вообще не нужно будет вызывать окно аргументов. Для этого следует ввести формулу вручную.
- Выделяем ячейку для вывода результата и прописываем в ней или в строке формул выражение по следующему шаблону:
=СТАНДОТКЛОН.Г(число1(адрес_ячейки1); число2(адрес_ячейки2);…) или =СТАНДОТКЛОН.В(число1(адрес_ячейки1); число2(адрес_ячейки2);…).
Всего можно записать при необходимости до 255 аргументов.
После того, как запись сделана, нажмите на кнопку Enter на клавиатуре.
Как видим, механизм расчета среднеквадратичного отклонения в Excel очень простой. Пользователю нужно только ввести числа из совокупности или ссылки на ячейки, которые их содержат. Все расчеты выполняет сама программа. Намного сложнее осознать, что же собой представляет рассчитываемый показатель и как результаты расчета можно применить на практике. Но постижение этого уже относится больше к сфере статистики, чем к обучению работе с программным обеспечением.
В программе эксель можно посчитать среднеквадратичное отклонение двумя способами: использовать стандартные формулы или воспользоваться специальной функцией. Рассмотрим оба метода расчета и сравним их результаты.
Перед нами таблица, состоящая из двух строк и шести столбцов, на основании этих данных и будем делать расчет.
Первый способ.
Первый шаг. Рассчитаем среднее значение пяти данных показателей, для этого воспользуемся функцией СРЗНАЧ, в ячейке «В3» напишем формулу: =СРЗНАЧ(B2:F2).
Второй шаг. Рассчитаем отклонения каждого показателя от среднего, для этого в ячейке «В4» пишем формулу: =B2-$B$3, знаки доллара ставим, чтобы при копировании данной формулы на другие ячейки, параметр среднего значения всегда вычитался. Копируем соответственно данную формулу на другие ячейки.
Третий шаг. Возведем каждое отклонения от среднего в квадратный корень, для этого в ячейке «В5» пишем формулу: =B4^2, которую копируем на оставшийся диапазон ячеек (с «С5» по «F5»).
Четвертый шаг. Посчитаем сумму квадратных отклонений, для этого в ячейке «В6» напишем формулу =СУММ(B5:F5).
Пятый шаг. У нас все готово, чтобы рассчитать среднеквадратичное отклонения. Для этого нужно сумму отклонений от среднего значения в квадрате (8,8) разделить на количество опытов минус один (5-1) и от получившегося значения изъять квадратный корень. Пишем в ячейке «В8» формулу: =КОРЕНЬ((B6/(5-1))).
В итоге получили цифру равную 1,483
Второй способ.
Программа эксель позволяет избегать такого количества расчетов, а, следовательно, сэкономить время, вам просто нужно воспользоваться для расчета среднеквадратичное отклонения функцией СТАНДОТКЛОН, вы внутри неё указываете диапазон, для которого нужно сделать расчет. В ячейке «В8» пишем формулу =СТАНДОТКЛОН(B2:F2).
В итоге результаты обоих вариантов расчета среднеквадратичного отклонения совпали, а вы выбирайте метод, который наиболее подходит к вам.
Размах вариации (r)
Размах
вариации
—
это разность между максимальным и
минимальным значениями признака
Он
показывает пределы, в которых изменяется
величина признака в изучаемой совокупности.
Опыт
работы у пяти претендентов на предшествующей
работе составляет: 2,3,4,7 и 9 лет.
Решение:
размах вариации = 9 — 2 = 7 лет.
Для
обобщенной характеристики различий в
значениях признака вычисляют средние
показатели вариации, основанные на
учете отклонений от средней арифметической.
За отклонение от средней принимается
разность
.
При
этом во избежании превращения в нуль
суммы отклонений вариантов признака
от средней (нулевое свойство средней)
приходится либо не учитывать знаки
отклонения, то есть брать эту сумму по
модулю
,
либо возводить значения отклонений в
квадрат
Вычисление дисперсии
Дисперсия – это показатель вариации, который представляет собой средний квадрат отклонений от математического ожидания. Таким образом, он выражает разброс чисел относительно среднего значения. Вычисление дисперсии может проводиться как по генеральной совокупности, так и по выборочной.
Способ 1: расчет по генеральной совокупности
Для расчета данного показателя в Excel по генеральной совокупности применяется функция ДИСП.Г. Синтаксис этого выражения имеет следующий вид:
Всего может быть применено от 1 до 255 аргументов. В качестве аргументов могут выступать, как числовые значения, так и ссылки на ячейки, в которых они содержатся.
Посмотрим, как вычислить это значение для диапазона с числовыми данными.
- Производим выделение ячейки на листе, в которую будут выводиться итоги вычисления дисперсии. Щелкаем по кнопке «Вставить функцию», размещенную слева от строки формул.
Запускается Мастер функций. В категории «Статистические» или «Полный алфавитный перечень» выполняем поиск аргумента с наименованием «ДИСП.Г». После того, как нашли, выделяем его и щелкаем по кнопке «OK».
Выполняется запуск окна аргументов функции ДИСП.Г. Устанавливаем курсор в поле «Число1». Выделяем на листе диапазон ячеек, в котором содержится числовой ряд. Если таких диапазонов несколько, то можно также использовать для занесения их координат в окно аргументов поля «Число2», «Число3» и т.д. После того, как все данные внесены, жмем на кнопку «OK».
Как видим, после этих действий производится расчет. Итог вычисления величины дисперсии по генеральной совокупности выводится в предварительно указанную ячейку. Это именно та ячейка, в которой непосредственно находится формула ДИСП.Г.
Урок: Мастер функций в Эксель
Способ 2: расчет по выборке
В отличие от вычисления значения по генеральной совокупности, в расчете по выборке в знаменателе указывается не общее количество чисел, а на одно меньше. Это делается в целях коррекции погрешности. Эксель учитывает данный нюанс в специальной функции, которая предназначена для данного вида вычисления – ДИСП.В. Её синтаксис представлен следующей формулой:
Количество аргументов, как и в предыдущей функции, тоже может колебаться от 1 до 255.
- Выделяем ячейку и таким же способом, как и в предыдущий раз, запускаем Мастер функций.
В категории «Полный алфавитный перечень» или «Статистические» ищем наименование «ДИСП.В». После того, как формула найдена, выделяем её и делаем клик по кнопке «OK».
Производится запуск окна аргументов функции. Далее поступаем полностью аналогичным образом, как и при использовании предыдущего оператора: устанавливаем курсор в поле аргумента «Число1» и выделяем область, содержащую числовой ряд, на листе. Затем щелкаем по кнопке «OK».
Результат вычисления будет выведен в отдельную ячейку.
Урок: Другие статистические функции в Эксель
Как видим, программа Эксель способна в значительной мере облегчить расчет дисперсии. Эта статистическая величина может быть рассчитана приложением, как по генеральной совокупности, так и по выборке. При этом все действия пользователя фактически сводятся только к указанию диапазона обрабатываемых чисел, а основную работу Excel делает сам. Безусловно, это сэкономит значительное количество времени пользователей.
Опишите, что у вас не получилось.
Наши специалисты постараются ответить максимально быстро.
Как написать коэффициент в экселе
Одним из основных статистических показателей последовательности чисел является коэффициент вариации. Для его нахождения производятся довольно сложные расчеты. Инструменты Microsoft Excel позволяют значительно облегчить их для пользователя.
Вычисление коэффициента вариации
Этот показатель представляет собой отношение стандартного отклонения к среднему арифметическому. Полученный результат выражается в процентах.
В Экселе не существует отдельно функции для вычисления этого показателя, но имеются формулы для расчета стандартного отклонения и среднего арифметического ряда чисел, а именно они используются для нахождения коэффициента вариации.
Шаг 1: расчет стандартного отклонения
Стандартное отклонение, или, как его называют по-другому, среднеквадратичное отклонение, представляет собой квадратный корень из дисперсии.
Для расчета стандартного отклонения используется функция СТАНДОТКЛОН.
Начиная с версии Excel 2010 она разделена, в зависимости от того, по генеральной совокупности происходит вычисление или по выборке, на два отдельных варианта: СТАНДОТКЛОН.Г и СТАНДОТКЛОН.В.
Синтаксис данных функций выглядит соответствующим образом:
= СТАНДОТКЛОН(Число1;Число2;…) = СТАНДОТКЛОН.Г(Число1;Число2;…)
= СТАНДОТКЛОН.В(Число1;Число2;…)
- Для того, чтобы рассчитать стандартное отклонение, выделяем любую свободную ячейку на листе, которая удобна вам для того, чтобы выводить в неё результаты расчетов. Щелкаем по кнопке «Вставить функцию». Она имеет внешний вид пиктограммы и расположена слева от строки формул.
Выполняется активация Мастера функций, который запускается в виде отдельного окна с перечнем аргументов. Переходим в категорию «Статистические» или «Полный алфавитный перечень». Выбираем наименование «СТАНДОТКЛОН.Г» или «СТАНДОТКЛОН.В», в зависимости от того, по генеральной совокупности или по выборке следует произвести расчет. Жмем на кнопку «OK».
Открывается окно аргументов данной функции. Оно может иметь от 1 до 255 полей, в которых могут содержаться, как конкретные числа, так и ссылки на ячейки или диапазоны. Ставим курсор в поле «Число1».
Мышью выделяем на листе тот диапазон значений, который нужно обработать. Если таких областей несколько и они не смежные между собой, то координаты следующей указываем в поле «Число2» и т.д.
Когда все нужные данные введены, жмем на кнопку «OK»
В предварительно выделенной ячейке отображается итог расчета выбранного вида стандартного отклонения.
Шаг 2: расчет среднего арифметического
Среднее арифметическое является отношением общей суммы всех значений числового ряда к их количеству. Для расчета этого показателя тоже существует отдельная функция – СРЗНАЧ. Вычислим её значение на конкретном примере.
- Выделяем на листе ячейку для вывода результата. Жмем на уже знакомую нам кнопку «Вставить функцию».
В статистической категории Мастера функций ищем наименование «СРЗНАЧ». После его выделения жмем на кнопку «OK».
Запускается окно аргументов СРЗНАЧ. Аргументы полностью идентичны тем, что и у операторов группы СТАНДОТКЛОН. То есть, в их качестве могут выступать как отдельные числовые величины, так и ссылки.
После того, как их координаты были занесены в поле окна аргументов, жмем на кнопку «OK».
Результат вычисления среднего арифметического выводится в ту ячейку, которая была выделена перед открытием Мастера функций.
Шаг 3: нахождение коэффициента вариации
Теперь у нас имеются все необходимые данные для того, чтобы непосредственно рассчитать сам коэффициент вариации.
- Выделяем ячейку, в которую будет выводиться результат. Прежде всего, нужно учесть, что коэффициент вариации является процентным значением. В связи с этим следует поменять формат ячейки на соответствующий.
Это можно сделать после её выделения, находясь во вкладке «». Кликаем по полю формата на ленте в блоке инструментов «Число». Из раскрывшегося списка вариантов выбираем «Процентный».
После этих действий формат у элемента будет соответствующий.
Снова возвращаемся к ячейке для вывода результата. Активируем её двойным щелчком левой кнопки мыши. Ставим в ней знак «=». Выделяем элемент, в котором расположен итог вычисления стандартного отклонения.
Кликаем по кнопке «разделить» (/) на клавиатуре. Далее выделяем ячейку, в которой располагается среднее арифметическое заданного числового ряда.