Formula generica
=IF(F5>ct,"",INDEX(data,AGGREGATE(15,6,(ROW(data)-ROW($B$5)+1)/ISNUMBER(SEARCH(search,data)),F5)))
Sommario
Per estrarre tutte le corrispondenze in base a una corrispondenza parziale, è possibile utilizzare una formula di matrice basata sulle funzioni INDICE e AGGREGATA, con il supporto di NUMERO ISTANTANEO e RICERCA. Nell'esempio mostrato, la formula in G5 è:
=IF(F5>ct,"",INDEX(data,AGGREGATE(15,6,(ROW(data)-ROW($B$5)+1)/ISNUMBER(SEARCH(search,data)),F5)))
con i seguenti intervalli denominati: "search" = D5, "ct" = D8, "data" = B5: B55.
Nota: questa è una formula di matrice, ma non richiede control + shift + invio, poiché AGGREGATE può gestire gli array in modo nativo.
Spiegazione
Il cuore di questa formula è la funzione INDICE, con AGGREGATO utilizzato per calcolare l '"ennesima corrispondenza" per ogni riga nell'area di estrazione:
INDEX(data,nth_match_formula)
Quasi tutto il lavoro consiste nell'individuare e riportare quali righe in "dati" corrispondono alla stringa di ricerca e nel riportare la posizione di ogni valore corrispondente a INDICE. Questo viene fatto con la funzione AGGREGATE configurata in questo modo:
AGGREGATE(15,6,(ROW(data)-ROW($B$5)+1)/ISNUMBER(SEARCH(search,data)),F5)
Il primo argomento, 15, dice a AGGREGATO di comportarsi come SMALL e di restituire l'ennesimo valore più piccolo. Il secondo argomento, 6, è un'opzione per ignorare gli errori. Il terzo argomento è un'espressione che genera una matrice di risultati corrispondenti (descritti di seguito). Il quarto argomento, F5, agisce come "k" in SMALL per specificare il valore "nth".
AGGREGATE opera su array e l'espressione seguente crea un array per il terzo argomento all'interno di AGGREGATE:
(ROW(data)-ROW($B$5)+1)/ISNUMBER(SEARCH(search,data))
Qui, la funzione ROW viene utilizzata per generare un array di numeri di riga relativi e ISNUMBER e SEARCH vengono utilizzati insieme per abbinare la stringa di ricerca ai valori nei dati, che genera un array di valori TRUE e FALSE.
La parte intelligente è dividere i numeri di riga per i risultati della ricerca. In un'operazione matematica come questa, TRUE si comporta come 1 e FALSE si comporta come zero. Il risultato è che i numeri di riga associati a una corrispondenza positiva vengono divisi per 1 e sopravvivono all'operazione, mentre i numeri di riga associati a valori non corrispondenti vengono distrutti e diventano errori # DIV / 0. Poiché AGGREGATE è impostato per ignorare gli errori, ignora gli errori # DIV / 0 e restituisce il numero più piccolo "nth" nei valori rimanenti, utilizzando il numero nella colonna F per "nth".
Gestire le prestazioni
Come tutte le formule di matrice, questa formula è "costosa" in termini di risorse con un ampio set di dati. Per ridurre al minimo gli impatti sulle prestazioni, l'intera formula INDICE e CONFRONTA è racchiusa in IF in questo modo:
=IF(F5>ct,"",formula)
dove l'intervallo denominato "ct" (D8) contiene questa formula:
=COUNTIF(data,"*"&search&"*")
Questo controllo interrompe l'esecuzione delle parti INDICE e AGGREGATO della formula una volta che tutti i valori corrispondenti sono stati estratti.
Formula di matrice con PICCOLO
Se la tua versione di Excel non ha la funzione AGGREGA, puoi utilizzare una formula alternativa basata su PICCOLO e SE:
=IF(F5>ct,"",INDEX(data,SMALL(IF(ISNUMBER(SEARCH(search,data)),ROW(data)-ROW($B$5)+1),F5)))
Nota: questa è una formula di matrice e deve essere inserita con CTRL + MAIUSC + INVIO.
