Cómo combinar y analizar datos de múltiples conjuntos de datos utilizando Excel Power Pivot

Si tiene muchos datos y muchos análisis que hacer, pero poco tiempo o habilidad, necesita la función Power Pivot de Excel. He aquí cómo empezar a usarlo.

Excel Power Pivot es una función de análisis de datos que es fácil de usar, tiene una curva de aprendizaje corta y, lo más importante, es lo suficientemente flexible y versátil como para manejar toneladas de datos y necesidades personalizadas. Con un poco de conocimiento y planificación, puede convertir los datos en información significativa sin necesidad de recurrir a expertos desarrolladores de bases de datos. No reemplaza a la tecnología de bases de datos de ninguna manera, pero sí proporciona una herramienta poderosa para el usuario sin esos recursos. Además, es rápido: ¡obtener resultados rápidamente!

Si utiliza Excel 2010, debe descargar e instalar Power Pivot. Si está usando Excel 2013 o 2019, es parte del paquete! Estoy usando Excel 2019 (escritorio) en un sistema Windows 10 de 64 bits. Las instrucciones serán similares para las versiones anteriores. Para su comodidad, puede descargar el archivo de demostración.xlsx.

Más información sobre Office

Puede empezar leyendo Cómo usar el modelo de datos de Excel para convertir datos relacionados en información significativa. Este artículo proporciona una revisión básica de la característica guiándole a través del proceso de creación de una relación entre dos conjuntos de datos. En este artículo, avanzaremos creando relaciones múltiples y añadiendo una columna calculada.

VER: Microsoft Power BI: Introducción a la visualización de datos (PDF gratuito) (ConsejoTecnológico.com)

Los datos

Nuestro libro de trabajo de ejemplo tiene tres hojas de datos relacionados: Clientes, pedidos y detalles del pedido (copiado de ProductInventory.accdb, una base de datos de Access. Como puede ver en la , los conjuntos de datos están relacionados por campos comunes:

• Pedidos.cliente y clientes.empresa
• ID de pedido y detalles del pedido ID de pedido

Trabajaremos con dos relaciones para combinar los datos de tres tablas.

Cada registro en la hoja de Órdenes representa una orden. Cada pedido en esa hoja está relacionado con un cliente en la hoja Clientes y los detalles sobre ese pedido en la hoja Detalles del pedido. Ninguna de las hojas por sí sola da la imagen completa. Necesita los datos de las tres hojas para obtener una venta completa.

Lo que es obvio en este punto es que no se puede convertir los datos en información significativa, al menos, no fácilmente. Por ejemplo, viendo la hoja de Pedidos puede ver que el subtotal para el pedido 1 (antes de gastos de envío e impuestos) es de 477,20 euros. Lo que usted no sabe es cuántos productos componen el total o el precio unitario o la cantidad comprada para cada producto. Al ver la hoja Detalles del pedido, puede ver todos los artículos que componen cada venta, pero no sabe quién hizo la compra. La hoja Clientes almacena información sobre cada cliente, pero no hay historial de compras. Ahí es donde Power Pivot puede ayudar. Construirá relaciones entre los tres conjuntos de datos; con ellos, podrá analizar rápidamente los datos de manera significativa.

Como mencioné en Cómo usar el modelo de datos de Excel para convertir datos relacionados en información significativa, puede crear registros completos en una sola hoja utilizando fórmulas complejas para buscar datos. No sólo es un trabajo intensivo, sino que la ruta de la fórmula consume mucha memoria y ralentiza las cosas si se dispone de muchos datos. Afortunadamente, con Power Pivot, esa ruta es innecesaria.

Convertir a objetos de tabla

En lugar de conectar las hojas usando fórmulas, usaremos Power Pivot para crear relaciones entre las tablas. De esta manera, puede omitir las fórmulas por completo. Sin embargo, Power Pivot sólo reconoce objetos de tabla. Por lo tanto, el primer paso es convertir cada conjunto de datos en una Tabla y darle el siguiente nombre:

1. Haga clic en cualquier parte del conjunto de datos.
2. Haga clic en la ficha Insertar y, a continuación, en Tabla en el grupo Tablas.
3. En el diálogo resultante, marque o desmarque (según sea necesario) la opción Mi tabla tiene encabezados. Todos los conjuntos de datos de ejemplo tienen cabeceras.
4. Haga clic en Aceptar.
5. Haga clic en la pestaña Diseño contextual.
6. Haga clic dentro del control Nombre de tabla a la izquierda de la cinta, introduzca un nombre significativo para la tabla y pulse Intro. Nombrar la tabla, aunque no es necesario, será útil más adelante.

Cree tres objetos de Tabla: Clientes, Detalles de Pedidos y Pedidos. Cuando termines, estás listo para continuar.

VER: Aproveche el poder de la validación de datos en Excel (PDF gratuito) (ConsejoTecnologico.com)

Crear relaciones

Después de convertir los sets de datos en objetos Tabla, puede crear las relaciones de ordenación. Para hacerlo, haga clic en cualquier lugar dentro de una Tabla y luego haga clic en la ficha Power Pivot para abrir la ventana Power Pivot. Haga clic en Agregar a modelo de datos en el grupo Tablas. Al hacerlo, se genera otra vista de los datos. Los datos de Excel están seguros: no puede editarlos en la vista Power Pivot. Agregue los tres objetos de su Tabla a Power Pivot. La muestra la vista Clientes en Power Pivot: hay una pestaña para cada vista. (Power Pivot se refiere a los conjuntos de datos como vistas.

Añada los tres objetos Table a Power Pivot.

Ahora vamos a crear la primera relación. Específicamente, vamos a crear la relación entre Clientes y Pedidos:

1. En Power Pivot, haga clic en la pestaña Diseño (si es necesario. A continuación, haga clic en la pestaña Clientes (ver) (en la parte inferior.
2. Haga clic en Crear relación en el grupo Relaciones. Power Pivot muestra los campos de la vista y una muestra de los registros.
3. En el menú desplegable Tabla vacía (abajo), seleccione Órdenes ) y PivotTable mostrará sus campos y registros.
4. Ahora debe especificar qué campo comparten las dos vistas. En este caso, es Empresa en la tabla Clientes y Cliente en la tabla Pedidos. Seleccione ambos campos .
5. Haga clic en Aceptar.

Seleccione un objeto de Tabla.

Relacionar las dos vistas por un campo común.

Utilizando las instrucciones anteriores y la como guía, cree una relación entre los Pedidos y los Detalles del Pedido basada en los campos ID e ID del Pedido.

Construir una relación entre los Pedidos y los Detalles de los Pedidos.

En este punto, tenemos tres vistas basadas en tres objetos Table. Hay dos relaciones entre las tres

vistas, como puede ver en la . Para ver esto usted mismo, haga clic en la pestaña Inicio y luego en la opción Vista de Diagrama en el grupo Ver.

Ver las relaciones entre las vistas.

Ahora tiene una base sólida sobre la cual analizar rápidamente sus datos de ventas. Sin fórmulas, puede producir información significativa con poco esfuerzo.

Construir sobre los cimientos

Ahora pongamos a trabajar las relaciones entre las vistas de Power Pivot. Específicamente, vamos a crear una Tabla Pivotante que devuelve los costos de cada artículo por cliente y filtra esos resultados por los estados de los clientes y el personal interno que hizo la venta.

Antes de continuar, demos un paso atrás para revisar de dónde vienen todos esos datos:

• Añadiremos los campos Empresa y Estado/Provincia de la vista Clientes.
• Añadiremos el Producto y la Cantidad desde la vista Detalles del Pedido.
• Agregaremos el campo Empleado de la vista Órdenes.

La figura G muestra la lista de campos y la tabla pivotante resultante – los filtros están en las filas 1 y 2. Podemos filtrar por estado y empleado, pero hasta ahora,

no hay costo total para cada artículo.

La tabla pivotante resultante no proporciona los resultados completos que queremos.

La solución es un campo calculado, pero debe

añadirlo a la vista como se indica a continuación:

1. Haga clic en la ficha Power Pivot y, a continuación, en Administrar (en el grupo Modelo de datos.
2. Seleccione la pestaña de la vista Detalles del Pedido.
3. Seleccionar la primera celda en Agregar Columna

.

1. Para construir la fórmula enter =, haga clic en el campo Cantidad, ingrese *, haga clic en el campo Precio unitario ) y presione Enter.

Introduzca la fórmula para devolver el coste total de cada artículo comprado.

Con el campo calculado en su lugar, vuelva a la Tabla pivotante y añada el nuevo campo Total a Valores. La muestra los resultados añadiendo el formato Moneda a la nueva columna.

La tabla pivotante muestra ahora el precio de venta total de los artículos individuales por empresa.

Puede filtrar los resultados por los estados de los clientes y por el empleado interno responsable de la venta. Por ejemplo, la muestra el desglose por clientes en el estado de Washington – sólo la Compañía A. El segundo conjunto filtrado muestra sólo las compras en Washington atendidas por Laura Giussani. En su lugar, podría añadir fácilmente cortadoras de fiambres para filtrar la PivotTable.

Puede utilizar los filtros para centrarse en categorías específicas.

Esta no es la única manera de lograr estos resultados, pero ahora que las relaciones están en su lugar, usted puede obtener rápidamente información de las tres vistas en información significativa y añadir campos calculados para aumentar el valor! Puede pensar en Power Pivot como un motor de consulta que crea una relación entre conjuntos de datos relacionados basados en un campo común.

Es una simplificación excesiva, pero es una buena descripción.

Boletín semanal de Microsoft

Conviértase en un experto en Microsoft de su empresa con la ayuda de estos tutoriales de Windows y Office y de los análisis de nuestros expertos sobre los productos empresariales de Microsoft. Entregado Lunes y Miércoles

Envíeme su pregunta sobre Office

Respondo a las preguntas de los lectores cuando puedo, pero no hay garantía. No envíe archivos a menos que se le solicite; las solicitudes iniciales de ayuda que lleguen con archivos adjuntos se eliminarán sin leer. Puede enviar capturas de pantalla de sus datos para ayudar a aclarar su pregunta. Cuando se ponga en contacto conmigo, sea lo más específico posible. Por ejemplo, «Please troubleshoot my workbook and fix what’s wrong» probablemente no obtendrá respuesta, pero «Can you tell me why this formula is’ t returning the expected results? Por favor, menciona la aplicación y la versión que estás usando. ConsejoTecnologico.com no me reembolsa por mi tiempo o experiencia en ayudar a los lectores, ni tampoco pido una cuota a los lectores a los que ayudo. Puede ponerse en contacto conmigo en susansalesharkins@gmail.com

Ver también: