Light-electric.com

IT Журнал
2 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

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

Расчет показателей вариации в Excel

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

Проведение любого статанализа немыслимо без расчетов. И сегодня в рамках рубрики «Работаем в Excel» мы научимся рассчитывать показатели вариации. Теоретическая основа была рассмотрена ранее в ряде статей о вариации данных. Кстати, на этом указанная тема не закончилась, к выпуску планируются новые статьи – следите за рекламой! Однако сухая теория без инструментов реализации – вещь не сильно полезная. Поэтому по мере появления теоретических выкладок, я стараюсь не отставать с заметками о соответствующих расчетах в программе Excel.

Сегодняшняя публикация будет посвящена расчету в Excel следующих показателей вариации:

— максимальное и минимальное значение

— среднее линейное отклонение

— дисперсия (по генеральной совокупности и по выборке)

— среднее квадратическое отклонение (по генеральной совокупности и по выборке)

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

Максимальное и минимальное значение

Начнем с формул максимума и минимума. Что такое максимальное и минимальное значение, уверен, знают почти все. Максимум – самое большое значение из анализируемого набора данных, минимум – самое маленькое (может быть и отрицательным числом). Это крайние значения в совокупности данных, обозначающие границы их вариации. Примеры реального использования каждый может придумать сам – их полно. Это и минимальные/максимальные цены на что-нибудь, и выбор наилучшего или наихудшего решения задачи, и всего, чего угодно. Минимум и максимум – весьма информативные показатели. Давайте теперь их рассчитаем в Excel.

Как нетрудно догадаться, делается сие элементарно – как два клика об асфальт. В Мастере функций следует выбрать: МАКС – для расчета максимального значения, МИН – для расчета минимального значения. Для облегчения поиска перечень всех функций можно отфильтровать по категории «Статистические».

Выбираем нужную формулу, в следующем окошке указываем диапазон данных (в котором ищется максимальное или минимальное значение) и жмем «ОК».

Функции МАКС и МИН достаточно часто используются, поэтому разработчики Экселя предусмотрительно добавили соответствующие кнопки в ленту. Они находятся там же, где суммаи среднее значение – в разворачивающемся списке.

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

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

Среднее линейное отклонение, напоминаю, представляет собой среднее из абсолютных (по модулю) отклонений от средней арифметической в анализируемой совокупности данных. Математическая формула имеет вид:

a – среднее линейное отклонение,

x – анализируемый показатель, с черточкой сверху – среднее значение показателя,

n – количество значений в анализируемой совокупности данных.

В Excel эта функция называется СРОТКЛ.

После выбора функции СРОТКЛ указываем диапазон данных, по которому должен произойти расчет. Нажимаем «ОК». Наслаждаемся результатом.

Дисперсия

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

x – анализируемый показатель, с черточкой сверху – среднее значение показателя,

n – количество значений в анализируемой совокупности данных.

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

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

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

в Excel выборочная дисперсия рассчитывает через функцию ДИСП.В.

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

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

Среднеквадратическое отклонение по генеральной совокупности – это корень из генеральной дисперсии.

Выборочное среднеквадратическое отклонение – это корень из выборочной дисперсии.

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

— Среднеквадратическое отклонение по генеральной совокупности СТАНДОТКЛОН.Г

— Среднеквадратическое отклонение по выборке СТАНДОТКЛОН.В.

С названием этого показателя может возникнуть путаница, т.к. часто можно встретить синоним «стандартное отклонение». Пугаться не нужно – смысл тот же.

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

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

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

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

В скобках должен быть указан диапазон данных. При необходимости используется среднее квадратическое отклонение по выборке (СТАНДОТКЛОН.В).

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

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

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

Читать еще:  Графическая диаграмма в excel

В целом, с помощью Excel все, или почти все, статистические показатели рассчитываются очень просто. Если что-то непонятно, всегда можно воспользоваться окошком для поиска в Мастере функций. Ну, и Гугл в помощь.

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

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

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

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

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

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

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

Обычно данный коэффициент используют в таких целях как:

  • Для сравнения нескольких различных рядов данных или показателей;
  • Для оценки потенциальных объектов инвестирования;
  • Для проведения XYZ-анализа.

КЛЮЧЕВЫЕ МОМЕНТЫ

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

Формула CV

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

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

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

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

В Open Office данный показатель рассчитывается аналогично. Функция стандартного отклонения здесь — STDEV, а функция среднего значения — AVERAGE.

Давайте рассмотрим пример расчёта коэффициента вариации в Open Office. Предположим, что у нас есть три потенциальных объекта для инвестиций — объект А, объект Б и объект В. Прибыль по каждому из этих проектов за последние 6 лет занесена в таблицу представленную ниже:

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

Аналогичным образом рассчитаем среднее значение для каждого ряда данных:

Наконец рассчитаем CV. Для этого разделим полученные значения отклонений на средние значения. В результате получим следующую таблицу:

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

Пример использования коэффициента вариации для выбора объекта инвестиций

Рассмотрим инвестора не склонного к риску, который хочет инвестировать в биржевой фонд (ETF) состоящий из корзины ценных бумаг отслеживающей индекс широкого рынка. Инвестор выбирает SPDR S&P 500 ETF, Invesco QQQ ETF и iShares Russell 2000 ETF. Затем он анализирует доходность и волатильность выбранных ETF за последние 15 лет и предполагает, что в будущем они могут иметь аналогичную доходность в отношении к своим долгосрочным средним значениям.

Для принятия решения инвестором используется следующая 15-летняя историческая информация:

  • SPDR S&P 500 ETF имеет среднюю годовую доходность 5,47% и стандартное отклонение 14,68%. Коэффициент вариации SPDR S&P 500 ETF составляет 2,68;
  • Средняя годовая доходность Invesco QQQ ETF составляет 6,88%, а стандартное отклонение-21,31%. Коэффициент вариации QQQ равен 3,09;
  • iShares Russell 2000 ETF имеет среднюю годовую доходность 7,16% и стандартное отклонение 19,46%. Коэффициент вариации IWM равен 2,72.

Исходя из этих данных, инвестор может инвестировать либо в SPDR S&P 500 ETF, либо в iShares Russell 2000 ETF, так как соотношение риска и вознаграждения для них является сравнительно одинаковым. А для Invesco QQQ ETF соотношение риск-доходность, как видите, будет несколько хуже.

Понравилась статья? Сохраните ссылку на неё у себя в соцсетях:

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

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

Читать еще:  Как посчитать коэффициент вариации в excel

Дисперсия

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Читать еще:  Группировка ячеек в excel

Размещено на www.rnz.ru

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

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

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

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

где σ — среднее квадратическое отклонение;

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

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

Формула расчета точного значения коэффициента вариации

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

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

Как рассчитывается коэффициент вариации и как его проанализировать

Коэффициент вариации, VAR или CV, – ключевой показатель в оценке риска проектов и доходности ценных бумаг. Он позволяет заранее проанализировать сразу два показателя, которые обладают меняющимися во времени значениями. Если показатель оказывается менее 0,1, направление инвестирования характеризуется низким уровнем риска. При показателе свыше 0,3 уровень риска необоснованно высок. Для расчета удобнее всего использовать функции СТАНДОТКЛОН и СРЗНАЧ табличного редактора Excel.

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

Коэффициент вариации (Coefficient of variation – CV, VAR) – относительный финансовый показатель, который демонстрирует сравнение рассеивания значений двух случайных показателей, которые имеют разные единицы измерения относительно ожидаемого значения.

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

Coefficient of variation – показатель из числа относительных методов статистики, который, как NPV и IRR, применяется в рамках инвестиционного анализа. Он измеряется в процентах и может применяться для сравнения вариаций двух несвязанных между собой критериев. Его чаще всего используют финансовые и инвестиционные аналитики.

Справка! На базе коэффициента вариации оценивается так называемый «унифицированный риск» (unitized risk), поскольку он оценивает относительный разброс двух показателей по отношению к прогнозному значению.

Для чего используют показатель VAR:

  • в целях сравнения двух разных показателей;
  • для определения степени устойчивости прогнозных моделей (в основном по инвестициям и портфельному инвестированию);
  • для осуществления XYZ-анализа.

Справка! XYZ-анализ – аналитический инструмент, в рамках которого продукция компании оценивается по двум параметрам: стабильность потребления и продаж.

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

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

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

CV = σ / t ср, где:

CV – коэффициент вариации;

σ – среднее квадратическое отклонение;

t – среднее арифметическое значение для случайной величины.

Формула расчета показателя VAR может принимать самые разнообразные интерпретации в зависимости от объекта оценки.

Ссылка на основную публикацию
Adblock
detector