Tutorial de Power Pivot para Excel: Los Casos y Ejemplos más Usados

Todo analista financiero es un genio en Excel. Sin embargo, al hacerse más económico el almacenamiento, nuestras organizaciones van acumulando más data. Y al tener más de esta data se hace más complicado trabajar con Excel—o llegamos al límite de fila 1,048,576, o el documento disminuye la velocidad tratando de procesar todo.

A menudo, la decisión se encuentra entre perder detalles organizados cuidadosamente, o trabajar en un libro de trabajo tediosamente lento. A veces, tenemos que ser creativos para poder combinar dos grandes sets de data. Recurrimos a usar fórmulas complejas y esperamos una eternidad para que los cálculos sean resueltos.

Afortunadamente, ya no tienes que tomar estas decisiones. La funcionalidad de Power Pivot de Excel provee una manera de extraer, combinar y analizar grandes sets de data. A pesar del hecho de que fue lanzado con Excel en el año 2010, la mayoría de los analistas financieros que conozco todavía no saben cómo usar Power Pívot, y muchos ni siquiera saben que existe.

En este artículo, te voy a enseñar cómo usar Power Pívot para sobrellevar algunos problemas comunes de Excel y echaremos un vistazo a las ventajas adicionales clave del software usando algunos ejemplos. Este tutorial de Power Pívot para Excel tiene como finalidad servir de guía para lo que puedes conseguir con esta herramienta y, al final, voy a explorar algunos ejemplos de casos de uso dónde Power Pívot puede ser invaluable.

¿Qué es Power Pivot y Por qué es Útil?

Power Pivot es una característica de Microsoft Excel que fue introducida como un agregado de Excel 2010 y 2013 y es, ahora, una característica nativa para Excel 2016. Como lo explica Microsoft, Power Pivot para Excel “te permite importar millones de filas de data desde múltiples fuentes de data a un solo libro de trabajo de Excel, crear relaciones entre data heterogénea, crear columnas calculadas y medir usando fórmulas, construir PivotTables y PivotCharts, y luego analizar a profundidad la data para que así puedas tomar decisiones de negocios oportunas sin la asistencia del equipo IT.”

La expresión de lenguaje principal usada por Microsoft en Power Pivot es DAX (Data Analysis Expressions), aunque otros también pueden usarse en situaciones específicas. De nuevo, como lo explica Microsoft, “DAX es una colección de funciones, operadores y constantes que se pueden usar en una fórmula, o expresión, para calcular y regresar uno o más valores. Puesto de manera más sencilla, DAX te ayuda a crear nueva información desde la data que ya se encuentra en tu modelo.” Afortunadamente, para aquellos ya familiarizados con Excel, las fórmulas DAX les parecerán conocidas, ya que muchas de las fórmulas tienen sintaxis similar (ej., SUM, AVERAGE, TRUNC).

Para aclarar, los beneficios clave de usar Power Pivot vs. Excel básico se puede resumir así:

• Te permite importar y manipular cientos de millones de filas de data donde Excel tiene una fuerte constricción de más de un millón de filas.
• Te permite importar data desde múltiples fuentes a un libro de trabajo de fuente único, sin tener que crear múltiples hojas de fuente que sufren de control de versión y problemas de transferibilidad.
• Te permite manipular la data importada, analizarla y sacar conclusiones sin bajar la velocidad de tu computadora a la velocidad de un caracol.
• Te permite visualizar la data con PivotCharts y Power BI.

En las siguientes secciones, pasaré por cada uno de los beneficios nombrados y te mostraré cómo Power Pivot para Excel te puede ayudar.

1) Importar Grandes Sets De Data

Como ya lo discutimos, una de las limitaciones más grandes de Excel tiene que ver con el trabajo con sets de data extremadamente grandes. Afortunadamente para nosotros, Excel puede subir más del límite de la fila de un millón, directamente a Power Pivot.

Para demostrar esto generé una muestra de un set de data de un valor de ventas de dos años, esto para un vendedor de productos de deporte, con nueve categorías de productos diferentes y cuatro regiones. El set de data resultante es de dos millones de filas.

Usando la tabulación Data en la banda, cree una Nueva Pregunta del archivo CSV (Muestra 1). Esta funcionalidad usada antes se llamaba PowerQuery, pero desde Excel 2016, se ha integrado mucho más en la tabulación de Data de Excel.

Muestra 1: Creando una Nueva Prueba

¡Pasar de un libro de trabajo en blanco de Excel a subir las dos millones de filas a Power Pivot tomó más o menos un minuto! Nota que pude formatear un poco de data ligera al ascender la primera fila para que se convirtieran en los nombres de las columnas. En los últimos años, la funcionalidad de Power Query ha mejorado enormemente, pasó de un agregado de Excel a parte integrada de la tabulación de Data en la barra de herramientas. Power Query puede girar, aplastar, limpiar y dar forma a tu data a través de una variedad de opciones y su propio idioma, M.

2) Importar Data desde Múltiples Fuentes

Uno de los beneficios clave de Power Pivot para Excel, es la habilidad de importar data fácilmente de múltiples fuentes. Previamente, muchos de nosotros creábamos múltiples hojas de trabajo para nuestras distintas fuentes de data. A menudo, este proceso involucraba escribir código VBA y copiar y pegar de estas fuentes dispares. Afortunadamente para nosotros, Power Pívot te permite importar data desde distintas fuentes de data directamente a Excel sin tener que encontrarse con los problemas mencionados anteriormente.

Al usar la función Query en la Muestra 1, podemos extraer desde cualquiera de las siguientes fuentes:

• Microsoft Azure
• SQL Server
• Teradata
• Facebook
• Salesforce
• Archivos JSON
• Libros de trabajo de Excel
• …y muchos más

Más aún, múltiples fuentes de data pueden ser combinadas en la función Query o en la ventana Power Pivot, para integrar data. Por ejemplo, puedes extraer data de costo de producción de un libro de trabajo de Excel y resultados de ventas verdaderos del servidor SQL a través de Query hacia Power Pivot. A partir de ahí, puedes combinar los dos sets de data al combinar números de un lote de producción con márgenes brutos por unidad.

3) Trabajar con Grandes Sets de Data

Otra ventaja clave de Power Pivot para Excel es la habilidad de manipular y trabajar con grandes sets de data para alcanzar conclusiones y análisis relevantes. Pasaré por algunos ejemplos comunes, para darte una apreciación del poder de la herramienta.

Medidas

Los adictos a Excel estarán completamente de acuerdo con que PivotTables son unas de las tareas más útiles pero al mismo tiempo frustrantes de realizar. Particularmente frustrantes cuando se trata de trabajar con grandes sets de data. Afortunadamente, Power Pivot para Excel nos permite crear, rápida y fácilmente, PivotTables cuando se trabaja sets de data más grandes.

En la imagen de abajo (Muestra 2), nota como la ventana de Power Pivot está separada en dos cristales. El cristal de arriba tiene la data y el de abajo contiene las medidas. Una medida es un cálculo que se realiza en todo el set de data. He ingresado una medida al ingresar la celda resaltada.

Total Sales:=SUM('Accounting Data'[Amount])

Esto crea una nueva medida que suma a través de la columna de Cantidad. Similarmente, puedo ingresar otra medida en la celda de abajo.

Average Sales:=AVERAGE('Accounting Data'[Amount])

Muestra 2: Creando Medidas

Desde ahí, observa lo rápido que es crear una PivotTable familiar en un gran set de data.

Muestra 3: Creando una PivotTable

Tablas de Dimensión

Como analistas financieros que usan Excel, nos convertimos adeptos al usar fórmulas complicadas para doblegar la tecnología a nuestra voluntad. Dominamos VLOOKUP, SUMIF, y hasta el temido INDEX(MATCH()). Sin embargo, al usar Power Pivot, podemos deshacernos de mucho de esto.

Muestra 4: Agregar una Tabla Creada por el Usuario a un Modelo Power Pivot

Para demostrar esta funcionalidad, creé una pequeña tabla de referencia en la que asigné cada Categoría a un Tipo. Al escoger “Agregar a Modelo de Data”, esta tabla es cargada a Power Pivot (Muestra 4 arriba).

También creé una tabla de fecha para ser usada con nuestro set de data (Muestra 5). Power Pivot para Excel hace más fácil crear una tabla de fecha rápidamente, para así consolidar por meses, trimestres y días de la semana. El usuario puede también crear una tabla de fecha más a la medida para analizar por semanas, años fiscales, o cualquier agrupamiento específico de la organización.

Muestra 5: Crear una Tabla de Fecha

Columnas de Cálculo

Aparte de las medidas, hay otro tipo de cálculo: las columnas de cálculo. Los usuarios de Excel se sentirán cómodos escribiendo estas fórmulas, ya que son muy similares a escribir fórmulas en tablas de fecha. Creé una nueva columna de cálculo abajo (Muestra 6) la cual organiza la tabla de Data de Contabilidad por Cantidad. Las ventas por debajo de 50 dólares se marcan como “Pequeñas” y el resto se marcan como “Grandes”. ¿No se siente intuitiva la fórmula?

Muestra 6: Creando una Columna de Cálculo

Relaciones

Luego, podemos crear una relación entre el campo de la Categoría de la tabla de Data de Contabilidad y el campo de Categoría de la tabla de Categoría, usando la Vista de Diagrama. Adicionalmente, podemos definir una relación entre el campo Fecha de Ventas de la tabla de Data de Contabilidad y el campo de Fecha de la tabla de Calendario.

Muestra 7: Definiendo Relaciones

Ahora, sin necesidad de ninguna función SUMIF o VLOOKUP, podemos crear una PivotTable que calculó Ventas Totales por año y tipo, con un cortador para el Tamaño de la Transacción.

Muestra 8: PivotTable Usando Relaciones

O, podemos crear una gráfica de Ventas Regulares para cada día de la semana usando la nueva tabla de Calendario.

Muestra 9: PivotChart Usando Relaciones

Mientras esta gráfica parece simple, es impresionante que haya tomado menos de 10 segundos crear una consolidación de más de dos millones de filas de data, sin agregar una nueva columna a la data de ventas.

Mientras podemos llevar a cabo estos escenarios de reportaje consolidados, también podemos adentrarnos en los ítems de línea individuales. Retenemos nuestra data altamente granular.

Funciones Avanzadas

Hasta ahora, la mayoría de los análisis que he mostrado son, relativamente, cálculos directos. Ahora, quiero demostrar algunas de las capacidades más avanzadas de esta plataforma.

Inteligencia de Tiempo

A menudo, cuando examinamos los resultados financieros, queremos compararlos a un periodo de tiempo comparable del año anterior. Power Pivot tiene unas funciones de inteligencia de tiempo integradas.

Same Period Last Year Sales:=CALCULATE([Total Sales],SAMEPERIODLASTYEAR('Calendar'[Date]))
 
YOY Sales Growth:=if(not(ISBLANK([Same Period Last Year Sales])),([Total Sales]/[Same Period Last Year Sales])-1,BLANK())

Por ejemplo, agregar solo dos medidas sobre la tabla de Data de Contabilidad en Power Pivot me permite producir la siguiente PivotTable en solo un par de clics.

Muestra 10: Inteligencia de Tiempo PivotTable

Granularidades Disparejas

Como analista financiero, un problema que tengo que resolver a menudo es las granularidades disparejas. En nuestro ejemplo, la data exacta de ventas se muestra en el nivel de categoría, pero preparemos un presupuesto que está solo en un nivel de temporada. Para extender esta disparidad, vamos a preparar un presupuesto trimestral, aunque la data de ventas sea diaria.

Muestra 11: Granularidades - Tabla de Presupuesto

Con Power Pivot para Excel, esta inconsistencia se resuelve fácilmente. Al crear dos tablas de referencia adicionales, o tablas de dimensión en nomenclatura de base de datos, podemos crear las relaciones apropiadas para analizar nuestras ventas verdaderas en comparación con las cantidades presupuestadas.

Muestra 12: Granularidades - Relaciones Disparejas

En Excel, la siguiente PivotTable está lista rápidamente.

Muestra 13: Granularidades - Presupuesto Disparejos vs. Resultados Verdaderos

Además, podemos definir nuevas medidas que calculan la divergencia entre ventas verdaderas y ventas presupuestadas, como se muestra abajo:

Actual-to-Budget Variance:=if(ISBLANK([Total Budgeted Sales]),BLANK(),[Total Sales]/[Total Budgeted Sales]-1)

Al usar esta medida, podemos mostrar la divergencia en una PivotTable.

Muestra 14: Granularidad es Disparejas - Resultados Divergentes

Porcentaje Total

Finalmente, examinemos las ventas en una categoría en particular como porcentaje de todas las ventas (ej., contribución de categoría a ventas generales), y ventas en una categoría en particular como porcentaje de todas las ventas de un mismo tipo (ej., contribución de categoría a ventas por temporadas). Creé las dos medidas que se muestran abajo:

Total Sales as Percent of All Sales:=[Total Sales]/CALCULATE([Total Sales],ALL('Accounting Data'))
Total Sales as Percent of Type:=[Total Sales]/CALCULATE([Total Sales],ALL('Accounting Data'[Category]))

Esas medidas ahora se pueden implementar en una nueva PivotTable:

Muestra 15: Porcentaje Total

Nota como se realizan los cálculos en ambos tipos de niveles, el de categoría y el de tipo temporal. Me encanta como se realizan estos cálculos, tan rápido y sin esfuerzo, sobre todo en un set de data tan grande. Estos son solo unos pocos ejemplos de la elegancia y el claro poder computacional de Power Pivot.

Compresión

Otro beneficio es que los tamaños de los archivos se minimizan. El tamaño original del archivo era de 91MB y ahora es menos de 4MB. Eso es una compresión de 96% del archivo original.

Muestra 16: Tamaños de Archivo

¿Cómo sucede esto? Power Pivot usa el motor xVelocity para comprimir la data. En términos más sencillos, la data está almacenada en columnas en vez de filas. Este método de almacenamiento permite a la computadora comprimir valores duplicados. En nuestro ejemplo de set de data, hay solo cuatro regiones que se repiten en los dos millones de filas. Power Pivot para Excel puede almacenar eficientemente esta data. El resultado es que para la data que tiene muchos valores repetitivos, cuesta mucho menos almacenar esta data.

Algo importante a resaltar es que yo usé cantidades de dólares completas en este set de data de muestra. Si hubiese incluido dos puntos decimales para reflejar céntimos, el efecto de compresión disminuiría a un impresionante 80% del tamaño del archivo original.

Tabular SSAS

Los modelos Power Pivot también pueden ser expansibles a toda la empresa. Digamos que construyes un modelo Power Pivot que comienza a ganar usuarios en la organización, o la data crece a 10 millones de filas, o ambas. En ese momento, puede que no quieras treinta usuarios distintos actualizando el modelo o haciendo cambios. El modelo puede ser convertido en una Tabular SSAS, sin mayores inconvenientes. Todas las tablas y relaciones son retenidas, pero ahora puedes controlar la frecuencia de actualización, asignar roles (ej., solo lectura, lectura y procesar) a varios usuarios, e implementar un pequeño front-end de Excel, el cual se conecta al modelo Tabular. El resultado es que tus usuarios podrían acceder al modelo tabular implementado, con un pequeño libro de trabajo, pero no accederían a las fórmulas y medidas.

4) Análisis y Visualización de Data

Fórmulas CUBE

Una de las peticiones más comunes de mis clientes es que cree reportes que conformen diseños estrictamente definidos. Tengo clientes que piden anchos de columna específicos, códigos de color RGB y nombres y tamaños de letras predeterminados. Considera el siguiente panel de instrumentos:

Muestra 17: Fórmulas CUBE empotradas

¿Cómo poblamos los números de ventas sin generar PivotTables, si todas nuestras ventas son almacenadas con Power Pivot para Excel? Usando fórmulas CUBE! Podemos escribir fórmulas CUBE dentro de cualquier celda Excel y realizará el cálculo con el modelo Power Pivot que ya hemos construido.

Por ejemplo, la siguiente fórmula está escrita en la celda bajo el nombre “Ventas Totales 2016:”

=CUBEVALUE("ThisWorkbookDataModel","[Measures].[Total Sales]","[Calendar].[Year].[2016]")

La primera parte de la fórmula, resaltada en amarillo, se refiere al nombre del modelo Power Pivot. En general, se usa ThisWorkbookDataModel para versiones más nuevas de Power Pivot para Excel. La porción en verde define que queremos usar la medida Ventas Totales. La parte en azul instruye a Excel a filtrar solo para filas que tengan una Fecha de Ventas con un año igual a 2016.

Detrás de escena, Power Pivot ha construido un cube Online Analytical Processing (OLAP) con la data, columnas calculadas y medidas. Este diseño permite al usuario Excel poder acceder a la data con solo traerla directamente con las funciones de CUBE. Al usar las fórmulas CUBE, he podido construir declaraciones financieras que conforman diseños predeterminados. Esta habilidad es uno de los beneficios de usar Power Pivot para Excel para análisis financieros.

Power BI

Otra ventaja de Power Pivot para Excel es que puedes tomar rápidamente cualquier libro de trabajo de Power Pivot que construyas y convertirlo en un modelo Power BI. Al importar el libro de trabajo de Excel directamente a una aplicación de escritorio Power BI o Power BI online, puedes analizar, visualizar y compartir tu data con cualquier persona en tu organización. Esencialmente, Power BI es Power Pivot, PowerQuery, y SharePoint, todos en uno. Debajo, creé un tablero al importar el libro de trabajo de Power Pivot para Excel a la aplicación de escritorio Power BI. Nota lo interactiva que es la interfaz:

Muestra 18: Power BI

Algo genial sobre Power BI son las Preguntas y Respuestas en Idioma Natural. Para demostrarlo, cargué el modelo Power BI a mi cuenta en línea de Power BI. Desde la página web puedo hacer preguntas y Power BI construye el análisis apropiado mientras escribo:

Muestra 19: Preguntas y Respuestas en Idioma Natural

Este tipo de habilidad de preguntar le permite al usuario hacer preguntas del modelo de data e interactuar con la data en una manera más sencilla que en Excel.

Otro beneficio de Power BI es que los desarrolladores de Microsoft están lanzando actualizaciones constantemente. Nuevas características, que han sido pedidas por los usuarios, se lanzan mensualmente. Lo mejor de todo, es una transición sin inconvenientes de Power Pivot para Excel. Así que, ¡el tiempo que invertiste aprendiendo las fórmulas DAX se puede implementar en Power BI! Para el analista que necesita compartir sus análisis con muchos usuarios en varios dispositivos, valdría la pena explorar Power BI.

Las Mejores Prácticas

Una vez que comienzas, hay un par de mejores prácticas que deberías seguir.

La primera es decidir conscientemente que importa en primer lugar. ¿En algún momento usarás la dirección de la casa del vendedor? ¿Necesito saber la dirección de correo electrónico de mi cliente en el contexto de este libro de trabajo? Si la meta es agregar la data al tablero, entonces parte de la data disponible no será necesaria para esos cálculos. Dedicarle tiempo a limpiar la data entrante permitirá aligerar la carga de problemas y uso de memoria más tarde cuando se expanda tu set de data.

Otra de las mejores prácticas es recordar que Power Pivot no es Excel. En Excel, estamos acostumbrados a crear cálculos al expandir, constantemente, nuestras hojas de cálculo a la derecha. Power Pivot para Excel procesa efectivamente la data si limitamos esto a lo deseado. En vez de crear constantemente columnas de cálculo a la derecha de tu data, aprende a escribir medidas en el cristal inferior. Este hábito asegurará archivos más pequeños y computaciones más rápidas.

Finalmente, sugeriría usar nombres comunes en inglés para las medidas. Me tomó algo de tiempo adaptarme a esto. Pasé los primeros años inventando nombres como SumExpPctTotal, pero una vez que otras personas comenzaron a usar los mismos libros de trabajo, tuve mucho que explicar. Ahora, cuando comienzo un nuevo libro de trabajo, uso nombres como Ítem de Línea de Gastos como Porcentaje de Gastos Totales. A pesar de que el nombre es más largo, es mucho más fácil de usar por otra persona.

Casos de Uso en el Mundo Real

En este artículo, presenté solo parte de las maneras en que Power Pivot para Excel te permite tomar un paso importante, más allá de lo común de Excel. Pensé que sería útil resaltar algunos casos de la vida real en los que Power Pivot para Excel me ha sido extremadamente útil.

Aquí hay algunos:

• Analiza el rendimiento de un gran portafolio de valores en distintos rangos de tiempo: Ya que Power Pivot para Excel nos permite definir medidas que comparan un período de tiempo con uno previo, rápidamente podemos tener rendimiento trimestre tras trimestre, año tras año y mes tras mes, todos de manera continua, al escribir un par de medidas.
• Resalta la data de contabilidad usando niveles de agregación a la medida: Al identificar por nombre, categoría y estado financiero cada ítem de línea de un libro mayor de contabilidad, los reportes se pueden crear rápidamente, incluyendo los ítems de línea apropiados.
• Las cadenas pueden identificar ventas de la misma tienda: Al usar una tabla que mapea cuando las tiendas están en línea, los resultados financieros pueden compararse en una base de la misma tienda.
• Señala los que han estado por encima y por debajo de las ventas: PivotTables se puede crear para resaltar el top 5 de los SKU al igual que los 5 más bajos por ventas, márgenes generales, período de producción, etc.
• Los minoristas pueden definir tablas de calendario que usan una configuración 4-4-5: Usando una tabla de fecha hecha a la medida, un minorista puede asignar cada día a un mes específico 4-4-5 fácilmente, luego los resultados de las ventas diarias se pueden pasar al mes correspondiente.

De Hojas de Trabajo Torpes a Libros de Trabajo Modernos

Como analistas financieros, se nos pide realizar cálculos complejos en sets de data que siguen creciendo. Ya que Excel es la herramienta analítica por defecto, la curva de aprendizaje de Power Pivot es fácil y muchas de las funciones imitan las funciones nativas de Excel.

Con el uso de las funciones CUBE, Power Pivot para Excel se integra fácilmente a tus libros de trabajo ya existentes en Excel. La ganancia de eficiencia computacional no se puede negar. Asumiendo una rapidez de procesamiento de 20% más velocidad, lo cual es conservador, el analista financiero que pase seis horas al día en Excel se puede ahorrar 300 horas al año.

Adicionalmente, ahora podemos analizar sets de data que son más largos comparados con los que analizábamos anteriormente con nuestro Excel tradicional. Con modelos diseñados eficientemente, podemos tener más de 10 veces la data que se nos permitía anteriormente con Excel tradicional, mientras mantenemos una agilidad analítica. Con la habilidad de convertir los modelos desde Power Pivot al Tabular SSAS, la cantidad de data que se puede procesar es de 100–1,000 veces lo que podemos alcanzar en Excel.

La habilidad de Power Pivot para Excel de realizar cálculos con extrema rapidez, en grandes cantidades de data y todavía retener la habilidad de sumergirse en los detalles, puede transformar los análisis financieros de torpes hojas de cálculo a libros de trabajo modernos.

Si te interesa probar Power Pivot para Excel, debajo hay algunos materiales que te pueden ser útil para comenzar.

Referencias y Guías Útiles

• Collie, R., & Singh, A. (2016). Power Pivot and Power BI: The Excel user’s guide to DAX, Power Query, Power BI & Power Pivot in Excel 2010-2016. United States: Holy Macro! Books.
• Ferrari, A., & Russo, M. (2015). The definitive guide to DAX: Business intelligence with Microsoft Excel, SQL Server Analysis Services, and Power BI. United States: Microsoft Press, USA.

Por: Ellen Su

Articulo vía: Toptal