Excel Power Pivot: administración del modelo de datos
AREAtutorial » Big Data & Analitycs » Excel Power Pivot: administración del modelo de datos
El uso principal de Power Pivot es su capacidad para administrar tablas de datos y las relaciones entre ellas para facilitar el análisis de datos de varias tablas. Puede agregar una tabla de Excel al modelo de datos cuando crea una tabla dinámica o directamente desde la cinta de PowerPivot.
Solo puede analizar datos de varias tablas si existen relaciones entre ellas. Con Power Pivot, puede crear relaciones desde la vista de datos o la vista de gráfico. Además, si decide agregar una tabla a Power Pivot, también debe agregar una relación.
Cuando crea una tabla dinámica en Excel, se basa en una sola tabla / rango. Si desea agregar más tablas a la tabla dinámica, puede hacerlo utilizando el modelo de datos.
Suponga que tiene dos hojas en su libro de trabajo:
Uno que contiene datos sobre los vendedores y las regiones que representan en la tabla: Vendedor.
Otro que contiene datos de ventas por región y mes en la tabla es Ventas.
Puede resumir las ventas, desde el punto de vista del vendedor, como se muestra a continuación.
Haga clic en la tabla – Ventas.
Haga clic en la pestaña INSERTAR en la cinta.
Seleccione Tabla dinámica en el grupo Tablas.
Se creará una tabla dinámica vacía con campos de la tabla de ventas: región, mes y monto del pedido. Como puede ver, hay MAS TABLAS debajo de la lista de campos de la tabla dinámica.
Haga clic en MÁS TABLAS.
EN Crea una nueva tabla dinámica aparecerá un cuadro de mensaje. Mensaje mostrado: para utilizar varias tablas en su análisis, debe crear una nueva tabla dinámica utilizando el modelo de datos. Haga clic en Sí.
Se creará una nueva tabla dinámica como se muestra a continuación:
En los campos de la tabla dinámica, puede observar que hay dos pestañas: ACTIVO y TODOS…
Haga clic en la pestaña TODOS.
En la lista de «Campos de tabla dinámica» se muestran dos tablas: «Ventas» y «Vendedor» con los campos correspondientes.
Haga clic en el campo Vendedor en la tabla Vendedor y arrástrelo al área FILAS.
Haga clic en el campo Mes en la tabla Ventas y arrástrelo al área FILAS.
Haga clic en el campo Monto del pedido en la tabla Ventas y arrástrelo al área ∑ VALORES.
Se ha creado la tabla dinámica. Aparecerá un mensaje en los campos de la tabla dinámica: Es posible que se requieran relaciones entre tablas…
Haga clic en el botón CREAR junto al mensaje. EN Crear relación Un cuadro de diálogo aparecerá.
Debajo Mesa, seleccione Ventas.
Debajo Columna (extranjera) en el cuadro, seleccione Región.
Debajo Tabla vinculada, seleccione Vendedor.
Debajo Columna asociada (principal) en el cuadro, seleccione Región.
Haga clic en Aceptar.
Su tabla dinámica de dos tablas en dos hojas está lista.
Además, como declaró Excel al agregar la segunda tabla a la tabla dinámica, la tabla dinámica se creó utilizando un modelo de datos. Para comprobarlo, haga lo siguiente:
Haga clic en la pestaña POWERPIVOT en la cinta.
Haga clic en Mandar en el grupo Modelo de datos. Aparece la vista de datos de Power Pivot.
Puede observar que las dos tablas de Excel que utilizó al crear la tabla dinámica se han convertido en tablas de datos en el modelo de datos.
Suponga que dos tablas, Vendedor y Ventas, están en dos libros diferentes.
Puede agregar una hoja de cálculo de Excel de otro libro al modelo de datos de la siguiente manera:
Haga clic en la tabla de ventas.
Haga clic en la pestaña INSERTAR.
Haga clic en Tabla dinámica en el grupo Tablas. EN Crear tabla dinámica Un cuadro de diálogo aparecerá.
En el cuadro Tabla / Rango, ingrese Ventas.
Haga clic en Nueva hoja de trabajo.
Seleccione la casilla de verificación Agregar estos datos al modelo de datos.
Haga clic en Aceptar.
Obtendrá una tabla dinámica en blanco en una nueva hoja que contiene solo los campos correspondientes a la tabla de ventas.
Ha agregado los datos de la tabla de ventas al modelo de datos. Entonces debería obtener los datos de la tabla de vendedores también en el modelo de datos como este:
Haga clic en la hoja de trabajo que contiene la tabla de ventas.
Haga clic en la pestaña DATOS en la cinta.
Haga clic en Conexiones existentes en el grupo Obtener datos externos. Aparece el cuadro de diálogo «Conexiones existentes».
Haga clic en la pestaña Tablas.
Debajo Este modelo de datos de libro de trabajo, 1 tabla mostrado (esta es la tabla de ventas que agregó anteriormente). También encontrará dos libros que muestran mesas.
Haga clic en Vendedor en Salesperson.xlsx.
Haga clic en Abrir. EN Datos de importacion Un cuadro de diálogo aparecerá.
Haga clic en Informe de tabla dinámica.
Haga clic en Nueva hoja de trabajo.
Ves que la caja esta Agregue estos datos al modelo de datos comprobado e inactivo. Haga clic en Aceptar.
Se creará la tabla dinámica.
Como puede ver, hay dos tablas en el modelo de datos. Es posible que deba crear una relación entre las dos tablas como en la sección anterior.
Otra forma de agregar tablas de Excel al modelo de datos: así que desde la cinta de PowerPivot…
Suponga que tiene dos hojas en su libro de trabajo:
Uno que contiene datos sobre los vendedores y las regiones que representan, en forma de tabla: Vendedor.
Otro que contiene datos de ventas por región y mes en la tabla es Ventas.
Puede agregar estas tablas de Excel al modelo de datos antes de realizar cualquier análisis.
Haga clic en Hoja de cálculo de Excel – Ventas.
Haga clic en la pestaña POWERPIVOT en la cinta.
Haga clic en Agregar al modelo de datos en el grupo Tablas.
Aparece la ventana de Power Pivot con la tabla de datos del vendedor agregada. A continuación, aparece la pestaña Tabla vinculada en la cinta de la ventana de Power Pivot.
Haga clic en la pestaña Tabla vinculada en la cinta.
Haga clic en Hoja de cálculo de Excel: vendedor.
Es posible que se muestren los nombres de las dos tablas presentes en su libro de trabajo y que el nombre del vendedor esté marcado. Esto significa que la tabla de datos del vendedor está vinculada a la tabla de Excel del vendedor.
Haga clic en Ir a la hoja de cálculo de Excel…
Aparece una ventana de Excel con una hoja que contiene la tabla del vendedor.
Haga clic en la pestaña Hoja de trabajo de ventas.
Haga clic en la tabla de ventas.
Haga clic en Agregar al modelo de datos en el grupo Tablas de la cinta.
La tabla Sales Excel también se ha agregado al modelo de datos.
Si desea hacer un análisis basado en estas dos tablas, como sabe, debe crear una relación entre las dos tablas de datos. Hay dos formas de hacer esto en Power Pivot:
Desde la vista de datos
Desde la vista de diagrama
Como sabe, en la Vista de datos, puede ver tablas de datos con registros como filas y campos como columnas.
Haga clic en la pestaña Diseño en la ventana de Power Pivot.
Haga clic en Crear relación en el grupo Relaciones. EN Crear relación Un cuadro de diálogo aparecerá.
Haga clic en Ventas en el campo Tabla. Esta es la mesa desde la que comienza la relación. Como sabe, la columna debe ser un campo que esté presente en la tabla Vendedor relacionada y que contenga valores únicos.
Haga clic en la región en el campo de la columna.
Haga clic en Vendedor en el campo Tabla vinculada relacionada.
La columna vinculada vinculada se llena automáticamente con la región.
Haga clic en el botón Crear. Se crea la relación.
Crear relaciones desde una vista de diagrama es relativamente más sencillo. Sigue las instrucciones de abajo.
Haga clic en la pestaña Inicio en la ventana de Power Pivot.
Haga clic en Ver diagrama en el grupo Ver.
La vista de diagrama del modelo de datos aparece en la ventana de Power Pivot.
Haga clic en la región en la tabla de ventas. La región está resaltada en la tabla de ventas.
Arrastre a la región en la tabla de vendedores. La región también se destaca en la tabla «Vendedor». Aparece una línea en la dirección que arrastró.
Aparece una fila de la tabla de ventas a la tabla de vendedores que indica la relación.
Como puede ver, se muestra una línea desde la tabla Ventas a la tabla Vendedor, indicando la relación y la dirección.
Si desea conocer un campo que forma parte de una relación, haga clic en la fila de la relación. La fila y el campo en ambas tablas están resaltados.
Puede editar o eliminar relaciones existentes en el modelo de datos.
Haga clic en la pestaña Diseño en la ventana de Power Pivot.
Haga clic en Administrar relaciones en el grupo Relaciones. Aparece el cuadro de diálogo «Gestión de relaciones».
Se muestran todas las relaciones que existen en el modelo de datos.
Haga clic en Relación.
Haga clic en el icono Editar botón. EN Cambiar relaciones Un cuadro de diálogo aparecerá.
Realice los cambios necesarios en la relación.
Haga clic en Aceptar. Los cambios se reflejan en la relación.
Haga clic en Relación.
Haga clic en el botón Eliminar. Aparece un mensaje de advertencia que muestra cómo las tablas afectadas por la eliminación del enlace afectarán a los informes.
Haga clic en Aceptar si está seguro de que desea desinstalar. El enlace seleccionado ha sido eliminado.
Digamos que cambió los datos en una hoja de cálculo de Excel. Puede agregar / modificar / eliminar datos en la tabla de Excel.
Para actualizar los datos de PowerPivot, siga estos pasos:
Haga clic en la pestaña Tabla vinculada en la ventana de Power Pivot.
Haga clic en Actualizar todo.
La tabla de datos se actualiza con los cambios realizados en la tabla de Excel.
Como puede ver, no puede modificar directamente los datos en las tablas de datos. Por lo tanto, es mejor almacenar datos en tablas de Excel vinculadas a tablas de datos cuando los agrega al modelo de datos. Esto facilita la actualización de los datos de las tablas de datos a medida que se actualizan los datos de las tablas de Excel.
🚫