Análisis Pareto en Excel 2003

En la nota anterior sobre análisis de datos según el principio de Pareto mostramos cómo crear el cuadro de resultados y el gráfico con Excel. La solución con tablas dinámicas que mostré en esa nota sólo se aplica a Excel 2010.

Entre las mejoras introducidas en Excel 2010 se cuentan seis funcionalidades en la opción “Mostrar como…” de las tablas dinámicas. Entre ellas “% del Total en…”, que calcula el total acumulado como porcentaje del total. Esta funcionalidad no está disponible en las versiones anteriores de Excel.

En esta nota mostraré un rodeo para dar la misma solución que Excel 2003
.
En la tabla de datos agregamos un campo (columna): “Pct del Total” (porcentaje del total)

La fórmula para calcular los valores del campo es

=C2/SUMA($C$2:$C$287)

que sencillamente calcula el porcentaje de cada valor en relación al total. Como hemos definido nuestra tabla como “lista” no debemos preocuparnos por actualizar las fórmulas cuando agreguemos o quitemos valores. Si usamos versiones anteriores a Excel 2003 tendremos que usar rangos dinámicos.

Una vez creado el campo auxiliar, creamos la tabla dinámica con el campo Categoría en el área de las filas y el campo Ventas en el área de datos. Luego ordenamos la tabla de mayor a menor según el campo de Ventas.

En el próximo paso agregamos el campo “Pct del Total” al área de ventas y definimos “Mostrar datos como..” con la opción “Total en…” y “Categoría” como capo de base

Todo el proceso puede verse en este video



Finalmente creamos el gráfico dinámico, donde deberemos hacer algunos cambios para obtener algo humanamente legible. Empezamos por crear el gráfico (un clic al icono de gráficos en la barra de herramientas de las tablas dinámicas). Por defecto Excel crea el gráfico en una hoja separada de manera que si queremos ubicarlo en la misma hoja de la tabla dinámica tendremos que cambiar la ubicación.

El segundo paso es cambiar las proporciones del gráfico (por defecto, estos gráficos dinámicos sufren de enanismo agudo al cambiarlos de ubicación).

Para que la serie “Pct del Total” sea visible tendremos que definirla en un eje Y secundario. La forma más sencilla es usar la barra de herramientas de los gráficos para seleccionar la serie y el menú Formato para cambiar las definiciones

Asegurándonos que la series esté seleccionada cambiamos a “eje secundario” y el tipo de gráfico a “Línea”. Después de hacer otros cambios obtenemos

Análisis de datos según el principio de Pareto con Excel

Esto es lo que me consulta uno de mis lectores:

Quisiera consultarte si se puede ser segmentar una tabla dinámica según el teorema de Pareto o sea mostrar el 20% de los ítem que explican el 80% de los valores y el resto agruparlo en un ítem "otros(as)" integrando el 100% de la muestra.

El principio de Pareto lleva el nombre del economista italiano que lo enunció, observó que:

la gente en su sociedad se dividía naturalmente entre los «pocos de mucho» y los «muchos de poco»; se establecían así dos grupos de proporciones 80-20 tales que el grupo minoritario, formado por un 20% de población, ostentaba el 80% de algo y el grupo mayoritario, formado por un 80% de población, el 20% de ese mismo algo.

Este principio es aplicado en muchas áreas, como la producción, logística y economía.

Supongamos que queremos analizar las ventas de una cadena a acuerdo al principio de Pareto (usaremos la base de datos Northwind incluida en el paquete de Office).

Antes de responder a la consulta de mi lector veamos cómo crear un histograma de Pareto con Excel.
Empezamos por resumir los datos por categoría en una tabla:

Esta matriz resume los datos que hemos organizados como “tabla” (En Excel 2003, Lista), a la que le hemos dado el nombre “tblVentas”

Para totalizar los datos por categoría usamos SUMAR.SI

=SUMAR.SI(tblVentas[Categoría],reporte!A4,tblVentas[Ventas])

Es importante notar que la matriz está ordenada de mayor a menor según el campo Ventas.

A partir de esta matriz, creamos este gráfico:

Como puede observarse usamos dos ejes Y. El primario para representar las ventas (columnas) y el secundario para el porcentaje acumulado (línea).

La ventaja de usar la funcionalidad “tabla” reside en que la matriz que resume los datos por categoría se actualiza automáticamente si agregamos o quitamos filas en la base de datos, y por lo tanto también el gráfico.

Podemos hacer lo mismo con tablas y gráficos dinámicos (para versiones anteriores a Excel 2010 ver esta nota). A partir de la base de datos creamos una tabla dinámica ordenada de mayor a menor según el campo Ventas

Ahora agregamos por segunda vez el campo Ventas en el área de valores y cambiamos “Mostrar valores en…” a “% del total en”

Todo lo que nos queda por hacer es crear el gráfico dinámico y hacer los cambios necesarios en el tipo de gráfico por serie.

Pero mi lector preguntaba si se puede hacer algo similar con tablas dinámicas, agrupando los datos en dos categorías: los productos que representan el 80% de las ventas y los que representan el restante 20%.

Una solución sencilla es agrupar los datos manualmente. Después de agrupar tenemos que hacer algunas correcciones al resultado

Tenemos que agregar subtotales y cambiar “Mostrar valores…” a “% del total general”

El archivo con los ejemplo se puede descargar aquí.