CÓMO CALCULAR EL COEFICIENTE DE GINI EN EXCEL

Hola. En este post te voy a enseñar cómo calcular el Coeficiente de Gini y el Índice de Concentración (IC) en un contexto de salud, mediante una tabla elaborada en Excel. Ambos indicadores desde luego, los desarrollaré a través de un ejercicio cuyos datos y nombres son enteramente ficticios.

Paso 1: Planteamiento.
Lo primero que debes hacer es tener un planteamiento claro. Por ejemplo:
“Calcular en Excel, el Coeficiente de Gini y el Índice de Concentración del año 2015 de un Estado que llamaremos El Dorado, para describir cómo se distribuyen las muertes infantiles (Def. < 1 año) con respecto a la población de nacidos vivos registrados (NVR), teniendo como unidad geográfica de análisis, el Municipio.
Paso 2: Elaboración de la Tabla de Datos.
Diseña una tabla en Excel de 12 columnas: A, B, C, D, E, F, G, H, I, J, K y L, y luego escribe sus encabezados, como está en la figura.
Paso 3: Preparación de las Columnas A, B, C, D y E.
La información de estas columnas es imprescindible para realizar los cálculos.
Columna A: En la celda A2 coloca el tipo de unidad de análisis (País, Estado, Región, Municipio, etc.). En el ejercicio, es el Municipio. En la celda A3 escribes 0, y a partir de A4 escribe el nombre de todas y cada una de las unidades de análisis, que en el ejemplo son 5. Cuando las unidades de análisis pasan de 10, es conveniente agregarlas en estratos mediante la técnica de medidas de posición para series no agrupadas como los cuartiles y quintiles. 
Columna B: esta columna te será de utilidad para calcular el Índice de Concentración y la Curva de Concentración. En la celda B2 escribes el nombre del Indicador Socioeconómico, (pobreza, condiciones de la vivienda, alfabetismo, etc.) en el ejercicio es el % de Viviendas Sin Acceso al Agua Potable (%VSAAP). En B3 escribes 0, y a partir de B4 rellena las celdas con los valores del indicador socioeconómico para cada una de las unidades.
Columna C: En la celda C2 escribes el nombre del Indicador sanitario, (proporción, porcentaje o tasa) en el ejercicio es la Tasa de Mortalidad Infantil (TMI). En la celda C3 también escribes 0, y a partir de C4 transcribe los valores del indicador de salud para cada una de las unidades.
Columna D: En la celda D2 va el nombre de la Población X, para que no tengas dudas,  escribe textualmente el denominador del indicador de salud que estás utilizando. Como en el ejercicio el Indicador de salud es la Tasa de Mortalidad Infantil (TMI), el denominador son  los Nacidos Vivos Registrados (NVR). Igualmente, en la celda D3 anota 0, y a partir de D4 en adelante transcribe el número de NVR para cada una de las unidades de análisis.
Columna E: En la celda E2 escribe el nombre de la variable Y, que es el numerador del indicador de salud que estás utilizando. Como en el ejercicio el Indicador de salud es la Tasa de Mortalidad Infantil (TMI), entonces el numerador corresponde a  las Defunciones en menores de 1 año (Def. < 1 año). En la celda E3 pones 0, y a partir de E4 en adelante copia los valores de las Def. < 1 año para cada una de las unidades.
En la última fila van los totales; en el ejemplo de A9 a E9.

Paso 4: Ordenar los datos según el indicador de salud.

Cuando se calcula el Coeficiente de Gini y la Curva de Lorenz para las desigualdades de salud, deben ordenarse todos los datos según el Indicador sanitario, de la peor situación a la mejor; es decir de la mayor TMI a la más baja. Fíjate que en la tabla, está resaltado en verde de A4 a E8 (no incluyas los totales) para que la selecciones con el ratón y en la opción ordenar, lo hagas de mayor a menor por la columna C.

Presta atención que si en la ventana Ordenar aparece en la esquina superior derecha seleccionado Mis datos tienen encabezados, debes quitar la selección para que te aparezcan las columnas en Ordenar por.

Ahora ya están ordenados de mayor a menor según la TMI.

Ya terminaste con los 4 primeros pasos del procedimiento. En las siguientes columnas vas a trabajar con fórmulas que podrás aplicar fácilmente en cualquier versión de excel. Trabaja con cifras no menores de 2 decimales.
Paso 5: Preparación de las Columnas F y G:
En estas columnas vas a calcular las frecuencias relativas en proporciones de X y Y, cuyos valores absolutos estan en las columnas D (NVR) y E (Def. < 1 año), respectivamente.
Columna F: En la celda F2 escribe NVR. Luego de F3 en adelante lo que se desea es calcular la proporción  de NVR de cada Municipio en relación al total del Estado.  Para ello usa la barra de fórmulas de Excel de la siguiente manera: selecciona la celda F3 y escribe con el teclado =D3/D9, presiona enter y te aparecerá el valor 0,00. Luego en F4 escribes =D4/D9 presiona enter y te aparecerá el valor 0,16 y así sucesivamente hasta la celda F8 cuyo valor debe ser 0,34. Al final la suma de las proporciones debe ser igual a 1. 

Columna G: En la celda G2 escribe Def. < 1 año. Luego de G3 en adelante lo que se desea es calcular la proporción de Def. < 1 año de cada municipio en relación al total del estado. Para ello usa la barra de fórmulas de Excel de la siguiente manera: selecciona la celda G3 y escribe con el teclado =E3/E9, presiona enter y te aparecerá el valor 0,00. Luego en G4 escribes =E4/E9 presiona enter y te aparecerá el valor 0,29, y así sucesivamente hasta la celda E8 cuyo valor debe ser 0,15. Al final la suma de las proporciones debe ser igual a 1.

Paso 6: Preparación de las Columnas H e I:

En estas columnas vas a calcular las Frecuencias Acumuladas de las proporciones simples de X y Y, con los valores de las columnas F (Prop. Simples de X, NVR) y G (Prop. Simples de Y, Def. < 1 año), respectivamente.

Columna H: En la celda H2 escribe NVR. Luego de H3 en adelante lo que se desea es calcular las proporciones acumuladas de NVR de cada municipio.  Para ello usa la barra de fórmulas de Excel de la siguiente manera: selecciona la celda H3 y escribe con el teclado =F3, presiona enter y te aparecerá el valor 0,00. Luego en H4 escribe la fórmula =F4+H3. Con el ratón selecciona H3 y pega la fórmula arrastrando hasta H8, cuyo valor debe ser 1,00. La celda H9 la dejas en blanco.

Columna I: En la celda I2 escribe Def. < 1 año. Luego de I3 en adelante lo que se desea es calcular las proporciones acumuladas de Def. < 1 año de cada municipio ordenado según la TMI.  Para ello usa la barra de fórmulas de Excel de la siguiente manera: selecciona la celda I3 y escribe con el teclado =G3, presiona enter y te aparecerá el valor 0,00. Luego en I4 escribe la fórmula =G4+I3. Con el ratón selecciona I3 y pega la fórmula arrastrando hasta I8 cuyo valor debe ser 1,00. La celda I9 la dejas en blanco.

Paso 7: Preparación de las Columnas J, K y L.
En estas columnas vas a despejar los valores de los componentes de la fórmula de Brown para el cálculo de Gini.

Columna J (Xi+1–Xi): En la celda J2 escribe NVR. Luego en J3 escribes la fórmula =H3, te arrojará el valor 0,00. Luego Selecciona J4 y escribe con el teclado =H4-H3, presiona enter y te aparecerá el valor 0,16. Con el ratón selecciona J4 y pega la fórmula arrastrando hasta J8 cuyo valor debe ser 0,34. La celda J9 la dejas en blanco.

Columna K (Yi+1+Yi): En la celda K2 escribe Def. < 1 año. Luego en K3 escribes la fórmula =I3, te arrojará el valor 0,00. Selecciona K4 y escribe con el teclado =I4+I3, presiona enter y te aparecerá el valor 0,29. Con el ratón selecciona la celda K3 y pega la fórmula arrastrando hasta K8 cuyo valor debe ser 1,85. La celda k9 la dejas en blanco.

Columna L (Xi+1–Xi)(Yi+1+Yi): En la celda L2 escribe J * K. Luego desde L3 hasta L8 van a contener el resultado de multiplicar cada celda de la columna J por su correspondiente de la misma fila de la columna K. Nuevamente has uso de la barra de fórmulas de Excel, escribe en L3 =J3*K3, te dará el valor de 0,00. Con el ratón selecciona L3 y pega la fórmula arrastrando hasta L8 cuyo valor debe ser 0,34. Finalmente en L9 has la sumatoria escribiendo =SUMA(L3:L8) o también =L3+L4+L5+L6+L7+L8, el valor es igual a 1,30.

Paso 8: Calcular el Coeficiente de Gini mediante la fórmula de Brown.

Como ya tenemos los componentes de la fórmula de Gini pasamos a su cálculo. Vas a la celda C11 o cualquiera que tu decidas; escribe la fórmula =ABS(1-L9). Como Gini solo toma valores positivos de 0 a 1, usas la función ABS de Excel que devuelve el valor absoluto de un número, sin importar el signo negativo.

En el ejercicio Gini toma un valor de 0,30. ¿Qué interpretación le darías tu a ese resultado?. No puedes darle ninguna interpretación. El valor 0,30 es sólo una cifra fría. Sencillamente, porque el Gini, como cualquier otro indicador de salud, tendrá significado para ti, y te brindará información valiosa, si lo evalúas en el contexto correcto. En este sentido, el Gini = 0,30 será importante si se tiene otro valor, para la misma variable (Defunciones < 1 año) y en el mismo estado, pero de otros años, pues servirá para conocer cómo se ha comportado en el tiempo; o también si te propones cotejarlo con unidades geográficas diferentes.
Sin embargo, cuando no existen valores previos del Gini, puedes compararlo con un valor teórico convencional de 0,20, el cual es considerado por los autores como bastante equitativo. En nuestro caso 0,30 es mayor al valor de 0,20; por lo que únicamente podemos concluir que la distribución de las Defunciones en menores de un año en la población de NVR del Estado El Dorado durante el año 2.015, tuvo un comportamiento desigual; concentrándose dichas defunciones en los Municipios con las mayores Tasas de Mortalidad Infantil (TMI). Más información la puedes obtener a través del análisis de la Curva de Lorenz. 

Paso 9: Calcular el Índice de Concentración mediante la fórmula de Brown. 

Cuando se calcula el Índice y la Curva de Concentración para las desigualdades de salud, deben ordenarse todos los datos según el Indicador socioeconómico y no según el indicador de salud, de la peor situación a la mejor. En el ejercicio el indicador socioeconómico es el % de Viviendas sin Acceso al Agua Potable (%VSAAP), y obviamente la peor situación es el valor más alto, y la mejor es el más bajo. Fíjate que en la tabla, está resaltado en verde de A4 a E8 para que la selecciones con el ratón y en la opción ordenar, lo hagas de mayor a menor por la columna B. 

Si en la ventana Ordenar aparece seleccionado en la esquina superior derecha Mis datos tienen encabezados, tienes quitar la selección con el ratón para que te aparezcan las columnas en Ordenar por.

 Ahora están ordenados de mayor a menor según el indicador %VSAAP.

El resto del procedimiento es el mismo que para el Gini. Deja todo lo demás igual en la tabla. Entonces vas a la celda C11 o cualquiera que tu decidas; escribe la fórmula =1-L9. Observa que la única diferencia con el Coeficiente de Gini, es que es que para calcular el IC debes eliminar la función ABS, porque este indicador si acepta valores negativos y positivos, de -1 a +1.

En el ejercicio el Índice de Concentración toma un valor de -0,28. ¿Qué interpretación le darías tu a ese resultado?. Igual que para el Coeficiente de Gini, no puedes darle ninguna interpretación. El valor 0,28 es sólo una cifra fría. Sencillamente, porque el Índice de Concentración, como cualquier otro indicador de salud, tendrá significado para ti, y te brindara información valiosa, si lo evalúas en el contexto correcto. En este sentido, IC= -0,28 será importante si se tiene otro valor, para la misma variable (Defunciones < 1 año) y en el mismo Estado, pero en otros años, pues servirá para evaluar cómo se ha comportado en el tiempo; o también si te propones cotejarlo con unidades geográficas diferentes.

Sin embargo, cuando no existen valores previos del IC, puedes compararlo con un valor teórico convencional de -0,20 o +0,20 (dependiendo si el IC es negativo o positivo, respectivamente), el cual es considerado por los autores como bastante equitativo. En nuestro caso -0,28 es mayor en términos absolutos al valor -0,20; por lo que únicamente podemos concluir, que la distribución de las Defunciones en menores de un año en la población de NVR del Estado El Dorado durante el año 2.015, tuvo un comportamiento desigual; concentrándose dichas defunciones en los Municipios de ese Estado con los mayores Porcentajes de Viviendas Sin Acceso al Agua Potable (%VSAAP). Más información la puedes obtener a través del análisis de la Curva de Concentración.

Haz click en mi publicación Cómo dibujar la Curva de Lorenz y Curva de Concentración en Excel, para completar el ejercicio con los gráficos.

Si te parece muy tedioso descarga la plantilla en excel y te será mas fácil, haciendo click en mi post PLANTILLA DE EXCEL PARA GINI Y LORENZ EN SALUD

¿Qué te ha parecido la publicación?

Si tienes alguna duda u observación que hacer, te agradecería envíes tu comentario para que entre todos mejoremos el contenido del post.