Excel

Obtener la primera celda de coincidencia contiene

Get First Match Cell Contains

Fórmula de Excel: Obtener la primera celda de coincidencia contieneFórmula genérica
{= INDEX (things, MATCH (TRUE, ISNUMBER ( SEARCH (things,A1)),0))}
Resumen

Para verificar una celda en busca de una de varias cosas y devolver la primera coincidencia encontrada en la lista, puede usar una fórmula INDICE / COINCIDIR que usa BUSCAR o ENCONTRAR para ubicar una coincidencia. En el ejemplo que se muestra, la fórmula en C5 es:





|_+_|

donde 'cosas' es el rango con nombre E5: E9.

contar el número de celdas en un rango

Nota: esta es una fórmula de matriz y debe ingresarse con Control + Shift + Enter.





Explicación

En este ejemplo, tenemos una lista de colores en un rango con nombre llamado 'cosas' (E5: E9). Queremos comprobar el texto de la columna B para ver si contiene alguno de estos colores. Si es así, queremos devolver el nombre del primer color encontrado.

Trabajando de adentro hacia afuera, esta fórmula usa el Función ISNUMBER y Buscando función para buscar el texto en B5 para cada color enumerado en 'cosas' como esta:



|_+_|

Esta expresión se basa en una fórmula ( explicado en detalle aquí ) que comprueba una celda para una sola subcadena. Si la celda contiene la subcadena, la expresión devuelve VERDADERO. Si no, la expresión devuelve FALSO.

Cuando le damos a esta BÚSQUEDA un lista de cosas (en lugar de una cosa) nos devolverá una serie de resultados. Cada color encontrado generará una posición numérica y los colores no encontrados generarán un error:

|_+_|

La función ISNUMBER luego convierte los resultados en valores VERDADERO / FALSO. Cualquier número se convierte en VERDADERO y cualquier error (no encontrado) se convierte en FALSO. El resultado es una matriz como esta:

|_+_|

Esta matriz se devuelve al Función COINCIDIR como el argumento de la matriz. El valor de búsqueda es VERDADERO y el tipo de coincidencia se establece en cero para forzar una coincidencia exacta. Cuando hay un color coincidente, COINCIDIR devuelve la primera posición VERDADERA encontrada. Este valor se introduce en el Función INDICE como el número de fila, con el rango con nombre 'cosas' proporcionado como la matriz. Cuando hay al menos una coincidencia, INDICE devuelve el color en esa posición. Cuando no se encuentra ninguna coincidencia, esta fórmula devuelve el error # N / A.

Con valores codificados

Si no desea configurar un rango con nombre externo como 'cosas' en este ejemplo, puede codificar valores en la fórmula como 'constantes de matriz' como esta:

¿Cómo se usa el pintor de formato en Excel?
|_+_|

Obtener la primera coincidencia en la celda

El lenguaje aquí es bastante confuso, pero la fórmula anterior devolverá la primera coincidencia encontrada en el lista de cosas para buscar . Si, en cambio, desea devolver la primera coincidencia encontrada en la celda que se está probando , puedes probar una fórmula como esta:

|_+_|

En esta versión de la fórmula, la función COINCIDIR está configurada para buscar el resultado de este fragmento:

|_+_|

que usa el Función agregada para obtener el valor mínimo en los resultados devueltos por SEARCH. Necesitamos AGREGAR aquí, porque la matriz entrante probablemente contenga errores (devueltos por SEARCH cuando no se encuentran cosas), y necesitamos una función que ignore esos errores y aún nos dé el valor numérico mínimo.

El resultado de AGGREGATE se devuelve directamente a COINCIDIR como valor de búsqueda, junto con la misma matriz devuelta por SEARCH. El resultado final es la primera coincidencia que se encuentra en la celda, no la primera coincidencia que se encuentra en la lista de cosas.

Autor Dave Bruns


^