Representamos los datos con un gráfico de columnas apiladas
Dado que los montos de ventas mensuales son similares, no es fácil distinguir en qué mes se encuentra el máximo de ventas y en qué mes el mínimo.
Lo que queremos lograr es este gráfico
La idea es agregar los rótulos de máximo y mínimo en forma dinámica. Es decir, que a medida que cambiemos los datos en la tabla los rótulos aparezcan sobre las columnas correspondientes.
Para lograr esto lo que haremos es agregar dos series de datos a nuestra tabla, una el valor máximo, y otra para el mínimo.
Empezamos por agregar las series Máximo y Mínimo a la tabla
En la celda C2 ponemos la fórmula =SI(MAX($B$2:$B$13)=B2,B2,NOD()), que copiamos al resto de rango. Usamos la función NOD() para evitar valores "vacíos" en la serie.
Hacemos lo mismo para la serie Mínimo en el rango D2:D13, sólo que aquí usamos la función MIN():=SI(MIN($B$2:$B$13)=B2,B2,NOD()).
El próximo paso es construir el gráfico. Seleccionamos toda la tabla y creamos un gráfico de columnas apiladas
De ser necesario cambiamos la escala del eje de la Y para que comience de cero
Cada serie aparece con un color distinto. De las series Máximo y Mínimo vemos sólo un punto para cada serie, ya que el resto de los puntos son valores N/A.
Ahora seleccionamos la columna de la serie Mínimo (de color amarillo) y cambiamos el tipo de gráfico a Dispersión
Hacemos lo mismo con la columna de la serie Máximo. Las columnas han sido ahora reemplazadas por puntos.
Con el menú de Autoformas creamos un flecha de bloque con fondo azul y texto"Max."
y otra flecha de bloque con fondo rojo y el texto "Min."
Ahora copiamos la flecha "Min." (Ctrl+C), seleccionamos el punto de la serie Mínimo y lo pegamos con Ctrl+V. Hacemos lo mismo con la flecha "Max." que pegamos al punto de la serie Máximo
Para crear una separación entre el extremo superior de la columna y el inferior de la flecha, modificamos levemente las fórmulas de las series Mínimo y Máximo
=SI(MAX($B$2:$B$13)=B2,B2+5000,NOD())
Al agregar 5000 al valor, creamos una separación entre las flechas y las columnas. El valor requerido cambia en función de la magnitud de los valores de la serie de las ventas.
Para agregar un poco más de "cosmética" podemos aplicar Formato condicional en el rango A2:A13, para que los meses de máximo y mínimo aparezcan con el color de fuente correspondiente con estas fórmulas
- para máximo: =MAX($B$2:$B$13)=B2
- para mínimo: =MIN($B$2:$B$13)=B2
6 comentarios:
Muy pero muy bueno! Como les pasa a muchos, no le dí jamás pelota a los gráficos en Excel. Y esto, que resulta sencillo de hacer, le da un valor agregado enorme a la presentación.
Así que, gracias, muchas gracias.
UN LUJO!!
Hola Jorge,
Mi nombre es Karla F.
Utilicé este tipo de grafica pero para marcar un punto de equilibrio (hice un análisis data-table) en vez de máx y min, donde diera cero me pone una raya roja marcando el % donde está el punto de equilibrio, quedó perfecta, PERO cuando cambio los datos del análisis me vuelve a salir el "punto" de la grafica de dispersión,¿Cómo hago para que se quede la raya roja?
Muchas gracias!
Hola Karla
he probado el modelo en varias condiciones y no veo que suceda lo que describis. Puedes mandarme el archivo?
si claro que te lo mando, tienes un mail?
saludos y gracias!
Att. Karla
Karla
haz clic en el enlace que aparece en Contacto (en la columna izquierda del blog, arriba)
Publicar un comentario