Power Pivot: análisis de datos eficaz y modelado de datos en Excel

PowerPivot es un complemento de Excel que puede usar para realizar análisis de datos y crear sofisticados modelos de datos. PowerPivot, puede mezclar grandes volúmenes de datos de varios orígenes, realizar análisis de información rápidamente y compartir información con facilidad.En ambos Excel y PowerPivot, puede crear un modelo de datos, una colección de tablas con relaciones. El modelo de datos que se ve en un libro en Excel es el mismo modelo de datos que se ve en la ventana de PowerPivot. Los datos que se importan en Excel están disponibles en PowerPivot y viceversa.
Principales características de PowerPivot para Excel
  • Importar millones de filas de datos de varios orígenes de datos    Con Power Pivot para Excel, puede importar millones de filas de datos de varios orígenes de datos en un único libro de Excel, crear relaciones entre datos heterogéneos, crear columnas calculadas y medidas mediante fórmulas, crear tablas dinámicas y Gráficos dinámicos y analizar aún más los datos de modo que puede tomar decisiones de negocios oportunas: todo sin necesidad de asistencia de TI.
  • Análisis y disfrute de cálculos rápidos    Proceso millones de filas de la misma hora como miles y hacer que la mayoría de los procesadores multinúcleo y gigabytes de memoria para el procesamiento de más rápida de los cálculos. Supera las limitaciones existentes masiva para análisis de datos en el escritorio con algoritmos de compresión eficaz cargar incluso los conjuntos de datos mayores en memoria.
  • Soporte técnico prácticamente ilimitada de orígenes de datos    Proporciona la base para importar y combinar datos de origen de cualquier ubicación masiva para análisis de datos en el escritorio, incluyendo bases de datos relacionales, orígenes multidimensionales, servicios de nube, fuentes de datos, archivos de Excel, archivos de texto y datos desde la Web.
  • Seguridad y administración   Power Pivot Panel de administración permite a los administradores de TI supervisar y administrar las aplicaciones compartidas para garantizar la seguridad, alta disponibilidad y rendimiento.
  • Expresiones de análisis de datos (DAX)    DAX es un lenguaje de fórmulas que amplía las capacidades de manipulación de datos de Excel para habilitar más sofisticadas y complejas agrupamiento, cálculos y análisis. La sintaxis de las fórmulas DAX es muy similar de las fórmulas de Excel.

Cuando se habilita el complemento de Power Pivot, se habilita la pestaña Power Pivot en la cinta, tal como se muestra en la siguiente imagen.
En la pestaña de la cinta Power Pivot, seleccione Administrar en la sección Modelo de datos, como se muestra a continuación.
Cuando selecciona Administrar, aparece la ventana de Power Pivot, que es donde puede ver y administrar el modelo de datos, agregar cálculos, establecer relaciones y ver los elementos de su modelo de datos de Power Pivot. Un modelo de datos es una colección de tablas o de cualquier otro dato, generalmente con relaciones establecidas entre ellos. La siguiente imagen muestra la ventana de Power Pivot.La ventana de Power Pivot también puede establecer (y representar gráficamente) relaciones entre los datos incluidos en el modelo. Al seleccionar el icono Vista de diagrama de la esquina inferior derecha de la ventana de Power Pivot, puede ver las relaciones existentes en el modelo de datos de Power Pivot. La siguiente imagen muestra la ventana de Power Pivot en Vista de diagrama.

Importar Grandes volúmenes de datos

Usando la cinta Datos , cree un  Nuevo Query de un archivo CSV. Esta funcionalidad usada antes se llamaba PowerQuery, pero desde Excel 2016, se ha integrado mucho más en la cinta Datos de Excel.

Creando una Nueva Consulta

 

¡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!

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 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 , 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 datos. Por ejemplo, puedes extraer el dato del costo de producción de un libro de trabajo de Excel y los resultados de ventas del servidor SQL a través de Query hacia Power Pivot.

Medidas

Los adictos a Excel estarán completamente de acuerdo con que las tablas dinámicas son unas de las tareas más útiles pero al mismo tiempo frustrantes de realizar. Particularmente frustrantes cuando se trata de trabajar con grandes volúmenes de datos. Afortunadamente, Power Pivot para Excel nos permite crear, rápida y fácilmente, Tablas Dinámicas cuando se trabaja con conjuntos de datos muy grandes..

En la imagen de abajo, nota como la ventana de Power Pivot está separada en dos cuadriculas. La de  arriba tiene los datos y la de abajo contiene las medidas. Una medida es un cálculo que se aplica a todo el conjunto de datos. Ej.

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])

Creando Medidas

Desde ahí, observa lo rápido que es crear una Tabla dinámica con un conjunto de datos muy grande.

Creando una Tabla Dinámica

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.

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

Muestra 4 GIF animado

Para demostrar esta funcionalidad, tenemos una pequeña tabla de referencia en la que se asigno cada Categoría a un Tipo. Al escoger “Agregar a Modelo de Datos”, esta tabla es cargada a Power Pivot (Ver imagen anterior).

También se creó una tabla de fecha para ser usada con nuestro conjuntos de datos. 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.

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.

Se creó una nueva columna de cálculo la cual organiza la tabla de Datos 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?

Creando una Columna de Cálculo

Muestra 6 GIF animado

Relaciones

Luego, se puede crear una relación entre el campo de la Categoría de la tabla de Datos 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 Datos de Contabilidad y el campo de Fecha de la tabla de Calendario.

Definiendo Relaciones

Muestra 7 GIF animado

Ahora, sin necesidad de ninguna función SUMIF o VLOOKUP, podemos crear una Tabla dinamica que calcule las Ventas Totales por año y tipo.

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.

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 reportes consolidados, también podemos adentrarnos en los ítems de línea individuales.

Funciones Avanzadas

Hasta ahora, la mayoría de los análisis que he mostrado son, relativamente, cálculos directos. Ahora, veremos 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 Datos de Contabilidad en Power Pivot me permite producir la siguiente Tabla dinámica en solo un par de clics.

Inteligencia de Tiempo PivotTable

Escriba su comentario

Tu dirección de correo electrónico no será publicada. Los campos obligatorios están marcados con *

x Shield Logo
Este sitio es protegido por
The Shield →