Generisk formel
=IF(F5>ct,"",INDEX(data,AGGREGATE(15,6,(ROW(data)-ROW($B$5)+1)/ISNUMBER(SEARCH(search,data)),F5)))
Resumé
For at udtrække alle matches baseret på en delvis match kan du bruge en matrixformel baseret på INDEX- og AGGREGATE-funktionerne med støtte fra ISNUMBER og SEARCH. I det viste eksempel er formlen i G5:
=IF(F5>ct,"",INDEX(data,AGGREGATE(15,6,(ROW(data)-ROW($B$5)+1)/ISNUMBER(SEARCH(search,data)),F5)))
med følgende navngivne områder: "søg" = D5, "ct" = D8, "data" = B5: B55.
Bemærk: dette er en matrixformel, men det kræver ikke kontrol + shift + enter, da AGGREGATE kan håndtere arrays indbygget.
Forklaring
Kernen i denne formel er INDEX-funktionen, hvor AGGREGATE bruges til at finde ud af "nte match" for hver række i ekstraktområdet:
INDEX(data,nth_match_formula)
Næsten alt arbejdet går ud på at finde ud af og rapportere, hvilke rækker i "data" der matcher søgestrengen, og rapportere placeringen for hver matchende værdi til INDEX. Dette gøres med AGGREGATE-funktionen konfigureret således:
AGGREGATE(15,6,(ROW(data)-ROW($B$5)+1)/ISNUMBER(SEARCH(search,data)),F5)
Det første argument, 15, fortæller AGGREGATE at opføre sig som SMÅ og returnere nth mindste værdier. Det andet argument, 6, er en mulighed for at ignorere fejl. Det tredje argument er et udtryk, der genererer en matrix med matchende resultater (beskrevet nedenfor). Det fjerde argument, F5, fungerer som "k" i SMALL for at specificere "nth" -værdien.
AGGREGATE fungerer på arrays, og nedenstående udtryk bygger en matrix til det tredje argument inde i AGGREGATE:
(ROW(data)-ROW($B$5)+1)/ISNUMBER(SEARCH(search,data))
Her bruges ROW-funktionen til at generere en matrix med relative rækkenumre, og ISNUMBER og SEARCH bruges sammen til at matche søgestrengen mod værdierne i dataene, som genererer en matrix med SANDE og FALSKE værdier.
Den smarte bit er at opdele rækkenumrene efter søgeresultaterne. I en matematikoperation som denne opfører TRUE sig som 1, og FALSE opfører sig som nul. Resultatet er række numre tilknyttet et positivt match divideret med 1 og overlever operationen, mens række numre tilknyttet ikke-matchende værdier destrueres og bliver til # DIV / 0 fejl. Da AGGREGATE er indstillet til at ignorere fejl, ignorerer den # DIV / 0-fejlene og returnerer det "nth" mindste tal i de resterende værdier ved hjælp af tallet i kolonne F for "nth".
Styring af ydeevne
Som alle matrixformler er denne formel "dyr" med hensyn til ressourcer med et stort datasæt. For at minimere ydeevneeffekter er hele INDEX- og MATCH-formlen pakket ind i HVIS sådan:
=IF(F5>ct,"",formula)
hvor det navngivne interval "ct" (D8) har denne formel:
=COUNTIF(data,"*"&search&"*")
Denne markering stopper INDEX- og AGGREGATE-delen af formlen i at køre, når alle matchende værdier er blevet ekstraheret.
Arrayformel med SMALL
Hvis din version af Excel ikke har funktionen AGGREGATE, kan du bruge en alternativ formel baseret på SMALL og IF:
=IF(F5>ct,"",INDEX(data,SMALL(IF(ISNUMBER(SEARCH(search,data)),ROW(data)-ROW($B$5)+1),F5)))
Bemærk: dette er en matrixformel og skal indtastes med kontrol + skift + enter.
