Hacer un diagrama de Pareto en Excel

El tutorial explica los conceptos básicos del análisis de Pareto y muestra cómo hacer un diagrama de Pareto en diferentes versiones de Excel.

En un mundo perfecto, todo estaría en armonía: cada trabajo pagaría lo mismo, cada contribuyente obtendría los mismos beneficios fiscales, cada jugador sería igualmente importante para un equipo. Pero nuestro mundo no es perfecto y las relaciones entre entradas y salidas no son iguales. ¿Quieres conocer las principales causas a las que deberías dedicar más esfuerzos? De eso se trata el principio de Pareto, o la ley de los pocos vitales.

Análisis de Pareto en Excel
Diagrama de Pareto en Excel
Cómo crear un gráfico de Pareto en Excel 2016
Cómo hacer un gráfico de Pareto en Excel 2013
Cómo dibujar un gráfico de Pareto en Excel 2010

Análisis de Pareto en Excel

El análisis de Pareto se basa en el principio de Pareto, que lleva el nombre del economista italiano Vilfredo Pareto. Y este principio establece que para muchos eventos alrededor del 80% de los efectos provienen del 20% de las causas. Por eso, el principio de Pareto a veces se llama la regla 80/20.

Aquí hay algunos ejemplos prácticos del principio de Pareto:

En economía, los 20 más ricos El% de la población mundial controla aproximadamente el 80% de los ingresos del mundo.
En medicina, se informa que el 20% de los pacientes utilizan el 80% de los recursos de atención médica.
En el software, el 20% de los errores causan el 80% de los errores y fallas.

Para identificar los factores más importantes en los que debe concentrarse, puede dibujar un diagrama de Pareto en su hoja de cálculo de Excel.

Diagrama de Pareto en Excel

El diagrama de Pareto, también llamado diagrama de Pareto, es un gráfico basado en el principio de Pareto. En Microsoft Excel, es una especie de histograma ordenado que contiene barras verticales y una línea horizontal. Las barras, trazadas en orden descendente, representan la frecuencia relativa de los valores y la línea representa el porcentaje total acumulado.

A continuación, se muestra cómo se ve un gráfico Pareto típico de Excel:

Como puede ver, el gráfico de Pareto resalta los elementos principales en un conjunto de datos y muestra la importancia relativa de cada elemento para el total. A continuación encontrará las instrucciones detalladas sobre cómo para crear un diagrama de Pareto en diferentes versiones de Excel.

Cómo hacer un diagrama de Pareto en Excel 2016

Trazar un diagrama de Pareto en Excel 2016 es fácil porque tiene una función Tipo de gráfico de Pareto. Todo lo que necesita es una lista de elementos (problemas, factores, categorías, etc.) en una columna y su recuento (frecuencia) en otra columna.

Como ejemplo, estamos vamos a hacer un análisis de Pareto de las quejas típicas de los usuarios sobre el software basado en este conjunto de datos:

Para hacer un gráfico de Pareto en Excel, siga estos pasos simples:

Seleccione su tabla. En la mayoría de los casos, es suficiente seleccionar una sola celda y Excel seleccionará la tabla completa automáticamente.
En la pestaña Insertar, en el grupo Chats, haga clic en Gráficos recomendados.
Cambiar a la pestaña Todos los gráficos, seleccione Histograma en el panel izquierdo y haga clic en la miniatura de Pareto.
Haga clic en Aceptar.

¡Eso es todo! El gráfico de Pareto se inserta inmediatamente en una hoja de trabajo. La única mejora que probablemente querrá hacer es agregar / cambiar el título del gráfico:

Personalización del gráfico de Pareto de Excel

El gráfico de Pareto creado por Excel es totalmente personalizable. Puede cambiar los colores y el estilo, mostrar u ocultar etiquetas de datos, y más.

Diseñe el gráfico de Pareto a su gusto

Haga clic en cualquier parte de su gráfico de Pareto para que aparezcan las herramientas de gráficos en la cinta. Cambie a la pestaña Diseño y experimente con diferentes estilos y colores de gráficos:

Mostrar u ocultar etiquetas de datos

Por Por defecto, se crea un gráfico de Pareto en Excel sin etiquetas de datos. Si desea mostrar los valores de la barra, haga clic en el botón Elementos del gráfico en el lado derecho del gráfico, seleccione la casilla de verificación Etiquetas de datos y elija dónde desea colocar las etiquetas:

El eje vertical principal que muestra los mismos valores se ha vuelto superfluo y puede ocultarlo. Para ello, vuelva a hacer clic en el botón Elementos del gráfico y luego en la flecha pequeña junto a Ejes y anule la selección de la casilla Eje vertical principal.

El gráfico de Pareto resultante se verá similar a esto:

Cómo crear un gráfico de Pareto en Excel 2013

Excel 2013 no tiene una opción predefinida para el gráfico de Pareto, por lo que usaremos el tipo de gráfico Combo, que es el más cercano a lo que necesitamos. requerirá algunos pasos más porque todas las manipulaciones que Excel 2016 realiza detrás de la escena, tendrá que hacerlas manualmente.

Organizar datos para análisis de Pareto

Configure su conjunto de datos como explicado a continuación:

1.Calcule el porcentaje total acumulado

Agregue una columna más a su conjunto de datos e ingrese la fórmula del porcentaje total acumulado allí:

=SUM($B$2:B2)/SUM($B$2:$B$11)

Donde B2 es la primera y B11 es la última celda con datos en la columna Recuento.

En el dividendo, pones una fórmula de suma acumulativa que suma los números en la celda actual y en todas las celdas por encima de él. Luego, divide parte por total para obtener los porcentajes.

Ingresa la fórmula anterior en la primera celda y luego cópiala en la columna. Para que los resultados se muestren como porcentajes, configure el formato de porcentaje para la columna. Si desea que los porcentajes se muestren como números enteros, reduzca el número de lugares decimales a cero (consulte Cómo mostrar lugares decimales en Excel para obtener instrucciones).

2. Ordenar por recuento en orden descendente

Dado que las barras en un gráfico de Pareto deben trazarse en orden descendente, organice los valores en la columna Recuento de mayor a menor. Para esto, seleccione cualquier celda y haga clic en AZ en la pestaña Datos, en Ordenar y filtrar Si Excel solicita expandir la selección, hágalo para mantener las filas juntas mientras ordena.

Alternativamente, agregue un filtro automático para poder reordenar los datos más rápido en el futuro.

En este punto, sus datos de origen deberían verse similares a esto:

Dibuje un diagrama de Pareto

Con los datos de origen organizados correctamente, crear un gráfico de Pareto es tan fácil como 1-2-3. Literalmente, solo 3 pasos:

Seleccione su tabla o cualquier celda dentro de ella.
En la pestaña Insertar, en el grupo Chats, haga clic en Gráficos recomendados.
S En la pestaña Todos los gráficos, seleccione Combo en el lado izquierdo y realice las siguientes modificaciones:

Para la serie Count, seleccione Clustered Column (tipo predeterminado).
Para la serie% acumulativo, seleccione el tipo de línea y marque la casilla Eje secundario.

El gráfico que Excel inserta en su hoja de trabajo se parecerá a este:

Mejore el gráfico de Pareto

Su gráfico ya se parece mucho a un diagrama de Pareto, pero es posible que desee mejorar algunas cosas:

1. Establezca el valor de porcentaje máximo en 100%

De forma predeterminada, Excel ha establecido el valor máximo para el eje vertical secundario en 120%, mientras que lo queremos 100%.

Para cambiar esto, haga clic con el botón derecho en los valores de los porcentajes en el eje Y en el lado derecho y elija Dar formato a eje… En el panel Dar formato al eje, en Límites, establezca 1.0 en el cuadro Máximo:

2. Elimina el espacio adicional entre barras

En un gráfico de Pareto clásico, las barras se trazan más cerca entre sí que en un gráfico combinado. Para solucionar este problema, haga clic con el botón derecho en las barras y elija Dar formato a serie de datos… En el panel Dar formato a serie de datos, establezca el Ancho de espacio deseado, digamos 5%:

Por último, cambie el título del gráfico y, opcionalmente, oculte la leyenda del gráfico.

Lo que tiene ahora parece un gráfico Pareto de Excel perfecto:

Cómo dibujar un diagrama de Pareto en Excel 2010

Excel 2010 no tiene el tipo de diagrama Pareto ni Combo, pero eso no significa que no pueda dibujar un diagrama de Pareto en Excel anterior Versiones Por supuesto, esto requerirá un poco más de trabajo, pero también más diversión 🙂 Entonces, comencemos.

Organice sus datos como se explicó anteriormente: ordene por conteo en orden descendente y calcule porcentaje total acumulado.
Seleccione su tabla, vaya a la pestaña Insertar > grupo Gráficos y elija el tipo de gráfico de columnas agrupadas 2-D:

Esto insertará un gráfico de columnas con 2 series de datos (recuento y porcentaje acumulativo).
Haga clic con el botón derecho en las barras de% acumulativo, y haga clic en Cambiar tipo de serie de gráfico. (Esta puede ser la parte más complicada porque las barras son muy pequeñas. Intente pasar el mouse sobre las barras hasta que vea la sugerencia «Porcentaje acumulativo» de la serie y luego haga clic con el botón derecho).
En el cuadro de diálogo Cambiar tipo de gráfico, elija una línea
En este punto, tiene un gráfico de barras con una línea plana a lo largo del eje horizontal. Para darle una curva, debe colocar una eje vertical en el lado derecho. Para ello, haga clic con el botón derecho en la línea% acumulativo y, a continuación, haga clic en Dar formato a serie de datos…
En el cuadro de diálogo Dar formato a serie de datos, elija Eje secundario en Opciones de serie y cierre el cuadro de diálogo:
Haga los toques finales: establezca el valor máximo para el eje vertical secundario en 100%, haga las barras se ensanchan y, opcionalmente, ocultan la leyenda. Los pasos son básicamente los mismos que en Excel 2013 descritos anteriormente.

Voilà, su diagrama de Pareto en Excel 2010 está listo:

Así es como hacer un diagrama de Pareto en Excel. Si desea aprender sobre otros tipos de gráficos, le animo a que consulte los recursos a continuación.¡Gracias por leer y espero verte en nuestro blog de nuevo la próxima semana!

Cómo crear un diagrama de Pareto en Excel