EXCEL: La celda contiene muchas cosas

Si desea probar una celda para ver si contiene todos los elementos de una lista, puede hacerlo con una fórmula que use la función HALLAR, con la ayuda de las funciones ESNUMERO, SUMAPRODUCTO y CONTARA.

Contexto

Supongamos que tiene una lista de cadenas de texto en el rango B5: B8, y desea averiguar si estas celdas contienen todas las palabras en otro rango, E5: E7.

Podría crear una fórmula que use declaraciones SI anidadas para verificar cada elemento, pero que no se escalará bien si tiene muchas cosas que buscar. Cada vez que agregue una palabra para buscar, deberá agregar otro SI anidado y ajustar los paréntesis.

Solución

La solución es crear una fórmula que cuente todas las coincidencias a la vez. Una vez que tenemos eso, simplemente comparamos ese recuento con el recuento de artículos que estamos buscando. Si coinciden, sabemos que una celda contiene todos los elementos.

En el ejemplo que se muestra, la fórmula que estamos usando es:

=SUMAPRODUCTO(--ESNUMERO(HALLAR(things,B5)))=CONTARA(things)

Cómo funciona esta fórmula

La clave es este fragmento:

ESNUMERO(HALLAR(things,B5)

Esto se basa en otra fórmula (explicada en detalle aquí) que simplemente comprueba una celda para una sola subcadena. Si la celda contiene la subcadena, la fórmula devuelve VERDADERO. Si no, la fórmula devuelve FALSO.

Sin embargo, si le damos a la misma fórmula una lista de cosas (en este caso, estamos usando un rango con nombre llamado "cosas", E5: E7) nos devolverá una lista de valores VERDADERO / FALSO, uno para cada elemento en . El resultado es una matriz que se ve así:

{VERDADERO; VERDADERO; VERDADERO}

Donde cada VERDADERO representa un elemento encontrado, y cada FALSO representa un elemento no encontrado.

Podemos forzar los valores VERDADERO / FALSO a 1s y 0s con un doble negativo (-, también llamado doble unario):

--ESNUMERO(HALLAR(things,B5))

que produce una matriz como esta:

{1; 1; 1}

A continuación, procesamos esta matriz con SUMAPRODUCTO, que nos dará una suma total. Si esta suma es igual al número de elementos en el rango "cosas" con nombre, sabemos que hemos encontrado todas las cosas y podemos devolver VERDADERO. La forma en que hacemos esto es comparar los dos números directamente. Obtenemos un recuento de celdas no en blanco en "cosas" usando CONTARA:

=CONTARA(things)

Con una lista codificada

No hay ningún requisito de que use un rango para su lista de cosas. Si solo está buscando un pequeño número de cosas, puede usar una lista en formato de matriz, que se denomina constante de matriz. Por ejemplo, si solo está buscando los colores rojo, azul y verde, puede usar {"rojo", "azul", "verde"} de esta manera:

=SUMAPRODUCTO(--ESNUMERO(HALLAR({"yellow","green","dog"},B5)))=CONTARA(things)