Recientemente una lectora me propuso un reto interesante, era asignar un Formato condicional a un rango de una hoja de Excel, basándose en ciertos valores determinados en otro rango, que sirven para definir un subrango mediante la función DESREF. La cuestión más o menos decía:
Veamos el planteamiento de la hoja en la siguiente imagen:
De lo que se trata entonces es asignar al rango A1:T13 un formato condicional en función a los valores del rango G15:G19, que son los que sirven como argumento de una función DESREF (empleada en este caso para definir un rango, y no solo una posición). Por tanto a partir de la posición dada en la celda G15, localizaremos la primera celda del rango y la última, para posteriormente poder aplicar el formato condicional en base a la posición.
Pero vamos por parte.
Lo primero que vamos a hacer es crear dos nombres definidos en su variante de fórmula matricial. Lo que conseguiremos es facilitar la operativa, ya que las fórmulas que se van a crear son algo largas. Son estas:
rng_1: =DIRECCION(FILA(DESREF(INDIRECTO($G$15);$G$16;$G$17));COLUMNA(DESREF(INDIRECTO($G$15);$G$16;$G$17)))
rng_2: DIRECCION(FILA(DESREF(INDIRECTO($G$15);$G$16+$G$18-1;$G$17+$G$19-1));COLUMNA(DESREF(INDIRECTO($G$15);$G$16+$G$18-1;$G$17+$G$19-1)))
Vemos que cada fórmula es un compendio de funciones anidadas. Antes de explicar cómo funcionan, diremos qué obtenemos con cada una de ellas. Con
rng_1 obtenemos la dirección absoluta de la primera celda del rango que queremos formatear.
rng_2 obtenemos la dirección absoluta de la última celda del rango a formatear.
La idea es determinar cuales son la primera y última fila del rango, y la primera y última columna del rango a formatear.
La explicación de esas funciones sería, desde lo más profundo de ellas:
Con DESREF(INDIRECTO($G$15);$G$16;$G$17) conseguimos el valor de la celda en cuestión, como no nos interesa el valor de la celda, sino su referencia, aplicamos sobre ella la función FILA y COLUMNA para conocer en número la fila y columna de dicha celda, de la primera celda que define el rango a formatear. Sabiendo cual es esa FILA y COLUMNA, obtenemos su referencia absoluta, anidando esos resultados en la función DIRECCION. Para el ejemplo de partida, obtendremos la referencia: $I$10.
De igual forma para el nombre definido 'rng_2', con la variante que ahora sólo nos interesa la última celda que deine el rango a formatear, por lo que como argumentos de DESREF aplicamos la altura y el ancho definidos. Para el ejemplo de partida obtenemos la referencia: $K$12.
El siguiente paso es deshacer, en parte lo conseguido, puesto que ya conozco la primera y última celda del rango, por su referencia absoluta, ahora para poder aplicar el Formato condicional, conseguiré el intervalo de filas y de columnas (por número) que corresponde al área a formatear. Esto lo conseguiremos anidano nuestros Nombres_fórmulas a la función INDIRECTO y FILA o COLUMNA:
Estas serán las fórmulas que emplearemos a la hora de configurar nuestro formato condicional... que es el siguiente paso. Accedemos al formato condicional tipo fórmula, una vez seleccionado el rango A1:T13 con la celda A1 activa, e incluimos:
=Y(FILA(A1)>=FILA(INDIRECTO(rng_1));FILA(A1)<=FILA(INDIRECTO(rng_2));COLUMNA(A1)>=COLUMNA(INDIRECTO(rng_1));COLUMNA(A1)<=COLUMNA(INDIRECTO(rng_2)))
En ella comprobamos que la celda activa del rango, que su número de fila y de columna, se encuentran en el intervalo del rango a formatear; si es así le asignamos un formato particular, en el ejemplo color verde de fondo de celda.
Para finalizar la petición de la lectora, generaremos una nueva regla de formato condicional, para marcar la celda Ancla. Asi que accedemos al formato condicional y agregamos una nueva regla con fórmula (igual que antes con el rango A1:T13 seleccionado y la celda A1 activa):
=DIRECCION(FILA(A1);COLUMNA(A1);4)=$G$15
Listo, podemos observar en la imagen animada su funcionamiento:
| ...lo que quiero es que me coloree en formato condicional ,en función a las celdas G15:G19, que sea dinámico respecto a la tabla (A1:T13)... |
De lo que se trata entonces es asignar al rango A1:T13 un formato condicional en función a los valores del rango G15:G19, que son los que sirven como argumento de una función DESREF (empleada en este caso para definir un rango, y no solo una posición). Por tanto a partir de la posición dada en la celda G15, localizaremos la primera celda del rango y la última, para posteriormente poder aplicar el formato condicional en base a la posición.
Pero vamos por parte.
Lo primero que vamos a hacer es crear dos nombres definidos en su variante de fórmula matricial. Lo que conseguiremos es facilitar la operativa, ya que las fórmulas que se van a crear son algo largas. Son estas:
rng_1: =DIRECCION(FILA(DESREF(INDIRECTO($G$15);$G$16;$G$17));COLUMNA(DESREF(INDIRECTO($G$15);$G$16;$G$17)))
rng_2: DIRECCION(FILA(DESREF(INDIRECTO($G$15);$G$16+$G$18-1;$G$17+$G$19-1));COLUMNA(DESREF(INDIRECTO($G$15);$G$16+$G$18-1;$G$17+$G$19-1)))
Vemos que cada fórmula es un compendio de funciones anidadas. Antes de explicar cómo funcionan, diremos qué obtenemos con cada una de ellas. Con
rng_1 obtenemos la dirección absoluta de la primera celda del rango que queremos formatear.
rng_2 obtenemos la dirección absoluta de la última celda del rango a formatear.
La idea es determinar cuales son la primera y última fila del rango, y la primera y última columna del rango a formatear.
La explicación de esas funciones sería, desde lo más profundo de ellas:
Con DESREF(INDIRECTO($G$15);$G$16;$G$17) conseguimos el valor de la celda en cuestión, como no nos interesa el valor de la celda, sino su referencia, aplicamos sobre ella la función FILA y COLUMNA para conocer en número la fila y columna de dicha celda, de la primera celda que define el rango a formatear. Sabiendo cual es esa FILA y COLUMNA, obtenemos su referencia absoluta, anidando esos resultados en la función DIRECCION. Para el ejemplo de partida, obtendremos la referencia: $I$10.
De igual forma para el nombre definido 'rng_2', con la variante que ahora sólo nos interesa la última celda que deine el rango a formatear, por lo que como argumentos de DESREF aplicamos la altura y el ancho definidos. Para el ejemplo de partida obtenemos la referencia: $K$12.
El siguiente paso es deshacer, en parte lo conseguido, puesto que ya conozco la primera y última celda del rango, por su referencia absoluta, ahora para poder aplicar el Formato condicional, conseguiré el intervalo de filas y de columnas (por número) que corresponde al área a formatear. Esto lo conseguiremos anidano nuestros Nombres_fórmulas a la función INDIRECTO y FILA o COLUMNA:
Estas serán las fórmulas que emplearemos a la hora de configurar nuestro formato condicional... que es el siguiente paso. Accedemos al formato condicional tipo fórmula, una vez seleccionado el rango A1:T13 con la celda A1 activa, e incluimos:
=Y(FILA(A1)>=FILA(INDIRECTO(rng_1));FILA(A1)<=FILA(INDIRECTO(rng_2));COLUMNA(A1)>=COLUMNA(INDIRECTO(rng_1));COLUMNA(A1)<=COLUMNA(INDIRECTO(rng_2)))
En ella comprobamos que la celda activa del rango, que su número de fila y de columna, se encuentran en el intervalo del rango a formatear; si es así le asignamos un formato particular, en el ejemplo color verde de fondo de celda.
Para finalizar la petición de la lectora, generaremos una nueva regla de formato condicional, para marcar la celda Ancla. Asi que accedemos al formato condicional y agregamos una nueva regla con fórmula (igual que antes con el rango A1:T13 seleccionado y la celda A1 activa):
=DIRECCION(FILA(A1);COLUMNA(A1);4)=$G$15
Listo, podemos observar en la imagen animada su funcionamiento:
Hola Ismael,
ResponderEliminarUn gusto saludarte me parece muy interesante lo de Formato condicional para un rango movil de Excel, tengo una observacion en el formato condicional, porqué no colorea cuando introduzco en celdas (G18 y G19), cualquiera de las celdas valores negativos.
muchas gracias
Sldos,
Jorge
Hola Jorge,
Eliminarsi, la formulación está empleando los valores Ancho y Alto para sumar y determinar la última celda del rango a formatear, al colocar valores negativos el valor devuelto (de fila y columna) para la última celda del rango hace imposible el cumplimiento en la condición del Formato condicional...
habría que incorporar algún tipo de condicional para el caso en que Ancho y Alto fueran negativos...
Slds
Hola excelforo
ResponderEliminarMira lo he realizado el ejercicio de Formato condicional para un rango movil pues si da error cuando se incorpora valores negativos obvíamente el formato condicional no colorea, pues miré los comentarios y Jorge te hace la misma pregunta y tu hablas de incorporar algún tipo de condicional para este caso.
mí curiocidad es que tipo de condicional pondrías, porqué yo lo he intentado pero sin resultado positivo si pudiera agudarme en ese sentido te lo agradeceria.
muchas gracias Raúl.
Hola Raúl...
Eliminarbueno, el condicional podría ser algo del estilo:
=SI(Alto<0;=Y(FILA(A1)<=FILA(INDIRECTO(rng_1));FILA(A1)>=FILA(INDIRECTO(rng_2));...
ya que si el valor alto es negativo, el rng_2 corresponde a una fila por encima del rng_1.
Habría que construir el condicional anidado para controlar todos los casos posibles, si Alto<0 y/o Ancho<0... incluso, siendo estrictos con las celdas Filas y/o columnas.
Espero te sirva la pista.
Slds cordiales
Hola Ismael
ResponderEliminarMuchas gracias, en verdad me ayudó bastante el condicional
Sldos cordiales
Raúl.
Hola chicos, traigo un problema que puede ser simple pero para mi no lo ha sido, es el siguiente , en una columna de valores debo resaltar en colores distintos 3 tipos de condiciones, en rojo los valores iguales o menores a 10, en amarillo los valores superiores a 10 pero menores a 50 y en verde los valores superiores o iguales a 50, me he complicado con el segundo ya que no se como hacer formato condicional entre ese rango pero excluyendo los extremos, espero puedan ayudarme, Importante, hacerlo sin agregar otra columna,
ResponderEliminarHola Reinier,
Eliminarsupongamos sea la columna B donde están los datos
serían tres reglas de formato condicional:
1. Nueva regla con fórmula:
=$B2<=10 - formato rojo
2. Nueva regla con fórmula:
=$B2>=50 - formato verde
3- Nueva regla con fórmula:
=Y($B2>10;$B2<500) - formato amarillo
Saludos