Dashboard de recursos humanos en Excel: ingresos y rotación de personal

¡Hola! Un placer tenerte de nuevo por aquí. Hace poco decidimos armar un dashboard con algunos de los gráficos diseñados en este blog, el resultado fue un cuadro de mando sencillo y práctico.

El caso: los indicadores de personal de una universidad

Para este caso, vamos a suponer que seremos parte de la reunión semestral donde se analizará el rendimiento de algunos indicadores, por eso nuestro jefe; el gerente de Recursos Humanos de la Universidad Privada de la Educación; nos solicita reflejar la situación del staff contratado y cesado a lo largo de este año, tú como experto en dashboards le propones desarrollar uno donde reflejes la rotación del personal y segmentar la información por facultad, sexo, cargo, mes de contratación y mes de cese.

Revisa y prepara la base de datos

Ante todo, debemos familiarizarnos con la base de datos, esto ayudará a comprender, diseñar e identificar algunos errores en la base de datos (espacios en blanco, espacios innecesarios, etc).

Como vemos, en las columnas Fecha y Mes de cese existen espacios vacíos, sin embargo, para este caso solo son registros de la fecha y mes exacto del cese de contrato de algunos empleados, entonces en este caso no necesitamos depurar errores en la tabla.

No podríamos comenzar a realizar los distintos indicadores desde aquí, antes debemos hacer algunos cálculos y/o armar tablas adicionales que faciliten el análisis de la información.

Crea las tablas dinámicas de cada segmentación

En este caso, vamos apoyarnos de tablas dinámicas para consolidar y resumir la información:

Creamos una hoja nueva y cambiamos el nombre por “Análisis” –> Insertamos una tabla dinámica –> Seleccionamos todo el rango de la BD y elegimos B4 como la celda para ubicar la tabla dinámica.

El resultado debería ser una tabla vacía como esta, vamos a llevar el campo apellido hacia valores y tendríamos nuestra primera tabla titulada “TOTAL DE EMPLEADOS”:

Para las siguientes tablas no será necesario insertar cada una de ellas, con un simple CTRL + C y CTRL +V vamos a duplicar y simplemente editar el contenido de cada una de ellas. Así quedarían:

Título –> “Segmentación por facultad”

Título –> “Segmentación por cargo”

Título –> “Segmentación por género”

Título –> “Segmentación por mes de contratación”

Título –> “Segmentación por mes de cese”

Título –> “Segmentación por situación”

Título –> “Rotación”

Lleva los totales al panel

Ahora vamos referenciar el total de cada tabla dinámica en otra simple que servirá como resumen:

E2 –> Empleados

E3 –> =B5

F2 –> Facultad

F3 –> =C17

G2 –> Cargo

G3 –> =C28

H2 –> Mes de contratación

H3 –> =C44

I2 –> Mes de Cese

I3 –> =C56

J2 –> Rotación

J3 –> =C68/B68

Género y situación, sin celdas con error

Cómo te habrás dado cuenta el contenido de la tabla son solo títulos y referencias, la única distinta es J3, pues hicimos una simple división para hallar el ratio de rotación (total de cesados / total de empleados).

Sin embargo, encontramos inconvenientes para reflejar los porcentajes de género y del personal cesado, por eso nos vimos obligados a hacer algunos cálculos extra con la función SI.ERROR y armar dos tablas extra.

E5 –> Género

E8 –> Situación

F5 –> Femenino

F6 –> Masculino

F8 –> Cesado

F9 –> Empleado

G5 –> =C33

G6 –> =C34

G8 –> =C61

G9 –> =C62

H5 –> =G5/E3

H6 –> =G6/E3

H8 –> =G8/E3

H9 –> =G9/E3

I5 –> =SI.ERROR(G5,0)

I6 –> =SI.ERROR(G6,0)

I8 –> =SI.ERROR(G8,0)

I9 –> =SI.ERROR(G9,0)

J5 –> =SI.ERROR(H5,0)

J6 –> =SI.ERROR(H6,0)

J8 –> =SI.ERROR(H8,0)

J9 –> =SI.ERROR(H9,0)

Diseña el dashboard

Ahora creamos una hoja nueva para diseñar el dashboard, le vamos a poner fondo verde a toda la hoja, luego insertamos un cuadro de texto –> “REPORTE DE INGRESO Y ROTACIÓN DE PERSONAL 2017 – 2018” –> Este será el título de nuestro dashboard.

Ahora necesitamos enseñar el número de trabajadores y lo haremos también por cuadros de texto, insertamos uno y vamos a referenciar el total de trabajadores de género masculino que está en la celda I6 de la hoja “ANÁLISIS”. Hacemos lo mismo para el género Femenino y para el número total de empleados, pero vamos referenciar la celda I5 y E3 respectivamente. Por último, ponemos título a esos tres cuadros de texto con otros tres cuadros de texto, pero estos no tendrán marco ni relleno, este sería el resultado.   Luego de hacer esto vamos a colocar segmentadores por facultad, cargo, género, situación actual, mes de contratación y mes de cese, esto servirá para analizar por cada categoría, es un proceso fácil colocarlos, sólo debemos seleccionar cualquiera de las tablas dinámicas creadas antes –> pestaña analizar –> insertar segmentación de datos –> seleccionar una de las opciones (en este caso facultad) –> cambiamos el formato (color, tamaño, etc) –> la copiamos y pegamos en la hoja del dashboard; repetimos con cada una de las segmentaciones restantes.

El dashboard va quedando así:

Sin embargo, falta graficar los indicadores por género, para esto vamos a utilizar los datos calculados en la tabla resumen, o sea J5 y J6, aquí dejo un enlace donde te explico paso a paso como hacer este gráfico especial.

 Para finalizar, faltaría armar el velocímetro, te dejo los datos del velocímetro que están ubicados después de la última tabla dinámica de la hoja “ANÁLISIS”, aquí dejo el enlace para que puedas ir hacia el artículo donde detallo el paso a paso de la elaboración del velocímetro.

Este es el resultado final del dashboard:

Descarga la plantilla