FILTROS, GRÁFICOS MÁXIMOS Y MÍNIMOS

FILTROS, GRÁFICOS MÁXIMOS Y MÍNIMOS 

valores máximo y mínimo en un gráfico de líneas.

Es muy fácil crear un gráfico de máximos y mínimos en Excel. La creación se basa en un gráfico de área con algunas modificaciones para dejar en claro la banda de máximos y mínimos dentro del gráfico. En primer lugar debes preparar tus datos de la siguiente manera:

La fila Promedio es importante porque nos ayudará a trazar una línea que pueda distinguir los valores máximos de los mínimos. Para nuestro ejemplo he utilizado la función PROMEDIO y podrás ver la manera en que la utilizo al descargar el archivo Excel asociado a este artículo. La fórmula realiza el promedio de las celdas superiores, máximo y mínimo, para obtener el promedio correspondiente.

Una vez que tienes lista tu tabla de datos, podemos comenzar la creación del gráfico seleccionando cualquier celda de la tabla, vamos a la ficha Insertar y dentro del grupo Gráficos seleccionamos el comando Área y posteriormente la opción Gráfico de área 2D:

El gráfico será creado de inmediato y obtendremos un resultado como el mostrado en la siguiente imagen:

Ajustes al gráfico de área

En seguida haré un par de ajustes al gráfico que acabamos de crear . En primer lugar, haré clic sobre la leyenda del gráfico, es decir, el recuadro que muestra el nombre de las series de datos:  Máximo, Mínimo, Promedio. Una vez seleccionadas pulsaré la tecla Suprimir para eliminar la leyenda.

El segundo ajuste que realizaré será eliminar las líneas de división del eje vertical. De igual manera haré clic sobre dichas líneas y pulsaré la tecla Suprimir. Como resultado tendremos un gráfico como el que se muestra en la siguiente imagen.

Ordenar las series de datos

Para continuar, debemos cambiar el orden de las series para asegurarnos que todas estén visibles en el gráfico. En el gráfico anterior, la serie Mínimo no se puede ver porque ha quedado por detrás de las otras series. Para corregir este problema con el gráfico, lo seleccionamos y hacemos clic en el comando Seleccionar datos de la ficha Diseño:

Se mostrará el cuadro de diálogo Seleccionar origen de datos:

Para nuestro ejemplo debo seleccionar la serie Promedio y pulsar el botón Subir para mover dicha seria hacia arriba y dejarla por debajo de la serie Mínimo tal como lo observas en la siguiente imagen:

Ocultar la serie Mínimo

Ahora haremos que el color de relleno de la serie Mínimo sea blanco. La elección de este color se debe a que el color de fondo de la hoja de Excel es blanco, y ya que queremos “ocultar” dicha serie, igualaremos su color de fondo al de la hoja de Excel.

Para eso debemos hacer clic derecho sobre el área de la serie Mínimo y seleccionar la opción Dar formato a serie de datos. Eso mostrará el cuadro de diálogo Formato de serie de datos, y en la sección Relleno debemos seleccionar la opción Relleno sólido y el color blanco.

Después de hacer este cambio y cerrar el cuadro de diálogo, tendremos un gráfico como el siguiente:

El gráfico de máximos y mínimos

Para tener nuestro gráfico de máximos y mínimos igualaremos los colores de relleno de las series Máximo y Promedio. Para cada una de ellas deberás hacer clic derecho sobre el área y elegir la opción de menú Dar formato a serie de datos de la misma manera que lo hicimos en la sección anterior.

Para ambas series de datos elegiré el mismo color de relleno, pero para la serie Promedio haré un cambio adicional y será en la sección Color del borde donde elegiré la opción Línea sólida tal como lo ves en la siguiente imagen.

El colocar una línea sólida al borde la serie Promedio nos dará un gráfico como el siguiente:

En este gráfico puedes observar tanto los valores máximos y m

La función de la columna Máximo es la siguiente:

=SI(B4=MAX($B$2:$B$13), B4, NOD())

La función de la columna Mínimo será similar y solo cambia por el uso de la función MIN:

=SI(B11=MIN($B$2:$B$13), B11, NOD())

Crear el gráfico de líneas

El siguiente paso es crear el gráfico de líneas utilizando todas las columnas. Es importante decir que a primera vista no observarás los puntos máximo y mínimo:

Crear los marcadores de máximo y mínimo

Para poder mostrar los puntos máximo y mínimo necesitamos seleccionar cada serie para modificar sus propiedades. La manera más sencilla es ir a la ficha Presentación, que se encuentra dentro de la ficha contextual Herramientas de gráficos. Dentro del grupo Selección actual debemos elegir de la lista la opción Serie Máximo.

Inmediatamente después  debemos pulsar el botón Aplicar formato a la selección lo cual abrirá el cuadro de diálogo Formato de serie de datos.

En las Opciones de marcador selecciona la opción Integrado y elige un Tipo de marcador, así como un tamaño apropiado. Al pulsar el botón cerrar observarás el marcador de Máximo en el gráfico.

Realiza este mismo procedimiento para la Serie Mínimo y obtendrás un gráfico de líneas como el siguiente:

EJEMPLO DE MAXIMOS, MINIMOS Y PROMEDIOS CON FORMATO CONDICINAL

Además de calcular los valores máximos, mínimos y promedio general, queremos que las zonas que individualmente superen dicho promedio general aparezcan sombreadas (el nombre de la zona) en color, por ejemplo, verde.

1. Seleccionamos el rango C8:H19 y hacemos un clic en el cuadro de nombres (a la izquierda de la barra de fórmulas). Escribimos el nombre ventas09.

2. Nos situamos en la celda C2 y escribimos la fórmula:

=MAX(ventas09)

3. En C3:

=MIN(ventas09)

4. En C4:

=PROMEDIO(ventas09)

5. Seleccionamos el rango C7:H7 y vamos a Formato/Formato condicional. Como Condición1seleccionamos fórmula (en vez de valor de la celda) y a continuación escribimos la siguiente fórmula:

=SI(PROMEDIO(C$8:C$19)>=$C$4;1;0)

 Al utilizar referencias mixtas (fijando fila pero no columna) la fórmula que aplicará a cada columna será la adecuada, es decir, comparará el promedio de cada columna (zona) con el promedio general (C4).

6. Pulsamos el botón Formato, vamos a la pestaña Tramas y seleccionamos el color verde. Aceptamos.

A partir de este momento las zonas cuyo promedio de ventas supere el promedio general se rellenarán (el encabezado) de color verde:

7. Seleccionamos el rango ventas09 (por ejemplo haciendo directamente clic sobre este nombre dentro del cuadro de nombres) y vamos a Formato/Formato condicional. Establecemos las condiciones como se muestran en la imagen y aceptamos:

El resultado será el solicitado...

Puede conseguir los mismos resultados sin tener que realizar los pasos 1, 2 y 3, es decir, sin la necesidad de mostrar en la hoja (rango C2:C4) los valores máximo, mínimo y promedio respectivamente. Para ello debe sustituir dentro del primer formato condicional la fórmula indicada en el paso 5 por la siguiente:

=SI(PROMEDIO(C$8:C$19)>=PROMEDIO(ventas09);1;0)

En el segundo formato condicional debe sustituir las condiciones por las siguientes:

donde la primera fórmula dice =SI(C8=MAX(ventas09);1;0) y en donde la segunda fórmula dice =SI(C8=MIN(ventas09);1;0)

LA FORMULA SE PUEDE SUSTITUIR COMO:

=SI(C8=MAX(ventas09);1;0),POR ESTA OTRA:
=C8=MAX(VENTAS09)
Además de =SI(C8=MIN(ventas09);1;0), POR ESTA OTRA:
=C8=MIN(VENTAS09)