Cómo utilizar Excel Power Pivot para análisis de datos y obtener información significativa

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 una gran cantidad de datos y necesidades personalizadas. Con un poco de conocimiento y planificación, puedes convertir datos en información significativa sin necesidad de recurrir a desarrolladores de bases de datos especializados. No reemplaza la tecnología de bases de datos de ninguna manera, pero proporciona una herramienta poderosa para el usuario sin esos recursos. Además, es rápido, ¡obtienes resultados rápidamente!

Si estás utilizando Excel 2010, debes descargar e instalar Power Pivot. ¡Si estás utilizando Excel 2013 o 2016, es parte del paquete! Estoy utilizando Excel 2016 (escritorio) en un sistema de Windows 10 de 64 bits. Las instrucciones serán similares para versiones anteriores. Para tu comodidad, puedes descargar el archivo de demostración .xlsx.

Tal vez quieras 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 función al guiarte a través del proceso de creación de una relación entre dos conjuntos de datos. En este artículo, avanzaremos creando múltiples relaciones y añadiendo una columna calculada.

Los datos

Nuestro ejemplo de libro de trabajo tiene tres hojas de datos relacionadas: Clientes, Pedidos y Detalles del Pedido (copiadas de ProductInventory.accdb, una base de datos de Access). Como se puede ver en Figura A, los conjuntos de datos están relacionados por campos comunes:

• Pedidos.Cliente y Clientes.Empresa
• Pedidos.ID y Detalles del Pedido.ID del Pedido

Figura A

Trabajaremos con dos relaciones para combinar datos de tres tablas.

Cada registro en la hoja de Pedidos representa un pedido. Cada pedido en esa hoja está relacionado con un cliente en la hoja de Clientes y detalles sobre ese pedido en la hoja de Detalles del Pedido. Ninguna de las hojas por sí sola proporciona la imagen completa. Necesitas los datos de las tres hojas para unir todas las ventas en un todo completo.

Lo que es obvio en este punto es que no puedes convertir los datos en ninguna información significativa, al menos no fácilmente. Por ejemplo, al ver la hoja de Pedidos, puedes ver que el subtotal del pedido 1 (antes del envío y los impuestos) es de $477.20. Lo que no sabes es cuántos productos componen el total o el precio unitario o la cantidad comprada de cada producto. Al ver la hoja de Detalles del Pedido, puedes ver todos los artículos que componen cada venta, pero no sabes quién hizo la compra. La hoja de Clientes almacena información sobre cada cliente, pero no hay historial de compras. Ahí es donde Power Pivot puede ayudar. Construirás relaciones entre los tres conjuntos de datos; con ellas en su lugar, puedes analizar rápidamente los datos de formas significativas.

5 formas sencillas de eliminar filas en blanco en Excel

Como mencioné en Cómo usar el modelo de datos de Excel para convertir datos relacionados en información significativa, podrías crear registros completos en una única hoja utilizando fórmulas complejas para buscar datos. No solo es un trabajo intensivo, la ruta de las fórmulas consume mucha memoria y ralentiza las cosas si tienes muchos datos. Afortunadamente, con Power Pivot, esa ruta no es necesaria.

Convertir a objetos de tabla

En lugar de conectar las hojas usando fórmulas, utilizaremos Power Pivot para crear relaciones entre las tablas. De esta manera, puedes omitir las fórmulas por completo. Sin embargo, Power Pivot solo reconoce objetos de tabla. Entonces, tu primer paso es convertir cada conjunto de datos en una tabla y nombrarlo de la siguiente manera:

1. Haz clic en cualquier lugar dentro del conjunto de datos.
2. Haz clic en la pestaña Insertar y luego en Tabla en el grupo Tablas.
3. En el cuadro de diálogo resultante, marca o desmarca (según sea necesario) la opción Mi tabla tiene encabezados. Todos los conjuntos de datos de ejemplo tienen encabezados.
4. Haz clic en Aceptar.
5. Haz clic en la pestaña de Diseño contextual.
6. Haz clic dentro del control de Nombre de Tabla a la izquierda de la cinta y entra un nombre significativo para la Tabla y presiona Enter. Nombrar la Tabla, aunque no es necesario, será útil más adelante.

Crea tres objetos de tabla: Clientes, Detalles del Pedido y Pedidos. Cuando hayas terminado, estarás listo para continuar.

Crear relaciones

Después de convertir los conjuntos de datos en objetos de tabla, puedes crear las relaciones. Para hacerlo, haz clic en cualquier lugar dentro de una tabla y luego haz clic en la pestaña Power Pivot para abrir la ventana Power Pivot. Haz clic en Agregar a Modelo de Datos en el grupo Tablas. Al hacerlo, se genera otra vista de tus datos. Tus datos de Excel están seguros, no puedes editar los datos en la vista de Power Pivot. Agrega los tres objetos de tabla a Power Pivot. Figura B muestra la vista de Clientes en Power Pivot: hay una pestaña para cada vista. (Power Pivot se refiere a los conjuntos de datos como vistas).

Figura B

Agrega los tres objetos de tabla a Power Pivot.

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

1. Aún en Power Pivot, haz clic en la pestaña Diseño (si es necesario). Luego haz clic en la pestaña Clientes (vista) (en la parte inferior).
2. Haz clic en Crear relación en el grupo Relaciones. Power Pivot muestra los campos de la vista y una muestra de los registros.
3. Desde el menú desplegable de la tabla vacía (abajo), elige Pedidos (Figura C) y la tabla dinámica mostrará sus campos y registros.
4. Ahora tienes que especificar qué campo comparten las dos vistas. En este caso, es Empresa en la tabla Clientes y Cliente en la tabla Pedidos. Selecciona ambos campos (Figura D).
5. Haz clic en Aceptar.

Figura C

Elige un objeto de tabla.

Figura D

Relaciona las dos vistas mediante un campo común.

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

Cómo redirigir automáticamente las llamadas no deseadas a buzón de voz en Android 8.0

Figura E

Genera una relación entre Pedidos y Detalles del Pedido.

Figura F. Para ver esto por ti mismo, haz clic en la pestaña Inicio y luego haz clic en la opción Vista de Diagrama en el grupo Vista.

Figura F

Visualiza las relaciones entre las vistas.

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

Construye sobre la base

Ahora pongamos a trabajar las relaciones entre las vistas de Power Pivot. Específicamente, crearemos una tabla dinámica que muestra los costos de cada artículo por cliente y filtraremos esos resultados por los estados de los clientes y por el personal interno que realizó la venta.

Antes de continuar, hagamos una revisión para entender de dónde provienen todos esos datos:

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

Figura G muestra la lista de campos y la tabla dinámica resultante: los filtros se encuentran en las filas 1 y 2. Podemos filtrar por el estado y el empleado, pero aún no tenemos el costo total para cada artículo.

Figura G

La tabla dinámica resultante no proporciona los resultados completos que queremos.

La solución es un campo calculado, pero debes añadirlo a la vista de la siguiente manera:

3 formas sencillas de utilizar datos presupuestarios en Excel para obtener información significativa

1. Haz clic en la pestaña Power Pivot y luego en Administrar (en el grupo Modelo de Datos).
2. Selecciona la pestaña Ordenes vista.
3. Selecciona la primera celda en Agregar columna.
4. Para construir la fórmula, ingresa =, haz clic en el campo Cantidad, ingresa *, haz clic en el campo Precio Unitario (Figura H) y presiona Enter.
5. Cambia el nombre de la columna a Total (esto no es estrictamente necesario, pero los nombres significativos son más fáciles de trabajar).

Figura H

Ingresa la fórmula para obtener el costo total de cada artículo comprado.

Con el campo calculado en su lugar, regresa a la tabla dinámica y agrega el nuevo campo Total a los Valores. Figura I muestra los resultados agregando el formato Moneda a la nueva columna.

Figura I

La tabla dinámica ahora muestra el precio total de venta para cada artículo por empresa.

Puedes filtrar los resultados por los estados de los clientes y por el empleado interno responsable de la venta. Por ejemplo, Figura J muestra el desglose por clientes en el estado de Washington, solo la Empresa A. El segundo conjunto filtrado muestra solo las compras en Washington atendidas por Laura Giussani. Podrías añadir fácilmente segmentadores para filtrar la tabla dinámica en su lugar.

Figura J

Puedes usar los filtros para enfocarte en categorías específicas.

Esta no es la única forma de lograr estos resultados, pero ahora que las relaciones están en su lugar, puedes rápidamente recopilar información de las tres vistas en información significativa y añadir campos calculados para aumentar su valor. Puedes 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. Esa es una simplificación exagerada, pero es una buena descripción.

Envíame tu pregunta sobre Office

Respondo las preguntas de los lectores cuando puedo, pero no hay garantía. No envíes archivos a menos que se solicite; las solicitudes iniciales de ayuda que lleguen con archivos adjuntos se eliminarán sin leer. Puedes enviarme capturas de pantalla de tus datos para ayudar a aclarar tu pregunta. Al contactarme, sé lo más específico posible. Por ejemplo, "Por favor, soluciona mi libro de trabajo y arregla lo que está mal" probablemente no recibirá una respuesta, pero "¿Puedes decirme por qué esta fórmula no está devolviendo los resultados esperados?" podría sí recibirla. Por favor, menciona la aplicación y la versión que estás utilizando. No soy recompensada por Newsmatic por mi tiempo o conocimientos al ayudar a los lectores, ni les pido una tarifa a los lectores que ayudo. Puedes contactarme en [email protected]

También puedes ver:

• Cómo numerar encabezados en un documento Word 2016 (Newsmatic)
• Preguntas y respuestas de Office: Cómo evitar que la función pegar de Excel sobrescriba el formato de la celda de destino (Newsmatic)
• 5 formas de insertar una marca de verificación en documentos de Office (Newsmatic)
• Cómo controlar el espaciado y la alineación en una lista numerada en Microsoft Word (Newsmatic)
• Cómo usar el modelo de datos de Excel para convertir datos relacionados en información significativa (Newsmatic)

Cómo eliminar masivamente correos electrónicos de Gmail

En Newsmatic nos especializamos en tecnología de vanguardia, contamos con los artículos mas novedosos sobre Software, allí encontraras muchos artículos similares a Cómo utilizar Excel Power Pivot para análisis de datos y obtener información significativa , tenemos lo ultimo en tecnología 2023.