Cuadro de mando en Excel desde cero: masterclass

Una masterclass de unos 50 minutos que recorre el camino completo: desde varios ficheros de texto sueltos en una carpeta hasta un cuadro de mando terminado, con filtros, gráficos y KPIs. Sin complementos de pago y sin salir de Excel.

Los tres pasos que tiene cualquier cuadro de mando

Antes de tocar nada, conviene tener claro el esqueleto. Todo cuadro de mando pasa por tres fases, y saltarse la primera es el error más caro:

1. Traer los datos y consolidarlos desde su origen.
2. Preparar y enriquecer la información para que la parte visual sea sencilla.
3. Montar el cuadro final, que es lo que se ve y lo que se enseña.

El ejemplo: una empresa que comercializa vinos y licores, con las ventas del último trimestre repartidas en varios ficheros de texto, uno por mes y tipo de producto.

Fase 1: consolidar varios ficheros de una carpeta

En Datos → Obtener datos → De una carpeta. Excel abre un asistente, lee la carpeta entera y lista lo que hay dentro.

Aquí aparece el primer detalle práctico: en esa carpeta también hay libros de Excel que no queremos incluir. Se resuelve con un filtro por extensión, igual que filtrarías una columna en una hoja. También podrías filtrar por nombre o por fecha del fichero.

Con la selección hecha, Combinar archivos. Excel muestra cómo va a leer cada uno, confirmas la estructura y carga todo en una única tabla.

Por qué merece la pena hacerlo así y no copiando y pegando: un análisis puntual sirve una vez, pero lo que aporta valor de verdad es poder repetirlo cada cierre de mes sin volver a montarlo. Esta carga es Power Query, y queda grabada: el mes que viene, actualizas y ya está.

Fase 2: enriquecer los datos

Los datos que llegan casi nunca traen todo lo que necesitas. Aquí faltan dos cosas: el mes y la familia de producto. Se consiguen por dos caminos distintos, y los dos son muy reutilizables.

El mes, extraído del nombre del fichero

El periodo está dentro del nombre del fichero, entre corchetes. La técnica es localizar los dos corchetes y sacar lo que hay en medio:

• ENCONTRAR devuelve la posición de la primera aparición de un carácter. Se usa dos veces: una para el corchete de apertura y otra para el de cierre.
• EXTRAE saca el trozo intermedio: empieza en la posición del primer corchete más uno, y toma tantos caracteres como la diferencia entre ambas posiciones menos uno.

Con el octubre 2024 ya aislado, se parte en dos:

• El año con DERECHA, cuatro caracteres, que siempre son cuatro.
• El mes con IZQUIERDA, y aquí está el truco: como el nombre del mes varía de longitud, el número de caracteres se calcula con LARGO del texto completo menos cinco (los cuatro del año y el espacio).

Cinco funciones de texto que resuelven la inmensa mayoría de los "sácame este dato de dentro de esta cadena".

La familia de producto, con una tabla de mapeo

Un mapeo es una tabla de apoyo que enriquece los datos originales. Aquí, para cada producto indica su grupo grande (vino o licor) y su subcategoría.

Se trae con BUSCARV, usando el nombre del producto como campo de enlace: valor buscado, tabla de mapeo, número de columna que quieres de vuelta (2 para el grupo, 3 para la subcategoría) y coincidencia exacta, que es FALSO.

Este paso es el que permite aplicar jerarquías, y sirve para mucho más que productos: agrupar puntos de venta por zonas, personas por departamento, servicios por línea de negocio.

Fase 3: tablas dinámicas, una por gráfico

Lo que no se ve del cuadro de mando son las tablas dinámicas. Cada gráfico necesita la suya, con la estructura exacta que ese gráfico va a dibujar.

Una tabla dinámica tiene cuatro zonas: filas, columnas, valores y filtros. Dos recomendaciones del vídeo para no liarse: en columnas, una sola categoría; en filas puedes poner varias, porque la tabla simplemente se alarga y se sigue leyendo.

Se monta la primera (meses en filas, año en columnas, unidades en valores) y las siguientes se copian y se pegan debajo, cambiando el campo: por grupo de producto, por población, por tipo.

Segmentación de datos en vez de filtros

La zona de filtros funciona, pero la segmentación de datos es mucho más amable para quien va a usar el cuadro: botones, clic, y con Ctrl selección múltiple. Se insertan desde Analizar tabla dinámica → Insertar segmentación de datos.

Fase 4: los gráficos y los KPIs

Cada gráfico se crea seleccionando su tabla y usando Insertar → Gráficos recomendados. Después:

• Ocultar los botones de campo, que ensucian el resultado.
• Elegir un diseño de los precargados, evitando el primero, que es el que usa todo el mundo.
• Aplicar la misma paleta de colores en todos los gráficos, que es lo que da coherencia visual al conjunto.
• En las barras horizontales, si te descoloca ver el valor mayor arriba: botón derecho sobre el eje de categorías → Categorías en orden inverso.

Los cuadros de KPI (esos recuadros con un número grande) no son gráficos: son formas. Insertas un rectángulo, lo seleccionas y en la barra de fórmulas escribes = y la celda de la dinámica que contiene el valor. El número se actualiza al filtrar.

Un aviso que ahorra tiempo: al vincular una forma a una celda, la forma pierde el formato. Se recupera con el copiador de formato desde otra que ya esté bien.

El paso que lo convierte en un cuadro de mando

Al principio, al filtrar, solo cambia un dato. El motivo es que la segmentación se creó sobre una tabla dinámica concreta y solo controla esa.

La solución: seleccionar la segmentación → Conexiones de informes → marcar todas las tablas dinámicas del cuadro. Hay que repetirlo con cada segmentación. A partir de ahí, un clic mueve el cuadro entero.

Lo último es cosmético: logo, medidas, y ocultar las columnas donde viven las tablas dinámicas.

Funciones clave

• ENCONTRAR, EXTRAE, IZQUIERDA, DERECHA, LARGO — el kit completo para sacar datos de dentro de un texto.
• BUSCARV con coincidencia exacta — el mapeo que enriquece los datos de origen.

Conclusión

La parte visual es la que luce, pero ocupa la menor parte del trabajo. Lo que sostiene un cuadro de mando útil es la fase de carga y preparación: si esa parte está sistematizada, el mes siguiente es pulsar actualizar.

Y el objetivo final es más importante que el fichero: dejar de depender de que alguien te prepare el informe. Con Excel se llega a resultados profesionales, y en el grupo de WhatsApp de InflueXcel hay gente montando esto a diario y resolviendo dudas por el camino.

Más contenido de Excel en InflueXcel

• Acumular el saldo en PIVOTARPOR: una función de agregación distinta por columna Caso
  Nuevo caso que empieza con una suposicion razonable y acaba en una contraprueba del propio autor. Juan trae un libro mayor en bruto y quiere
• CONTAR.SI no acepta ELEGIRCOLS: por qué las funciones .SI exigen una referencia y no una matriz Caso
  Hay errores de Excel que te mandan a buscar en la dirección equivocada, y este es de manual. Joan Recasens llega al grupo con una fórmula qu
• Dar formato a la última fila de una tabla que crece (y el techo del formato condicional) Caso
  Un miembro de la comunidad llega con una tabla cuyo rango real va de la columna embalaje a la columna total, y cuya columna de numeración co
• Colorear la fila entera según el técnico asignado: la referencia mixta que casi nadie aplica bien Caso
  Esta semana surgió una duda que parece de principiante y en realidad esconde el concepto peor entendido del formato condicional. Johann Frar
• Del VBA a una sola fórmula: calcular el RFC mexicano con LET, MAP y expresiones regulares Caso
  Hector llega al grupo con un problema que no se ve todos los días: tiene resuelto el cálculo del RFC mexicano, pero lo tiene en VBA, y quier
• Un rango desbordado como serie de un gráfico: el nombre tiene que ser de ámbito Hoja Caso
  Miki llega al grupo con un caso de esos que no dan error, simplemente no funcionan. Quiere un gráfico que crezca solo: si mañana hay más dat
• Tabla de clasificación de una liga que se ordena sola: tres formas de montarla Caso
  De madrugada, Hector lanzó al grupo una petición que suena sencilla y que acabó generando tres enfoques radicalmente distintos. Tiene un lib
• Conciliación contable: una sola fórmula por hoja que cuadra, descuadra y avisa Caso
  Esta semana alguien preguntó en el grupo si tenía sentido montarse un fichero para conciliaciones bancarias. La respuesta llegó en forma de
• Comentarios persistentes junto a AGRUPARPOR/PIVOTARPOR Caso
  Juan plantea un problema muy habitual para quienes trabajan con PIVOTARPOR o AGRUPARPOR: añadir comentarios o anotaciones en columnas auxili
• Siguiente consecutivo alfanumérico: calcular matrículas con reglas de prioridad Caso
  Un miembro de la comunidad, J. Gil, planteó un problema clásico con un giro interesante: calcular el siguiente consecutivo de una matrícula