Как строить кривую лоренца в excel

Кривая Лоренца

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

Пример №1 . На основе данных таблицы:

  1. Рассчитайте коэффициент Джини
  2. Постройте кривую Лоренца.
Социальная группа населения Численность населения, % Денежные доходы, %
1. 20 6,0
2. 20 11,6
3. 20 17,6
4. 20 26,5
5. 20 38,3
Итого 100 100,0

Денежные доходы населения

Группы по денежным доходам, % Доля денежных доходов в группе, %
20 6
40 11.6
60 17.6
80 26.5
100 38.3

Линия фактического неравенства строится на основании данных о процентах дохода приходящихся на каждые 20 % населения.
Если нижняя первая часть населения получила 6.0% всех доходов, то графически это будет точка А. Чтобы получить точку В необходимо сложить процент дохода первых 20 % населения с процентами доходов вторых 20 % населения (6.0% + 11.6%) и т.д.

20-ти процентные группы населения Объем денежных доходов населения, в % к итогу Доля денежных доходов нарастающим итогом, % Площадь треугольника Площадь прямоугольника Общая площадь фигуры, Si
20 6 6 60 0 60
40 11.6 17.6 116 120 236
60 17.6 35.2 176 352 528
80 26.5 61.7 265 704 969
100 38.3 100 383 1234 1617
3410

Чтобы построить кривую Лоренца откладываем по оси Х значения 1-го столбца, а по оси Y значения 3-го столбца.

2. Индекс Джини.
Для исчисления коэффициента Джини необходимо рассчитать величины pi и qi. Здесь qi — доля денежных доходов нарастающим итогом (столбец №3 табл.1) деленная на 100.

pi qi piqi+1 pi+1qi
0.2 0.06 0.0352
0.4 0.18 0.14 0.024
0.6 0.35 0.37 0.11
0.8 0.62 0.8 0.28
1 1 0.62
ВСЕГО 1.3462 1.0282

Коэффициент Джини равен: KL = ∑piqi+1 — ∑pi+1qi = 1.3462 — 1.0282 = 0.318

Пример №2 . Имеются следующие данные о распределении доходов населения региона по трем группам и доле населения в каждой группе:

1 группа 2 группа 3 группа
Доходы населения по группам (руб.) 5000-10000 10000-20000 20000-30000
Доля населения в группе 0,15 0,60 0,25

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

1 группа 2 группа 3 группа Итого
Доходы населения по группам (руб.) 5000-10000 10000-20000 20000-30000
Среднее значение дохода в группе, руб. 7500 15000 25000 47500
Доля доходов в группе, % 15,8 31,6 52,6 100,0

Далее решается через калькулятор.

Доля доходов в группе, % Доля населения в группе
15,8 0,15
31,6 0,6
52,6 0,25

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

№ группа Среднедушевые доходы населения (тыс. руб.) Количество насосов
1 0-30 10
2 30,1-60 14
3 60,1-90 19
4 90,1-120 25

Пример №4 . Исходные данные:

% Доход
10 2.3
20 4.5
30 4.7
40 6
50 7.5
60 10.2
70 14.1
80 16.7
90 18.5
100 15.5

Линия фактического неравенства строится на основании данных о процентах дохода приходящихся на каждые 10% населения. Если нижняя первая часть населения получила 2.3% всех доходов то графически это будет точка А. Чтобы получить точку В необходимо сложить процент дохода первых 10% населения с процентами доходов вторых 10% населения (2.3% + 4.5%) и т.д.

% Доход S=Si+Si-1 Площадь треугольника Площадь Сумма
10 2.3 2.3 11.5 0 11.5
20 4.5 6.8 22.5 23 45.5
30 4.7 11.5 23.5 68 91.5
40 6 17.5 30 115 145
50 7.5 25 37.5 175 212.5
60 10.2 35.2 51 250 301
70 14.1 49.3 70.5 352 422.5
80 16.7 66 83.5 493 576.5
90 18.5 84.5 92.5 660 752.5
100 15.5 100 77.5 845 922.5
Итого 3481

Чтобы построить кривую Лоренца откладываем по оси Х откладываем значения столбца Процент (%), а по сои Y значения столбца S.

Рассчитаем коэффициенты концентрации доходов (индекс Джини)

Уровень неравенства определяется с помощью коэффициента Джини.

Он рассчитывается как отношение площади фигуры OABCDKLMNPE к площади треугольника ОEG.

Для того чтобы определить площадь фигуры, лежащей ниже кривой Лоренца, соединяем прямыми линиями точки ОА, АВ и т.д.

Опускаем перпендикуляр на ось X и находим площади фигур, лежащих ниже точек А, B , С.

Площадь SABB’A состоит из треугольника и прямоугольника SBCCB’ также состоит из треугольника и прямоугольника.

Сложив все площади фигур, получим площадь фигуры S2.

Площадь треугольника OEG находим по формуле:

1/2 *100%*100% = 5000

Отсюда индекс Джини равен:

I = 1519 / 5000 = 0.3038

Децильный коэффициент дифференциации доходов

Построение кривой Лоренца в Microsoft Excel

Кривая Лоренца в Microsoft Excel

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

Использование кривой Лоренца

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

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

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

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

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

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

Теперь давайте на конкретном примере посмотрим, как создать линию равенства и кривую Лоренца в Экселе. Для этого используем таблицу количества населения разбитого на пять равных групп (по 20%), которые суммируются в таблице по нарастающей. Во второй колонке этой таблицы представлена величина национального дохода в процентном соотношении, которая соответствует определенной группе населения.

Таблица доходов населения в Microsoft Excel

Для начала построим линию абсолютного равенства. Она будет состоять из двух точек – нулевой и точки суммарного национального дохода для 100% населения.

    Переходим во вкладку «Вставка». На линии в блоке инструментов «Диаграммы» жмем на кнопку «Точечная». Именно данный тип диаграмм подойдет для нашей задачи. Далее открывается список подвидов диаграмм. Выбираем «Точечная с гладкими кривыми и маркерами».

Выбор вида диаграммы в Microsoft Excel

Переход к выбору данных в Microsoft Excel

Открывается окно выбора источника данных. В левой его части, которая называется «Элементы легенды (ряды)» жмем на кнопку «Добавить».

Окно выбора источника данных в Microsoft Excel

Запускается окно изменения ряда. В поле «Имя ряда» записываем то наименование диаграммы, которое хотим ей присвоить. Оно может также располагаться на листе и в этом случае нужно указать адрес ячейки его нахождения. Но в нашем случае легче просто вписать название вручную. Дадим диаграмме наименование «Линия равенства».

В поле «Значения X» следует указать координаты точек диаграммы по оси X. Как мы помним, их будет всего две: 0 и 100. Записываем данные значения через точку с запятой в данном поле.

В поле «Значения Y» следует записать координаты точек по оси Y. Их тоже будет две: 0 и 35,9. Последняя точка, как мы можем видеть по графику, соответствует совокупному национальному доходу 100% населения. Итак, записываем значения «0;35,9» без кавычек.

После того, как все указанные данные внесены, жмем на кнопку «OK».

Изменения ряда для диаграммы Линия равенства в Microsoft Excel

После этого мы возвращаемся к окну выбора источника данных. В нем тоже следует нажать на кнопку «OK».

Закрытие окна выбора источника данных в Microsoft Excel

  • Как видим, после вышеуказанных действий линия равенства будет построена и отобразится на листе.
  • Линия равенства построена в Microsoft Excel

    Создание кривой Лоренца

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

      Кликаем правой кнопкой мыши по области диаграммы, на которой уже расположена линия равенства. В запустившемся меню снова останавливаем выбор на пункте «Выбрать данные…».

    Переход к выбору данных в программе Microsoft Excel

    Опять открывается окно выбора данных. Как видим, среди элементов уже представлено наименование «Линия равенства», но нам нужно внести ещё одну диаграмму. Поэтому жмем на кнопку «Добавить».

    Переход к добавлению нового элемента в окне выбора источника в Microsoft Excel

    Снова открывается окно изменения ряда. Поле «Имя ряда», как и в прошлый раз, заполняем вручную. Сюда можно вписать наименование «Кривая Лоренца».

    В поле «Значения X» следует занести все данные столбца «% населения» нашей таблицы. Для этого устанавливаем курсор в область поля. Далее зажимаем левую кнопку мыши и выделяем соответствующий столбец на листе. Координаты тут же будут отображены в окне изменения ряда.

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

    После того, как все вышеуказанные данные внесены, жмем на кнопку «OK».

    Изменения ряда для кривой Лоренца в Microsoft Excel

    После возврата к окну выбора источника опять жмем на кнопку «OK».

    Закрытие окна выбора источника данных в программе Microsoft Excel

  • Как видим, после выполнения вышеуказанных действий кривая Лоренца также отобразится на листе Excel.
  • Кривая Лоренца построена в Microsoft Excel

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

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

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

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

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

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