Modelado de datos en Excel: guía práctica con un ejemplo real

Pocas cosas desesperan más que una hoja de ventas con el nombre del cliente escrito a mano en cada fila, la ciudad repetida hasta el infinito y el precio del producto copiado una vez por pedido. Modelar datos es, en una frase, decidir qué tablas creas, qué campos guardas en cada una y cómo las unes para que la misma información viva una sola vez. Al terminar este tutorial tendrás un libro con tres hojas (Clientes, Productos y Pedidos) relacionadas entre sí, una tabla dinámica que cruza las tres sin escribir un solo Y, ni los procurarás, y sabrás cuándo te basta con BUSCARV (en inglés VLOOKUP) y cuándo necesitas el Modelo de datos de Power Pivot. Funciona en Excel 2021, 2024 y Microsoft 365; en Excel 2019 y 2016 también, aunque Power Pivot se activa como complemento.

Qué es el modelado de datos y para qué te sirve en Excel

Imagina una hoja de pedidos donde en cada línea escribes, a pulso, el nombre del cliente, su ciudad, el producto, su categoría y el importe. Cuando un cliente cambia de ciudad, tienes que corregirlo en decenas de filas; si un producto cambia de precio, el pasado deja de cuadrar; y un día necesitas «total por categoría» y no sabes por dónde empezar. Eso no es un problema de fórmulas: es un problema de modelo.

Modelar datos es decidir, antes de escribir nada, qué información merece su propia tabla y qué información es solo un dato que se busca en otra tabla. La regla de oro es no repetir: si el nombre del cliente vive en una sola fila, no hay forma de que quede mal escrito en otra. A partir de ahí, el resto (los totales, los filtros, las tablas dinámicas) sale casi solo.

En Excel, este trabajo se hace sobre todo con tres herramientas: las Tablas de Excel, que estructuran cada lista; Power Query, que limpia y combina datos sin fórmulas; y el Modelo de datos de Power Pivot, que entiende las relaciones entre tablas. No necesitas usarlas todas a la vez: con unas tablas bien hechas y BUSCARV ya modelas.

Los tres niveles de un modelo: conceptual, lógico y físico

Antes de tocar Excel se piensa. Cualquier modelo de datos se describe en tres niveles que vienen del mundo de las bases de datos y que, aunque suene a teoría, te ahorran errores en la hoja.

Conceptual: qué entidades existen y cómo se relacionan

El nivel conceptual es un mapa mental: cajitas con nombres y entidades aisladas. Para la tienda del ejemplo, las cajitas son tres: Cliente, Producto y Pedido. Un cliente hace muchos pedidos y un pedido pertenece a un cliente; un producto aparece en muchos pedidos y un pedido contiene un producto. No hay columnas ni tipos todavía, solo la forma del negocio.

Lógico: tablas, campos, claves primarias y claves externas

El nivel lógico convierte ese mapa en tablas con columnas y tipos. Aquí aparecen las claves: la clave primaria, que identifica cada fila de una tabla (IdCliente, IdProducto), y la clave externa, que en otra tabla apunta a esa clave primaria (IdCliente dentro de Pedidos). Es el plano antes de construir.

Físico: cómo se traduce a celdas, rangos y tablas de Excel

El nivel físico es lo que ya se ve en pantalla: cada tabla lógica vive en una hoja, con sus columnas en la primera fila como encabezados y sus datos debajo. Las claves externas (IdCliente, IdProducto dentro de Pedidos) son números que, llegado el momento, se buscan en su tabla con esa fórmula de toda la vida, BUSCARV, o se conectan en Power Pivot arrastrando un campo sobre otro. La diferencia entre un modelo bien montado y uno mal montado se nota justo aquí.

Las herramientas de Excel que hacen el modelo por ti

Excel trae de serie casi todo lo que necesitas para modelar sin escribir código. El truco es saber qué hace cada cosa y no mezclarlas.

Tablas de Excel: el contenedor que da estructura a cada tabla

Una Tabla de Excel no es solo «un rango con color»: es un objeto con nombre, con encabezados fijos y que crece solo cuando le añades filas debajo. Convertir un rango en Tabla es el primer paso del modelo.

1. Selecciona el rango con los datos, incluidos los encabezados.
2. Ve a la cinta Insertar > Tablas > Tabla.
3. Marca La tabla tiene encabezados y pulsa Aceptar.
4. En la pestaña Diseño de tabla, arriba a la izquierda, cambia Nombre de la tabla por uno descriptivo (tblClientes, por ejemplo).

Power Query: limpiar y relacionar tablas sin fórmulas

Power Query (Datos > Obtener y transformar datos) importa cada tabla desde su origen, le cambia el tipo, le quita duplicados, le renombra columnas y deja cada paso guardado. Si el mes que viene entra un archivo nuevo, un clic en Actualizar repite todo.

Modelo de datos en Power Pivot: las relaciones entre tablas

Power Pivot añade al libro un Modelo de datos: las tablas se ven como cajitas en una Vista de diagrama y se relacionan arrastrando el campo clave de una sobre el campo clave de la otra. Una vez relacionadas, las tablas dinámicas cruzan las tablas sin BUSCARV.

Monta un modelo de ventas en tres hojas conectadas

Vamos a construir el libro de la idea. Empezarás con tres hojas separadas, las convertirás en Tablas y las unirás en Power Pivot.

Crear las tres tablas con sus claves y sus tipos

En un libro nuevo, crea tres hojas. Escribe los encabezados en la fila 1 y los datos a partir de la fila 2.

Convertir cada hoja en Tabla y nombrarlas

1. En la hoja Clientes, selecciona A1:C5 y pulsa Ctrl+T.
2. En el cuadro Crear tabla, confirma La tabla tiene encabezados y acepta.
3. En la pestaña Diseño de tabla, en Propiedades a la izquierda, escribe Nombre de la tabla: tblClientes.
4. Repite en Productos (rango A1:C5, nombre tblProductos) y en Pedidos (A1:E6, nombre tblPedidos).

Llevar las tablas al Modelo de datos y relacionarlas

1. Con el cursor en tblClientes, abre la pestaña Power Pivot (si no la ves, actívala primero) y pulsa Agregar al modelo de datos.
2. Repite con tblProductos y tblPedidos.
3. En Power Pivot, ve a Inicio > Vista > Vista de diagrama.
4. Arrastra el campo IdCliente de la tabla Pedidos sobre el campo IdCliente de Clientes. Aparece una línea con un 1 en el lado de Clientes y un * en el lado de Pedidos.
5. Arrastra IdProducto de Pedidos sobre IdProducto de Productos.

Comprobar el modelo con una tabla dinámica y BUSCARV

Modelo montado. Ahora se demuestra que responde preguntas de negocio sin escribir relaciones a mano.

Ventas por categoría desde el Modelo de datos

1. Coloca el cursor en cualquier celda vacía.
2. Insertar > Tablas > Tabla dinámica.
3. Marca Usar el modelo de datos de este libro y acepta.
4. En la lista de campos, arrastra Categoría (de Productos) a Filas.
5. Arrastra Importe (de Pedidos) a Valores.

El nombre del cliente en cada pedido con BUSCARV

En la hoja Pedidos, añade una columna F con encabezado NombreCliente y, en F2, esta fórmula:

=BUSCARV(D2;tblClientes;2;FALSO)

Arrastra el controlador de relleno hasta F6. La fórmula busca el IdCliente de cada pedido en la primera columna de tblClientes y devuelve la segunda (Nombre). El FALSO al final exige coincidencia exacta.

Sustituir BUSCARV por una relación del Modelo de datos

La misma pregunta, esta vez sin fórmula. En la tabla dinámica del paso anterior, añade Nombre (de Clientes) a Filas junto a Categoría. Excel cruza las dos tablas por la relación que ya creaste: nada de BUSCARV.

Errores típicos al modelar datos en Excel y cómo arreglarlos

El modelo se rompe en los mismos sitios una y otra vez. Mejor verlos antes de que aparezcan.

Filas duplicadas en la tabla de Clientes

Si dos filas de Clientes comparten el mismo IdCliente, la relación con Pedidos devuelve totales multiplicados. La causa es no haber definido bien la clave primaria. La solución está en la misma hoja: Datos > Herramientas de datos > Quitar duplicados, dejando marcada la columna IdCliente.

La relación no muestra datos o muestra el mismo valor en todas las filas

La causa más común es que los campos clave no son del mismo tipo (texto contra número) o que la arrastraste al revés. Abre Power Pivot > Inicio > Vista de diagrama, borra la línea (clic derecho > Eliminar relación) y vuelve a arrastrar de la tabla de muchos (Pedidos) a la de uno (Clientes o Productos).

#N/A al buscar el cliente con BUSCARV

=BUSCARV(D2;tblClientes;2;FALSO)

La fórmula devuelve #N/A. La causa casi siempre es que la columna de búsqueda (IdCliente) no es la primera de tblClientes, o que el IdCliente no existe en Clientes. Mueve IdCliente a la primera columna de tblClientes o usa INDICE (INDEX) y COINCIDIR (MATCH) como alternativa, que no exige esa primera columna.

=INDICE(tblClientes[Nombre];COINCIDIR(D2;tblClientes[IdCliente];0))

Power Pivot no aparece en la cinta

El complemento no está cargado. Ve a Archivo > Opciones > Complementos > Administrar: Complementos COM > Ir, marca Microsoft Power Pivot for Excel y reinicia Excel.

Lo que Excel no hace como modelador de datos

Excel modela muy bien hasta cierto tamaño. A partir de ahí, se queda corto.

Modelo conceptual y lógica de negocio compleja

Si necesitas documentar el modelo con diagramas Entidad-Relación, jerarquías estrictas o reglas de negocio detalladas, Excel no es el sitio. Para eso existen herramientas de modelado dedicadas como Erwin Data Modeler, Lucidchart o Microsoft Visio, donde el diagrama es el modelo y se puede revisar antes de generar la base de datos.

Volumen grande, muchos usuarios y gobierno del dato

Cuando el inventario pasa del millón de filas, varios equipos escriben a la vez y hace falta auditoría de quién cambió qué, Excel deja de ser el lugar. El salto natural es una base de datos relacional (Access, SQL Server, PostgreSQL, MySQL) con su capa de modelado propia, o un almacén en la nube tipo Snowflake, BigQuery o Redshift, donde el modelo se define en el esquema y se gobierna desde fuera.

Power Query y Power Pivot en Excel para la web y en Mac

Excel para la web trae Power Query con menos conectores que el de escritorio. Power Pivot solo está en Windows, en Excel 2021, 2024 y Microsoft 365, y necesita el complemento activado. En Mac, Power Query funciona desde Excel 2016 con un conjunto de conectores más pequeño, y Power Pivot no está disponible.

Lo que ya sabes hacer con el modelado de datos en Excel

• Diferenciar los tres niveles de un modelo (conceptual, lógico, físico) y aplicarlos a cualquier libro antes de tocar el teclado.
• Convertir listas en Tablas de Excel con Ctrl+T y nombrarlas con un prefijo claro (tbl).
• Crear relaciones entre tablas en Power Pivot arrastrando los campos clave en Vista de diagrama.
• Responder preguntas de negocio con una tabla dinámica basada en el Modelo de datos, sin escribir BUSCARV.
• Reconocer los errores típicos (duplicados, tipos cruzados, columnas desordenadas) y arreglarlos en la misma hoja.

El siguiente paso lo decides tú: abre una de tus hojas reales, convierte cada lista en Tabla y mira si las relaciones que te hacen falta las cubre BUSCARV o ya piden Power Pivot. Si quieres profundizar en la limpieza previa de los datos, el tutorial de Power Query es el siguiente capítulo natural.

Dudas al modelar datos en Excel