Cómo crear Gráfico dinámico de Pareto en Excel
En este artículo, aprenderá a crear un gráfico de Pareto dinámico.
Un gráfico de Pareto, llamado así por Vilfredo Pareto, es un tipo de gráfico que contiene tanto barras como un gráfico de líneas, donde los valores individuales se representan en orden descendente mediante barras y el total acumulado está representado por la línea.
Entendamos con un ejemplo:
-
Tenemos datos de quejas de la escuela y necesitamos crear un diagrama de Pareto.
Necesitamos la columna auxiliar% acumulado en la columna C; fórmula en la celda C2 sería = SUMA ($ B $ 2: B2) / SUMA ($ B $ 2: $ B $ 11) * y arrastre la fórmula hacia abajo
Para crear Pareto dinámico; En el gráfico, necesitamos tres celdas en las que haremos algunos cálculos.En primer lugar, crearemos la barra de desplazamiento y la vincularemos a la celda B16. Haga clic con el botón derecho en la barra de desplazamiento y seleccione Control de formato e ingrese los valores como se muestra en la siguiente instantánea
En la celda B14 la fórmula es = B16 / 100 para calcular * Target
En la celda B15; tenemos la siguiente fórmula para *% acumulado
* = ÍNDICE ($ C $ 2: $ C $ 11, SIERROR (COINCIDIR ($ B $ 14, $ C $ 2: $ C $ 11,1), 0) +1)
-
Ahora necesitamos crear 2 columnas auxiliares más, es decir,% resaltado y% restante
En la celda D2 la fórmula es * = SI ($ B $ 15> = C2, B2, NA ())
En la celda E2 la fórmula es * = SI ($ B $ 15 <C2, B2, NA ())
-
Finalmente todo está configurado para crear el diagrama de Pareto; necesitamos seleccionar el rango A1: A11 y C1: E11
Desde la pestaña Diseño, haga clic en Tipo de gráfico Aplicar el tipo de gráfico de líneas a% acumulativo y haga clic en Eje secundario
Necesitamos hacer el% acumulado al 100% ya que muestra 120% Haga clic derecho en el eje secundario y seleccione Formato de eje * Seleccione el valor máximo como 1 en lugar de 1.2
Ahora estamos listos para ver el gráfico de Pareto dinámico; el único problema es que al hacer clic en la barra de desplazamiento, encontrará que la barra se desplazará de su posición original porque hay dos series. En Superposición de series ingrese 100%, consulte la siguiente instantánea
-
Para ver qué% objetivo está seleccionado; en la celda J24 ingrese la fórmula como
* = «Destino» & TEXTO (B14, «0%»)
De esta manera podemos hacer un gráfico de Pareto dinámico en el que a medida que aumenta o disminuye el valor de la barra de desplazamiento, se actualizará el objetivo.
