Как посчитать размах в excel

Расчет коэффициента вариации в Microsoft Excel

Одним из основных статистических показателей последовательности чисел является коэффициент вариации. Для его нахождения производятся довольно сложные расчеты. Инструменты Microsoft Excel позволяют значительно облегчить их для пользователя.

Вычисление коэффициента вариации

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

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

Шаг 1: расчет стандартного отклонения

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

Синтаксис данных функций выглядит соответствующим образом:

= СТАНДОТКЛОН(Число1;Число2;…)
= СТАНДОТКЛОН.Г(Число1;Число2;…)
= СТАНДОТКЛОН.В(Число1;Число2;…)

Шаг 2: расчет среднего арифметического

Среднее арифметическое является отношением общей суммы всех значений числового ряда к их количеству. Для расчета этого показателя тоже существует отдельная функция – СРЗНАЧ. Вычислим её значение на конкретном примере.

Шаг 3: нахождение коэффициента вариации

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

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

Вместо наименования «Диапазон значений» вставляем реальные координаты области, в которой размещен исследуемый числовой ряд. Это можно сделать простым выделением данного диапазона. Вместо оператора СТАНДОТКЛОН.В, если пользователь считает нужным, можно применять функцию СТАНДОТКЛОН.Г.

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

Как видим, программа Эксель позволяет значительно упростить расчет такого сложного статистического вычисления, как поиск коэффициента вариации. К сожалению, в приложении пока не существует функции, которая высчитывала бы этот показатель в одно действие, но при помощи операторов СТАНДОТКЛОН и СРЗНАЧ эта задача очень упрощается. Таким образом, в Excel её может выполнить даже человек, который не имеет высокого уровня знаний связанных со статистическими закономерностями.

Помимо этой статьи, на сайте еще 12540 инструкций.
Добавьте сайт Lumpics.ru в закладки (CTRL+D) и мы точно еще пригодимся вам.

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

Источник

Коэффициент вариации: формула и расчет в Excel и интерпретация результатов

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

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

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

Коэффициент вариации представляет собой отношение среднеквадратического отклонения к среднему арифметическому. Для расчета в статистике используется следующая формула:

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

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

В Excel не существует встроенной функции для расчета коэффициента вариации. Но можно найти частное от стандартного отклонения и среднего арифметического значения. Рассмотрим на примере.

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

Наглядно это можно продемонстрировать на графике:

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

Значение коэффициента для компании А – 33%, что свидетельствует об относительной однородности ряда. Формула расчета коэффициента вариации в Excel:

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

Интерпретация результатов

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

Ожидаемая доходность ценных бумаг составит:

Среднеквадратическое отклонение доходности для активов компании А и В составляет:

Ценные бумаги компании В имеют более высокую ожидаемую доходность. Они превышают ожидаемую доходность компании А в 1,14 раза. Но и инвестировать в активы предприятия В рискованнее. Риск выше в 1,7 раза. Как сопоставить акции с разной ожидаемой доходностью и различным уровнем риска?

Для сопоставления активов двух компаний рассчитан коэффициент вариации доходности. Показатель для предприятия В – 50%, для предприятия А – 33%. Риск инвестирования в ценные бумаги фирмы В выше в 1,54 раза (50% / 33%). Это означает, что акции компании А имеют лучшее соотношение риск / доходность. Следовательно, предпочтительнее вложить средства именно в них.

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

Источник

Дисперсия, среднеквадратичное (стандартное) отклонение, коэффициент вариации в Excel

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

Дисперсия

Дисперсия случайной величины – это один из основных показателей в статистике. Он отражает меру разброса данных вокруг средней арифметической.

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

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

То есть дисперсия — это математическое ожидание отклонений от математического ожидания.

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

s 2 – выборочная дисперсия, рассчитанная по данным наблюдений,

X – отдельные значения,

X̅– среднее арифметическое по выборке.

Стоит отметить, что у такого расчета дисперсии есть недостаток – она получается смещенной, т.е. ее математическое ожидание не равно истинному значению дисперсии. Подробней об этом здесь. Однако при увеличении объема выборки она все-таки приближается к своему теоретическому аналогу, т.е. является асимптотически не смещенной.

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

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

Генеральную и выборочную дисперсии легко рассчитать в Excel. Есть специальные функции: ДИСП.Г и ДИСП.В соответственно.

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

Свойства дисперсии

Свойство 1. Дисперсия постоянной величины A равна 0 (нулю).

Свойство 2. Если случайную величину умножить на постоянную А, то дисперсия этой случайной величины увеличится в А 2 раз. Другими словами, постоянный множитель можно вынести за знак дисперсии, возведя его в квадрат.

Свойство 3. Если к случайной величине добавить (или отнять) постоянную А, то дисперсия останется неизменной.

Свойство 4. Если случайные величины X и Y независимы, то дисперсия их суммы равна сумме их дисперсий.

Свойство 5. Если случайные величины X и Y независимы, то дисперсия их разницы также равна сумме дисперсий.

Среднеквадратичное (стандартное) отклонение

Если из дисперсии извлечь квадратный корень, получится среднеквадратичное (стандартное) отклонение (сокращенно СКО). Встречается название среднее квадратичное отклонение и сигма (от названия греческой буквы). Общая формула стандартного отклонения в математике следующая:

На практике формула стандартного отклонения следующая:

Как и с дисперсией, есть и немного другой вариант расчета. Но с ростом выборки разница исчезает.

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

Для расчета стандартного отклонения достаточно из дисперсии извлечь квадратный корень. Но в Excel есть и готовые функции: СТАНДОТКЛОН.Г и СТАНДОТКЛОН.В (по генеральной и выборочной совокупности соответственно).

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

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

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

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

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

Расчет коэффициента вариации в Excel также производится делением стандартного отклонения на среднее арифметическое:

Коэффициент вариации обычно выражается в процентах, поэтому ячейке с формулой можно присвоить процентный формат:

Коэффициент осцилляции

Еще один показатель разброса данных на сегодня – коэффициент осцилляции. Это соотношение размаха вариации (разницы между максимальным и минимальным значением) к средней. Готовой формулы Excel нет, поэтому придется скомпоновать три функции: МАКС, МИН, СРЗНАЧ.

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

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

Ниже видео о том, как посчитать коэффициент вариации, дисперсию, стандартное (среднеквадратичное) отклонение и другие показатели вариации в Excel.

Источник

Квантили распределений EXCEL

Медиана и квартили

Квантили нормального распределения

Математическое описание

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

(2) E(X) = Σ(p_i•X_i) E – от английского слова Expected (ожидание)
Для математического ожидания справедливы равенства:
(3) E(X + Y) = E(X) + E(Y)
(4) E(X•Y) = E(X) • E(Y)

Центральный момент степени k:

Среднее значение

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

Чтобы найти среднее значение всех результатов необходимо сложить всё вместе и разделить на количество результатов:

μ = (114 • 0 + 115 • 1 + 52 • 2 + 52 • 3 + 24 • 4 + 13 • 5 + 30 • 6) / 400 = 716/400 = 1.79

То же самое мы можем проделать используя формулу 2:

μ = M(X) = Σ(X_i•p_i) = 0 • 0.29 + 1 • 0.29 + 2 • 0.13 + 3 • 0.13 + 4 • 0.06 + 5 • 0.03 + 6 • 0.08 = 1.79 Момент первой степени, формула (5)

Собственно, формула 2 представляет собой среднее арифметическое всех значений
Итог: в среднем, 1.79 посетителя в час

Отклонение от среднего

Посмотрите на это распределение, можно предположить, что в среднем случайная величина равна 100±5, поскольку кажется, что таких значений несравнимо больше чем тех, что меньше 95 или больше 105:

График 2. График функции вероятности. Распределение ≈ 100±5

Среднее значение по формуле (2): μ = 99.95, но как посчитать, насколько далеко все значения находятся от среднего? Вам должна быть знакома запись 100±5. Что бы получить это значение ±, нам необходимо определить диапазон значений вокруг среднего. И мы могли бы использовать в качестве меры удалённости “разность” между средним и случайными величинами:

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

Соответственно, среднее значение удалённости – это математическое ожидание квадратов удалённости:

(9) σ 2 = E[(X – E(X)) 2 ] Поскольку вероятности любой удалённости равносильны – вероятность каждого из них – 1/n, откуда: (10) σ 2 = E[(X – E(X)) 2 ] = ∑[(X_i – μ) 2 ]/n Она же формула центрального момента (6) второй степени

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

Возвращаясь к примеру, посчитаем среднеквадратическое отклонение для графика 2:

Квантиль

График 3. Функция распределения. Медиана

График 4. Функция распределения. 4-квантиль или квартиль

График 5. Функция распределения. 0.34-квантиль

Для анализа функции распределения ввели понятие квантиль. Квантиль – это случайная величина при заданном уровне вероятности, т.е.: квантиль для уровня вероятности 50% – это случайная величина на графике плотности вероятности, которая имеет вероятность 50%. На примере с графиком 3, квантиль уровня 0.5 = 99 (ближайшее значение, поскольку распределение дискретно и события со значением 99.3 просто не существует)

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

Также, используется обозначение x-квантиль, где х – дробное число, например, 0.34-квантиль, такая запись означает значение случайной величины при p = 0.34.

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

Построение интервалов

Квантили используют для построения доверительных интервалов, которые необходимы для исследования статистики не одного конкретного события (например, интерес – случайное число = 98), а для группы событий (например, интерес – случайное число между 96 и 99). Доверительный интервал бывает двух видов: односторонний и двусторонний. Параметр доверительного интервала – уровень доверия. Уровень доверия означает процент событий, которые можно считать успешными.

Двусторонний доверительный интервал

Двусторонний доверительный интервал строится следующим образом: мы задаёмся уровнем значимости, например, 10%, и выделяем область на графике так, что 90% всех событий попадут в эту область. Поскольку интервал двусторонний, то мы отсекаем по 5% с каждой стороны, т.е. мы ищем 5й перцентиль, 95й перцентиль и значения случайной величины между ними будут являться доверительной областью, значения за пределами доверительной области называются “критическая область“

Первый квартиль

Значение квартиля Q1 находится в интервале 68,98 – 71,70, соответствующего частоте fQ1 = 150:4 = 37,5

Третий квартиль

Значение квартиля находится в интервале 68,98 – 71,70, соответствующего частоте fQ3 = (3*150):4 = 112,5

Квартили непрерывного распределения

Если функция распределения F (х) случайной величины х непрерывна, то 1-й квартиль является решением уравнения F(х) =0,25, второй – F(х) =0,5, а третий F(х) =0,75.

Например, решив аналитическим способом это уравнение для Логнормального распределения lnN(μ; σ 2 ), получим, что медиана (2-й квартиль ) вычисляется по формуле e μ или в MS EXCEL =EXP(μ). При μ=1, медиана равна 2,718.

Примечание : Напомним, что интеграл от функции плотности вероятности по всей области задания случайной величины равен единице:

Поэтому, линии квартилей ( х=квартиль ) делят площадь под графиком функции плотности вероятности на 4 равные части.

Квартили в MS EXCEL

Моменты случайной величины

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

#1 — первый момент случайной величины — среднее значение данных, которое показывает место распределения.

#2 — второй момент случайной величины — дисперсия, которая показывает разброс распределения. Большие значения имеют больший размах, чем маленькие.

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

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

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

Статистический анализ роста доли дохода в Excel за период

Пример 2. В таблице приведены данные о доходах предпринимателя за год. Доказать, что примерно 75% значений меньше, чем третий квартиль доходов.

Вид исходной таблицы:

Определим 3-й по формуле:

Определим соотношение чисел, меньше полученного числа, к общему количеству значений по формуле:

=СЧЁТЕСЛИ(B2:B13;” Пример 3. Имеется диапазон случайных чисел, отсортированный в порядке возрастания. Определить соотношение суммы чисел, которые меньше 1-го квартиля, к сумме чисел, которые превышают значение 1-го квартиля.

Чтобы сгенерировать случайное число в Excel воспользуемся функцией:

После генерации отсортируем случайно сгенерированные числа по возрастанию. Вид исходной таблицы данных со случайными числами:

Формула для расчета имеет следующий вид (формула массива CTRL+SHIFT+ENTER):

Функции СУММ с вложенными функциями ЕСЛИ выполняют расчет суммы только тех чисел, которые меньше и больше соответственно значения, возвращаемого функцией для исследуемого диапазона. Из полученных значений вычисляется частное. Результат расчетов:

Общая сумма чисел исследуемого диапазона, которые меньше 1-го квартиля, составляет всего 8,57% от общей суммы чисел, которые больше 1-го квартиля.

Расчет квартилей в R и SAS

Функция quantile в R использует все девять алгоритмов расчета квантилей, в соответствии с нумерацией, предложенной Hyndman and Fan в работе 1996 г. (рис. 15; если вы не знакомы с R, рекомендую начать с Алексей Шипунов. Наглядная статистика. Используем R! ). Квантиль при i-м методе расчета:

Расчет децилей для дискретного ряда

Определяем номер дециля по формуле: ,

Если номер дециля – целое число, то значение дециля будет равно величине элемента ряда, которое обладает накопленной частотой равной номеру дециля. Например, если номер дециля равен 20, его значение будет равно значению признака с S =20 (накопленной частотой равной 20).

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

Например, если номер дециля равна 20,25, дециль попадает между 20-м и 21-м наблюдениями, и его значение будет равно значению 20-го наблюдения плюс 1/4 разности между значением 20-го и 21-го наблюдений.

Квантили специальных видов

Часто используются Квантили специальных видов:

В качестве примера вычислим медиану (0,5-квантиль) логнормального распределения LnN(0;1) (см. файл примера лист Медиана ).

Это можно сделать с помощью формулы =ЛОГНОРМ.ОБР(0,5; 0; 1)

Квантили стандартного нормального распределения

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

В данных задачах часто используется специальная терминология:

Однако, при проверке гипотез и построении доверительных интервалов чаще используется “верхний” α-квантиль. Покажем почему.

Верхним α – квантилем называют такое значение x _α , для которого вероятность, того что случайная величина X примет значение больше или равное x _α равна альфа: P(X>= x _α )= α . Из определения понятно, что верхний альфа – квантиль любого распределения равен нижнему (1- α) – квантилю. А для распределений, у которых функция плотности распределения является четной функцией, верхний α – квантиль равен нижнему α – квантилю со знаком минус . Это следует из свойства четной функции f(-x)=f(x), в силу симметричности ее относительно оси ординат.

Действительно, для α=0,05, верхний 0,05-квантиль стандартного нормального распределения равен 1,645. Т.к. функция плотности вероятности стандартного нормального распределения является четной функцией, то вычисления в MS EXCEL верхнего квантиля можно сделать по двум формулам:

Чтобы пояснить название « верхний» квантиль , построим график плотности вероятности и функцию вероятности стандартного нормального распределения для α=0,05.

Вычислить двусторонний 0,05 – квантиль это можно с помощью формул MS EXCEL: =НОРМ.СТ.ОБР(1-0,05/2) или =-НОРМ.СТ.ОБР(0,05/2)

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

Квантили распределения Стьюдента

Чтобы вычислить в MS EXCEL верхний 0,05/2 – квантиль для t-распределения с 10 степенями свободы (или тоже самое двусторонний 0,05-квантиль ), необходимо записать формулу =СТЬЮДЕНТ.ОБР.2Х(0,05; 10) или =СТЬЮДРАСПОБР(0,05; 10) или =СТЬЮДЕНТ.ОБР(1-0,05/2; 10) или =-СТЬЮДЕНТ.ОБР(0,05/2; 10)

Квантили распределения ХИ-квадрат

Вычислять квантили распределения ХИ-квадрат с n -1 степенью свободы требуется, если проводится проверка гипотезы о дисперсии нормального распределения (см. статью Проверка статистических гипотез в MS EXCEL о дисперсии нормального распределения ).

Чтобы вычислить верхний 0,05/2 – квантиль для ХИ 2 -распределения с числом степеней свободы 10, т.е. χ 2 _0,05/2,n-1 , необходимо в MS EXCEL записать формулу =ХИ2.ОБР.ПХ(0,05/2; 10) или =ХИ2.ОБР(1-0,05/2; 10)

Источник