Excel Power Pivot: gestión del modelo de datos
El uso principal de Power Pivot es su capacidad para administrar las tablas de datos y las relaciones entre ellas, para facilitar el análisis de los datos de varias tablas. Puede agregar una tabla de Excel al modelo de datos mientras crea una tabla dinámica o directamente desde la cinta de PowerPivot.
Puede analizar datos de varias tablas solo cuando existen relaciones entre ellas. Con Power Pivot, puede crear relaciones desde la Vista de datos o la Vista de diagrama. Además, si eligió agregar una tabla al Power Pivot, también debe agregar una relación.
Agregar tablas de Excel al modelo de datos con tabla dinámica
Cuando crea una tabla dinámica en Excel, se basa solo en una única tabla / rango. En caso de que desee agregar más tablas a la tabla dinámica, puede hacerlo con el modelo de datos.
Suponga que tiene dos hojas de trabajo en su libro de trabajo:
Uno que contiene los datos de los vendedores y las regiones que representan, en una tabla- Vendedor.
Otro que contiene los datos de ventas, región y mes, en una tabla - Ventas.
Puede resumir las ventas, en términos de vendedor, como se indica 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 los campos de la tabla de ventas: región, mes y monto del pedido. Como puede observar, hay unaMORE TABLES comando debajo de la lista Campos de tabla dinámica.
Haga clic en MÁS TABLAS.
los Create a New PivotTableaparece el cuadro de mensaje. El mensaje que se muestra es: para usar varias tablas en su análisis, se 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 Campos de tabla dinámica, puede observar que hay dos pestañas: ACTIVE y ALL.
Haga clic en la pestaña TODOS.
En la lista Campos de tabla dinámica aparecen 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 crea la tabla dinámica. Aparece un mensaje en los campos de la tabla dinámica:Relationships between tables may be needed.
Haga clic en el botón CREAR junto al mensaje. losCreate Relationship aparece el cuadro de diálogo.
Debajo Table, seleccione Ventas.
Debajo Column (Foreign) cuadro, seleccione Región.
Debajo Related Table, seleccione Vendedor.
Debajo Related Column (Primary) cuadro, seleccione Región.
Haga clic en Aceptar.
Su tabla dinámica de las dos tablas en dos hojas de trabajo está lista.
Además, como dijo Excel al agregar la segunda tabla a la tabla dinámica, la tabla dinámica se creó con el modelo de datos. Para verificar, haga lo siguiente:
Haga clic en la pestaña POWERPIVOT en la cinta.
Hacer clic Manageen el grupo Modelo de datos. Aparece la Vista de datos de Power Pivot.
Puede observar que las dos tablas de Excel que utilizó para crear la tabla dinámica se convierten en tablas de datos en el modelo de datos.
Agregar tablas de Excel de un libro de trabajo diferente al modelo de datos
Suponga que las dos tablas: Vendedor y Ventas están en dos libros de trabajo diferentes.
Puede agregar la tabla de Excel de un libro de trabajo diferente 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. losCreate PivotTable aparece el cuadro de diálogo.
En el cuadro Tabla / Rango, escriba Ventas.
Haga clic en Nueva hoja de trabajo.
Marque la casilla Agregar estos datos al modelo de datos.
Haga clic en Aceptar.
Obtendrá una tabla dinámica vacía en una nueva hoja de trabajo con solo los campos correspondientes a la tabla de ventas.
Ha agregado los datos de la tabla de ventas al modelo de datos. A continuación, debe obtener los datos de la tabla de vendedor también en el modelo de datos de la siguiente manera:
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 This Workbook Data Model, 1 tablese muestra (esta es la tabla de Ventas que agregó anteriormente). También encontrará los dos libros de trabajo que muestran las tablas en ellos.
Haga clic en Vendedor en Salesperson.xlsx.
Haga clic en Abrir. losImport Data aparece el cuadro de diálogo.
Haga clic en Informe de tabla dinámica.
Haga clic en Nueva hoja de trabajo.
Puedes ver que la caja ... Add this data to the Data Modelestá marcado e inactivo. Haga clic en Aceptar.
Se creará la tabla dinámica.
Como puede observar, las dos tablas están en el modelo de datos. Es posible que deba crear una relación entre las dos tablas como en la sección anterior.
Agregar tablas de Excel al modelo de datos desde la cinta de PowerPivot
Otra forma de agregar tablas de Excel al modelo de datos es hacer so from the PowerPivot Ribbon.
Suponga que tiene dos hojas de trabajo en su libro de trabajo:
Uno que contiene los datos de los vendedores y las regiones que representan, en una tabla - Vendedor.
Otro que contiene los datos de ventas, región y mes, en una tabla - Ventas.
Puede agregar estas tablas de Excel al modelo de datos primero, antes de realizar cualquier análisis.
Haga clic en la tabla 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 Salesperson agregada. Además, aparece una pestaña: Tabla vinculada en la cinta de opciones de la ventana de Power Pivot.
Haga clic en la pestaña Tabla vinculada en la cinta.
Haga clic en Tabla de Excel: Vendedor.
Puede encontrar que se muestran los nombres de las dos tablas presentes en su libro de trabajo y que el nombre Vendedor está marcado. Esto significa que la tabla de datos Vendedor está vinculada a la tabla de Excel Vendedor.
Hacer clic Go to Excel Table.
Aparece la ventana de Excel con la hoja de trabajo que contiene la tabla de vendedores.
Haga clic en la pestaña de la hoja de trabajo 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 de Excel Sales también se agrega al modelo de datos.
Si desea realizar un análisis basado en estas dos tablas, como sabe, debe crear una relación entre las dos tablas de datos. En Power Pivot, puede hacer esto de dos maneras:
Desde la vista de datos
Desde la vista de diagrama
Crear relaciones desde la vista de datos
Como sabe, en la Vista de datos, puede ver las 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. losCreate Relationship aparece el cuadro de diálogo.
Haga clic en Ventas en el cuadro Tabla. Esta es la tabla desde donde comienza la relación. Como sabe, Columna debe ser el campo que está presente en la tabla relacionada Vendedor que contiene valores únicos.
Haga clic en Región en el cuadro Columna.
Haga clic en Vendedor en el cuadro Tabla vinculada relacionada.
La columna vinculada relacionada se completa automáticamente con Región.
Haga clic en el botón Crear. Se crea la relación.
Crear relaciones desde la vista de diagrama
Crear relaciones desde la vista de diagrama es relativamente más fácil. Siga los pasos dados.
Haga clic en la pestaña Inicio en la ventana de Power Pivot.
Haga clic en Vista de diagrama en el grupo Vista.
La Vista de diagrama del modelo de datos aparece en la ventana de Power Pivot.
Haga clic en Región en la tabla Ventas. La región en la tabla de Ventas está resaltada.
Arrastre a Región en la tabla Vendedor. También se resalta la región en la tabla Vendedor. Aparece una línea en la dirección que arrastró.
Aparece una línea desde la tabla Ventas a la tabla Vendedor indicando la relación.
Como puede ver, aparece una línea desde la tabla Ventas a la tabla Vendedor, indicando la relación y la dirección.
Si desea conocer el campo que forma parte de una relación, haga clic en la línea de relación. La línea y el campo en ambas tablas están resaltados.
Gestión de relaciones
Puede editar o eliminar una relación existente 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 Administrar relaciones.
Se muestran todas las relaciones que existen en el modelo de datos.
Para editar una relación
Haga clic en una relación.
Haga clic en el Editbotón. losEdit Relationship aparece el cuadro de diálogo.
Realice los cambios necesarios en la relación.
Haga clic en Aceptar. Los cambios se reflejan en la relación.
Para eliminar una relación
Haga clic en una 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 de la relación afectarían a los informes.
Haga clic en Aceptar si está seguro de que desea eliminar. Se elimina la relación seleccionada.
Actualización de datos de Power Pivot
Suponga que modifica los datos en la tabla de Excel. Puede agregar / cambiar / eliminar los datos en la tabla de Excel.
Para actualizar los datos de PowerPivot, haga lo siguiente:
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 las modificaciones realizadas en la tabla de Excel.
Como puede observar, no puede modificar datos en las tablas de datos directamente. Por lo tanto, es mejor mantener sus datos en tablas de Excel que están vinculadas a las tablas de datos cuando las agrega al modelo de datos. Esto facilita la actualización de los datos en tablas de datos a medida que actualiza los datos en tablas de Excel.