Explicación
Para probar una celda para una de varias cadenas y devolver un resultado personalizado para la primera coincidencia encontrada, puede usar una fórmula INDICE / COINCIDIR basada en la función HALLAR. En el ejemplo que se muestra, la fórmula en C5 es:
donde las cosas (E5: E8) y los resultados (F5: F8) se denominan rangos.
Esta es una fórmula de matriz y debe ingresarse con Control + Shift + Enter.
Cómo funciona esta fórmula
Esta fórmula utiliza dos rangos con nombre: cosas y resultados. Si está transfiriendo esta fórmula directamente, asegúrese de usar rangos con nombre con los mismos nombres (definidos en función de sus datos). Si no desea usar rangos con nombre, use referencias absolutas en su lugar.
El núcleo de esta fórmula es este fragmento:
Esto se basa en otra fórmula (explicada en detalle aquí) que verifica una celda para una sola subcadena. Si la celda contiene la subcadena, la fórmula devuelve VERDADERO. Si no, la fórmula devuelve FALSO.
Debido a que le estamos dando a la función HALLAR más de una cosa para buscar, en las cosas de rango con nombre, nos dará más un resultado, en una matriz que se ve así:
{#VALUE!;9;#VALUE!;#VALUE!}
Los números representan coincidencias en cosas, los errores representan elementos que no se encontraron.
Para simplificar la matriz, utilizamos la función ESNUMERO para convertir todos los elementos de la matriz a VERDADERO o FALSO. Cualquier número válido se convierte en VERDADERO, y cualquier error (es decir, algo que no se encuentra) se convierte en FALSO. El resultado es una matriz como esta:
{FALSO;VERDADERO;FALSO;FALSO}
que entra en la función COINCIDIR como argumento lookup_array, con un valor lookup_ver de VERDADERO:
COINCIDIR(VERDADERO,{FALSO;VERDADERO;FALSO;FALSO},0) // returns 2
COINCIDIR luego devuelve la posición del primer VERDADERO encontrado, 2 en este caso.
Finalmente, usamos la función INDICE para recuperar un resultado de los resultados del rango nombrado en esa misma posición:
=INDICE(results,2) // returns "found red"
Puede personalizar el rango de resultados con los valores que tengan sentido en su caso de uso.
Prevenir falsas coincidencias
Un problema con este enfoque con el enfoque ESNUMERO + HALLAR es que puede obtener coincidencias falsas de coincidencias parciales dentro de palabras más largas. Por ejemplo, si intenta hacer coincidir "dr", también puede encontrar "Andrea", "bebió", "goteo", etc., ya que "dr" aparece dentro de estas palabras. Esto sucede porque HALLAR automáticamente hace una coincidencia "contiene tipo".
Para una solución rápida, puede ajustar las palabras de búsqueda en caracteres de espacio (es decir, "dr" o "dr") para evitar encontrar "dr" en otra palabra. Pero esto fallará si "dr" aparece primero o último en una celda.
Si necesita una solución más robusta, una opción es normalizar el texto primero en una columna auxiliar y agregar un espacio inicial y final. Luego use la fórmula de esta página en el texto de la columna auxiliar, en lugar del texto original.
0 comentarios:
Publicar un comentario