¿Cómo elaborar un Dashboard KPI’s?

Un dashboard o panel de control, es una herramienta visual que nos permite ver todos aquellos datos o medidores de desempeño de nuestra empresa o proyecto. A partir de esto junto a un análisis, podremos tomar decisiones diarias o mensuales de acuerdo a nuestros objetivos.  

Aprenderemos a crear un dashboard en Excel, paso a paso, ya que este contiene gráficos de barras, velocímetros, tacómetros, e imágenes interactivas entrelazadas a un total, que se puede filtrar según el área o categoría que deseemos  

En este artículo analizaremos mediante un velocímetro de avance, el porcentaje de ingresos de cada producto según la zona y el trimestre.

Crearemos una tabla dinámica de nuestra base de datos, seleccionamos la tabla, e insertamos en una hoja nueva.

Vamos a tomar el campo de productos y lo colocamos en fila, eliminando el total, dejando solo el nombre. Haremos lo mismo con ingresos en el área de valores, dos veces.

Uno de los ingresos, lo vamos a convertir en el porcentaje basado en el monto que tenemos. Nos posicionamos en la columna, clic derecho > mostrar valores como > % del total de columnas.

Si queremos modificar el nombre de las tablas, podemos usar la tecla “Ctrl + B” Para reemplazar la palabra que no queremos.

Copiamos la tabla dinámica, usando el formato de “=” en la celda y arrastraremos hasta abajo, para que tome los datos, así se creará una nueva tabla que la usaremos como apoyo para crear los gráficos.

Agregaremos dos columnas más, una que nos representara la aguja de nuestro velocímetro, donde la columna tendrá un valor muy pequeño, y la otra será un ayudante para que la aguja se mueva.

Para que ambas marquen el avance, haremos la siguiente fórmula en el apoyo.

Insertaremos un gráfico de anillo, al cual le agregaremos los datos, con una partición de 11 partes.

Daremos clic derecho > formato serie de datos para modificar el ángulo y el tamaño del agujero central, de igual forma los colores a cada parte. El lado más grande, lo dejaremos sin color y sin contorno.

Procedemos a cortar y pegar el gráfico en la hoja donde queremos colocar el dashboard. Lo posicionamos, damos clic derecho > seleccionar datos, y agregamos una nueva serie.

El nombre de esta nueva serie será el “Producto A” y los datos, las tres filas consiguientes.

Modificaremos el tipo de gráfico por un gráfico circular en 2D, que será nuestro eje secundario.

De igual forma que con el primer gráfico, cambiaremos el ángulo, y los colores dejándolo sin relleno exceptuando la parte más delgada, ya que esta ser nuestra aguja.

A esta única parte le daremos grosor, color tanto de relleno como de contorno.

Lo que haremos será copiar el gráfico en cada uno de los cuadros de los productos marcados. Y modificaremos los datos de cada uno, seleccionando el producto y datos correspondientes.

¿Cómo creamos los filtros en un dashboard? 

Para el filtro de zonas o región, volvemos a la tabla inicial, y en analizar > insertar segmentación de datos, escogeremos el filtro que deseemos.  

Para este caso, copiaremos y pegaremos la tabla dinámica. Sacaremos de nuestras áreas, cada campo, dejándola vacía. Si queremos otro filtro como fecha ya sea mensual, trimestral. Lo que haremos será que la tabla dinámica nos fraccione las fechas. Agregaremos únicamente la fecha y luego las sacaremos del área. Ingresaremos de nuevo, la fecha fraccionada que nos interesa. Seleccionaremos la tabla inicial e iremos a analizar > insertar segmentación de datos, escogeremos el filtro que fracciono.

Una vez tengamos los filtros listos, los llevaremos a la hoja de dashboard.

Personalizaremos cada velocímetro, para visualizar el cambio porcentual que se genere según el filtro. Para esto, agregaremos un cuadro de texto o una figura. En la barra de fórmulas vincularemos con la celda del porcentaje del segundo cuadro realizado.

Podemos modificar el formato de texto, tamaño. De igual forma, podemos agregarle a nuestro velocímetro un círculo como base de la aguja, para darle más cuerpo.

Vamos a crear un indicador en texto con la función “si” para darle una valoración a cada porcentaje que nos marque el velocímetro. Hacemos una nueva columna que nos represente el “estado” con una pequeña leyenda de valoración que será nuestra guía. Ingresaremos la siguiente fórmula en cada celda de cada producto.

Agregamos un cuadro de texto en el dashboard, vinculándola con la celda de estado de nuestra tabla para que nos muestre un dato más preciso.

¿Cómo realizar una condición de texto que nos especifique si se halló un valor o no? 

Para realizar esto, debemos ingresar una celda con la función “concatenar” uniendo tanto la celda de producto como de ingreso en monto, combinándola con la función “texto” para que nos dé un formato de moneda y decimal.

Arrastramos esa fórmula hasta completar los productos.

Ahora crearemos una condición, con la función “si” para que el dato que nos proporcione sea con números mayores a “0”

De igual manera arrastraremos para el resto de los productos.

Insertaremos un cuadro de texto o figura en nuestro dashboard, vinculando las celdas de la condición, cambiaremos el formato, y pegamos en el resto de nuestros velocímetros.

De esta manera, tendremos un dashboard de indicadores dinámico, que nos muestre el avance de las ventas de cada producto, según la zona y el trimestre correspondiente.