Ejercicio de creación de un cuadro de mando

El siguiente ejercicio de Excel tiene como objetivo preparar al usuario para crear un cuadro de mando en Excel para poder reportar una cierta actividad de manera visual y sencilla, orientada siempre al público hacia el que el cuadro de mando va dirigido. Tradicionalmente un cuadro de mando se compone de un conjunto de indicadores agrupados de diferentes maneras. En la actualidad, los cuadros de mando suelen realizarse (para grandes corporaciones) en herramientas de BI (Business Intelligence) aunque muchos departamentos siguen usando Excel como herramientra principal para preparar estos reportes. Para aprender un poco más sobre cuadros de mando integrales puedes ir al siguiente enlace: wikipedia.org/Cuadro_de_mando_integral.

EJERCICIO

En el siguiente ejercicio vamos a realizar un cuadro de mando integral con los datos que tenemos de distribución del tiempo de un área dentro de una empresa. En este ejercicio tenemos una extracción de una herramienta de “control del tiempo” de los empleados.

El objetivo del cuadro de mando será que cada uno de los managers vea el tiempo dedicado por su equipo a las diferentes actividades posibles.

Para realizar este cuadro de mando utilizaremos la validación de datos, gráficos dinámicos que varían en función del número de elementos, crearemos listas de datos, usaremos tablas dinámicas, usaremos checkboxes para hacer validaciones… etc.

La información necesaria para el cuadro de mando puede encontrarse en el siguiente enlace: Ejercicio Creación de un dashboard

SOLUCIÓN

La solución de este ejercicio no es compleja pero requiere del conocimiento de varias áreas de Excel.

cuadro de mando en excel

1. Lo primero que se ha hecho con este ejercicio es ligar la información de las áreas, las personas y su manager en la hoja Data. Esto se ha conseguido transfiriendo la información de la hoja de configuración a la hoja “data” mediante la función BUSCARV (para leer más de esta función haz click aquí).

2. Después hemos realizado un pequeño análisis de la información de la hoja data mediante tablas dinámicas. Para saber más de tablas dinámicas puedes visitar este tutorial o este ejercicio resuelto.

3. Creación de dos listas de validación de datos para poder elegir de desplegables, en nuestro cuadro de mando, el manager que queremos analizar o la semana o ambos. Para ello, pensamos que sería mucho más sencilla elegir los valores de una lista que tener que escribirlos. Si necesitas aprender un pelín más sobre listas de validación échale un vistazo al siguiente enlace: validación de datos.

4. Por último hemos creado una serie de gráficos ligados a los resultados de manera que tengamos un reporte visual.

Con estos sencillos cuatro pasos habremos creado un cuadro de mando en Excel con el que poder reportar cualquier actividad de forma sencilla, gráfica e interactiva.

En el siguiente enlace puedes descargar el cuadro de mando que hemos presentado en este artículo para que puedas ver cómo se hace: Resolución creación de un dashboard

Autor: Quique Arranz

Quique es un fanático del Excel desde que empezó a trabajar con ello hace unos pocos años. Adquirió su conocimiento a través de Google y de infinitas pruebas y más pruebas. Además, Quique es un entusiasta de su familia (mujer e hijos), del deporte y de la cerveza... no dudes en contactar con él para cualquier duda que tengas.

28 opiniones en “Ejercicio de creación de un cuadro de mando”

  1. Buenos días, mi nombre es Maurizio
    El Escribo porque tendría un problema con un gráfico creado con el Doc en una hoja de cálculo de Excel
    Que no puedo resolver.
    Mi problema es el siguiente: El uso de un gráfico en una hoja de Excel con Office 2007,
    He creado un reloj analógico con una gran cantidad de manos y todo lo demás.
    Después de que en lugar de números para indicar las obras clásicas pasar las horas o minutos,
    He incluido una Serie de los símbolos de Rotondi amarillo.
    Ahora mi problema es el siguiente: Me gustaría que a la expiración de los 5 Minutos podía
    Lo más destacado de la Red símbolo Corresponsal.
    Tengo entendido que dicho así parece un poco difícil de entender y de explicar; de aquí
    Me gustaría enviar tanto mi reloj otra mi pequeño programa, pero la
    almidón podría ayudar a comprender mejor el conjunto.
    Entre otras cosas: Yo no quiero que usted ponga a hacer todo el trabajo en mi lugar,
    Con todos sus compromisos seguramente ya la suya.
    Pero te pido un Cortesía Ayúdame a darse cuenta también sólo me puedo mostrar cómo
    no para destacar minuto (0,5), entonces el (10; 15; 20; 25), etc ….! Puedo hacerlo yo mismo
    que tiene una base sólida de trabajo aquí.
    Gracias por toda la ayuda que me dará un mérito; Saludos sinceros de A.Maurizio

  2. Hola Quique,
    Gracias por tus articulos.
    Tengo excel 2007 y no se como realizar la validación de datos para poder realizar el cambio de gráficos según lo seleccionado, y tampoco he entendido bien como lo enlazas todos los datos de la otra pestaña con los gráficos y con el desplegable.
    Me podrías detallar o especificar mejor los pasos, gracias

    1. Hola Hanadi,
      Lamentablemente en Excel 2007 no se puede usar la validación de datos entre otras hojas (o eso recuerdo, ya hace mucho que dejé de usar dicha versión).
      Saludos!
      Quique

  3. Buenas tardes: Quise seguir el Link y me manda a uan página de error, quisiera replicar en mi trabajo el cuadro de mando pero no puedo ver el ejemplo. Saludos y gracias.

  4. Hola Quique, gracias por compartir tu valiosa experiencia. Me podrias por favor ayudar con ejercicios que usen diferentes tipos de gráficos, las razones de su uso. Otros que requieran los números aleatorios y mariciales. Muchas gracias

    Juan

    1. Hola Juan,
      Por supuesto, nos ponemos “manos a la obra” y en breve verás algún ejercicio sobre el tema. Una pequeña duda, ¿qué son los números marciales?
      Un saludo,
      Quique

  5. Quique:
    Como hago para que al seleccionar un valor -excluyente- de un botón de opción me aparezca una tabla de datos y su gráfico correspondiente en la misma hoja y, nuevamente, al seleccionar un valor distinto me aparezca otra información diferente, también en la misma hoja.
    Gracias y disculpa por las molestias

    1. Hola Eugenio,
      La opción fácil es que toda la hoja esté linkada a dicho valor y usar el condicional SI o la función CHOOSE para que la información que se muestre sea diferente en función de la selección… es un trabajo arduo pero si son contenidos similares puede quedar chulo… ahora bien, ¿te has planteado la opción de poner un hipervínculo a diferentes zonas de la hoja para que al seleccionar se vean diferentes tablas y resultados?
      Saludos
      Quique

      1. Creo que no me he explicado claramente Quique.
        Lo que yo pretendo es precisamente lo que nos has enseñado en tu tutorial pero, en vez de utilizar listas desplegables se utilicen botones de opción.
        El escenario sería el siguiente: Dispongo de 4 hojas. En las 3 primeras (Enero, Febrero y Marzo) se incluyen, respectivamente, un gráfico y una imagen, es decir, cada hoja incluye 2 elementos, un gráfico y su imagen corporativa. En la 4ª hoja se insertarán 3 botones de opción, denominados precisamente Enero, Febrero y Marzo.
        LA IDEA ES LA SIGUIENTE: Al seleccionar de la hoja 4ª un botón de opción cualquiera, por ejemplo Marzo, se deberá mostrar en ESTA MISMA HOJA el contenido de la hoja Marzo; si nuevamente seleccionamos el botón Enero el contenido anterior se reemplazará por el contenido de la hoja Enero Y EN LA MISMA HOJA. Lo mismo debería suceder con el botón Febrero.
        Saludos y disculpa por las molestias que te estoy ocasionando

        1. Hola Eugenio,
          esto se puede hacer, sin duda… hay un camino corto que es mediante macros pero es más complejo y el camino largo que es mediante funciones condicionales que varíen los datos.
          En el ejemplo que puedes ver se hace con tablas dinámicas y los datos no cambian, simplemente se ajustan a las diferentes condiciones que vamos añadiendo mediante los desplegables.
          Creo que en tú caso sería mucho mejor ir variando de hoja mediante botones que tengan enlaces a las diferentes hojas. El efecto es el mismo.
          Saludos,
          Quique

  6. Me gusta su post, quise replicar pero me sale un error. Será posible mandarle por email el archivo para ver cual es el error? Le agradaceria mucho ya que quiero basarme en su modelo para replicar en mi trabajo

      1. Buenos días, Hay alguna forma de crear en forma automática (con macros) un filtro de datos, antes de importar un txt al Excel. Es decir, quiero abrir un txt con una macro, pero no quisiera que se incorporen algunas filas de ese archivo (o en su defecto, que se incorporen y sean borradas, pero en forma automática mediante alguna función de condición)

        1. Hola Areil,
          lo más fácil es copiar toda la información en Excel y después dedicarse a borrar la que no te interese tener. Puedes hacerlo mediante una macro o mediante un filtro avanzado… cuestión de gustos y número de veces que vayas a realizar la operación.
          Saludos!
          Quique

  7. Hola,

    Una duda acerca de las Tablas dinámicas:
    cómo se puede hacer para seleccionar y/o arrastrar varios campos a valores ? Tengo 24 campos que de no poder arrastrarlos todos de golpe los tendría que ir seleccionando y arrastrando a mano 1 a 1 (con la consiguiente perdida de tiempo y eficiencia…)

    Saludos y enhorabuena como siempre por el Blog,

    Alicia.

    1. Hola Alicia,
      A priori no se puede (no conozco la manera). Si es una tarea repetitiva puedes grabar una macro para hacerlo siempre.
      saludos.
      quique

  8. Hola, Quique,

    Entiendo que la lista de validación la usuarías para seleccionar Fecha semana y Manager ( en la esquina superiior derecha) . Mi pregunta también es, si has ligado estos 2 campos a las tablas y al grñafico dinámico para que muestren sólo l ainformación de GANDALF y SEMANA 25/12/2013.

    El formato está muy bien y la información es muy clara.
    Creo que aunque desde una suite de BI o un SAP business objects puedas descargarte los cuadros de mando, siempre te van a solicitar que los “personalices” para la persona que irá dirigida, es decir, que Excel va a ser siempre necesario.

    Saludos!!!

    1. Sí, ahí está el truco. Los resultados de las tablas de validación están ligadas con la hoja de análisis de los datos. Esto se consigue en las tablas (de las que beben los gráficos) que atacan a las tablas dinámicas y tienen información tanto de la semanana como del manager.

      1. Sí, eso es lo dificil, según entiendo, debería de convertir el rango en el que están asociados MANAGER y Departamento, por ejemplo por no hablar de las fechas , directamente en una lista desplegable?

        1. Correcto! No hace falta que sea super dinámico, aunque también se podría hacer. Simplemente necesitas un listado de los managers (para meter en una lista de validación de datos) y las fechas posibles (pasadas a formato tipo texto y disponibles a través de una listad e validación de datos.
          No es difícil, es un poco trabajoso.

        2. No, primero crear la lista y luego asociar la validación de datos a dicha lista. quizás de la otra manera se pueda hacer creando rangos dinámicos o con una macro… no lo recomiendo, demasiado engorroso!

          1. Jejee Gracias si lo consigo te lo digo!

            de momento si que me he creado la validacion a dicha Lista , ya tengo mi Lista depslegable en la misma hoja de los Gráficos….Pero me falta lo más importante asociar o bien los Graficos dinamicos a mi Lista desplegable…o bien las tablas dinámicas a Lista desplegable

Comentarios cerrados.