Modelo de datos en Excel: qué es y cómo se usa con Power Pivot

Imagina una hoja de Ventas con miles de filas, otra de Productos con su precio y categoría, y otra de Clientes con la ciudad. El modelo de datos de Excel te permite relacionar esas tablas para cruzarlas en una tabla dinámica y analizarlas juntas, sin escribir un solo BUSCARV. Cuando termines este tutorial tendrás un libro con tres tablas conectadas, una tabla dinámica que cruza ventas por producto y ciudad, y una medida DAX que calcula el total. Funciona en Excel para Microsoft 365, Excel 2021 y Excel 2024; en Excel 2019 y 2016 hay que activar Power Pivot como complemento, y en Excel para la web y para Mac no se pueden crear medidas DAX.

Qué es un modelo de datos y cuándo lo necesitas en Excel

Un modelo de datos es la forma que tiene Excel de guardar varias tablas relacionadas dentro de un mismo libro, de manera que puedas analizarlas con una tabla dinámica como si fueran una sola. Piensa en tu tienda: la hoja Ventas guarda cada ticket con un IdProducto y un IdCliente; la hoja Productos tiene el nombre y la categoría de cada producto; la hoja Clientes tiene la ciudad de cada cliente. Con un modelo de datos, la tabla dinámica une las tres y te enseña las ventas por categoría y por ciudad sin escribir BUSCARV una y otra vez. Lo necesitas cuando los datos ya no caben en una sola hoja lógica, cuando se repiten y se desactualizan, o cuando quieres resumir por varios criterios a la vez. Para unas decenas de filas no hace falta; para miles, sí.

Las piezas del modelo de datos en Excel

El modelo se compone de cuatro piezas que trabajan juntas:

• Tablas de Excel: cada rango de datos convertido en tabla con Ctrl+T, con sus encabezados y su nombre en la pestaña Diseño de tabla.
• Relaciones entre tablas: los enlaces que dicen qué columna de una tabla apunta a qué columna de otra (Ventas.IdProducto con Productos.Id).
• Tablas dinámicas que leen el modelo: con la opción «Usar el modelo de datos de este libro» al crearlas.
• Power Pivot: el motor que guarda el modelo y permite crear medidas DAX; viene integrado en Microsoft 365, 2021 y 2024.

Crear el modelo de datos paso a paso

Partimos de un libro con tres hojas: Ventas, Productos y Clientes. Lo primero es convertir cada rango en tabla de Excel y, al crear la primera tabla dinámica, marcar la casilla que las añade al modelo.

Convertir los datos en tablas de Excel

Abre el libro y comprueba que tienes las tres hojas con estos datos:

Ahora convierte cada rango en tabla:

1. Sitúate en Ventas!A1, selecciona todo el rango con Ctrl+Mayús+Fin y pulsa Ctrl+T. Acepta que la tabla tiene encabezados.
2. En la pestaña Diseño de tabla, cambia el nombre a Ventas en el cuadro Nombre de la tabla.
3. Repite lo mismo en Productos (nombre Productos) y en Clientes (nombre Clientes).

Añadir las tablas al modelo de datos

Al insertar la primera tabla dinámica, Excel te pregunta si quieres añadir los datos al modelo. Es la casilla clave.

1. Selecciona una celda de la tabla Ventas.
2. Ve a la cinta:

1. En el cuadro que se abre, marca la casilla Agregar estos datos al modelo de datos y pulsa Aceptar.

Crear relaciones entre las tablas

Las relaciones son los enlaces entre las columnas que comparten las tablas: aquí, Ventas.IdProducto con Productos.Id y Ventas.IdCliente con Clientes.Id.

1. Haz clic en la tabla dinámica para que aparezca la pestaña Analizar tabla dinámica.
2. Abre el cuadro de relaciones:

1. Pulsa Nueva y rellena el primer enlace:

1. Pulsa Nueva otra vez y crea la segunda: Clientes.Id ↔ Ventas.IdCliente.
2. Cierra el cuadro Administrar relaciones; debería listar las dos relaciones activas.

Por qué la relación uno a varios es la más habitual

La relación más usada en un modelo de datos es la de uno a varios: cada fila de la tabla de dimensión aparece una sola vez, y la tabla de hechos la apunta muchas veces. Aquí la tienes:

Productos y Clientes son las dimensiones (lo que filtra y agrupa); Ventas es la tabla de hechos (lo que se suma).

Qué pasa si las columnas relacionadas no se llaman igual

Si en Productos la llave se llamara Codigo en lugar de Id, Excel no te dejaría crear la relación directamente. Tienes dos caminos: renombrar la columna en la tabla de dimensión para que coincida con la columna de la tabla de hechos, o bien elegirla manualmente al crear el enlace. Lo más limpio es lo primero:

1. En la hoja Productos, haz clic derecho sobre el encabezado Codigo y elige Cambiar nombre de columna.
2. Escribe Id y pulsa Entrar.
3. Vuelve a Administrar relaciones y crea el enlace.

Analizar el modelo con una tabla dinámica

Ahora la tabla dinámica puede cruzar las tres tablas. Vamos a construir el informe clásico: ventas por categoría de producto, con ciudades como columnas.

Campos de varias tablas en una sola tabla dinámica

1. En la lista de campos, marca Ventas.Importe (valores), Productos.Categoria (filas) y Clientes.Ciudad (columnas).

Crear jerarquías para analizar por niveles

Una jerarquía agrupa campos en un orden (Año → Trimestre → Mes) y permite desplegar el detalle con un clic. Para crearla necesitas una tabla Fechas con esas columnas.

1. Añade una hoja Fechas con una columna Fecha (por ejemplo del 01/01/2026 al 30/06/2026, una fila por día) y, al lado, las columnas Año, Trimestre y Mes calculadas.
2. Conviértela en tabla con Ctrl+T y llámala Fechas.
3. En la lista de campos de la tabla dinámica, clic derecho sobre Año y elige Crear jerarquía. Llámala Calendario.
4. Clic derecho sobre Trimestre → Agregar a jerarquía → Calendario. Repite con Mes.
5. Arrastra Calendario al área Filas y despliega sus niveles con el signo + del primer campo.

Medidas con DAX: los cálculos del modelo

Una medida DAX es un cálculo que vive dentro del modelo y se recalcula según el contexto de la tabla dinámica (filtros, filas, columnas). Se crean desde la propia tabla dinámica.

1. Selecciona la tabla dinámica y ve a:

1. Escribe:

=Ventas[Importe]

1. Así se obtiene Ventas totales = SUMA(Ventas[Importe]), lista para arrastrar al área Valores.

Una medida rápida con Autosuma

Si solo necesitas una suma básica, no hace falta abrir el cuadro de Nueva medida.

1. Haz clic en una celda vacía junto a la tabla dinámica.
2. Pulsa Alt+= y acepta la fórmula:

=SUMA(Ventas[Importe])

1. Excel la guarda como medida del modelo.

Cuando la medida da error o muestra lo mismo

Si tu medida siempre devuelve el mismo número sea cual sea la fila, probablemente estás midiendo una columna de texto o un identificador. Ejemplo: =SUMA(Ventas[IdCliente]) no tiene sentido porque es un identificador. Para arreglarlo:

1. Quita la medida mal creada (clic derecho en la lista de campos → Eliminar).
2. Cambia el agregado del campo en Configuración de campo de valor a Suma.

Qué necesitas para usar el modelo de datos

El modelo de datos básico (tablas + tabla dinámica + relaciones) está disponible en Excel para Microsoft 365, 2021, 2024, 2019 y 2016. El editor de medidas DAX (Power Pivot) viene integrado en Microsoft 365, 2021 y 2024; en Excel 2019 y 2016 hay que activarlo desde Archivo > Opciones > Complementos > Complementos COM > Power Pivot. En Excel para la web puedes consumir un modelo ya creado y arrastrar campos, pero no crear medidas DAX ni modificar relaciones. En Excel para Mac pasa algo parecido: el modelo se puede usar desde una tabla dinámica, pero el editor de Power Pivot no está disponible.

Errores típicos al trabajar con el modelo de datos

«No se puede crear la relación porque faltan valores»

Suele aparecer cuando algún IdProducto de Ventas no existe en Productos. Lo ves así:

Causa: el 999 no existe en Productos. Arreglo: filtra la columna IdProducto por los valores que no están en Productos y corrige el dato suelto (aquí, cambiarlo por 102).

El campo aparece dos veces en la lista

A veces el mismo campo está como columna de la tabla y, además, dentro del modelo. La lista de campos lo muestra dos veces y te confunde. Causa: copiaste los datos con formato y arrastraste también una copia fuera de la tabla. Arreglo: borra la columna original suelta para que solo quede la del modelo.

La tabla dinámica no se actualiza al cambiar los datos

Las tablas dinámicas no se actualizan solas al modificar los datos. Causa: cambiaste un importe en Ventas pero la tabla dinámica sigue con el valor anterior. Arreglo:

1. Clic derecho sobre la tabla dinámica → Actualizar, o pulsa Alt+F5.
2. Si cada día entran ventas nuevas, convierte el origen en tabla de Excel para que las filas nuevas entren automáticamente al actualizar.

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

• Tienes tres tablas (Ventas, Productos, Clientes) convertidas en tablas de Excel con Ctrl+T y añadidas al modelo.
• Has creado dos relaciones (Productos y Clientes hacia Ventas) desde Datos > Herramientas de datos > Relaciones.
• Has analizado el modelo con una tabla dinámica que cruza productos y ciudades sin escribir BUSCARV.
• Has creado una jerarquía Calendario y una medida DAX Ventas totales con SUMA.
• Sabes actualizar con Alt+F5 y ampliar el origen cuando entren filas nuevas.

El siguiente paso natural es usar Power Query para traer los datos limpios al modelo: desde Datos > Obtener y transformar datos puedes importar las tablas desde archivos CSV o desde una base de datos y aplicar pasos de limpieza que se guardan y se vuelven a aplicar con un solo clic.

Dudas al usar el modelo de datos en Excel

¿Qué diferencia hay entre un modelo de datos y BUSCARV en Excel?

BUSCARV une dos tablas celda a celda con una fórmula; el modelo de datos une las tablas a nivel de motor y deja que la tabla dinámica haga los cruces. Para pocas filas sirve BUSCARV; para miles y para resumir por varios criterios, el modelo es más rápido y no rompe las fórmulas al cambiar los datos.

¿Cuántas tablas puedo tener en un modelo de datos de Excel?

El modelo puede contener decenas de tablas relacionadas. Lo que importa es que cada una tenga una columna de identificación única y que las relaciones apunten a columnas con el mismo tipo de dato.

¿Puedo crear medidas DAX en Excel para la web o para Mac?

No. El editor de Power Pivot, necesario para crear medidas DAX, solo está disponible en Excel para Windows en Microsoft 365, 2024 y 2021. En Excel para la web y para Mac puedes usar un modelo creado en otro equipo, pero no crear medidas nuevas.

¿Qué pasa si cambio un dato en una tabla del modelo?

Tienes que actualizar la tabla dinámica con Alt+F5 o con el botón Actualizar. Si el origen es una tabla de Excel, las filas nuevas entran al actualizar; las filas borradas también.