Crear un diagrama de Pareto en Excel

En Excel, un diagrama de Pareto está compuesto por gráficos de columnas y líneas, donde las barras de las columnas representan los valores de frecuencia en orden descendente, y la línea indica los totales acumulados en un eje secundario. Se utiliza para analizar las áreas significativas de defectos en un producto y determinar las mejoras que aumentan el valor de una empresa. Para crear un diagrama de Pareto que muestre las razones más comunes de fallos o defectos del producto en una hoja de cálculo de Excel, este artículo te ayudará.

• Crear un diagrama de Pareto simple en Excel 2016 y versiones posteriores
• Crear un diagrama de Pareto simple en Excel 2013 y versiones anteriores
• Crear un diagrama de Pareto dinámico en Excel
• Descargar archivo de ejemplo de Diagrama de Pareto
• Video: Crear un diagrama de Pareto en Excel

Crear un diagrama de Pareto simple en Excel 2016 y versiones posteriores

Si tienes Excel 2016 y versiones posteriores, hay una función integrada llamada Diagrama de Pareto, que puede ayudarte a insertar un diagrama de Pareto rápidamente y fácilmente. Por favor, sigue estos pasos:

1. Selecciona el rango de datos sobre el cual deseas crear un diagrama de Pareto.

2. Luego, haz clic en Insertar > Insertar Gráfico Estadístico > Pareto, ver captura de pantalla:

3. Y entonces, se habrá creado un diagrama de Pareto inmediatamente como se muestra en la siguiente captura de pantalla:

4. Luego, puedes editar el título del gráfico y agregar etiquetas de datos según sea necesario, ver captura de pantalla:

Crear un diagrama de Pareto simple en Excel 2013 y versiones anteriores

Si estás utilizando Excel 2013 y versiones anteriores, debes aplicar los siguientes pasos uno por uno:

Primero, prepara los datos para crear el diagrama de Pareto:

1. Debes ordenar los datos en orden descendente, selecciona la celda B2, y luego haz clic en Datos > Ordenar Z a A, ver captura de pantalla:

2. Y luego el rango de datos se ha ordenado en orden descendente, ver captura de pantalla:

3. Luego, calcula el Conteo Acumulado escribiendo esta fórmula =B2 en la celda C2 en este caso, y presiona la tecla Enter. Ver captura de pantalla:

4. Y luego, introduce esta fórmula =C2+B3 en la celda C3, y arrastra el controlador de relleno hacia abajo hasta las celdas necesarias, ver capturas de pantalla:

5. Después de obtener el conteo acumulado, continúa calculando el porcentaje acumulado, introduce esta fórmula: =C2/$C$11 en la celda D2, y arrastra el controlador de relleno hacia abajo hasta las celdas que necesites, ver captura de pantalla:

6. Luego, deberías convertir los valores decimales a valores porcentuales, selecciona las celdas con fórmulas, y luego haz clic en Inicio > Estilo de Porcentaje, ver captura de pantalla:

Segundo, crea el gráfico basado en los datos

7. Después de preparar los datos, selecciona los datos en la columna A, columna B y columna D manteniendo presionada la tecla Ctrl, ver captura de pantalla:

8. Luego, haz clic en Insertar > Insertar Gráfico de Columnas o Barras > Columnas Agrupadas, ver captura de pantalla:

9. Y obtendrás un gráfico como se muestra en la siguiente captura de pantalla:

10. Luego, haz clic derecho en una barra roja (Porcentaje Acumulado) y elige Cambiar Tipo de Gráfico de Serie desde el menú contextual, ver captura de pantalla:

11. En el cuadro de diálogo Cambiar Tipo de Gráfico, bajo la pestaña Todos los Gráficos, haz clic en la opción Combinado en el panel izquierdo, en el cuadro de lista Elegir el tipo de gráfico y eje para tus series de datos, haz clic en la lista desplegable del campo Acumulado, y elige el tipo de gráfico Línea con Marcadores, y marca la casilla de verificación, ver captura de pantalla:

12. Luego, haz clic en el botón Aceptar, y obtendrás un gráfico como se muestra en la siguiente captura de pantalla:

13. Luego, haz clic derecho en el eje de porcentaje, y luego elige la opción Formato de Eje desde el menú contextual, ver captura de pantalla:

14. En el panel Formato de Eje, bajo la pestaña Opciones del Eje, al lado de Máximo, establece el número en 1.0 en el cuadro de texto, y el diagrama de Pareto se habrá creado con éxito, ver captura de pantalla:

15. Por último, puedes cambiar el título del gráfico y agregar etiquetas de datos según sea necesario.

Crear un diagrama de Pareto dinámico en Excel

En esta sección, hablaré sobre cómo crear un diagrama de Pareto dinámico utilizando una barra de desplazamiento. Cuando cambias el valor objetivo haciendo clic o moviendo la barra de desplazamiento, el gráfico resaltará automáticamente la barra de los problemas como se muestra en la siguiente demostración.

Para crear este tipo de diagrama de Pareto, sigue paso a paso lo siguiente:

1. Primero, calcula el porcentaje acumulado en la columna C, aplica esta fórmula en la celda C2:

2. Luego arrastra el controlador de relleno hacia abajo hasta las celdas, y formatea los números decimales como valores porcentuales, ver captura de pantalla:

3. A continuación, necesitas tres celdas auxiliares que son Objetivo, Valor Acumulado, Valor Vinculado de la Barra de Desplazamiento para realizar algunos cálculos como se muestra en la siguiente captura de pantalla:

4. Después de crear las tres celdas, haz clic en Programador > Insertar > Barra de Desplazamiento (Control de Formulario), y luego dibuja una barra de desplazamiento como se muestra en las siguientes capturas de pantalla:

5. Luego, haz clic derecho en la barra de desplazamiento, y elige Formato de Control desde el menú contextual, en el cuadro de diálogo emergente Formato de Objeto, bajo la pestaña Control, establece los valores relativos y especifica la celda vinculada como se muestra en la siguiente captura de pantalla:

6. Luego, haz clic en el botón Aceptar para cerrar el cuadro de diálogo. Ahora, escribe las siguientes dos fórmulas en las celdas B14 y B15 respectivamente, y luego formatea los valores como porcentajes:

7. Y ahora, deberías crear dos columnas auxiliares junto a los datos originales, e ingresar las siguientes dos fórmulas en las celdas D2 y E2:

8. Luego copia las fórmulas a otras celdas como se muestra en la siguiente captura de pantalla:

9. Después de crear los datos, selecciona los datos en la columna A, columna C, columna D, columna E manteniendo presionada la tecla Ctrl, luego haz clic en Insertar > Insertar Gráfico de Columnas o Barras > Columnas Agrupadas, y se insertará un gráfico como se muestra en la siguiente captura de pantalla:

10. Luego, selecciona el gráfico, y haz clic en Diseño > Cambiar Tipo de Gráfico, en el cuadro de diálogo Cambiar Tipo de Gráfico, bajo la pestaña Todos los Gráficos, haz clic en Combinado en el panel izquierdo, y luego haz clic en Gráfico Combinado Personalizado, luego en la sección Elegir el tipo de gráfico y eje para tus series de datos, especifica las siguientes operaciones para cada serie de datos:

• % Acumulado: Tipo de gráfico de línea, y marca la casilla de verificación Eje Secundario también;
• Barras Resaltadas: Tipo de gráfico de Columnas Agrupadas;
• Barras Restantes: Tipo de gráfico de Columnas Agrupadas.

11. Luego, haz clic en el botón Aceptar para cerrar el cuadro de diálogo, y obtendrás un gráfico como se muestra en la siguiente captura de pantalla:

12. Luego, haz clic derecho en el eje de porcentaje, y luego elige la opción Formato de Eje, en el panel expandido Formato de Eje, bajo la pestaña Opciones del Eje, al lado de Máximo, establece el número en 1.0 en el cuadro de texto, ver captura de pantalla:

13. Luego, sigue haciendo clic derecho en cualquier serie de barras en el gráfico, y luego elige Formato de Serie de Datos, en el panel Formato de Serie de Datos, bajo la pestaña Opciones de Serie, ingresa 100% en el cuadro de texto Superposición de Series, ver captura de pantalla:

14. Por último, puedes mover la barra de desplazamiento debajo del gráfico, e ingresar esta fórmula: ="Objetivo "&TEXTO(B14,"0%") en una celda debajo de la barra de desplazamiento para obtener el valor porcentual objetivo, ver captura de pantalla:

15. Ahora, se ha creado el diagrama de Pareto dinámico, puedes cambiar el color de relleno de la barra resaltada según sea necesario, y cuando cambies el valor objetivo usando la barra de desplazamiento, el diagrama de Pareto se actualizará automáticamente como se muestra en la siguiente demostración:

Descargar archivo de ejemplo de Diagrama de Pareto

Video: Crear un diagrama de Pareto en Excel