Cada día es más habitual encontrar libros de Excel en los cuales se ha agregado “Tableros de Control” más conocidos simplemente como “Dashboards”. Dichos tableros facilitan la presentación y/o análisis de datos que en muchas ocasiones necesitamos por lo que en esta ocasión veremos cómo hacer uno que incluso nos permita visualizar los datos a través de gráficos.
Para comenzar, la disposición de nuestros datos para el ejemplo será de este modo:
Para que el ejercicio resulte mucho más visible, he agregado varios registros con datos de tres años. Para que no haya problema cuando vamos agregando más datos, yo siempre recomiendo convertir en “Tabla” dichos rangos. Además de usar el ya “clásico” ejemplo de ventas para estos casos, también necesitaremos, como siempre se recomienda, tener tres hojas en nuestro libro: Una para los datos, otras para las tablas dinámicas que vamos a necesitar y una para el “Dashboard”.
Sugiero dar formato a los números para que después en los gráficos que usemos se refleje dicho formato. En mi caso usaré el formato de moneda, tal cual está en la tabla de datos.
Ahora lo que haremos será insertar un gráfico dinámico para cada una de las tablas dinámicas. Por si se han olvidado en dónde está la opción para crearlas, se sitúan en cualquier celda de la tabla dinámica respectiva y en la pestaña “Analizar” la veremos así:
Podemos elegir el tipo de gráfico que deseemos, en mi caso yo he elegido barras 3D agrupadas (horizontales).
No olviden que es un gráfico por cada tabla dinámica que usemos.
Luego lo que debemos hacer es cortar y pegar cada gráfico en la hoja llamada “Dashboard”. Por supuesto que podemos cambiar el diseño de cada gráfico con las opciones, justamente, de la pestaña “Diseño”. Además de eso, sugiero usar las opciones de cada gráfico dinámico para ocultar los botones de campos.
Una vez hecho eso, seleccionamos cualquiera de nuestros gráficos dinámico y vamos a la pestaña “Analizar” y en el grupo “Filtrar” usaremos el botón “Insertar escala de tiempo”.
En el menú que veremos elegimos, en mi caso, el campo “Día” y aceptamos.
Nuestra escala de tiempo incluirá todos los meses ya años que existan en nuestros datos.
Podemos cambiar el diseño a dicha escala y, además, una de las ventajas que tiene es que nos permitirá elegir días, meses, trimestres o años.
Lo que toca hacer ahora es relacionar nuestra escala de tiempo con las tablas dinámicas, para ello seleccionaremos nuestra escala y en la pestaña “Opciones”, en el grupo “Escala de tiempo” vamos a usar el botón “Conexiones de informes”.
Y activaremos la conexión a todas las tablas dinámicas que estamos usando.
Si bien ya tenemos como ir filtrando por tiempo, ahora lo que haremos es agregar algo que nos permita también hacerlo por vendedor o tienda, para eso seleccionamos cualquiera de los gráficos y en la pestaña “Analizar”, en el grupo “Filtrar”, esta vez vamos a elegir “Insertar segmentación de datos” y elegiremos “Vendedor” y “Tienda”.
Con los “slicer” que tenemos ahora, lo que debemos hacer es usar “Conexiones de informes” y relacionarlos a nuestras tablas dinámicas, tal y como hicimos con la escala de tiempo. Por supuesto también podemos cambiarle el diseño a dichos “slicer”.
Para ver como seguir agregando datos que nos pueden ser útiles, en una celda obtendremos el total de ventas y haremos que dependa de lo que vamos a ir eligiendo en la escala de tiempo y/o los “slicer”. Para ello vamos a usar el total de general de una de las tablas dinámicas y vincular dicho resultado a la celda deseada. En este caso he usado el total de la tabla dinámica con los resultados de las “Tiendas” .
Como las tablas dinámicas trabajan sobre los mismos datos, vamos a usar la de “Vendedores” y en el campo de totales vamos cambiar para que nos dé como resultado el promedio.
Vinculamos otra celda con dicho resultado del promedio, y con ello tendremos el promedio de venta de cada vendedor en base a lo que vayamos filtrando en la hoja “Dashboard”.
Para finalizar podemos colocar cada elemento de los obtenidos en la parte de la hoja que deseemos y tendremos algo como esto:
Y listo, ya podemos comenzar a “jugar” con los datos. Espero les sea útil. Hasta la próxima.
Abraham Valencia
Descarga el ejemplo aquí: Enlace
Excelente explicación y muy útil! muchisimas gracias!
ResponderBorrarGracias muy util
ResponderBorrarQue bueno que te ayudó. Saludos.
Borrar