Generisk formel
=IF(F5>ct,"",INDEX(data,AGGREGATE(15,6,(ROW(data)-ROW($B$5)+1)/ISNUMBER(SEARCH(search,data)),F5)))
Sammendrag
For å trekke ut alle treff basert på en delvis samsvar, kan du bruke en matriseformel basert på INDEX- og AGGREGATE-funksjonene, med støtte fra ISNUMBER og SEARCH. I eksemplet vist er formelen i G5:
=IF(F5>ct,"",INDEX(data,AGGREGATE(15,6,(ROW(data)-ROW($B$5)+1)/ISNUMBER(SEARCH(search,data)),F5)))
med følgende navngitte områder: "søk" = D5, "ct" = D8, "data" = B5: B55.
Merk: dette er en matriseformel, men det krever ikke kontroll + shift + enter, siden AGGREGATE kan håndtere matriser naturlig.
Forklaring
Kjernen i denne formelen er INDEX-funksjonen, med AGGREGATE som brukes til å finne ut den "nte match" for hver rad i ekstraktområdet:
INDEX(data,nth_match_formula)
Nesten alt arbeidet går ut på å finne ut og rapportere hvilke rader i "data" som samsvarer med søkestrengen, og rapportere posisjonen til hver samsvarende verdi til INDEX. Dette gjøres med AGGREGATE-funksjonen konfigurert slik:
AGGREGATE(15,6,(ROW(data)-ROW($B$5)+1)/ISNUMBER(SEARCH(search,data)),F5)
Det første argumentet, 15, forteller AGGREGATE å oppføre seg som LITEN, og returnere den minste verdi. Det andre argumentet, 6, er et alternativ for å ignorere feil. Det tredje argumentet er et uttrykk som genererer en rekke samsvarende resultater (beskrevet nedenfor). Det fjerde argumentet, F5, fungerer som "k" i SMALL for å spesifisere "nth" -verdien.
AGGREGATE opererer på matriser, og uttrykket nedenfor bygger en matrise for det tredje argumentet inne i AGGREGATE:
(ROW(data)-ROW($B$5)+1)/ISNUMBER(SEARCH(search,data))
Her brukes ROW-funksjonen til å generere en matrise med relative radnumre, og ISNUMBER og SØK brukes sammen for å matche søkestrengen mot verdiene i dataene, som genererer en matrise med SANTE og FALSE verdier.
Den smarte biten er å dele radnumrene etter søkeresultatene. I en matteoperasjon som denne oppfører SANT seg som 1, og FALSE oppfører seg som null. Resultatet er radnummer assosiert med en positiv samsvar delt på 1 og overlever operasjonen, mens radnumre tilknyttet ikke-samsvarende verdier blir ødelagt og blir til # DIV / 0-feil. Fordi AGGREGATE er satt til å ignorere feil, ignorerer den # DIV / 0-feilene, og returnerer det "nte" minste tallet i de gjenværende verdiene, ved å bruke tallet i kolonne F for "nth".
Administrere ytelse
Som alle matriseformler er denne formelen "dyr" når det gjelder ressurser med et stort datasett. For å minimere ytelsespåvirkninger er hele INDEX- og MATCH-formelen pakket inn i HVIS slik:
=IF(F5>ct,"",formula)
der det navngitte området "ct" (D8) inneholder denne formelen:
=COUNTIF(data,"*"&search&"*")
Denne sjekken stopper INDEX og AGGREGATE-delen av formelen fra å kjøre når alle samsvarende verdier er hentet ut.
Arrayformel med LITEN
Hvis din versjon av Excel ikke har AGGREGATE-funksjonen, kan du bruke en alternativ formel basert på SMALL og IF:
=IF(F5>ct,"",INDEX(data,SMALL(IF(ISNUMBER(SEARCH(search,data)),ROW(data)-ROW($B$5)+1),F5)))
Merk: dette er en matriseformel og må angis med kontroll + skift + enter.
