EXCEL: Extraer todas las coincidencias parciales.

Para extraer todas las coincidencias basadas en una coincidencia parcial, puede usar una fórmula de matriz basada en las funciones INDICE y AGREGAR, con el apoyo de ESNUMERO y HALLAR. En el ejemplo que se muestra, la fórmula en G5 es:

=SI(F5>ct,"",INDICE(data,AGREGAR(15,6,(FILA(data)-FILA($B$5)+1)/ESNUMERO(HALLAR(search,data)),F5)))

con los siguientes rangos con nombre: "search" = D5, "ct" = D8, "data" = B5: B55.

Nota: esta es una fórmula de matriz, pero no requiere control + shift + enter, ya que AGREGAR puede manejar matrices de forma nativa.

Cómo funciona esta fórmula

El núcleo de esta fórmula es la función INDICE, con AGREGAR utilizado para determinar la "enésima coincidencia" para cada fila en el área de extracción:

INDICE(data,nth_match_formula)

Casi todo el trabajo consiste en averiguar e informar qué filas en "datos" coinciden con la cadena de búsqueda, e informar la posición de cada valor coincidente a INDICE. Esto se hace con la función AGREGAR configurada de esta manera:

AGREGAR(15,6,(FILA(data)-FILA($B$5)+1)/ESNUMERO(HALLAR(search,data)),F5)

El primer argumento, 15, le dice a AGREGAR que se comporte como K.ESIMO.MENOR y que devuelva el enésimo valor más pequeño. El segundo argumento, 6, es una opción para ignorar los errores. El tercer argumento es una expresión que genera una matriz de resultados coincidentes (se describe a continuación). El cuarto argumento, F5, actúa como "k" en K.ESIMO.MENOR para especificar el valor "enésimo".

AGREGAR opera en matrices, y la siguiente expresión crea una matriz para el tercer argumento dentro de AGREGAR:

(FILA(data)-FILA($B$5)+1)/ESNUMERO(HALLAR(search,data))

Aquí, la función FILA se usa para generar una matriz de números de fila relativos, y ESNUMERO y HALLAR se usan juntos para hacer coincidir la cadena de búsqueda con los valores de los datos, lo que genera una matriz de valores VERDADERO y FALSO.

El bit inteligente es dividir los números de fila por los resultados de búsqueda. En una operación matemática como esta, VERDADERO se comporta como 1 y FALSO se comporta como cero. El resultado es que los números de fila asociados con una coincidencia positiva se dividen entre 1 y sobreviven a la operación, mientras que los números de fila asociados con valores no coincidentes se destruyen y se convierten en errores # DIV / 0. Debido a que AGREGAR está configurado para ignorar errores, ignora los errores # DIV / 0 y devuelve el "número más pequeño" en los valores restantes, utilizando el número en la columna F para "enésimo".

Gestionar el rendimiento

Al igual que todas las fórmulas de matriz, esta fórmula es "costosa" en términos de recursos con un gran conjunto de datos. Para minimizar los impactos en el rendimiento, toda la fórmula INDICE y COINCIDIR está envuelta en SI de esta manera:

=SI(F5>ct,"",formula)

donde el rango con nombre "ct" (D8) contiene esta fórmula:

=CONTAR.SI(data,"*"&search&"*")

Esta comprobación detiene la ejecución de la parte INDICE y AGREGAR de la fórmula una vez que se han extraído todos los valores coincidentes.

Fórmula de matriz con K.ESIMO.MENOR

Si su versión de Excel no tiene la función AGREGAR, puede usar una fórmula alternativa basada en K.ESIMO.MENOR y SI:

=SI(F5>ct,"",INDICE(data,K.ESIMO.MENOR(SI(ESNUMERO(HALLAR(search,data)),FILA(data)-FILA($B$5)+1),F5)))

Nota: esta es una fórmula de matriz y debe ingresarse con control + shift + enter.