Esta guía sobre cómo crear un diagrama de Pareto solo se aplica si está trabajando con una versión de Excel anterior a 2016. Desde la versión Excel 2016, el El diagrama de Pareto se crea directamente desde la cinta. sin realizar todos los pasos que se describen a continuación.
Construir un diagrama de Pareto no es muy complicado y se basa en 2 tareas distintas
- En primer lugar debes presentar los valores numéricos de una forma muy precisa.
- Luego, debes construir una gráfica compuesta por 2 series; un histograma y una curva
¿Por qué hacer un diagrama de Pareto?
Un diagrama de Pareto te permite visualizar de un vistazo, los factores o causas que actúan favorablemente o no sobre un resultado. Para aclarar, partiremos de la siguiente tabla en la que se rastrean las respuestas a un cuestionario para determinar los factores que contribuyen a tener nuevos clientes.
Si observamos los resultados, vemos que la reputación del establecimiento es la primera razón. ¿Pero influyen también otros factores?
Sólo un diagrama de Pareto puede respondernos 😊😉
En effet, la acumulación de factores que suman el 80% se consideran significativos. Por tanto, crearemos un gráfico que nos permitirá resaltar los factores significativos con un color diferente.
Tenga en cuenta que en el gráfico anterior, los colores de los primeros histogramas no se han modificado manualmente. Ellos aparecen automáticamente en función de los valores leer.
Construcción de fórmulas
Paso 1: ordenar los datos en orden descendente
Primero, debes ordenar los datos en orden descendente.
- Coloque su celda activa en una de las celdas de su tabla
- Activar el menú Datos>Ordenar
- Elija la columna de clasificación Responder
- Luego ordenar Del más grande al más pequeño
Los datos ahora se presentan de la siguiente manera:
Paso 2: Cálculo de la suma
Nada complicado aquí. Sólo necesitas calcular la suma de las respuestas usando la función de suma. En nuestro libro de trabajo, la fórmula está escrita:
=SUMA(B4:B10)
Paso 3: Cálculo de porcentajes
Usaremos la suma calculada en el paso anterior para calcular el porcentaje de cada respuesta. El objetivo es medir el “peso” ¿Qué representa cada respuesta en el total?
Para el primer porcentaje, escribimos la fórmula:
= B4 / $ B $ 11
Paso 4: copia la fórmula
Ahora, debemos copiar esta fórmula para las otras celdas. Esta operación se realiza de forma muy sencilla con una de las técnicas para copiar una celda.
Por eso rodeamos la referencia B11 (el cálculo de la suma) con $. Los dólares bloquean una referencia de celda para evitar que las referencias cambien cuando se copian. Para obtener más ejemplos sobre referencias bloqueadas y desbloqueadas, puedes leer el siguiente artículo.
También podemos cambiar el formato del número para mostrar el símbolo de porcentaje.
Paso 5: Cálculo de los porcentajes acumulados
El último paso (y sí ya 😍) consiste en realizar la suma acumulada de los porcentajes. Para esto usaremos la técnica utilizada en este artículo para obtener el resultado final.
Creando el diagrama de Pareto
Ahora usaremos estos datos para construir el diagrama de Pareto. Y para crearlo más fácilmente, Se recomienda seleccionar todos sus datos antes de insertar el gráfico..
Gráficos recomendados
Desde Excel 2013, una función muy inteligente le permite "adivinar" el gráfico que mejor se adapta a sus datos. este es el menú Insertar > Gráficos recomendados.
Dado que un gráfico de Pareto es un gráfico "estándar", Excel no tiene problemas para comprender que sus datos se ajustan a este tipo de gráfico.
Es por eso que Excel le presenta como segunda opción en la lista de gráficos recomendados, un gráfico que se parece exactamente a un diagrama de Pareto. Es decir :
- Un histograma de los resultados recopilados durante la encuesta.
- Una curva que muestra la acumulación de porcentajes.
Por supuesto, no todo es perfecto con este gráfico y serán necesarios algunos ajustes. Pero, gracias a esta opción, los pasos 1 y 3 que se explican a continuación ya se realizarán automáticamente 😍😉😎
Paso 1: crear el histograma
Si no sigue el asistente de gráficos recomendado, aquí están todos los pasos detallados. Comience activando el menú Insertar > Histograma > Histograma agrupado
El gráfico se ve así
Paso 2: Eliminar la serie "Porcentaje"
En un diagrama de Pareto, la columna de cálculo de porcentaje no es necesaria. Por lo tanto, lo eliminaremos de nuestro gráfico.
- Haga clic derecho en el gráfico
- Menú Seleccionar datos
- Desmarca la serie porcentaje
Paso 3: cambiar la serie Cumul a curva
Ahora nos centraremos en la serie Cumul para transformar el histograma en una curva.
- Haga clic derecho en el gráfico
- Activar el menú Cambiar tipo de gráfico
- En el menú de la izquierda, seleccione el menú Gráfico combinado
- para la serie Acumulación usted
- Seleccionar tipo de gráfico Curva
- Marque la casilla del eje secundario.
Paso 4: Agregar etiquetas de datos
Hay varias formas de agregar etiquetas a un gráfico en Excel, pero la más rápida es
- Habilitar gráfico
- Muévase hacia la derecha y haga clic en + para mostrar las opciones del gráfico.
- Marque la casilla Etiquetas de datos
Paso 5: cambie el formato
Todo lo que tenemos que hacer ahora es mejorar el formato de las barras y la curva para finalizar nuestro diagrama.
Aquí hay un consejo para seleccionar fácilmente un elemento de un gráfico, el más simple es
- Ir al menú Formato
- A la izquierda, activa el menú desplegable.
- Seleccione el elemento que desea editar
- Haga clic en el botón Formatear la selección
Para el histograma necesita cambiar los siguientes parámetros
- Opciones de serie
- Ancho del intervalo: 0%
- Superposición de series: 0%
- relleno
- Color: azul (este es el color más utilizado para un diagrama de Pareto)
- frontera
- Línea completa
- De color negro
- Grosor: 0,75 puntos
para la curva
- Llenando la linea
- Color: rojo (este es el color más utilizado para un diagrama de Pareto)
- Ancho: 1,5 puntos
- Suavizado marcado (la opción en la parte inferior)
- marcador
- Color rojo
- Tipo: Diamante
- Tamaño: 8
Cambie el título del gráfico y ahora obtendrá el siguiente gráfico
Cambia el color de las barras que representan el 80%.
Las características de un diagrama de Pareto es mostrar la participación que representa el 80% datos. Esto se puede hacer manualmente, pero al actualizar todo el formato del gráfico deberá rehacerse.
Por lo tanto, es mejor transformar el gráfico para que el color de las barras cambie automáticamente según los datos. Aquí está el método.
Paso 1: agregar una columna condicional
Comenzaremos agregando una columna que mostrará datos menores o iguales al 80% del total. Obviamente usaremos el Función SI para hacer este trabajo.
Pero ojo, la fórmula no es tan sencilla como parece.
De hecho, si escribimos la siguiente fórmula nunca incluiremos el elemento que llegue al 80% (o 0,8).
Por lo tanto, debemos enriquecer la prueba para
- Determine cuando la celda de la columna anterior es inferior al 80%.
- O, si el valor de la columna anterior es mayor al 80%, la celda anterior debe ser menor al 80%
=SI(OU(D4<=0,8;ET(D3<=0,8;D4>0,8));B4;"")
Paso 2: agregue la nueva serie al gráfico
Para agregar una serie a cualquier gráfico y para cualquier versión de Excel, simplemente:
- Copie el rango de datos que contiene los valores para agregar al gráfico sin el titulo de la serie
- Seleccionar gráfico
- Pegar (Ctrl + V) 😉
Paso 3: cambia la serie a un histograma
En el paso 3 de la creación del gráfico, vimos cómo cambiar el eje y el tipo de gráfico fácilmente. Usaremos este método para
- Pon la serie en forma de histograma.
- No poner la serie en eje secundario.
El gráfico ahora tiene este aspecto (se ha inicializado el espacio entre los histogramas).
Paso 4: Superposición de histogramas
Ese es el truco 💡. Superpondremos los histogramas para que la nueva serie quede encima de la anterior.
- Seleccionar nueva serie
- En las opciones de la serie.
- Superposición: 100%
- Ancho del intervalo: 0%
Paso 5: Quitar etiquetas
Este paso no es el más complicado pero no se debe omitir.
Es importante quitar las etiquetas de la nueva serie. No aportan ninguna información adicional porque son estrictamente idénticas a la columna B.
- Selecciona la nueva serie
- Ir al menú de opciones del gráfico
- Desmarque la opción Etiquetas de datos
Paso 6: Cambia los colores para completar el diagrama.
Te dejaré revelar tus talentos en diseño gráfico para cambiar los colores de los histogramas y la curva. Te propongo este resultado.
14/10/2024 a las 15:50
GRACIAS POR LAS EXPLICACIONES. ES MUY AGRADABLE DE TU PARTE