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

Как создать кривую Лоренца в Excel (с примером)

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

В этом руководстве представлен пошаговый пример того, как создать следующую кривую Лоренца в Excel:

Шаг 1: введите данные

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

Вот как интерпретировать значения:

• Нижние 20% людей в этой стране составляют 10% от общего дохода.
• Нижние 50% людей в этой стране составляют 31% от общего дохода.
• Нижние 60% людей в этой стране составляют 40% от общего дохода.
• На 100% физических лиц в этой стране приходится 100% общего дохода.

Затем мы введем значения (x, y) для линии равенства, которую мы в конечном итоге добавим на график:

Шаг 2: Создайте базовую кривую Лоренца

Затем выделите значения в ячейках A2: B6 , а затем щелкните вкладку « Вставка » на верхней ленте, затем щелкните параметр « Разброс с плавными линиями » в группе « Диаграммы »:

Автоматически будет создана следующая диаграмма:

Затем щелкните правой кнопкой мыши в любом месте диаграммы и выберите параметр « Выбрать данные ».

В появившемся окне нажмите « Добавить » в разделе « Записи легенды» :

Нажмите кнопку « Добавить », затем введите следующую информацию:

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

Основная кривая Лоренца завершена. Синяя линия представляет собой кривую Лоренца, а оранжевая линия представляет собой линию равенства.

Шаг 3: Настройте кривую Лоренца

Наконец, мы можем настроить внешний вид диаграммы.

Нажмите на зеленый знак плюса ( + ) в правом верхнем углу диаграммы, затем нажмите « Легенда » и нажмите « Внизу »:

Легенда будет добавлена в нижнюю часть диаграммы.

Затем щелкните линии сетки на диаграмме и удалите их.

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

Затем добавьте название оси как к оси X, так и к оси Y.

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

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

Argument ‘Topic id’ is null or empty

Сейчас на форуме

© Николай Павлов, Planetaexcel, 2006-2023
info@planetaexcel.ru

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

ООО «Планета Эксел» ИНН 7735603520 ОГРН 1147746834949

ИП Павлов Николай Владимирович ИНН 633015842586 ОГРНИП 310633031600071

Создание диаграммы Парето

Excel для Microsoft 365 Word для Microsoft 365 Outlook для Microsoft 365 PowerPoint для Microsoft 365 Excel для Microsoft 365 для Mac Word для Microsoft 365 для Mac Outlook для Microsoft 365 для Mac PowerPoint для Microsoft 365 для Mac Excel 2021 Excel 2021 для Mac Excel 2019 Word 2019 Outlook 2019 PowerPoint 2019 Excel 2019 для Mac Outlook 2019 для Mac Excel 2016 Word 2016 Outlook 2016 PowerPoint 2016 Excel 2016 для Mac Excel для iPad Excel для iPhone Еще. Меньше

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

Создание диаграммы Парето

1. Выделите данные. Обычно выделяется столбец, содержащий текст (категории), и одно из чисел. Затем на диаграмме Парето группируются одинаковые категории и суммируются соответствующие числа.

Если выбрать два столбца чисел, а не одно из чисел и одну соответствующую текстовую категорию, Excel отобразит данные в виде интервалов, как на гистограмме. Затем вы можете настроить эти интервалы.
Выберите Вставка >Вставить диаграмму статистики, а затем в разделе Гистограмма, щелкните элемент Парето.

Совет: На вкладках Конструктор и Формат можно настроить внешний вид диаграммы. Если эти вкладки не отображаются, щелкните в любом месте диаграммы Парето, чтобы добавить на ленту область Работа с диаграммами.

Настройка интервалов

1. Щелкните правой кнопкой мыши горизонтальную ось диаграммы и выберите Формат оси >Параметры оси.

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

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

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

Формулы для создания гистограмм в Excel 2016

Формула Скотта:

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

Выход за верхнюю границу интервала

Выход за нижнюю границу интервала

Создание диаграммы Парето

1. Выделите данные. Обычно выделяется столбец, содержащий текст (категории), и одно из чисел. Затем на диаграмме Парето группируются одинаковые категории и суммируются соответствующие числа.

Если выбрать два столбца чисел, а не одно из чисел и одну соответствующую текстовую категорию, Excel отобразит данные в виде интервалов, как на гистограмме. Затем вы можете настроить эти интервалы. Подробные сведения см. в подстроке «Настройка Windows».
На ленте перейдите на вкладку Вставка и выберите

(значок статистической диаграммы), а затем в разделе Гистограмма нажмите кнопку Парето.

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

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

ABC-анализ широко используется в различных областях экономики: при управлении запасами, выборе поставщиков, работе с клиентами и т. п. [1, 2]. Он основан на эмпирическом законе Парето (принципе 80/20), согласно которому наибольший вклад в суммарный результат S какой-либо деятельности или активности дают усилия небольшого числа ее участников [1]. Под участником (игроком) будем понимать любой объект или субъект, вклад которого учитывается в суммарном итоге. Фольклорная формулировка принципа Парето (ПП): 80% результата дает деятельность 20% игроков. В ПП полагается, что функция результата является аддитивной от вкладов участников, количество которых N.
При проведении ABC-анализа рекомендуют придерживаться следующего алгоритма:
формулировка цели анализа;
определение участников;
установление показателей для дифференциации участников;
получение данных для анализа;
оценка участников по выбранным показателям;
упорядочение игроков согласно величинам показателей;
разделение участников на группы;
экономическая интерпретация результатов анализа, принятие управленческих решений и т. п. [1, 3].
Нами будет рассмотрен случай одного показателя. При этом упорядочение участников обычно осуществляется по убыванию величины показателя [1–4]. Каждому участнику присваивают номер, начиная с игрока с наибольшим значением показателя. Далее по полученным упорядоченным значениям находят для каждого участника показатель с нарастающим итогом. Строят в прямоугольной декартовой системе координат кривую Лоренца (КЛ) [5] зависимости этого показателя y от номера участника x. При построении кривой учитывают точку с координатами (0;0) – нулевой вклад от фиктивного («нулевого») участника. Часто эту кривую строят на основании данных в процентах. Для получения таких данных значения показателя с нарастающим итогом делят на значение для последнего участника (суммарный результат S) и умножают на 100%. Аналогично поступают с номерами участников: все их делят на максимальный номер N и результаты умножают на 100%. Используя полученную кривую, тем или иным способом [1, 3, 4, 6, 7] производят разбиение множества участников на три непересекающихся подмножества (группы) A, B и C.
Методов такого разбиения достаточно много [3, 4, 6, 7]. Мы упомянем эмпирический и метод касательных. Внимание сконцентрируем на последнем, который можно реализовывать аналитически и графически [4].
При графической реализации (рис. 1) начальную и конечную точки кривой Лоренца соединяют отрезком прямой. Далее на этой кривой находят точку (xA; yA), касательная в которой параллельна построенному отрезку прямой. Абсцисса xA найденной точки разделяет группы A и B и сама включается в группу A.?Чтобы разделить оставшихся участников на группы B и C, найденную точку (xA; yA) соединяют отрезком прямой с крайней правой точкой КЛ. На участке КЛ, лежащем правее (xA; yA), находят точку (xB; yB), в которой касательная к кривой параллельна второму отрезку. Абсцисса xB этой точки разделяет группы B и C и сама включается в группу B.?Если абсциссы точек (xA; yA) и (xB; yB) не принадлежат экспериментальным данным, то эти абсциссы не включаются в соответствующую группу. Отметим, что графическое разделение на группы A, B и C подразумевает аппроксимацию экспериментальных данных некоторой кривой [4].
Подобная аппроксимация экспериментальных данных функциональной зависимостью осуществляется при аналитической реализации метода касательных [4, 6]. Пусть аппроксимирующая функция имеет вид y = f(x), причем a x b, f(a) y f(b) (a, b – левая, правая границы области задания функции y = f(x)). Если эмпирические данные нормированы на 100%, то 0 x 1; 0 y ? 1 (f(0) = 0; f(1) = 1).
Уравнение прямой, содержащей первый отрезок, имеет вид
, (1)
в случае нормирования данных оно принимает вид

y = x. (1a)
Если функция y = f(x) непрерывна на [a; b] и дифференцируема в (a; b), то, согласно теореме Лагранжа [8], существует точка xA ? [a; b], в которой производная
; (2)
в случае нормирования –

f'(xA) = 1. (2a)
Точка xA разделяет группы A и B.
Уравнение прямой, содержащей второй отрезок, имеет вид
; (3)
при нормировании данных это уравнение принимает вид
. (3a)
По той же теореме Лагранжа существует точка xB ? [xA; b], в которой производная
; (4)
при нормировке данных
. (4a)
Точка xB разделяет группы B и C.
Следовательно, при аналитическом подходе необходимо: аппроксимировать экспериментальную зависимость аналитической, например методом наименьших квадратов [9]; решить аналитически или численно уравнения (2) и (4) относительно xA и xB соответственно.
Отметим, что метод касательных при любой реализации позволяет осуществлять разбиение на большее, чем три, число групп – путем дальнейшего построения аналогичных (3) отрезков и отыскания касательных к кривой, параллельных этим отрезкам.
Приведем доводы о тесной связи метода касательных с ПП. Согласно последнему вклад SA, который больше половины общего результата S, дают участники, количество NA которых меньше половины общего их числа N.?Тогда средняя скорость нарастания результата для этой доли участников равна SA/NA > (S/2)/(N/2) = S/N, где S/N – средняя скорость нарастания результата для всего множества участников. S/N равна угловому коэффициенту первого отрезка (рис. 1). Тогда абсцисса xA точки касания разделяет участников, дающих скорость роста результата большую или равную S/N и меньшую S/N. Под этой скоростью, даваемой одним участником, понимаем отношение вклада этого участника к единице (один участник). В силу ранжирования исходных данных эти две группы находятся по разные стороны от точки xA (абсцисса xA может относиться к группе A).
Так, метод касательных обеспечивает разделение участников на группы по признаку отношения величины скорости роста результата, даваемой участником, к средней скорости этого роста на рассматриваемом участке КЛ. Непосредственная реализация метода требует, как указывалось выше, либо построения КЛ и нахождения графически касательной к ней, либо применения какого-либо математического пакета, например MathCAD, при аналитическом подходе [6].
Мы предлагаем использовать возможности Microsoft EXCEL. Для обоснования этого подхода вновь обратимся к теореме Лагранжа, точнее к ее доказательству [8]. При этом доказательстве вводится функция
, (6)
то есть в рассматриваемом нами случае функция разности уравнения кривой Лоренца и уравнения отрезка (1). Из (6) сразу получаем, что F(a) = F(b) = 0. Поскольку функция F(x) непрерывна на [a; b], дифференцируема в (a; b) и принимает на концах [a; b] одинаковые значения, то для F(x) выполняется утверждение теоремы Ролля [8]: существует точка xA ? [a; b], в которой F(x) = 0. По построению функция f(x) монотонно возрастает, тогда возможны два вида графика функции F(x):
все участники делают различные вклады в результат, поэтому график F(x) имеет единственный максимум в точке хА – граничной для групп A и B;
есть совокупность участников, дающих одинаковые вклады; тогда график функции F(x) имеет горизонтальный участок (плато), ординаты точек которого превосходят ординаты всех остальных точек графика.
Однозначное разделение на группы методом касательных возможно при первом виде графика функции F(x); функции с такими графиками и будут представлены в примерах. Замечание по поводу второго вида графика функции F(x) приведем в конце данной статьи.
Таким образом, для реальных дискретных экспериментальных данных в Microsoft EXCEL необходимо находить максимумы функций вида (6), построенных на соответствующих интервалах.
Конкретный пример реализации метода касательных в MS EXCEL. Для него взяты тридцать первых позиций табл. 3.1 из [4]. Расчеты в нормированных величинах представлены на листе EXCEL (рис. 2). В столбцах B8:B38 и D8:D38 содержатся исходные данные (учтен также «нулевой» участник – строка B8:H8), в столбцах C8:C38 и E8:E38 – нормированные (общий результат в 117 040 использован при нормировке данных из столбца D8:D38). Определяем границу групп A и B.?Столбец G8:G38 содержит ординаты точек первого отрезка (1а) (угловой коэффициент K1 = 1 из ячейки G5), столбец H8:H38 – значения функции (6). Максимальная величина в этом столбце – в ячейке H16. Тогда, согласно приведенной выше интерпретации метода касательных, количество участников группы A составляет 26,67% (ячейка C16) от общего их числа; они дают 77,54% (ячейка F16) конечного результата, то есть xA = 26,67%, f(xA) = 77,54%. Теперь определяем границу групп B и C. В столбец I16:I38 помещены ординаты точек второго отрезка (3а) (угловой коэффициент K2 = = (1 – f(xA))/(1 – xA) = (1–0,7754)/(1–0,2667) ? 0,3063 из ячейки F5), в столбец J16:J38 – значения функции (6), максимальное значение – в ячейке I16. Таким образом, xB = 56,67% (ячейка C25), f(xB) = 93,31% (ячейка F25). Поэтому группа B содержит 56,67% – 26,67% = 30% участников и дает 93,31% – 77,54% = 15,77% общего результата. Группа C содержит 100% – 56,67% = 43,33% игроков и дает 100% – 93,31% = 6,69% от общего результата. Итак, получили разделение на группы:
Приведем еще один способ реализации метода касательных в MS EXCEL. Для этого обратимся к функции f(x). Исходя из определения, она может быть представлена в виде:
(0 ? u ? N), (7)
где ?(z) – вклад участника с номером z. Функция ?(z) – убывающая, так как предварительно проведено ранжирование вкладов по убыванию. Рассматриваем ненормированный интервал участников. Интегральное представление (7) возможно при достаточно большом их количестве. В противном случае в (7) вместо интегрирования применяется суммирование по номерам участников. На результате это не сказывается.
Для нахождения xA берем соотношение (2). Выпишем его, используя (7):
, так как f(0) = 0.
Величина = представляет
собой среднее значение ??(z)?1 функции вклада всех N участников.
Аналогично после отделения группы A находим xB, используя (4) и (7):
– среднее значение вкладов участников с номерами из (xA; N].
Исходя из вышеизложенного, реализация в MS EXCEL такова. Для всех участников, кроме «нулевого», находим среднюю величину вклада. К группе A отнесем игроков с вкладами, большими либо равными среднему. Для оставшихся участников вновь находим средний вклад. К группе B отнесем тех из них, чьи вклады больше либо равны второму среднему вкладу. После отделения групп A и B получаем группу C.?При необходимости разбиения на большее число групп процедуру нужно продолжить с оставшимися после выделения групп A и B участниками.
Продемонстрируем реализацию в Microsoft EXCEL указанного подхода (рис. 3). Используем те же исходные данные, что и в первом примере; они содержатся в ячейках C4:C33 и D4:D33. Нормированные величины помещены в ячейки B4:B33 и E4:E33; для нормирования значений из E4:E33 взят конечный результат в 117 040 (ячейка D34). Средний вклад по всем участникам находится в D35 (для нормированных данных – в E35). Представители группы A отражены в ячейках B4:B11 и составляют 26,67% от всех участников; эти игроки дают 77,54% от общего результата. Средний вклад для оставшихся после выделения группы A участников находится в D36 (для нормированных данных – в E36). Представители группы B (в ячейках B12:B20) составляют 56,67% – 26,67% = 30% от всех участников и дают 93,31% – 77,54% = 15,77% от общего результата. Тогда группа C содержит 100% – 56,67% = 43,33% участников и дает 100% – 93,31% = 6,69% от общего результата. На листе EXCEL группы выделены цветом с помощью опции «Условное форматирование».
Разбиение на подмножества, полученное при таком подходе, естественно совпадает с разбиением в результате первого подхода, поскольку оба – суть реализации в EXCEL одного и того же метода касательных. Отметим одну особенность последнего. Если кривая Лоренца имеет участок, параллельный первому отрезку (это соответствует второму виду графика функции F(x)), то метод касательных даст не одну точку (xA; yA), а весь этот участок. Такая же ситуация возможна и на шаге разделения групп B и C, а также на других шагах (если они будут предприняты). Это качество относят к недостаткам метода [5], так как оно не позволяет однозначно провести разделение на группы. Наличие прямолинейного участка на КЛ свидетельствует о присутствии нескольких игроков с равными вкладами. Решение об их включении (полном или частичном) или невключении в ту или иную группу должен принимать исследователь, воспользовавшись другим методом, например эмпирическим [4].
Таким образом, изучен метод касательных. Указан признак, согласно которому осуществляется разделение на группы при его применении. Представлена связь метода с эмпирическим принципом Парето, по результатам анализа предложены два подхода для реализации в MS EXCEL. Их легко применять на практике, поскольку они освобождают лицо, проводящее ABC-анализ, от необходимости осуществлять какие-либо графические построения или использовать продвинутые математические пакеты при аналитическом определении границ групп.
Статья поступила в редакцию 09.02.2016?г.

Литература
1. Гаджинский?А. М.?Логистика.?– М., 2012.
2. Стерлигова?А. Н.?Управление запасами широкой номенклатуры: с чего начать? // Логинфо. 2003, №12. С. 50–55; 2004, №1. С. 46–49.
3. Фишер А.?Методы выделения групп в АВС-анализе // http://logist.ru/articles/metody-vydeleniya-grupp-v-avs-analize.
4. Лукинский?В. С.?Логистика / В. С.?Лукинский, И. А.?Цвиринько, Ю. В.?Малевич.?– СПб., 2003.
5. Gudehus T.?Comprehensive Logistics, 2-nd edition / T.?Gudehus, H.?Kotzab.?– Berlin, Heidelberg. 2012.
6. Шмидт А. К вопросу о методах выделения групп при проведении ABC-анализа // Логистика. 2013, №8. С. 22–27.
7. Методы классификации в АВС-анализе // https://olegon.ru/showthread.php?t=23033.
8. Ильин?В. А.?Основы математического анализа. Ч. 1 / В. А.?Ильин, Э. Г.?Позняк.?– М., 1971.
9. Бородич?С. А.?Эконометрика: уч. пособие / 2-е изд., испр.?– Мн., 2004.

• Войдите или зарегистрируйтесь, чтобы оставлять комментарии