Cómo hacer un diagrama de Pareto en Excel (estático e interactivo)

Ver video: Cómo hacer un 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 bien conocido en la gestión de proyectos.

De acuerdo con este principio, ~ 80% de los problemas se pueden atribuir a aproximadamente ~ 20% de los problemas (o ~ 80% de sus resultados podrían ser un resultado directo de ~ 20% de sus esfuerzos, etc.).

El valor porcentual del 80/20 puede variar, pero la idea es que de todos los problemas / esfuerzos, hay algunos que dan 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, te mostraré cómo hacer:

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

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

Todo el truco está oculto en la forma en que 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 diagrama de Pareto en Excel, necesita tener los datos ordenados en orden descendente.

Crear un diagrama de Pareto simple (estático) en Excel

Estos son los pasos para crear un gráfico 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: = SUMA ($ B $ 2: B2) / SUMA ($ 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:
    • # de quejas: columna agrupada.
    • % Acumulado: línea (marque también la casilla de verificación Eje secundario).[Si utiliza Excel 2010 o 2007, será un proceso de dos pasos proceso. Primero, cambie el tipo de gráfico a gráfico de líneas. Luego haga clic derecho en el gráfico de líneas y seleccione Formato de serie de datos y seleccione Eje secundario en Opciones de serie]
  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 diagrama de Pareto destaca los principales problemas en los que el hotel debe centrarse para clasificar el número máximo de quejas. Por ejemplo, enfocarse en los primeros 3 problemas se ocuparía automáticamente de aproximadamente el 80% de las quejas.

Por ejemplo, enfocarse en 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 la barra de desplazamiento de Excel), y el gráfico resaltará automáticamente los problemas que deben ser examinados.

La idea aquí es tener 2 barras diferentes.

El rojo se resalta cuando el valor porcentual acumulado 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. Dado que 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 e 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):
    = SI ($ 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<>
  3. Seleccione los datos en las columnas A, C, D y E (presione el control y seleccione con el mouse).
  4. Vaya a Insertar -> Gráficos -> Columna 2-D -> 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 barras restantes)
  5. Haga clic con el botón derecho en cualquiera de las barras y seleccione Cambiar tipo de gráfico de serie.
  6. En el cuadro de diálogo Cambiar tipo de gráfico, seleccione Combo en el panel izquierdo y realice los siguientes cambios:
    • % Acumulado: línea (marque también 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!

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á en consecuencia.

¿Utiliza el diagrama de Pareto en Excel?

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

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

Va a ayudar al desarrollo del sitio, compartir la página con sus amigos

wave wave wave wave wave