Formatos condicionales para encontrar un texto en una lista de datos

Lo cierto es que Excel dispone de una gran variedad de formatos condicionales que ayudan a resaltar los datos de forma visual y destacar aquellos que cumplen determinadas condiciones. Son muy fáciles de usar, merece la pena practicar con ellos ya que normalmente el resultado obtenido puede ser muy atractivo.

Pero uno de las posibilidades más interesantes de los formatos condicionales es la de poder aplicarlos a aquellas celdas que cumplen condiciones que especificaremos por medio de fórmulas. Son algo más complejas, sin embargo pueden llegar a tener aplicaciones muy útiles y sernos de gran ayuda.

Vamos a suponer que tenemos una amplia lista de diversos artículos que vendemos, similar a ésta:

Formato condicional
Formato condicional

Ahora deseamos que al introducir un texto en la celda B3, se nos indique de alguna manera las celdas que contienen dicho texto. Por ejemplo, si en B3 escribimos la palabra Flexo, se resaltan las celdas B11 y B12, que son las que la contienen. Una posibilidad es usar un formato condicional. Para ello seleccionamos previamente el rango de la lista (B6:B17 en el ejemplo) y elegimos la pestaña Inicio. En el grupo Estilos, la opción Formato condicional. Aparece un menú a continuación, elegiremos Nueva regla (pues es la primera vez que la establecemos, aunque también en Administrar reglas, se pueden establecer, modificar, etc.). En el cuadro de diálogo siguiente:

Formato condicional2
Formato condicional2

Hemos elegido la última opción de la lista, aquella que pone “Utilice una fórmula…”. Un poco más abajo leemos: “Dar formato a los valores donde esta fórmula sea verdadera”. En nuestro ejemplo queremos que se señalen aquellos valores de la lista de datos que contengan lo que escribamos en la celda B3. Usaremos para ello la función encontrar, que tiene la siguiente sintaxis:
=encontrar(textobuscar,texto)
El primer argumento indica el texto que deseamos buscar y el segundo dónde se buscará, en qué texto. Por ejemplo, supongamos que escribimos en nuestra hoja de cálculo la siguiente fórmula:
=ENCONTRAR("el","Siempre es el mismo")
El resultado es 12. Si contamos, “el” comienza a partir de la letra o posición 12 en el texto “Siempre es el mismo”. Hay que recordar que se cuentan los espacios en blanco y que distingue entre mayúsculas y minúsculas.
Volviendo a nuestro ejemplo escribiríamos la fórmula siguiente en el apartado “Dar formato a los valores…”:
=ENCONTRAR($B$3,B6)
B3 es la celda que contiene el valor a buscar y es fija (por eso le ponemos los símbolos $), pues siempre ocupará la misma posición. B6 es la primera celda del rango en el que buscaremos, no es fija. Una vez introducida la fórmula, no debemos olvidar dar al botón “Formato” a fin de especificar cómo queremos que nos señale las celdas en cuestión, podemos elegir que se aplique un color azul, por ejemplo. Si olvidáramos este paso, fundamental, aunque la fórmula sea correcta, no funcionaría, pues no habría formato que aplicar. Aceptamos entonces en todas las ventanas hasta volver a nuestra hoja de cálculo. Vemos con asombro que se han señalado todas las celdas en azul. Sin embargo, si colocamos el cursor en B3 y escribimos Tornillos, se nos indican las celdas B6 y B14. ¿Por qué si B3 está vacía se señalan todas? porque todas las celdas cumplen la condición impuesta. ¿Cómo podemos evitarlo? Alterando la fórmula y añadiendo además otra condición de la forma siguiente:
=Y($B$3"",ENCONTRAR($B$3,B6))
Es decir, que B3 no esté vacía.
Otro problema que encontramos es que si escribimos en mayúsculas todo o minúsculas no lo encuentra. Ya comentamos anteriormente que la función encontrar distingue mayúsculas y minúsculas. Por ejemplo si ponemos TORNILLOS, no se encontrará, pues en la lista aparece escrito Tornillos. Si queremos evitar este problema podemos hacer la búsqueda convirtiendo todo, tanto el texto buscado, como en el que buscamos, a mayúsculas o a minúsculas de la forma siguiente:
=Y($B$3"",ENCONTRAR(MAYUSC($B$3),MAYUSC(B6)))
Con ésta sí funcionará independientemente de cómo escribamos la palabra a encontrar. En la imagen buscamos CABLE, el resultado es correcto:
condicional3

Anuncios

Poner el contenido de una misma celda en distintos formatos

Todos conocemos las distintas posibilidades que existen en cuanto a formato de una celda. Hay que distinguir entre el contenido de una celda, que puede ser un texto, una función, etc. y el formato con el que se nos muestra ésta. La primera se refiere a lo que nosotros escribimos en la celda y está visible normalmente en la barra de fórmulas situada en la parte superior de la hoja de cálculo. La segunda sin embargo es la apariencia con que se nos presentan ese contenido en la celda situada en la propia hoja de cálculo. Lo vemos más claramente en la imagen a continuación donde la celda A5 contiene =SUMA(A1:A4), se puede ver en la barra arriba, y su formato es en negrita con 2 decimales, separación de miles y un tamaño muy superior al de las otras celdas.

Contenido y formato de una celda
Contenido y formato de una celda

Lo habitual es que tengamos el mismo formato para una celda. Sin embargo, muchos usuarios de Excel desconocen que es posible dar formatos distintos para una misma celda. Supongamos que tenemos que poner un texto con la fórmula E=mc2. Vemos que todo el texto irá en un formato normal, pero el dos debe aparecer como superíndice, un poco más arriba y pequeño que el resto.
Para hacer esto en Excel procederemos primero a introducir la fórmula, tal y como lo hacemos siempre. Nos situamos en la posición y escribimos E=mc2, el dos sin ningún formato de momento. Ahora seleccionamos con el ratón la parte de la entrada a la que queremos poner un formato diferente, en este caso el dos, deseamos que aparezca como superíndice. A continuación debemos ir a formato de celda. Podemos hacerlo tanto arriba en Inicio, como pulsando el botón derecho del ratón. Entonces marcamos la opción superíndice, aceptamos y damos intro. La entrada debe aparecer correctamente en ese momento.
Igualmente podemos cambiar el tipo de letra, color, etc. para la misma celda, las veces que queramos. En el caso de entradas ya escritas, podemos pulsar F2 y proceder a su edición, siguiendo los pasos que se explican aquí.

Efectos 3D en la hoja de cálculo

Lo que llamamos efectos 3D son debidos a formatos que aplicamos utilizando distintos colores y con los cuales conseguimos un efecto óptico que nos crea la sensación visual de volumen, a pesar de que, evidentemente, no lo tiene, pues estamos hablando de objetos bidimensionales.Por ejemplo podemos hacer que una celda tenga un aspecto de botón. Para ello nos situamos sobre ella y elegimos la opción que nos permite añadir bordes. Para ello podemos pulsar el botón derecho del ratón sobre la propia celda y la opción Formato de celda. Aparecen una serie de fichas y en la de bordes, que vemos a continuación en la imagen, elegimos el más grueso. Luego cuidando que esté el color negro, pulsamos sobre la indicación de borde  inferior y derecho. Después seleccionamos un color claro, por ejemplo un gris muy claro y pulsamos sobre los bordes superior e izquierdo. Para terminar aplicamos un fondo, en otra de las fichas está, de color gris más oscuro. Cuando pulsemos a aceptar y quitemos el curso de la celda, el resultado será como sigue:

Efecto 3D en una celda simulando que se trata de un botón
Efecto 3D en una celda simulando que se trata de un botón

Si cambiamos el orden de los bordes, dará la sensación de relieve, en lugar de saliente, entrante. Este efecto puede aplicarse a todo un conjunto o aplicar otro color. A nuestro gusto, según nuestra imaginación y creatividad.

Hacer que las filas o columnas de nuestra hoja de cálculo aparezcan alternativamente coloreadas.

Es frecuente que, con el fin de darle mejor legibilidad a nuestra hoja, recurramos a colorear de manera diferente las filas (o columnas). Por lo general se suelen colorear las filas alternativamente de gris claro. La hoja resultante tiene un acabado con un aspecto mucho más profesional y es mucho más sencillo leer los datos que contiene.

La solución parece fácil, nos situamos con el cursor en cada una de las filas/columnas que deseamos cambiar de color y vamos aplicándoles los formatos. Incluso existe la posibilidad de agilizar nuestra tarea si seleccionamos las filas/columnas pulsando la tecla ctrl simultáneamente y después les aplicamos el formato de una sola vez. Esta solución es buena si la cantidad de filas/columnas a colorear no es muy grande. ¿Pero y si tu hoja de cálculo es muy extensa?. Incluso podría ser que te olvidaras de alguna fila/columna. En cualquier caso se trata de una tarea bastante pesada.

Antes de continuar con nuestro tema, haremos un paréntesis para ver un par de funciones y lo que hacen, luego explicaremos en qué nos pueden ayudar para lo que perseguimos.

La primera de ellas es RESIDUO. Esta función obtiene el resto de la división entera de dos números que hemos de poner como argumentos. El primer número es el dividendo y el segundo el divisor. Así por ejemplo si ponemos la fórmula =RESIDUO(32;5) el resultado que obtenemos es 2, porque si dividimos 32 entre 5, el resto es 2. Otro ejemplo: si ponemos =RESIDUO(20;2) nos dirá que es cero, ya que al dividir 20 entre 2 el resto es cero, pues 20 es un múltiplo de 2. Podemos seguir poniendo ejemplos y llegamos a la conclusión de que cuando un número es divisible por otro (es decir que el primer número  es un múltiplo del segundo), entonces el resto es cero. Cuando no es divisible por el segundo número, obtenemos otra cosa que no es cero.

Las funciones FILA() y COLUMNA() sin ningún argumento entre los paréntesis, nos devuelven respectivamente un número que corresponde a la fila y la columna actual.

Entonces, si unimos las funciones anteriores y ponemos la fórmula =RESIDUO(FILA();2) ó =RESIDUO(COLUMNA();2) se nos devolverá un cero para las filas/columnas pares y un 1 para las filas/columnas impares. Y como sabemos, en informática el cero tiene un valor falso y el uno un valor cierto.

Volviendo a la cuestión principal que era el objetivo de nuestra entrada: colorear alternativamente las filas o columnas de una hoja, la formula anterior puede ser usada como formato condicional,veamos ahora cómo conseguirlo. Señalaremos previamente el rango al que queremos aplicar el formato. Vamos a la opción de formato condicional que se encuentra dentro de Inicio. Elegimos establecer Nueva regla… Buscamos entre las distintas posibilidades, eligiendo la última de ellas que se llama Utilice una fórmula que determine las celdas para aplicar el formato (efectivamente, queremos una fórmula para aplicar el formato), y nos aparecerá la ventana siguiente:

Entrada de un formato condicional mediante una fórmula
Aplicación de un formato condicional

En el apartado Dar formato a los valores donde esta fórmula sea verdadera: escribiremos la fórmula que acabamos de determinar, es decir: =RESIDUO(FILA();2). Y dando en el botón Formato determinaremos el formato elegido, para nuestro ejemplo debemos poner el Relleno en el color deseado.

Si damos a aceptar, efectivamente se nos colorean las filas o columnas (según la formula que hayamos puesto) alternativamente. Sin embargo, la fórmula tal y como está, colorea las filas/columnas impares. Si específicamente necesitamos colorear las filas/columnas pares y dejar en blanco las impares, podemos aplicar otra función que es NO(). La función NO() niega todo el argumento que tenga dentro. Entonces sería: =NO(RESIDUO(FILA();2)).

Como última observación diremos que el formato se aplica a todas las filas o columnas independientemente de que tengan o no contenido. Si quisiéramos aplicar un formato sólo para aquellos casos en que haya algo escrito en alguna celda y de esta manera fuera dinámico, es decir que al escribir nuevas entradas en la hoja se cambiara, tenemos que añadir algo más a nuestra fórmula.

Pero eso lo explicaremos en otro momento…