Excel

La celda contiene una de muchas cosas

Cell Contains One Many Things

Fórmula de Excel: la celda contiene una de muchas cosasFórmula genérica
= SUMPRODUCT (-- ISNUMBER ( SEARCH (things,A1)))>0
Resumen

Para probar si una celda contiene una de muchas cadenas, puede usar una fórmula basada en el BUSCAR , ES NÚMERO y SUMPRODUCTO funciones. La fórmula en C5, copiada, es:

|_+_|

dónde cosas es el rango con nombre E5: E7.



Explicación

El objetivo de este ejemplo es probar cada celda en B5: B14 para ver si contiene alguna de las cadenas en el rango con nombre cosas (E5: E7). Estas cadenas pueden aparecer en cualquier lugar en la celda, por lo que este es un problema literal 'contiene'. La fórmula en C5, copiada, es:

|_+_|

Esta fórmula se basa en otra formula que 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:

|_+_|

Cuando el Buscando función encuentra una cadena, devuelve la posición de esa cadena como un número. Si SEARCH no encuentra una cadena, devuelve un #VALOR! error. Esto significa que ISNUMBER devolverá VERDADERO si hay una coincidencia y FALSO si no.

En este ejemplo, el objetivo es verificar más de una cadena, por lo que le estamos dando a la función de BÚSQUEDA un lista de cadenas en el rango con nombre cosas . Dado que hay 3 cadenas en cosas ('rojo', 'verde' y 'azul'), BÚSQUEDA devuelve 3 resultados en una formación como esto:

cómo hacer que Excel cuente celdas con texto
|_+_|

Debido a que no se encuentran 'rojo' y 'azul', la BÚSQUEDA devuelve un #VALOR! error. Sin embargo, debido a que aparece 'verde' cerca del final del texto en la celda B5, BUSCAR devuelve 23 (es decir, 'verde' comienza en el carácter 23).

Esta matriz se devuelve directamente al Función ISNUMBER , que convierte los elementos de la matriz en VERDADERO o FALSO:

|_+_|

Lógicamente, si tenemos incluso un VERDADERO en la matriz, sabemos que una celda contiene al menos una de las cadenas que estamos buscando. La forma más fácil de verificar VERDADERO es sumar todos los valores. Podemos hacer eso con el Función SUMPRODUCTO , pero primero necesitamos coaccionar los valores VERDADERO / FALSO a 1 y 0 con un doble negativa (--) como esto:

|_+_|

Esto produce una nueva matriz que contiene solo 1 y 0:

ejemplos de rango de criterios de filtro avanzados de Excel
|_+_|

que se entrega directamente a SUMPRODUCT:

|_+_|

Con solo una matriz para procesar, SUMPRODUCT suma los elementos de la matriz y devuelve un resultado. Cualquier resultado distinto de cero significa que tenemos un 'acierto', por lo que agregamos> 0 para forzar un resultado final de VERDADERO o FALSO:

|_+_|

Tenga en cuenta que cualquier combinación de coincidencias devolverá un número mayor que cero y hará que la fórmula devuelva VERDADERO.

Con una lista codificada

No es necesario utilizar un rango para buscar la lista de cadenas. También puede utilizar un constante de matriz . Por ejemplo, para buscar 'rojo', 'azul' o 'verde', puede usar una fórmula como esta:

|_+_|

Función SUM

Históricamente, SUMPRODUCTO aparece a menudo en fórmulas de matriz , porque puede manejar matrices de forma nativa, sin control + shift + enter . Esto hace que la fórmula sea 'más amigable' para la mayoría de los usuarios. En Excel 365 , que maneja matrices nativamente , la Función SUM se puede usar en lugar de SUMPRODUCT sin control + shift + enter:

cómo usar la función max en Excel
|_+_|

Evitar coincidencias falsas

Un problema con este enfoque es que puede obtener coincidencias falsas de subcadenas que aparecen dentro de palabras más largas. Por ejemplo, si intenta hacer coincidir 'dr', también puede encontrar 'Andrea', 'beber', 'seco', etc. ya que 'dr' aparece dentro de estas palabras. Esto sucede porque SEARCH automáticamente hace una coincidencia 'contiene'.

Para un truco rápido, puede agregar espacio alrededor de las palabras de búsqueda (es decir, 'dr' o 'dr') para evitar atrapar 'dr' en otra palabra. Pero esto fallará si 'dr' aparece primero o último en una celda, o aparece con puntuación.

Si necesita una solución más precisa, una opción es normalizar el texto primero en un columna de ayuda , teniendo cuidado de agregar también un espacio inicial y final. Luego, usa la fórmula de esta página en el texto resultante.

Archivos adjuntos Archivo la celda contiene una de muchas cosas.xlsx Autor Dave Bruns


^