Excel 2016 es indiscutiblemente una herramienta bastante amplia que nos ofrece un sin número de oportunidades y utilidades para representar y organizar nuestros datos de una forma sencilla, pero a la vez totalmente completa y detallada.
Una de las herramientas que han incursionado desde Excel 2010 y que muchas veces no tomamos en cuenta es PowerPivot. Hoy vamos a comprender cómo podemos implementar y usar PowerPivot en Excel 2106 para llevar a un nivel más allá toda la gestión de nuestros datos.
1. Cómo habilitar PowerPivot en Excel 2016
Es importante aclarar que PowerPivot no viene configurado por defecto en Excel 2016 por lo cual es necesario que la activemos. Para esto vamos al menú Archivo y allí seleccionamos Opciones
En la ventana desplegada seleccionamos la ficha Complementos y en el costado inferior derecho elegimos la opción Complementos COM en el campo Administrar. Pulsamos en el botón Ir
En la ventana desplegada será necesarios activar la casilla Microsoft Office PowerPivot Excel 2013 (esta es la opción brindada para Excel 2016)
Pulsamos Aceptar y ahora podemos ver que se ha activado la ficha PowerPivot en Excel 2016.
Desde este punto gestionaremos todo el proceso asociado a PowerPivot en Excel 2016.
2. Origen de datos
Para conocer cómo funciona PowerPivot en Excel 2016 hemos creado los siguientes datos:
En este caso hemos creado tres tablas, en una tenemos el nombre del ejecutivo y las unidades que ha vendido en una fecha determinada, en otra tabla tenemos la zona asignada a dicho ejecutivo y en la última tabla el producto asociado a un determinado ID.
En este caso es obligatorio que integremos las tres tablas para poder generar un reporte que indique las cantidades vendidas por un ejecutivo en una zona determinada.
Para conocer las diferencias entre PowerPivot y las funciones normales de Excel 2016 usaremos ambos métodos para comprobar la eficacia de PowerPivot.
3. Generar reporte usando funciones en Excel 2016
El primer paso consiste en integrar los datos de las tablas entre sí, para ello el primer paso es conocer la zona de cada ejecutivo y para ello usaremos la función BUSCARV con la siguiente sintaxis:
=BUSCARV([@Ejecutivo];Tabla3;2;FALSO)
De esta manera obtenemos de forma correcta la zona de cada ejecutivo. Ahora obtenemos el nombre del producto usando la siguiente sintaxis:
=BUSCARV([@[ID producto]];Tabla4;2;FALSO)
Hasta este punto tenemos los datos requeridos para el reporte.
4. Creando la tabla dinámica para el reporte
El método más efectivo para generar un reporte completo, dinámico e íntegro es sin lugar a dudas la tabla dinámica.
Para iniciar el proceso de creación de la tabla dinámica vamos a la ficha Insertar y en el grupo Tablas elegimos la opción Tabla dinámica. En la ventana desplegada seleccionamos el rango que hemos de representar en la tabla dinámica
En la configuración de la tabla dinámica realizaremos lo siguiente:
- Al campo Filas agregamos los valores Zona y Producto
- Al campo Valores agregamos la opción Cantidad vendida
De este modo podemos generar un reporte por zona, producto y cantidad vendida. Ahora veremos cómo hacer este mismo reporte usando PowerPivot para notar la diferencia.
5. Generar reporte usando PowerPivot en Excel 2016
La principal ventaja de usar PowerPivot es que evitamos el uso de fórmulas y funciones lo cual simplifica la tarea de gestión de los datos.
Para esto seleccionaos cualquier celda de la tabla creada y nos dirigimos a la ficha PowerPivot y del grupo Tablas seleccionamos la opción Agregar a modelado de datos
Allí se desplegará la ventana de PowerPivot y el entorno que tendremos será el siguiente:
Al ver los valores de la tabla podemos estar seguros que estos datos han sido integrados al modelado de datos. Debemos repetir este mismo proceso con las otras dos tablas y veremos lo siguiente:
Veremos que en la parte inferior de la hoja tenemos las tres tablas integradas.
Una vez tengamos las tablas integradas al modelado de datos el siguiente paso consiste en crear las relaciones entre las columnas. Para ello damos clic en la opción Vista de diagrama ubicada en la esquina superior derecha en el grupo Ver
La vista obtenida será la siguiente. Desde esta vista podemos ver las tablas que hemos integrado y con estos datos debemos crear la respectiva relación.
En primer lugar daremos clic en la línea Ejecutivo de la tabla 3 y la arrastraremos a la línea Ejecutivos de la tabla 2. Lo mismo haremos con la línea ID producto entre la tabla 2 y la tabla 4. Veremos lo siguiente:
Para validar que se ha creado una relación de forma correcta podemos dar clic derecho sobre alguna de las relaciones y seleccionar la opción Editar relación
Se desplegará la siguiente ventana donde podremos ver las tablas que actúan en la relación creada:
Hasta este punto hemos creado las respectivas relaciones.
6. Crear la tabla dinámica
A continuación daremos clic en la opción Tabla dinámica ubicada en la ficha Inicio y veremos la siguiente ventana:
Esta tabla dinámica se diferencia de las comunes ya que está basada en el modelado de datos que hemos definido y podremos acceder a cada una de las tablas usando la opción Campos de tabla dinámica.
Al pulsar Aceptar basta con arrastrar los respectivos campos a los valores adecuados. En este caso arrastramos el campo Cantidad vendida de la tabla 2 a Valores, el campo Zona de la tabla 3 a Filas y el campo Producto de la tabla 4 a Filas
Podemos ver que se ha creado el mismo reporte de una forma dinámica y sin usar ninguna función de Excel 2016. De esta manera contamos con PowerPivot en Excel 2016 para crear reportes de una manera mucho más práctica, con mayor facilidad y con menos posibilidad de cometer errores en el proceso de reporte en Excel 2016.