El tutorial proporciona una serie de ejemplos de fórmulas «Excel si contiene» que muestran cómo devolver algo en otra columna si una celda de destino contiene un valor requerido, cómo buscar con coincidencia parcial y probar múltiples criterios con la lógica OR y AND.
Una de las tareas más comunes en Excel es verificar si una celda contiene un valor de interés. ¿Qué tipo de valor puede ser ese? Cualquier texto o número, texto específico o cualquier valor (no una celda vacía).
Existen varias variaciones de la fórmula «Si la celda contiene» en Excel, dependiendo exactamente de los valores que desee encontrar. Por lo general, usará la función IF para realizar una prueba lógica y devolverá un valor cuando se cumpla la condición (la celda contiene) y/u otro valor cuando no se cumpla la condición (la celda no contiene). Los siguientes ejemplos cubren los escenarios más frecuentes.
Si la celda contiene algún valor, entonces
Para empezar, veamos cómo encontrar celdas que contengan cualquier cosa: cualquier texto, número o fecha. Para esto, vamos a usar una fórmula IF simple que verifica las celdas que no están en blanco.
SI(celda<>«», valor_a_devolver, «»)
Por ejemplo, para devolver «No en blanco» en la columna B si la celda de la columna A en la misma fila contiene algún valor, ingrese la siguiente fórmula en B2 y luego haga doble clic en el pequeño cuadrado verde en la esquina inferior derecha para copiar la fórmula abajo de la columna:
=IF(A2<>"", "Not blank", "")
El resultado será similar a esto:
Si la celda contiene texto, entonces
Si desea encontrar solo celdas con valores de texto que ignoren números y fechas, use IF en combinación con la función ISTEXT. Aquí está la fórmula genérica para devolver algún valor en otra celda si una celda de destino contiene cualquier texto:
SI(ESTEXTO(celda), valor_a_devolver, «»)
Supongamos que desea insertar la palabra «sí» en la columna B si una celda de la columna A contiene texto. Para hacerlo, pon la siguiente fórmula en B2:
=IF(ISTEXT(A2), "Yes", "")
Si la celda contiene un número, entonces
De manera similar, puede identificar celdas con valores numéricos (números y fechas). Para ello, utilice la función SI junto con ESNUMERO:
SI(ESNUMERO(celda), valor_a_devolver, «»)
La siguiente fórmula devuelve «sí» en la columna B si una celda correspondiente en la columna A contiene cualquier número:
=IF(ISNUMBER(A2), "Yes", "")
Si la celda contiene un texto específico
Encontrar celdas que contengan cierto texto (o números o fechas) es fácil. Escribe una fórmula IF regular que verifica si una celda de destino contiene el texto deseado y escribe el texto para devolver en el argumento valor_si_verdadero.
SI(celda=»texto», valor_a_retorno, «»)
Por ejemplo, para averiguar si la celda A2 contiene «manzanas», use esta fórmula:
=IF(A2="apples", "Yes", "")
Si la celda no contiene un texto específico
Si está buscando el resultado opuesto, es decir, devolver algún valor a otra columna si una celda de destino no contiene el texto especificado («manzanas»), realice una de las siguientes acciones.
Proporcione una cadena vacía («») en el argumento valor_si_verdadero y texto para devolver en el argumento valor_si_falso:
=IF(A2="apples", "", "Not apples")
O bien, coloque el operador «distinto de» en prueba_lógica y texto para devolver en valor_si_verdadero:
=IF(A2<>"apples", "Not apples", "")
De cualquier manera, la fórmula producirá este resultado:
Si la celda contiene texto: fórmula que distingue entre mayúsculas y minúsculas
Para obligar a su fórmula a distinguir entre caracteres en mayúsculas y minúsculas, use la función EXACTA que verifica si dos cadenas de texto son exactamente iguales, incluida la mayúscula:
=IF(EXACT(A2,"APPLES"), "Yes", "")
También puede ingresar la cadena de texto del modelo en alguna celda (por ejemplo, en C1), corregir la referencia de la celda con el signo $ ($C$1) y comparar la celda de destino con esa celda:
=IF(EXACT(A2,$C$1), "Yes", "")
Si la celda contiene una cadena de texto específica (coincidencia parcial)
Hemos terminado con las tareas triviales y pasamos a otras más desafiantes e interesantes 🙂 En este ejemplo, se necesitan tres funciones diferentes para averiguar si un carácter o subcadena determinado es parte del contenido de la celda:
SI(ESNÚMERO(BÚSQUEDA(«texto», celda)), valor_a_devolver,»»)
Trabajando de adentro hacia afuera, esto es lo que hace la fórmula:
- La función BUSCAR busca una cadena de texto y, si la encuentra, devuelve la posición del primer carácter, el #¡VALOR! error de lo contrario.
- La función ESNUMERO verifica si la BÚSQUEDA tuvo éxito o falló. Si BÚSQUEDA ha devuelto algún número, ESNÚMERO devuelve VERDADERO. Si la BÚSQUEDA da como resultado un error, ESNÚMERO devuelve FALSO.
- Finalmente, la función IF devuelve el valor especificado para las celdas que tienen VERDADERO en la prueba lógica, una cadena vacía («») de lo contrario.
Y ahora, veamos cómo funciona esta fórmula genérica en hojas de trabajo de la vida real.
Si la celda contiene cierto texto, ponga un valor en otra celda
Supongamos que tiene una lista de pedidos en la columna A y desea encontrar pedidos con un identificador específico, diga «A-«. La tarea se puede lograr con esta fórmula:
=IF(ISNUMBER(SEARCH("A-",A2)),"Valid","")
En lugar de codificar la cadena en la fórmula, puede ingresarla en una celda separada (E1), la referencia a esa celda en su fórmula:
=IF(ISNUMBER(SEARCH($E$1,A2)),"Valid","")
Para que la fórmula funcione correctamente, asegúrese de bloquear la dirección de la celda que contiene la cadena con el signo $ (referencia de celda absoluta).
Si la celda contiene texto específico, cópielo en otra columna
Si desea copiar el contenido de las celdas válidas en otro lugar, simplemente proporcione la dirección de la celda evaluada (A2) en el argumento valor_si_verdadero:
=IF(ISNUMBER(SEARCH($E$1,A2)),A2,"")
La siguiente captura de pantalla muestra los resultados:
Si la celda contiene texto específico: fórmula que distingue entre mayúsculas y minúsculas
En los dos ejemplos anteriores, las fórmulas no distinguen entre mayúsculas y minúsculas. En situaciones en las que trabaje con datos que distinguen entre mayúsculas y minúsculas, utilice la función BUSCAR en lugar de BUSCAR para distinguir entre mayúsculas y minúsculas.
Por ejemplo, la siguiente fórmula identificará solo los pedidos con la «A-» mayúscula e ignorará la «a-» minúscula.
=IF(ISNUMBER(FIND("A-",A2)),"Valid","")
Si la celda contiene una de muchas cadenas de texto (lógica OR)
Para identificar las celdas que contienen al menos una de las muchas cosas que está buscando, use una de las siguientes fórmulas.
Fórmula de BÚSQUEDA SI O ES UN NÚMERO
El enfoque más obvio sería verificar cada subcadena individualmente y hacer que la función OR devuelva VERDADERO en la prueba lógica de la fórmula IF si se encuentra al menos una subcadena:
SI(O(ESNÚMERO(BÚSQUEDA(«cadena1», celda)), ESNÚMERO(BUSQUEDA(«cadena2», celda))), valor_a_devolver, «»)
Supongamos que tiene una lista de SKU en la columna A y desea encontrar aquellos que incluyen «vestido» o «falda». Puedes hacerlo usando esta fórmula:
=IF(OR(ISNUMBER(SEARCH("dress",A2)),ISNUMBER(SEARCH("skirt",A2))),"Valid ","")
La fórmula funciona bastante bien para un par de elementos, pero ciertamente no es el camino a seguir si desea verificar muchas cosas. En este caso, un mejor enfoque sería usar la función SUMPRODUCT como se muestra en el siguiente ejemplo.
SUMPRODUCT ISNUMBER BÚSQUEDA fórmula
Si está tratando con múltiples cadenas de texto, buscar cada cadena individualmente haría que su fórmula fuera demasiado larga y difícil de leer. Una solución más elegante sería incrustar la combinación BÚSQUEDA DE ESNÚMERO en la función SUMAPRODUCTO y ver si el resultado es mayor que cero:
SUMPRODUCT(–ISNUMBER(BUSQUEDA(cadenas, celda)))>0
Por ejemplo, para averiguar si A2 contiene alguna de las palabras ingresadas en las celdas D2:D4, use esta fórmula:
=SUMPRODUCT(--ISNUMBER(SEARCH($D$2:$D$4,A2)))>0
Alternativamente, puede crear un rango con nombre que contenga las cadenas para buscar o proporcionar las palabras directamente en la fórmula:
=SUMPRODUCT(--ISNUMBER(SEARCH({"dress","skirt","jeans"},A2)))>0
De cualquier manera, el resultado será similar a este:
Para que la salida sea más fácil de usar, puede anidar la fórmula anterior en la función SI y devolver su propio texto en lugar de los valores VERDADERO/FALSO:
=IF(SUMPRODUCT(--ISNUMBER(SEARCH($D$2:$D$4,A2)))>0, "Valid", "")
Cómo funciona esta fórmula
Básicamente, usa ESNUMERO junto con BUSCAR como se explica en el ejemplo anterior. En este caso, los resultados de la búsqueda se representan en forma de matriz como {VERDADERO;FALSO;FALSO}. Si una celda contiene al menos una de las subcadenas especificadas, habrá VERDADERO en la matriz. El operador unario doble (–) fuerza los valores VERDADERO/FALSO a 1 y 0, respectivamente, y entrega una matriz como {1;0;0}. Finalmente, la función SUMPRODUCT suma los números y seleccionamos las celdas donde el resultado es mayor que cero.
Si la celda contiene varias cadenas (lógica AND)
En situaciones en las que desee buscar celdas que contengan todas las cadenas de texto especificadas, utilice la ya familiar combinación BÚSQUEDA DE ESNÚMERO junto con SI Y:
SI(Y(ESNÚMERO(BÚSQUEDA(«cadena1»,celda)), ESNÚMERO(BUSQUEDA(«cadena2″,celda))), valor_a_devolver,»»)
Por ejemplo, puede encontrar SKU que contengan tanto «vestido» como «azul» con esta fórmula:
=IF(AND(ISNUMBER(SEARCH("dress",A2)),ISNUMBER(SEARCH("blue",A2))),"Valid ","")
O bien, puede escribir las cadenas en celdas separadas y hacer referencia a esas celdas en su fórmula:
=IF(AND(ISNUMBER(SEARCH($D$2,A2)),ISNUMBER(SEARCH($E$2,A2))),"Valid ","")
Como solución alternativa, puede contar las ocurrencias de cada cadena y verificar si cada conteo es mayor que cero:
=IF(AND(COUNTIF(A2,"*dress*")>0,COUNTIF(A2,"*blue*")>0),"Valid","")
El resultado será exactamente como se muestra en la captura de pantalla anterior.
Cómo devolver resultados diferentes según el valor de la celda
En caso de que desee comparar cada celda de la columna de destino con otra lista de elementos y devolver un valor diferente para cada coincidencia, utilice uno de los siguientes enfoques.
IF anidados
La lógica de la fórmula IF anidada es tan simple como esto: usa una función IF separada para probar cada condición y devuelve diferentes valores dependiendo de los resultados de esas pruebas.
IF(celda=»texto_buscado1″, «texto_retorno1″, IF(celda=»texto_buscado2», «texto_retorno2″, IF(celda=»texto_buscado3», «texto_retorno3», «»)))
Supongamos que tiene una lista de elementos en la columna A y desea tener sus abreviaturas en la columna B. Para hacerlo, use la siguiente fórmula:
=IF(A2="apple", "Ap", IF(A2="avocado", "Av", IF(A2="banana", "B", IF(A2="lemon", "L", ""))))
Para obtener detalles completos sobre la sintaxis y la lógica de los IF anidados, consulte IF anidado de Excel: múltiples condiciones en una sola fórmula.
fórmula de búsqueda
Si está buscando una fórmula más compacta y mejor comprensible, use la función BUSCAR con los valores de búsqueda y retorno proporcionados como constantes de matriz vertical:
BUSCAR(celda, {«buscar_texto1″;»buscar_texto2″;»buscar_texto3»;…}, {«retorno_texto1″;»retorno_texto2″;»retorno_texto3»;…})
Para obtener resultados precisos, asegúrese de enumerar los valores de búsqueda en orden alfabeticode la A a la Z.
=LOOKUP(A2,{"apple";"avocado";"banana";"lemon"},{"Ap";"Av";"B";"L"})
En comparación con los IF anidados, la fórmula de búsqueda tiene una ventaja más: comprende el caracteres comodín y por lo tanto puede identificar coincidencias parciales.
Por ejemplo, si la columna A contiene algunos tipos de plátanos, puede buscar «*plátano*» y obtener la misma abreviatura («B») para todas esas celdas:
=LOOKUP(A2,{"apple";"avocado";"*banana*";"lemon"},{"Ap";"Av";"B";"L"})
Para obtener más información, consulte la fórmula de búsqueda como alternativa a los IF anidados.
Fórmula de búsqueda V
Cuando se trabaja con un conjunto de datos variables, puede ser más conveniente ingresar una lista de coincidencias en celdas separadas y recuperarlas usando una fórmula Vlookup, por ejemplo:
=VLOOKUP(A2, $D$2:$E$5, 2,FALSE )
Para obtener más información, consulte el tutorial BUSCARV de Excel para principiantes.
Así es como verifica si una celda contiene algún valor o texto específico en Excel. Para ver más de cerca las fórmulas discutidas en este tutorial, puede descargar nuestra muestra Excel si contiene libro de trabajo.
La próxima semana, vamos a seguir analizando Si la celda de Excel contiene fórmulas y aprenderemos a contar o sumar celdas relevantes, copiar o eliminar filas enteras que contengan esas celdas, y más. ¡Por favor manténgase al tanto!