Децильный коэффициент как рассчитать в экселе
Обновлено: 02.07.2024
Имеются данные о распределении населения России по среднедушевому денежному доходу за 2015 год.
| Среднедушевой денежный доход в месяц, руб. | Население в % к итогу | Накопленные частоты |
|---|---|---|
| До 5000 | 2,4 | 2,4 |
| 5000 – 7000 | 3,8 | 6,2 |
| 7000 – 10000 | 8,0 | 14,2 |
| 10000 – 14000 | 12,1 | 26,3 |
| 14000 – 19000 | 14,4 | 40,7 |
| 19000 – 27000 | 18,2 | 58,9 |
| 27000 – 45000 | 22,5 | 81,4 |
| свыше 45000 | 18,6 | 100 |
| Итого | 100 | - |
Рассчитайте:
1. Структурные средние: медиану, первый и третий квартили, первый и девятый децили.
2. Децильный коэффициент дифференциации доходов населения.
Решение:
1. Структурные средние.
1) Медиана – это варианта, расположенная в середине ранжированного ряда. Медиана интервального вариационного ряда распределения определяется по формуле:
– начальное значение интервала, содержащего медиану;
– величина медианного интервала;
– сумма накопленных частот, предшествующих медианному интервалу;
– частота медианного интервала.
Определим, прежде всего, медианный интервал. Сумма накопленных частот, превышающая половину всех значений (58,9), соответствует интервалу 19 000 – 27 000. Это и есть медианный интервал, в котором находится медиана. Определим её значение по приведённой выше формуле, если
= 19 000, = 8 000, Σf = 100, = 40,7, = 18,2:
Это так называемый медианный доход - показатель уровня дохода индивида, находящегося в середине ранжированного ряда распределения.
Таким образом, половина населения имеет среднедушевой доход ниже 23 087,91 руб., а вторая половина – вышее 23 087,91 руб.
2) Рассчитаем первый и третий квартили:
– начальные значения квартильных интервалов, первого и третьего соответственно;
i – величина квартильного интервала;
Σf – сумма частот ряда;
– накопленные частоты интервала, предшествующего квартильному;
– частота квартильного интервала.
Определим интервалы, в которых находятся 1 и 3 квартиль. По ряду накопленных частот (третья графа в таблице) видно, что варианта, отсекающая 1/4 численности частот, находится в интервале от 10 000 до 14 000.
А варианта, отсекающая 3/4 численности частот, находится в интервале от 27 000 до 45 000.
То есть у 25% населения душевой доход не превышает 13 570,25 руб.
То есть у 25% населения душевой доход выше, чем 39 880,00 руб.
3) Децили делят ряд распределения признака по частоте на 10 равных частей.
Рассчитаем первый и девятый децили.
– начальные значения децильных интервалов, первого и девятого соответственно;
i – величина децильного интервала;
Σf – сумма частот ряда;
– накопленные частоты интервала, предшествующего децильному;
– частота децильного интервала.
Определим интервалы, в которых находятся 1 и 9 дециль. По ряду накопленных частот (третья графа в таблице) видно, что варианта, отсекающая 1/10 численности частот находится в интервале от 7 000 до 10 000.
А варианта, отсекающая 9/10 численности частот находится в интервале от 45 000 и выше.
Следовательно, 10% самых бедных имеют среднедушевые доходы менее 8425 руб., а остальные 90% - выше этой суммы.
Последний интервал является открытым (не имеет верхней границы). Величину данного интервала принимают равной величине предыдущего интервала.
i = 45 000 – 27 000 = 18 000.
У 10% самого обеспеченного населения среднедушевые доходы превышают 53 322,58 руб.
2. Децильный коэффициент дифференциации доходов населения определяется по формуле:
Минимальные доходы 10% самого богатого населения России в 2015 году превысили максимальные доходы 10% наименее обеспеченного населения в 6, 329 раз.
Источник данных: Россия в цифрах. 2016: Крат.стат.сб./Росстат-М.,2016 - 543 с.
Дециль, как звучит его название, является статистическим термином, который делит данные на десять определенных интервалов. Он в основном делит точки данных на набор данных в 10 равных частях в числовой строке. Этот тип ранжирования данных используется во многих областях, таких как финансы, экономика и т. Д. Мы должны помнить одну вещь: точки данных могут быть случайными, и мы должны поместить эти числа в строку сначала в числовой строке в порядке возрастания, а затем разделите их в децилях. Как только мы разделим данные, ранжирование по децилям может быть дано:
Как и другие инструменты квартиль и процентиль, дециль также является методом, который делит данные на более мелкие части, которые легче измерять, анализировать и понимать.
Формула для дециля:
Допустим, у нас есть набор данных с N точками данных:
X - (X1, X2, X3 ……… .. XN)
Формула для децилей дается:
D i = (N + 1) * i / 10
Из приведенной выше формулы видно, что D5 = (N + 1) * 5/10 = (N + 1) / 2, что является медианой. Таким образом, 5- й дециль представляет собой медиану.
Для сгруппированных данных:
Di = L + (h / f) * (i * (N / 10) – C)
- L - нижняя граница группы
- h - ширина
- f - частота
- N - общее количество точек данных
- C - накопленная частота, предшествующая этому классу
Примеры формул дециля (с шаблоном Excel)
Давайте рассмотрим пример, чтобы лучше понять расчет децилей.
Вы можете скачать этот шаблон Excel с формулой дециля здесь - Шаблон Excel с формулой дециля
Децильная формула - пример № 1
Допустим, у нас есть наборы данных, которые содержат 39 точек данных. Рассчитайте децили.
Набор данных:
Решение:
Прежде всего, мы должны расположить это в порядке возрастания, т.е. от низшего к высшему.
Дециль рассчитывается по формуле, приведенной ниже
D i = (N + 1) * i / 10
Результат будет таким, как указано ниже.
Аналогичным образом рассчитайте для всех точек.
Если вы видите набор данных, медиана этого набора: (n + 1) / 2 = 40/2 = 20- е значение, т.е. 58, это то же самое, что и D5.
Децильная формула - пример № 2
Предположим, ниже приведена таблица частот, которая у вас есть, для которой вам нужно рассчитать децили.
Из приведенного выше изображения, мы дали следующую информацию.
Дециль рассчитывается по формуле, приведенной ниже
Di = L + (h / f) * (I * (N / 10) - C)
Результат будет как указано ниже
Аналогичным образом рассчитайте для других значений
Di = L + (h / f) * (i * (N / 10) - C)
- D1 = 10 + (10/8) * (1 * (70/10) - 0) = 18, 75
- D2 = 20 + (10/12) * (2 * (70/10) - 8) = 25
- D3 = 30 + (10/14) * (3 * (70/10) - 20) = 30, 7
- D4 = 40 + (10/10) * (4 * (70/10) - 34) = 34
- D5 = 50 + (10/6) * (5 * (70/10) - 44) = 35
- D6 = 60 + (10/16) * (6 * (70/10) - 50) = 55
- D7 = 70 + (10/4) * (7 * (70/10) - 66) = 27, 5
объяснение
Мы знаем, что квартили делят данные на четыре равные группы, дециль делит данные на десять равных частей. Дециль, как обсуждалось выше, ранжирует набор данных от самого низкого до самого высокого. Это делается по шкале от 1 до 10, где каждое число в основном указывает на увеличение на 10 процентных пунктов. Таким образом, 1-й дециль имеет 10% точек данных под ним, D2 имеет 20% точек данных под ним и так далее. Таким образом, ранжирование по децилям является еще одним методом классификации данных, таких как квартиль или процентиль.
Актуальность и использование формулы дециля
Как обсуждалось выше, Decile помогает нам очень быстро разделить данные на десять частей и, в конечном итоге, облегчает понимание данных в этих частях. В финансах децили используются для анализа эффективности различных фондов путем их ранжирования и измерения производительности одного фонда по сравнению с аналогичными фондами. Например, вы являетесь аналитиком и оцениваете набор взаимных фондов, поэтому любой фонд, который занимает 6 место по шкале децилей, означает, что он входит в верхние 60% фондов. Таким образом, в основном мы можем использовать децили и разделять эти средства на наиболее эффективные и худшие за определенный период.
Рекомендуемые статьи
Это руководство по формуле децилей. Здесь мы обсудим, как рассчитать децили, а также практические примеры и загружаемый шаблон Excel. Вы также можете посмотреть следующие статьи, чтобы узнать больше -
Каждый раз, выполняя в Excel статистический анализ, нам приходится сталкиваться с расчётом таких значений, как дисперсия, среднеквадратичное отклонение и, разумеется, коэффициент вариации. Именно расчёту последнего стоит уделить особое внимание. Очень важно, чтобы каждый новичок, который только приступает к работе с табличным редактором, мог быстро подсчитать относительную границу разброса значений.
В этой статье мы расскажем, как автоматизировать расчеты при прогнозировании данных
Что такое коэффициент вариации и для чего он нужен?
Итак, как мне кажется, нелишним будет провести небольшой теоретический экскурс и разобраться в природе коэффициента вариации. Этот показатель необходим для отражения диапазона данных относительно среднего значения. Иными словами, он показывает отношение стандартного отклонения к среднему значению. Коэффициент вариации принято измерять в процентном выражении и отображать с его помощью однородность временного ряда.
Коэффициент вариации станет незаменимым помощником в том случае, когда вам необходимо будет сделать прогноз по данным из заданной выборки. Этот индикатор выделит главные ряды значений, которые будут наиболее полезными для последующего прогнозирования, а также очистит выборку от малозначительных факторов. Так, если вы видите, что значение коэффициента равно 0%, то с уверенностью заявляйте о том, что ряд является однородным, а значит, все значения в нём равны один с другим. В случае, если коэффициент вариации принимает значение, превышающее отметку в 33%, то это говорит о том, что вы имеете дело с неоднородным рядом, в котором отдельные значения существенно отличаются от среднего показателя выборки.
Как найти среднее квадратичное отклонение?
Поскольку для расчёта показателя вариации в Excel нам необходимо использовать среднее квадратичное отклонение, то вполне уместно будет выяснить, как нам посчитать этот параметр.
Из школьного курса алгебры мы знаем, что среднее квадратичное отклонение — это извлечённый из дисперсии квадратный корень, то есть этот показатель определяет степень отклонения конкретного показателя общей выборки от её среднего значения. С его помощью мы можем измерить абсолютную меру колебания изучаемого признака и чётко её интерпретировать.
Рассчитываем коэффициент в Экселе
К сожалению, в Excel не заложена стандартная формула, которая бы позволила рассчитать показатель вариации автоматически. Но это не значит, что вам придётся производить расчёты в уме. Отсутствие шаблона в «Строке формул» никоим образом не умаляет способностей Excel, потому вы вполне сможете заставить программу выполнить необходимый вам расчёт, прописав соответствующую команду вручную.
Вставьте формулу и укажите диапазон данных
Для того чтобы рассчитать показатель вариации в Excel, необходимо вспомнить школьный курс математики и разделить стандартное отклонение на среднее значение выборки. То есть на деле формула выглядит следующим образом — СТАНДОТКЛОН(заданный диапазон данных)/СРЗНАЧ(заданный диапазон данных). Ввести эту формулу необходимо в ту ячейку Excel, в которой вы хотите получить нужный вам расчёт.
Не забывайте и о том, что поскольку коэффициент выражается в процентах, то ячейке с формулой нужно будет задать соответствующий формат. Сделать это можно следующим образом:
- Откройте вкладку «Главная».
- Найдите в ней категорию «Формат ячеек» и выберите необходимый параметр.
Как вариант, можно задать процентный формат ячейке при помощи клика по правой кнопке мыши на активированной клеточке таблицы. В появившемся контекстном меню, аналогично вышеуказанному алгоритму нужно выбрать категорию «Формат ячейки» и задать необходимое значение.
Выберите «Процентный», а при необходимости укажите число десятичных знаков
Возможно, кому-то вышеописанный алгоритм покажется сложным. На самом же деле расчёт коэффициента так же прост, как сложение двух натуральных чисел. Единожды выполнив эту задачу в Экселе, вы больше никогда не вернётесь к утомительным многосложным решениям в тетрадке.
Всё ещё не можете сделать качественное сравнение степени разброса данных? Теряетесь в масштабах выборки? Тогда прямо сейчас принимайтесь за дело и осваивайте на практике весь теоретический материал, который был изложен выше! Пусть статистический анализ и разработка прогноза больше не вызывают у вас страха и негатива. Экономьте свои силы и время вместе с табличным редактором Excel.
Коэффициент вариации в статистике применяется для сравнения разброса двух случайных величин с разными единицами измерения относительно ожидаемого значения. В итоге можно получить сопоставимые результаты. Показатель наглядно иллюстрирует однородность временного ряда.
Коэффициент вариации используется также инвесторами при портфельном анализе в качестве количественного показателя риска, связанного с вложением средств в определенные активы. Особенно эффективен в ситуации, когда у активов разная доходность и различный уровень риска. К примеру, у одного актива высокая ожидаемая доходность, а у другого – низкий уровень риска.
Как рассчитать коэффициент вариации в Excel
Коэффициент вариации представляет собой отношение среднеквадратического отклонения к среднему арифметическому. Для расчета в статистике используется следующая формула:
- CV – коэффициент вариации;
- σ – среднеквадратическое отклонение по выборке;
- ǩ – среднеарифметическое значение разброса значений.
Коэффициент вариации позволяет сравнить риск инвестирования и доходность двух и более портфелей активов. Причем последние могут существенно отличаться. То есть показатель увязывает риск и доходность. Позволяет оценить отношение между среднеквадратическим отклонением и ожидаемой доходностью в относительном выражении. Соответственно, сопоставить полученные результаты.
При принятии инвестиционного решения необходимо учитывать следующий момент: когда ожидаемая доходность актива близка к 0, коэффициент вариации может получиться большим. Причем показатель значительно меняется при незначительном изменении доходности.
В Excel не существует встроенной функции для расчета коэффициента вариации. Но можно найти частное от стандартного отклонения и среднего арифметического значения. Рассмотрим на примере.
Доходность двух ценных бумаг за предыдущие пять лет:
Наглядно это можно продемонстрировать на графике:
Обычно показатель выражается в процентах. Поэтому для ячеек с результатами установлен процентный формат.
Значение коэффициента для компании А – 33%, что свидетельствует об относительной однородности ряда. Формула расчета коэффициента вариации в Excel:
Сравните: для компании В коэффициент вариации составил 50%: ряд не является однородным, данные значительно разбросаны относительно среднего значения.
Интерпретация результатов
Прежде чем включить в инвестиционный портфель дополнительный актив, финансовый аналитик должен обосновать свое решение. Один из способов – расчет коэффициента вариации.
Ожидаемая доходность ценных бумаг составит:
Среднеквадратическое отклонение доходности для активов компании А и В составляет:
Ценные бумаги компании В имеют более высокую ожидаемую доходность. Они превышают ожидаемую доходность компании А в 1,14 раза. Но и инвестировать в активы предприятия В рискованнее. Риск выше в 1,7 раза. Как сопоставить акции с разной ожидаемой доходностью и различным уровнем риска?
Для сопоставления активов двух компаний рассчитан коэффициент вариации доходности. Показатель для предприятия В – 50%, для предприятия А – 33%. Риск инвестирования в ценные бумаги фирмы В выше в 1,54 раза (50% / 33%). Это означает, что акции компании А имеют лучшее соотношение риск / доходность. Следовательно, предпочтительнее вложить средства именно в них.
Таким образом, коэффициент вариации показывает уровень риска, что может оказаться полезным при включении нового актива в портфель. Показатель позволяет сопоставить ожидаемую доходность и риск. То есть величины с разными единицами измерения.
Читайте также: