Cargando

Ir a contenido

X



Cómo hacer un Dashboard Profesional con Tablas Dinámicas de Excel

Aprende cómo construir un cuadro de mandos en Excel Profesional usando las tablas dinámicas y gráficos dinámicos, así como otras habilidad adquiridas en Excel. Te explicamos cómo hacer un Dashboard profesional con gráficos dinámicos de diferentes estilo, segmentación de datos y un diseño sencillo e intuitivo.


Escrito por ago 08 2022 15:36 office

Para poder trabajar con números, necesitamos como ya sabemos una herramienta de cálculo potente. Cuando trabajamos con datos, el objetivo es entender la información, representarla, reportarla, evaluar resultados y tomar decisiones. Cuantos casos hemos visto de decisiones mal ejecutadas por un análisis erróneo, o por un análisis parcial.

 

El entendimiento del conjunto de los datos es lo más importante a la hora de plasmar la información en un informe, entendiendo como ya hemos explicado en otras ocasiones que el informe general es la primera lectura que debe realizarse; Analizar los datos en detalle sería la tarea que debemos realizar a continuación, para poder entender de forma profunda el por qué de los datos generales.

 

Toda esta información, general e información más detallada, segmentada o filtrada es lo que debe reflejar un documento que se utilice en aras de análisis y toma de decisiones. En ocasiones la profundidad de la información nos desvía del camino correcto cuando analizamos los datos, podemos perdernos en la inmensidad de los datos.

 

Por esta razón, es importante entender que entender la situación general que arrojan los datos es fundamental, como también lo es en un nivel inferior entender la información parcial o segmentada. Y siempre en este orden, de lo más general a lo particular. Evitemos que el árbol, como se suele decir, nos impida ver el bosque.

 

Es en estas tareas donde se hace necesario disponer de un cuadro de mandos o Dashboard que nos permita ver la información rápido, de un vistazo, que nos ayude a hacer un análisis rápido de las principales métricas a seguir, donde se vean los resultados de una forma global, y que a su vez nos permita establecer ciertos criterios de segmentación para poder hacer “micro analasis”. Con este fín, y en estas circunstancias, es donde un Dashboard o cuadro de mandos cobra sentido y es útil, en dos tareas fundamentales de los que ya hemos hablado: analizar y tomar decisiones.

 


1. ¿Qué es un Dashboard?


Un Dashboard es un tablero o cuadro de mandos de control interactivo que arroja información representada principalmente en gráficos. Un Dashboard es una herramienta, un panel de control simplificado que refleja información importante, de valor para poder entender rápido un conjunto de datos. Un Dashboard debe reflejar una evaluación de los principales indicadores o KPIS.

 

Cuando trabajamos en Excel el objetivo es usar una herramienta de cálculo que nos ayude en el apartado de análisis y evaluación de los datos. Un Dashboard es una herramienta de control que debe reflejar la información importante, de una forma sencilla de interpretar, debe facilitar la lectura. El Dashboard es el conjunto de funciones y técnicas aprendidas en Excel utilizadas en este caso para poder conseguir un sistema de control de métricas principales a analizar. Por eso debe ser el resultado final de una serie de técnicas y habilidades adquiridas que es hora de poner en marcha para dotar a nuestro Dashboard de unas funciones y estilo que lo hagan atractivo, útil, y único.

 

Cuando creamos un cuadro de mandos, es importante tener en cuenta qué queremos enseñar.

 

Qué tener en cuenta para elaborar nuestro Dashboard
  • ¿Por qué estas usando Excel?
  • ¿Cuáles son los objetivos del análisis que te hicieron abrir el Excel?
  • ¿Qué resultados son más importantes para ti, para tu empresa, para tu trabajo cuando trabajas con una hoja de Excel, o estás realizando un informe?
  • ¿A quién va dirigida la información?

 

Son preguntas que debes realizarte a fin de realizar un cuadro de mandos útil, importante y completo, que resalte la información de una forma fiel a la realidad sin errores en los principales puntos de control o métricas a seguir.

 


2. ¿Qué Contiene un Dashboard?


Un Dashboard debe contener información resumida, simplificada, importante. Esta información debe representarse de tal forma que facilite la lectura a quien lo va a ver. Un Dashboard utiliza los gráficos para poder enseñar la información de forma rápida, concisa, con perspectiva, resume la información de tablas y sobre todo ayuda a entender qué nos quieren decir los datos.
Para poder poder decir que nuestro dashboard cumple con las exigencias propias del guión, vamos a repasar aspectos que nos ayudarán a elaborar un Dashboard profesional:
  • DISEÑO: Un Dashboard profesional debe lucir y destacar en el aspecto del diseño. Realizar un diseño bonito es algo que podemos hacer en poco tiempo, y es importante en dos aspectos: dar valor al trabajo que hemos realizado, y facilitar la lectura de la información. No hay que volverse loco en realizar el mejor diseño; A veces un diseño sencillo es más fácil de leer e interpretar.
  • ORGANIZACIÓN DEL DASHBOARD: Organización del Dashboard, es algo importante junto con el diseño en el apartado visual. Un Dashboard bien organizado nos ayudará a entender mejor la situación, a evaluar los datos de forma rápida, sencilla. Nos debe ayudar a entender la información en varios niveles. Debe ser por tanto intuitivo en la lectura, y mostrar la información a nivel general, y a nivel segmentada o parcial.
  • GRÁFICO e INTERACTIVO. Hacer un dashboard visual, es sinónimo de un dashboard gráfico, donde podamos hacer una lectura actual, pero también con perspectiva. Imaginemos que los datos mostraran los resultados del 2021 de una empresa, donde vemos beneficio al final del ejercicio. Si solo analizamos un momento concreto, nos perdemos el contexto. Contexto que en este caso sería el beneficio conseguido en años anteriores. Si vemos que una empresa da beneficio, y sin embargo vemos que año a año el beneficio se ve disminuido, los resultados ya no son tan positivos debido a la tendencia decreciente año tras año. Y esta tendencia nos podría indicar que pronto podemos entrar en pérdidas. Por eso es importante incluir gráficos que aporten perspectiva en el tiempo en los principales KPIs o indicadores a seguir, como puedes ver las ventas. De igual forma y como hemos visto, poder evaluar los resultados a un nivel inferior será importante siguiendo con el mismo ejemplo para localizar qué nos está llevando a perder año a año beneficio. La parte interactiva juega un papel importante en un Dashboard pues permite al que lo maneja poder filtrar la información a varios niveles.
  • AUTOMÁTICO: un Dashboard profesional debe eludir posibles errores en los datos. Un Dashboard nos tiene que ayudar en la toma de decisiones. Por esta razón es tan importante que la fiabilidad de los datos nos ayude a tomar las decisiones correctas. Un Dashboard que se actualiza solo, evita errores humanos en la formulación de las celdas, errores en el rango de datos, desconfiguración de los datos, etc.

 


3. Pasos para hacer un Dashboard

 

Para poder hacer un cuadro de mandos profesional, con todas las características ya mencionadas (Diseño, organizado, simple, gráfico e interactivo y automático) debemos empezar haciendo los deberes poniendo en práctica algunas lecciones que hemos ido explicando. El Dashboard debe ser el reflejo, la exposición final de una serie de prácticas y técnicas en Excel. Debe ser el fruto de un trabajo secuenciado y trabajado, bien presentado.

 

A continuación, puedes ver los pasos a seguir para poder realizar un Dashboard:

  • Revisión de las BBDD. Revisar los datos de origen, la fuente de los datos que vamos a usar para realizar las tablas dinámicas.

 

Pasos para revisar tus datos
  • Revisa que tenemos toda la información completa
  • Revisa encabezados, que todas las columnas tengan un encabezado y no haya celdas vacías
  • Revisa que no hay símbolos o errores en las celdas
  • Comprueba que no hay celdas vacías
  • Revisar en los campos que los nombres a agrupar en las tablas dinámicas están bien escritos y de la misma forma.
  • Convertir datos en Tablas de Excel: Una vez que tenemos nuestra fuente de datos revisada, y bien organizada, vamos a convertir los datos en tablas de Excel para poder trabajar a pleno rendimiento con las tablas dinámicas, con todas la funciones habilitadas y disponibles. Cómo ya hemos visto, esto nos ayudará a evitar errores en los datos que muestren las tablas dinámicas en tanto las tablas dinámicas actualizarán automáticamente el rango de datos cuando metemos nuevos registros en la base de datos.

 

Tutorial que explica los pasos para poder hacer una Tabla Oficial de Excel

 

IMPORTANTE
Una vez realizada la tabla de Excel, aplica un estilo y formato. Las tablas y gráficos que realices después con esta tabla, tomarán por defecto el mismo “estilo” o “look and feel” de la tabla. Por eso dedica en este punto algo de tiempo a dar un formato y estilo a la tabla, que encaje con el estilo final que quieras dar al Dashboard.

  • ¿Qué datos queremos mostrar en el Dashboard final? Es importante tener claro antes de ponerse manos a la obra, tener claro cuál es el objetivo del Dashboard, y que datos queremos enseñar en la versión final. De esta forma, a la hora de realizar las tablas dinámicas, tendremos claro que datos necesitamos, qué tablas y gráficos debemos realizar. En este sentido, puedes hacer tu propio boceto, en formato digital, o hacerlo a mano con papel y lápiz. Así será más sencillo tener claro que vas a necesitar realizar para hacer tu Dashboard: qué información quieres enseñar, en qué lugar del Dashboard, qué datos colocar antes, y cómo organizar la estructura general del Dashboard. En la imagen de abajo puedes ver cómo ejemplo, cómo hemos realizado nosotros el boceto, para poder ver qué información vamos a plasmar en el Dashboard, y por tanto saber qué necesitamos realizar a nivel de tablas dinámicas y gráficos.

 

 

  • Realizar tablas dinámicas y gráficos dinámicos: Ahora si podemos realizar las tablas dinámicas necesarias, y los gráficos dinámicos que vayamos a poner en Dashboard. Puedes realizar las tablas dinámicas y los gráficos dinámicos al mismo tiempo, tal y cómo te explicamos en cómo hacer gráficos dinámicos. Realiza en esta hoja cuantas tablas y gráficos sean necesarios. Posteriormente usarás los gráficos dinámicos realizados, para copiarlos en la hoja del Dashboard. Por eso es importante que realices todas las tablas y gráficos que vas a querer incluir en el Dashboard, y que apliques ya el diseño necesario a las tablas, unificando en estilo todos los gráficos (usar mismos colores y formatos). Establecer para una misma métrica, como podrían ser las ventas, el mismo color en todos los gráficos. Este tipo de detalles ayudarán en gran medida a que tu Dashboard sea intuitivo y fácil de ver y leer a alguien que no lo haya visto antes.

 

Tutorial que explica los pasos para aprender a crear Tablas Dinámicas en Excel

  • Si a parte de la base de datos, necesitamos calcular o realizar alguna operación, debemos realizarla dentro de la tabla dinámica como ya te explicamos en cómo realizar un campo calculado en Excel.
  • Vamos a organizar nuestro Excel de tal forma que podamos disponer de toda la información y los datos bien organizados y separados en varias hojas de Excel.
  • Por un lado, en una hoja de Excel dejaremos la base de datos o fuente de datos en una sola hoja. Podemos convertir los datos en tabla de Excel en la misma hoja, o hacer la tabla de Excel en otra hoja.
  • En otra hoja de Excel del mismo documento, hacemos las tablas dinámicas y los gráficos dinámicos. Usaremos esta hoja para hacer todas las tablas dinámicas necesarias y los gráficos, y podemos usarla para realizar alguna operación si lo necesitamos.
  • En una hoja nueva, haremos el Dashboard, cuando hayamos cumplido con todos los pasos anteriores (revisar base de datos, realizar la tabla de Excel, hacer las tablas dinámicas y los gráficos dinámicos, hacer que la tabla de datos se actualice automáticamente y los gráficos dinámicos también)

 

Cómo-hacer-un-Dashboard-5.png

 

A la hora de realizar el Dashboard, te darás cuenta de cómo se debe plasmar la información necesaria, para poder ver prácticamente de un vistazo, sin necesidad de hacer scroll, no debes pecar de incluir demasiada información. Esto a veces se traduce en que vas a tener la sensación de no poder poner toda la información que quieres para ver en detalle algunos aspectos importes. ¿Cómo puedes solucionar este hándicap?

 

La solución se llama segmentación de datos, que como ya hemos visto es muy útil para poder hacer una ventana con opciones para poder filtrar la información. Y que además nos ayuda a conectar tablas dinámicas para poder manejar los filtros de varias tablas dinámicas y gráficos dinámicos. Por ejemplo imaginemos que tenemos varios gráficos construidos para mostrar la tendencia y resultados de varios niveles de una empresa, donde mostramos resultados a nivel general, a nivel de empresas, de delegaciones, países y a nivel de vendedor. Pues bien como ya vimos en cómo hacer la segmentación de datos en tablas dinámicas, podemos por ejemplo crear una segmentación por años para que todos esos gráficos mencionados cambien al seleccionar nosotros una fecha.

 

Esto es algo realmente útil y que seguramente has visto en muchos Dashboards. Y es necesario para que la persona que vaya a ver el Dashboard también pueda entrar a ver el detalle filtrando la información según la necesidad.

 

Cuando hayas realizado todos los pasos anteriores, es hora de empezar a construir el Dashboard, con la información que consideras necesaria para plasmar los resultados esperados. Es algo que has tenido que pensar previamente cuando has realizado las tablas dinámicas y los gráficos. Si te das cuenta que necesitas realizar alguna operación a parte, puedes usar una nueva hoja de Excel o realizar estas operaciones en la hoja donde hiciste las tablas dinámicas.

 


4. Diseño del Dasboard

 

El diseño del Dashboard como ya comentamos al inicio es un factor muy importante como ya explicamos necesario para que la lectura de los datos, la interpretación, el análisis y la toma de decisiones posterior sean tareas sencillas. Un Dashboard debe contener la información “ad hoc”, dependiendo de cual sea el objeto del análisis.

 

Un Dasboard debe ser 100% personalizado; Aunque busques ideas para realizar el Dashboard, úsalos siempre como referencia para construir uno propio que realmente refleje la información importante a destacar. Nosotros te explicamos qué información debería contener el Dashboard para que sea completo y profesional

  • Información actual: Que contenga información actualizada del año, trimestre y mes si es necesario reflejar la información tan actualizada. Si se trata de un Dashboard que refleje la información anual completa, para analizar el último año, la información debe se completa para poder comparar años enteros.
  • Datos Evolutivos (Tendencias): Como hemos comentado la perspectiva a la hora de analizar los datos es fundamental para entender los resultados en un contexto más amplio. No se trata de ver solo un número, o el resumen del año si no de establecer una comparativa para ver si los resultados son positivos en tendencia, o si por el contrario la tendencia de los resultados anualmente es decreciente
  • Destacados: También puedes reservar un espacio del Dashboard para poder plasmar lo más destacado del año en diversas categorías. Para una empresa matriz que a su vez está formada por más empresas, delegaciones y vendedores, con presencia en varias países, podemos configurar campos para resaltar dónde se han producido más ventas en todos estos niveles: qué empresas del grupo ha vendido más, en qué país se realizaron más ventas, quién ha sido el mejor vendedor del mes. Con esta información de valor, podemos ver dónde destinar nuestros recursos dependiendo de dónde y quién estén contribuyendo a que los resultados de la empresas sean mejores, podemos entender qué vendedores son de más valor para la empresa y nos interesa retener, motivar, y en países podemos reforzar para afianzar el posicionamiento de la empresa, por poner algunos ejemplos de decisiones por el bien de la empresa que se pueden tomar viendo un Dashboard resumen y evolutivo.

 

¿Cómo obtener el valor máximo de una tabla dinámica en Excel?

Excel te permite obtener el valor máximo o mínimo de una tabla mediante el uso de la fórmula de coincidir. Con esta fórmula, podemos decir que nos saque el atributo con un valor máximo, directamente, sin tener que ordenar la tabla o buscar manualmente el valor más alto.
Usamos de ejemplo una tabla dinámica de Excel que hemos construido con datos por vendedor o comercial.

 

Cómo-hacer-un-Dashboard-6.png

 

De esta tabla mediante la fórmula de coincidir, vamos a buscar y extraer el vendedor al que se le atribuye un volumen de ventas más grande. En nuestro caso, aplicaríamos la siguiente fórmula para poder saber quién ha vendido más.
=INDICE(A42:A68;COINCIDIR(MAX(B42:B68);B42:B68;0))

 

Es importante tener en cuenta que en tu caso variará si no colocas la tabla dinámica en las columnas y filas igual que en el ejemplo. En la imagen abajo puedes ver los conceptos de cada parte de la fórmula para que te resulte más sencillo aplicar la fórmula.

 

 

Así estaríamos obteniendo el vendedor con el valor más alto en ventas, que en nuestro ejemplo sería de forma destacada la vendedora Carla.

 

Saber el valor máximo de una tabla no es complicado, ordenando la tabla dinámica por la columna que queramos. Pero mediante la fórmula de coincidir y máximos estamos haciendo que automáticamente Excel nos de el valor que queramos. Así podemos esta tarea automatizada.

 

Si vas a utilizar esta u otra fórmula en el Dashboard, has de tener en cuenta que cuando tengas que actualizar el Dashboard, deberás seguramente actualizar el rango de datos de la fórmula si por ejemplo entraron nuevos vendedores a la empresa. Te recomendamos por tanto que esta tabla dinámica que vas a usar para extraer el atributo con el valor máximo, la hagas en una hoja nueva y le quites los datos. Así podrás poner de inicio un rango de datos amplio.

 

 

Cómo-hacer-un-Dashboard-1.png

  • Información general: El Dashboard debe contener información general. Siguiendo con el ejemplo donde analizamos los resultados de una empresa, será importante entender a nivel general los resultados de ventas e ingresos para poder así establecer comparativas con años anteriores.

 

Cómo-hacer-un-Dashboard-2.png

  • Información por categorías: Una vez plasmada la información general, debemos establecer un sistema por el cual la información se pueda ver para diferentes categorías o segmentos y para diferentes periodos.

 

Es probable que si queremos plasmar toda esta información, el Dashboard sea más grande de lo esperado, y rompamos con la estética visual sencilla que queremos para facilitar la lectura de los gráficos. Aquí la segmentación de los datos juega un papel importante, que podemos incluir en el informe para poder establecer filtros en varios gráficos dinámicos, como en el ejemplo de la imagen de abajo.

 

Cómo-hacer-un-Dashboard-3.png

 

Con la segmentación de los datos, conectamos los gráficos dinámicos que queramos vincular. Así podemos filtrar la información a diferentes niveles con los gráficos que ya hemos incluido en el Dashboard, sin necesidad de incluir más gráficos o más tablas.


Con esta información, podemos construir un Dashboard completo como el de la imagen abajo, donde se contemplan todos los puntos importantes que hemos ido hablando

 

Cómo-hacer-un-Dashboard-4.png

 

De esta forma puedes construir tu propio Dashboard. Recuerda que la información de valor, así como el estilo marcarán que tu Dashboard sea útil y profesional. Dependiendo del tipo de Dashboard que realices, puedes necesitar incluir información diferente. Recuerda que tu Dashboard debe ser original, personalizado a tus necesidades. Puedes tomar otros Dashboard si lo necesitas, para captar ideas entendiendo que tu Dashboard debe ser único. Tu cuadro de mandos debe reflejar las principales métricas o KPIs a seguir, mostrando información general y segmentada en varios niveles.

 

Un Dashboard gráfico resultará más atractivo, y además te ayudará a mostrar la tendencia de los resultados. Puedes apoyarte en recursos como te hemos explicado que te ayuden a realizar segmentaciones de datos en el propio Dashboard, usando la segmentación de datos en los gráficos dinámicos para poder controlar y cambiar los gráficos cuando se quiera entrar al detalle en alguna parte de los resultados, o cuando se quiera establecer un filtro importante, como por ejemplo el periodo de tiempo a analizar.


Teniendo en cuenta lo que has aprendido de Excel, y de las tablas dinámicas, podrás poner en práctica tus habilidades para poder hacer un Dashboard profesional.


¿Te ayudó este Tutorial?


Sin comentarios, sé el primero!

No esperes más y entra en Solvetic
Deja tus comentarios y aprovecha las ventajas de la cuenta de usuario ¡Únete!


  Información

  •   Publicado ago 08 2022 15:36
  •   Actualizado sep 07 2022 10:28
  •   Visitas 358
  •   Nivel
    Avanzado


X