Dashboards en Excel, Tablas dinámicas y gráficos
En este articulo y video veremos cómo hacer un Dashboard en Excel, o támbién llamado Panel de Control o Cuadro de mando. Aunque realmente lo de menos es el nombre. Lo importante es que en un Dashboard puedes plasmar indicadores de cualquier información que requieras. Para analizar estos indicadores usaremos Tablas dinámicas, Segmentación de datos y Gráficos dinámicos.
Vistazo general de nuestro dashboard
En la Figura 1 vemos el dashboard terminado, en el cual vemos algunos elementos interesantes que podrás utilizar para armar los tuyos. Lo importante de los dashboards es conocer la información con la cual vas a trabajar y tener imaginación.
Figura 1. Dashboard en Excel.
En base a buenas prácticas que los expertos recomiendan, puedo decirte que para hacer un dashboard bien diseñado y que además pueda ser modificado a futuro, lo primero que debes hacer es tener 3 horas en tu archivo:
- Hoja Datos: En esta hoja tendrás tus datos. Recuerda entre más columnas tenga tu información, mejor análisis podrás hacer.
- Hoja Tablas dinámicas: En esta hoja insertaras las Tablas dinámicas que serán la base para el diseño de tu dashboard.
- Hoja Dashboard: Aquí irá el diseño principal del dashboard.
Y como vemos en la Figura 1, tenemos principalmente 3 elementos, los cuales podemos combinar a nuestro gusto:
- Segmentación de datos: También llamados Slices. Nos ayudarán como filtros principales para el resto de los elementos.
- Fórmulas y funciones: Extraeremos información desde Tablás dinámicas usando fórmulas.
- Gráficos dinámicos: Los gráficos son la herramienta favorita para analizar información.
Ver video Dashboards en Excel
Suscríbete al canal de EXCELeINFO en YouTube para aprender más de Excel y macros.
Pasos generales para armar un Dashboard en Excel
Para armar un dashboard, principalmente debes seguir los siguientes pasos:
- Insertar varias Tablas dinámicas, obteniendo diferentes indicadores. Dejarlas en la hoja Tablas dinámicas.
- Por cada Tablas dinámica, insertar un gráfico dinámico. Moverlos a la hoja Dashboard.
- Darles el diseño deseado.
Figura 2. Elementos de un dashboard.
Manos a la obra
Top 5 de vendedores, Ventas y Gráfica
Sigamos los siguientes pasos para armar este indicador:
1. Estando en la hoja Datos insertamos una Tabla dinámica. Nos vamos a la Pestaña Insertar > Tabla dinámica.
2. La Insertamos en la hoja Tablas dinámicas.
3. La columna Empleados la movemos a Filas y la columna Ventas a Valores.
Figura 3. Insertamos una Tabla dinámica para obtener los mejores vendedores.
4. En la hoja Dashboard ponemos los encabezados Top 5 de vendedores, Ventas y Gráfica en el rango E6:G6.
5. Para traer el nombre de los vendedores desde la Tabla dinámica, sólo ingresamos la fórmula en la celda E7: =’Tablas dinámicas’!C2
6. Arrastramos la fórmula hasta la celda E11.
Figura 4. Devolvemos el nombre de los 5 mejores vendedores.
7. En la celda F7 ingresamos una fórmula para devolver la ventas de los vendedores, y la modificamos para que haga referencia a los nombres de la celda E7: =IMPORTARDATOSDINAMICOS(“Ventas”,’Tablas dinámicas’!$C$1,”Empleado”,E7)
Figura 5. Devolvemos las ventas.
8. Ahora para graficas las ventas, vamos a usar la siguiente fórmula en la celda G7: =REPETIR(“|”,F7/MAX($F$7:$F$11)*100)
9. Arrastramos hasta la celda G11.
10. Damos formato a las celdas con tipo de Fuente Playbill.
Figura 6. Graficamos las ventas con fórmulas y dando formato a la celda.
Gráfica de Ventas por Ciudad
Para graficas las ventas por Ciudad sigamos los siguientes pasos:
1. Seleccionamos la hoja Datos e Insertamos una Tabla dinámica. Pestaña Insertar > Tabla dinámica.
2. La Tablas dinámica la insertamos en la hoja Tablas dinámicas.
3. En etiquetas de fila movemos la columna Estado o Provincia y en Valores movemos Ventas.
4. Elegimos cualquier celda de la Tabla creada y nos vamos a la pestaña Analizar > Gráfico dinámico.
5. Elegimos el tipo de Barra.
Figura 7. Insertamos un gráfico dinámico en la base a la Tabla dinámica.
6. Seleccionamos el gráfico dinámico y lo cortamso.
7. Seleccionamos la hoja Dashboard y pegamos el gráfico.
8. Una vez pegado el gráfico lo seleccionamos y damos el formato deseado en la pestaña Diseño.
Figura 8. Pegamos el gráfico en la hoja Dashaboard y le damos formato.
Hasta este momento hemos creado una tabla con el top 5 y una gráfica. Para no hacer más grande el artículo repitiendo los pasos, haz otra tabla para el top 5 de productos y otra gráfica de Ventas por compañía. Siguiendo los pasos anteriores.
Segmentación de datos o Slices
En la hoja Tablas dinámicas hemos insertado algunas para hacer nuestros análisis, y los indicadores que medimos nos funcionan bien, pero qué sucede si deseamos que estos indicadores se ajusten a un filtro global, por ejemplo, filtros por año y mes. Imaginemos que deseamos que todos los indicadores muestren sus valores por determinado año y mes. Para lo anterior usaremos la Segmentación de datos. Sigamos los siguientes pasos:
1. En la hoja Dashboard elijamos uno de los gráficos.
2. Nos vamos a la pestaña Analizar > Insertar segmentación de datos.
3. Se mostrarán los campos de la tabla origen donde elegiremos el campo Año y Mes.
Figura 9. Elegimos Año y Mes para que sean nuestros filtros globales.
4. Se insertarán dos Slices, uno mostrará los años y otro los meses.
5. Ahora debemos vincular esos dos Slices a todas las Tablas dinámicas, para que al elegir un elemento de un Slice, todas las gráficas se adecúen al filtro seleccionado.
6. Seleccionamos el Slice de Año y nos vamos a la pestaña Opciones > Conexiones de informe.
7. Marcamos las Tablas dinámicas que deseemos afectar.
Figura 10. Marcamos las Tabla dinámicas que deseemos afectar.
