Cuando una hoja de ventas empieza a tener 30, 50, 100 mil filas y columnas repetidas como Categoría, Subcategoría, Ciudad o Vendedor, filtrar se vuelve lento y los informes se rompen. El modelo estrella y el copo de nieve son la forma de ordenar esos datos: una tabla de hechos en el centro con las cifras y unas dimensiones limpias alrededor con las descripciones. En este tutorial montarás las dos estructuras en un libro de Excel real, pasando de una hoja plana sucia a un esquema que cualquier tabla dinámica entiende. Necesitas Excel 2016 o posterior; BUSCARX y las matrices dinámicas piden Excel 2021, 2024 o Microsoft 365.
Qué son el modelo estrella y el copo de nieve y para qué sirven
Imagina una hoja de Ventas con una fila por venta y columnas como Producto, Categoría, Subcategoría, Cliente, Ciudad, País, Tienda, Fecha, Unidades e Importe. La misma categoría "Electrónica" aparece cientos de veces, la misma ciudad otras tantas, y al filtrar por una tienda Excel tiene que revisar toda la tabla para recomponer los totales. Cuando el libro crece, los filtros tardan, las tablas dinámicas repiten sumas y cualquier cambio en la descripción de un producto obliga a tocar cientos de filas.
El modelo estrella resuelve esto dividiendo la hoja en dos tipos de tabla. En el centro va la tabla de hechos, con una fila por venta y solo números y claves (IdProducto, IdCliente, IdTienda, Fecha, Unidades, Importe). Alrededor van las dimensiones: una por cada concepto descriptivo, con una fila por producto, por cliente, por tienda y por fecha, cada una con sus atributos limpios. El esquema se llama estrella porque el hecho queda en el centro y las dimensiones en las puntas.
El copo de nieve es una variante: cuando una dimensión tiene sus propios atributos repetidos (categoría y subcategoría dentro de producto, país y ciudad dentro de tienda), se normaliza aún más y se rompe en varias tablas encadenadas. Es más limpio en teoría, pero en Excel casi siempre se aplana, como verás al final.
Las piezas del esquema: hechos, dimensiones y claves
Toda tabla de hechos guarda los números que se quieren analizar: ventas, unidades, importes, costes. Tiene muchas filas (una por cada evento: cada venta, cada pedido, cada albarán) y muy pocas columnas: la fecha, las claves que apuntan a las dimensiones y las medidas numéricas. La granularidad la decide el negocio: una fila por venta significa que cada línea del ticket es un registro.
Las dimensiones son tablas pequeñas y descriptivas. Cada dimensión tiene una clave única (IdProducto, IdCliente, IdTienda, IdFecha) y columnas con los atributos que el usuario quiere ver al filtrar o al cruzar: nombre del producto, categoría, subcategoría; nombre del cliente, ciudad, país; nombre de la tienda, responsable, región; día, mes, trimestre, año.
La tabla de hechos en el centro
| Campo | Tipo | Ejemplo |
|---|---|---|
| IdVenta | Clave | 1001 |
| Fecha | Fecha | 15/01/2026 |
| IdProducto | Clave externa | P-014 |
| IdCliente | Clave externa | C-207 |
| IdTienda | Clave externa | T-03 |
| Unidades | Medida | 2 |
| Importe | Medida | 89,00 |
La granularidad la marca una fila por venta: cada IdVenta aparece una sola vez, con sus claves y sus medidas. Las claves externas no son texto libre: son el identificador único que existe en la dimensión correspondiente.
Las dimensiones alrededor
| Dimensión | Clave única | Atributos |
|---|---|---|
| DimProducto | IdProducto | Producto, Categoría, Subcategoría, Precio lista |
| DimCliente | IdCliente | Cliente, Ciudad, País, Segmento |
| DimFecha | IdFecha | Día, Mes, Trimestre, Año, Día semana |
| DimTienda | IdTienda | Tienda, Ciudad, Responsable, Región |
Cada dimensión tiene una fila por valor único de su clave. Si un producto aparece en 5.000 ventas, en DimProducto tiene una sola fila con sus atributos completos.
Cómo se conectan hechos y dimensiones
La relación entre una dimensión y la tabla de hechos es de uno a muchos: un mismo IdProducto aparece en muchas ventas, pero cada venta apunta a un solo IdProducto. La conexión se hace llevando la clave de la dimensión a la hoja de hechos y usando esa clave para traer los atributos descriptivos con una búsqueda. La fórmula clásica es =BUSCARV(Clave;TablaDimensión;Columna;FALSO); en Excel 2021 y Microsoft 365 se usa =BUSCARX(Clave;TablaDimensión[Clave];TablaDimensión[Atributo]), que es más legible.
Monta el modelo estrella en un libro de Excel
Partimos de una hoja llamada Ventas con 30 filas planas: IdVenta, Fecha, Producto, Cliente, Tienda, Unidades, Importe. Vamos a dividirla en cinco pestañas: DimProducto, DimCliente, DimFecha, DimTienda y Hechos.
Preparar las dimensiones como tablas con nombre
Copia los productos únicos a la pestaña DimProducto con encabezados IdProducto, Producto, Categoría, Subcategoría. Repite con clientes, fechas y tiendas. Selecciona cada lista y conviértela en tabla.
Marca "La tabla tiene encabezados" y acepta. Luego, en la pestaña Diseño de tabla, cambia el nombre a DimProducto, DimCliente, DimFecha y DimTienda. Así las fórmulas podrán usar referencias estructuradas como DimProducto[Producto].
| A | B | C | D | |
|---|---|---|---|---|
| 1 | IdProducto | Producto | Categoría | Subcategoría |
| 2 | P-001 | Auriculares BT | Electrónica | Audio |
| 3 | P-002 | Ratón óptico | Electrónica | Periféricos |
| 4 | P-003 | Cuaderno A4 | Papelería | Cuadernos |
| 5 | P-004 | Bolígrafo azul | Papelería | Escritura |
| 6 | P-005 | Mochila 15" | Accesorios | Bolsos |
Cargar la clave del producto en la hoja de hechos
En la hoja Hechos, sustituye la columna de texto Producto por la clave IdProducto. Como las ventas ya están, la fórmula debe buscar el producto y devolver su IdProducto.
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Producto | Fecha | IdProducto | Cliente | IdCliente | Tienda | Importe |
| 2 | Auriculares BT | 15/01/2026 | P-001 | Cliente 12 | C-012 | Tienda Centro | 89,00 |
| 3 | Ratón óptico | 15/01/2026 | P-002 | Cliente 04 | C-004 | Tienda Norte | 24,50 |
| 4 | Cuaderno A4 | 16/01/2026 | P-003 | Cliente 19 | C-019 | Tienda Sur | 12,00 |
| 5 | Bolígrafo azul | 16/01/2026 | P-004 | Cliente 07 | C-007 | Tienda Centro | 3,20 |
| 6 | Mochila 15" | 17/01/2026 | P-005 | Cliente 22 | C-022 | Tienda Norte | 45,00 |
Si tu Excel es 2021, 2024 o Microsoft 365: BUSCARX
=BUSCARX(A2;DimProducto[Producto];DimProducto[IdProducto]";"";"No encontrado")El cuarto argumento es el valor si no encuentra coincidencia (dejamos vacío para que no rompa); el quinto fuerza la coincidencia exacta. Arrastra el controlador de relleno hasta la última fila.
Si tu Excel es 2019 o 2016: BUSCARV con columna entera
=BUSCARV(A2;DimProducto[[Producto]:[IdProducto]];3;FALSO)La referencia estructurada [Producto]:[IdProducto] toma el rango desde la columna Producto hasta IdProducto, así no hace falta contar columnas. Repite el patrón para IdCliente e IdTienda.
Conectar las dimensiones a la tabla dinámica
Ahora que Hechos tiene solo claves y medidas, crear una tabla dinámica que cruce dimensiones es directo. Inserta una tabla dinámica sobre la hoja Hechos.
En el diálogo, elige Hechos como origen y coloca la tabla dinámica en una hoja nueva llamada Análisis.
Elija dónde desea colocar el informe de tabla dinámica:
Elegir cada dimensión como campo de filas, columnas o filtros
| Campo de la TD | Dimensión que arrastras | Origen |
|---|---|---|
| Filas | Producto | DimProducto (traído por BUSCARX) |
| Columnas | Mes | DimFecha |
| Valores | Importe (Suma) | Hechos |
| Filtros | Tienda | DimTienda |
Al filtrar por una sola tienda solo cambian los importes, la estructura de filas y columnas se mantiene. Si el modelo está bien montado, ningún producto desaparece sin motivo.
Crear medidas con SUMAR.SI.CONJUNTO para totalizar
Para ver el total por producto junto a DimProducto, añade una columna de cálculo en la propia tabla DimProducto.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | IdProducto | Producto | Categoría | Subcategoría | Total ventas |
| 2 | P-001 | Auriculares BT | Electrónica | Audio | 1.245,00 |
| 3 | P-002 | Ratón óptico | Electrónica | Periféricos | 980,00 |
| 4 | P-003 | Cuaderno A4 | Papelería | Cuadernos | 420,00 |
| 5 | P-004 | Bolígrafo azul | Papelería | Escritura | 156,00 |
| 6 | P-005 | Mochila 15" | Accesorios | Bolsos | 675,00 |
Cuándo conviene el copo de nieve y cómo se monta
Cuando DimProducto empieza a tener categorías y subcategorías que se repiten mucho, la dimensión se ensucia: cambiar el nombre de una categoría obliga a editar decenas de filas. La normalización consiste en sacar esos atributos repetidos a su propia tabla.
Normalizar las dimensiones repetidas
Crea una pestaña DimCategoria con IdCategoria y Categoria, y otra DimSubcategoria con IdSubcategoria, IdCategoria y Subcategoria. Añade IdCategoria e IdSubcategoria a DimProducto y elimina las columnas de texto.
Convierte cada lista en tabla con nombre y elimina de DimProducto las columnas Categoria y Subcategoria (queda solo IdProducto, Producto, IdCategoria, IdSubcategoria).
Traer los datos con tres BUSCARV encadenados
Para reconstruir el nombre de la categoría junto a cada producto, encadena dos BUSCARV en columnas auxiliares.
=BUSCARV([@IdCategoria];DimCategoria[IdCategoria];2;FALSO)=BUSCARV([@IdSubcategoria];DimSubcategoria[IdSubcategoria];2;FALSO)La segunda fórmula puede anidarse en la primera si quieres una sola celda: =BUSCARV([@IdCategoria];DimCategoria;2;FALSO)&" / "&BUSCARV([@IdSubcategoria];DimSubcategoria;2;FALSO). Arrastra el controlador de relleno para todas las filas de DimProducto.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | IdProducto | Producto | IdCategoria | IdSubcategoria | Categoria / Subcategoria |
| 2 | P-001 | Auriculares BT | CAT-1 | SUB-101 | Electrónica / Audio |
| 3 | P-002 | Ratón óptico | CAT-1 | SUB-102 | Electrónica / Periféricos |
| 4 | P-003 | Cuaderno A4 | CAT-2 | SUB-201 | Papelería / Cuadernos |
| 5 | P-004 | Bolígrafo azul | CAT-2 | SUB-202 | Papelería / Escritura |
| 6 | P-005 | Mochila 15" | CAT-3 | SUB-301 | Accesorios / Bolsos |
Por qué en Excel casi siempre se aplana el copo de nieve
La tabla dinámica de Excel evalúa cada relación por separado y el rendimiento cae con más de tres niveles anidados. Kimball, el autor original del esquema, recomienda aplanar las dimensiones salvo que la normalización aporte una ventaja clara (ahorro de espacio, atributos que cambian con frecuencia). En Excel, mantener DimProducto con las columnas de texto originales y usar el copo de nieve solo cuando la dimensión tenga más de 50.000 filas suele ser la decisión correcta.
Las tablas dimensionales pesadas se montan en Power Query
Cuando DimProducto o DimCliente superan las 100.000 filas, mantener BUSCARV en la hoja penaliza el rendimiento. Power Query mueve las búsquedas al motor de datos y deja la hoja limpia.
Quitar columnas y filas vacías en cada consulta
Carga cada pestaña como una consulta independiente.
Elige el libro actual, selecciona las hojas DimProducto, DimCliente, DimFecha, DimTienda y Hechos, y pulsa Transformar datos. En el editor de Power Query, en cada consulta:
Combinar las consultas para validar las claves
Para detectar ventas con un IdProducto que no existe en DimProducto, une las dos consultas.
Elige Hechos[IdProducto] como columna izquierda y DimProducto[IdProducto] como columna derecha, y usa Left Outer Join. Expande la columna añadida; las filas con null son ventas con producto inexistente.
Cerrar y cargar las tablas al modelo de datos
Marca "Solo crear conexión" y "Agregar estos datos al modelo de datos". Las tablas quedan en el modelo de datos (en la pestaña Power Pivot) y se pueden usar en tablas dinámicas grandes sin BUSCARV.
Errores típicos al montar el modelo en Excel
La tabla dinámica duplica o pierde filas al filtrar
La clave de la dimensión no es única. La causa más frecuente es un duplicado en DimProducto: dos filas con el mismo IdProducto pero distinto nombre. Para detectarlo:
=CONTAR.SI(DimProducto[IdProducto];[@IdProducto])Si devuelve 2, hay duplicado. Revisa DimProducto, borra la fila sobrante y actualiza la tabla dinámica.
BUSCARV devuelve #N/A en todas las filas
El dato de la dimensión no está escrito exactamente igual: un espacio al final, mayúsculas distintas o un acento olvidado. Para limpiarlo añade una columna auxiliar con =MAYUSC(ESPACIOS.LIMPIOS([@Producto])) en DimProducto y úsala como clave. En Excel 2021 o Microsoft 365, cambia a BUSCARX con coincidencia exacta y el cuarto argumento en blanco para que no devuelva error.
El modelo se vuelve lento al cruzar varias dimensiones
La tabla de hechos tiene columnas calculadas con fórmulas que se recalculan al cambiar cualquier celda. La solución es mover esos cálculos a Power Query o a una medida de tabla dinámica (campo calculado) con =SUMAR.SI.CONJUNTO(Hechos[Importe];Hechos[IdProducto];[@IdProducto]) en una celda auxiliar de la dimensión, no en Hechos.
No se puede abrir en Excel 2007 ni en Excel para la web
Las tablas dinámicas con modelo de datos necesitan Excel 2010 o posterior. En Excel 2007 no existe el modelo de datos y la tabla dinámica solo lee la tabla única. En Excel para la web el archivo se abre y se ven las tablas dinámicas, pero no se puede editar el modelo de datos ni refrescar Power Query. Para los usuarios con estas versiones, usa tablas dinámicas clásicas sin modelo de datos: agrega los campos de dimensión directamente arrastrándolos desde la tabla de hechos ampliada con BUSCARV.
Lo que ya sabes hacer con el modelo estrella y el copo de nieve
- Dividir una hoja plana de ventas en una tabla de hechos central y cuatro dimensiones limpias con clave única.
- Sustituir el texto repetido por claves externas con BUSCARX en Excel 2021 y Microsoft 365, o con BUSCARV y referencias estructuradas en Excel 2016 y 2019.
- Montar una tabla dinámica que cruce Producto, Mes, Tienda e Importe y se filtre sin romper la estructura.
- Crear medidas con SUMAR.SI.CONJUNTO junto a la dimensión para ver totales por atributo.
- Normalizar DimProducto en un copo de nieve con DimCategoria y DimSubcategoria, y decidir conscientemente cuándo aplanarlo.
- Cargar las dimensiones pesadas en Power Query, combinarlas para validar claves y dejarlas listas en el modelo de datos.
El siguiente paso natural es Power Pivot y el lenguaje DAX (Data Analysis Expressions), que permite crear medidas explícitas como Total Ventas := SUM(Hechos[Importe]) y trabajar con calendarios, acumulados y comparativos entre periodos, todo dentro de Microsoft 365 o Excel 2021 con el complemento Power Pivot activado.
=BUSCARV(A2;B2:C10;2;FALSO)). Si tu Excel está configurado con punto decimal (México, Estados Unidos), el separador es la coma: =BUSCARV(A2,B2:C10,2,FALSO).Dudas al montar el modelo estrella y el copo de nieve en Excel
1. Qué es un modelo estrella y un modelo copo de nieve en Excel
El modelo estrella organiza los datos en una tabla central de hechos rodeada de tablas de dimensiones, mientras que el copo de nieve normaliza esas dimensiones en varias tablas relacionadas. En Excel, el primero se implementa con tablas separadas y BUSCARX para combinar la información, y el segundo con más tablas intermedias unidas por clave.
2. Qué diferencia hay entre el modelo estrella y el copo de nieve
El estrella desnormaliza las dimensiones en una sola tabla cada una, lo que hace las consultas más rápidas y sencillas. El copo de nieve las divide en varias tablas para evitar repetición de datos, a costa de joins más largos. En Excel, elige estrella si priorizas velocidad de cálculo y copo de nieve si buscas ahorrar espacio en dimensiones muy grandes.
3. Cómo se aplica el modelo estrella y copo de nieve en Power BI
En Power BI, el modelo estrella se crea cargando las tablas de hechos y dimensiones en el modelo de datos y relacionándolas por columnas clave, formando una relación de uno a varios. El copo de nieve añade relaciones adicionales entre las dimensiones, creando jerarquías normalizadas. Power BI recomienda siempre el esquema estrella porque su motor VertiPaq lo optimiza mejor.
4. Cómo es un ejemplo práctico de esquema estrella
Un ejemplo típico: una tabla de hechos Ventas con columnas ProductoID, ClienteID, FechaID e Importe, y a su alrededor las tablas Productos, Clientes y Fechas, cada una con su clave primaria. En Excel, cada tabla va en su hoja, se nombran como tablas y se relacionan en Datos > Relaciones. Luego un BUSCARX en Ventas![Producto] te devuelve el nombre del producto desde Productos.
5. Cómo se diseña un modelo estrella en una base de datos
Partimos de un proceso de negocio, definimos la granularidad de la tabla de hechos y luego identificamos las dimensiones que la rodean. Cada dimensión debe tener una clave primaria sustituta única y columnas descriptivas desnormalizadas. En SQL Server o MySQL, las tablas se crean con CREATE TABLE y se relacionan con FOREIGN KEY referenciando la clave de cada dimensión.
6. Cómo montar un modelo estrella con SQL
Crea la tabla de hechos con columnas clave hacia cada dimensión y columnas métricas, y las tablas de dimensión con su clave primaria y atributos descriptivos. Define las relaciones con claves foráneas y rellena las dimensiones antes que los hechos. Un SELECT con JOIN entre hechos y dimensiones te devuelve el reporte desnormalizado que necesitas.
7. Qué es un modelo estrella en un Data Warehouse
Es el esquema más usado en Data Warehouse porque equilibra sencillez y rendimiento analítico: una tabla de hechos central con métricas y claves, conectada a tablas de dimensiones desnormalizadas. Fue popularizado por Ralph Kimball a partir de los años 90. En Excel puede simularse con tablas y BUSCARX, aunque en entornos profesionales se implementa en SQL Server, Snowflake o BigQuery.