Как посчитать эксцесс в excel

Обновлено: 08.07.2024

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

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

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

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

CV – коэффициент вариации;
σ – среднеквадратическое отклонение по выборке;
ǩ – среднеарифметическое значение разброса значений.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Функция ЭКСЦЕСС предназначена для анализа значений диапазона данных и возвращает значение коэффициента эксцесса.

Примеры использования функции ЭКСЦЕСС в Excel

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

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

Анализ соотношения цены и спроса по коэффициенту ЭКСЦЕСС в Excel

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

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

Для расчета эксцесса используем данные из столбца C. Вид формулы:

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

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

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

Анализ распределения чисел и статистических выбросов в Excel

Пример 2. На основе данных об успеваемости студентов определить, есть ли в группе студенты, сильно отстающие от процесса обучения.

Исходная таблица данных:

Для расчетов используем формулу:

B3:B13 – диапазон исследуемых значений (средних баллов студентов).

В результате получаем следующее значение:

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

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

Функция СКОС в Excel предназначена для определения коэффициента асимметрии для последовательности числовых данных и возвращает соответствующее числовое значение.

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

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

Из диапазона отрицательных чисел – отклонение в сторону отрицательных значений (отрицательные величины преобладают).
Из диапазона положительных чисел – отклонение в сторону положительных значений (преобладание положительных величин).
0 – асимметрия отсутствует (например, для последовательности 1, 2, 3, -1, -2, -3 асимметрический коэффициент равен нулю – 0).

Для определения коэффициента асимметрии используется уравнение:

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

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

Для решения используем следующую формулу:

С помощью функции ЕСЛИ выполняем проверку коэффициента симметрии («имеет ли второй ряд большее значение скоса?») и возвращаем соответствующее значение с пояснением.

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

Обе последовательности имеют отклонения в отрицательную сторону, но у ряда 1 это выражено в большей степени.

Коэффициент асимметрии и аппроксимация нормальным распределением в Excel

Пример 2. Имеем последовательность чисел. Необходимо проанализировать данную последовательность и сделать вывод о возможности аппроксимации нормальным распределением.

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

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

Определим значения асимметрии и эксцесса следующими функциями:

Отклонения от 0 значительны, поэтому аппроксимация невозможна. Чтобы автоматизировать подобные расчеты введем некоторые условия:

В данном случае принято допущение о том, что максимальное допустимое отклонение модулей асимметрии и эксцесса составляет 0,1

Коэффициент корреляции ( критерий корреляции Пирсона, англ. Pearson Product Moment correlation coefficient) определяет степень линейной взаимосвязи между случайными величинами.

где Е[…] – оператор математического ожидания , μ и σ – среднее случайной величины и ее стандартное отклонение .

Как следует из определения, для вычисления коэффициента корреляции требуется знать распределение случайных величин Х и Y. Если распределения неизвестны, то для оценки коэффициента корреляции используется выборочный коэффициент корреляции r ( еще он обозначается как R _xy или r _xy ) :

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

Рассчитать коэффициент корреляции и ковариацию выборки в MS EXCEL не представляет труда, так как для этого имеются специальные функции КОРРЕЛ() и КОВАР() . Гораздо сложнее разобраться, как интерпретировать полученные значения, большая часть статьи посвящена именно этому.

Теоретическое отступление

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

Примечание . Если случайную природу имеет только одна переменная, например, Y, а значения другой являются детерминированными (задаваемыми исследователем), то можно говорить только о регрессии.

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

Корреляционная связь между переменными может возникнуть несколькими путями:

Наличие причинной зависимости между переменными. Например, количество инвестиций в научные исследования (переменная Х) и количество полученных патентов (Y). Первая переменная выступает как независимая переменная (фактор) , вторая - зависимая переменная (результат) . Необходимо помнить, что зависимость величин обуславливает наличие корреляционной связи между ними, но не наоборот.
Наличие сопряженности (общей причины). Например, с ростом организации растет фонд оплаты труда (ФОТ) и затраты на аренду помещений. Очевидно, что неправильно предполагать, что аренда помещений зависит от ФОТ. Обе этих переменных во многих случаях линейно зависят от количества персонала.
Взаимовлияние переменных (при изменении одной, вторая переменная изменяется, и наоборот). При таком подходе допустимы две постановки задачи; любая переменная может выступать как в роли независимой переменной и в роли зависимой.

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

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

Корреляция близкая к 1 или -1 (т.е. близкая по модулю к 1) показывает сильную линейную взаимосвязь переменных, значение близкое к 0 показывает отсутствие взаимосвязи. Положительная корреляция означает, что с ростом одного показателя другой в среднем увеличивается, а при отрицательной – уменьшается.

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

количество переменных должно быть равно двум;
переменные должны быть количественными (например, частота, вес, цена). Вычисленное среднее значение этих переменных имеет понятный смысл: средняя цена или средний вес пациента. В отличие от количественных, качественные (номинальные) переменные принимают значения лишь из конечного набора категорий (например, пол или группа крови). Этим значениям условно сопоставлены числовые значения (например, женский пол – 1, а мужской – 2). Понятно, что в этом случае вычисление среднего значения , которое требуется для нахождения корреляции , некорректно, а значит некорректно и вычисление самой корреляции ;
переменные должны быть случайными величинами и иметь нормальное распределение.

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

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

Использование MS EXCEL для расчета корреляции

В качестве примера возьмем 2 переменные Х и Y и, соответственно, выборку состоящую из нескольких пар значений (Х _i ; Y _i ). Для наглядности построим диаграмму рассеяния .

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

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

Примечание : В файле примера можно задать параметры линейного тренда (наклон, пересечение с осью Y) и степень разброса относительно этой линии тренда. Также можно настроить параметры квадратичной зависимости.

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

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

Как было сказано выше, для расчета коэффициента корреляции в MS EXCEL существует функций КОРРЕЛ() . Также можно воспользоваться аналогичной функцией PEARSON() , которая возвращает тот же результат.

Для того, чтобы удостовериться, что вычисления корреляции производятся функцией КОРРЕЛ() по вышеуказанным формулам, в файле примера приведено вычисление корреляции с помощью более подробных формул:

Примечание : Квадрат коэффициента корреляции r равен коэффициенту детерминации R2, который вычисляется при построении линии регрессии с помощью функции КВПИРСОН() . Значение R2 также можно вывести на диаграмме рассеяния , построив линейный тренд с помощью стандартного функционала MS EXCEL (выделите диаграмму, выберите вкладку Макет , затем в группе Анализ нажмите кнопку Линия тренда и выберите Линейное приближение ). Подробнее о построении линии тренда см., например, в статье о методе наименьших квадратов .

Использование MS EXCEL для расчета ковариации

Ковариация близка по смыслу с дисперсией (также является мерой разброса) с тем отличием, что она определена для 2-х переменных, а дисперсия - для одной. Поэтому, cov(x;x)=VAR(x).

Для вычисления ковариации в MS EXCEL (начиная с версии 2010 года) используются функции КОВАРИАЦИЯ.Г() и КОВАРИАЦИЯ.В() . В первом случае формула для вычисления аналогична вышеуказанной (окончание .Г обозначает Генеральная совокупность ), во втором – вместо множителя 1/n используется 1/(n-1), т.е. окончание .В обозначает Выборка .

Примечание : Функция КОВАР() , которая присутствует в MS EXCEL более ранних версий, аналогична функции КОВАРИАЦИЯ.Г() .

Примечание : Функции КОРРЕЛ() и КОВАР() в английской версии представлены как CORREL и COVAR. Функции КОВАРИАЦИЯ.Г() и КОВАРИАЦИЯ.В() как COVARIANCE.P и COVARIANCE.S.

Дополнительные формулы для расчета ковариации :

Эти формулы используют свойство ковариации :

Если переменные x и y независимые, то их ковариация равна 0. Если переменные не являются независимыми, то дисперсия их суммы равна:

VAR(x+y)= VAR(x)+ VAR(y)+2COV(x;y)

А дисперсия их разности равна

VAR(x-y)= VAR(x)+ VAR(y)-2COV(x;y)

Оценка статистической значимости коэффициента корреляции

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

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

которая имеет распределение Стьюдента с n-2 степенями свободы.

Если вычисленное значение случайной величины |t _r | больше, чем критическое значение t _α,n-2 (α- заданный уровень значимости ), то нулевую гипотезу отклоняют (взаимосвязь величин является статистически значимой).

Надстройка Пакет анализа

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

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

Надстройка возвращает вычисленные значения корреляции и ковариации (для ковариации также вычисляются дисперсии обоих случайных величин).

Читайте также: