Formato condicional funciona con fórmulas booleanas, es decir, que el resultado debe ser VERDADERO o FALSO. Sólo cuando el resultado es VERDADERO, se aplica el formato elegido.
Dado esto, nuestra táctica será calcular si la fila de la celda evaluada cae dentro del rango de la fórmula. Por ejemplo, si queremos marcar las últimas 10 filas
seleccionamos el rango C5:C160 (siguiendo con nuestro ejemplo) y en “formato condicional-nueva regla-utilice fórmula…” usamos
Y(FILA(C5)>=CONTAR($C$5:$C$160)+5-$C$1,FILA(C5)<=CONTAR($C$5:$C$160)+4)
donde C1 contiene el número de valores a incluir en el cálculo.
La fórmula funciona así: calculamos el número fila de la primer celda en el rango con
CONTAR($C$5:$C$160)+5-$C$1
Calculamos el número de fila del última valor en el rango con
CONTAR($C$5:$C$160)+4
donde 4 es el número de filas por encima de la primer fila del rango de valores.
Ahora usamos la función FILA para comparar si el número de fila de la celda evaluada cae dentro del rango relevante. Esto lo hacemos con la función Y, que da VERDADERO sólo si todas las condiciones dentro la fórmula se cumplen.
Personalmente prefiero una técnica distinta. Por algún motivo que no termino de comprender, muchos usuarios evitan usar columnas auxiliares en los modelos de Excel. Esto nos lleva casi siempre a crear fórmulas complicadas cuyo principal problema reside en la dificultad de controlar los resultados (o recordar que quisimos hacer cuando volvemos a trabajar con el modelo dos semanas más tarde).
En el caso que estamos analizando, mi propuesta es la siguiente:
1 – insertamos 3 columnas a la izquierda de la tabla de datos (dos columnas son suficientes)
2 – En la celda B2 ponemos la fórmula “=CONTAR(E5:E160)+4”, que calcula la última fila con valores en el rango (tal como hicimos en la fórmula anterior)
3 – En la celda A2 ponemos “=B2-E1+1” que calcula la primer fila del rango (E1 contiene el número de valores que queremos incluir en el cálculo)
4 – En la celda B5 ponemos la fórmula “=Y(FILA(E5)>=$A$2,FILA(E5)<=$B$2)” y la copiamos a lo largo del rango a evaluar (en nuestro ejemplo B5:B160)
Como se ve, creamos una serie de valores VERDADERO (cuando la fila cae dentro del rango) o FALSO (cuando está fuera del rango). Ahora usamos esto valores para accionar el formato condicional
Como puede apreciarse, la fórmula en el formato condicional es obviamente sencilla, lo mismo que las fórmulas en las columnas auxiliares.
El último toque es ocultar las columnas auxiliares
Descarga del archivo del ejemplo
Hola.
ResponderBorrarLo de FILA funciona muy bien.
En otros casos se podria utilizar el operador de interseccion de rangos, y como condiciones en el condicional :
ESNUMERO(C5 (DESREF($C$5;CONTAR($C$5:$C$160)-$C$1;0;$C$1;1)))
o
NO(ESERROR(C5 (DESREF($C$5;CONTAR($C$5:$C$160)-$C$1;0;$C$1;1))))
--- Gracias por el blog ---- cllach
Hola Jorge
ResponderBorrartengo una duda con un formato condicional de Numero: tengo una tabla que conecta dinamicamente a un conjunto de datos segun una celda "control", los datos son todos numeros pero algunos son tasas y otros son numeros enteros, por cuanto quiero formatear el numero de decimales segun la celda "control", al aplicar el formato condicional me funciona bien sólo la primera vez, luego se pega el formato con decimales.
(uso excel 2007), muchas gracias por tus consejos.
Marco Antonio,
ResponderBorrarcreo que tienes un problema con las referencias en la fórmula. La referencia a la celda "control" tiene que ser absoluta (por ejemplo, $A$1)