Блог о программе Microsoft Excel: приемы, хитрости, секреты, трюки
Как расчитать дисперсию в Excel с помощью функции ДИСП.В
Опубликовано 11.04.2014 Автор Ренат Лотфуллин
Дисперсия — это мера рассеяния, описывающая сравнительное отклонение между значениями данных и средней величиной. Является наиболее используемой мерой рассеяния в статистике, вычисляемая путем суммирования, возведенного в квадрат, отклонения каждого значения данных от средней величины. Формула для вычисления дисперсии представлена ниже:
s 2 – дисперсия выборки;
xср — среднее значение выборки;
n — размер выборки (количество значений данных),
(xi – xср) — отклонение от средней величины для каждого значения набора данных.
Для лучшего понимания формулы, разберем пример. Я не очень люблю готовку, поэтому занятием этим занимаюсь крайне редко. Тем не менее, чтобы не умереть с голоду, время от времени мне приходится подходить к плите для реализации замысла по насыщению моего организма белками, жирами и углеводами. Набор данных, редставленный ниже, показывает, сколько раз Ренат готовит пищу каждый месяц:
Элементы статистики. Дисперсия. Стандартное отклонение
Первым шагом при вычислении дисперсии является определение среднего значения выборки, которое в нашем примере равняется 7,8 раза в месяц. Остальные вычисления можно облегчить с помощью следующей таблицы.
Финальная фаза вычисления дисперсии выглядит так:
Для тех, кто любит производить все вычисления за один раз, уравнение будет выглядеть следующим образом:
Использование метода «сырого счета» (пример с готовкой)
Существует более эффективный способ вычисления дисперсии, известный как метод «сырого счета». Хотя с первого взгляда уравнение может показаться весьма громоздким, на самом деле оно не такое уж страшное. Можете в этом удостовериться, а потом и решите, какой метод вам больше нравится.
Математическое Ожидание, Дисперсия, Стандартное Отклонение за 5 минут
— сумма каждого значения данных после возведения в квадрат,
— квадрат суммы всех значений данных.
Не теряйте рассудок прямо сейчас. Позвольте представить все это в виде таблицы, и тогда вы увидите, что вычислений здесь меньше, чем в предыдущем примере.
Как видите, результат получился тот же, что и при использовании предыдущего метода. Достоинства данного метода становятся очевидными по мере роста размера выборки (n).
Расчет дисперсии в Excel
Как вы уже, наверное, догадались, в Excel присутствует формула, позволяющая рассчитать дисперсию. Причем, начиная с Excel 2010 можно найти 4 разновидности формулы дисперсии:
1) ДИСП.В – Возвращает дисперсию по выборке. Логические значения и текст игнорируются.
2) ДИСП.Г — Возвращает дисперсию по генеральной совокупности. Логические значения и текст игнорируются.
3) ДИСПА — Возвращает дисперсию по выборке с учетом логических и текстовых значений.
4) ДИСПРА — Возвращает дисперсию по генеральной совокупности с учетом логических и текстовых значений.
Для начала разберемся в разнице между выборкой и генеральной совокупностью. Назначение описательной статистики состоит в том, чтобы суммировать или отображать данные так, чтобы оперативно получать общую картину, так сказать, обзор. Статистический вывод позволяет делать умозаключения о какой-либо совокупности на основе выборки данных из этой совокупности. Совокупность представляет собой все возможные исходы или измерения, представляющие для нас интерес. Выборка — это подмножество совокупности.
Например, нас интересует совокупность группы студентов одного из Российских ВУЗов и нам необходимо определить средний бал группы. Мы можем посчитать среднюю успеваемость студентов, и тогда полученная цифра будет параметром, поскольку в наших расчетах будет задействована целая совокупность. Однако, если мы хотим рассчитать средний бал всех студентов нашей страны, тогда эта группа будет нашей выборкой.
Разница в формуле расчета дисперсии между выборкой и совокупностью заключается в знаменателе. Где для выборки он будет равняться (n-1), а для генеральной совокупности только n.
Теперь разберемся с функциями расчета дисперсии с окончаниями А, в описании которых сказано, что при расчете учитываются текстовые и логические значения. В данном случае при расчете дисперсии определенного массива данных, где встречаются не числовые значения, Excel будет интерпретировать текстовые и ложные логические значения как равными 0, а истинные логические значения как равными 1.
Итак, если у вас есть массив данных, рассчитать его дисперсию ни составит никакого труда, воспользовавшись одной из перечисленных выше функций Excel.
Вам также могут быть интересны следующие статьи
- Что такое стандартное отклонение — использование функции СТАНДОТКЛОН для расчета стандартного отклонения в Excel
- Как построить график с нормальным распределением в Excel
- Как рассчитать коэффициент корреляции в Excel
- Функция СЖПРОБЕЛЫ в Excel с примерами использования
- Четыре способа использования ВПР с несколькими условиями
- Несколько условий ЕСЛИ в Excel
- Как рассчитать регрессию в Excel
- Метод наименьших квадратов в Excel — использование функции ТЕНДЕНЦИЯ
- Переводчик в Excel — Microsoft Translator и Яндекс Переводчик
- Функция СУММПРОИЗВ — как использовать формулу СУММПРОИЗВ в Excel
Источник: exceltip.ru
Как вычислять дисперсию: разъяснение с примерами
Теория вероятностей работает со случайными величинами. Для случайных величин существуют так называемые законы распределения. Такой закон свою случайную величину описывает с абсолютной полнотой. Однако при работе с реальными наборами случайных величин сразу установить закон их распределения часто очень трудно и ограничиваются некоторым набором численных характеристик. Например, вычислить математическое ожидание и дисперсию случайной величины часто бывает очень полезно.
Зачем это нужно
Если суть математического ожидания по смыслу близка к среднему значению величины, то в таком случае дисперсия говорит, как разбросаны значения нашей величины вокруг этого математического ожидания. Например, если мы измеряли IQ у группы людей и хотим исследовать результаты измерений (выборку), математическое ожидание покажет примерное среднее значение коэффициента интеллекта у данной группы людей, а если вычислить дисперсию выборки, мы узнаем, как результаты группируются около математического ожидания: кучкой вблизи него (маленький разброс IQ) или более равномерно на всем участке от минимального до максимального результата (большой разброс, и где-то в середине — мат. ожидание).
Чтобы вычислить дисперсию, необходима новая характеристика случайной величины — отклонение значения от математического ожидания.
Отклонение
Чтобы понять, как вычислять дисперсию, надо сначала разобраться с отклонением. Его определение — разность между значением, которое принимает случайная величина и ее математическим ожиданием. Грубо говоря, для того чтобы понять, как величина «разбрасывается», нужно посмотреть, каким образом распределяется ее отклонение. То есть, мы заменяем значение величины значением ее отклонения от мат. ожидания и исследуем уже его закон распределения.
Закон распределения дискретной, то есть принимающей отдельные значения случайной величины записывается в виде таблицы, где значение величины соотнесено с вероятностью ее появления. Тогда в законе распределения отклонения случайная величина заменится на его формулу, в которой присутствует величина (сохранившая свою вероятность) и ее же мат. ожидание.
Свойства закона распределения отклонения случайной величины
У нас записан закон распределения отклонения случайной величины. Из него мы можем извлечь пока только такую характеристику, как математическое ожидание. Для удобства лучше взять численный пример.
Пусть имеется закон распределения какой-нибудь случайной величины: X — значение, p — вероятность.
Рассчитываем математическое ожидание по формуле и сразу же отклонения.
Рисуем новую таблицу распределения отклонения.
Рассчитываем математическое ожидание и здесь.
Получается ноль. Пример лишь один, но так будет всегда: это нетрудно доказать в общем случае. Формулу математического ожидания отклонения можно разложить на разность математических ожиданий случайной величины и, как бы ни криво это звучало, математического ожидания мат. ожидания (рекурсия, однако), что есть одно и то же, следовательно, их разность будет равна нулю.
Это ожидаемо: ведь отклонения по знаку бывают как положительными, так и отрицательными, следовательно, в среднем должны давать ноль.
Как вычислять дисперсию дискретной случ. величины
Если мат. ожидание отклонения высчитывать бессмысленно, надо искать что-то другое. Можно просто взять абсолютные значения отклонений (по модулю); но с модулями все не так просто, поэтому отклонения возводят в квадрат, а потом считают их математическое ожидание. Собственно, это и имеется в виду, когда говорят о том, как вычислять дисперсию.
То есть, мы берем отклонения, возводим их в квадрат и составляем таблицу из квадратов отклонений и вероятностей, которые соответствуют случайным величинам. Это новый закон распределения. Чтобы посчитать математическое ожидание, необходимо сложить произведения квадрата отклонения и вероятности.
Более простая формула
Однако началась статья с того, что закон распределения изначальной случайной величины зачастую бывает неизвестен. Поэтому нужно что-то полегче. Действительно, существует другая формула, позволяющая вычислить дисперсию выборки с помощью только мат. ожидания:
Дисперсия — разность между мат. ожиданием квадрата случайной величины и, наоборот, квадратом ее мат. ожидания.
Доказательство этому существует, однако приводить его здесь не имеет смысла, так как оно не имеет практической ценности (а нам-то нужно только посчитать дисперсию).
Как вычислять дисперсию случайной величины в вариационных рядах
В реальной статистике невозможно отразить все случайные величины (потому что их, грубо говоря, как правило, бесконечно много). Поэтому то, что попадает в исследование — так называемая репрезентативная выборка из какой-то общей генеральной совокупности. И, поскольку численные характеристики любой случайной величины из такой генеральной совокупности рассчитываются по выборке, они называются выборочными: выборочное среднее, соответственно, выборочная дисперсия. Вычислить ее можно точно так же, как и обычную (через квадраты отклонений).
Однако такую дисперсию называют смещенной. Формула несмещенной дисперсии выглядит немного по-другому. Посчитать требуется обычно именно ее.
Небольшое дополнение
С дисперсией связана еще одна численная характеристика. Она также служит для оценки того, как рассеивается случайная величина вокруг своего мат. ожидания. В способах, как вычислить дисперсию и среднее квадратическое отклонение нет большой разницы: последнее — это квадратный корень из первого.
Источник: fb.ru