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