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í.