Estructurar datos de ventas

Llegamos al proyecto final del curso. Durante las proximas cuatro lecciones, construiras un dashboard profesional de ventas en Excel que integra todas las tecnicas aprendidas: tablas dinamicas, graficos avanzados, funciones de analisis, y Power Query.

En esta primera leccion, nos enfocaremos en lo mas importante: estructurar correctamente los datos. Un dashboard es tan bueno como sus datos base, y una estructura deficiente causara problemas en cada paso posterior.

El escenario del proyecto

Trabajaras como analista de Business Intelligence para Distribuidora Nacional, una empresa de distribucion de productos de consumo con:

• 4 regiones de venta: Norte, Sur, Este, Oeste
• 12 vendedores distribuidos en las regiones
• 50+ productos en 5 categorias
• Ventas mensuales de aproximadamente $2.5M

El gerente comercial necesita un dashboard que responda:

1. Como van las ventas vs el objetivo mensual
2. Que regiones y vendedores estan cumpliendo metas
3. Cuales son los productos mas vendidos
4. Como es la tendencia de ventas en el tiempo

Estructura de datos requerida

Para este dashboard necesitamos tres tablas principales y una tabla de soporte.

Tabla 1: Ventas (Fact Table)

Esta es la tabla central con todas las transacciones:

Columna	Tipo	Descripcion	Ejemplo
ID_Venta	Texto	Identificador unico	VTA-2026-00001
Fecha	Fecha	Fecha de la venta	15/01/2026
ID_Vendedor	Texto	Codigo del vendedor	V001
ID_Producto	Texto	Codigo del producto	P0012
Region	Texto	Region de la venta	Norte
Unidades	Numero	Cantidad vendida	150
Precio_Unitario	Moneda	Precio por unidad	$45.00
Descuento	Porcentaje	Descuento aplicado	5%
Total_Venta	Moneda	Monto total	$6,412.50

Tabla 2: Vendedores (Dimension)

Informacion de cada vendedor:

Columna	Tipo	Descripcion	Ejemplo
ID_Vendedor	Texto	Codigo unico	V001
Nombre	Texto	Nombre completo	Ana Martinez
Region	Texto	Region asignada	Norte
Fecha_Ingreso	Fecha	Inicio en empresa	01/03/2022
Meta_Mensual	Moneda	Objetivo de ventas	$85,000

Tabla 3: Productos (Dimension)

Catalogo de productos:

Columna	Tipo	Descripcion	Ejemplo
ID_Producto	Texto	Codigo unico	P0012
Nombre_Producto	Texto	Nombre del producto	Monitor LED 24"
Categoria	Texto	Categoria del producto	Tecnologia
Costo	Moneda	Costo de compra	$180.00
Precio_Lista	Moneda	Precio de venta	$320.00
Stock_Minimo	Numero	Stock de seguridad	50

Tabla 4: Calendario (Soporte)

Tabla de fechas para analisis temporal:

Columna	Tipo	Descripcion	Ejemplo
Fecha	Fecha	Fecha unica	15/01/2026
Ano	Numero	Ano	2026
Mes	Numero	Numero de mes	1
Nombre_Mes	Texto	Nombre del mes	Enero
Trimestre	Texto	Trimestre	Q1
Semana	Numero	Numero de semana	3

Paso 1: Crear la tabla de Ventas

Abre un nuevo libro de Excel y crea la primera hoja llamada "Ventas".

Configurar los encabezados

En la fila 1, escribe los encabezados:

A1: ID_Venta
B1: Fecha
C1: ID_Vendedor
D1: ID_Producto
E1: Region
F1: Unidades
G1: Precio_Unitario
H1: Descuento
I1: Total_Venta

Agregar datos de ejemplo

Ingresa estos registros de ejemplo para probar la estructura:

VTA-2026-00001,15/01/2026,V001,P0012,Norte,150,45.00,5%,6412.50
VTA-2026-00002,15/01/2026,V002,P0025,Norte,80,120.00,0%,9600.00
VTA-2026-00003,16/01/2026,V003,P0008,Sur,200,28.50,10%,5130.00
VTA-2026-00004,16/01/2026,V004,P0012,Sur,100,45.00,3%,4365.00
VTA-2026-00005,17/01/2026,V005,P0031,Este,50,250.00,8%,11500.00
VTA-2026-00006,17/01/2026,V006,P0018,Este,300,15.75,0%,4725.00
VTA-2026-00007,18/01/2026,V007,P0025,Oeste,120,120.00,5%,13680.00
VTA-2026-00008,18/01/2026,V008,P0008,Oeste,180,28.50,0%,5130.00
VTA-2026-00009,19/01/2026,V009,P0045,Norte,60,89.00,12%,4700.40
VTA-2026-00010,19/01/2026,V010,P0012,Sur,250,45.00,7%,10462.50

Convertir a tabla de Excel

Este paso es fundamental para que las formulas y referencias funcionen automaticamente:

1. Selecciona cualquier celda con datos
2. Presiona Ctrl+T (o Insertar > Tabla)
3. Verifica que "La tabla tiene encabezados" este marcado
4. Clic en Aceptar
5. Con la tabla seleccionada, ve a Diseno de tabla en la cinta
6. En el campo Nombre de la tabla, escribe: tbl_Ventas

Configurar formatos de columna

Selecciona cada columna y aplica el formato correcto:

• Columna B (Fecha): Formato de fecha corta
• Columna F (Unidades): Numero sin decimales
• Columna G (Precio_Unitario): Moneda con 2 decimales
• Columna H (Descuento): Porcentaje con 0 decimales
• Columna I (Total_Venta): Moneda con 2 decimales

Formula de Total_Venta

En la columna I, reemplaza los valores estaticos con una formula:

=[@Unidades]*[@Precio_Unitario]*(1-[@Descuento])

Esta formula usa referencias estructuradas de tabla, lo que significa que se aplicara automaticamente a nuevas filas.

Paso 2: Crear la tabla de Vendedores

Crea una nueva hoja llamada "Vendedores".

Estructura y datos

ID_Vendedor,Nombre,Region,Fecha_Ingreso,Meta_Mensual
V001,Ana Martinez,Norte,01/03/2022,85000
V002,Carlos Ruiz,Norte,15/06/2021,90000
V003,Diana Lopez,Norte,08/11/2023,75000
V004,Eduardo Sanchez,Sur,01/02/2020,95000
V005,Fernanda Garcia,Sur,20/09/2022,80000
V006,Gabriel Torres,Sur,12/04/2024,70000
V007,Helena Morales,Este,05/01/2021,85000
V008,Ivan Herrera,Este,18/08/2023,75000
V009,Julia Mendez,Este,30/03/2022,80000
V010,Kevin Ortiz,Oeste,10/07/2020,90000
V011,Laura Vega,Oeste,22/12/2022,85000
V012,Miguel Flores,Oeste,14/05/2024,70000

Convertir a tabla

1. Selecciona los datos
2. Ctrl+T para convertir a tabla
3. Nombra la tabla: tbl_Vendedores

Formato de columnas

• Columna D (Fecha_Ingreso): Fecha corta
• Columna E (Meta_Mensual): Moneda sin decimales

Paso 3: Crear la tabla de Productos

Crea una nueva hoja llamada "Productos".

Estructura y datos

ID_Producto,Nombre_Producto,Categoria,Costo,Precio_Lista,Stock_Minimo
P0008,Papel Bond A4,Papeleria,18.00,28.50,200
P0012,Monitor LED 24",Tecnologia,180.00,320.00,50
P0018,Boligrafos x12,Papeleria,8.50,15.75,500
P0025,Teclado Mecanico,Tecnologia,75.00,120.00,80
P0031,Silla Ergonomica,Mobiliario,150.00,250.00,30
P0038,Escritorio Ejecutivo,Mobiliario,280.00,450.00,20
P0045,Impresora Laser,Tecnologia,350.00,550.00,25
P0052,Archivador 4 Gavetas,Mobiliario,180.00,295.00,15
P0059,Mouse Inalambrico,Tecnologia,22.00,45.00,150
P0066,Lampara LED Escritorio,Mobiliario,35.00,65.00,100

Convertir a tabla

1. Ctrl+T para crear tabla
2. Nombre: tbl_Productos

Agregar columna de Margen

Agrega una columna calculada para el margen de ganancia:

=[@Precio_Lista]-[@Costo]

Nombra esta columna "Margen" y formatea como moneda.

Paso 4: Crear la tabla de Calendario

Esta tabla es esencial para analisis de tiempo. Crea una hoja llamada "Calendario".

Generar fechas con formula

En la celda A1 escribe "Fecha", y en A2:

=DATE(2026,1,1)

Luego arrastra hacia abajo hasta cubrir todo el ano (hasta el 31/12/2026).

Agregar columnas calculadas

Columna	Formula
B (Ano)	=YEAR([@Fecha])
C (Mes)	=MONTH([@Fecha])
D (Nombre_Mes)	=TEXT([@Fecha],"mmmm")
E (Mes_Corto)	=TEXT([@Fecha],"mmm")
F (Trimestre)	="Q"&ROUNDUP(MONTH([@Fecha])/3,0)
G (Semana)	=WEEKNUM([@Fecha])
H (Dia_Semana)	=TEXT([@Fecha],"dddd")

Convertir a tabla

Nombra la tabla: tbl_Calendario

Paso 5: Agregar validacion de datos

Las validaciones previenen errores de captura y mantienen la integridad de los datos.

Validar Region en tabla Ventas

1. Ve a la hoja Ventas
2. Selecciona toda la columna E (Region), excluyendo el encabezado
3. Datos > Validacion de datos
4. En "Permitir", selecciona "Lista"
5. En "Origen", escribe: Norte,Sur,Este,Oeste
6. En la pestana "Mensaje de error", escribe un mensaje claro

Validar ID_Vendedor

1. Selecciona la columna C (ID_Vendedor)
2. Datos > Validacion de datos
3. Permitir: Lista
4. Origen: =tbl_Vendedores[ID_Vendedor]

Esta validacion usa la lista dinamica de vendedores, asi que si agregas nuevos vendedores, automaticamente estaran disponibles.

Validar ID_Producto

1. Selecciona la columna D (ID_Producto)
2. Datos > Validacion de datos
3. Permitir: Lista
4. Origen: =tbl_Productos[ID_Producto]

Validar valores numericos

Para la columna Unidades:

1. Datos > Validacion de datos
2. Permitir: Numero entero
3. Minimo: 1
4. Maximo: 10000
5. Mensaje de error: "Las unidades deben ser entre 1 y 10,000"

Para el Descuento:

1. Permitir: Decimal
2. Minimo: 0
3. Maximo: 0.5 (50% maximo)

Paso 6: Conectar con Power Query

Aunque los datos estan en el mismo libro, configurar Power Query permite actualizaciones automaticas y transformaciones avanzadas.

Cargar tabla Ventas a Power Query

1. Selecciona cualquier celda de tbl_Ventas
2. Datos > Desde tabla o rango
3. Se abrira Power Query con los datos

Transformaciones basicas

En Power Query, aplica estas transformaciones:

1. Verificar tipos de datos: Asegurate que cada columna tenga el tipo correcto

2. Agregar columna de Ano-Mes:

   • Agregar columna > Columna personalizada
   • Nombre: AnoMes
   • Formula: Date.ToText([Fecha], "yyyy-MM")

3. Agregar columna de Dia de Semana:

   • Agregar columna > Columna personalizada
   • Nombre: DiaSemana
   • Formula: Date.DayOfWeekName([Fecha])

Guardar la consulta

1. Clic en Cerrar y cargar
2. Selecciona "Solo crear conexion"
3. Marca "Agregar estos datos al modelo de datos"

Repite el proceso para las tablas de Vendedores, Productos y Calendario.

Paso 7: Crear relaciones en el modelo de datos

Con las tablas cargadas al modelo, necesitamos relacionarlas.

1. Ve a Datos > Administrar modelo de datos (o Power Pivot)
2. En la vista de diagrama, arrastra:
   • tbl_Ventas[ID_Vendedor] hacia tbl_Vendedores[ID_Vendedor]
   • tbl_Ventas[ID_Producto] hacia tbl_Productos[ID_Producto]
   • tbl_Ventas[Fecha] hacia tbl_Calendario[Fecha]

El modelo resultante deberia verse asi:

                    tbl_Calendario
                          |
                          | (1:*)
                          v
tbl_Vendedores  <--  tbl_Ventas  -->  tbl_Productos
     (1:*)                              (1:*)

Dataset completo para el proyecto

Para tener suficientes datos para un dashboard significativo, necesitaras al menos 500-1000 registros de ventas. Puedes:

1. Generar datos manualmente: Copia y modifica los ejemplos
2. Usar formula RANDBETWEEN: Genera datos aleatorios
3. Descargar dataset: El archivo de practica estara disponible en los recursos del curso

Formula para generar datos de prueba

Si quieres generar datos aleatorios, usa estas formulas en una hoja auxiliar:

' ID_Venta (en A2)
="VTA-2026-"&TEXT(ROW()-1,"00000")

' Fecha (en B2) - Fechas aleatorias de enero 2026
=DATE(2026,1,1)+RANDBETWEEN(0,30)

' ID_Vendedor (en C2)
="V"&TEXT(RANDBETWEEN(1,12),"000")

' ID_Producto (en D2)
=INDEX(tbl_Productos[ID_Producto],RANDBETWEEN(1,COUNTA(tbl_Productos[ID_Producto])))

' Unidades (en F2)
=RANDBETWEEN(10,300)

' Descuento (en H2)
=CHOOSE(RANDBETWEEN(1,5),0,0.03,0.05,0.08,0.1)

Verificacion de la estructura

Antes de continuar, verifica que tu estructura cumpla estos criterios:

Verificacion	Estado
Tabla tbl_Ventas creada con 9 columnas	[ ]
Tabla tbl_Vendedores con 5 columnas	[ ]
Tabla tbl_Productos con 7 columnas (incluyendo Margen)	[ ]
Tabla tbl_Calendario con 8 columnas	[ ]
Validaciones de datos configuradas	[ ]
Consultas de Power Query creadas	[ ]
Relaciones en el modelo de datos establecidas	[ ]

Errores comunes a evitar

1. Celdas combinadas: Nunca uses celdas combinadas en tablas de datos
2. Filas en blanco: Eliminan la continuidad de la tabla
3. Formatos inconsistentes: Fechas como texto causan problemas en analisis
4. IDs duplicados: Cada registro debe tener un identificador unico
5. Espacios en nombres de columna: Usa guiones bajos en lugar de espacios

Puntos clave de esta leccion

• La estructura de datos es la base de cualquier dashboard exitoso
• Las tablas de Excel proporcionan referencias estructuradas y expansion automatica
• La validacion de datos previene errores de captura
• Power Query permite transformaciones y actualizaciones automaticas
• El modelo de datos relaciona las tablas para analisis cruzado
• Un minimo de 500 registros genera dashboards significativos

En la siguiente leccion, crearemos las metricas y KPIs que forman el corazon del dashboard de ventas.

---

Ejercicio practico

1. Crea las cuatro tablas siguiendo las instrucciones
2. Genera al menos 100 registros de ventas de prueba
3. Configura las validaciones de datos
4. Carga las tablas a Power Query
5. Establece las relaciones en el modelo de datos
6. Verifica que no haya errores de tipos de datos

Recursos adicionales

• Archivo de Excel con estructura completa (disponible en recursos del curso)
• Dataset de ventas de ejemplo con 1000 registros
• Plantilla de validacion de datos