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

Как построить кривую Лоренца в Excel

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

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

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

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

Построим график, который будет представлять собой линию равенства. Во вкладке «Вставка» выберем точечную диаграмму – «Точечная с гладкими кривыми и маркерами».

Заполняем данные, на основе которых будет построена диаграмма. Заполняем «Имя ряда», вводим максимальные и минимальные значения для оси Х и У и применяем процедуру.

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

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

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

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

Источник

Как сделать кривую в excel?

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

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

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

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

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

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

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

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

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

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

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

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

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

Урок: Как сделать диаграмму в Экселе

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

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

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

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

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

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

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

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

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

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

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

Простейший график изменений

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

Допустим, у нас есть данные по чистой прибыли предприятия за 5 лет:

* Цифры условные, для учебных целей.

Заходим во вкладку «Вставка». Предлагается несколько типов диаграмм:

Выбираем «График». Во всплывающем окне – его вид. Когда наводишь курсор на тот или иной тип диаграммы, показывается подсказка: где лучше использовать этот график, для каких данных.

Выбрали – скопировали таблицу с данными – вставили в область диаграммы. Получается вот такой вариант:

Прямая горизонтальная (синяя) не нужна. Просто выделяем ее и удаляем. Так как у нас одна кривая – легенду (справа от графика) тоже убираем. Чтобы уточнить информацию, подписываем маркеры. На вкладке «Подписи данных» определяем местоположение цифр. В примере – справа.

Улучшим изображение – подпишем оси. «Макет» – «Название осей» – «Название основной горизонтальной (вертикальной) оси»:

Заголовок можно убрать, переместить в область графика, над ним. Изменить стиль, сделать заливку и т.д. Все манипуляции – на вкладке «Название диаграммы».

Вместо порядкового номера отчетного года нам нужен именно год. Выделяем значения горизонтальной оси. Правой кнопкой мыши – «Выбрать данные» — «Изменить подписи горизонтальной оси». В открывшейся вкладке выбрать диапазон. В таблице с данными – первый столбец. Как показано ниже на рисунке:

Можем оставить график в таком виде. А можем сделать заливку, поменять шрифт, переместить диаграмму на другой лист («Конструктор» — «Переместить диаграмму»).

График с двумя и более кривыми

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

Но принцип построения остался прежним. Только теперь есть смысл оставить легенду. Так как у нас 2 кривые.

Добавление второй оси

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

Сначала строим график так, будто у нас одинаковые единицы измерения.

Выделяем ось, для которой хотим добавить вспомогательную. Правая кнопка мыши – «Формат ряда данных» – «Параметры ряда» — «По вспомогательной оси».

Нажимаем «Закрыть» — на графике появилась вторая ось, которая «подстроилась» под данные кривой.

Это один из способов. Есть и другой – изменение типа диаграммы.

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

Определяемся с видом для второго ряда данных. В примере – линейчатая диаграмма.

Всего несколько нажатий – дополнительная ось для другого типа измерений готова.

Строим график функций в Excel

Вся работа состоит из двух этапов:

Пример: y=x(√x – 2). Шаг – 0,3.

Составляем таблицу. Первый столбец – значения Х. Используем формулы. Значение первой ячейки – 1. Второй: = (имя первой ячейки) + 0,3. Выделяем правый нижний угол ячейки с формулой – тянем вниз столько, сколько нужно.

В столбце У прописываем формулу для расчета функции. В нашем примере: =A2*(КОРЕНЬ(A2)-2). Нажимаем «Ввод». Excel посчитал значение. «Размножаем» формулу по всему столбцу (потянув за правый нижний угол ячейки). Таблица с данными готова.

Переходим на новый лист (можно остаться и на этом – поставить курсор в свободную ячейку). «Вставка» — «Диаграмма» — «Точечная». Выбираем понравившийся тип. Щелкаем по области диаграммы правой кнопкой мыши – «Выбрать данные».

Выделяем значения Х (первый столбец). И нажимаем «Добавить». Открывается окно «Изменение ряда». Задаем имя ряда – функция. Значения Х – первый столбец таблицы с данными. Значения У – второй.

Жмем ОК и любуемся результатом.

С осью У все в порядке. На оси Х нет значений. Проставлены только номера точек. Это нужно исправить. Необходимо подписать оси графика в excel. Правая кнопка мыши – «Выбрать данные» — «Изменить подписи горизонтальной оси». И выделяем диапазон с нужными значениями (в таблице с данными). График становится таким, каким должен быть.

Наложение и комбинирование графиков

Построить два графика в Excel не представляет никакой сложности. Совместим на одном поле два графика функций в Excel. Добавим к предыдущей Z=X(√x – 3). Таблица с данными:

Выделяем данные и вставляем в поле диаграммы. Если что-то не так (не те названия рядов, неправильно отразились цифры на оси), редактируем через вкладку «Выбрать данные».

А вот наши 2 графика функций в одном поле.

Графики зависимости

Данные одного столбца (строки) зависят от данных другого столбца (строки).

Построить график зависимости одного столбца от другого в Excel можно так:

Условия: А = f (E); В = f (E); С = f (E); D = f (E).

Выбираем тип диаграммы. Точечная. С гладкими кривыми и маркерами.

Выбор данных – «Добавить». Имя ряда – А. Значения Х – значения А. Значения У – значения Е. Снова «Добавить». Имя ряда – В. Значения Х – данные в столбце В. Значения У – данные в столбце Е. И по такому принципу всю таблицу.

Скачать все примеры графиков

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

Чуть ранее мы уже писали, как красиво оформить нулевые/пустые значения на графике, чтобы диаграмма не получалась «зубчатой». Помимо этого, для лучшей визуализации информации иногда нужно сделать сглаживание графика в Excel. Как это сделать? Читайте ниже

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

Сглаживание графика в Excel. Как быстро сделать?

Часто соединения узлов графика выглядят некрасиво, если линии на графике расположены под острыми углами. Как сделать плавную линию? Правой кнопкой мыши нажимаем на сам график — выплывает окно —

Теперь линия сгладилась.

Экспоненциальное сглаживание в Excel

В Excel можно подключить пакет анализа для сглаживания самих данных.

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

Зайдите в меню — Параметры Excel — Надстройки — Пакет анализа (в правом окне) и в самом низу нажимайте Перейти

В открывшемся окне находим Экспоненциальное сглаживание.

Как найти прямую приближенных значений

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

Поделитесь нашей статьей в ваших соцсетях:

(Visited 15 933 times, 8 visits today)

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

Как построить простой график

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

Дальше нужно выполнить следующие действия.

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

Как построить график с несколькими рядами данных

Сделать большую диаграмму с двумя и более колонками несложно. Принцип практически такой же.

В этом случае заголовком таблицы будет значение по умолчанию – «Название диаграммы», поскольку Эксель не знает, какая из колонок главная. Всё это можно изменить, но об этом будет сказано немного позже.

Как добавить линию на уже существующую диаграмму

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

Здесь вы можете подумать, что проще построить всё заново. С одной стороны – да. Но с другой – представьте, что у вас на листе не то что показано выше, а что-то более масштабное. В таких случаях быстрее будет добавить новый ряд, чем начинать сначала.

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

Как увеличить количество значений на графике

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

В данном примере были добавлены даты с 15-го по 20-е июля, но на графике их нет. Для того чтобы исправить это, нужно сделать следующее.

Теперь выберите один из рядов и нажмите на кнопку «Изменить».

Построение графиков математических уравнений

Название и назначение формулы может меняться в зависимости от задания. «Степень» подходит именно для нашего примера.

Переходим на вкладку «Вставка». Кликаем на иконку «Графики». Выбираем первый из предложенных вариантов.

Теперь всё как положено.

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

Виды графиков

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

Для второго случая необходимо сделать следующие шаги:

В программе Excel существуют следующие виды графиков:

Оформление

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

Название диаграммы

Для того чтобы изменить заголовок, нужно сначала кликнуть по нему.

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

В результате этого можно написать что угодно.

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

Сразу после этого вы увидите окно, в котором можно сделать с текстом то же самое, что и в редакторе Microsoft Word.

Для сохранения необходимо нажать на кнопку «OK».

Обратите внимание на то, что напротив этого элемента есть дополнительное «подменю», в котором вы можете выбрать положение названия:

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

Название осей

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

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

Подписи данных

Для активации этой функции, необходимо снова нажать на иконку «+» и поставить соответствующую галочку.

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

Если нажать на иконку «треугольника», то появится дополнительное меню, в котором можно указать положение данных цифр:

При нажатии на пункт «Дополнительные параметры», в правой части программы появится панель с различными свойствами. Там вы сможете:

К основным категориям относятся:

Таблица данных

Этот компонент диаграммы включается аналогичным образом.

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

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

При нажатии на пункт «Дополнительные параметры» вы увидите следующее.

Сетка

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

В дополнительных параметрах можно увидеть следующее.

Легенда

Данный элемент всегда включен по умолчанию. При желании вы сможете отключить его или указать положение на диаграмме.

Полосы понижения и повышения

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

К дополнительным параметрам «Полос» относятся:

Дополнительные вкладки на панели инструментов

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

Конструктор

В этом разделе вы сможете:

Формат

Содержимое данного раздела постоянно меняется. Всё зависит от того, с каким объектом (элементом) вы работаете в данный момент.

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

Заключение

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

Кроме этого, отсутствие ожидаемого результата может быть из-за неправильного выбора типа диаграммы. Большое количество вариантов внешнего вида связано с различным назначением.

Видеоинструкция

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

Источник

Примеры решения типовых задач

Пример 6.1.Дано распределение объектов по имущественному признаку.

Найти накопленные частости .

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

1) Рассчитаем частости используя данные из столбца «Частота»по формуле:

Для этого в электронной таблице Excelв пустой ячейке ставим знак «=», после указываем соответствующую ячейку из столбца «Частота», ставим знак деления и, используя функцию СУММ, выделяем весь столбец «Частота»;

2) Находим накопленные частости по формуле

В электронной таблице Excel выбираем функцию СУММ в категории «Математические» и в каждой строке столбца Р указываем предшествующие (включая данную) строки в столбце (см. рис. 6.2).

Пример 6.2.Дано распределение объектов по признаку.

Найти значение первого дециля.

Для нахождения первого дециля нам необходимо воспользоваться формулой.

– первый дециль; – значение столбца «ОТ»; – значение столбца «ДО» или часто используют обозначение h вместо ; — накопленная частость предшествующего значения в %; – частость в %.

Зная эти значения можно найти значение первого дециля.

Для этого в электронной таблице Excelс помощью формул СУММ и ПРОИЗВЕД, знаков деления и «минус», и соответствующих ячеек выполняем действия указанные в формуле (пример =СУММ(G2;ПРОИЗВЕД((H2-G2);(10-0)/(J2*100))), где G2 – значение ОТ входящий в первый дециль; Н2 – значение ДО входящий в первый дециль; J2 – частость, в которую входит первый дециль).

На основе проведённых расчетов получены следующие результаты (см. рис.6.2).

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

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

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

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

1) Находим варианту для каждой строки по формуле средней арифметической:

где

– варианта; – значения столбца «ОТ»; – значения столбца «ДО».

Для того чтобы сделать это в электронной таблице Excelвыбираем в категории «Статистические» функцию СРЗНАЧ и указываем необходимые ячейки;

2) Рассчитаем для средней выборочной сумму значений произведений

В электронной таблице Excel выбираем в категории «Математические» функцию ПРОИЗВЕД и, указывая на необходимые ячейки, делаем столбик после чего, выбирая функцию СУММ в категории «Математические», указываем весь столбик;

3) Рассчитаем доли по формуле

Для этого в электронной таблице Excelв пустой ячейке ставим знак «=» пишем «100*», после указываем соответствующую ячейку из столбца после ставим знак деления и, используя формулу СУММ, указываем весь столбец ;

4) Для нахождения коэффициента Герфиндаля рассчитываем столбец путём возведения в квадрат каждое значение

В электронной таблице Excel выбираем формулу СТЕПЕНЬ в категории «Математические» в первой графе указываем нужную нам ячейку, а во второй цифру «2»

5) Найдём коэффициент Герфиндаля по формуле

Для этого в Excel выбираем функцию СУММ в категории «Математические» и указываем все значения в столбце

На основе проведённых расчетов получены следующие результаты (см. рис. 6.3).

Рис.6.3. Результаты расчета коэффициента Герфиндаля

Пример 6.4.Денежные доходы населения составляют:

Необходимо построить кривую Лоренца.

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

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

Рис. 6.4. Построение кривой Лоренца

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

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

Рис. 6.5. Кривая Лоренца

Пример 6.5. Определить коэффициенты Лоренца и Джини.

1). Коэффициент Лоренца рассчитываем по следующей формуле:

Приступаем к его вычислению в Excel (формула выше). Выглядеть это будет так (см. рис. 6.6):

Рис.6.6. Таблица расчета Коэффициента Лоренца.

2) Коэффициент Джини рассчитываем по следующей формуле:

Определим согласно формуле выше произведение Xi*Yi. Для этого в электронной таблице Excel в пустой ячейке С2 ставим знак: «=», выбираем нужную нам ячейку А2 ставим знак умножить: «*» на ячейку В2 и так далее по каждой паре, а потом делаем сумму всех этих произведений.

Расчёт кумуляты Xi и Yi будет по следующему принципу: CumXi=0+0.2=0,2; 0.2+0,15=0.35; 0.2+0,15+0,28=0.63; и так далее. В электронной таблице Excel это будет выглядеть так: A2+A3;A4+A5+A6 и так далее. Расчёт кумуляты Yi аналогично.В Excel это будет выглядеть так (см. рис. 6.6):

Рис.6.6. Таблица расчета Коэффициента Джини.

Источник