El modelo de datos de Excel (Power Pivot) le permite conectar un gran conjunto de datos detallados de datos reales a un presupuesto de nivel superior mediante el uso de tablas de unión.
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: una 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 marcar la casilla para 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.
Ver video
- Tiene un pequeño conjunto de datos presupuestarios de arriba hacia abajo
- Quiere comparar con un conjunto de datos reales ascendentes
- Los datos reales pueden provenir de un registro de facturas
- El modelo de datos le permitirá comparar estos conjuntos de datos de diferentes tamaños
- Convierta ambos conjuntos de datos en una tabla Ctrl + T
- Para cada campo de texto sobre el que desee informar, cree una tabla de unión
- Copie los valores y elimine los duplicados
- Para las fechas, puede incluir fechas de ambas tablas y convertirlas a fin de mes
- Hacer que los carpinteros sean tablas Ctrl + T
- Opcional pero útil para nombrar las cinco tablas
- Cree una tabla dinámica desde Budget y elija el modelo de datos
- Cree una tabla dinámica utilizando Presupuesto y Real de las tablas originales
- Todos los demás campos deben provenir de las tablas de unión.
- Agregar cortadoras por producto
- Cree tres relaciones de Budget a Joiners
- Cree tres relaciones de Real a Joiners
- Mañana: cómo construir relaciones es más fácil con Power Pivot y DAX Formulas
Transcripción del video
Aprenda Excel del podcast, episodio 2016 - ¡Presupuesto de arriba hacia abajo frente a datos reales de abajo hacia arriba!
Oye, estoy publicando un podcast de este libro completo, haz clic en esa "i" en la esquina superior derecha y sigue la lista de reproducción.
Oye, voy a interrumpir esto, este es Bill Jelen de 15 minutos a partir de ahora. Ahora me doy cuenta de que este es un podcast increíblemente largo, y estás tentado a hacer clic en él, pero déjame darte un breve resumen. Si está en Excel 2013, y alguna vez ha tenido una tabla de presupuesto pequeña y una tabla de datos reales masiva, y necesita mapearlas juntas, esta es una nueva habilidad asombrosa que tenemos en Excel 2013, que no mucha gente ha explicado , y probablemente no lo sepas. Si este es usted, está en 2013 y necesita mapear estos dos conjuntos de datos, tómese el tiempo, tal vez hoy, tal vez mañana, tal vez agregarlo a la lista de observación, vale la pena, es una técnica increíble.
Muy bien, esto es lo que tenemos, en el lado izquierdo tenemos un presupuesto, este presupuesto, se hace en el nivel superior, de arriba hacia abajo, a la derecha para cada línea de productos, para cada región, para cada mes, hay un presupuesto . No hay muchos registros aquí, recuento de 55, en el lado derecho estamos tratando de comparar esto con los datos reales. Los datos reales provienen de un registro de facturas, por lo que tenemos Región, Producto e Ingresos, pero son facturas individuales, muchos más datos aquí, ya estamos a mitad de año y ya tengo 423 registros. Muy bien, entonces, ¿cómo se asignan estos 55 a estos 423? Puede ser difícil de hacer con BUSCARV, primero tendría que resumir, pero afortunadamente en Excel 2013, el modelo de datos lo hace realmente muy fácil. Lo que necesitamos para permitir que esta gran mesa se comunique con esta pequeña mesa son intermediarios, yo los llamo carpinteros.Pequeñas tablas, Producto, Región y Calendario, vamos a unir el presupuesto a estas tres tablas, vamos a unir lo real a estas tres tablas, y milagrosamente la tabla dinámica funcionará. Muy bien, así es como lo hacemos.
Primero, necesito crear los ensambladores, así que tomo este campo Producto de la columna A, y lo copio a la columna F, y luego Datos, Eliminar duplicados, haga clic en Aceptar y nos queda una pequeña tabla pequeña, 1 encabezado 3 filas. Lo mismo para Región, tome las regiones, Ctrl + C, vaya a la columna G, Pegar, Eliminar duplicados, haga clic en Aceptar, 3 filas 1 encabezado, de acuerdo. Ahora, para las fechas, las fechas no son las mismas, estas son fechas de finalización de mes, en realidad se almacenan como fechas de finalización de mes y estos son días de semana. Voy a tomar ambas listas, Ctrl + C la segunda lista y pegarla aquí, Ctrl + V, luego voy a tomar la lista más corta, copiarla y pegarla abajo, de acuerdo. Y es realmente molesto que, aunque se almacenan como fechas, aparecen como meses y Eliminar duplicados no los verá como iguales.Entonces, antes de usar Eliminar duplicados, tengo que cambiarlo a una fecha corta. Elija esos datos, Datos, Eliminar duplicados, haga clic en Aceptar y luego ordene un poco aquí para que funcione.
Muy bien, ahora no quiero informar por fecha diaria, así que voy a agregar una columna aquí, una columna de búsqueda que dice Mes, y será igual a EOMONTH esa fecha, 0, lo que nos llevará a el final del mes. Lo formateará como una fecha corta y lo copiará, de acuerdo. Ahora, debemos convertir cada uno de estos en una tabla Ctrl + T, así que desde aquí Ctrl + T, Mi tabla tiene encabezados, hermoso. Los pequeños, no se dan cuenta de que son encabezados allá arriba, así que tenemos que asegurarnos de marcar eso y Ctrl + T, está bien, y llaman a estas tablas Table1, Table2, Table3, nombres realmente aburridos, ¿verdad? Así que voy a cambiarles el nombre y llamarlo BudTable, ProdTable, RegTable, my CalTable y luego ActTable, de acuerdo.
Comenzamos desde la primera tabla y, por cierto, no vamos a usar PowerPivot hoy, vamos a hacer todo esto con el modelo de datos. Entonces, Excel 2013 o más reciente, tiene este Insert, PivotTable, vamos a marcar la casilla para "Agregar estos datos al modelo de datos", hacer clic en Aceptar y obtenemos nuestra lista de campos con el botón mágico Todos, que permite Yo elijo entre las cinco tablas del libro de trabajo: Real, Presupuesto, Calendario, Producto, Región. Muy bien, entonces los números vendrán de la tabla de Presupuesto, pondré el presupuesto allí, y de la tabla Actual pondré el real allí, pero luego, aquí está lo que pasa con el resto de la tabla Pivot. Cualquier otro campo de texto que vayamos a poner en el área de la fila o el área de la columna o como rebanadores, tienen que venir de los ensambladores, tienen que venir de esas tablas entre las tablas.
Muy bien, de la tabla Calendario tomaremos ese campo Mes y lo colocaremos en la parte superior, vamos a ignorar otras relaciones en este momento. Estaré creando las relaciones, pero quiero crearlas todas a la vez. Y la tabla Región, ponga las regiones al lado. Podría dejar los productos a un lado, pero en realidad voy a usar la tabla Producto como rebanador, así que Analizar, Insertar rebanador, nuevamente tienes que ir a Todos si aún no has usado la tabla Producto. Así que vaya a Todos y verá que el Producto está disponible para crear como segmentador de los productos, así. Muy bien, en este punto no hemos creado relaciones, por lo que todos estos números son incorrectos. Y las relaciones que tenemos que crear, tenemos que crear 3 tablas de esta pequeña tabla de presupuesto, una para los productos, una para las regiones, una para el calendario,eso es 3 relaciones. Y luego tenemos que crear relaciones desde la tabla Actual a la región Producto en Calendario, por lo que un total de 6 tablas. Y sí, esto definitivamente sería más fácil si tuviéramos PowerPivot, pero no lo tenemos o supongamos que no lo tenemos.
Entonces voy a usar la forma antigua, el diálogo Crear aquí, donde tenemos la tabla Presupuesto a la izquierda, y vamos a usar el campo Región y relacionarlo con la tabla Región, el campo Región. . Muy bien, se crean 1/6. Elegiré Crear, nuevamente desde la tabla Presupuesto vamos al Producto, y luego lo vinculamos a la tabla Producto, al Producto, hacemos clic en Aceptar. Desde la tabla Presupuesto, el campo Fecha, vamos a la tabla Calendario y el campo Destino, haga clic en Aceptar, estamos a la mitad del camino, está bien. Desde la tabla Datos reales, vamos a Región, a la tabla Región, hacemos clic en Aceptar, de la tabla Datos reales al Producto y de la tabla Datos reales al Calendario. De hecho, voy a tomar los valores y hacer que bajen por el costado, está bien. Diseño, diseño de informe, mostrar en forma tabular para obtener la vista que prefiero, repetir todas las etiquetas de elementos, de acuerdo,¡esto es absolutamente asombroso! Ahora tenemos esta pequeña tabla diminuta, unos 50 registros en esta tabla de cientos de registros, y hemos creado una única tabla dinámica gracias al modelo de datos. Para cada lugar donde podemos ver el Presupuesto, podemos ver los Ingresos, está desglosado por Región, está desglosado por Mes y se puede dividir por Producto.
Ahora, este concepto me vino de Rob Collie, que ejecuta Power Pivot Pro, y Rob ha creado muchos libros, su último es "Power Pivot y Power BI". Creo que este estaba en el libro "Power Pivot Alchemy", es el que vi esto y dije "Bueno, esto, aunque no tengo millones de filas para informar a través de Power Pivot, este es uno que he hecho una GRAN diferencia en mi vida, al tener dos conjuntos de datos de tamaños no coincidentes y la necesidad de informar de ambos ". Bueno, este ejemplo y muchos otros están en este libro, eventualmente obtendré el podcast del libro completo, que parece que tomará dos meses y medio. Pero puede obtener el libro completo hoy, a la misma hora, ir allí, comprar el libro, $ 10 por el libro electrónico, $ 25 por el libro impreso, y puede tener todos esos consejos a la vez.
Muy bien, un episodio realmente largo aquí: tenemos un pequeño presupuesto de arriba hacia abajo y un real de abajo hacia arriba, son de diferentes tamaños, pero usando el modelo de datos en Excel 2013 … Y por cierto, si estás en 2010, , en teoría, haga esto obteniendo el complemento Power Pivot y siga todos estos pasos en 2010. Convierta ambos conjuntos de datos en una tabla Ctrl + T, y luego una sus tablas para cualquier cosa sobre la que desee informar, en el etiqueta de fila, o la etiqueta de columna, o las segmentaciones, así que copie esos valores y elimine duplicados para las fechas. De hecho, tomé valores de ambas tablas, porque había algunos valores únicos en cada una, y luego usé EOMONTH para salir, hacer que esas tablas de unión sean tablas controladas. Es opcional, pero nombré las 5 tablas, porque es más fácil cuando estás configurando esas relaciones, en lugar de que te llamen Table1,Tabla2, Tabla3.
Y así, comience desde la tabla Presupuesto, Insertar, Tabla dinámica, marque la casilla de Modelo de datos y luego cree una tabla dinámica utilizando Presupuesto y Real. Todo lo demás proviene de las tablas de unión, por lo que Región y Mes en el área de filas y columnas, las segmentaciones provienen de la tabla Producto. Y luego tuvimos que crear 3 relaciones del Presupuesto a los carpinteros, 3 relaciones del Actual a los carpinteros, y tenemos una tabla dinámica increíble. Ahora, mañana veremos cómo usar la pestaña Power Pivot y cómo crear algunos cálculos adicionales. Entonces, todo esto es posible, es cuando queremos insertar un campo calculado, es entonces cuando tienes que pagar los $ 2 adicionales al mes para obtener la versión Pro Plus de Office 365.
Bueno, oye, gracias a Rob Collie de Power Pivot Pro por este consejo, y gracias a ti por pasar, ¡nos vemos la próxima vez para otro netcast de!
Descargar archivo
Descargue el archivo de muestra aquí: Podcast2016.xlsx
