Crear un diagrama de Pareto en Excel (estático y dinámico)

Ver video – Cómo hacer un El diagrama de Pareto en Excel

El diagrama de Pareto se basa en el principio de Pareto (también conocido como la regla 80/20), que es un concepto muy conocido en la gestión de proyectos.

El 80 / 20 El valor porcentual puede variar, pero la idea es que de todos los problemas / esfuerzos, hay algunos que tienen como resultado el máximo impacto.

Este es un concepto ampliamente utilizado en la gestión de proyectos para priorizar el trabajo.

Crear un diagrama de Pareto en Excel

En este tutorial, le mostraré cómo hacer un:

• Diagrama de Pareto simple (estático) en Excel.
• Diagrama de Pareto dinámico (interactivo) en Excel.

Crear un diagrama de Pareto en Excel es muy fácil.

Todo el truco está oculto en cómo organiza los datos en el backend.

Tomemos un ejemplo de un hotel para el que los datos de las quejas podrían verse como se muestra a continuación:

NOTA: Para hacer un gráfico de Pareto en Excel, necesita tener los datos ordenados en orden descendente.

Descargue la plantilla de diagrama de Pareto de Excel

Creación de un diagrama de Pareto simple (estático) en Excel

Aquí están los Pasos para crear un diagrama de Pareto en Excel:

1. Configure sus datos como se muestra a continuación.
2. Calcule el porcentaje acumulado en la columna C. Utilice la siguiente fórmula: = SUM ($ B $ 2: B2) / SUM ($ B $ 2: $ B $ 1 )
3. Seleccione el conjunto de datos completo (A1: C10), vaya a Insertar – > Gráficos – > Columna 2-D – > Columna agrupada. Esto inserta un gráfico de columnas con 2 series de datos (número de quejas y porcentaje acumulado).
4. Haga clic con el botón derecho en cualquiera de las barras y seleccione Cambiar Tipo de gráfico de serie.
5. En el cuadro de diálogo Cambiar tipo de gráfico, seleccione Combo en el panel izquierdo.
6. Realice los siguientes cambios:
   • N.º de quejas: columna agrupada.
   • % acumulativo: línea (marque también la casilla de verificación del eje secundario).
7. Su diagrama de Pareto en Excel está listo. Ajuste los valores del eje vertical y el título del gráfico.

Cómo interpretar este diagrama de Pareto en Excel

Este Pareto El cuadro destaca los principales problemas en los que el hotel debe centrarse para clasificar el número máximo de quejas. Por ejemplo, la orientación de los primeros 3 problemas se ocuparía automáticamente de aproximadamente el 80% de las quejas.

Por ejemplo, la orientación de los primeros 3 problemas se ocuparía automáticamente de aproximadamente el 80% de las quejas.

Creación de un gráfico de Pareto dinámico (interactivo) en Excel

Ahora que tenemos un gráfico de Pareto estático / simple en Excel, vayamos un paso más allá y hagámoslo un poco interactivo.

Algo como se muestra a continuación:

En este caso, un usuario puede especificar el% de quejas que deben abordarse (usando el Excel scroll barra), y el gráfico resaltará automáticamente los problemas que se deben analizar.

La idea aquí es tener 2 barras diferentes.

La roja se resalta cuando el porcentaje acumulativo el valor está cerca del valor objetivo.

Estos son los pasos para hacer este gráfico de Pareto interactivo en Excel:

1. En la celda B14, tengo el valor objetivo que está vinculado a la barra de desplazamiento (cuyo valor varía de 0 a 100).
2. En la celda B12, he usado la fórmula = B14 / 100. Como no puede especificar un valor porcentual en una barra de desplazamiento, simplemente dividimos el valor de la barra de desplazamiento (en B14) por 100 para obtener el valor porcentual.
3. En la celda B13, ingrese la siguiente combinación de funciones INDICE, COINCIDIR y SIERROR:
   = SIERROR (INDICE ($ C $ 2: $ C $ 10, SIERROR (COINCIDIR ($ B $ 12, $ C $ 2: $ C $ 10,1 ), 0) +1), 1)
   Esta fórmula devuelve el valor acumulativo que cubriría el valor objetivo. Por ejemplo, si tiene el valor objetivo como 70%, devolvería 77%, lo que indica que debe intentar resolver los tres primeros problemas.

1. En la celda D2, ingrese la siguiente fórmula (y arrastre o copie para todas las celdas – D2: D10):
   = IF ($ B $ 13 > = C2, B2, NA ())
2. En la celda E2 ingrese la siguiente fórmula (y arrastre o copie para todas las celdas – E2: E10):
   = SI ( $ B $ 13 < C2, B2, NA ())
3. Seleccione los datos en las columnas A, C, D & E (presione control y seleccione con el mouse).
4. Vaya a Insertar – > Gráficos – > Columna 2D: > Columna agrupada.Esto insertará un gráfico de columnas con 3 series de datos (porcentaje acumulado, las barras que se resaltarán para cumplir con el objetivo y el resto de las barras restantes)
5. Haga clic con el botón derecho en cualquiera de las barras y seleccione Cambiar gráfico de serie Escriba.
6. En el cuadro de diálogo Cambiar tipo de gráfico, seleccione Combo en el panel izquierdo y realice los siguientes cambios:
   • % acumulativo: Línea (también marque la casilla de verificación Eje secundario).
   • Barras resaltadas: columna agrupada.
   • Barras restantes: columna agrupada.
7. Haga clic con el botón derecho en cualquiera de las barras resaltadas. y cambie el color a rojo.

¡Eso es todo!

Ha creado un diagrama de Pareto interactivo en Excel.

Ahora, cuando cambie el objetivo usando la barra de desplazamiento, el diagrama de Pareto se actualizaría en consecuencia.

Descargue la plantilla de diagrama de Pareto de Excel

¿Utiliza el diagrama de Pareto en Excel?

Me encantaría conocer su opinión sobre esta técnica y cómo ha lo he usado. Deje sus huellas en la sección de comentarios 🙂

Tutoriales de gestión de proyectos y gráficos relacionados:

• Análisis de las quejas de restaurantes usando el diagrama de Pareto.
• Creación de un Gantt Gráfico en Excel.
• Creación de un gráfico de hitos en Excel.
• Creación de un histograma en Excel.
• Plantilla de calculadora de hoja de tiempo de Excel.
• Plantilla de seguimiento de ausencias de empleados.
• Calcular el promedio ponderado en Excel.
• Crear una curva de campana en Excel.
• Gráficos avanzados de Excel
• Cómo para agregar un eje secundario en gráficos de Excel.