Cree un gráfico de Pareto en Excel

En Excel, un gráfico de Pareto está compuesto por un gráfico de columnas y uno de líneas, donde las barras de 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 más significativas de defectos en un producto y determinar las mejoras que incrementan el valor de una empresa. Para crear un gráfico de Pareto que muestre las razones más comunes de fallos o defectos de productos en una hoja de Excel, este artículo le ayudará.

• Cree un gráfico de Pareto sencillo en Excel 2016 y versiones posteriores
• Cree un gráfico de Pareto sencillo en Excel 2013 y versiones anteriores
• Cree un gráfico de Pareto dinámico en Excel
• Descargue el archivo de ejemplo del gráfico de Pareto
• Vídeo: Cree un gráfico de Pareto en Excel

Cree un gráfico de Pareto sencillo en Excel 2016 y versiones posteriores

Si dispone de Excel 2016 o una versión posterior, podrá aprovechar la función integrada llamada Gráfico de Pareto para insertar este tipo de gráfico de forma rápida y sencilla. Siga estos pasos:

1. Seleccione el rango de datos a partir del cual desea crear un gráfico de Pareto.

2. A continuación, haga clic en Insertar > Insertar gráfico estadístico > Pareto. Consulte la captura de pantalla:

3. A continuación, se generará de inmediato un gráfico de Pareto, como se muestra en la siguiente captura de pantalla:

4. A continuación, puede editar el título del gráfico y añadir las etiquetas de datos según sus necesidades; consulte la captura de pantalla:

Cree un gráfico de Pareto sencillo en Excel 2013 y versiones anteriores

Si utiliza Excel 2013 o versiones anteriores, debe aplicar los siguientes pasos uno a uno:

Primero, prepare los datos para crear el gráfico de Pareto:

1. Ordene los datos en orden descendente: seleccione la celda B2 y, a continuación, haga clic en Datos > Ordenar de Z a A. Consulte la captura de pantalla:

2. A continuación, el rango de datos se habrá ordenado en orden descendente; consulte la captura de pantalla:

3. A continuación, calcule el recuento acumulado introduciendo la fórmula =B2 en la celda C2 (en este caso) y pulse la tecla Entrar. Consulte la captura de pantalla:

4. A continuación, introduzca la fórmula =C2+B3 en la celda C3 y arrastre el controlador de relleno hacia abajo hasta las celdas deseadas. Consulte las capturas de pantalla:

5. Tras obtener el recuento acumulado, calcule el porcentaje acumulado introduciendo la siguiente fórmula: =C2/$C$11 en la celda D2 y arrastre el controlador de relleno hacia abajo hasta las celdas necesarias; consulte la captura de pantalla:

6. A continuación, convierta los valores decimales en porcentajes: seleccione las celdas con fórmulas y, después, haga clic en Inicio > Estilo de porcentaje. Consulte la captura de pantalla:

Segundo, cree el gráfico a partir de los datos

7. Tras preparar los datos, selecciona las columnas A, B y D manteniendo pulsada la tecla Ctrl. Consulta la captura de pantalla:

8. A continuación, haga clic en Insertar > Insertar gráfico de columnas o gráfico de barras > Columnas agrupadas. Consulte la captura de pantalla:

9. Obtendrá un gráfico como el que se muestra en la siguiente captura de pantalla:

10. A continuación, haga clic con el botón derecho en una barra roja (porcentaje acumulado) y elija Cambiar tipo de gráfico en el menú contextual; consulte la captura de pantalla:

11. En el cuadro de diálogo Cambiar tipo de gráfico, en la pestaña Todos los gráficos, haga clic en la opción Combinado del panel izquierdo. En el cuadro de lista Elija el tipo de gráfico y el eje para sus series de datos, haga clic en la lista desplegable del campo Acumulado, seleccione el tipo de gráfico Línea con marcadores y active la casilla correspondiente; consulte la captura de pantalla:

12. A continuación, haga clic en el botón Aceptar y obtendrá un gráfico como el que se muestra en la siguiente captura de pantalla:

13. A continuación, haga clic con el botón derecho en el eje de porcentajes y seleccione Formato de eje en el menú contextual; consulte la captura de pantalla:

14. En el panel Formato de eje, en la pestaña Opciones de eje, junto a Máximo, establezca el número en 1,0 en el cuadro de texto y el gráfico de Pareto se habrá creado correctamente; consulte la captura de pantalla:

15. Por último, puede modificar el título del gráfico y añadir las etiquetas de datos según sus necesidades.

Cree un gráfico de Pareto dinámico en Excel

En esta sección te explicaré cómo crear un gráfico de Pareto dinámico mediante una barra de desplazamiento. Al ajustar el valor objetivo —haciendo clic o arrastrando la barra—, el gráfico resaltará automáticamente la barra correspondiente a los problemas, tal como se muestra en la siguiente demostración.

Para crear este tipo de gráfico de Pareto, siga paso a paso las instrucciones siguientes:

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

2. A continuación, arrastre el controlador de relleno hasta las celdas correspondientes y formatee los números decimales como porcentajes; consulte la captura de pantalla:

3. A continuación, necesitará tres celdas auxiliares denominadas Objetivo, Valor acumulado y Valor vinculado a la barra de desplazamiento para realizar algunos cálculos, tal como se muestra en la siguiente captura de pantalla:

4. Tras crear las tres celdas, haga clic en Desarrollador > Insertar > Barra de desplazamiento (control de formulario) y dibuje una barra de desplazamiento como se muestra en las siguientes capturas de pantalla:

5. A continuación, haz clic con el botón derecho en la barra de desplazamiento y selecciona Formato de control en el menú contextual. En el cuadro de diálogo emergente Formato de objeto, ve a la pestaña Control, configura los valores correspondientes y especifica la celda vinculada tal como se muestra en la siguiente captura de pantalla:

6. A continuación, haga clic en el botón Aceptar para cerrar el cuadro de diálogo. Ahora, introduzca las dos fórmulas siguientes en las celdas B14 y B15, respectivamente, y después aplíqueles formato de porcentaje:

7. Ahora debe crear dos columnas auxiliares junto a los datos originales e introducir las siguientes fórmulas en las celdas D2 y E2:

8. A continuación, copie las fórmulas en el resto de las celdas, tal como se muestra en la siguiente captura de pantalla:

9. Tras crear los datos, selecciona las columnas A, C, D y E manteniendo pulsada la tecla Ctrl. A continuación, haz clic en Insertar > Insertar gráfico de columnas o gráfico de barras > Columnas agrupadas, y se insertará un gráfico como el que se muestra en la siguiente captura de pantalla:

10. A continuación, seleccione el gráfico y haga 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, haga clic en Combinado en el panel izquierdo y, a continuación, haga clic en el gráfico Combinación personalizada. Luego, en la sección Elija el tipo de gráfico y el eje para sus series de datos, especifique las siguientes operaciones para cada serie de datos:

• % acumulado: seleccione el tipo de gráfico de línea y active también la casilla de verificación del eje secundario.
• Barras resaltadas: tipo Gráfico de columnas agrupadas;
• Barras restantes: tipo gráfico de columnas agrupadas.

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

12. A continuación, haz clic con el botón derecho en el eje de porcentajes y selecciona Formato del eje. En el panel expandido Formato del eje, bajo la pestaña Opciones del eje, junto a Máximo, establece el valor en 1,0 en el cuadro de texto. Consulta la captura de pantalla:

13. A continuación, haz clic con el botón derecho en cualquier serie de barras del gráfico y selecciona Formato de serie de datos. En el panel Formato de serie de datos, bajo la pestaña Opciones de serie, introduce 100 % en el cuadro de texto Superposición de series. Consulta la captura de pantalla:

14. Por último, mueva la barra de desplazamiento situada bajo el gráfico e introduzca esta fórmula:=«Target »&TEXT(B14,"0 %") en una celda situada bajo la barra de desplazamiento para obtener el valor porcentual objetivo. Vea la captura de pantalla:

15. ¡Ya tienes tu gráfico de Pareto dinámico listo! Puedes personalizar el color de relleno de la barra resaltada según tus necesidades, y al ajustar el valor objetivo con la barra de desplazamiento, el gráfico de Pareto se actualizará automáticamente, tal como se muestra en la siguiente demostración:

Descargue el archivo de ejemplo del gráfico de Pareto

Vídeo: Crear un gráfico de Pareto en Excel