Modelado de bases de datos en Excel: diseña y documenta tus tablas

El modelado de bases de datos en Excel consiste en organizar la información en tablas relacionadas: clientes, productos y pedidos. Cada tabla guarda un tipo de registro y se conecta con las demás mediante identificadores, evitando repetir datos y facilitando consultas y mantenimiento.

En este tutorial construirás un libro de ventas pequeño y documentado. Terminarás con tres tablas, un importe calculado, filtros y comprobaciones de referencias. El método funciona en Excel 2007 y posteriores, Microsoft 365, Excel para la web, Mac y móviles; las funciones y la interfaz pueden variar ligeramente.

Qué es el modelado de bases de datos en Excel

El modelado de datos es la forma de decidir qué información representa cada entidad y cómo se relacionan esas entidades. En nuestro libro Ventas separaremos los datos en tres bloques:

• Clientes: identificador, nombre y ciudad.
• Productos: referencia, producto y precio unitario.
• Pedidos: número, identificador de cliente, referencia de producto, cantidad e importe.

Un pedido no necesita copiar el nombre del cliente ni el precio del producto. Basta guardar su identificador y su referencia. Así, si cambia el precio de un producto, solo se modifica en la tabla Productos.

Esta separación es la idea esencial del modelado: cada fila representa un registro, cada columna un atributo y cada identificador una relación. Excel no crea un sistema de base de datos con servidor, permisos o concurrencia. Sí permite mantener y consultar un modelo pequeño dentro de un libro. El modelo completo se apoya en estructuras de tabla de Excel.

Dónde crear las tablas del modelo en Excel

Las tablas deben estar en rangos separados, con una fila de encabezados y sin celdas combinadas. Cada bloque tendrá una fila por registro.

1. Selecciona el rango de clientes.
2. Abre la ruta siguiente:

1. Confirma que la opción La tabla tiene encabezados está marcada.
2. Escribe un nombre representativo, como tblClientes, y pulsa Aceptar.

Repite la operación con los bloques de productos y pedidos. Una tabla de Excel aplica formato, mantiene filtros y adapta las fórmulas al añadir filas. No crea claves primarias ni relaciones automáticas: esas relaciones se documentan mediante identificadores.

Definir clientes, pedidos y productos como entidades

Una entidad es un conjunto de cosas que puedes describir con los mismos atributos. En este modelo, cada cliente tiene un identificador, un nombre y una ciudad.

Los pedidos guardan el IdCliente y la RefProducto. El identificador de pedido, PED-001, distingue sus registros.

Añadir claves primarias y relaciones entre las tablas

La clave primaria es el identificador que debe ser único dentro de su tabla. En este modelo:

• IdCliente identifica una fila de Clientes.
• RefProducto identifica una fila de Productos.
• IdPedido identifica una fila de Pedidos.

Para relacionar Pedidos con Clientes, la columna IdCliente guarda el mismo valor que aparece en la tabla Clientes. Por ejemplo, CLI-002 conecta un pedido con Binary. Lo mismo sucede con RefProducto.

Excel no impone que CLI-002 exista ni que sea único. Esa validación se realiza en las pasos de comprobación.

Diseñar una tabla de ventas que se pueda consultar

Una tabla de ventas debe contener los datos de cada pedido y los cálculos derivados. Para este libro, cada fila incluye cliente, producto, cantidad, precio unitario e importe.

Si el precio se encuentra en Productos, la forma más sencilla de este ejercicio es traer el precio al pedido mediante una referencia. Para consultar un cliente concreto no hace falta crear otra tabla: se filtra la columna IdCliente.

En las siguientes prácticas construiremos el mismo libro paso a paso. La estructura final tiene clientes en A1:C4, productos en E1:G4 y pedidos en I1:M5.

Calcular el importe de cada pedido

En esta versión sencilla, cada fila de Pedidos incluye el precio unitario y el importe se obtiene multiplicando cantidad por precio.

La fórmula es:

=K2*L2

Al copiarla hacia abajo, K3 y L3 sustituyen a K2 y L2. Si una celda de precio queda vacía, el importe sehopefully calculará como cero; para exigir un precio informado habrá que añadir una comprobación, no solo una multiplication.

Consultar los pedidos de un cliente concreto

1. Selecciona cualquier celda de la tabla de pedidos.
2. En la cinta, abre:

1. Pulsa la flecha de IdCliente.
2. Desactiva Todos y elige CLI-002.

Solo quedará visible PED-001, el pedido vinculado con Binary. No se duplica el nombre del cliente porque la tabla contiene su identificador. Si después ejecutaste consultas en otra hoja, puedes filtrar en la copia y no en la tabla original.

Un modelo pequeño con datos de clientes y pedidos

Vamos a montar el libro Ventas. Los tres bloques estarán separados por una columna vacía, lo que facilita leerlos y convertirlos en tablas independientes.

La distribución inicial será:

El modelo utiliza identificadores de texto para que las relaciones sean legibles. Si el negocio necesita otros tipos de clave, se documentan en el diccionario.

Escribir y ajustar las tablas de referencia

1. Renombra la hoja actual como Ventas.
2. Escribe los datos de clientes en A1:C4.
3. Escribe los productos en E1:G4.
4. Escribe los pedidos en I1:M5.
5. Convierte cada bloque en una tabla mediante Insertar > Tablas > Tabla.
6. Asigna los nombres tblClientes, tblProductos y tblPedidos.

Cada fila de clientes representa una persona o empresa; cada fila de productos, un artículo; cada fila de pedidos, una compra.

Insertar fórmulas sin romper la relación entre datos

Ahora añadimos la última columna de Pedidos: Importe.

1. Escribe Importe en M1.
2. En M2, escribe =K2*L2.
3. Selecciona M2 y arrastra el controlador de relleno hasta M5.

=K2*L2

Las relaciones siguen en J y K: cambiar un IdCliente o una RefProducto no rompe el cálculo del importe.

Documentar el modelo para que otros entiendan su estructura

Un modelo sin documentación puede ser correcto hoy y confuso dentro de un mes. La hoja Diccionario explicará qué contiene cada tabla, qué significa cada campo y cómo se relacionan las entidades.

El diccionario no sustituye los datos. Es una descripción legible: permite distinguir IdCliente de IdPedido, explicar que RefProducto es una clave y dejar escrita la regla que debe cumplir un nuevo registro.

Registrar las relaciones entre las entidades

Las relaciones del modelo son:

• Clientes 1:N Pedidos: Pedidos.IdCliente referencia Clientes.IdCliente.
• Productos 1:N Pedidos: Pedidos.RefProducto referencia Productos.RefProducto.
• Clientes N:1 Clientes: cada IdCliente identifica como máximo un cliente.
• Productos N:1 Productos: cada RefProducto identifica como máximo un producto.

En la hoja Diccionario, crea una tabla con estas columnas: Origen, Origen.Campo, Destino, Destino.Campo y Regla.

Comprobar que los datos del modelo son coherentes

Una estructura clara puede contener datos incoherentes. Las comprobaciones deben responder a tres preguntas: ¿las claves están repetidas?, ¿faltan identificadores en pedidos? y ¿las referencias apuntan a registros existentes?

Para este libro usaremos CONTAR.SI (COUNTIF). La función cuenta cuántas veces aparece un valor dentro de un rango.

=CONTAR.SI($A$2:$A$4;A2)

• $A$2:$A$4 permanece fijo al copiar la fórmula.
• A2 cambia para revisar cada cliente.

En el área de comprobaciones, calcula cuántos pedidos tiene cada cliente. Si un cliente no aparece en ningún pedido, el resultado será cero. Esa cifra no siempre es un error: puede ser correcto; por eso la revisión combina fórmulas y filtros.

Detectar identificadores repetidos o referencias incompletas

Para buscar clientes repetidos:

1. Abre la tabla tblClientes.
2. Pulsa la flecha de filtro de IdCliente.
3. Revisa si algún valor aparece en varias filas.

Para detectar una referencia de pedido inexistente, coloca CLI-999 en un IdCliente de prueba y filtra la columna. El filtro solo mostrará esa coincidencia; después debes buscar CLI-999 en la tabla Clientes. Si no aparece, la referencia está incompleta o equivocada.

En el área de comprobaciones puedes contar pedidos y clientes registrados:

=CONTAR(Ventas!$I$2:$I$5)

=CONTAR(Ventas!$A$2:$A$4)

El primer resultado es 4; el segundo, 3. No deben ser iguales: cada pedido necesita un cliente, pero un cliente puede tener cero, uno o varios pedidos.

Revisar el modelo después de añadir registros

Añadimos un pedido para comprobar que el libro admite nuevos registros.

1. Ve a la última fila de tblPedidos.
2. Escribe PED-005, CLI-003, PRD-B, 4 y 2,25.
3. En la celda siguiente, escribe =K6*L6.
4. Presiona Ctrl+Enter si el cursor está dentro de la tabla, para confirmar la fórmula en la celda seleccionada.
5. Revisa los filtros de las tres tablas.

La fórmula usa K6 y L6, por lo que 4 × 2,25 produce 9,00. Los filtros siguen incluyendo la fila nueva.

Qué errores aparecen al relacionar tablas en Excel

Corregir referencias que no encuentran el registro esperado

Un error frecuente es escribir CLI 002 con un espacio en lugar de CLI-002. La referencia ya no coincide con la clave guardada.

Lo que se ve: el filtro de IdCliente no agrupa CLI 002 con CLI-002.

La causa: el espacio forma parte del texto y la clave no coincide.

El arreglo: corrige la celda a CLI-002. Después busca esa clave en Clientes con un filtro. No deduzcas el registro solamente por su posición en la tabla: el orden puede cambiar.

Otro error es usar CLI-003 donde debería ir CLI-002. La referencia existe, pero apunta al cliente equivocado. Para encontrarlo, filtra ambos valores y compara el pedido con el registro de clientes.

Evitar duplicados al copiar o pegar registros

Si copias PED-001 y lo vuelves a pegar en otra fila, tendrás dos registros con la misma clave primaria.

Lo que se ve: al filtrar IdPedido, PED-001 aparece en dos filas.

La causa: se duplicó una clave que debe ser única.

El arreglo:

1. Activa el filtro de IdPedido.
2. Localiza las repeticiones.
3. Conserva la fila correcta.
4. Elimina la fila duplicada completa, no solo su contenido.
5. Revisa que el importe y las referencias de la fila conservada sigan siendo válidos.

La duplicación no siempre se detecta solo contando filas. Usa el filtro y revisa también IdCliente y RefProducto.

Si una fórmula se copia solo hasta M4, la fila M5 puede quedar vacía o mostrar un resultado incorrecto. El arreglo es seleccionar M2:M5 y usar el controlador de relleno, o copiar la fórmula desde la última fila correcta.

Qué puede y qué no puede hacer Excel como modelador

Excel puede:

• Organizar registros en tablas separadas.
• Identificar filas con claves primarias.
• Documentar relaciones mediante campos relacionados.
• Calcular importes y otros valores derivados.
• Filtrar, ordenar y resumir datos.
• Detectar duplicados y referencias vacías mediante filtros y fórmulas.
• Ampliar el libro con más registros y tablas.

Excel no funciona por sí solo como una base de datos de servidor:

• No centraliza las validaciones entre varios libros.
• No coordina cambios simultáneos de varios usuarios.
• No ofrece permisos detallados como un sistema de gestión de bases de datos.
• No impone claves foráneas ni tipos de campo con la fuerza de un SGBD.
• No mantiene relaciones automáticas cuando se borra una fila.
• No sustituye copias de seguridad, auditoría y control de concurrencia.

Si el modelo necesita acceso simultáneo, permisos, validación centralizada o un volumen elevado de operaciones, hay que valorar Access u otro sistema de gestión de bases de datos. Herramientas como Microsoft Visio o dbdiagram.io pueden ayudar a diseñar y representar un esquema, pero no sustituyen el almacenamiento del libro.

Lo que ya sabes hacer con un modelo de bases de datos

• Crear tablas separadas para clientes, productos y pedidos.
• Identificar cada registro con una clave primaria.
• Relacionar pedidos con clientes y productos mediante sus identificadores.
• Calcular el importe de cada pedido y copiar la fórmula hacia abajo.
• Consultar los pedidos de un cliente con un filtro.
• Documentar entidades, campos y relaciones en una hoja de diccionario.
• Detectar claves repetidas, referencias incorrectas y datos incompletos.
• Añadir registros sin rehacer las tablas ni sus fórmulas.

El siguiente paso es ampliar el modelo con una tabla de productos, catálogos y existencias, manteniendo cada dato en su entidad correspondiente.