El ejemplo que desarrollaremos en esta ocasión nos dejará en claro uno de los más grandes beneficios de utilizar Power Pivot y me refiero a la facilidad con que se pueden crear relaciones entre los datos para generar reportes fácilmente.
Para dejar en claro las ventajas de Power Pivot resolveremos el mismo problema utilizando el método tradicional, es decir con fórmulas, y en la segunda parte utilizaremos Power Pivot para que poder comparar las diferencias respecto al primer método.
Planteamiento del problema
El problema que resolveremos es el siguiente. Pertenecemos a una compañía que se dedica a vender teléfonos móviles y nuestro jefe quiere un reporte de las unidades que se han vendido en las cuatro regiones de la ciudad: Norte, Sur, Este y Oeste.
El reporte deberá ser similar a lo que muestra la siguiente imagen donde se enlistan las regiones de la ciudad, los modelos de teléfonos móviles y las unidades vendidas:
No habrá ningún problema para generar dicho reporte porque tenemos disponible toda la información, pero tendremos que integrarla adecuadamente porque los datos están distribuidos en diferentes tablas de Excel.
La Tabla1 contiene la fecha de la venta, el nombre del representante de ventas, el código del producto y la cantidad de unidades vendidas.
Solo con la información de la Tabla1 no podríamos conocer la región en la que se ha realizado la venta así que es necesario integrar la columna Región de la Tabla2. En dicha tabla tenemos la región a la cual pertenece cada uno de los representantes de ventas y por lo tanto podremos asociarla correctamente a cada orden.
Además, la Tabla1 tiene el código de producto vendido pero necesitamos el nombre del producto, así que incluiremos los datos de la Tabla3 la cual tiene la equivalencia entre el código del producto y su descripción.
Para hacer más fácil la elaboración de nuestro ejemplo, las tablas están en el mismo archivo de Excel y están ubicadas en diferentes hojas. En un caso real, es probable que tuvieras que importar la información de otros sistemas o archivos antes de poder utilizarla.
Solución utilizando fórmulas
Nuestra primera tarea es integrar los datos de la Tabla2 y la Tabla3 en nuevas columnas de la Tabla1 y eso lo podemos lograr utilizando la función BUSCARV. Por ejemplo, para conocer la zona a la que pertenece un representante de ventas podemos utilizar la siguiente fórmula:
=BUSCARV([@Representante],Tabla2, 2, FALSO)
El valor buscado es el nombre del representante que está ubicado en la columna @Representante. Dicha búsqueda se hace sobre la Tabla2 y obtenemos la segunda columna la cual contiene la región a la que pertenece el representante de ventas. Al insertar una nueva columna en la Tabla1 con la fórmula anterior, obtenemos el siguiente resultado:
De manera similar, para obtener el nombre de cada producto, podemos utilizar una fórmula como la siguiente:
=BUSCARV([@CódigoProducto], Tabla3, 2, FALSO)
En este caso la búsqueda la hacemos sobre la Tabla3 que contiene la equivalencia entre los códigos y la descripción de cada producto. Al insertar una nueva columna en la Tabla1 y utilizar la fórmula anterior obtenemos el siguiente resultado:
Con los pasos anteriores tenemos todos los datos necesarios en la Tabla1 y podemos crear el reporte requerido y para eso crearé una Tabla dinámica desde la pestaña Insertar > Tabla dinámica.
En el cuadro de diálogo dejaré las opciones predeterminadas para crear la tabla dinámica en una nueva hoja de cálculo.
Una vez que se ha creado la tabla dinámica, en el panel de Campos de tabla dinámica arrastraré el campo Unidades al área de Valores. Y al área de Filas, arrastraré el campo Región y posteriormente el campo Descripción. La tabla dinámica quedará de la siguiente manera:
De esta manera hemos logrado el reporte requerido que enlista las unidades vendidas de cada producto en las diferentes regiones de la ciudad.
Solución utilizando Power Pivot
Ahora crearemos el mismo reporte pero utilizando Power Pivot donde la diferencia principal con el método anterior será que evitaremos la creación de fórmulas para integrar los datos. Utilizando Power Pivot, dejaremos las tablas de Excel intactas, y crearemos las relaciones dentro del modelo de datos.
Recuerda que es indispensable instalar o habilitar Power Pivot antes de hacer este ejercicio. Si no lo has hecho, vista este artículo donde podrás leer los requisitos para utilizar dicho complemento en Excel.
El primer paso es seleccionar cualquier celda de la Tabla1 y posteriormente pulsar el botón Power Pivot > Tablas > Agregar a modelo de datos.
Se abrirá la ventana de Power Pivot para Excel y tendrás una vista como la siguiente:
Podrás observar los mismos datos de la Tabla1 pero el hecho de que estén presentes en la ventana de Power Pivot indica que han sido agregados al Modelo de datos.
Considera que se ha abierto una nueva ventana para Power Pivot pero Excel no se ha cerrado así que, debes regresar a la ventana de Excel para continuar agregando el resto de las tablas al Modelo de datos.
Para agregar la Tabla2 y Tabla3 deberás repetir los mismos pasos: seleccionar una celda de la tabla y pulsar el botón Agregar a modelo de datos que se encuentra en la pestaña Power Pivot. Después de haber agregado las tres tablas tendrás una ventana de Power Pivot como la siguiente:
Quiero que pongas especial atención a la esquina inferior izquierda de la ventana donde se encuentran las pestañas para cada una de las tablas que hemos agregado al Modelo de datos. Desde ahí podrás ver los datos de cada una de las tablas.
El siguiente paso será crear las relaciones entre las columnas de las tablas que hemos importado al Modelo de datos. Para crear las relaciones de manera gráfica podemos activar la Vista diagrama utilizando el botón que se encuentra en el extremo derecho de la Cinta de opciones en la pestaña Inicio.
Esta vista nos permitirá visualizar cada una de las tablas así como las columnas que integran cada una de ellas. Nuestro objetivo es decirle a Power Pivot que la columna Representante de la Tabla2 es lo mismo que la columna Representante de la Tabla1.
Para crear esta relación, debes hacer clic en la columna Representante de la Tabla2 y arrastrar el puntero del ratón hasta que se resalte la columna Representante de la Tabla1 como se muestra en la siguiente imagen:
Lo mismo haré para la relación entre la Tabla3 y la Tabla1 haciendo clic en la columna CódigoProducto para indicarle a Power Pivot que dicha columna es la misma en ambas tablas.
En muchas ocasiones, Power Pivot es capaz de reconocer varias de las relaciones de las tablas basándose en el nombre y tipo de dato de las columnas, así que no te sorprenda si al activar por primera vez la Vista diagrama ya tengas alguna relación establecida.
Puedes validar que una relación se ha creado correctamente si haces clic derecho sobre la línea y seleccionas la opción Editar relación.
Esto mostrará el cuadro de dialogo Editar relación que indicará las tablas y columnas involucradas en la relación y podrás editarlas en caso de ser necesario.
Hemos terminado con la creación de nuestro Modelo de datos y sus relaciones. Lo que nos falta es crear la tabla dinámica para obtener el reporte que necesitamos. Para eso deberás pulsar el botón Tabla dinámica de la pestaña Inicio.
Se activará Excel y se mostrará un cuadro de diálogo preguntándonos la ubicación de la nueva tabla dinámica. Para nuestro ejemplo elegiré la opción Nueva hoja de cálculo:
A diferencia de las tablas dinámicas convencionales, esta tabla dinámica está basada en nuestro modelo de datos y por lo tanto podrás acceder a cada una de las tablas del modelo así como a sus columnas desde el panel de Campos de tabla dinámica.
Para crear el reporte, utilizaré los mismos campos que en el ejemplo anterior. Arrastraré el campo Tabla1.Unidades al área Valores y los campos Tabla2.Region y Tabla3.Descripción al área Filas. La tabla dinámica se verá de la siguiente manera:
De esta manera hemos terminado con el reporte. Aunque este ejemplo ha sido muy sencillo, podrías imaginarte el impacto positivo al tener reportes basados en decenas o cientos de tablas que tienen datos relacionados ya que Power Pivot nos ahorraría la creación de un sin número de columnas auxiliares y el uso de fórmulas de búsqueda para relacionar los datos.
Existen muchos beneficios de utilizar Power Pivot para analizar nuestra información y no existe más que la práctica, y el empeño que puedas poner al aprendizaje de esta herramienta, para familiarizarte pronto con el uso de este extraordinario complemento en Excel.
En el archivo de descarga encontrarás tres archivos. El primero de ellos tiene solamente las tablas de datos del ejemplo. El segundo archivo es la solución utilizando la técnica del primer ejemplo, es decir las columnas auxiliares y fórmulas. El tercer archivo contiene la solución utilizando Power Pivot.
Solución con Power BI Desktop
Si por alguna razón no has podido instalar Power Pivot en Excel, puedes utilizar Power BI Desktop que es una herramienta gratuita creada por Microsoft. Consulta el artículo Ejemplo práctico con Power BI Desktop para dar solución al mismo problema que hemos resuelto en este artículo pero utilizando Power BI Desktop.