domingo, 27 de septiembre de 2009

Resaltar Máximos, Mínimos y Promedios con Formato Condicional



"Mi empresa está compuesta por cinco zonas. Tengo una tabla en la que reflejo las ventas mensuales de cada zona. Me gustaría saber si es posible destacar los encabezados de zona que superan individualmente el promedio del conjunto. También me gustaría que resaltase las cifras máximas y mínimas de ventas".

Partimos del siguiente ejemplo:


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ón1 seleccionamos 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)

Fíjese que 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)


20 comentarios:

  1. ¿Podía sustituirse la fórmula:
    =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)
    Así lo veo menos complicado.

    P.D. Me encanta esta página. ¡FELICIDADES KIKO!

    ResponderEliminar
  2. Gracias por tus comentarios.
    Se podría hecer perfectamente como sugieres. Yo tengo "la manía" de introducir el condicional porque me parece que se entiende mejor pero, lo dicho, es sólo una manía. Gracias por tu aportación.

    ResponderEliminar
  3. Hola de nuevo, Kiko. Me he viciado a tu blog, y de rebote también a explorar las potencialidades de excel. A partir de varios de tus posts me he puesto manos a la obra y he empezado con una base para calcular la ley de Hondt. Después de un buen rato he conseguido que las fórmulas me cuadren de un modo automático, pero tengo un serio problema para que me resalte los valores máximos. Como te digo, es la aplicación de la ley de Hondt, así que el número de casillas a resaltar variará en función del número de escaños que se elijan (7, 11, 21...). Imagino que debería hacerlo con el k.esimo.maximo, pero no se me ocurre como vincularlo con la casilla A1 (pongamos por caso) en donde se debe introducir la variable 'escaños'... Un saludo. Carles

    ResponderEliminar
  4. Por cierto. Ya solucioné el problema para identificar los valores que corresponden a la aplicación de la ley de Hondt: la fórmula que he utilizado es

    =(Y(JERARQUIA(D2;valors;0)>=1;JERARQUIA(D2;valors;0)<=$A$1))=VERDADERO

    No sé si será la más simple que se pudiera crear, pero funciona correctamente.

    Un saludo. Carles

    ResponderEliminar
  5. Hola.. tengo 4 pares de columnas(presupuestos) cada par compuesto por precio unitario y la otra col por el subtotal que resulta de multiplicar precio unit por cantidad, las columnas tienen 53 items (son plantas de viveros).
    Lo que quiero hacer es seleccionar el menor precio unitario de las 4 col., es decir que los subtotales no me interesan, solo quiero resaltar el menor precio de las cuatro ofertas.

    ResponderEliminar
    Respuestas
    1. Si no entiendo mal la pregunta lo único que debes hacer es una selección discontinua del rango de la función MIN. Esto es: =MIN(A1:A53;C1:C53;E1:E53)

      Eliminar
  6. Buenas, en mi caso tengo varias columnas, pero lo que quiero es resaltar el valor maximo y mino en cada columna

    ResponderEliminar
    Respuestas
    1. Queda resuelto en el post de hoy día 4 de marzo de 2013. Un saludo

      Eliminar
  7. Buenas Kiko, si quieres calcular un promedio quitando los 2 valores mas bajos, como lo haces con formula?

    por ejemplo tengo: 7 6 5 4 3 y quiero obtener el promedio quitando el 4 y 3...

    Saludos

    ResponderEliminar
    Respuestas
    1. Buenos días. Por favor dime a qué mail te puedo mandar la solución. Un saludo

      Eliminar
  8. Esta muy bien muchas gracias, pero como se puede hacer de forma automatica cuando tienes 1000 columnas??

    ResponderEliminar
    Respuestas
    1. Perdón pero no entiendo bien la pregunta. En principio lo único que cambia es el tamaño del rango pero no la formulación ¿Me puedes especificar un poco más? Gracias

      Eliminar
  9. Hola Kiko muy buen dia, tengo un pequeño problema como lo que mencionas en el procedimiento anterior, solo que yo no necesito el promedio, tengo un conjunto de datos ya tengo dos celdas donde encuentra el minimo y el maximo como lo haces tu, pero solo necesito que identifique el máximo y el mínimo y los resalte en color rojo únicamente.

    Espero puedas apoyarme muchas gracias, saludos!

    ResponderEliminar
    Respuestas
    1. Pues, en principio, lo único que tienes que hacer es la fórmula =C8=MAX(ventas09) como condición 1 y =C8=MIN(ventas09) como condición 2 del formato condicional.

      Eliminar
  10. Buenas noches Kiko tengo un problema quiero ponerle formato condicional a un archivo que contiene varias columnas pero a una de ellas quiero introducir el formato donde me coloree donde se reflejan los valores de cero. como aplico la formula gracias

    ResponderEliminar
  11. Entiendo que lo que necesitas es que si el valor es cero se coloree, por ejemplo, de rojo pero si está en blanco o tiene otro valor entonces que no se aplique formato condicional. Si es así, seleccionamos la columna en cuestión (voy a suponer en este ejemplo el rango A1:A10). Vamos a Formato Condicional y seleccionamos Nueva regla. Elegimos la opción Utilice una fórmula que determine las celdas para aplicar formato, y en el cuadro Dar formato a los valores donde esta fórmula sea verdadera, escribimos la siguiente fórmula:
    =Y(ESBLANCO(A1)=FALSO;A1=0)
    Pulsamos el botón Formato y seleccionamos el color de relleno que deseemos y terminamos aceptando. A partir de aquí cualquier valor 0 que aparezca en el rango A1:A10 se coloreará de rojo pero si las celdas contienen otro valor o están en blanco entonces permanecerán sin formato condicional.

    ResponderEliminar
  12. Mucho gusto, necesito por mi trabajo, obtener valores minimos por fila y por columnas, al mismo tiempo debo resaltarlos, para luego poder filtrarlos, o bien realizar toda la operación con una función. Gracias.

    ResponderEliminar
    Respuestas
    1. No acabo de entender bien la diferencia de lo que necesitas con lo que se explica en este post. Por favor mándame un archivo explicando lo que tienes y lo que quieres conseguir a kiko.rial@gmail.com

      Eliminar
  13. Hola a todos, felicitaciones por el blog Kiko. Una duda que me esta causando algunos dolores de cabeza, si tengo 10 columnas con valores y quiero sumar, pero restando el máximo y el mínimo, ¿Cual es la formula?

    ResponderEliminar
    Respuestas
    1. Si los datos los tengo en el rango B3:K14 la fórmula sería:
      =(SUMA(B3:K14)-MAX(B3:K14)-MIN(B3:K14))/(CONTAR(B3:K14)-2)

      Eliminar