Los presupuestos se realizan en el nivel superior: ingresos por línea de producto por región por mes. Los datos reales se acumulan lentamente con el tiempo: factura por factura, artículo de línea por artículo de línea. Comparar el pequeño archivo de Budget con los voluminosos datos reales ha sido una molestia para siempre. Me encanta este truco de Rob Collie, también conocido como PowerPivotPro.com.
Para configurar el ejemplo, tiene una tabla de presupuesto de 54 filas: 1 fila por mes por región por producto.
El archivo de la factura está a nivel de detalle: 422 filas en lo que va de año.
No hay VLOOKUP en el mundo que le permita hacer coincidir estos dos conjuntos de datos. Pero, gracias a Power Pivot (también conocido como el modelo de datos en Excel 2013+), esto se vuelve fácil.
Necesita crear pequeñas tablas pequeñas que yo llamo "ensambladores" para vincular los dos conjuntos de datos más grandes.
En mi caso, Producto, Región y Fecha son comunes entre las dos tablas. La tabla Producto es una pequeña tabla de cuatro celdas. Lo mismo ocurre con la región. Cree cada uno de ellos copiando datos de una tabla y utilizando Eliminar duplicados.
La tabla de calendario de la derecha fue en realidad más difícil de crear. Los datos presupuestarios tienen una fila por mes, siempre al final del mes. Los datos de la factura muestran fechas diarias, generalmente entre semana. Entonces, tuve que copiar el campo Fecha de ambos conjuntos de datos en una sola columna y luego eliminar los duplicados para asegurarme de que todas las fechas estén representadas. Luego solía =TEXT(J4,"YYYY-MM")crear una columna Mes a partir de las fechas diarias.
Si no tiene el complemento Power Pivot completo, debe crear una tabla dinámica a partir de la tabla Presupuesto y seleccionar la casilla de verificación Agregar estos datos al modelo de datos.
Como se discutió en el consejo anterior, a medida que agrega campos a la tabla dinámica, tendrá que definir seis relaciones. Si bien puede hacer esto con seis visitas al cuadro de diálogo Crear relación, activé mi complemento Power Pivot y usé la vista de diagrama para definir las seis relaciones.
Aquí está la clave para hacer que todo esto funcione: Puede usar los campos numéricos de Presupuesto y Real. Pero si desea mostrar Región, Producto o Mes en la tabla dinámica, ¡deben provenir de las tablas de unión!
Aquí hay una tabla dinámica con datos provenientes de cinco tablas. La columna A proviene del ensamblador de región. La fila 2 proviene del ensamblador de Calendar. La cortadora de productos es del ensamblador de productos. Los números de Presupuesto provienen de la tabla de Presupuesto y los números reales provienen de la tabla de Factura.
Esto funciona porque las tablas de unión aplican filtros a la tabla Presupuesto y Real. Es una técnica hermosa y muestra que Power Pivot no es solo para big data.
