sábado, diciembre 29, 2007

Remarcar máximos y mínimos en gráficos de Excel.

Supongamos esta tabla de ventas



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:

leiv dijo...

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.

Anónimo dijo...

UN LUJO!!

Anónimo dijo...

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!

Jorge Dunkelman dijo...

Hola Karla
he probado el modelo en varias condiciones y no veo que suceda lo que describis. Puedes mandarme el archivo?

Anónimo dijo...

si claro que te lo mando, tienes un mail?

saludos y gracias!
Att. Karla

Jorge Dunkelman dijo...

Karla
haz clic en el enlace que aparece en Contacto (en la columna izquierda del blog, arriba)