Diseño de Modelos de Gestión en Excel con Listas y Tablas Dinámicas

Aprende a construir un modelo de gestión en Excel que integra listas desplegables, BUSCARV, tabla dinámica y un semáforo con formato condicional. Con estos pasos lograrás una matriz simple y potente para consultar datos por código y visualizar el desempeño de inversión real vs. expectativa.

¿Cómo crear la base con listas desplegables en Excel?

Para controlar la entrada de datos, se construye una lista desplegable en la casilla de código. Así solo se aceptan los códigos posibles desde B13 hasta B332, es decir, trescientas veinte opciones.

• Ir a Datos > Validación de datos > permitir: lista.
• Seleccionar el rango B13:B332 con Control + Shift + flecha abajo.
• Aceptar y probar que el menú despliegue códigos.

Este control garantiza consistencia y evita errores de digitación. Además, puedes desplegar con el teclado usando Alt + flecha abajo.

¿Qué atajos aceleran el proceso?

• Control + Shift + flecha abajo: seleccionar rangos largos.
• Control + Página abajo: cambiar de hoja.
• Alt + flecha abajo: abrir la lista desplegable.

¿Cómo llenar nombres automáticamente con buscar v?

El objetivo es que, al elegir un código, se recupere el nombre desde una base en otra hoja. La matriz tiene el código en la primera columna y el nombre en la segunda, ideal para BUSCARV.

• Insertar BUSCARV y abrir con Tab.
• Valor_buscado: la celda del código.
• Matriz: seleccionar en Hoja 2 la tabla completa y fijar con referencias absolutas.
• Indicador de columna: 2, porque el nombre está en la segunda columna.
• Coincidencia exacta: falso.

Se valida que el código 1 corresponde a “Valentina”. Luego, al cambiar el código, el nombre se actualiza de forma inmediata. Esto muestra el uso principal de BUSCARV: traer un dato relacionado según un identificador único.

¿Qué buenas prácticas aplicar?

• Fijar la matriz con referencias absolutas para evitar errores al copiar la fórmula.
• Usar coincidencia exacta: falso para resultados precisos.
• Probar con varios códigos para verificar correspondencias.

¿Cómo integrar la tabla dinámica y crear un semáforo?

Se aprovecha una tabla dinámica ya creada en la Hoja 2 para traer la inversión real y la expectativa 2018 al modelo. La clave es vincular el código de la tabla dinámica a la celda donde el usuario elige el código.

• Ubicar el valor de “inversión real” del código seleccionado y presionar Enter.
• Editar la referencia del campo “código” en la tabla dinámica: reemplazar el número escrito por la celda del código (por ejemplo, B8).
• Repetir con “expectativa 2018”.

Al cambiar el código, las cifras se actualizan automáticamente. Ejemplo: con código 2 (Guillermo), la inversión real es 40 millones y la expectativa es 10 millones.

¿Cómo construir el semáforo con formato condicional?

Primero, se configura una fórmula condicional que devuelve 3, 2 o 1 según la relación entre inversión y expectativa. Luego, esos números se traducen a íconos.

• Fórmula base en la celda junto a “expectativa 2018”:
• Si D8 > E8, devolver 3 (verde).
• Si D8 = E8, devolver 2 (amarillo).
• En otro caso, devolver 1 (rojo).
• Ir a Inicio > Formato condicional > Nueva regla > aplicar formato a todas las celdas según sus valores.
• Elegir conjunto de íconos tipo semáforo.
• Definir reglas:
• Verde: mayor o igual a 3.
• Amarillo: menor que 3 y mayor o igual a 2.
• Rojo: menor que 2.

Pruebas rápidas muestran el comportamiento esperado: si la inversión real es menor a la expectativa (por ejemplo, expectativa 50 millones frente a inversión 40 millones), el semáforo aparece en rojo. Si son iguales, amarillo. Si la inversión supera la expectativa, verde.

¿Qué extensión puedes intentar después?

Repite el flujo usando como clave el mail en lugar del código. Si es necesario, ajusta la tabla de datos. Mantén la lógica: lista para seleccionar, BUSCARV para traer información, vínculo con tabla dinámica y semáforo con formato condicional.

¿Te gustaría que exploremos otra métrica para el semáforo o una versión con XLOOKUP y medidas en Power Pivot? Cuéntalo en los comentarios y comparte qué parte te resultó más útil.