Tutorial de Power Pivot para Excel: Principales casos de uso y ejemplos

Resumen ejecutivo

Descargue el conjunto de datos aquí para seguir el tutorial.

El Traje Nuevo del Emperador: Tutorial de Power Pivot

En los diversos campos y sub-subcampos que abarcan finanzas, análisis financiero, mercados financieros e inversiones financieras, Microsoft Excel es el rey. Sin embargo, con la llegada y el crecimiento exponencial de los grandes datos, impulsados ​​por décadas de agregación y acumulación de datos, la llegada del almacenamiento en la nube barato y el auge de Internet de las cosas, es decir, el comercio electrónico, las redes sociales y la interconexión de los dispositivos, Excel La funcionalidad y las capacidades heredadas se han llevado al límite.

Más específicamente, la infraestructura y las limitaciones de procesamiento de la generación anterior de Excel, como su límite de filas de 1,048,576 filas, o la inevitable ralentización del procesamiento en lo que respecta a grandes conjuntos de datos, tablas de datos y hojas de cálculo interconectadas, redujeron su usabilidad como una herramienta efectiva de big data. Sin embargo, en 2010, Microsoft agregó una nueva dimensión a Excel, llamada Power Pivot. Power Pivot ofreció inteligencia comercial y funcionalidad de análisis comercial de última generación a Excel en su capacidad para extraer, combinar y analizar conjuntos de datos casi ilimitados sin afectar la velocidad de procesamiento. Sin embargo, a pesar de su lanzamiento hace ocho años, la mayoría de los analistas financieros todavía no saben cómo usar Power Pivot, y muchos ni siquiera saben que existe.

En este artículo, le mostraré cómo usar Power Pivot para superar los problemas comunes de Excel y echaré un vistazo a las ventajas clave adicionales del software usando algunos ejemplos. Este tutorial de Power Pivot está destinado a servir como una guía de lo que puede lograr con esta herramienta y, al final, explorará algunos casos de uso de muestra en los que Power Pivot a menudo resulta invaluable.

¿Qué es Power Pivot y por qué es útil?

Power Pivot es una característica de Microsoft Excel que se introdujo como un complemento de Excel 2010 y 2013, y ahora es una característica nativa de Excel 2016 y 365. Como explica Microsoft, Power Pivot para Excel “le permite importar millones de filas de datos de múltiples fuentes de datos en un solo libro de Excel, cree relaciones entre datos heterogéneos, cree columnas y medidas calculadas usando fórmulas, cree tablas dinámicas y gráficos dinámicos, y luego analice más los datos para que pueda tomar decisiones comerciales oportunas sin necesidad de asistencia de TI. ”

El lenguaje de expresión principal que usa Microsoft en Power Pivot es DAX (Expresiones de análisis de datos), aunque se pueden usar otros en situaciones específicas. Nuevamente, como 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 devolver uno o más valores. Dicho de manera más simple, DAX lo ayuda a crear nueva información a partir de los datos que ya están en su modelo”. Afortunadamente para aquellos que ya están familiarizados con Excel, las fórmulas DAX les resultarán familiares, ya que muchas de las fórmulas tienen una sintaxis similar (p. ej., SUM , AVERAGE , TRUNC ).

Para mayor claridad, los beneficios clave de usar Power Pivot frente a Excel básico se pueden resumir de la siguiente manera:

• Le permite importar y manipular cientos de millones de filas de datos donde Excel tiene una restricción estricta de poco más de un millón de filas.
• Le permite importar datos de múltiples fuentes en un solo libro de trabajo de origen sin tener que crear múltiples hojas de origen que sufren problemas de transferencia y control de versiones.
• Le permite manipular los datos importados, analizarlos y sacar conclusiones sin ralentizar su computadora a paso de tortuga.
• Le permite visualizar los datos con PivotCharts y Power BI.

En las siguientes secciones, repasaré cada uno de los anteriores y le mostraré cómo Power Pivot para Excel puede ser útil.

Cómo usar Power Pivot

1) Importación de grandes conjuntos de datos

Como se mencionó anteriormente, una de las principales limitaciones de Excel se relaciona con el trabajo con conjuntos de datos extremadamente grandes. Afortunadamente para nosotros, Excel ahora puede cargar mucho más del límite de un millón de filas directamente en Power Pivot.

Para demostrar esto, generé un conjunto de datos de muestra de dos años de ventas para un minorista de artículos deportivos con nueve categorías de productos diferentes y cuatro regiones. El conjunto de datos resultante es de dos millones de filas.

Usando la pestaña Datos en la cinta, creé una nueva consulta desde el archivo CSV (consulte Crear una nueva consulta a continuación). Esta funcionalidad solía llamarse PowerQuery, pero a partir de Excel 2016 y 365, estaba más integrada en la pestaña Datos de Excel.

Creación de una nueva consulta

Desde un libro de trabajo en blanco en Excel hasta cargar los dos millones de filas en Power Pivot, ¡tomó aproximadamente un minuto! Tenga en cuenta que pude realizar un formateo ligero de datos al promover la primera fila para que se convirtiera en los nombres de las columnas. En los últimos años, la funcionalidad de Power Query ha mejorado enormemente de un complemento de Excel a una parte estrechamente integrada de la pestaña Datos en la barra de herramientas. Power Query puede pivotar, aplanar, limpiar y dar forma a sus datos a través de su conjunto de opciones y su propio lenguaje, M.

2) Importación de datos de múltiples fuentes

Uno de los otros beneficios clave de Power Pivot para Excel es la capacidad de importar fácilmente datos de múltiples fuentes. Anteriormente, muchos de nosotros creamos varias hojas de trabajo para nuestras diversas fuentes de datos. A menudo, este proceso implicó escribir código VBA y copiar y pegar desde estas fuentes dispares. Sin embargo, afortunadamente para nosotros, Power Pivot le permite importar datos de diferentes fuentes de datos directamente a Excel sin tener que enfrentarse a los problemas mencionados anteriormente.

Usando la función de consulta en el Anexo 1, podemos extraer de cualquiera de las siguientes fuentes:

• microsoft azure
• servidor SQL
• Teradata
• Facebook
• Fuerza de ventas
• Archivos JSON
• libros de excel
• …y muchos más

Además, se pueden combinar varias fuentes de datos en la función Consulta o en la ventana de Power Pivot para integrar datos. Por ejemplo, puede extraer datos de costos de producción de un libro de Excel y resultados de ventas reales del servidor SQL a través de Query en Power Pivot. A partir de ahí, puede combinar los dos conjuntos de datos haciendo coincidir los números de lote de producción para producir márgenes brutos por unidad.

3) Trabajar con grandes conjuntos de datos

Otra ventaja clave de Power Pivot para Excel es la capacidad de manipular y trabajar con grandes conjuntos de datos para sacar conclusiones y análisis relevantes. Veré algunos ejemplos comunes a continuación para darle una idea del poder de la herramienta.

Medidas

Los adictos a Excel sin duda estarán de acuerdo en que las tablas dinámicas son una de las tareas más útiles y, al mismo tiempo, una de las más frustrantes que realizamos. Frustrante particularmente cuando se trata de trabajar con conjuntos de datos más grandes. Afortunadamente, Power Pivot para Excel nos permite crear tablas dinámicas fácil y rápidamente cuando trabajamos con conjuntos de datos más grandes.

En la imagen a continuación, titulada Creación de medidas , observe cómo la ventana de Power Pivot se separa en dos paneles. El panel superior tiene los datos y el panel inferior alberga las medidas. Una medida es un cálculo que se realiza en todo el conjunto de datos. Ingresé una medida escribiendo en la celda resaltada.

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

Esto crea una nueva medida que suma toda la columna Importe. Del mismo modo, puedo escribir otra medida en la celda de abajo

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

Creación de medidas

A partir de ahí, observe lo rápido que es crear una tabla dinámica familiar en un gran conjunto de datos.

Crear una tabla dinámica

Tablas de dimensiones

Como analistas financieros que utilizan Excel, nos volvemos expertos en el uso de fórmulas enrevesadas para adaptar la tecnología a nuestra voluntad. Dominamos VLOOKUP , SUMIF e incluso el temido INDEX(MATCH()) . Sin embargo, al usar Power Pivot, podemos tirar mucho de eso por la ventana.

Agregar una tabla creada por el usuario a un modelo de Power Pivot

Para demostrar esta funcionalidad, creé una pequeña tabla de referencia en la que asigné cada Categoría a un Tipo. Al elegir "Agregar al modelo de datos", esta tabla se carga en Power Pivot (consulte Agregar una tabla creada por el usuario a un modelo de Power Pivot arriba).

También creé una tabla de fechas para usar con nuestro conjunto de datos (consulte Crear una tabla de fechas a continuación). Power Pivot para Excel facilita la creación rápida de una tabla de fechas para consolidar por meses, trimestres y días de la semana. El usuario también puede crear una tabla de fechas más personalizada para analizar por semanas, años fiscales o cualquier agrupación específica de la organización.

Creación de una tabla de fechas

Columnas calculadas

Además de las medidas, existe otro tipo de cálculo: las columnas calculadas. 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 datos. Creé una nueva columna calculada a continuación (consulte Crear una columna calculada a continuación) que ordena la tabla de datos contables por cantidad. Las ventas por debajo de $50 se etiquetan como "Pequeñas" y todas las demás se etiquetan como "Grandes". ¿No se siente intuitiva la fórmula?

Creación de una columna calculada

Relaciones

A continuación, podemos crear una relación entre el campo Categoría de la tabla Datos contables y el campo Categoría de la tabla Categoría mediante la Vista de diagrama. Además, podemos definir una relación entre el campo Fecha de venta de la tabla Datos contables y el campo Fecha de la tabla Calendario.

Definición de relaciones

Ahora, sin necesidad de las funciones VLOOKUP o SUMIF , podemos crear una tabla dinámica que calculó las ventas totales por año y tipo, con una segmentación para el tamaño de la transacción.

Tabla dinámica usando relaciones

O bien, podemos crear un gráfico de Ventas promedio para cada día de la semana usando la nueva tabla Calendario.

Gráfico dinámico usando relaciones

Si bien este gráfico parece simple, es impresionante que tomó menos de diez segundos crear una consolidación de más de dos millones de filas de datos, sin agregar una nueva columna a los datos de ventas.

Si bien podemos realizar todos estos escenarios de informes consolidados, siempre podemos profundizar en los elementos de línea individuales. Conservamos nuestros datos altamente granulares.

Funciones avanzadas

Hasta ahora, la mayoría de los análisis que he mostrado son cálculos relativamente sencillos. 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 con un período de tiempo comparable del año anterior. Power Pivot tiene algunas 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 anteriores a la tabla de datos contables en Power Pivot me permite producir la siguiente tabla dinámica con unos pocos clics.

Tabla dinámica de inteligencia de tiempo

Granularidades no coincidentes

Como analista financiero, un problema que a menudo tengo que resolver es el de las granularidades no coincidentes. En nuestro ejemplo, los datos de ventas reales se muestran a nivel de categoría, pero preparemos un presupuesto que sea solo a nivel estacional. Para profundizar este desajuste, prepararemos un presupuesto trimestral, aunque el dato de ventas sea diario.

Granularidades no coincidentes: tabla de presupuesto

Con Power Pivot para Excel, esta inconsistencia se resuelve fácilmente. Al crear dos tablas de referencia adicionales, o tablas de dimensiones en la nomenclatura de la base de datos, ahora podemos crear las relaciones adecuadas para analizar nuestras ventas reales con respecto a los montos presupuestados.

Granularidades no coincidentes: relaciones

En Excel, la siguiente tabla dinámica se integra rápidamente.

Granularidades no coincidentes: presupuesto frente a resultados reales

Además, podemos definir nuevas medidas que calculan la variación entre las ventas reales y las ventas presupuestadas de la siguiente manera:

 Actual-to-Budget Variance:=DIVIDE([Total Sales],[Total Budgeted Sales])-1

Con esta medida, podemos mostrar la varianza en una tabla dinámica.

Granularidades no coincidentes: resultados de varianza

Porcentaje del total

Finalmente, examinemos las ventas en una categoría particular como un porcentaje de todas las ventas (p. ej., la contribución de la categoría a las ventas generales) y las ventas en una categoría particular como un porcentaje de todas las ventas del mismo tipo (p. ej., la contribución de la categoría a las ventas de tipo estacional). Ventas). Creé las dos medidas a continuación:

 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 tabla dinámica:

Porcentaje del total

Observe cómo se realizan los cálculos tanto a nivel de categoría como de tipo estacional. Me encanta la rapidez y la facilidad con que se realizan estos cálculos en un conjunto de datos tan grande. Estos son solo algunos ejemplos de la elegancia y el puro poder computacional de Power Pivot.

Compresión

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

Tamaños de archivo

¿Como sucedió esto? Power Pivot usa el motor xVelocity para comprimir los datos. En términos simples, los datos se almacenan en columnas en lugar de filas. Este método de almacenamiento permite que la computadora comprima valores duplicados. En nuestro conjunto de datos de ejemplo, solo hay cuatro regiones que se repiten en los dos millones de filas. Power Pivot para Excel puede almacenar estos datos de manera más eficiente. El resultado es que para los datos que tienen muchos valores repetidos, cuesta mucho menos almacenar estos datos.

Una cosa a tener en cuenta es que utilicé montos enteros en dólares en este conjunto de datos de muestra. Si hubiera incluido dos puntos decimales para reflejar los centavos, el efecto de compresión se reduciría a un impresionante 80 % del tamaño del archivo original.

SSAS tabular

Los modelos de Power Pivot también se pueden escalar a toda la empresa. Supongamos que crea un modelo de Power Pivot que comienza a ganar muchos usuarios en la organización, o los datos crecen hasta diez millones de filas, o ambas cosas. En este punto, es posible que no desee que treinta usuarios diferentes actualicen el modelo o realicen cambios. El modelo se puede convertir sin problemas en SSAS Tabular. Todas las tablas y relaciones se conservan, pero ahora puede controlar la frecuencia de actualización, asignar funciones (por ejemplo, solo lectura, lectura y proceso) a varios usuarios e implementar solo una pequeña interfaz de Excel que se vincula con el modelo tabular. El resultado es que sus usuarios podrían acceder al modelo Tabular implementado con un pequeño libro de trabajo, pero no tener acceso a las fórmulas y medidas.

4) Visualización y análisis de datos

Fórmulas CUBO

Una de las solicitudes constantes de mis clientes es que cree informes que se ajusten a un diseño estrictamente definido. Tengo clientes que solicitan anchos de columna específicos, códigos de color RGB y nombres y tamaños de fuente predefinidos. Considere el siguiente tablero:

Fórmulas CUBE integradas

¿Cómo completamos los números de ventas sin generar tablas dinámicas si todas nuestras ventas están alojadas en Power Pivot para Excel? ¡Usando fórmulas CUBE! Podemos escribir fórmulas CUBE dentro de cualquier celda de Excel y realizará el cálculo utilizando el modelo de Power Pivot que ya hemos construido.

Por ejemplo, la siguiente fórmula se escribe en la celda debajo de "Ventas totales de 2016:"

=VALORCUBO(" Este modelo de datos de libro de trabajo "," [Medidas].[Ventas totales] "," [Calendario].[Año].[2016] ")

La primera parte de la fórmula, resaltada en amarillo, hace referencia al nombre del modelo Power Pivot. En general, suele ser ThisWorkbookDataModel para las versiones más recientes de Power Pivot para Excel. La parte en verde define que queremos usar la medida Ventas totales. La parte en azul le indica a Excel que filtre solo las filas que tienen una Fecha de venta con un año igual a 2016.

Detrás de escena, Power Pivot ha construido un cubo de procesamiento analítico en línea (OLAP) con los datos, las columnas calculadas y las medidas. Este diseño permite que el usuario de Excel acceda a los datos buscándolos directamente con las funciones CUBE. Usando fórmulas CUBE, he podido construir estados financieros completos que se ajustan a diseños predefinidos. Esta capacidad es uno de los aspectos más destacados del uso de Power Pivot para Excel para el análisis financiero.

BI de energía

Otra ventaja de Power Pivot para Excel es que puede tomar rápidamente cualquier libro de trabajo de Power Pivot que cree y convertirlo rápidamente en un modelo de Power BI. Al importar el libro de Excel directamente a la aplicación Power BI Desktop o Power BI Online, puede analizar, visualizar y compartir sus datos con cualquier persona de su organización. Básicamente, Power BI es Power Pivot, PowerQuery y SharePoint, todo en uno. A continuación, he creado un tablero al importar el libro de trabajo anterior de Power Pivot para Excel a la aplicación de escritorio Power BI. Observe lo interactiva que es la interfaz:

BI de energía

Una gran cosa acerca de Power BI son las preguntas y respuestas del lenguaje natural. Para demostrarlo, cargué el modelo de Power BI en mi cuenta de Power BI en línea. Desde el sitio web, puedo hacer preguntas y Power BI construye el análisis adecuado a medida que escribo:

Preguntas y respuestas sobre lenguaje natural

Este tipo de capacidad de consulta permite al usuario hacer preguntas sobre el modelo de datos e interactuar con los datos de una manera más sencilla que en Excel.

Otro beneficio de Power BI es que los desarrolladores de Microsoft lanzan actualizaciones constantemente. Las nuevas funciones, muchas solicitadas por los usuarios, se lanzan mensualmente. Lo mejor de todo es que es una transición perfecta desde Power Pivot para Excel. Por lo tanto, el tiempo que invirtió en aprender las fórmulas DAX se puede implementar en Power BI. Para el analista que necesita compartir su análisis con muchos usuarios en diferentes dispositivos, vale la pena explorar Power BI.

Mejores prácticas

Una vez que comience, hay algunas mejores prácticas que debe seguir.

El primero es decidir cuidadosamente qué importar en primer lugar. ¿Utilizará alguna vez 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 el objetivo es agregar los datos en un tablero, algunos de los datos disponibles no serán necesarios para esos cálculos. Dedicar tiempo a curar los datos que ingresan aliviará en gran medida los problemas y el uso de la memoria más adelante cuando su conjunto de datos se expanda.

Otra práctica recomendada es recordar que Power Pivot no es Excel. En Excel, estamos acostumbrados a crear cálculos expandiendo constantemente nuestras hojas de trabajo hacia la derecha. Power Pivot para Excel procesa los datos de manera más eficiente si limitamos este deseo de destino manifiesto. En lugar de crear continuamente columnas calculadas a la derecha de sus datos, aprenda a escribir medidas en el panel inferior. Este hábito garantizará tamaños de archivo más pequeños y cálculos más rápidos.

Finalmente, sugeriría usar nombres sencillos en inglés para las medidas. Este me tomó mucho tiempo para adoptar. 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 empiezo un nuevo libro de trabajo, uso nombres de medidas como Expense Line Item as Percent of Total Expenses . Si bien el nombre es más largo, es mucho más fácil de usar para otra persona.

Casos de uso del mundo real

En este artículo, he presentado solo algunas de las formas en que Power Pivot para Excel le permite dar un paso importante más allá del simple Excel. Pensé que sería útil resaltar algunos casos de uso del mundo real en los que descubrí que Power Pivot para Excel es extremadamente útil.

Aquí están algunas:

• Analice el rendimiento de una gran cartera de activos en distintos intervalos de tiempo: dado que Power Pivot para Excel nos permite definir medidas que comparan un período de tiempo con uno anterior, podemos tener rápidamente trimestre tras trimestre, año tras año, y el rendimiento mes a mes, todo de forma continua, escribiendo solo unas pocas medidas.
• Resuma los datos contables utilizando niveles de agregación personalizados: al identificar cada elemento de línea del libro mayor por nombre, categoría y estado financiero, se pueden crear rápidamente informes que incluyan los elementos de línea apropiados.
• Las cadenas pueden identificar las ventas en la misma tienda: al usar una tabla que mapea cuándo las tiendas están en línea, los resultados financieros se pueden comparar en la misma tienda.
• Identifique los resultados superiores e inferiores en ventas: se pueden crear tablas dinámicas que destacan los cinco SKU principales y los cinco SKU inferiores por ventas, márgenes brutos, plazos de producción, etc.
• Los minoristas pueden definir tablas de calendario que utilicen una configuración 4-4-5: con una tabla de fechas personalizada, un minorista puede asignar fácilmente cada día a un mes específico de 4-4-5, luego los resultados de las ventas diarias se pueden transferir al mes correspondiente.

De hojas de cálculo torpes a libros de trabajo modernos

Como analistas financieros, debemos realizar cálculos complejos en conjuntos de datos en constante expansión. Dado que Excel ya es la herramienta analítica predeterminada, la curva de aprendizaje de Power Pivot es fácil y muchas de las funciones reflejan las funciones nativas de Excel.

Con el uso de las funciones CUBE, Power Pivot para Excel se integra a la perfección en sus libros de Excel existentes. La ganancia de eficiencia computacional no puede pasarse por alto. Suponiendo una velocidad de procesamiento un 20 % más rápida, lo cual es conservador, ¡el analista financiero que pasa seis horas al día en Excel puede ahorrar 300 horas al año!

Además, ahora podemos analizar conjuntos de datos que son mucho más grandes que antes con nuestro Excel tradicional. Con modelos diseñados de manera eficiente, podemos tener fácilmente 10 veces la cantidad de datos que antes teníamos en Excel tradicional, mientras mantenemos una agilidad analítica rápida. Con la capacidad de convertir los modelos de Power Pivot a SSAS Tabular, la cantidad de datos que se pueden procesar es entre 100 y 1000 veces superior a la que podemos lograr en Excel.

La capacidad de Power Pivot para Excel para realizar cálculos ultrarrápidos en grandes cantidades de datos y aún así conservar la capacidad de sumergirse en los detalles puede transformar el análisis financiero de hojas de cálculo toscas a libros de trabajo modernos.

Si está interesado en probar Power Pivot para Excel, a continuación encontrará algunos materiales útiles para comenzar.

Referencias y guías útiles

Collie, R. y Singh, A. (2016). Power Pivot y Power BI: la guía del usuario de Excel para DAX, Power Query, Power BI y Power Pivot en Excel 2010-2016. Estados Unidos: ¡Santo Macro! Libros.

Ferrari, A. y Russo, M. (2015). La guía definitiva de DAX: Business Intelligence con Microsoft Excel, SQL Server Analysis Services y Power BI. Estados Unidos: Microsoft Press, EE.