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

domingo, junio 26, 2011

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

11 comments:

Jim McLean 27 junio, 2011 12:03  

Excelente, como siempre

Carlos Véliz 30 junio, 2011 06:11  

Muchas gracias Jorge, muy buena tu solución.-

Suerte !!

Anónimo,  03 julio, 2011 00:03  

De antemano muchas gracias Jorge, tengo una duda, para poder realizar un grafico dinamico de pareto de estos datos hace falta una columna donde acumule los porcentajes, sabes si esto es posible en una tabla dinamica?.

Jorge L. Dunkelman 03 julio, 2011 21:17  

Pero si es lo que muestro en la nota! Por favor, relee la nota; también puedes descargar el archivo con el ejemplo.

Anónimo,  06 julio, 2011 02:08  

Hola Jorge!!, ya descubri que es lo que ocurre, la opcion "mostrar valores como - % del total en" no se encuentra en el excel 2007 :(.

Jorge L. Dunkelman 06 julio, 2011 07:10  

Tienes razón. Estaré publicando una solución en breve.

Catarsis 16 octubre, 2011 15:46  

Aunque aun no los he revisado me parece que tus contribuciones son muy utiles. Gracias.

Anónimo,  01 agosto, 2012 19:40  

Buena la explicación, pero el instructor parece no tener claro qué es un histograma. Aquí hay una gráfica sencilla con dos ejes, no un histograma que tiene que ver con frecuencia de ocurrencia de un evento. Saludos.-

Jorge L. Dunkelman 02 agosto, 2012 07:08  

Yo creo que el instructor tiene bastante claro el concepto a juzgar por lo que parece en esta nota y también en esta otra.

Anónimo,  16 noviembre, 2012 16:40  

Hola, Una molestía Instructor, ya investigaste la solución en Excel 2007? Gracias.

Jorge L. Dunkelman 16 noviembre, 2012 18:02  

En la nota hay un enlace a la entrada que publique en julio del 2011 sobre el tema.

Publicar un comentario

Google+ Followers

Seguidores

Google+ Badge

Términos Legales

  © Blogger template On The Road by Ourblogtemplates.com 2009

Back to TOP