Exceltip

Блог о программе Microsoft Excel: приемы, хитрости, секреты, трюки

Как расчитать дисперсию в Excel с помощью функции ДИСП.В

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

n — размер выборки (количество значений данных),

(x_i – x_ср) — отклонение от средней величины для каждого значения набора данных.

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

Первым шагом при вычислении дисперсии является определение среднего значения выборки, которое в нашем примере равняется 7,8 раза в месяц. Остальные вычисления можно облегчить с помощью следующей таблицы.

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

Использование метода «сырого счета» (пример с готовкой)

Существует более эффективный способ вычисления дисперсии, известный как метод «сырого счета». Хотя с первого взгляда уравнение может показаться весьма громоздким, на самом деле оно не такое уж страшное. Можете в этом удостовериться, а потом и решите, какой метод вам больше нравится.

— сумма каждого значения данных после возведения в квадрат,

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

Как видите, результат получился тот же, что и при использовании предыдущего метода. Достоинства данного метода становятся очевидными по мере роста размера выборки (n).

Расчет дисперсии в Excel

Как вы уже, наверное, догадались, в Excel присутствует формула, позволяющая рассчитать дисперсию. Причем, начиная с Excel 2010 можно найти 4 разновидности формулы дисперсии:

1) ДИСП.В – Возвращает дисперсию по выборке. Логические значения и текст игнорируются.

2) ДИСП.Г — Возвращает дисперсию по генеральной совокупности. Логические значения и текст игнорируются.

3) ДИСПА — Возвращает дисперсию по выборке с учетом логических и текстовых значений.

4) ДИСПРА — Возвращает дисперсию по генеральной совокупности с учетом логических и текстовых значений.

Разница в формуле расчета дисперсии между выборкой и совокупностью заключается в знаменателе. Где для выборки он будет равняться (n-1), а для генеральной совокупности только n.

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

[expert_bq id=»1570″]Тем не менее, чтобы не умереть с голоду, время от времени мне приходится подходить к плите для реализации замысла по насыщению моего организма белками, жирами и углеводами. Если же вы хотите что-то уточнить, обращайтесь ко мне![/expert_bq] Коэффициент вариации – это сравнение рассеивания двух случайно взятых величин. Величины имеют единицы измерения, что приводит к получению сопоставимого результата. Этот коэффициент нужен для подготовки статистического анализа.

Расчет средневзвешенного значения в Excel

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

Как найти дисперсию в excel

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

Вычисление дисперсии

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

Способ 1: расчет по генеральной совокупности

Для расчета данного показателя в Excel по генеральной совокупности применяется функция ДИСП.Г. Синтаксис этого выражения имеет следующий вид:

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

Посмотрим, как вычислить это значение для диапазона с числовыми данными.

Запускается Мастер функций. В категории «Статистические» или «Полный алфавитный перечень» выполняем поиск аргумента с наименованием «ДИСП.Г». После того, как нашли, выделяем его и щелкаем по кнопке «OK».

Выполняется запуск окна аргументов функции ДИСП.Г. Устанавливаем курсор в поле «Число1». Выделяем на листе диапазон ячеек, в котором содержится числовой ряд. Если таких диапазонов несколько, то можно также использовать для занесения их координат в окно аргументов поля «Число2», «Число3» и т.д. После того, как все данные внесены, жмем на кнопку «OK».

Способ 2: расчет по выборке

В отличие от вычисления значения по генеральной совокупности, в расчете по выборке в знаменателе указывается не общее количество чисел, а на одно меньше. Это делается в целях коррекции погрешности. Эксель учитывает данный нюанс в специальной функции, которая предназначена для данного вида вычисления – ДИСП.В. Её синтаксис представлен следующей формулой:

Количество аргументов, как и в предыдущей функции, тоже может колебаться от 1 до 255.

В категории «Полный алфавитный перечень» или «Статистические» ищем наименование «ДИСП.В». После того, как формула найдена, выделяем её и делаем клик по кнопке «OK».

Производится запуск окна аргументов функции. Далее поступаем полностью аналогичным образом, как и при использовании предыдущего оператора: устанавливаем курсор в поле аргумента «Число1» и выделяем область, содержащую числовой ряд, на листе. Затем щелкаем по кнопке «OK».

Отблагодарите автора, поделитесь статьей в социальных сетях.

Блог о программе Microsoft Excel: приемы, хитрости, секреты, трюки

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

n — размер выборки (количество значений данных),

(x_i – x_ср) — отклонение от средней величины для каждого значения набора данных.

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

Первым шагом при вычислении дисперсии является определение среднего значения выборки, которое в нашем примере равняется 7,8 раза в месяц. Остальные вычисления можно облегчить с помощью следующей таблицы.

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

Использование метода «сырого счета» (пример с готовкой)

Существует более эффективный способ вычисления дисперсии, известный как метод «сырого счета». Хотя с первого взгляда уравнение может показаться весьма громоздким, на самом деле оно не такое уж страшное. Можете в этом удостовериться, а потом и решите, какой метод вам больше нравится.

— сумма каждого значения данных после возведения в квадрат,

— квадрат суммы всех значений данных.

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

Как видите, результат получился тот же, что и при использовании предыдущего метода. Достоинства данного метода становятся очевидными по мере роста размера выборки (n).

Расчет дисперсии в Excel

Как вы уже, наверное, догадались, в Excel присутствует формула, позволяющая рассчитать дисперсию. Причем, начиная с Excel 2010 можно найти 4 разновидности формулы дисперсии:

1) ДИСП.В – Возвращает дисперсию по выборке. Логические значения и текст игнорируются.

2) ДИСП.Г — Возвращает дисперсию по генеральной совокупности. Логические значения и текст игнорируются.

3) ДИСПА — Возвращает дисперсию по выборке с учетом логических и текстовых значений.

4) ДИСПРА — Возвращает дисперсию по генеральной совокупности с учетом логических и текстовых значений.

Разница в формуле расчета дисперсии между выборкой и совокупностью заключается в знаменателе. Где для выборки он будет равняться (n-1), а для генеральной совокупности только n.

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

Вычислим в MS EXCEL дисперсию и стандартное отклонение выборки. Также вычислим дисперсию случайной величины, если известно ее распределение.

Сначала рассмотрим дисперсию, затем стандартное отклонение.

Дисперсия выборки

Дисперсия выборки (выборочная дисперсия, sample variance) характеризует разброс значений в массиве относительно среднего.

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

Дисперсию выборки можно также вычислить непосредственно по нижеуказанным формулам (см. файл примера )
=КВАДРОТКЛ(Выборка)/(СЧЁТ(Выборка)-1)
=(СУММКВ(Выборка)-СЧЁТ(Выборка)*СРЗНАЧ(Выборка)^2)/ (СЧЁТ(Выборка)-1) – обычная формула
=СУММ((Выборка -СРЗНАЧ(Выборка))^2)/ (СЧЁТ(Выборка)-1 ) – формула массива

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

Дисперсия выборки является точечной оценкой дисперсии распределения случайной величины, из которой была сделана выборка. О построении доверительных интервалов при оценке дисперсии можно прочитать в статье Доверительный интервал для оценки дисперсии в MS EXCEL.

Дисперсия случайной величины

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

Для дисперсии случайной величины Х часто используют обозначение Var(Х). Дисперсия равна математическому ожиданию квадрата отклонения от среднего E(X): Var(Х)=E[(X-E(X)) 2 ]

Если случайная величина имеет дискретное распределение, то дисперсия вычисляется по формуле:

где x_i – значение, которое может принимать случайная величина, а μ – среднее значение (математическое ожидание случайной величины), р(x) – вероятность, что случайная величина примет значение х.

Если случайная величина имеет непрерывное распределение, то дисперсия вычисляется по формуле:

Для распределений, представленных в MS EXCEL, дисперсию можно вычислить аналитически, как функцию от параметров распределения. Например, для Биномиального распределения дисперсия равна произведению его параметров: n*p*q.

Примечание: Дисперсия, является вторым центральным моментом, обозначается D[X], VAR(х), V(x). Второй центральный момент — числовая характеристика распределения случайной величины, которая является мерой разброса случайной величины относительно математического ожидания.

Примечание: О распределениях в MS EXCEL можно прочитать в статье Распределения случайной величины в MS EXCEL.

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

Var(Х)=E[(X-E(X)) 2 ]=E[X 2 -2*X*E(X)+(E(X)) 2 ]=E(X 2 )-E(2*X*E(X))+(E(X)) 2 =E(X 2 )-2*E(X)*E(X)+(E(X)) 2 =E(X 2 )-(E(X)) 2

Это свойство дисперсии используется в статье про линейную регрессию .

Var(Х+Y)=Var(Х) + Var(Y) + 2*Cov(Х;Y), где Х и Y — случайные величины, Cov(Х;Y) — ковариация этих случайных величин.

Если случайные величины независимы (independent), то их ковариация равна 0, и, следовательно, Var(Х+Y)=Var(Х)+Var(Y). Это свойство дисперсии используется при выводе стандартной ошибки среднего.

Покажем, что для независимых величин Var(Х-Y)=Var(Х+Y). Действительно, Var(Х-Y)= Var(Х-Y)= Var(Х+(-Y))= Var(Х)+Var(-Y)= Var(Х)+Var(-Y)= Var(Х)+(-1) 2 Var(Y)= Var(Х)+Var(Y)= Var(Х+Y). Это свойство дисперсии используется для построения доверительного интервала для разницы 2х средних.

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

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

По определению, стандартное отклонение равно квадратному корню из дисперсии:

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

В MS EXCEL 2007 и более ранних версиях для вычисления Стандартного отклонения выборки используется функция =СТАНДОТКЛОН() , англ. название STDEV, т.е. STandard DEViation. С версии MS EXCEL 2010 рекомендуется использовать ее аналог =СТАНДОТКЛОН.В() , англ. название STDEV.S, т.е. Sample STandard DEViation.

Стандартное отклонение можно также вычислить непосредственно по нижеуказанным формулам (см. файл примера )
=КОРЕНЬ(КВАДРОТКЛ(Выборка)/(СЧЁТ(Выборка)-1))
=КОРЕНЬ((СУММКВ(Выборка)-СЧЁТ(Выборка)*СРЗНАЧ(Выборка)^2)/(СЧЁТ(Выборка)-1))

Другие меры разброса

Функция КВАДРОТКЛ() вычисляет с умму квадратов отклонений значений от их среднего. Эта функция вернет тот же результат, что и формула =ДИСП.Г( Выборка )*СЧЁТ( Выборка ) , где Выборка — ссылка на диапазон, содержащий массив значений выборки (именованный диапазон). Вычисления в функции КВАДРОТКЛ() производятся по формуле:

Функция СРОТКЛ() является также мерой разброса множества данных. Функция СРОТКЛ() вычисляет среднее абсолютных значений отклонений значений от среднего. Эта функция вернет тот же результат, что и формула =СУММПРОИЗВ(ABS(Выборка-СРЗНАЧ(Выборка)))/СЧЁТ(Выборка) , где Выборка — ссылка на диапазон, содержащий массив значений выборки.

[expert_bq id=»1570″]Далее поступаем полностью аналогичным образом, как и при использовании предыдущего оператора устанавливаем курсор в поле аргумента Число1 и выделяем область, содержащую числовой ряд, на листе. Если же вы хотите что-то уточнить, обращайтесь ко мне![/expert_bq] 2. Откроется оболочка управления функциями. Там нужно искать функцию «ДИСП.Г», которая может быть в категории «Статистические» или «Полный алфавитный перечень». Когда она будет найдена, следует выделить ее и кликнуть «ОК».

Как посчитать ячейки выше определенного значения или среднего значения в Excel?

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

Как Рассчитать Выборочное Среднее в Excel

Как посчитать ячейки выше определенного значения или среднего значения в Excel?

Если у вас есть диапазон значений на листе, и вы хотите подсчитать ячейки выше / ниже определенного значения или выше / ниже среднего значения диапазона, как вы можете быстро их подсчитать? В Excel для этого можно использовать формулу СЧЁТЕСЛИ.

Вкладка Office позволяет редактировать и просматривать в Office с вкладками и значительно упрощает работу .

• Повторное использование чего угодно: Добавляйте наиболее часто используемые или сложные формулы, диаграммы и все остальное в избранное и быстро используйте их в будущем.
• Более 20 текстовых функций: Извлечь число из текстовой строки; Извлечь или удалить часть текстов; Преобразование чисел и валют в английские слова.
• Инструменты слияния : Несколько книг и листов в одну; Объединить несколько ячеек / строк / столбцов без потери данных; Объедините повторяющиеся строки и сумму.
• Разделить инструменты : Разделение данных на несколько листов в зависимости от ценности; Из одной книги в несколько файлов Excel, PDF или CSV; От одного столбца к нескольким столбцам.
• Вставить пропуск Скрытые / отфильтрованные строки; Подсчет и сумма по цвету фона ; Отправляйте персонализированные электронные письма нескольким получателям массово.
• Суперфильтр: Создавайте расширенные схемы фильтров и применяйте их к любым листам; Сортировать по неделям, дням, периодичности и др .; Фильтр жирным шрифтом, формулы, комментарий .
• Более 300 мощных функций; Работает с Office 2007-2019 и 365; Поддерживает все языки; Простое развертывание на вашем предприятии или в организации.

Подсчитайте ячейки выше / ниже среднего

Удивительный! Использование эффективных вкладок в Excel, таких как Chrome, Firefox и Safari!

Экономьте 50% своего времени и сокращайте тысячи щелчков мышью каждый день!

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

Выберите пустую ячейку, например Ячейку C6, введите эту формулу = СЧЁТЕСЛИ (A1: C5; «>» & СРЕДНЕЕ (A1: C5)) (диапазон A1: C5 указывает диапазон, в котором вы хотите подсчитать количество ячеек выше среднего, вы можете изменить его по своему усмотрению) и нажмите Enter кнопку на клавиатуре. Смотрите скриншот:

Чаевые: Если вы хотите подсчитать количество ячеек ниже среднего, введите эту формулу = СЧЁТЕСЛИ (A1: C5; » (диапазон A1: C5 указывает диапазон, в котором вы хотите подсчитывать ячейки ниже среднего, вы можете изменить его по своему усмотрению).

Подсчитайте ячейки выше / ниже определенного значения

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

Выберите пустую ячейку, например Ячейку C6, введите эту формулу = СЧЁТЕСЛИ (A1: C5; «> 50») (диапазон A1: C5 указывает диапазон, в котором вы хотите подсчитать ячейки выше указанного значения 50, число 50 соответствует критерию, вы можете изменить их по своему усмотрению) и нажмите Enter кнопку на клавиатуре. Смотрите скриншот:

Совет: если вы хотите подсчитать количество ячеек ниже определенного значения, введите эту формулу = СЧЁТЕСЛИ (A1: C5; « <50»)(диапазон A1: C5 указывает диапазон, в котором вы хотите посчитать ячейки ниже указанного значения, число 50 соответствует критерию, вы можете изменить их по своему усмотрению).

Как расчитать дисперсию в Excel с помощью функции ДИСП. В | Exceltip

где x_i – значение, которое может принимать случайная величина, а μ – среднее значение (математическое ожидание случайной величины), р(x) – вероятность, что случайная величина примет значение х.

[expert_bq id=»1570″]где x i значение, которое может принимать случайная величина, а μ среднее значение математическое ожидание случайной величины , р x вероятность, что случайная величина примет значение х. Если же вы хотите что-то уточнить, обращайтесь ко мне![/expert_bq] Если у вас есть диапазон значений на листе, и вы хотите подсчитать ячейки выше / ниже определенного значения или выше / ниже среднего значения диапазона, как вы можете быстро их подсчитать? В Excel для этого можно использовать формулу СЧЁТЕСЛИ.

Как найти дисперсию в excel

• Повторное использование чего угодно: Добавляйте наиболее часто используемые или сложные формулы, диаграммы и все остальное в избранное и быстро используйте их в будущем.
• Более 20 текстовых функций: Извлечь число из текстовой строки; Извлечь или удалить часть текстов; Преобразование чисел и валют в английские слова.
• Инструменты слияния : Несколько книг и листов в одну; Объединить несколько ячеек / строк / столбцов без потери данных; Объедините повторяющиеся строки и сумму.
• Разделить инструменты : Разделение данных на несколько листов в зависимости от ценности; Из одной книги в несколько файлов Excel, PDF или CSV; От одного столбца к нескольким столбцам.
• Вставить пропуск Скрытые / отфильтрованные строки; Подсчет и сумма по цвету фона ; Отправляйте персонализированные электронные письма нескольким получателям массово.
• Суперфильтр: Создавайте расширенные схемы фильтров и применяйте их к любым листам; Сортировать по неделям, дням, периодичности и др .; Фильтр жирным шрифтом, формулы, комментарий .
• Более 300 мощных функций; Работает с Office 2007-2019 и 365; Поддерживает все языки; Простое развертывание на вашем предприятии или в организации.

В категории «Полный алфавитный перечень» или «Статистические» ищем наименование «ДИСП.В». После того, как формула найдена, выделяем её и делаем клик по кнопке «OK».

Дополнительные данные → Похожие темы → Все про Exel → Как вставить значения → Как объединить ячейки → Как вставить форматы → Дополнительные данные → Вставить формулы→ Аргументы функции