Diagrama de modelo de datos en Excel: guía para empezar

Un diagrama de modelo de datos es la representación visual de qué información contiene tu libro y cómo se relaciona. En este tutorial tomaré el ejemplo de una tienda con clientes, pedidos y productos: construirás las hojas, definirás sus identificadores y representarás las relaciones con formas y conectores.

El método funciona en Excel 2007 y versiones posteriores, incluidos Excel 2021, 2024, Microsoft 365, Excel para Mac, web y móviles. Las hojas de cálculo y las formas están disponibles en todas ellas; las funciones que aparecen al final se limitarán a CONTAR.SI (COUNTIF).

Qué es un diagrama de modelo de datos

Un diagrama de modelo de datos muestra la estructura de la información antes de construir fórmulas, informes o tablas resúmenes. En el libro Modelo, cada hoja representará una entidad: Clientes, Pedidos y Productos. El diagramaAllowlist将这些关系画出来，relaciones a la izquierda columna representativas qué。

Por ejemplo, la hoja Clientes contiene información de las personas que compran. La hoja Pedidos registra las compras mediante un identificador y el identificador del cliente. Productos guarda el catálogo. La relación se establece con identificadores coincidentes, no con nombres, porque dos clientes pueden llamarse igual y un nombre de producto puede cambiar.

El diagrama no calcula por sí solo. Es una guía visual para detectar datos repetidos, revisar claves y decidir qué información debe estar en cada hoja. En nuestro caso, ayuda a mantener una sola ficha por cliente y una sola ficha por producto.

Al terminar, el diagrama habrá documenting estas relaciones:

• Un cliente puede tener muchos pedidos.
• Un pedido puede contener muchos productos.
• La tabla DetallePedido conecta pedidos y productos sin repetir sus nombres.

Qué muestra un diagrama de modelo de datos

Un diagrama útil distingue cuatro elementos:

Las entidades se convierten en tablas rectangulares. Sus atributos se convierten en columnas y cada registro ocupa una fila. Las claves primarias identifican un registro de forma única dentro de una tabla.

Primero conviene identificar las claves primarias. Después se representarán las claves foráneas: columnas que guardan el identificador de otra tabla. Por ejemplo, IDCliente es clave primaria en Clientes y clave foránea en Pedidos.

Entidades y atributos: las piezas del modelo

La entidad Pedido contiene una fila por compra. Sus atributos principales son IDPedido, IDCliente, Fecha e Importe. El identificador del pedido permite distinguir dos compras aunque se_packeton el mismo día y para el mismo cliente.

Aquí, IDPedido es la clave primaria. IDCliente identifica al cliente asociado, mientras que Fecha e Importe describen el pedido. Conviene guardar aquí solo los datos propios del pedido; el nombre y el correo del cliente deben permanecer en Clientes.

Relaciones entre tablas: uno a varios y varios a varios

La relación entre Clientes y Pedidos es de uno a varios: cada cliente puede tener varios pedidos, pero cada pedido corresponde a un cliente.

La relación entre Pedidos y Productos es de varios a varios: un pedido puede incluir varios productos y un mismo producto puede aparecer en varios pedidos. Para expresarla se necesita una tabla intermedia, DetallePedido, con una fila por combinación de pedido y producto.

En un diagrama, coloca 1 junto a Clientes y N junto a Pedidos. Para la relación N:N, une Pedidos con DetallePedido y DetallePedido con Productos.

Preparar las hojas que representan el modelo

Crea un libro llamado Modelo y añade tres hojas: Clientes, Pedidos y Productos. También crearás DetallePedido cuando construyas la relación de varios a varios.

1. Haz clic con el botón derecho en la pestaña de una hoja y elige Insertar.
2. Escribe Clientes y acepta.
3. Repite para crear Pedidos y Productos.
4. En Clientes, escribe estos encabezados: IDCliente, NombreCliente, Ciudad y Correo.
5. En Pedidos, escribe: IDPedido, IDCliente, Fecha e Importe.
6. En Productos, escribe: IDProducto, NombreProducto, Precio y Stock.

Definir una clave para no repetir datos

En Clientes, usa IDCliente como clave primaria. Asigna 1001, 1002 y 1003 a Ana Torres, Luis Mora y Sara Gil. En Pedidos, IDCliente guarda el valor que conecta cada pedido con su cliente.

Cada valor de IDCliente debe identificar una sola fila. Por eso no se usa el correo como clave: el correo identifica a la persona, pero la clave debe estar dentro de la tabla y ser fácil de validar.

Evitar duplicados antes de relacionar las hojas

Revisa que ninguna clave esté vacía o repetida. Para el ejercicio, agrega temporalmente dos filas con errores: una con IDCliente vacío y otra con el mismo identificador.

1. En Clientes, selecciona A2:A5.
2. Revisa visualmente que 1001, 1002 y 1003 no estén repetidos.
3. Corrige la fila vacía con 1004.
4. Cambia el identificador duplicado de prueba por 1005.
5. Elimina la fila que no forma parte del libro.

Construir el diagrama con formas y conectores

Crea una hoja Diagrama y colócala junto a las tablas. Allí representarás cada entidad con un rectángulo y conectarás las claves con líneas.

1. Inserta la hoja Diagrama.
2. En la cinta, abre:

1. Dibuja un rectángulo para Clientes.
2. Duplica el rectángulo para crear Pedidos y Productos.
3. Escribe dentro de cada forma su nombre.
4. Añade dentro de cada forma sus columnas, separadas por saltos de línea.

Las formas quedan superpuestas a la cuadrícula; la tabla muestra aquí su contenido y posición relativa.

Representar una relación uno a varios

Desde Insertar > Formas, elige una línea o conector y únelo con Clientes y Pedidos. Añade el texto IDCliente sobre la línea, cerca de Pedidos.

1. Selecciona el conector.
2. Arrastra un extremo al borde inferior de Clientes.
3. Arrastra el otro extremo al borde izquierdo de Pedidos.
4. Escribe IDCliente junto al conector.
5. Escribe 1 junto a Clientes y N junto a Pedidos.

Representar una relación muchos a muchos

Para representar correctamente la relación N:N, añade una hoja DetallePedido. Sus columnas serán IDPedido, IDProducto, Cantidad y PrecioUnitario.

Una fila de detalle corresponde a un producto dentro de un pedido. La combinación de IDPedido e IDProducto identifica esa combinación. Si una misma pareja necesita más propiedades, como una fecha o un descuento, la combinación de ambas columnas puede actuar como clave compuesta.

Usar una tabla intermedia en lugar de duplicar datos

La tabla DetallePedido evita guardar de nuevo Ana Torres en cada pedido o el nombre Cuaderno junto a cada línea. Guarda referencias a los identificadores y atributos propios del vínculo.

Si cada línea guarda PrecioUnitario, el precio queda congelado en ese detalle. Si el catálogo cambia después, el detalle conserva el valor cobrado en aquel momento.

Marcar la tabla intermedia como entidad propia

En el diagrama, añade un rectángulo DetallePedido con sus cuatro atributos. Créalo como una entidad propia, porque contiene información que no pertenece por completo a Pedidos ni a Productos.

• Clave: combinación de IDPedido e IDProducto.
• Atributos propios: Cantidad y PrecioUnitario.
• Relación con Pedidos: N:1.
• Relación con Productos: N:1.

Validar que el diagrama coincide con los datos

Un diagrama puede quedar bien dibujado y aun así no coincidir con las hojas. Comprueba que cada clave foránea exista en la tabla a la que apunta.

En Pedidos, usa CONTAR.SI (COUNTIF) para localizar IDCliente que no aparece en Clientes. En una celda vacía, escribe:

=CONTAR.SI(Clientes!$A$2:$A$5;B2)

Devuelve 1 cuando B2 existe en Clientes y 0 cuando no. Copia la fórmula hasta la última fila de Pedidos.

Repite el control para IDProducto en DetallePedido:

=CONTAR.SI(Productos!$A$2:$A$4;B2)

También puedes comprobar la otra dirección. En una celda de Clientes, cuenta cuántos pedidos tienen cada cliente:

=CONTAR.SI(Pedidos!$B$2:$B$4;A2)

Los resultados esperados son 2 para el cliente 1001, 1 para 1002 y 0 para 1003.

1. Revisa que ningún control de existencia devuelva 0.
2. Si aparece 0, busca el valor de clave en la tabla de destino.
3. Corrige el identificador o agrega el registro que falta.
4. Comprueba también que no haya claves duplicadas.
5. Elimina las columnas de control cuando terminen, o consérvalas para futuras revisiones.

IDCliente devuelve 0 y no encuentra al cliente

Si CONTAR.SI devuelve 0, la clave de Pedidos no aparece dentro del rango elegido de Clientes. Puede haber un error al escribir el identificador, una celda vacía o un rango que termina antes del cliente.

Corrige el valor de IDCliente o amplía el rango. En este libro, el rango correcto es Clientes!$A$2:$A$5.

El diagrama muestra una relación que las hojas no tienen

Si dibujas N:N entre Pedidos y Productos pero no creaste DetallePedido, el diagrama describe una intención, no la estructura real. Añade la tabla intermedia con IDPedido, IDProducto, Cantidad y PrecioUnitario, y vuelve a validar las referencias.

Una relación 1:N parece N:N

Si cada pedido puede tener varios detalles, la relación entre Pedidos y DetallePedido es 1:N. El diagrama queda mal orientado cuando 1 y N aparecen invertidos. Coloca 1 junto a la entidad que puede repetirse: un pedido único puede tener varias líneas de detalle.

Los nombres coinciden, pero la relación no funciona

Dos clientes pueden llamarse igual. Si conectas las hojas por NombreCliente, el nombre no garantiza una correspondencia única. La relación debe usar IDCliente, igual que IDPedido e IDProducto conectan sus tablas.

Qué restricción tiene Excel como modelador de datos

Excel organiza y relaciona información, pero no sustituye una base de datos relacional. Su libro puede contener tablas, fórmulas, filtros y consultas, aunque no aplica de forma nativa las restricciones de clave foránea que|goals un motor como Access.

En el modelo de la tienda:

• Cada hoja es una tabla sencilla.
• Las fórmulas de Excel calculan o validan referencias.
• Las formas del diagrama son documentación visual.
• Una clave repetida en un modelo real puede duplicar información.
• Un dato referenciado puede quedar desactualizado si se corrige a mano.

Access ofrece tablas, formularios e informes para gestionar muchos registros y varios usuarios. Si el proyecto necesita integridad de referencias, permisos y un sistema central de gestión, conviene trasladar el modelo a Access. La ayuda oficial explica cuándo utilizar Excel o Access: usar Access o Excel para administrar los datos.

Para consultas y transformaciones de tablas completas, Power Query puede limpiar y combinar datos; está integrado desde Excel 2016 y, en Excel 2010 y 2013, se utilizaba como complemento. No sustituye el modelo relacional ni resuelve por sí solo las claves foráneas. Consulta la documentación oficial de Power Query.

Lo que ya sabes hacer con un modelo de datos

• Identificar entidades como Clientes, Pedidos, Productos y DetallePedido.
• Convertir cada entidad en una hoja con sus atributos.
• Definir claves primarias como IDCliente, IDPedido e IDProducto.
• Representar relaciones 1:N y N:N con formas y conectores.
• Crear una tabla intermedia para evitar duplicar datos.
• Validar referencias con CONTAR.SI (COUNTIF).
• Corregir claves vacías, repetidas o inexistentes.
• Decidir cuándo Excel basta y cuándo Access resulta más adecuado.

El siguiente paso es abrir tu libro Modelo, comprobar cada relación contra sus hojas y guardar la versión final del diagrama junto a los datos.