Разное

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

27.04.2021

Содержание

что это такое, как рассчитать?

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

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

Что представляет собой коэффициент вариации

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

Коэффициент вариации является в некотором роде сигналом, ведь он позволяет убрать в данных случайные факторы, а также «показывает», на что следует обратить внимание юзеру перед проведением расчетов. Таким образом, если коэффициент равен 0%, значит ряд абсолютно однородный, соответственно, все значения равны между собой. Если же он составляет больше 33%, то ряд, как вы уже догадались, не является однородным, значит, присутствует явный разброс данных относительно среднего значения. Это наглядно можно увидеть если построить график.

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

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

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

Как провести XYZ анализ товарных запасов в Excel

В целях оптимизации ассортимента компании проводится XYZ анализ товарных запасов. Методика применяется в совокупности с ABC-анализом и заключается в классификации товаров в зависимости от потребностей покупателей. На основании результатов расчетов прогнозируются закупки и продажи.

Как выполняется XYZ анализ ассортимента

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

  • А – наиболее ценные виды товаров (около 20 %) приносят организации большую часть продаж (около 80 %).
  • В – промежуточные товары (около 30 %) приносят около 15 % реализации.
  • С – наименее ценные товарные позиции (около 50 %) приносят 5 % реализации.
XYZ анализ позволяет группировать ресурсы по степени их нужности организации, то есть в зависимости от интенсивности и стабильности их потребления.

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

Общая формула расчета:

К вариации = Среднеквадратическое отклонение случайной величины / Среднее значение случайной величины.

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


где:

где Хi — величина объема продаж по отдельному товару за i-й период;

х — среднее значение объема продаж по анализируемой позиции;

n — количество заданных периодов.

При проведении расчетов К вариации определяется по каждой из анализируемой позиции отдельно. Затем оцениваются полученные отклонения продаж от среднего значения. После ассортимент ранжируется по группам – X, Y и Z. Каждая из полученных групп классифицируется так:

  • X – такие ресурсы характеризуются наиболее стабильным спросом, высоким уровнем потребления, а значит, значительными продажами и достоверными результатами прогнозирования. Компании не грозят убытки даже при закупке значительных партий этих товаров. На их долю приходится до 10 % от общих объемов ресурсов.
  • Y – это ресурсы, которые отличаются известными колебаниями в потреблении, но все равно остаются достаточно надежными для организации. В качестве причины колебания выступает, к примеру, сезонный спрос. Для точности прогнозов требуется углубленный анализ и контроль статистики продаж, оптимизация складских запасов. На долю данной группы приходится 11-25 % от общих объемов ресурсов.
  • Z – ресурсы последней группы имеют самый волатильный спрос, отличаются нерегулярными продажами и сложным прогнозированием. Такие позиции рекомендуется выводить из ассортимента или работать с ними по предзаказу. Коэффициент вариации имеет значение от 25 %.

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

Затем, на основании исходных данных, определяют наиболее популярные среди покупателей товары, наименее популярные и средние. Для этого по каждому наименованию рассчитывается К вариации. Номенклатура классифицируется по 3 основным группам:

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

Пример проведения XYZ анализа в Excel

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

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

Программы, представленные на нашем сайте позволяют автоматически провести XYZ анализ. Для анализа все данные за выбранный временный интервал импортируются в Excel-программу из 1С, путем выгрузки стандартного отчета “Анализ счета» в разрезе анализируемых позиций.

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


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

  • Товары группы X – те, которые максимально привлекают клиентов. Всегда должны быть в наличии (выделены желтым цветом на рисунке). Спрос на такие товары стабильный, он не имеет сезонных колебаний и относительно постоянный.
  • Товары группы Y – имеют стабильный спрос, который существенно подвержен сезонным колебаниям. 
  • Товары группы Z – отличаются случайным спросом. Компания ничего не потеряет, если откажется от их реализации. В крайнем случае можно работать с этими товарами по предоплате.

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

Коэффициент Джини Формула | Калькулятор (Примеры с шаблоном Excel)

Формула коэффициента Джини (Содержание)

  • формула
  • Примеры
  • Калькулятор

Что такое формула коэффициента Джини?

Термин «коэффициент Джини» нации относится к измерению распределения доходов по населению нации. Другими словами, его можно рассматривать как меру неравенства распределения доходов в обществе, и его значение лежит в диапазоне от 0 до 1, где значение 1 указывает на более высокую степень неравенства в доходах. Формула для коэффициента Джини включает вычисление совокупного балла, который представляет собой сложную функцию вклада дохода (доли дохода) для другой части населения (доли населения). Затем коэффициент Джини рассчитывается путем вычитания совокупного балла из 1. Математически формула коэффициента Джини представляется как

Gini Coefficient = 1 – Aggregate Score

Примеры формулы коэффициента Джини (с шаблоном Excel)

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

Вы можете скачать этот шаблон формулы формулы коэффициента Джини здесь - Шаблон коэффициента формулы Джини Excel
Коэффициент Джини - формула № 1

Давайте возьмем простой пример из 20 человек, чтобы понять концепцию коэффициента Джини. Согласно предоставленной информации, первые 5 человек получали 50 долларов в месяц на человека, следующие 10 человек получали 100 долларов в месяц на человека, а последние 5 человек получали 300 долларов в месяц на человека.

Рассчитайте коэффициент Джини для населения.

Решение:

Общий доход рассчитывается как

  • Общий доход = (5 * $ 50) + (10 * $ 100) + (5 * $ 300)
  • Общий доход = 2750 долларов

Доля дохода рассчитывается как

Первые 5 человек

  • Доля дохода = (5 * 50 долларов США) / 2750 долларов США
  • Доля дохода = 0, 09

Следующие 10 человек

  • Доля дохода = (10 * 100 долларов США) / 2750 долларов США
  • Доля дохода = 0, 36

Последние 5 человек

  • Доля дохода = (5 * 300 долл. США) / 2750 долл. США
  • Доля дохода = 0, 55

Доля населения рассчитывается как

Первые 5 человек

  • Доля населения = 5/20
  • Доля населения = 0, 25

Следующие 10 человек

  • Доля населения = 10/20
  • Доля населения = 0, 50

Последние 5 человек

  • Доля населения = 5/20
  • Доля населения = 0, 25

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

Первые 5 человек

  • Доля более богатого населения = 1 - 0, 25
  • Доля более богатого населения = 0, 75

Следующие 10 человек

  • Доля более богатого населения = 1 - 0, 25 - 0, 50
  • Доля более богатого населения = 0, 25

Последние 5 человек

  • Доля более богатого населения = 1 - 0, 25 - 0, 50 - 0, 25
  • Доля более богатого населения = 0, 00

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

Оценка = доля дохода * (доля населения + 2 * доля более богатого населения)

Первые 5 человек

  • Оценка = 0, 09 * (0, 25 + 2 * 0, 75)
  • Оценка = 0, 159

Следующие 10 человек

  • Оценка = 0, 36 * (0, 50 + 2 * 0, 25)
  • Оценка = 0, 364

Последние 5 человек

  • Оценка = 0, 55 * (0, 25 + 2 * 0, 00)
  • Оценка = 0, 136

Совокупный балл рассчитывается как

  • Совокупный балл = 0, 159 + 0, 364 + 0, 136
  • Совокупный балл = 0, 659

Коэффициент Джини рассчитывается по приведенной ниже формуле

Коэффициент Джини = 1 - Совокупный балл

  • Коэффициент Джини = 1 - 0, 659
  • Коэффициент Джини = 0, 341

Таким образом, коэффициент Джини населения составляет 0, 341.

Формула коэффициента Джини - пример № 2

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

Решение:

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

Группа 1

  • Доля более богатого населения = 1 - 0, 30
  • Доля более богатого населения = 0, 70

Группа 2

  • Доля более богатого населения = 1 - 0, 30 - 0, 60
  • Доля более богатого населения = 0, 10

Группа 3

  • Доля более богатого населения = 1 - 0, 30 - 0, 60 - 0, 10
  • Доля более богатого населения = 0, 00

Для страны X

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

Оценка = доля дохода * (доля населения + 2 * доля более богатого населения)

Группа 1

  • Оценка = 0, 03 * (0, 30 + 2 * 0, 70)
  • Оценка = 0, 051

Группа 2

  • Оценка = 0, 45 * (0, 60 + 2 * 0, 10)
  • Оценка = 0, 360

Группа 3

  • Оценка = 0, 52 * (0, 10 + 2 * 0, 00)
  • Оценка = 0, 052

Совокупный балл рассчитывается как

  • Совокупный балл = 0, 051 + 0, 360 + 0, 052
  • Совокупный балл = 0, 463

Коэффициент Джини рассчитывается по приведенной ниже формуле

Коэффициент Джини = 1 - Совокупный балл

  • Коэффициент Джини = 1 - 0, 463
  • Коэффициент Джини = 0, 537

Для страны Y

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

Оценка = доля дохода * (доля населения + 2 * доля более богатого населения)

Группа 1

  • Оценка = 0, 08 * (0, 30 + 2 * 0, 70)
  • Оценка = 0, 136

Группа 2

  • Оценка = 0, 37 * (0, 60 + 2 * 0, 10)
  • Оценка = 0, 296

Группа 3

  • Оценка = 0, 55 * (0, 10 + 2 * 0, 00)
  • Оценка = 0, 055

Совокупный балл рассчитывается как

  • Совокупный балл = 0, 136 + 0, 296 + 0, 055
  • Совокупный балл = 0, 487

Коэффициент Джини рассчитывается по приведенной ниже формуле

Коэффициент Джини = 1 - Совокупный балл

  • Коэффициент Джини = 1 - 0, 487
  • Коэффициент Джини = 0, 513

Таким образом, можно видеть, что Страна Y имеет более низкое неравенство в доходах, чем Страна X.

объяснение

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

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

Шаг 2: Затем сгруппируйте население в разные сегменты в зависимости от уровня дохода.

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

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

Шаг 5: Затем вычислите долю более богатого населения для каждой группы, которая равна единице минус совокупная доля населения (от группы с низким доходом к группе с более высоким доходом).

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

Оценка = доля дохода * (доля населения + 2 * доля более богатого населения)

Шаг 7: Затем суммируйте баллы каждой группы, чтобы получить совокупный балл.

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

Коэффициент Джини = 1 - Совокупный балл

Актуальность и использование формулы коэффициента Джини

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

Коэффициент формулы Джини

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

Совокупный балл
Коэффициент Джини

Коэффициент Джини = 1 - Совокупный балл
знак равно1 - 0 = 0

Рекомендуемые статьи

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

  1. Формула коэффициента корреляции | Определение | Примеры
  2. Формула для расчета коэффициента вариации
  3. Примеры формулы простого запаса (шаблон Excel)
  4. Что такое коэффициент определения формулы?

Методические указания по выполнению практических и лабораторных работ по статистике содержат требования по их выполнению, порядок расчетов вручную и с использованием ms excel, ппп statistica. (стр. 1 из 4)

ВВЕДЕНИЕ

Методические указания по выполнению практических и лабораторных работ по статистике содержат требования по их выполнению, порядок расчетов вручную и с использованием MS Excel, ППП Statistica.

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

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

1. ПРАКТИЧЕСКАЯ РАБОТА №3

РАСЧЕТ ПОКАЗАТЕЛЕЙ ВАРИАЦИИ

Цель работы: получение практических навыков в расчете различных показателей (меры) вариации в зависимости от поставленных исследованием задач.

Порядок выполнения работы:

1. Определить вид и форму (простая или взвешенная) показателей вариации.

2. Рассчитать показатели степени вариации для сгруппированных и несгруппированных данных и показатели формы распределения.

3. Сформулировать выводы.

Пример расчета показателей вариации

1. Определение вида и формы показателей вариации.

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

Размах вариации (R) является наиболее простым измерителем вариации признака и определяется по следующей формуле:

, (1)

где

– наибольшее значение варьирующего признака; – наименьшее значение варьирующего признака.

Квартильное отклонение (Q) – применяется для характеристики вариации признака в совокупности. Может использоваться вместо размаха вариации во избежание недостатков, связанных с использованием крайних значений.

, (2)

где

и – соответственно первая и третья квартили распределения.

Квартили – это значения признака в ранжированном ряду распределения, выбранные таким образом, что 25% единиц совокупности будут меньше по величине

; 25% единиц будут заключены между и ; 25% единиц будут заключены между и , и остальные 25% превосходят .

Квартили определяются по формулам:

, (3) где – нижняя граница интервала, в котором находится первая квартиль; – сумма накопленных частот интервалов, предшествующих интервалу, в котором находится первая квартиль; – частота интервала, в котором находится первая квартиль. , (4)

где Ме – медиана ряда;

, (5) условные обозначения те же, что и для величины .

В симметричных или умеренно асимметричных распределениях Q»2/3s. Так как на квартильное отклонение не влияют отклонения всех значений признака, то его использование следует ограничить случаями, когда определение среднего квадратического отклонения затруднительно или невозможно.

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

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

Дисперсия (

) – средний квадрат отклонений индивидуальных значений признака от их средней величины. Дисперсия вычисляется по формулам простой невзвешенной и взвешенной. (8) - невзвешенная, (9) - взвешенная.

Среднее квадратическое отклонение (s) – наиболее распространенный показатель вариации, представляет собой квадратный корень из значения дисперсии.

(10)

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

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

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

, (11)

Относительное линейное отклонение (линейный коэффициент вариации):

, (12)

Относительный показатель квартильной вариации:

(13) или (14)

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

, (15)

Наиболее часто применяемый в статистике показатель относительной колеблемости – коэффициент вариации. Его используют не только для сравнительной оценки вариации, но и как характеристику однородности совокупности. Совокупность считается однородной, если коэффициент вариации не превышает 33% (Ефимова М.Р., Рябцев В.М. Общая теория статистики: Учебник М.: Финансы и статистика, 1991 г. , стр. 105).

Для получения приблизительного представления о форме распределения строят графики распределения (полигон и гистограмму).

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

, тем больше асимметрия ряда.

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

. (16)

Величина показателя As может быть положительной и отрицательной. Положительная величина показателя указывает на наличие правосторонней асимметрии (правая ветвь относительно максимальной ординаты вытянута больше, чем левая). При правосторонней асимметрии между показателями центра распределения существует соотношение:

. Отрицательный знак показателя асимметрии свидетельствует о наличии левосторонней асимметрии (Рисунок 1). Между показателями центра распределения в этом случае имеется такое соотношение: .

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

Добавил:

Upload Опубликованный материал нарушает ваши авторские права? Сообщите нам.

Вуз:

Предмет:

Файл:

6. показатели вариации.doc

Скачиваний:

199

Добавлен:

Размер:

246.78 Кб

Скачать

Блок: 1/2 | Кол-во символов: 587
Источник: https://StudFiles.net/preview/5316293/page:3/

Калькулятор

Сервис бесплатной оценки стоимости работы

  1. Заполните заявку. Специалисты рассчитают стоимость вашей работы
  2. Расчет стоимости придет на почту и по СМС

Номер вашей заявки

Прямо сейчас на почту придет автоматическое письмо-подтверждение с информацией о заявке.

Оформить еще одну заявку

Блок: 2/2 | Кол-во символов: 308
Источник: https://StudFiles.net/preview/5316293/page:3/

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

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

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

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

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

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

  1. Для того, чтобы рассчитать стандартное отклонение, выделяем любую свободную ячейку на листе, которая удобна вам для того, чтобы выводить в неё результаты расчетов. Щелкаем по кнопке «Вставить функцию». Она имеет внешний вид пиктограммы и расположена слева от строки формул.
  2. Выполняется активация Мастера функций, который запускается в виде отдельного окна с перечнем аргументов. Переходим в категорию «Статистические» или «Полный алфавитный перечень». Выбираем наименование «СТАНДОТКЛОН.Г» или «СТАНДОТКЛОН.В», в зависимости от того, по генеральной совокупности или по выборке следует произвести расчет. Жмем на кнопку «OK».
  3. Открывается окно аргументов данной функции. Оно может иметь от 1 до 255 полей, в которых могут содержаться, как конкретные числа, так и ссылки на ячейки или диапазоны. Ставим курсор в поле «Число1». Мышью выделяем на листе тот диапазон значений, который нужно обработать. Если таких областей несколько и они не смежные между собой, то координаты следующей указываем в поле «Число2» и т.д. Когда все нужные данные введены, жмем на кнопку «OK»
  4. В предварительно выделенной ячейке отображается итог расчета выбранного вида стандартного отклонения.

Урок: Формула среднего квадратичного отклонения в Excel

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

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

  1. Выделяем на листе ячейку для вывода результата. Жмем на уже знакомую нам кнопку «Вставить функцию».
  2. В статистической категории Мастера функций ищем наименование «СРЗНАЧ». После его выделения жмем на кнопку «OK».
  3. Запускается окно аргументов СРЗНАЧ. Аргументы полностью идентичны тем, что и у операторов группы СТАНДОТКЛОН. То есть, в их качестве могут выступать как отдельные числовые величины, так и ссылки. Устанавливаем курсор в поле «Число1». Так же, как и в предыдущем случае, выделяем на листе нужную нам совокупность ячеек. После того, как их координаты были занесены в поле окна аргументов, жмем на кнопку «OK».
  4. Результат вычисления среднего арифметического выводится в ту ячейку, которая была выделена перед открытием Мастера функций.

Урок: Как посчитать среднее значение в Excel

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

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

  1. Выделяем ячейку, в которую будет выводиться результат. Прежде всего, нужно учесть, что коэффициент вариации является процентным значением. В связи с этим следует поменять формат ячейки на соответствующий. Это можно сделать после её выделения, находясь во вкладке «Главная». Кликаем по полю формата на ленте в блоке инструментов «Число». Из раскрывшегося списка вариантов выбираем «Процентный». После этих действий формат у элемента будет соответствующий.
  2. Снова возвращаемся к ячейке для вывода результата. Активируем её двойным щелчком левой кнопки мыши. Ставим в ней знак «=». Выделяем элемент, в котором расположен итог вычисления стандартного отклонения. Кликаем по кнопке «разделить» (/) на клавиатуре. Далее выделяем ячейку, в которой располагается среднее арифметическое заданного числового ряда. Для того, чтобы произвести расчет и вывести значение, щёлкаем по кнопке Enter на клавиатуре.
  3. Как видим, результат расчета выведен на экран.

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

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

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

  2. После этого, чтобы рассчитать значение и показать результат на экране монитора, щелкаем по кнопке Enter.

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

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

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

Опишите, что у вас не получилось. Наши специалисты постараются ответить максимально быстро.

Помогла ли вам эта статья?

ДА НЕТ

Блок: 2/2 | Кол-во символов: 5976
Источник: http://lumpics.ru/how-calculate-coefficient-of-variation-in-excel/

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

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

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

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

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

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

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

Блок: 3/3 | Кол-во символов: 1205
Источник: https://exceltable.com/otchety/koefficient-variacii

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

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

где

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

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

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

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

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

Блок: 3/6 | Кол-во символов: 581
Источник: http://vniioh.ru/raschet-pokazatelej-variacii-v-excel/

Дисперсия

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

где

D – дисперсия,

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

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

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

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

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

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

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

Блок: 4/6 | Кол-во символов: 1003
Источник: http://vniioh.ru/raschet-pokazatelej-variacii-v-excel/

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

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

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

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

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

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

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

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

Блок: 5/6 | Кол-во символов: 839
Источник: http://vniioh.ru/raschet-pokazatelej-variacii-v-excel/

Кол-во блоков: 7 | Общее кол-во символов: 10499
Количество использованных доноров: 4
Информация по каждому донору:
  1. http://lumpics.ru/how-calculate-coefficient-of-variation-in-excel/: использовано 1 блоков из 2, кол-во символов 5976 (57%)
  2. https://exceltable.com/otchety/koefficient-variacii: использовано 1 блоков из 3, кол-во символов 1205 (11%)
  3. https://StudFiles.net/preview/5316293/page:3/: использовано 2 блоков из 2, кол-во символов 895 (9%)
  4. http://vniioh.ru/raschet-pokazatelej-variacii-v-excel/: использовано 3 блоков из 6, кол-во символов 2423 (23%)

Дисперсия и стандартное отклонение в EXCEL. Примеры и описание

Вычислим в MS EXCEL дисперсию и стандартное отклонение выборки. 2)/ (СЧЁТ(Выборка)-1 ) – формула массива

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

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

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


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

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

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

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

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

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

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

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

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

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

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

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

Var(aХ)=a 2 Var(X)

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

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

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

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

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

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

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

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

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

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

Вычислим стандартное отклонение для 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

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

1

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

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

23

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

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

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

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

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

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

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

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

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

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

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

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

4

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

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

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

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

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

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

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

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

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

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

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

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

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

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

5

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

6

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

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

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

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

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

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

7

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

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

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

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

8

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

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

9

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

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

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

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

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

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

10

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

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

11

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

RDE4 Стандартное отклонение населения (стандартное отклонение ST
Статистика 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 представляет генеральную совокупность, дисперсия генеральной совокупности (символ σ 2 ) вычисляется из среднего генерального значения µ следующим образом:

Где S представляет собой выборку дисперсию выборки (символ s 2 ) вычисляется из выборочного среднего 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) 2 + (5–2,5) 2 + (-1–2,5) 2 + (3–2,5) 2 + (4–2,5) 2 + (5–2,5) 2 + (0–2,5) 2 + (2–2,5) 2 = 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 1 , x 2 ,…, x n }, то дисперсия выборки может быть выражено как

Свойство 2 : Если µ - среднее значение генеральной совокупности S = { x 1 , x 2 ,…, 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 1 , x 2 ,…, x n } имеет среднее значение µ x и стандартное отклонение σ x , а совокупность {y 1 , y 2 ,…, y m } имеет среднее значение µ y и стандартное отклонение σ y , тогда дисперсия объединенной совокупности составляет

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

Свойство 4 : Если образец { x 1 , x 2 ,…, x n } имеет среднее значение x и стандартное отклонение s x , а образец {y 1 , 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 и имеют одинаковые единицы измерения, 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. Во-первых, возводя числа в квадрат, мы всегда получаем неотрицательные вычисления. Не углубляясь в математику этого вопроса, интуитивно понятно, что дисперсия не может быть отрицательной.Дисперсия зависит от расстояния, и расстояние не может быть отрицательным .

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

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

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

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

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

Затем мы применяем формулу, которую мы только что обсудили: ((1 - 3) 2 + (2 - 3) 2 + (3 - 3) 2 + (4 - 3) 2 + (5 - 3) 2 ) / 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 ресторанов? Конечно, нет. Очевидно, это образец, взятый из всех ресторанов города.Затем мы должны использовать формулы для выборки мер изменчивости .

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

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

  1. Соответствующий образец стандартных отклонений составляет 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. Благодарю вас за чтение и надеюсь увидеть вас в нашем блоге на следующей неделе!

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

.

Добавить комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *