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

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

Deja un comentario

Tu dirección de correo electrónico no será publicada. Los campos obligatorios están marcados con *