Generisk formel
=IF(F5>ct,"",INDEX(data,AGGREGATE(15,6,(ROW(data)-ROW($B$5)+1)/ISNUMBER(SEARCH(search,data)),F5)))
Sammanfattning
För att extrahera alla matchningar baserat på en partiell matchning kan du använda en matrisformel baserad på INDEX- och AGGREGATE-funktionerna med stöd från ISNUMBER och SEARCH. I exemplet som visas är formeln i G5:
=IF(F5>ct,"",INDEX(data,AGGREGATE(15,6,(ROW(data)-ROW($B$5)+1)/ISNUMBER(SEARCH(search,data)),F5)))
med följande namngivna intervall: "sök" = D5, "ct" = D8, "data" = B5: B55.
Obs: detta är en matrisformel, men det kräver inte kontroll + shift + enter, eftersom AGGREGATE kan hantera arrays naturligt.
Förklaring
Kärnan i denna formel är INDEX-funktionen, med AGGREGATE som används för att räkna ut den "nte matchningen" för varje rad i extraktområdet:
INDEX(data,nth_match_formula)
Nästan allt arbete går ut på att räkna ut och rapportera vilka rader i "data" som matchar söksträngen och rapportera positionen för varje matchande värde till INDEX. Detta görs med funktionen AGGREGATE konfigurerad så här:
AGGREGATE(15,6,(ROW(data)-ROW($B$5)+1)/ISNUMBER(SEARCH(search,data)),F5)
Det första argumentet, 15, säger att AGGREGATE ska bete sig som LITT och returnera nth minsta värden. Det andra argumentet, 6, är ett alternativ att ignorera fel. Det tredje argumentet är ett uttryck som genererar en matris med matchande resultat (beskrivs nedan). Det fjärde argumentet, F5, fungerar som "k" i SMALL för att ange "nth" -värdet.
AGGREGATE fungerar på matriser, och uttrycket nedan bygger en matris för det tredje argumentet i AGGREGATE:
(ROW(data)-ROW($B$5)+1)/ISNUMBER(SEARCH(search,data))
Här används ROW-funktionen för att generera en matris med relativa radnummer, och ISNUMBER och SEARCH används tillsammans för att matcha söksträngen mot värden i data, vilket genererar en array med SANT och FALSKA värden.
Den smarta biten är att dela radnumren med sökresultaten. I en matteoperation som den här beter sig SANT som 1 och FALSE beter sig som noll. Resultatet är att radnummer associerade med en positiv matchning divideras med 1 och överlever operationen, medan radnummer som är associerade med icke-matchande värden förstörs och blir # DIV / 0-fel. Eftersom AGGREGATE är inställt på att ignorera fel ignorerar det # DIV / 0-felen och returnerar det "n" minsta antalet i de återstående värdena, med hjälp av siffran i kolumn F för "nth".
Hantera prestanda
Liksom alla matrisformler är denna formel "dyr" när det gäller resurser med en stor datamängd. För att minimera prestandapåverkan är hela INDEX- och MATCH-formeln inslagen i OM så här:
=IF(F5>ct,"",formula)
där det angivna intervallet "ct" (D8) innehåller denna formel:
=COUNTIF(data,"*"&search&"*")
Den här kontrollen hindrar att INDEX- och AGGREGATE-delen av formeln körs när alla matchande värden har extraherats.
Arrayformel med SMALL
Om din version av Excel inte har funktionen AGGREGATE kan du använda en alternativ formel baserad på SMALL och IF:
=IF(F5>ct,"",INDEX(data,SMALL(IF(ISNUMBER(SEARCH(search,data)),ROW(data)-ROW($B$5)+1),F5)))
Obs: detta är en matrisformel och måste anges med kontroll + skift + enter.
