Invitado | Relacionar múltiples Tablas en Power Pivot para resumir datos en Excel

Relacionar Múltiples Tablas

Primero, debemos mencionar que Power Pivot es el elemento base del paquete de soluciones que ha desarrollado Microsoft para hacer inteligencia de negocios, debido a que nos brinda la posibilidad de Integrar cantidades masivas de datos, que pueden estar alojadas en diferentes gestores de bases de datos como por ejemplo Access o SQL Server, para luego, a través de la construcción de expresiones creadas en el lenguaje DAX (Algo “similar” a las funciones de Excel), poder extraer conocimiento oportuno, accionable y aplicado a la toma de decisiones efectiva.

Cabe mencionar que Power Pivot es una herramienta relativamente nueva, debido a que salió en la versión de Excel del año 2010 como un complemento, sin embargo, existen muchos usuarios que desconocen su potencial y la cantidad de posibilidades para hacer inteligencia de negocios.

Recomendado: El ADN de Power Pivot. Sé un experto en análisis de información en Excel.

Pero dejemos las palabras de lado y veamos más de cerca, algunas de las funcionalidades que tiene Power Pivot a través del siguiente ejemplo.

Ver también: Tablas Dinámicas, La Quinta Dimensión.

Ejemplo – Análisis de Ventas

Imagina que trabajas en una tienda que distribuye teléfonos móviles a diferentes almacenes de cadena alrededor de sur américa, y se te ha solicitado crear un reporte de tabla dinámica para analizar los ingresos obtenidos, dado el nombre del producto y el nombre del cliente.

Para realizar dicho reporte contamos con tres tablas de datos, alojadas en diferentes hojas de un mismo libro, a continuación puedes ver cada tabla

• Registro de Ventas: Contiene toda la información referente a las ventas producidas desde el año 2005 hasta el año 2015.

• Información de Cliente: Contiene información detallada de todos los clientes de la tienda.

• Información de Producto: Contiene información asociada a cada producto de la tienda.

Como podemos observar, en la tabla Registro de Ventas no se encuentran el nombre del producto ni de cliente en cada transacción, sin embargo, en las otras dos tablas SI contamos con esta información. Para crear el reporte de tabla dinámica que discrimine los ingresos tanto por nombre de Cliente como por nombre de Producto, tendríamos que crear una Tabla Unificada, adicionando columnas auxiliares a la tabla registro de ventas, donde utilizaríamos la función BUSCARV para asignar el nombre del producto y nombre del cliente según los ID respectivos.

Este es el método clásico y aquí no vamos a entrar en detalle, por el contrario, vamos a utilizar el magnífico poder de Power Pivot para relacionar tablas solo con unos cuantos clics.

Activar Complemento

Para crear la relación entre tablas, primero debemos estar seguros que el complemento Power Pivot ha sido habilitado, de ser así, podremos ver la pestaña Power Pivot en la hoja de Excel, en caso contrario puedes activarlo de la siguiente manera:

1. Clic izquierdo en la pestaña Archivo.

2. En la lista de opciones que se despliega, elegimos Opciones.

3. En el cuadro de dialogo Opciones de Excel que aparece automáticamente, elegimos la sección Complementos.

4. En la lista desplegable Administrar, seleccionamos la opción Complementos COM y presionamos el botón Ir …

5. En el cuadro de dialogo Complementos COM, que aparece automáticamente, debemos habilitar la opción Microsoft Office Power Pivot for Excel 2013 y presionamos el botón Aceptar.

6. De manera inmediata debe visualizarse la Pestaña Power Pivot en la hoja de Excel, de no ser así, puedes reiniciar el programa y la pestaña aparecerá.

Nota: Debes tener en cuenta si tu versión de office incluye Power Pivot. A continuación se listan las versiones de Office 2016 que incluyen este complemento.

• Office 365 Enterprise E2
• Office 365 Enterprise E3
• Office Professional Plus
• Office Standalone

Ahora, vamos a agregar al Modelo de Datos/Power Pivot todas las tablas que tenemos a nuestra disposición en el libro, sin embargo, debemos tener en cuenta que es recomendable que la lista o rango de datos que se va a agregar al modelo de datos, sea configurado como una Tabla Estructurada.

Para convertir un rango de datos en una tabla estructurada:

1. Debemos seleccionar el Rango de Datos, el cual vamos a transformar en una tabla estructurada de Excel.

2. Presionamos la combinación de Teclas CTRL + T.

3. En el cuadro de dialogo Crear Tabla, validamos que el rango de datos sea el apropiado, y habilitamos la opción La tabla tiene encabezados de ser cierto, y para finalizar damos clic en el botón Aceptar.

De esta manera convertimos una lista de datos en una tabla estructurada de Excel, pero acá no para el asunto, luego, debemos modificar el Nombre de la Tabla Estructurada, porque al agregar las tablas al modelo de datos, mantendrán el nombre por defecto que ha asignado Excel.

Para Modificar el Nombre de una tabla estructurada:

1. Situamos la celda activa sobre la tabla estructurada.
2. Nos dirigimos al grupo Propiedades, de la pestaña Diseño, una vez allí, podremos reescribir el nombre de la tabla, para finalizar presionamos la tecla Enter.

Nota: Debes repetir el procedimiento para cada tabla estructurada que desees agregar al modelo de datos.

Agregar Tabla al Modelo de Datos

Con las tablas ya creadas, vamos a agregarlas al modelo de datos una a una:

1. Situamos la celda activa sobre la tabla estructurada RegistroVentas.

2. Nos dirigimos a la pestaña Power Pivot, grupo Tablas y damos clic sobre el comando Agregar al Modelo de Datos.

3. Al esperar unos segundos, se carga la propia ventana de Power Pivot con la tabla en una Hoja individual.

4. Ahora, Debemos repetir el procedimiento para las demás tablas.

Con las tablas agregadas al modelo de datos, ha llegado la hora de la verdad, puesto que ya podemos relacionarlas.

Relacionar Tablas en Power Pivot

1. En la ventana de Power Pivot, vamos a la pestaña Inicio, Grupo Ver y damos clic sobre el comando Vista de Diagrama.

En la vista de diagrama se muestra una representación de las tablas que han sido agregadas al modelo de datos, la columna ID Cliente en la tabla ClientesInfo contiene los elementos que son únicos, y se le denomina, Clave Principal, por otra parte, la columna ID de Cliente en la tabla RegistroVentas contiene los elementos repetidos, y es conocida como Clave Externa, teniendo esto en cuenta, vamos a crear una relación entre tablas de tipo Uno a Muchos y consiste en relacionar dos tablas mediante una columna equivalente, es decir, relacionar las columnas que coinciden en ambas tablas (ID Cliente), pero en una tabla, los elementos de la columna están listados una sola vez, como el campo ID Cliente de la tabla ClientesInfo.

2. Teniendo en mente el tipo de relación que vamos a crear, situamos con el cursor sobre el campo ID Cliente de la tabla ClientesInfo, pulsamos clic izquierdo y manteniéndolo presionado llevamos la línea que aparece a el campo equivalente en la tabla RegistroVentas.

3. Debemos repetir el mismo procedimiento para el campo ID Producto.

Y Aunque no lo creas, eso es todo lo que debes hacer para relacionas tablas con Power Pivot, un procedimiento que resulta bastante sencillo, elegante y sobre todo productivo, si pensamos en todas las “acrobacias” que debemos hacer para relacionar tablas de otra manera. Ahora, el paso siguiente consiste en crear una tabla dinámica con los campos de las diferentes tablas.

Creación de la Tabla Dinámica

1. En la ventana de Power Pivot, nos dirigimos a la pestaña Inicio y damos Clic sobre el comando Tabla Dinámica.

2. En el cuadro de dialogo Crear Tabla dinámica que aparece, elegimos la ubicación de la misma, para este caso una Nueva Hoja de Cálculo y presionamos Aceptar.

3. Y en la nueva hoja aparece el área para crear la tabla dinámica.

4. Arrastramos los campos de las diferentes tablas a las áreas de colocación de la tabla dinámica: En el área de valores vamos a agregar el campo Ingresos ($) de la Tabla RegistroVentas, al área de filas, el campo Nombre de Producto de la tabla ProductosInfo y por ultimo, al área de columnas vamos a agregar el campo Nombre de Cliente de la tabla ClientesInfo.

Nota: Puedes ver en el panel de campos, como aparecen los campos de cada una de las tablas agregadas a Power Pivot, preparados para ser utilizados en cualquier área de colocación de la tabla dinámica, no te parece genial la posibilidad de crear diferentes perspectivas en el reporte para analizar los ingresos de la tienda.

¡Y automáticamente aparece la tabla dinámica!

Como puedes ver, la integración y manipulación de varias bases de datos es posible con un par de clics, aunque esto es solo una pequeña parte de lo que podamos lograr con Power Pivot, la verdad es que con esta herramienta las posibilidades son infinitas.

Recursos Relacionados con Power Pivot.

El ADN de Power Pivot: El ADN DE POWER PIVOT, es una completa guía paso a paso sobre Power Pivot, Construcción de Modelos de Datos y el Lenguaje DAX, desde sus fundamentos esenciales hasta sus aspectos más avanzados y complejos, cargado de ejemplos, aplicaciones, ilustraciones, ayudas visuales y una lectura amigable para dominar el arte de transformar datos en conocimiento útil enfocado a la toma de decisiones.

Más Información: El ADN de Power Pivot. Sé un experto en análisis de información en Excel.

Ver también: Tablas Dinámicas, La Quinta Dimensión.

Bueno por esta ocasión es todo, espero que te sumerjas en el universo Power Pivot, puede aumentar tu productividad en un 1000%. Adicionalmente agradezco a Sergio por invitarnos a su maravillosa comunidad.

Autor invitado: Excel Free Blog.