El Coeficiente de Gini y la Curva de Lorenz en Excel

Compatibilidad: Truco compatible con Excel 365Truco compatible con Excel 2016Truco compatible con Excel 2013Truco compatible con Excel 2010Truco compatible con Excel 2007

Cómo obtener y representar el Coeficiente de Gini y la Curva de Lorenz en Excel, para conocer la distribución de la riqueza.

La curva de Lorenz es una representación gráfica para representar la distribución relativa (concentración del ingreso y la riqueza existente en un país, ciudad o grupo) para estudiar las desigualdades.

En ella se presenta en el eje de abscisas el porcentaje de trabajadores y en el de ordenadas el porcentaje acumulado de sus salarios respecto del total de la masa salarial. El desarrollo se debe a Max O. Lorenz en 1905.

El Coeficiente de Gini y la Curva de Lorenz en Excel

El Índice o coeficiente de Gini es un método desarrollado por Corrado Gini, para obtener la desigualdad que representa la distancia entre la curva de Lorenz y la bisectriz, que correspondería a una distribución perfecta.

El coeficiente de Gini es un número entre 0 y 1, donde 0 corresponde con la igualdad perfecta (todos con la misma riqueza o ingresos) y el valor 1 se corresponde con la desigualdad perfecta (uno tiene toda la riqueza o los ingresos y los demás ninguno).

El índice de Gini es el coeficiente de Gini expresado en porcentaje.

Deseamos analizar el Coeficiente de Gini y la Curva de Lorenz en Excel para los sueldos de los trabajadores de nuestra empresa.

En la columna A se encuentran los grupos de trabajadores, en la columna B se muestra el sueldo mensual de cada grupo de trabajador y en la columna C la población (frecuencia) o el número de trabajadores de cada clase e ingresos.

ABC
1ClaseIngresosPoblación
2
3Grupo 16502
4Grupo 28753
5Grupo 31.00012
6Grupo 41.5007
7Grupo 52.1004
8Grupo 63.5002
9Grupo 76.2001

La columna B deberá de estar ordenada para la población que se deseamos estudiar.

  • ACUMULADO DE LA POBLACIÓN

Calculamos el acumulado de la población para cada una de las clases.

  1. Ir a: Celda D1 / Escribir: Acumulado de la población. Encabezado de la columna.

  2. Ir a: Celda D3 / Escribir:

    = C3
  3. Ir a: Celda D4 / Escribir:

    = SUMA ($C$3:C4)
  4. Copiar la celda D4.

  5. Pegar en el rango: D5:D9.

Columna D con el valor Acumulado de la población
  • PRODUCTO ENTRE INGRESOS Y POBLACIÓN

Calculamos el producto entre el ingreso y población para cada una de las clases.

  1. Ir a: Celda E1 / Escribir: Producto entre ingresos y población. Encabezado de la columna.

  2. Ir a: Celda E3 / Escribir:

    = B3*C3
  3. Copiar la celda E3.

  4. Pegar en el rango: E4:E9.

Columna E con el valor del producto entre ingresos y población
  • ACUMULADO DEL PRODUCTO ENTRE INGRESOS Y POBLACIÓN

Calculamos el acumulado del producto entre ingresos y población para cada una de las clases.

  1. Ir a: Celda F1 / Escribir: Acumulado del producto entre ingresos y población. Encabezado de la columna.

  2. Ir a: Celda F3 / Escribir:

    = E3
  3. Ir a: Celda F4 / Escribir:

    = F3 + E4
  4. Copiar la celda F4.

  5. Pegar en el rango: F5:D9.

Columna F con el valor acumulado del producto entre ingresos y población
  • VALOR DE P
  1. Ir a: Celda G1 / Escribir: Valor de P. Encabezado de la columna.

  2. Ir a: Celda G2 / Escribir:

    0
  3. Ir a: Celda G3 / Escribir:

    =D3/SUMA($C$3:$C$9)
  4. Copiar la celda G3.

  5. Pegar en el rango: G4:G9.

Columna G con el valor P
  • VALOR DE q
  1. Ir a: Celda H1 / Escribir: Valor de q. Encabezado de la columna.

  2. Ir a: Celda H2 / Escribir:

    0
  3. Ir a: Celda H3 / Escribir:

    =F3/$F$9
  4. Copiar la celda H3.

  5. Pegar en el rango: H4:G9.

Columna H con el valor q
  • DIFERENCIA ENTRE P Y q
  1. Ir a: Celda I1 / Escribir: Diferencia entre P y q. Encabezado de la columna.

  2. Ir a: Celda I3 / Escribir:

    =G3-H3
  3. Copiar la celda I3.

  4. Pegar en el rango: I4:G9.

Columna I con el valor diferencia enter P y q
  • COEFICIENTE O ÍNDICE DE GINI

El coeficiente de Gini se calcula como una proporción de las áreas en el diagrama de la curva de Lorenz.

  1. Ir a: Celda G10 / Escribir:

    =SUMA(G2:G9)
  2. Ir a: Celda I10 / Escribir:

    =SUMA(I2:I9)
  3. Ir a: Celda A12 / Escribir: Gini. Como encabezado.

  4. Ir a: Celda B12 / Escribir:

    =I10 / G10

    O también = (I10 / G10)*100 para obtener el porcentaje.

Hoja Excel con celda B12 con el valor Gini
  • GRÁFICO O CURVA DE LORENZ
  1. Seleccionar el rango G2:H9. Valores de P y de q.

  2. Ir a: Cinta de Opciones / Pestaña: Insertar / Grupo: Gráficos / Dispersión / Dispersión con líneas suavizadas y marcadores. Insertará el gráfico.

    Gráfico o curva de Lorenz con los valores Excel
  3. Seleccionar el área del gráfico. Para editar el origen de datos.

  4. Ir a: Cinta de Opciones / Pestaña: Diseño (Herramientas de gráficos) / Grupo: Datos / Seleccionar datos. Mostrará ventana de seleccionar origen de datos.

    Ventana seleccionar origen de datos para el gráfico Excel
  5. Ir a: Entradas de leyenda (Series) / Pulsar el botón Agregar. Mostrará ventana de modificar serie.

    Ventana modificar serie para gráfico Excel
  6. Ir a: Valores X de la serie / Escribir:

    ={0\1}
  7. Ir a: Valores Y de la serie / Escribir:

    ={0\1}
  8. Pulsar el botón Aceptar. Cierra ventana de modificar serie.

  9. Pulsar el botón Aceptar. Cierra ventana de seleccionar origen de datos.

    Resultado del gráfico con el coeficiente de Gini y la Curva de Lorenz
  10. Aplicar el diseño y formato deseado al gráfico.

El Coeficiente de Gini y la Curva de Lorenz en Excel con diseño y formato
Nivel de dificultad: Básico Excel Básico

14 comentarios en “El Coeficiente de Gini y la Curva de Lorenz en Excel

Deja una respuesta

Tu dirección de correo electrónico no será publicada.

Este sitio usa Akismet para reducir el spam. Aprende cómo se procesan los datos de tus comentarios.