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

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

Compartir

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., SUMAVERAGETRUNC).

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

Muestra 1 GIF animado

¡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, PivotTablescuando 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

Muestra 2 imagen estática

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

Muestra 3: Creando una PivotTable

Muestra 3 GIF animado

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 VLOOKUPSUMIF, 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

Muestra 4 GIF animado

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

Muestra 5 GIF animado

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

Muestra 6 GIF animado

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

Muestra 7 GIF animado

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

Muestra 8 GIF animado

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

Muestra 9 imagen estática

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

Muestra 10 imagen estática

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

Muestra 11 imagen estática

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

Muestra 12 imagen estática

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

Muestra 13: Granularidades – Presupuesto Disparejos vs. Resultados Verdaderos

Muestra 13 imagen estática

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

Muestra 14 imagen estática

Via: Toptal.com