Otro problema con celdas combinadas

En cierta ocasión, un compañero de trabajo me consultó un problema que le surgió al aplicar el formato condicional a una hoja en la que había celdas combinadas. Éste era el aspecto de la hoja:

En la columna B las celdas se combinan de 4 en 4, y en la C, de 2 en 2. Las fórmulas de la columna B son irrelevantes para el caso (además, no las recuerdo) por lo que pondremos cualquier fórmula que genere errores a partir de una fila determinada.

Se trata de ocultar las celdas a partir de la fila en la que comienzan a presentarse los errores, aplicando formato condicional.

Si no hubiera celdas combinadas el procedimiento sería:

1. Seleccionar B2:B29
2. Acceder a Inicio + Formato condicional + Nueva regla.
3. Elegir la opción Utilice una fórmula que determine las celdas para aplicar formato.
4. En el cuadro de texto Dar formato a los valores donde esta fórmula sea verdadera, poner: =ESERROR($B2)
5. Pulsar el botón Formato y elegir: En Bordes: Ninguno; En Fuente: Color blanco.

Si damos estos pasos el resultado será:

Las filas 22 y 26 quedan ocultas, pero el resto de las filas, de la 22 hacia abajo, el formato no se aplica correctamente.

Como en otras ocasiones, nos ayudaremos de celdas auxiliares para crear una fórmula. Debemos conseguir que, en cada grupo de 4 filas, el valor que evalúe la función ESERROR sea el de la celda combinada de ese grupo. El procedimiento utilizado será similar al empleado en la entrada Problemas con celdas combinadas.

Eliminamos el formato condicional incorrecto accediendo a Inicio + Formato condicional + Borrar reglas + Borrar reglas de toda la hoja.

En F2:

=RESIDUO(FILA()+2;4)

Extendemos la fórmula hasta la fila 29. Con esta operación hemos conseguido que las filas de cada bloque estén numeradas del 0 al 3.

En G2:

=FILA()-F2

Extendemos la fórmula hasta la fila 29. Ahora, cada grupo contiene un número que coincide con el de la primera fila del grupo.

En H2:

=INDIRECTO(DIRECCION(G2;2))

Extendemos la fórmula hasta la fila 29. Hemos conseguido asociar cada fila con la fecha correcta de su grupo.

En I2:

=INDIRECTO(DIRECCION(FILA()-RESIDUO(FILA()+2;4);2))

Extendemos la fórmula hasta la fila 29. Esta fórmula compuesta es la que debemos poner en el formato condicional.

1. Seleccionamos B2:B29
2. Accedemos a Inicio + Formato condicional + Nueva regla.
3. Elegimos la opción Utilice una fórmula que determine las celdas para aplicar formato.
4. En el cuadro de texto Dar formato a los valores donde esta fórmula sea verdadera, ponemos: =ESERROR(INDIRECTO(DIRECCION(FILA()-RESIDUO(FILA()+2;4);2)))
5. Pulsamos el botón Formato y elegimos: En Bordes: Ninguno; En Fuente: Color blanco.

El resultado no es exactamente el deseado: falta la línea inferior de la última fila visible.

Lo solucionaremos añadiendo una nueva regla.

1. Seleccionamos B2:B29
2. Accedemos a Inicio + Formato condicional + Nueva regla.
3. Elegimos la opción Utilice una fórmula que determine las celdas para aplicar formato.
4. En el cuadro de texto Dar formato a los valores donde esta fórmula sea verdadera, ponemos:    =NO(ESERROR(INDIRECTO(DIRECCION(FILA()-RESIDUO(FILA()+2;4);2))))
5. Pulsamos el botón Formato y, en Bordes, elegimos Contorno.

Ahora, está perfecto.

Podemos probar diferentes valores en la celda E1 (hemos usado esta celda para crear las fechas de la columna B) para comprobar que todo funciona bien.

Nota: En Excel 2003 el formato condicional funciona de manera algo diferente que en 2010. Al aplicar la primera regla el resultado ya es correcto. Por eso, en el fichero para la versión 2003, que se puede descargar más abajo, se ha prescindido de la segunda regla.

Descargar archivo (S2611-Ocultar.xlsx)

Descargar archivo (S2611-Ocultar.xls)