Modelado conceptual de datos en Excel: cómo diseñar la estructura

Antes de tocar una sola celda, los datos piden una idea clara de qué guardas y cómo se relaciona. Un modelo conceptual de datos es ese esquema previo: una hoja con clientes, pedidos y productos repetidos una y otra vez se convierte en tres tablas limpias cuando decides qué es cada cosa y cómo encaja. En este tutorial llevarás esa idea a un libro de Excel real, lo convertirás en Tablas que crecen solas y le harás preguntas con fórmulas.

Funciona en Excel 2007 y posteriores. BUSCARX y las matrices dinámicas requieren Excel 2021, 2024 o Microsoft 365.

Qué es un modelo de datos conceptual y para qué te sirve en Excel

Un modelo conceptual de datos es el plano de lo que vas a guardar antes de crear columnas. Sirve para evitar el caos de una hoja única donde el mismo dato se repite, se desactualiza y obliga a cambiarlo a mano en veinte sitios. Cuando lo dibujas, decides tres cosas: qué cosas existen (clientes, pedidos, productos), cómo se conectan (un cliente realiza un pedido; un pedido contiene productos) y qué datos describen a cada una.

En Excel este esquema se traduce en una Tabla por entidad, referencias cruzadas por identificadores y fórmulas que unen la información en el momento de consultarla. El resultado es un libro que crece sin romperse y que responde preguntas reales con un par de funciones. Piénsalo como una receta: primero los ingredientes por separado en botes bien etiquetados y luego los mezclas cuando los necesitas.

Las tres piezas: entidades, relaciones y atributos

El truco para no fallar es leer las preguntas del negocio en voz alta: «¿quién compró?», «¿qué compró?», «¿cuándo?». Las respuestas convierten sustantivos en entidades y verbos en relaciones.

Entidades: las cosas de las que guardas información

Una entidad es cualquier cosa de la que necesitas datos propios y repetibles. Se saca de las preguntas de investigación del proyecto: si alguien pregunta «¿cuánto gasta cada cliente?» necesitas una entidad Cliente para poder responder.

En el libro que verás en este tutorial hay tres entidades: Clientes (datos de cada persona o empresa que compra), Pedidos (cada compra realizada) y Productos (el catálogo disponible). Otro ejemplo conocido: en un proyecto de registro de incidentes policiales las entidades podrían ser Oficial, Incidente y Persona afectada, cada una con sus datos guardados por separado.

Relaciones: cómo se conectan unas entidades con otras

Una relación se escribe como una frase corta con la fórmula sustantivo–verbo–sustantivo: Cliente realiza Pedido, Pedido contiene Producto. Cuando leas tus preguntas, busca el verbo: casi siempre te está diciendo qué relación falta.

A veces una relación se convierte ella misma en una entidad. Esto ocurre cuando una relación tiene datos propios además de conectar dos cosas: por ejemplo, Pedido contiene Producto necesita una entidad intermedia LíneaDePedido porque cada pedido puede incluir varios productos y cada línea tiene su cantidad, su precio del día y su descuento. Cuando veas que una frase «sustantivo verbo sustantivo» necesita más columnas, añade una entidad intermedia.

Atributos: los datos que describes de cada entidad

Un atributo es un dato puntual de una entidad. La regla de oro es guardar el dato de origen, no el resultado: la fecha de nacimiento en vez de la edad, el precio unitario en vez del total calculado, el país en vez del código postal internacional.

Pasar el modelo a una hoja de Excel: una tabla por entidad

La idea es simple: el modelo se convierte en un libro con tres hojas, una por entidad, con los encabezados en la fila 1 y los datos debajo, una fila por registro. Empezarás con Clientes.

Crear la hoja Clientes con sus atributos

1. En un libro nuevo, ve a la pestaña de la primera hoja y renómbrala como Clientes (doble clic sobre el nombre).
2. Escribe los encabezados en A1:D1: IdCliente, Nombre, Ciudad, Pais.
3. Rellena las cinco filas de abajo a mano, con identificadores únicos.

Crear la hoja Pedidos referenciando a Clientes y Productos

Ahora la entidad Pedidos. La regla importante: no copias el nombre del cliente en cada fila, guardas solo su IdCliente y dejarás que una fórmula lo traiga.

1. Crea una hoja nueva y llámala Pedidos.
2. Escribe los encabezados en A1:E1: IdPedido, Fecha, IdCliente, IdProducto, Importe.
3. Rellena cinco filas a mano. El IdCliente debe coincidir con uno existente en la hoja Clientes.

Crear la hoja Productos y unir los datos en una vista

La entidad Productos es la más corta. Crea la hoja con sus encabezados y un par de filas, y luego montaremos la hoja Resumen con las uniones.

Unir con BUSCARV (en inglés VLOOKUP)

BUSCARV es la fórmula clásica para traer el nombre del cliente y el producto a cada pedido a partir de su identificador.

1. Crea la hoja Resumen con los mismos encabezados que Pedidos más dos columnas extra: Cliente en F y Producto en G.
2. Copia A2:E2 de Pedidos y pega en A2:E2 de Resumen.
3. Selecciona F2 y escribe la fórmula, fijando el rango con referencias absolutas:

=BUSCARV(C2;Clientes!$A:$D;2;FALSO)
=BUSCARV(D2;Productos!$A:$B;2;FALSO)

1. Arrastra el controlador de relleno de F2 y G2 hacia abajo hasta la última fila.

Unir con BUSCARX (en inglés XLOOKUP) en Excel 365 y 2021

BUSCARX existe desde Excel 2021, en Excel 2024 y en Microsoft 365; en Excel 2019 o 2016 muestra el error #¿NOMBRE? y tienes que volver a BUSCARV o a INDICE y COINCIDIR. Permite buscar en cualquier dirección y dejar un mensaje si no encuentra nada.

=BUSCARX(C2;Clientes!$A:$A;Clientes!$B:$B;"No existe")
=BUSCARX(D2;Productos!$A:$A;Productos!$B:$B;"No existe")

Unir con INDICE y COINCIDIR (INDEX y MATCH)

Esta combinación funciona en cualquier versión desde Excel 2007 y se entiende muy bien cuando la otra falla.

=INDICE(Clientes!$B:$B;COINCIDIR(C2;Clientes!$A:$A;0))
=INDICE(Productos!$B:$B;COINCIDIR(D2;Productos!$A:$A;0))

Convertir cada hoja en una Tabla de Excel para que crezca sola

Convertir cada hoja en Tabla hace que el modelo sea a prueba de crecimiento. Cuando conviertes un rango en Tabla, las fórmulas que apunten a sus columnas se actualizan al añadir filas.

1. Selecciona A1:D6 en Clientes.
2. Ve a Insertar > Tablas > Tabla. Acepta que la tabla tiene encabezados.
3. En Diseño de tabla > Propiedades > Nombre de la tabla escribe Clientes.
4. Repite los pasos con Pedidos (llamándola Pedidos) y Productos (llamándola Productos).

Por qué la Tabla cuida el modelo: filas nuevas, nombres fijos y referencias estables

La Tabla trata los datos como un solo objeto con nombre propio. Al añadir una fila en Pedidos, las fórmulas que miran Pedidos[IdCliente] se amplían solas, y al pegar más filas dentro de la tabla las referencias estructuradas las recogen sin tocar nada.

Comprobar el modelo con preguntas reales

Las preguntas que definiste al principio se responden ahora con una fórmula cada una sobre las Tablas del paso anterior.

«¿Cuánto ha comprado cada cliente?»

1. Crea una hoja VentasPorCliente con encabezados IdCliente y TotalComprado en A1:B1.
2. Copia IdCliente de Clientes en A2:A6.
3. En B2 escribe:

=SUMAR.SI.CONJUNTO(Pedidos[Importe];Pedidos[IdCliente];A2)

1. Arrastra desde B2 hasta B6.

«¿Qué productos no se han vendido nunca?»

1. Crea una hoja SinVentas con encabezados IdProducto y SinPedidos en A1:B1.
2. Copia IdProducto de Productos en A2:A5.
3. En B2 escribe:

=SI(CONTAR.SI.CONJUNTO(Pedidos[IdProducto];A2)=0;"Sin ventas";"Vendido")

1. Arrastra desde B2 hasta B5 y aplica filtro automático (Ctrl+Mayús+L) para ver solo los que dicen «Sin ventas».

Errores típicos del modelo que se notan en la hoja

Tres fallos del esquema aparecen tarde o temprano en cualquier hoja: atributos calculados que se quedan viejos, entidades escondidas dentro de otras y pegados que rompen la Tabla.

Atributos estáticos que se quedan viejos

Una columna Edad parece útil y no lo es: el 1 de enero deja de servir. Lo correcto es guardar FechaNacimiento y calcular la edad cuando la necesites con =SIFECHA(FechaNacimiento;HOY();"A"). Así el dato se mantiene correcto un año tras otro sin tocar nada.

Entidades escondidas dentro de otra entidad

Una sola celda como Dirección completa mezcla calle, número, piso, ciudad y código postal. Cuando quieras filtrar por ciudad tendrás que «desmenuzar» la cadena a mano. La solución es partirla en columnas (Calle, Número, Piso, Ciudad, CódigoPostal) usando Datos > Herramientas de datos > Texto en columnas y guardando cada parte como un atributo propio.

Pegar datos por encima rompe las reglas de la tabla

Copiar datos nuevos y pegarlos justo debajo de la última fila de una Tabla los deja fuera de la Tabla: las fórmulas y los filtros dejan de verlos y el modelo se rompe sin aviso. La solución es pegar dentro del rango de la Tabla (clic en la última fila y pegar), o alimentar la Tabla con Power Query, que lleva los pasos de limpieza grabados.

Lo que no cubre este enfoque en una sola hoja

Excel aguanta muy bien este enfoque con miles de filas, pero tiene fronteras claras.

Miles de filas en una sola tabla

Una hoja tiene 1.048.576 filas y 16.384 columnas, así que el límite físico no es problema hasta llegar a varios millones de registros. Donde sí aparece es en la velocidad: una sola Tabla enorme con fórmulas y formatos se vuelve lenta al pasar de algunos cientos de miles de filas.

Varios usuarios escribiendo a la vez

Excel no es una base de datos relacional: cuando dos personas guardan a la vez, una de las dos versiones se pierde. Para edición compartida con validación, formularios y bloqueos, existe Access o un sistema gestor de bases de datos (SQL Server, PostgreSQL, MySQL). La elección depende del tamaño y de los permisos de cada organización.

Lo que ya sabes hacer con tu modelo en Excel

• Has pasado una lista repetida a tres entidades con sus atributos.
• Tienes un libro con una hoja por entidad y referencias cruzadas por identificadores.
• Cada hoja es una Tabla con nombre, lo que mantiene estables las referencias.
• Respondes preguntas de negocio con SUMAR.SI.CONJUNTO y CONTAR.SI.CONJUNTO.
• Sabes qué errores vigilar para que el modelo no se rompa al crecer.

El siguiente paso es alimentar esas Tablas desde Power Query: conectar a archivos, limpiar de una vez y aplicar siempre los mismos pasos de transformación con un clic.

Dudas al aplicar el modelo conceptual de datos en Excel