FORMATO CONDICIONAL EN EXCEL

FORMATO CONDICIONAL EN EXCEL

Esta diseñado para cambiar el formato de una celda siempre y cuando cumpla con ciertas condiciones dadas por el usuario, se puede aplicar mas de un formato condicional a un rango de celdas.

Ej:   Se escriben en Excel ciertos números aleatorios:

Y se desea darles formato azul a aquellos que son menores o iguales a 3, formato verde a los que están entre 4 y 6 (inclusive) y rojo a los mayores de de 7.

1. Se selecciona el rango de números y damos clic en el menú Formato y luego en Formato Condicional.

2. Se selecciona la opción deseada y al frente se pone la condición:

3. Al dar clic en el botón “formato” se selecciona color, fuente, bordes, etc.

4. Como son varias condiciones para un mismo rango de celdas, debemos dar clic en el botón “agregar” y aparecerá otra ventana de condiciones para agregar la siguiente categoría de 4 a 6.

5. Cuando ya se tienen todas las condiciones establecidas, se da clic en aceptar.

6. Si se desea quitar el formato condicional, se selecciona el rango de celdas y se da clic en el menú “formato” y luego en “formato condicional”, en la parte inferior aparece el botón “eliminar”, se pueden eliminar todas o solo una de las condiciones.

7. Si se desea encontrar celdas con formato condicional en una hoja de cálculo, se va al menú “Edición”, luego a “ir a”, y se da clic en el botón “especial”, se selecciona la opción “celdas con formatos condicionales” y se da clic en aceptar.

Diferencia entre ENCONTRAR y HALLAR

Las palabras encontrar y hallar son sinónimos, sin embargo cuando hablamos de la función ENCONTRAR y la función HALLAR nos damos cuenta de que son funciones muy similares pero que tienen una diferencia peculiar.

Las funciones ENCONTRAR y HALLAR

Ambas funciones pertenecen al grupo de las funciones de texto y devuelven la posición inicial donde se encuentra un texto buscado dentro de otra cadena de texto. Inclusive la sintaxis de las funciones es prácticamente idéntica:

ENCONTRAR(texto_buscado, dentro_del_texto, [núm_inicial])

HALLAR(texto_buscado, dentro_del_texto, [núm_inicial])

Puedes observar que las funciones tienen el mismo número de argumentos y además todos los argumentos se refieren a lo mismo: texto_buscado es el texto o palabras que deseamos buscar, dentro_del_texto es el texto principal donde realizaremos la búsqueda y núm_inicial es un parámetro opcional que nos indica el carácter donde se iniciará la búsqueda. Entonces, si ambas funciones son tan parecidas ¿Por qué Microsoft decidió incluir ambas?

Diferencia entre ENCONTRAR y HALLAR

Vamos a encontrar la diferencia entre ambas funciones con un ejemplo muy sencillo. En la siguiente imagen puedes observar que tengo la misma cadena en la celda A1 y A2. Y para ambas buscaré la palabra “excel” solo que para una fórmula utilizaré la función HALLAR y para la otra utilizaré la funcion Encontrar.

La función HALLAR encuentra que la palabra “excel” comienza en la posición 1 de la cadena, sin embargo la función ENCONTRAR nos devuelve un error #¡VALOR! indicando que no ha encontrado dicha palabra. La diferencia entre ambas funciones es sutil pero importante de recordar: La función ENCONTRAR es sensible a mayúsculas y minúsculas mientras que la función HALLAR no lo es.

Para comprobar que efectivamente la función ENCONTRAR es sensible a mayúsculas cambiaré las fórmulas para buscar la palabra “Excel” con la letra E como mayúscula. Observa los nuevos resultados:

Ahora ya lo sabes. La única diferencia entre las funciones ENCONTRAR y HALLAR es que la función ENCONTRAR es sensible a mayúsculas y minúsculas.

Calcular la edad con Excel

Calcular la edad con Excel puede llegar a ser un poco complicado porque los cálculos no solo dependen del año sino también del día actual. La solución se complica cuando te das cuenta que es necesario considerar los años bisiestos.

En esta ocasión presentaré tres maneras diferentes de calcular la edad con Excel. Todas las fórmulas asumen que la celda A2 contiene la fecha nacimiento y que la celda B2 tiene la fecha actual que es calculada por la función HOY.

Calcular la edad dividiendo los días

El primer método obtiene la diferencia en días entre ambas fechas y el resultado lo divide entre 365.25. Este último número es porque cada cuatro años tenemos un año con 366 días (bisiesto) por lo que 365.25 es el promedio de los cuatro años. La función ENTERO eliminará los decimales del resultado.

La desventaja de este método es que no es muy exacto y tiene problemas serios cuando intentas calcular la edad con Excel de niños. Por ejemplo, para un niño que tenga exactamente 1 año de edad cumplido obtendremos el resultado 0 (cero) por haber realizado la división entre 365.25.

Calcular la edad con la función FRAC.AÑO

La función FRAC.AÑO devuelve la fracción de un año a partir del número total de días que existen entre dos fechas. Observa el resultado:

De igual manera he utilizado la función ENTERO para remover los decimales del resultado.

Calcular la edad con la función SIFECHA

La función SIFECHA es el método más exacto para calcular la edad con Excel. Para obtener los años de diferencia entre dos fechas utilizamos el argumento “y” en la función:

Así que solamente elige el método que te parezca más adecuado para calcular la edad con Excel e impleméntalo.

Determinar fechas específicas en Excel

En esta ocasión revisaremos diferentes métodos para determinar ciertas fechas específicas en Excel como determinar el número de día del año, conocer el último día el mes, así como otras fechas que pueden ser de utilidad al trabajar con Excel.

Calcular los días transcurridos en el año

Si la celda B1 contiene la fecha 12 de junio del 2012 y necesito saber los días del año que han transcurrido hasta ese día lo puedo hacer con la siguiente fórmula:

=B1 - FECHA(AÑO(B1),1,0)

Observa como la fórmula devuelve el número 164 que es justamente el número de días transcurridos:

Si deseas calcular el número de días que faltan para el final de año podemos utilizar la siguiente fórmula:

=FECHA(AÑO(B1),12,31) - B1

La fórmula nos devolverá los días que existen entre el fin de año y nuestra fecha:

Por supuesto que si la fecha actual la deseas obtener con la función HOY, entonces la fórmula será la siguiente:

=FECHA(AÑO(HOY()), 12, 31) - HOY()

Determinar la fecha del domingo pasado

Para conocer la fecha del domingo anterior a la fecha proporcionada en la celda B1 podemos utilizar la siguiente fórmula:

=B1 - RESIDUO(B1 - 1, 7)

Observa el resultado de la fórmula para una fecha determinada:

Es importante mencionar que el resultado de la fecha será un valor numérico y deberás aplicar el formato de fecha correspondiente. Esta misma fórmula funciona para encontrar otros días de la semana con tan solo cambiar el número que es restado en el primer argumento de la función RESIDUO.

Para este ejemplo se hizo la resta del número 1 para obtener la fecha del domingo anterior, pero si deseo obtener la fecha del lunes anterior debo utilizar el número 2:

=B1 - RESIDUO(B1 - 2, 7)

Para obtener la fecha del martes anterior debo restar el número 3 y así sucesivamente hasta llegar al número 7 que obtendrá la fecha del sábado anterior.

Determinar el último día del mes

Para obtener el último día del mes podemos utilizar la función FECHA pero con un pequeño truco que será incrementar el número del mes en 1 y utilizar el día 0 (cero). El día “cero” del mes siguiente será el último día del mes actual.

=FECHA(AÑO(B1), MES(B1) + 1, 0)

Observa cómo se calcula el último día del mes en base a una fecha especificada en la celda B1.

Haciendo una pequeña variación a esta fórmula podemos obtener el número total de días del mes en cuestión ya que hemos obtenido el último día del mes podemos saber el número de días de un mes con la ayuda de la función DIA:

=DIA(FECHA(AÑO(B1), MES(B1) + 1, 0))

Para mostrar el resultado adecuadamente debemos dar un formato de celda de número:

Combinar la función BUSCARV y SI.ERROR

La función BUSCARV es una de las funciones más utilizadas en Excel por lo que es muy probable que hayas visto el error #N/A cuando la función no ha encontrado el valor que estás buscando. Hoy veremos una opción para mostrar un mensaje de error más amigable.

La función BUSCARV en Excel

Hemos visto en artículos anteriores cómo la función BUSCARV nos ayuda a encontrar un valor dentro de una tabla de datos. Pero ¿qué sucede cuando la función BUSCARV no encuentra una coincidencia exacta? Observa cómo la función regresa un error del tipo #N/A:

Evitar desplegar el error #N/A con la función SI.ERROR

La función SI.ERROR fue introducida desde Excel 2007 y es de mucha utilidad cuando queremos detectar si una función nos ha devuelto un mensaje de error. En nuestro ejemplo no deseamos ver el mensaje de error #N/A sino que deseamos desplegar el mensaje “Nombre no encontrado” en caso de que la función BUSCARV no encuentre el Nombre especificado en la celda E1. Para alcanzar nuestro objetivo utilizamos la función SI.ERROR de la siguiente manera:

La función SI.ERROR solamente tiene dos argumentos, el primero es el valor o expresión que va a evaluar, que para nuestro ejemplo es la función BUSCARV, y el segundo argumento es el valor que regresará en caso de que el primer argumento devuelva un error.

En nuestro ejemplo la función BUSCARV no ha encontrado el nombre “Dana” por lo que regresa el error #N/A pero la función SI.ERROR se da cuenta de ello y no deja que se despliegue la leyenda #N/A sino que sabe que le hemos indicado que muestre el mensaje “Nombre no encontrado”. Por el contrario, si la función BUSCARV ha encontrado el valor que estaba buscando entonces la función SI.ERROR no tienen ningún efecto en el resultado. Observa el siguiente ejemplo donde busco el nombre “Diana” el cual sí es encontrado en la lista:

La función SI.ERROR nos ayuda a personalizar los mensajes de error de cualquiera de las funciones de Excel incluyendo a la función BUSCARV.

Contar valores únicos en Excel

En ocasiones necesitamos contar valores únicos en Excel de manera que podamos conocer la cantidad exacta de entradas que no se repiten dentro de un rango. Para resolver este problema haré uso de las fórmulas matriciales.

Supongamos que nos ha llegado un archivo de Excel que tiene la lista consolidada de varias personas con su ciudad de origen.

Ahora me han pedido que cuente las diferentes ciudades de la lista, es decir obtener el número de ciudades únicas de la columna B. Para este ejemplo lo podría hacer visualmente, pero si tengo una lista con miles de registros la tarea se puede complicar.

Fórmula para contar valores únicos en Excel

Para contar valores únicos en Excel podemos utilizar la siguiente fórmula matricial:

{=SUMA(1/CONTAR.SI(B2:B10, B2:B10))}

Recuerda que para ingresar una fórmula matricial debemos pulsar las teclas CTRL + MAYÚS + ENTRAR justo al terminar de introducir la fórmula lo cual hará que Excel coloque los corchetes alrededor de la fórmula. Observa el resultado de aplicar esta fórmula a los datos del ejemplo:

La única desventaja de esta fórmula es que dejará de funcionar adecuadamente si una celda está vacía y obtendremos un error #¡DIV/0! como resultado.

Para resolver este problema podemos utilizar la función SI.ERROR de manera que nuestra fórmula siga funcionando. Esta es la fórmula a utilizar:

=SUMA(SI.ERROR(1/CONTAR.SI(B2:B10, B2:B10), 0))

Observa el resultado al utilizar esta fórmula sobre el rango que contiene una celda vacía:

De esta manera hemos eliminado el error #¡DIV/0! y hemos logrado contar valores únicos en Excel aún dentro de un rango con celdas vacías.