Diagrama físico de una base de datos con Excel

Un diagrama físico de base de datos describe cómo se almacenarán realmente los datos: qué tablas existen, qué columnas tiene cada una, qué tipo tipo, qué clave identifica cada registro y cómo se relacionan. En este tutorial usarás Excel como cuaderno de diseño para preparar un modelo de ventas y después conectarlo con Microsoft Access.

El método funciona en las versiones de Excel de escritorio y en la web. La conexión con Access requiere Windows, Power Query y una base de datos de Access; los datos están comprobados en octubre de 2026.

Qué es un diagrama físico y cómo se relaciona con Excel

El diagrama físico es el último nivel del diseño de una base de datos. El nivel conceptual identifica entidades y relaciones; el lógico organiza atributos, tipos y claves; el físico concreta cómo se guardarán los registros, campos e índices. En el tutorial de diseño de bases de datos de Lucidchart se explica que el esquema físico define el almacenamiento mediante archivos e índices.

En tu libro de trabajo, la hoja Modelo será el diagrama. Cada tabla será un rango de Excel, cada columna tendrá su tipo anotado y cada relación se comprobará con BUSCARV. Todavía no habrá consultas ni macros: solo una estructura clara para llevarla a Access.

Del modelo lógico al modelo físico de las ventas

El modelo lógico decide qué información pertenece a cada tabla. En este libro hay tres entidades: Cliente, Pedidos y Productos. La primera guarda quién compra; la segunda registra cada compra; la tercera describe el artículo vendido. Separar estos datos evita repetir el nombre del cliente en cada línea de pedido.

Identificar tablas, columnas y claves en Excel

Primero registramos los clientes. ID será la clave primaria: no puede repetirse, quedar vacía ni cambiar cuando se actualiza el resto de datos. Nombre y Ciudad son atributos de texto.

Pedidos beginningommará cada registro con IDPedido, como clave primaria. IDCliente e IDProducto serán claves foráneas: guardan el identificador de un registro de otra tabla.

Definir tipos de datos y restricciones del modelo

El tipo se decide según el valor que guardará la columna. ID es un número entero; Nombre, Ciudad y Producto son texto de longitud acotada; FechaPedido es fecha; Cantidad es entero; Importe y Precio son decimal.

Una relación uno a muchos se implementa con una clave foránea en la tabla hija. Una relación de muchos a muchos necesita una tabla intermedia: por eso el pedido lleva un solo IDCliente y un solo IDProducto.

Crear las tablas físicas en una hoja de Excel

Ahora convertimos los rangos en tablas de Excel. Esto no crea una base de datos, pero mantiene los datos vinculados, añade referencias Structured y permite ampliar el rango con filas nuevas. Prepararemos además una columna calculada para comprobar las relaciones.

Crear la tabla Cliente con su clave primaria

1. Escribe los tres clientes del ejemplo en A1:C4.
2. Selecciona A1:C4 con el controlador de relleno.
3. Abre la ruta siguiente y confirma que Excel ha detectado los encabezados.
4. Comprueba que la tabla se llama Cliente.

Crear Pedidos y Productos con claves foráneas

Añade ahora dos rangos. Productos usará A7:B9; Pedidos, D7:H9. En Pedidos, cada IDCliente debe existir en Cliente y cada IDProducto en Productos.

1. Escribe los encabezados y los datos de ambas tablas.
2. Convierte cada rango con Insertar > Tablas > Tabla.
3. Cambia los nombres predeterminados por Productos y Pedidos.

Comprobar una relación con BUSCARV

En J8 escribimos una fórmula que busca el cliente de la primera fila de Pedidos. El último argumento FALSO exige coincidencia exacta.

=BUSCARV(E8;A2:C4;2;FALSO)

Arrastra J8 hasta J10. Si el resultado es #N/A, el IDCliente no coincide exactamente con un ID de Cliente.

Resumir el volumen físico con SUBTOTALES

SUBTOTALES permite conocer el volumen visible de los importes. El código 109 suma e ignora tanto las filas filtradas como las ocultadas manualmente.

1. Escribe en J12 el rótulo Total visible.
2. Escribe en K12 la fórmula siguiente.
3. Filtra la columna H y observa el resultado.

=SUBTOTALES(109;H8:H10)

Llevar el diagrama desde Excel hacia Access

La hoja Modelo ha servido para diseñar y validar la estructura. Ahora Access será el lugar donde se materializan tablas, restricciones e índices. Excel puede recibir una tabla de Access o consultar una base de datos; Access, a su vez, exporta sus datos a un libro de Excel.

Usar Access o Excel para administrar tus datos explica que Excel sirve para calcular y analizar, mientras Access almacena muchos registros relacionados y ofrece formularios e informes para varios usuarios.

Exportar una tabla de Access a Excel

1. Abre la base de datos de Access y la tabla Pedidos.
2. En Access, elige Datos externos > Exportar > Excel.
3. Guarda el archivo con extensión .xlsx.
4. Ábrelo en Excel y comprueba las columnas.

Consultar Access desde Excel

1. Sitúa el cursor en una celda vacía de una hoja nueva.
2. Abre Datos > Obtener datos > Desde base de datos > Desde Microsoft Access.
3. Selecciona el archivo .accdb y la tabla Pedidos.
4. Acepta la importación y revisa la vista previa.

Consultar el modelo con Power Query

Power Query conecta la fuente de datos y graba los pasos de transformación. Así evitamos repetir ajustes manuales cada vez que Access cambia. La documentación oficial de Power Query explica que las consultas se actualizan con un clic.

Limpiar columnas antes de analizar los datos

1. Ve a Datos > Obtener y transformar datos > Obtener datos > Desde base de datos > Desde Microsoft Access.
2. Selecciona Pedidos y trabaja en la vista previa.
3. Conserva IDPedido, IDCliente, IDProducto, Cantidad e Importe.
4. Elimina una columna repetida y comprueba el tipo de Importe.

La transformación queda guardada dentro de la consulta, no solo en la hoja visible. Así, al actualizar, Excel vuelve a aplicar los mismos pasos.

Actualizar la consulta después de cambiar Access

1. En Access, cambia el nombre del cliente 102 por Luis Mora Ruiz.
2. Vuelve a Excel y abre la pestaña Datos.
3. Pulsa Actualizar todo.
4. Comprueba que la consulta trae el nombre modificado.

Problemas frequentos al conectar Excel con la base de datos

Cuando una conexión falla, conviene separar el síntoma de la causa. Un #N/A suele indicar una clave; un dato antiguo, una actualización pendiente; y una columna convertida en texto, un problema de tipos.

Una clave foránea no encuentra su registro

Si BUSCARV devuelve #N/A, la causa es que el valor buscado no coincide exactamente con la clave primaria. Un espacio final, un ID escrito como texto o una celda vacía provocan el fallo.

Corrige el IDCliente de la fila afectada y confirma la fórmula:

=BUSCARV(E9;A2:C4;2;FALSO)

La consulta no refleja los datos más recientes

Cuando Access ya tiene un cambio pero Excel conserva el valor anterior, la conexión no se ha vuelto a ejecutar. Actualiza la hoja o toda la consulta desde Datos > Actualizar todo. Si sigue igual, revisa que el archivo de Access esté accesible y que la conexión apunte a la tabla correcta.

Excel desordena o cambia los tipos al importar

Si una columna de importes llega como texto o las claves pierden ceros iniciales, revisa las columnas del archivo de origen. Usa Power Query para asignar el tipo correcto antes de cargar. No es un fallo de Access: es una transformación que debe quedar guardada en la consulta.

Qué versiones de Excel y Access admiten la conexión

Power Query está integrado desde Excel 2016; en 2010 y 2013 funciona como complemento. Excel 2007 y 2003 no incluyen esa herramienta. En Excel 2021, 2024 y Microsoft 365 funciona directamente; en Mac y la web hay menos conectores disponibles.

Access solo existe para Windows. Se obtiene con los planes de Microsoft 365 para empresa o mediante una compra independiente. Por eso, un lector de Mac o de la web puede preparar el modelo en Excel, pero necesitará otra vía para mantener una base de Access.

Lo que ya sabes hacer con un diagrama físico

• Identificar las tablas Cliente, Pedidos y Productos a partir de los datos del negocio.
• Separar el modelo conceptual, el lógico y el físico sin confundir sus responsabilidades.
• Asignar tipos de datos, claves primarias y claves foráneas.
• Convertir rangos en tablas de Excel y ampliar su estructura.
• Comprobar relaciones con BUSCARV y obtener #N/A cuando una clave falla.
• Resumir el volumen visible con SUBTOTALES y código 109.
• Exportar una tabla desde Access e importarla en Excel.
• Conectar Excel con Access mediante Power Query y actualizar los datos.
• Diagnosticar problemas de claves, tipos y actualización antes de rehacer la consulta.

El siguiente paso es crear en Access las tablas con las claves y restricciones que ya definiste en la hoja Modelo.

Dudas al crear un diagrama físico de base de datos