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

Как построить кривую в Excel

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

  • Как видим, после выполнения вышеуказанных действий кривая Лоренца также отобразится на листе Excel.
  • Построение кривой Лоренца и линии равенства в Экселе производится на тех же принципах, что и построение любого другого вида диаграмм в этой программе. Поэтому для пользователей, которые овладели умением строить диаграммы и графики в 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 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 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

    Для оцінки рівня нерівності між різними верствами населення суспільства часто використовують криву Лоренца і похідний від неї показник — коефіцієнт Джині. За допомогою них можна визначити, наскільки великий соціальний розрив в суспільстві між найбагатшими і найбіднішими верствами населення. За допомогою інструментів програми Excel можна значно полегшити процедуру побудови кривої Лоренца. Давайте, розберемося, як в середовищі Ексель це можна здійснити на практиці.

    Використання кривої Лоренца

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

    Читайте также  Перемещение ячеек относительно друг друга в Microsoft Excel

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

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

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

    Рівень нерівності в суспільстві визначається за допомогою індексу Джіні, який може варіюватися від до 1. Він ще називається коефіцієнтом концентрації доходів.

    Побудова лінії рівності

    Тепер давайте на конкретному прикладі подивимося, як створити лінію рівності і криву Лоренца в Ексель. Для цього використовуємо таблицю кількості населення розбитого на п’ять рівних груп (по 20%), які підсумовуються в таблиці по наростаючій. У другій колонці цієї таблиці представлена ​​величина національного доходу в процентному співвідношенні, яка відповідає певній групі населення.

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

      Переходимо у вкладку «Вставка». На лінії в блоці інструментів «Діаграми» тиснемо на кнопку «Точкова». Саме цей тип діаграм підійде для нашої задачі. Далі відкривається список підвидів діаграм. Вибираємо «Точкова з гладкими кривими і маркерами».

    Після виконання даної дії відкривається порожня область для діаграми. Це сталося тому, що ми не вибрали дані. Для того, щоб внести дані і побудувати графік, натискаємо правою кнопкою миші по порожній області. В активувати контекстному меню вибираємо пункт «Вибрати дані . ».

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

    Запускається вікно зміни ряду. В поле «Ім’я ряду» записуємо то найменування діаграми, яке хочемо їй привласнити. Воно може також розташовуватися на аркуші і в цьому випадку потрібно вказати адресу комірки його знаходження. Але в нашому випадку легше просто вписати назву вручну. Дамо діаграмі найменування «Лінія рівності».

    В поле «Значення X» слід вказати координати точок діаграми по осі X. Як ми пам’ятаємо, їх буде всього дві: і 100. Записуємо дані значення через крапку з комою в даному полі.

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

    Після того, як всі зазначені дані внесені, тиснемо на кнопку «OK».

    Після цього ми повертаємося до вікна вибору джерела даних. У ньому теж слід натиснути на кнопку «OK».

  • Як бачимо, після вищевказаних дій лінія рівності буде побудована і відобразиться на аркуші.
  • Створення кривої Лоренца

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

      Натискаємо правою кнопкою миші по області діаграми, на якій вже розташована лінія рівності. У запустити меню знову зупиняємо вибір на пункті «Вибрати дані . ».

    Знову відкривається вікно вибору даних. Як бачимо, серед елементів вже представлено найменування «Лінія рівності», але нам потрібно внести ще одну діаграму. Тому тиснемо на кнопку «Додати».

    Знову відкривається вікно зміни ряду. Поле «Ім’я ряду», як і в минулий раз, заповнюємо вручну. Сюди можна вписати найменування «Крива Лоренца».

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

    В поле «Значення Y» заносимо координати комірок стовпчика «Сума національного доходу». Робимо це за тією ж методикою, за якою вносили дані в попереднє поле.

    Після того, як всі вищезгадані дані внесені, тиснемо на кнопку «OK».

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

    Побудова кривої Лоренца і лінії рівності в Ексель проводиться на тих же принципах, що і побудова будь-якого іншого виду діаграм в цій програмі. Тому для користувачів, які оволоділи вмінням будувати діаграми і графіки в Excel, дана задача не повинна викликати великих проблем.

    Моделирование. Построение кривой Лоренца
    план-конспект урока по обществознанию (8 класс) на тему

    Описан интегрированный урок Обществознание+Интформатика, на котором ученики

    · Использованием электронных таблиц для построения функции Лоренца

    · дополнительными возможностями ЭТ – диаграммами (графиками),

    · типами диаграмм в ЭТ,

    · порядком вставки диаграмм в ЭТ,

    · способами изменения и форматирования диаграмм,

    Скачать:

    Вложение Размер
    конспект урока информатики 560.16 КБ
    инструкция ученикам для работы в ЭТ 560.73 КБ
    задача, решаемая учениками на уроке 2.49 МБ
    решённая задача (для учителя) 58.5 КБ

    Предварительный просмотр:

    Урок ИКТ и обществознания

    На уроке Обществознания изучены функции спроса и предложения

    «Моделирование. Построение кривой Лоренца»

    1. Познавательная: познакомить учащихся с
    • Использованием электронных таблиц для построения функции Лоренца
    • дополнительными возможностями ЭТ – диаграммами (графиками),
    • типами диаграмм в ЭТ,
    • порядком вставки диаграмм в ЭТ,
    • способами изменения и форматирования диаграмм,
    1. Развивающая:
    • учить логически мыслить, выделять главное, ставить и решать проблемы
    • учить работать по инструкции.
    1. Воспитательная:
    • воспитывать аккуратность, внимательность, вежливость и дисциплинированность.

    После этого урока ученики смогут: создавать диаграммы и графики, форматировать и редактировать их.

    Тип урока: формирование новых знаний, навыков и умений.

    Оборудование: Мультимедийная система, пакет Microsoft Excel 2013.

    1. Организационный момент
    2. Актуализация опорных знаний:
    • Расскажите о способах выделения ячеек и диапазонов в ЭТ,
    • Как можно изменить представление числовых данных в ячейках?
    • Что такое автозаполнение?
    • Какие могут быть ошибки в формулах, как их исправить?
    1. Мотивация учебной деятельности учеников:
    • Диаграммы и графики позволяют наглядно представлять табличные данные и проводить быстрый визуальный анализ этих данных.
    1. Объявление темы и ожидаемых учебных результатов.
    2. Предоставление необходимой теоретической информации

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

    Сегодня мы решим задачу экономическую задачу.

    Задача: « Пусть в Тридевятом царстве проживают только 5 граждан: Иванушка-дурачок, Василиса Премудрая, Баба Яга, Кощей Бессмертный и Андрей-стрелок. При этом доходы между ними распределены следующим образом: Иванушка-дурачок получает в месяц 50 рублей, Андрей-стрелок – 75 рублей, Баба Яга – 125 рублей, Василиса Премудрая – 250 рублей и Кощей Бессмертный – 500 рублей.

    Постройте кривую Лоренца для этого царства.

    Решение задачи ВРУЧНУЮ приведено в ПРИЛОЖЕНИИ1. Попробуем решить данную задачу, используя электронные таблицы.

    1. Постройте таблицу на листе Excel как показано ниже:

    Далее начнём заполнять таблицу, используя формулы в электронных таблицах:

    В ячейке E8 -формула сумма ячеек с E2 по E7 .

    В ячейке F3 внести какую долю (в процентах) от совокупного дохода получает каждый из жителей царства, ввести формулу =E3/$E$8 . Формулу распространить до F7

    В ячейках G3 — G7 , рассчитываем долю нарастающим итогом

    В ячейку G3 какой процент общего дохода внес Иванушка-дурачок, по формуле =F3 , в ячейку G4 вводим формулу =G3+F4 , далее формулу распространяем до ячейки G7.

    В итоге должна получиться таблица:

    1. На листе Excel построить графики функций Лоренца
      на Оси Х должно быть заданы числа, определяющие
      на Оси у должно быть заданы числа, определяющие % от общего дохода
    2. Требования к графику функций:

    Выделить диапазон ячеек G2:G7 и по нему построить график:

    Вкладка Вставка → График → График (показано на рисунке ниже)

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

    Выделить график и далее все данные выбираются во вкладке КОНСТРУКТОР → Выбрать данные. Откроется окна

    • Подписи горизонтальной оси (категории)
      (Конструктор → Выбрать данные → Подписи горизонтальной оси (категории) → Изменить) и в открывшемся окошке выбрать диапазон D2:D7
    • Элементы легенды
      Задать для легенды имена рядов (Конструктор → Выбрать данные → Элементы легенды → ряд1 → Изменить и в открывшемся окошке выбрать G1)
    1. Подписать ОСЬ ГОРИЗОНТАЛЬНУЮ (КАТЕГОРИЙ): (Отражает % от количества граждан)

    Внешний вид оси Х задается в Формате оси ( открывается щелчком по оси правой кнопкой мыши):

    • Положение оси → совпадает с делениями
    • Добавить промежуточные и основные линии сетки
    • Добавить линию оси: цвет-чёрный, ширина – 1пт.
    1. Подписать ОСЬ ВЕРТИКАЛЬНУЮ: (Отражает Процент от общего дохода)

    Внешний вид оси Y задается в Формате оси ( открывается щелчком по оси правой кнопкой мыши):

    • Добавить промежуточные и основные линии сетки
    • Добавить название оси – Процент от общего дохода (размер 16, начертание полужирное)
    • Добавить линию оси: цвет-чёрный, ширина – 1пт.
    1. Положение ЛЕГЕНДЫ вверху.
    1. СДЕЛАТЬ ПОДПИСИ (график должен быть выделен)

    Во вкладке КОНСТРУКТОР → Добавить элемент диаграммы → Название диаграммы → Над диаграммой → в строке формул написать название «Кривая Лоренца»

    Название ГОРИЗОНТАЛЬНОЙ ОСИ :

    Во вкладке КОНСТРУКТОР → Добавить элемент диаграммы → Название осей → Основная горизонтальная → в строке формул написать название « % от количества граждан»

    Название ВЕРТИКАЛЬНОЙ ОСИ:

    Во вкладке КОНСТРУКТОР → Добавить элемент диаграммы → Название осей → Основная вертикальная → в строке формул написать название « Процент от общего дохода»

    . ВНИМАНИЕ . ВНЕШНИЙ ВИД графика показан в приложении к заданию

    ПОСЛЕ ВЫПОЛНЕНИЯ ЗАДАНИЯ ГРАФИК РАЗМЕСТИТЬ ТАК, ЧТОБЫ ТАБЛИЦА И ГРАФИК ПОМЕЩАЛИСЬ НА ОДНОМ ЛИСТЕ А4 КНИЖНОЙ ОРИЕНТАЦИИ.

    Как сделать кривую ABC в Excel

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

    Разве вы не знаете кривую ABC? Вы не проводите сравнение, чтобы контролировать свой запас? Знайте, что использование этого инструмента очень важно для снижения затрат вашего бизнеса.

    Происхождение кривой ABC — метод, основанный на теориях итальянского экономиста Вильфредо Парето. Речь идет о разделении предметов вашего запаса на группы 3 в соответствии с их важностью: A, B или C. В группе A наиболее важные предметы. В группе B предметы промежуточного уровня. В группу С входят предметы с низкой релевантностью.

    Управление запасами с кривой ABC в Excel

    Мы шаг за шагом разработали, как сделать лист кривой ABC в Excel. Следовать:

    Создание первых столбцов 4

    Шаг 1: Сначала мы создадим четыре столбца и введем значения. Это:

    • Первый столбец: A1-A11: это код для каждого продукта в вашем инвентаре.
    • Вторая колонка: B1-B11: Количество на складе.
    • Третий столбец: C1-C11: Стоимость единицы каждого продукта.
    • Четвертая колонка: D1-D11: Общая стоимость продуктов.

    Шаг 2: Чтобы заполнить данные в четвертом столбце, умножьте значение столбца B на значение столбца C для соответствующего продукта. Например: чтобы получить значение столбца D2, используйте формулу = B2 * C2. Следовательно, количество продуктов, умноженное на стоимость единицы, приведет к сумме, потраченной в вашей компании на этот товарный запас.

    Шаг 3: После этого поместите значения в столбце D2 в порядке убывания.

    Шаг 4: По очереди в ячейке D12 используется формула = SUM (D2: D11). Значение будет представлять собой сумму суммы, потраченной на текущий инвентарь вашей компании.

    Вставка%

    Шаг 5: Создайте столбец% в ячейке E1. Значения ячеек E2 в E11 представляют собой процент, в котором каждое значение столбца D (ячейки D1 — D11) относится к значению ячейки D12.

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

    • Ячейка E2: = D2 / D12
    • Ячейка E3: = D3 / D12
    • Ячейка E4: = D4 / D12
    • Ячейка E5: = D5 / D12
    • Ячейка E6: = D6 / D12
    • Ячейка E7: = D7 / D12
    • Ячейка E8: = D8 / D12
    • Ячейка E9: = D9 / D12
    • Ячейка E10: = D10 / D12
    • Ячейка E11: = D11 / D12

    Шаг 6: После этого выберите ячейки E2 до E11 и нажмите Home / Number /%.

    Шаг 7: По очереди в ячейке E12 используется формула = SUM (E2: E11). Если результат равен 100%, рабочий лист верен до сих пор. В противном случае, проверьте шаг за шагом снова, чтобы заметить ошибку и исправить ошибку.

    Вставка% Накопленная колонка

    Шаг 8: Создайте столбец% накопления в ячейке F1. Значения ячеек F2 для F11 представляют сумму процентов в столбце%.

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

    • Ячейка F2: = E2
    • Ячейка F3: = F2 + E3
    • Ячейка F4: = F3 + E4
    • Ячейка F5: = F4 + E5
    • Ячейка F6: = F5 + E6
    • Ячейка F7: = F6 + E7
    • Ячейка F8: = F7 + E8
    • Ячейка F9: = F8 + E9
    • Ячейка F10: = F9 + E10
    • Ячейка F11: = F10 + E11

    Шаг 9: После этого выберите ячейки F2 вплоть до F11 и нажмите Home / Number /%.

    Вставка графика с кривой ABC

    Шаг 10: Выберите значения ячеек A2 до A11.

    Шаг 11: Удерживая нажатой клавишу CTRL, также выберите значения ячеек F2 для F11.

    Шаг 12: Выберите диаграмму: Вставка / График / Строки / Линия 2d / Строки.

    Шаг 13: Выберите заголовок серии 1 и нажмите «УДАЛИТЬ».

    Шаг 14: Процентная ось находится в значениях между 0 и 120%. Чтобы оставить значения 0 и 100%, выберите ось и нажмите «Ось форматирования».

    Шаг 15: В разделе «Оси» выберите «FIX to Minimum» и «Maximum». Уважайте их в значениях 0,0 и 1,0. Нажмите ЗАКРЫТЬ.

    Через эти шаги рабочий лист будет готов. Вы можете изменить цвет и оставить его как угодно.

    Анализ данных таблицы с кривой ABC

    Принимая во внимание группы 3 A, B и C, мы выбрали ссылку 70-20-10.

    Таким образом, группа A представляет 70% общих затрат, группу B 20% и группу 10%.

    С учетом данных этой таблицы, группы представлены:

    • ГРУППА A: продукты 3 (коды 9, 4 и 10);
    • ГРУППА B: продукты 2 (коды 5 и 8);
    • ГРУППА C: продукты 5 (коды 1, 3, 6, 7 и 2).
    • 30% продуктов представляет собой 70% от общих затрат;
    • 20% продуктов представляет собой 20% от общих затрат;
    • 50% продуктов представляет собой 10% от общих затрат.

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

    Вам понравилась таблица? Помог ли анализ электронных таблиц с помощью кривой ABC в Excel помочь вам контролировать свои запасы и делать правильные инвестиции? Оставьте свои комментарии об опыте работы с этим листом.