El Secreto. Episodio 2
- Miguel Ferrer
- 15 jun 2017
- 6 Min. de lectura
3...2...1... ¡Acción!
Estimado lector bienvenido a la segunda toma de esta serie, en la cual hemos venido enseñando algunos tips y trucos (en realidad, estamos revelando nuestros secretos más oscuros :o) con las que buscamos hacer más eficientes la realización de reportes y en general la administración de información en las empresas. Si se usted se perdió la primera entrada de esta saga, donde lo introducimos al maravilloso mundo de Power Query lo invito a leerla acá.
Si usted es de los que se la pasa todo el día utilizando la función de Excel BUSCARV, déjeme ser bastante claro: tiene que leer esto. Por su parte, si usted no tienen ni idea qué es BUSCARV, ni con qué se come, pero, siempre ha necesitado cruzar información de dos tablas, bases de datos, hojas de cálculo, archivos, etc. (e.g. información de varios clientes como su edad en una tabla, con las ventas realizadas a estos mismo en otra) déjeme ser aún más ser claro: TIENE QUE LEER ESTO.
Hoy hablaremos de una herramienta más poderosa que Power Query, una herramienta que esta revolucionando el análisis de datos desde hace algún tiempo y que tiene unas capacidades bastante asombrosas. Pero vamos de menos a mas, déjenme presentarles otro de los Power de Excel, señores y señoras, con ustedes: Power Pivot.
De Menos a Más
Para empezar debemos activar/instalar el complemento:
Si usted tiene Excel 2010 debe descargar el complemento e instalarlo aquí.
Para Excel 2013, se debe tener la versión Standalone o una suscripción Office 365 ProPlus.
Para Excel 2016 se debe tener la versión Standalone o una suscripción Office 365 Pro o superior.
Para activar el complemento en Excel 2013-2016 vaya a Archivo > Opciones > Complementos. En el cuadro Administrar, haga clic en Complementos COM > Ir.
Y seleccione la casilla de Power Pivot > Aceptar
Y ya está, esto nos dejará con una nueva pestaña en la cinta de opciones
Calentando motores: algunas notas sobres Power Pivot.
Power Pivot se ha convertido en uno de los complementos más populares de Excel, tanto así que el mismo Microsoft ha evolucionado el mismo junto con los demás Powers (Query, Map, View) a un nuevo producto independiente que se conoce como Power BI. Pero esto nos deja una pregunta clave ¿por qué alejarme de Excel tradicional y comenzar a utilizar Power Pivot/Power BI? y la verdad es que estas nuevas herramientas agregan importantes características que complementan los más clásicos problemas de Excel:
Capacidad de datos: Power Pivot tiene un nuevo motor de procesamiento que permite manejar literalmente miles de millones de registros, a diferencia de Excel tradicional que solo llega al millón de datos.
Portabilidad: Si bien Excel tiene bastantes formulas, cuando calculamos una de ellas, como el total de ventas, este valor no esta disponible siempre que lo necesitemos, y peor aún, no podemos adaptar este resultado para ver el total de ventas por ejemplo por mes, sino que debemos crear un nuevo total para cada mes manualmente (¿a quien no se le ha olvidado fijar una celda haciendo esto?). Power Pivot soluciona este problema y nos permite calcular medidas que se pueden reutilizar y ajustar sin tener que recalcularlas.
Nuevas Formulas: Power Pivot hereda gran cantidad de fórmulas de Excel , pero viene con un arsenal nuevo más de 100 nuevas fórmulas que funcionan bastante similar a Excel y nos permiten hacer cosas aún mas poderosas que en el mismo.
Relacionamiento Instantáneo: En este post discutiremos este punto en detalle, sin embargo podemos decir por ahora, que si utilizamos las relaciones de tablas en Power Pivot no tendremos que volver a escribir un BUSCARV en nuestra vida y nos ahorraremos MUCHO tiempo.
Sin importar cuales sean los motivos para usar Power Pivot o Power BI, es importante recalcar que este complemento funciona bastante bien con Excel, y termina por convertirse en un aliado más que un enemigo, pues nos permite utilizar lo mejor de los dos mundos.
Los Datos
Empecemos... hoy cargaremos datos a Power Pivot y en futuros post haremos lo propio con Power Bi. Para este ejemplo trabajaremos con una base de datos del PIB de países descargada desde el Banco Mundial (haz click aquí para bajar los datos) y que previamente ha sido modelada como fue descrito en la serie anterior (haz click aquí para ver como modelar datos con Power Query)
Este archivo consta de dos tablas en la primera se encuentra el valor del PIB por año y por país (que está expresado con un código país).
En la otra una información cualitativa como el nombre completo del país, los códigos y una clasificación de cada uno en grupos según su ingreso y región.
Si se quisiera traer información de una tabla a otra (por ejemplo la región), cualquier conocedor de la función BUSCARV la aplicaría para conseguir el resultado deseado, modificando quizá un poco la estructura de la segunda tabla, y teniendo que repetir el proceso para cada característica que quisiera vincular, veamos porque esto es un despropósito.
Paso 1. Cargar Ambas Tablas a Power Pivot:
Parados sobre la tabla vamos a la pestaña de Power Pivot y hacemos click en Agregar Al Modelo de datos
Se nos abrirá la pestaña de PP y veremos algo así:
Como observamos hemos cargado nuestra primera tabla a Power Pivot con alrededor de 11.000 registros.
Hacemos lo mismo con los otros datos pero como no los tenemos en formato tabla nos preguntará:
Le damos mis datos tienen encabezado y ya está, tendremos nuestras dos tablas de en Power Pivot
Paso 2. Relacionar las tablas:
Luego de cargar nuestras tablas al motor de PP, es tiempo de relacionar la información, y para esto conviene hacer una precisión. Como vemos tenemos dos tablas una donde el nombre de los países es único y otra donde ser repite a lo largo de los años. La primera puede ser considerada una maestra de países y esta será la que nos permitirá relacionar la información con la tabla "transaccional" en donde cada año se estarán agregando las cifras del PIB. Dicho esto para crear la relación nos dirigimos en la parte superior derecha al campo Vista de Diagrama:
Una vez allí arrastraremos el campo Country Code de una tabla a la otra, luego veremos algo como esto:
Y así de simple nuestra tablas están relacionadas y listas para usar (Es recomendable no trabajar con nombre de tabla genéricos por lo que se han renombrado y esto se reflejará en la próxima imagen).
Paso 3. Explotar la información:
Ya hemos realizado la magia, pero aún no ha terminado el truco, solo nos falta demostrar la facilidad con la que podemos utilizar esta relación para construir una cantidad innumerable de reportes y por supuesto para reemplazar a nuestro ya memorable BUSCARV.
Vamos a la opción de tabla dinámica y hacemos click
Y nos aparecerá una tabla dinámica, tal cual la conocemos (en realidad, con una pequeña diferencia, ahora tenemos disponibles las dos tablas sobre la parte derecha y cada una tiene sus campos).
Utilizando los datos de las tablas podemos crear un reporte de esta forma:
¿Notan algo raro en la imagen?, al tener las tablas relacionadas no importa de donde se extraiga el nombre del país, pues el código está asociado al mismo nombre de país en ambas tablas. Pero, si aún no están totalmente convencidos de los beneficios déjenme mostrarles lo siguiente: utilizando los campos de la tabla Descriptiva crearé segmentaciones de datos que filtren los datos del reporte (que son principalmente de la tabla PIB)
En la imagen estamos viendo el producto interno bruto año a año de los países de Asía Oriental y El Pacífico que tiene ingreso mediano alto (¿interesante no?). Para este punto todos los lectores deberían estar convencidos de la utilidad de las relaciones de Power Pivot. Pero en caso de que exista algún escéptico que aún esté diciendo que no hemos logrado el mismo resultado, dejaré una imagen que muestra que puede conseguirse la estructura resultante de un BUSCARV y sólo requiere un par de configuraciones desde el diseño de la tabla dinámica (estructura totalmente inoficiosa si podemos explotar la información directamente):
En esta imagen se observa como hemos relacionado la tabla PIB con la tabla Descriptiva, en el mismo formato que resultaría de utilizar una formula (o dos) de BUSCARV , todo esto sin necesidad de utilizar ninguna formula y únicamente utilizando Power Pivot.
Conclusión
Power Pivot es una herramienta bastante simple de usar que permitirá ahorrar muchísimo tiempo en la construcción de reportes, por ejemplo en el caso explicado en este post, si algún dato llegase a cambiar en las tablas o se agregara el año 2017 a los datos, no habría que construir nuevamente las formulas para relacionar los datos, sino que simplemente con hacer click en la pestaña de Power Pivot y posteriormente en Actualizar Todo, la información estaría disponible al instante. Adicionalmente esta herramienta como se pudo ver, ofrece infinidad de opciones que permiten llevar los reportes realmente al siguiente nivel.
Si te pareció interesante este artículo déjanoslo saber en los comentarios y no olvides suscribirte a nuestro blog para ser el primero en recibir la información en tu email.
¡Queremos ayudarte a digerir y procesar la información de tu empresa para tomar decisiones con base a los datos! Mira Como
Comments