Коэф вариации формула в excel: Коэффициент вариации в эксель

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

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

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

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

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

где р(x) – плотность вероятности .

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

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

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

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

Некоторые свойства дисперсии :

Var(Х+a)=Var(Х), где Х — случайная величина, а — константа.

Var(aХ)=a ² Var(X)

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

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

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) ² Var(Y)= Var(Х)+Var(Y)= Var(Х+Y). Это свойство дисперсии используется для построения доверительного интервала для разницы 2х средних .

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

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

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

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

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

Вычислим стандартное отклонение для 2-х выборок: (1; 5; 9) и (1001; 1005; 1009). В обоих случаях, s=4. Очевидно, что отношение величины стандартного отклонения к значениям массива у выборок существенно отличается. Для таких случаев используется Коэффициент вариации (Coefficient of Variation, CV) — отношение Стандартного отклонения к среднему арифметическому , выраженного в процентах.

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

Кроме того, начиная с версии MS EXCEL 2010 присутствует функция СТАНДОТКЛОН. 2)/(СЧЁТ(Выборка)-1))

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

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

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

Вычисления в функции СРОТКЛ () производятся по формуле:

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

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

Как найти среднее арифметическое чисел (математика)?

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

Предположим, у нас есть итоговая таблица, в которой приводятся оценки одного ученика за выполненные промежуточные контрольные работы: 3, 4, 3, 5, 5. Средним значением за четверть в этом случае будет 4 балла.

Среднее арифметическое Excel

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

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

Это еще не все. После этого нужно ввести аргумент функции, для чего достаточно просто выделить те ячейки, для которых и требуется определение среднего арифметического. После этого нажимаем клавишу «Enter».

Синтаксис СРЗНАЧ

Как видим, нами используется формула СРЗНАЧ, которая имеет свой синтаксис. Он очень простой. Сначала пишется знак равно, как и с любой другой функцией. После этого пишется название СРЗНАЧ, после чего открывается скобка и записывается один единственный аргумент – диапазон значений, из которых требуется получить среднее арифметическое. После этого закрывается скобка.

Аргументы СРЗНАЧ

На практике формула вместе с введенным аргументом будет выглядеть следующим образом.

=СРЗНАЧ(A1:A10).

Такая формула осуществит расчет суммы значений от первого до десятого ряда, после чего поделит получившийся результат на 10. 

Вычисление среднего арифметического с помощью Мастера функций

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

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

Чтобы вызвать мастер функций, необходимо нажать комбинацию клавиш Shift + F3 или найти возле строки ввода формул клавишу fx. После того, как это сделать, появится окошко, в котором нам нужно найти функцию «СРЗНАЧ». Значительно проще искать нужную нам функцию, если выбрать ее тип. В специальном выпадающем меню, расположенном в верхней части экрана, нужно выбрать пункт: «Статистические». Тогда перечень существенно сузится и будет проще выбирать.

Потом появится еще одно окно, в котором можно осуществить ввод аргументов функции СРЗНАЧ.

Частный вариант – вызов функции вывода среднего арифметического из ленты. Для этого надо найти вкладку «Формулы», потом перейти в раздел «Другие функции», там навести мышью на пункт «Статистические». После всех этих операций появится функция СРЗНАЧ.

Панель формул

Каждый документ содержит панель формул, которая меняется в зависимости от того, какую ячейку выбрать. Если формула там есть, то она там будет записана. Если формула отсутствует, то там тогда будет отображаться просто значение ячейки (например, если там записан просто текст). На этом скриншоте видно конкретный пример, как может использоваться строка ввода формул. С ее помощью можно посмотреть на то, какая формула кроется за определенным числом (13,2) на примере, а также отредактировать аргументы. Или вообще убрать старую формулу и ввести новую. Или убрать все формулы, а оставить пустое значение или число. Возможностей у нее много достаточно. Можно выбрать любую, которая поможет выполнить поставленную задачу.

Ручной ввод функций

Функция СРЗНАЧ относится к простым. Ее легко запомнить, а также она содержит всего один аргумент. Поэтому мы ее введем вручную. В качестве примера будем использовать скриншот, приведенный выше. Как видим, можно использовать два разрозненных диапазона, не соединенных непосредственно между собой. 

Мы введем ее вручную.

=СРЗНАЧ(A1:B1;F1:h2)

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

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

Расчет среднего значения по условию

Может понадобиться находить среднее значение для определенных чисел только при условии, что они соответствуют конкретному критерию. Условие может быть любым, как текстовым, так и числовым. Также она может записываться как непосредственно в формулу, так и в другие ячейки.

Можно, конечно, использовать функцию ЕСЛИ в сочетании с функцией СРЗНАЧ, но это немного тяжеловатая задача. Если приходится эту комбинацию использовать очень часто, на это всё требуется много времени. Значительно разумнее использовать функцию СРЗНАЧЕСЛИ. В ней в разных ситуациях используется два или три аргумента, но их водить всё равно быстрее, чем прописывать две разные функции в одну формулу.

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

Конечная формула будет следующей:

 =СРЗНАЧЕСЛИ(A1:A8;”>=10″)

В результате, получится такое значение.

Разберем аргументы этой функции более подробно.

1. Диапазон. Это непосредственно тот диапазон, в котором будет содержаться набор критериев.
2. Условие. Это непосредственно условие. То есть, значение должно как-то соотноситься с критерием. В нашем случае оно должно быть больше или равно 10.
3. Диапазон усреднения. Необязательный аргумент, который используется если значения, для которых нужно искать среднее арифметическое, находятся в другом месте, а не непосредственно являются критериями. 

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

Как найти среднее значение с учетом текста?

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

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

Внешний вид функции будет следующим: =СРЗНАЧЕСЛИ($A$2:$A$12;A7;$B$2:$B$12). В качестве диапазона здесь используется соответствующий столбец (ссылки закреплены на случай, если нужно будет копировать), а критерием поиска будет выступать адрес ячейки, содержащей слово «столы» (или же можно просто написать это слово в соответствующем месте). А в качестве диапазона усреднения мы здесь использовали колонку B.

Важно! Указание диапазона усреднения является обязательным требованием к текстовым критериям.

Как рассчитать средневзвешенную цену в Excel?

Чтобы определить средневзвешенную цену, необходимо использовать функции СУММПРОИЗВ и СУММ. Итак, у нас есть лист, описывающий разные виды товаров, их количество и стоимость.

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

=СУММПРОИЗВ(C2:C12;B2:B12)/СУММ(C2:C12).

Эта формула работает в два этапа. Функция СУММПРОИЗВ позволяет определить общую сумму денег, которую получилось заработать компании после того, как все товары были проданы. А далее используется функция СУММ, которая просто определяет общее количество проданных товаров.

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

Среднее квадратическое отклонение: формула в Excel

Когда заходить речь о среднем арифметическом, обязательно где-то рядом находится еще одно понятие – среднеквадратическое отклонение (или просто стандартное отклонение). Но чтобы понять, что это такое, необходимо сначала разобраться, что такое дисперсия.

Этот термин означает степень разброса значений. Все-таки разница между набором значений 4 и 6 со средним арифметическим 5 и 1 и 9 с тем же средним значением колоссальна. В первом случае дисперсия минимальная, а во втором значения находятся в очень большом разбросе.

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

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

Есть два вида стандартного отклонения в Excel – по генеральной совокупности и выборочной.

Формула дисперсии нам не нужна для расчета стандартного отклонения (за тем лишь исключением, если по каким-то причинам она уже известна, тогда можно просто извлечь из нее корень). Как видим из скриншота выше, есть две формулы стандартного отклонения в Excel.

Здесь, как видим, нужно разобраться еще в двух терминах: генеральная и выборочная совокупность. Первый – это весь диапазон анализируемых данных (общество, например), а второй – это часть этого диапазона, которая должна представлять генеральную совокупность (например, конкретная группа людей, которая соответствует ей по демографическим, социально-экономическим показателям).

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

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

Таким образом, получение среднего арифметического в Excel может осуществляться целым рядом способов. Это одна из самых главных формул, используемых в электронных таблицах. Поэтому ее знать обязательно наизусть. Тем более, что запомнить ее несложно, название интуитивно понятное, а аргумент всего один (хотя если нужно проанализировать несколько диапазонов, то количество параметров будет большим).

Оцените качество статьи. Нам важно ваше мнение:

Как рассчитать коэффициент вариации в Excel

Коэффициент вариации , часто сокращенно CV, — это способ измерить, насколько разбросаны значения в наборе данных относительно среднего. Рассчитывается как:

CV = σ / μ

где:

σ = стандартное отклонение набора данных

μ = среднее значение набора данных

Проще говоря, коэффициент вариации — это просто отношение между стандартным отклонением и средним значением.

Коэффициент вариации часто используется для сравнения вариации между двумя разными наборами данных.

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

Например, предположим, что инвестор рассматривает возможность инвестирования в следующие два паевых инвестиционных фонда:

Паевой фонд A: среднее значение = 7%, стандартное отклонение = 12. 4%

Паевой фонд B: среднее значение = 5%, стандартное отклонение = 8,2%

После расчета коэффициента вариации для каждого фонда инвестор находит:

CV для Паевого инвестиционного фонда A = 12,4% / 7% = 1,77

CV для Паевого инвестиционного фонда B = 8,2% / 5% = 1,64

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

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

Предположим, у нас есть следующий набор данных, содержащий результаты экзаменов 20 студентов:

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

Среднее: = СРЕДНЕЕ (A2: A21)

Стандартное отклонение: = СТАНДОТКЛОН (A2: A21)

Чтобы вычислить коэффициент вариации, мы затем разделим стандартное отклонение на среднее значение:

Коэффициент вариации получается 0.0864 .

Обратите внимание, что мы также могли использовать только одну формулу для расчета CV:

Это приводит к тому же CV 0,0864 .

мер изменчивости | Реальная статистика с использованием Excel

Мы рассматриваем случайную величину x и набор данных S = { x ₁ , x ₂ ,…, x _n } размером n , который содержит возможные значения x .Набор данных может представлять либо изучаемую совокупность, либо выборку, взятую из совокупности. Среднее — это статистика, которая чаще всего используется для характеристики центра данных в S . Теперь мы рассмотрим следующие часто используемые меры изменчивости данных вокруг среднего, а именно: стандартное отклонение , дисперсия , квадратное отклонение , и среднее абсолютное отклонение , .

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

Из этих статистических данных чаще всего используются дисперсия и стандартное отклонение.

Функции Excel : Если R — это диапазон Excel, содержащий элементы данных в S , то функция Excel, которая вычисляет каждую из этих статистических данных, показана на рисунке 1. Функции, отмеченные звездочкой, являются дополнительными функциями, найденными в Real Статистический пакет ресурсов, хотя эквивалентные формулы в стандартном Excel описаны позже.

Статистика	Excel 2007	Excel 2010+	Символ
Дисперсия населения	VARP (R)	VAR.P (R)	σ 2
Вариант выборки	VAR (R)	VAR.S (R)	s 2
) )	STDEV.P (R)	σ
Стандартное отклонение образца	STDEV (R)	STDEV.S (R)	s
Квадратное отклонение R		DEVSQ (R)	SS
Среднее абсолютное отклонение	AVEDEV (R)	AVEDEV (R)	AAD
R (Среднее абсолютное отклонение	)	MAD (R) *	MAD
Диапазон	RNG (R) *	RNG (R) *
Межквартильный диапазон	IQR (R), b *	IQR (R, b 900 87) *	IQR
Коэффициент вариации	STDEV (R) / AVERAGE (R)	STDEV. S (R) / AVERAGE (R)	V

Рисунок 1 — Меры изменчивости

Наблюдение : Эти функции игнорируют любые пустые или нечисловые ячейки.

Дисперсия

Определение 1 : Дисперсия — это мера разброса данных вокруг среднего. Где S представляет генеральную совокупность, дисперсия генеральной совокупности (символ σ ² ) вычисляется из среднего генерального значения µ следующим образом:

Где S представляет собой выборку дисперсию выборки (символ s ² ) вычисляется из выборочного среднего x следующим образом:

Причина, по которой выражение дисперсии генеральной совокупности включает деление на n , в то время как дисперсия выборки включает деление на n — 1 объясняется в Свойстве 3 оценщиков, где требуется деление на n — 1 для получения несмещенной оценки дисперсии генеральной совокупности.

Функция Excel : дисперсия образца рассчитывается в Excel с использованием функции рабочего листа VAR . Дисперсия генеральной совокупности рассчитывается в Excel с помощью функции ДИСПР . В Excel 2010/2013 альтернативными формами этих функций являются VAR.S и VAR.P .

Пример 1 : Если S = {2, 5, -1, 3, 4, 5, 0, 2} представляет совокупность, то дисперсия = 4,25.

Рассчитывается следующим образом.Во-первых, среднее значение = (2 + 5-1 + 3 + 4 + 5 + 0 + 2) / 8 = 2,5, поэтому квадрат отклонения SS = (2–2,5) ² + (5–2,5) ² + (-1–2,5) ² + (3–2,5) ² + (4–2,5) ² + (5–2,5) ² + (0–2,5) ² + (2–2,5) ² = 34. Таким образом, дисперсия = SS / n = 34/8 = 4,25

Если вместо этого S представляет выборку, то среднее значение по-прежнему равно 2,5, но дисперсия = SS / ( n– 1) = 34/7 = 4.86.

Их можно рассчитать в Excel по формулам VARP (B3; B10) и VAR (B3: B10), как показано на рисунке 2.

Рисунок 2 — Примеры показателей изменчивости

Наблюдение : Когда данные представлены в виде частотных таблиц, полезны следующие свойства. Щелкните здесь для подтверждения этих свойств.

Свойство 1 : Если x 900 — среднее значение выборки S = { x ₁ , x ₂ ,…, x _n }, то дисперсия выборки может быть выражено как

Свойство 2 : Если µ — среднее значение генеральной совокупности S = { x ₁ , x ₂ ,…, x _n }, тогда дисперсия генеральной совокупности может быть выражена как

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

Определение 2 : Стандартное отклонение — это квадратный корень из дисперсии.Таким образом, стандартные отклонения генеральной совокупности и выборки рассчитываются соответственно следующим образом:

Функция Excel : Стандартное отклонение выборки рассчитывается в Excel с использованием функции рабочего листа СТАНДОТКЛОН . Стандартное отклонение генеральной совокупности рассчитывается в Excel с помощью функции СТАНДОТКЛОН . В Excel 2010/2013 альтернативными формами этих функций являются СТАНДОТКЛОН.S и СТАНДОТКЛОН.P .

Пример 2 : Если S = {2, 5, -1, 3, 4, 5, 0, 2} является генеральной совокупностью, то стандартное отклонение = квадратный корень из дисперсии генеральной совокупности = 2. 06

Если S является выборкой, то стандартное отклонение выборки = квадратный корень из дисперсии выборки = = 2,20

Это результаты формул STDEVP (B3: B10) и STDEV (B3: B10), как показано на рисунке 2.

Функции реальной статистики : Пакет ресурсов реальной статистики предоставляет следующие функции массива:

VARCOL (R1) = диапазон строк, который содержит образцы стандартной дисперсии каждого из столбцов в R1

STDEVCOL (R1) = диапазон строк, содержащий выборочные стандартные отклонения каждого из столбцов в R1

VARROW (R1) = диапазон столбцов, который содержит выборочные стандартные отклонения каждой из строк в R1

СТАНДОТКЛОН (R1) = диапазон столбцов, который содержит выборочные стандартные отклонения каждой из строк в R1

Пример 3 : Используйте функции VARCOL и STDEVCOL для вычисления выборочной дисперсии и стандартного отклонения. изменение каждого из столбцов в диапазоне L4: N11 на Фигуре 3.

Формула = VARCOL (J4: L11) дает первый результат (в диапазоне J15: L15), а формула = STDEVCOL (J4: L11) дает второй результат (в диапазоне J16: L16). Помните, что после ввода любой из этих формул вы должны нажать Ctrl-Shft-Enter .

Рисунок 3 — Вариация выборки и стандартное отклонение по столбцу

Свойство 3 : Если совокупность { x ₁ , x ₂ ,…, x _n } имеет среднее значение µ _x и стандартное отклонение σ _x , а совокупность {y ₁ , y ₂ ,…, y _m } имеет среднее значение µ _y и стандартное отклонение σ _y , тогда дисперсия объединенной совокупности составляет

Таким образом, если µ _x = µ _y , объединенная дисперсия совокупности будет

Свойство 4 : Если образец { x ₁ , x ₂ ,…, x _n } имеет среднее значение x и стандартное отклонение s _x , а образец {y ₁ , y 9009 2 2 ,…, y _m } имеет среднее значение ȳ и стандартное отклонение s _y , тогда дисперсия объединенной выборки составляет

Таким образом, если x̄ = ȳ , объединенная дисперсия выборки будет

Пример 4 : Найдите среднее значение и дисперсию выборки, полученной в результате объединения двух выборок {3, 4, 6, 7} и {6, 1, 5}.

Рисунок 4 — Вычисление комбинированного среднего и стандартного отклонения

Данные в двух выборках приведены в диапазоне B3: C7 на Рисунке 4. Из них рассчитываются среднее значение, дисперсия и стандартное отклонение для каждого двух образцов (диапазоны B12: B15 и C12: C15). Используя свойство 4, мы можем вычислить среднее значение и дисперсию объединенной выборки (D13 и D14).

Если мы просто объединим две выборки, мы получим данные в диапазоне F3: F10, из которых мы можем вычислить среднее значение, дисперсию и стандартное отклонение обычным способом (диапазон D12: D18).Как видим, результаты такие же.

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

Пример 5 : Найдите среднее значение и дисперсию выборки, полученной в результате объединения трех выборок, показанных в диапазоне A3: D6 на рисунке 5.

Рисунок 5 — Расчет комбинированного среднего и дисперсии

Мы есть три выборки, общий размер выборки которых составляет 58 (ячейка B7), рассчитанный с помощью = СУММ (B4: B6).Сумму элементов в каждой выборке можно вычислить из среднего значения, как показано в диапазоне F4: F6. Например. сумма всех элементов данных в образце 1 равна 276 (ячейка F4), рассчитывается по формуле = B4 * C4. Таким образом, сумма всех элементов во всех трех выборках равна 786 (ячейка F7) и вычисляется по формуле = СУММ (F4: F6). Таким образом, среднее значение объединенной выборки составляет 13,5517 (ячейка C7), рассчитанное по формуле = F7 / B7.

Расчет комбинированной дисперсии аналогичен. Главное — сначала найти сумму квадратов всех элементов в каждой выборке.2) / (B7-1) на основании свойства 1. Таким образом, стандартное отклонение составляет 12,6909.

Квадратное отклонение

Определение 3 : Квадратное отклонение (символ SS для суммы квадратов ) чаще всего используется в ANOVA и связанных с ним тестах. Он рассчитывается как

Функция Excel : Квадрат отклонения рассчитывается в Excel с использованием функции рабочего листа DEVSQ .

Пример 6 : Если S = {2, 5, -1, 3, 4, 5, 0, 2}, квадрат отклонения = 34. Это то же самое, что результат формулы DEVSQ (B3 : B10), как показано на рисунке 2.

Среднее абсолютное отклонение

Определение 4 : Среднее абсолютное отклонение ( AAD ) набора данных S рассчитывается как

Функция Excel : Среднее абсолютное отклонение рассчитывается в Excel с использованием функции рабочего листа AVEDEV .

Пример 7 : Если S = {2, 5, -1, 3, 4, 5, 0, 2}, среднее абсолютное отклонение = 1,75. Это то же самое, что результат формулы AVEDEV (B3: B10), показанной на рисунке 2.

Абсолютное отклонение медианы

Определение 5 : Среднее абсолютное отклонение ( MAD ) набора данных S рассчитывается как медиана

{| x _i — | : x _i в S }

где = медиана элементов данных в S .

Формула Excel : Если R — это диапазон, который содержит элементы данных в S , тогда MAD S можно рассчитать в Excel по формуле массива:

= MEDIAN (ABS (R-MEDIAN (R )))

Даже если значение представлено в одной ячейке, важно, чтобы вы нажали Ctrl-Shft-Enter , чтобы получить значение массива, иначе результат не будет корректным. Эта функция работает правильно, только если R не содержит пустых ячеек или ячеек с нечисловым значением.

В качестве альтернативы вы можете использовать функцию MAD (R), которая содержится в пакете ресурсов реальной статистики. Эта функция работает правильно, даже если R содержит пустые ячейки и / или ячейки с нечисловыми значениями. Вам не нужно нажимать Ctrl-Shft-Enter , чтобы использовать эту функцию.

Пример 8 : Если S = {2, 5, -1, 3, 4, 5, 0, 2}, среднее абсолютное отклонение = 2, поскольку S = {-1, 0, 2, 2, 3, 4, 5, 5}, и поэтому медиана S = (2 + 3) / 2 = 2.5. Таким образом, MAD = медиана {3,5, 2,5, 0,5, 0,5, 0,5, 1,5, 2,5, 2,5} = {0,5, 0,5, 0,5, 1,5, 2,5, 2,5, 2,5, 3,5}, т. Е. (1,5 + 2,5) / 2 = 2.

Вы можете достичь того же результата, используя формулу = MAD (E3: E10), как показано на рисунке 2.

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

Диапазон

Определение 6 : Диапазон набора данных S является приблизительной мерой изменчивости и состоит просто из разницы между наибольшим и наименьшим значениями в S .

Формула Excel : Если R — это диапазон, который содержит элементы данных в S , тогда диапазон S можно рассчитать в Excel по формуле:

= MAX (R) — MIN (R)

В качестве альтернативы вы можете использовать функцию RNG (R), которая содержится в пакете ресурсов реальной статистики.

Пример 9 : Если S = {2, 5, -1, 3, 4, 5, 0, 2}, диапазон = 5 — (-1) = 6. Вы можете достичь того же результата, используя формула = RNG (E3: E10), как показано на рисунке 2.

Межквартильный диапазон

Определение 7 : Межквартильный диапазон ( IQR ) набора данных S рассчитывается как 75% процентиль S минус 25% процентиль . IQR обеспечивает грубую аппроксимацию изменчивости около центра данных в S.

Формула Excel : Если R — это диапазон, который содержит элементы данных в S , то IQR S может быть рассчитывается в Excel по формуле:

= КВАРТИЛЬ (R, 3) — КВАРТИЛЬ (R, 1)

В Excel 2010/2013 появилась новая версия функции квартилей под названием КВАРТИЛЬ.ИСКЛ. Таким образом, альтернативная версия IQR:

= КВАРТИЛЬ.EXC (R, 3) — КВАРТИЛЬ.EXC (R, 1)

Дополнительную информацию о функциях КВАРТИЛЬ и КВАРТИЛЬ.EXC см. В разделе Функции ранжирования в Excel. Кроме того, вы можете рассчитать межквартильный диапазон с помощью функции IQR (R, b ), которая содержится в пакете ресурсов Real Statistics. Когда b = FALSE (по умолчанию), возвращается первая версия IQR, а когда b = TRUE, возвращается вторая версия.

Пример 10 : Если S = {2, 5, -1, 3, 4, 5, 0, 2}, то первая версия IQR = 4,25 — 1,5 = 2,75, а вторая версия — IQR = 4,75 — 0,5 = 4,25. Вы можете достичь того же результата, используя формулы реальной статистики = IQR (B3: B10) и = IQR (B3: B10, TRUE), как показано на рисунке 2.

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

Коэффициент вариации

Определение 8 : Коэффициент вариации (также известный как коэффициент изменчивости ), V (или CV ) набора данных S вычисляется как

V = s / x̄

Поскольку s и x̄ имеют одинаковые единицы измерения, V не имеет единиц измерения. Эта статистика имеет смысл только для данных шкалы соотношений. Чем выше значение V , тем больше дисперсия .

Понятно, что коэффициент вариации определяется только тогда, когда среднее значение не равно нулю.

Формула Excel : Если R — это диапазон, который содержит элементы данных в S , то коэффициент вариации для S можно рассчитать в Excel по формуле:

= СТАНДАРТ.S (R) / СРЕДНЕЕ (R)

Версия генерации V — σ / μ , которую можно рассчитать в Excel по формуле

= СТАНДОТКЛОН.P (R) / AVERAGE (R)

Пример 11 : Если S = {2, 5, -1, 3, 4, 5, 0, 2} представляет собой образец, то, как мы можем видеть из Пример 1, коэффициент вариации

V = s / x ̄ = 2,203892 / 2,5 = 88,16%

Пример 12 : Акция A имеет ожидаемую доходность 12% со стандартным отклонением 9% и акция B имеет ожидаемую доходность 8% со стандартным отклонением 5%. Используйте коэффициент вариации, чтобы определить, что является лучшим вложением.

Начиная с V _A = .09 / .12 = .75 и V _B = .05 / .08 = 0,625, акция B считается лучшей инвестицией, поскольку ее относительный риск (равен коэффициенту вариации) ниже.

Как рассчитать коэффициент вариации с примерами

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

Что такое коэффициент вариации?

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

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

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

Связано: Узнайте о том, как стать аналитиком данных

Использование коэффициента вариации

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

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

Связано: Узнайте, как стать учителем

Как рассчитать коэффициент вариации

Как юридические, так и частные лица могут нуждаться в расчете резюме. Основная формула, используемая в математике, устанавливает коэффициент вариации, равный стандарту отклонения над средним:

CV = Стандарт отклонения / Среднее x 100%

Поскольку эти термины могут быть немного сложными для бизнеса и финансов, условия часто меняются:

CV = волатильность / ожидаемая доходность x 100%

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

1. Определение волатильности

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

2. Определите ожидаемую доходность

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

3. Разделить

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

4. Умножение на 100%

Для преобразования в проценты умножьте десятичные дроби на 100%. Он перемещает десятичный разряд, создавая либо целое число, либо десятичный процент.Окончательный ответ — коэффициент вариации.

Как рассчитать коэффициент вариации в электронной таблице

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

1. Использование функции стандартного отклонения для набора данных

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

2.Вычислить среднее значение

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

3. Разделите стандартное отклонение на среднее значение

Получив как стандартное отклонение, так и среднее значение, все, что осталось, — это деление. Например, в обычно используемом процессоре электронных таблиц, если необходимо разделить сумму в ячейках A3 и A5, используйте функцию = A3 / A5 для вычисления дивиденда.Полученный ответ — коэффициент вариации.

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

Связано: Узнайте, как стать клерком по вводу данных

Примеры расчетов

Коэффициенты вариации полезны для широкого круга лиц, а не только для предприятий.Например:

Получив огромную прибыль от инвестиций с высоким риском, Джамила теперь ищет более безопасные инвестиции со стабильной и многообещающей прибылью. Теперь у нее есть три варианта:

• Инвестиции в акции: Ввиду успеха ее предыдущих инвестиций подруга предлагает Джамиле акции их компании. Он показал хорошие финансовые показатели за последние три года, его волатильность составляет 7%, а ожидаемая доходность — 13%.
• Вложение в облигации: Коллега консультирует Джамилу по новой облигации с высоким кредитным рейтингом.Его волатильность составляет 6%, а ожидаемая доходность — 4%.
• Биржевой фонд: Джамила наткнулась на редкий фонд, предлагающий волатильность 8% и ожидаемую доходность 16%.

Чтобы принять обоснованное решение, Джамила применяет ко всем трем вариантам формулу коэффициента вариации.

Для расчета коэффициента вариации своих потенциальных инвестиций в акции Джамила вводит свой процент волатильности 7 и ожидаемый процент доходности 13.

Инвестиции в акции: CV = (7/13) x 100%

Сначала разделите волатильность и доходность.

CV = 0,5385 x 100%

CV = 0,5385

Преобразуйте ответ в проценты, сдвинув десятичную дробь на два разряда вправо.

CV = 53,9%

Вложения Джамилы в акции показывают коэффициент вариации 53,9%. Теперь ей нужно определить размер своих вложений в облигации.

Для расчета коэффициента вариации вложения в облигации Джамила вводит свой процент волатильности, равный 6, и ожидаемый процент доходности, равный 4.

Инвестиции в облигации: CV = (6 / 4) x 100%

Сначала разделите волатильность и верните.

CV = 1,5 x 100%

CV = 0,015

Преобразуйте ответ в проценты.

CV = 15%

Инвестиции в облигации Джамилы показывают коэффициент вариации 15%.

Для расчета коэффициента вариации своего биржевого фонда Джамила вводит свой процент волатильности 8 и ожидаемый процент доходности 16.

Биржевой фонд : CV = (8/16) x 100%

CV = 0,5

CV = 50%

Инвестиции в ETF Джамилы показывают коэффициент вариации 50%.

После того, как взвесили все три варианта, Джамила теперь анализирует свои проценты. Вложения в облигации показывают самый низкий коэффициент вариации — всего 15%. Он также имеет самую высокую ожидаемую доходность из всех трех вариантов. При таком благоприятном соотношении риска и прибыли Джамила решает инвестировать в облигацию.

Как найти отклонение в Excel

Что нужно знать

• Чтобы вычислить дисперсию на основе всей генеральной совокупности в Excel, используйте VAR.P-функция . Синтаксис: ДИСПР.П (число1, [число2], …)
• Чтобы вычислить стандартное отклонение на основе всей генеральной совокупности, указанной в качестве аргументов, используйте функцию СТАНДОТКЛОН.П .

В этой статье объясняется суммирование данных и использование формул отклонения и отклонения в Excel для Microsoft 365, Excel 2019, 2016, 2013, 2010, 2007 и Excel Online.

Обобщение данных: центральная тенденция и спред

Центральная тенденция сообщает вам, где находится середина данных или среднее значение.Некоторые стандартные меры центральной тенденции включают среднее значение, медианное значение и моду.

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

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

Формула стандартного отклонения и дисперсии

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

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

Это даст дисперсию, своего рода среднее значение для всех квадратов разностей. Извлечение квадратного корня из дисперсии исправляет тот факт, что все различия были возведены в квадрат, что приводит к стандартному отклонению. Вы будете использовать его для измерения разброса данных. Если это сбивает с толку, не волнуйтесь. Excel выполняет фактические расчеты.

Выборка или совокупность?

Часто ваши данные будут выборкой, взятой из более широкой совокупности. Вы хотите использовать эту выборку для оценки дисперсии или стандартного отклонения для генеральной совокупности в целом. В этом случае вместо деления на количество наблюдений ( n ) вы делите на n -1. Эти два разных типа вычислений имеют разные функции в Excel:

• Функции с P : Выдает стандартное отклонение для фактических значений, которые вы ввели.Они предполагают, что ваши данные — это все население (деление на n ).
• Функции с S : дает стандартное отклонение для генеральной совокупности, если ваши данные являются выборкой, взятой из нее (деление на n -1). Это может сбивать с толку, поскольку эта формула дает расчетную дисперсию для генеральной совокупности; S указывает, что набор данных является выборкой, но результат предназначен для генеральной совокупности.

Использование формулы стандартного отклонения в Excel

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

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

   Для остальной части этого примера данные находятся в диапазоне A1: A20.

2. Если ваши данные представляют всю генеральную совокупность, введите формулу « = СТАНДОТКЛОН.P (A1: A20) .«В качестве альтернативы, если ваши данные являются выборкой из некоторой большей совокупности, введите формулу« = СТАНДОТКЛОН (A1: A20) ».

   Если вы используете Excel 2007 или более раннюю версию или хотите, чтобы ваш файл был совместим с этими версиями, формулы: «= STDEVP (A1: A20)», , если ваши данные представляют собой всю генеральную совокупность; «= СТАНДОТКЛОН (A1: A20)», если ваши данные являются выборкой из более широкой генеральной совокупности.

3. Стандартное отклонение будет отображаться в ячейке.

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

Вычисление дисперсии очень похоже на вычисление стандартного отклонения.

1. Убедитесь, что ваши данные находятся в одном диапазоне ячеек в Excel.

2. Если ваши данные представляют всю генеральную совокупность, введите формулу « = VAR.P (A1: A20) ». В качестве альтернативы, если ваши данные являются выборкой из некоторой более широкой совокупности, введите формулу « = VAR.S (A1: A20) ».

   Если вы используете Excel 2007 или более раннюю версию или хотите, чтобы ваш файл был совместим с этими версиями, формулы будут следующими: «= VARP (A1: A20),« если ваши данные — генеральная совокупность, или »= VAR ( A1: A20), «если ваши данные являются выборкой из более широкой генеральной совокупности.

3. Разница для ваших данных будет отображаться в ячейке.

Спасибо, что сообщили нам об этом!

Расскажите, почему!

Коэффициент вариации, дисперсии и стандартного отклонения

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

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

Когда у нас есть вся генеральная совокупность, каждая точка данных известна, поэтому вы на 100% уверены в тех показателях, которые мы рассчитываем.

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

Более того, если мы извлечем 10 разных выборок из одной и той же популяции, мы получим 10 разных показателей.

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

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

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

Формула дисперсии: дисперсия выборки и дисперсия совокупности

Разница измеряет разброс набора точек данных вокруг их среднего значения .

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

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

Более пристальный взгляд на формулу дисперсии совокупности

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

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

Почему мы поднимаемся до второй ступени

Возведение в квадрат различий преследует две основные цели.

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

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

2. Во-вторых, возведение в квадрат усиливает эффект больших различий. Например, если среднее значение равно 0, а у вас есть наблюдение 100, квадрат разброса равен 10 000!

Использование формулы численности населения

Хорошо, хватит сухой теории.Пришло время практического примера. У нас есть совокупность из пяти наблюдений — 1, 2, 3, 4 и 5. Давайте найдем ее дисперсию .

Начнем с вычисления среднего : (1 + 2 + 3 + 4 + 5) / 5 = 3.

Затем мы применяем формулу, которую мы только что обсудили: ((1 — 3) ² + (2 — 3) ² + (3 — 3) ² + (4 — 3) ² + (5 — 3) ² ) / 5.

Когда мы посчитаем, мы получим 2. Итак, дисперсия совокупности набора данных равна 2.

Расчет дисперсии выборки

А как насчет выборочной дисперсии ? Это было бы подходящим, только если бы нам сказали, что эти пять наблюдений были выборкой, взятой из совокупности. Итак, давайте представим, что это так. Образец означает, что снова равно 3. Числитель тот же, но знаменатель будет 4 вместо 5.

Это дает нам выборочную дисперсию , равную 2,5.

Почему результаты не совпадают

Чтобы завершить тему дисперсии , мы должны интерпретировать результат.Почему дисперсия выборки больше, чем дисперсия генеральной совокупности ? В первом случае мы знали население. То есть у нас были все данные, и мы вычислили дисперсию . Во втором случае нам сказали, что 1, 2, 3, 4 и 5 были выборкой, взятой из большей совокупности.

Население выборки

Представьте, что совокупность выборки состояла из следующих 9 чисел: 1, 1, 1, 2, 3, 4, 5, 5 и 5.

Очевидно, что числа те же, но есть концентрация вокруг двух крайних значений набора данных — 1 и 5.Разница этой совокупности составляет 2,96.

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

Формула стандартного отклонения: стандартное отклонение выборки и стандартное отклонение совокупности

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

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

Формулы

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

Коэффициент вариации (CV)

Последний показатель, который мы введем, — это коэффициент вариации . Оно равно стандартному отклонению , деленному на среднего .

\

Другое название этого термина — относительное стандартное отклонение . Это простой способ запомнить его формулу — это просто стандартное отклонение относительно среднего .

Как вы, наверное, догадались, снова есть формула генеральной совокупности и выборки.

Зачем нужен коэффициент вариации

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

Аристотель однажды сказал:

«Скажи, я забуду. Покажи мне, я запомню. Вовлеките меня, я пойму.

Примеры сравнения стандартных отклонений

Чтобы запомнить, вот пример сравнения стандартных отклонений . Давайте возьмем цены на пиццу в 10 разных местах Нью-Йорка. Как видно на картинке ниже, они варьируются от 1 до 11 долларов.

А теперь представьте, что у вас есть только мексиканские песо.Для вас цены будут больше похожи на 18,81 песо к 206,91 песо при обменном курсе 18,81 песо за доллар.

Давайте объединим наши знания и найдем стандартных отклонений, и коэффициентов вариации этих двух наборов данных.

Данные выборки или совокупности

1. Во-первых, мы должны увидеть, является ли это выборкой или генеральной совокупностью. В Нью-Йорке всего 11 ресторанов? Конечно, нет. Очевидно, это образец, взятый из всех ресторанов города.Затем мы должны использовать формулы для выборки мер изменчивости .

В поисках среднего

2. Во-вторых, нам нужно найти , среднее значение . означает в долларах равно 5,5, а означает в песо — 103,46.

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

3. Третий шаг процесса — нахождение выборочной дисперсии .Следуя формуле, которую мы рассмотрели ранее, мы можем получить 10,72 доллара в квадрате и 3793,69 песо в квадрате.

4. Соответствующий образец стандартных отклонений составляет 3,27 доллара и 61,59 песо, как показано на рисунке ниже.

Несколько наблюдений

Сделаем пару наблюдений.

Во-первых, отклонение дает результаты в квадратах, а стандартное отклонение в исходных единицах, как показано ниже.

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

Во-вторых, мы получили стандартных отклонений, 3,27 и 61,59 для той же пиццы в одних и тех же 11 ресторанах Нью-Йорка. Однако это кажется неправильным. Давайте исправим это, используя наш последний инструмент — , коэффициент вариации .

Преимущество коэффициента вариации

Мы можем разделить стандартных отклонений на соответствующие , значит . Как вы можете видеть на картинке ниже, мы получаем два коэффициента вариации .

Результат тот же — 0,60.

Важно: Обратите внимание, что это не доллары, песо, доллары в квадрате или песо в квадрате. Это всего 0,60.

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

На картинке выше вы можете увидеть основные преимущества коэффициента вариации .

Плюсы и минусы каждого из показателей изменчивости

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

Теперь использование мер при работе с одной переменной, вероятно, кажется простым занятием. Однако что, если бы было 2 переменных? Сможете ли вы изобразить их отношения? Если ваш ответ: нет , смело переходите к следующему руководству, чтобы превратить нет в да .

Или, если вы подумываете о карьере в области науки о данных, ознакомьтесь с нашими статьями: Профиль специалиста по данным, 5 навыков, необходимых для соответствия любому описанию работы в области науки о данных, Как написать резюме по науке о данных — Полное руководство и 15 компаний, занимающихся консалтингом в области анализа данных, нанимают сотрудников.

***

Хотите узнать больше? Вы можете улучшить свои навыки с помощью нашего курса статистики!

Попробовать курс статистики бесплатно

Next Tutorial: Как использовать ковариацию и коэффициент линейной корреляции

Формула коэффициента вариации

— концепция и приложения

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

Таким образом, это определение статистики стало известным во всем мире. «Статистика похожа на бикини. Аспекты, которые он раскрывает, интересны, а данные, которые он скрывает, важны ».

В статистическом наборе данных обязательно должен быть разброс. Некоторые значения могут быть выше среднего, а некоторые — ниже.Этот разброс данных вокруг среднего значения известен в статистике как отклонение.

Используя каждое из этих отклоненных значений, можно вычислить стандартное отклонение. Это стандартное отклонение составляет основу определения коэффициента вариации.

Что такое коэффициент вариации (CV)?

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

Проще говоря, вы можете объяснить, что CV равен отношению стандартного отклонения к среднему.

Формула коэффициента вариации

Формула коэффициента вариации следующая.

Формула вариации

Коэффициент вариации = стандартное отклонение / среднее значение

Вы можете иметь его в простой десятичной форме или умножить на 100%, чтобы получить процентное значение.

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

Загрузите подробную учебную программу и получите бесплатный доступ к ознакомительному занятию

Дата: 10 апреля 2021 г. (суббота)
Время: 10:30 — 11:30 (IST / GMT +5: 30)

Пример коэффициента вариации

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

Нам доступны следующие данные.

рупий

Территория А	Территория Б
Средняя арендная плата составляет около 120 000	Средняя арендная плата составляет около 200000 рупий
Стандартное отклонение 2,000	Стандартное отклонение 3000
Коэффициент вариации = 2000/120000 = 0,016 или 1,60%	Коэффициент вариации = 3000/200000 = 0. 015 или 1,50%

(i) Если вы посмотрите на стоимость аренды, территория A кажется более подходящей, поскольку средняя стоимость аренды значительно ниже по сравнению с территорией B.

(ii) Однако это неправильный вариант, потому что разница в стоимости аренды ниже на Территории B по сравнению с Территорией A

.

(iii) CV территории B составляет 1,50%, тогда как CV территории A составляет 1,60%

(iv) Таким образом, лучший вариант для владельца ресторана — открыть QSR на территории B.

Что это означает?

Приведенный выше пример доказывает, что более низкое значение коэффициента вариации предпочтительнее из-за меньшей степени волатильности.

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

Коэффициент вариации и стандартное отклонение

Коэффициент вариации (с финансовой точки зрения) также называется волатильностью инвестиций.Естественно, более безопасными являются вложения с меньшей степенью волатильности. Таким образом, в инвестиционном сценарии формула коэффициента вариации должна иметь вид

.

CV = волатильность / ожидаемая доходность X 100%

(Волатильность представляет собой стандартное отклонение, а «Среднее» относится к вознаграждению или ожидаемой отдаче от инвестиций)

Рассмотрим этот простой пример, который может правильно рассмотреть ситуацию.

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

Акции

Акции компании А доступны. Рамеш отмечает, что компания является зрелой и имеет хорошие производственные и финансовые показатели. Ожидаемая доходность составляет около 14% при волатильности акций 10%.

Биржевой фонд (ETF)

Финансовый советник предлагает возможность инвестирования в ETF. Рамеш подсчитал, что ожидаемая доходность находится в диапазоне 13%, но волатильность меньше 7%.

Облигации

Облигации с хорошими кредитными рейтингами предлагают среднюю доходность 3% при низкой волатильности 2%.

Давайте посмотрим, что делает Рамеш в данных обстоятельствах.

Рамеш использует формулу коэффициента вариации, чтобы получить следующие результаты.

Акции — CV = 10% / 14% X 100% = 71,40%

ETF — CV = 7% / 13% X 100% = 53.80%

Облигации — CV = 2% / 3% X 100% = 66,67%

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

Формула резюме помогает Рамешу сделать правильное вложение. Нет никаких стимулов предполагать, что Рамешу следует выбрать вариант ETF.У этого варианта самое низкое соотношение риска к прибыли.

Как рассчитать коэффициент вариации в Excel

Мы видели элементарные примеры, объясняющие концепцию коэффициента вариации. Однако на самом деле таких простых расчетов вы никогда не встретите. Поэтому следует знать, как использовать MS Excel для определения формулы коэффициента вариации.

Загрузите подробную учебную программу и получите бесплатный доступ к ознакомительному занятию

Дата: 10 апреля 2021 г. (суббота)
Время: 10:30 — 11:30 (IST / GMT +5: 30)

Вот как рассчитать коэффициент вариации в Excel.

(i) Предположим, что у вас есть десять значений данных от A2 до A11

(ii) Рассчитайте среднее из десяти значений с помощью функции «СРЕДНИЙ (A2: A11).

(iii) Рассчитайте стандартное отклонение десяти значений с помощью функции «СТАНДОТКЛОН» (A2: A11).

(iv) Коэффициент вариации — это соотношение между стандартным отклонением и средним значением, умноженное на 100.

(v) Используйте эту формулу, чтобы получить CV

Преимущества коэффициента вариации

Мы рассмотрели понятие коэффициента вариации, а также обсудили формулу коэффициента вариации.Точно так же мы также видели, как рассчитать коэффициент вариации в Excel.

Мы также изучили несколько примеров, чтобы понять, как можно использовать резюме в различных обстоятельствах. Теперь мы перейдем к обсуждению преимуществ CV.

1. Измерение точности

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

Мы видели пример использования CV для выбора лучшего инвестиционного портфеля в предыдущем абзаце. В этом примере у нас было три разных набора данных: акции, ETF и облигации. Концепция CV помогла нам сосредоточиться на наилучшем возможном варианте инвестирования в примере.

Преимущества CV — мера точности

2. Измерение повторяемости

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

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

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

Если у вас есть разные наборы данных, CV повторяемости будет выше по сравнению с CV, рассчитанным для аналогичных наборов данных.

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

Преимущества коэффициента вариации

3. Согласованность данных

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

См. Пример QSR в одном из предыдущих абзацев. Рассмотрим территорию A, где средняя стоимость аренды составляет 120 000 рупий, а стандартное отклонение — 2000. Следовательно, это означает, что значительная часть расходов на аренду будет составлять от 122 000 до 118 000 рупий.

Когда мы использовали формулу вариации коэффициентов , , мы обнаружили, что CV равняется 1.60%. Таким образом, он показывает, что максимальная арендная плата находится в диапазоне от 121 920 до 118 080 рупий. Это позволяет нам определить согласованность данных.

4. Индикатор оценки риска

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

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

5. Помощь в принятии решений

Если вы знаете, как рассчитать коэффициент вариации , , вам будет легко выбрать лучшие варианты, доступные вам. Эта концепция полезна, когда компания решает сократить масштабы своей деятельности из-за высоких затрат.Компании, возможно, придется уволить некоторых из своих сотрудников. Концепция CV помогает решить, какой отдел должен столкнуться с топором.

Этот пример должен помочь нам лучше понять концепцию.

ИТ-компания планирует сократить штат из-за высоких операционных расходов. Есть два отдела, например, кодирование и биллинг.

Кодировка	Биллинг
Количество сотрудников = 40	Количество сотрудников = 65
Средняя зарплата на одного сотрудника = 450 долларов США	Средняя зарплата на одного сотрудника = 350 $
Итого зарплата отдела = 18000 долларов	Итого зарплата по отделу = 22750 $
Стандартное отклонение = 7	Стандартное отклонение = 9
Вариация коэффициента для кодирования = 7/450 * 100 = 1.6%	Изменение коэффициента для выставления счетов = 9/350 * 100 = 2,6%

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

Коэффициент вариации

Недостатки коэффициента вариации

Разобравшись с преимуществами CV, давайте также рассмотрим недостатки этой концепции. Это поможет лучше понять.

1. Нулевой фактор — существенный недостаток

CV — это отношение стандартного отклонения к среднему.

Формула коэффициента вариации говорит: CV = стандартное отклонение / среднее значение * 100%.

Следовательно, значение CV зависит как от стандартного отклонения, так и от среднего. Если среднее значение становится «нулевым», тогда CV не принимает конечного значения. На самом деле, среднее значение иногда может быть нулевым, в зависимости от данных выборки. В таких обстоятельствах, когда среднее значение равно нулю, CV становится неопределимой.

2. Фактор логарифма

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

Загрузите подробную учебную программу и получите бесплатный доступ к ознакомительному занятию

Дата: 10 апреля 2021 г. (суббота)
Время: 10:30 — 11:30 (IST / GMT +5: 30)

Стандартное отклонение против коэффициента вариации

Мы много обсуждали CV и стандартное отклонение. Давайте теперь посмотрим на разницу между двумя концепциями на простом примере.

Предположим, вы выбираете футбольную команду и хотите выбрать одного игрока в команду.Перед вами два варианта. Вот статистика забитых голов обоими игроками в последних 30 матчах, в которых они играли.

Игрок A	Игрок B
Среднее = 60	Среднее = 48
Стандартное отклонение = 18	Стандартное отклонение = 12
Коэффициент вариации = 18/60 * 100 = 30%	Коэффициент вариации = 12/48 * 100 = 25%

Если вы воспользуетесь методом стандартного отклонения, вы можете выбрать игрока A, потому что A забил больше голов, чем B.Однако, взглянув на CV, вы обнаружите, что CV игрока B на 25% ниже, чем у 30% игрока A.

Таким образом, выбор игрока B должен быть идеальным вариантом.

Последние мысли

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

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

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

Вы также можете записаться на курс Data Analytics, чтобы получить более прибыльные возможности карьерного роста в Data Analytics.

Как рассчитать дисперсию в Excel — формула дисперсии выборки и генеральной совокупности

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

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

Что такое дисперсия?

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

Чтобы лучше понять, что вы на самом деле вычисляете с помощью дисперсии, рассмотрим этот простой пример.

Предположим, что в вашем местном зоопарке 5 тигров в возрасте 14, 10, 8, 6 и 2 лет.

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

1. Вычислите среднее (простое среднее) пяти чисел:
   
2. Из каждого числа вычтите среднее значение, чтобы найти различия. Чтобы наглядно это представить, нарисуем различия на графике:
   
3. Возведите каждую разницу в квадрат.
4. Вычислите среднее значение квадратов разностей.

Итак, дисперсия равна 16. Но что на самом деле означает это число?

По правде говоря, дисперсия дает вам очень общее представление о дисперсии набора данных. Значение 0 означает, что нет изменчивости, т. Е. Все числа в наборе данных одинаковы. Чем больше число, тем больше разброс данных.

Этот пример предназначен для дисперсии популяции (т.е. 5 тигров — это целая группа, которая вас интересует).Если ваши данные являются выборкой из более широкой генеральной совокупности, вам необходимо рассчитать дисперсию выборки, используя немного другую формулу.

Как рассчитать дисперсию в Excel

В Excel есть 6 встроенных функций для расчета дисперсии: ДИСП, ДИСПР, ДИСПР, ДИСПР, ДИСПР, ДИСПР и ДИСПР.

Ваш выбор формулы дисперсии определяется следующими факторами:

• Версия используемого Excel.
• Рассчитываете ли вы дисперсию выборки или генеральной совокупности.
• Хотите ли вы оценивать или игнорировать текст и логические значения.

Функции отклонения Excel

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

Имя	Excel версия	Тип данных	Текст и логика
VAR	2000-2019	Образец	Игнорируется
ВАР.S	2010-2019	Образец	Игнорируется
ВАРА	2000-2019	Образец	Оценено
ВАРП	2000-2019	Население	Игнорируется
ВАР.П	2010-2019	Население	Игнорируется
ВАРПА	2000-2019	Население	Оценено

VAR.S против VARA и VAR.P против VARPA

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

Тип аргумента	VAR, VAR.S, VARP, VAR.P	ВАРА И ВАРА
Логические значения в массивах и ссылках	Игнорируется	Вычислено (ИСТИНА = 1, ЛОЖЬ = 0)
Текстовое представление чисел в массивах и ссылках	Игнорируется	Оценивается как ноль
Логические значения и текстовые представления чисел, вводимых непосредственно в аргументы	Вычислено (ИСТИНА = 1, ЛОЖЬ = 0)
Пустые ячейки	Игнорируется

Как рассчитать выборочную дисперсию в Excel

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

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

Дисперсия выборки рассчитывается по следующей формуле:

Где:

• x — среднее (простое среднее) значений выборки.
• n — размер выборки, то есть количество значений в выборке.

Существует 3 функции для поиска выборочной дисперсии в Excel: ДИСП, ДИСПР и ДИНА.

Функция ДНА в Excel

Это самая старая функция Excel для оценки дисперсии на основе выборки. Функция VAR доступна во всех версиях Excel с 2000 по 2019 год.

VAR (число1, [число2],…)

Примечание. В Excel 2010 функция VAR была заменена на VAR.S, что обеспечивает повышенную точность.Хотя VAR по-прежнему доступен для обратной совместимости, рекомендуется использовать VAR. S в текущих версиях Excel.

Функция ДИСП. В Excel

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

VAR.S (число1, [число2],…)

Функция ДИНА в Excel

Функция Excel VARA возвращает образец дисперсии на основе набора чисел, текста и логических значений, как показано в этой таблице.

VARA (значение1, [значение2],…)

Пример формулы дисперсии в Excel

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

В качестве примера найдем дисперсию выборки, состоящей из 6 элементов (B2: B7). Для этого вы можете использовать одну из следующих формул:

= VAR (B2: B7)

= VAR.S (B2: B7)

= ВАРА (B2: B7)

Как показано на скриншоте, все формулы возвращают один и тот же результат (с округлением до двух знаков после запятой):

Чтобы проверить результат, сделаем вычисление var вручную:

1. Найдите среднее значение с помощью функции СРЕДНИЙ:
   = СРЕДНИЙ (B2: B7)

   Среднее значение попадает в любую пустую ячейку, скажем, B8.2

2. Сложите квадраты разностей и разделите результат на количество элементов в выборке минус 1:
   = СУММ (D2: D7) / (6-1)

Как видите, результат нашего ручного вычисления переменной в точности совпадает с числом, возвращаемым встроенными функциями Excel:

Если ваш набор данных содержит значения Boolean и / или text , функция VARA вернет другой результат. Причина в том, что VAR и VAR.S игнорирует любые значения, кроме чисел в ссылках, тогда как VARA оценивает текстовые значения как нули, TRUE как 1 и FALSE как 0. Поэтому, пожалуйста, внимательно выбирайте функцию дисперсии для своих вычислений в зависимости от того, хотите ли вы обрабатывать текст и логические выражения или игнорировать их. .

Как рассчитать дисперсию генеральной совокупности в Excel

Население — это все члены данной группы, то есть все наблюдения в области исследования. Дисперсия совокупности описывает, как распределены точки данных во всей генеральной совокупности.

Дисперсия генеральной совокупности может быть найдена по следующей формуле:

Где:

• x — среднее значение для населения.
• n — размер популяции, то есть общее количество значений в генеральной совокупности.

Есть 3 функции для расчета дисперсии генеральной совокупности в Excel: VARP, VAR.P и VARPA.

Функция ДИСПР в Excel

Функция ДИСПР в Excel возвращает дисперсию генеральной совокупности на основе всего набора чисел.Он доступен во всех версиях Excel с 2000 по 2019 год.

VARP (число1, [число2],…)

Примечание. В Excel 2010 VARP был заменен на VAR.P, но все еще сохранен для обратной совместимости. Рекомендуется использовать VAR.P в текущих версиях Excel, поскольку нет гарантии, что функция VARP будет доступна в будущих версиях Excel.

Функция ДИСПР.П в Excel

Это улучшенная версия функции VARP, доступная в Excel 2010 и более поздних версиях.

VAR.P (число1, [число2],…)

Функция ДИСПРА в Excel

Функция ДИСПР вычисляет дисперсию генеральной совокупности на основе всего набора чисел, текста и логических значений. Он доступен во всех версиях Excel с 2000 по 2019 год.

VARA (значение1, [значение2],…)

Формула дисперсии совокупности в Excel

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

Допустим, у нас есть экзаменационные баллы группы из 10 студентов (B2: B11). Оценки составляют всю генеральную совокупность, поэтому мы сделаем дисперсию с помощью следующих формул:

= VARP (B2: B11)

= VAR.P (B2: B11)

= ВАРПА (B2: B11)

И все формулы вернут одинаковый результат:

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

Если некоторые учащиеся не сдали экзамен и вместо числа баллов имеют значение «Н / Д», функция ДИСПРА вернет другой результат.Причина в том, что VARPA оценивает текстовые значения как нули, в то время как VARP и VAR.P игнорируют текстовые и логические значения в ссылках. Пожалуйста, см. VAR.P vs. VARPA для получения полной информации.

Формула отклонения в Excel — примечания по использованию

Чтобы правильно провести дисперсионный анализ в Excel, следуйте этим простым правилам:

• Предоставляет аргументы в виде значений, массивов или ссылок на ячейки.
• В Excel 2007 и более поздних версиях можно указать до 255 аргументов, соответствующих выборке или генеральной совокупности; в Excel 2003 и старше — до 30 аргументов.
• Чтобы оценить только числа в ссылках, игнорируя пустые ячейки, текст и логические значения, используйте функцию ДИСПР или ДИСПР.S для вычисления дисперсии выборки и ДИСПР или ДИСПР.P для поиска дисперсии генеральной совокупности.
• Чтобы оценить логических и текстовых значений в ссылках, используйте функцию ДИСПА или ДИСПРА.
• Укажите не менее двух числовых значений для выборочной формулы дисперсии и не менее одно числовое значение для формулы дисперсии генеральной совокупности в Excel, в противном случае — # DIV / 0! возникает ошибка.
• Аргументы, содержащие текст, который нельзя интерпретировать как числа, вызывают #VALUE! ошибки.

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

Дисперсия, несомненно, полезное понятие в науке, но оно дает очень мало практической информации. Например, мы нашли возраст популяции тигров в местном зоопарке и вычислили дисперсию, равную 16. Вопрос в том, как на самом деле использовать это число?

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

Стандартное отклонение рассчитывается как квадратный корень из дисперсии. Итак, извлекаем квадратный корень из 16 и получаем стандартное отклонение 4,

.

В сочетании со средним значением стандартное отклонение может сказать вам, сколько лет большинству тигров. Например, если среднее значение равно 8, а стандартное отклонение равно 4, возраст большинства тигров в зоопарке составляет от 4 лет (8–4) до 12 лет (8 + 4).

Microsoft Excel имеет специальные функции для расчета стандартного отклонения выборки и генеральной совокупности.Подробное объяснение всех функций можно найти в этом руководстве: Как рассчитать стандартное отклонение в Excel.

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

Вас также может заинтересовать

.