Splošna formula
=IF(F5>ct,"",INDEX(data,AGGREGATE(15,6,(ROW(data)-ROW($B$5)+1)/ISNUMBER(SEARCH(search,data)),F5)))
Povzetek
Če želite izvleči vsa ujemanja na podlagi delnega ujemanja, lahko uporabite matrično formulo, ki temelji na funkcijah INDEX in AGGREGATE, s podporo ISNUMBER in SEARCH. V prikazanem primeru je formula v G5:
=IF(F5>ct,"",INDEX(data,AGGREGATE(15,6,(ROW(data)-ROW($B$5)+1)/ISNUMBER(SEARCH(search,data)),F5)))
z naslednjimi imenovanimi obsegi: "iskanje" = D5, "ct" = D8, "podatki" = B5: B55.
Opomba: to je formula matrike, vendar ne zahteva nadzora + shift + enter, saj lahko AGGREGATE matično obdeluje nize.
Pojasnilo
Jedro te formule je funkcija INDEX, pri čemer je AGGREGATE uporabljen za ugotavljanje "n-tega ujemanja" za vsako vrstico v območju izvlečka:
INDEX(data,nth_match_formula)
Skoraj vse delo je v iskanju in poročanju, katere vrstice v "podatkih" se ujemajo z iskalnim nizom, in poročanju o položaju za vsako ujemajočo se vrednost v INDEX. To se naredi s tako konfigurirano funkcijo AGGREGATE:
AGGREGATE(15,6,(ROW(data)-ROW($B$5)+1)/ISNUMBER(SEARCH(search,data)),F5)
Prvi argument, 15, govori AGREGATE, da se obnaša kot SMALL, in vrne n-je najmanjše vrednosti. Drugi argument, 6, je možnost prezrtja napak. Tretji argument je izraz, ki generira niz ujemajočih se rezultatov (opisano spodaj). Četrti argument, F5, deluje kot "k" v SMALL, da določi vrednost "n-te".
AGGREGATE deluje na nizih, spodnji izraz pa gradi matriko za tretji argument znotraj AGGREGATE:
(ROW(data)-ROW($B$5)+1)/ISNUMBER(SEARCH(search,data))
Tu se funkcija ROW uporablja za generiranje nabora relativnih številk vrstic, ISNUMBER in SEARCH pa se skupaj uporabljata za povezovanje iskalnega niza z vrednostmi v podatkih, kar ustvari matriko TRUE in FALSE vrednosti.
Pameten del je razdelitev številk vrstic na rezultate iskanja. Pri taki matematični operaciji se TRUE obnaša kot 1, FALSE pa kot nič. Rezultat so številke vrstic, povezane s pozitivnim ujemanjem, deljene z 1 in preživijo operacijo, medtem ko so številke vrstic, povezane z neujemajočimi se vrednostmi, uničene in postanejo # DIV / 0 napake. Ker je AGGREGATE nastavljen tako, da prezre napake, prezre napake # DIV / 0 in v preostalih vrednostih vrne najmanjše število "n", pri čemer za "nth" uporabi število v stolpcu F.
Upravljanje uspešnosti
Kot vse formule matrike je tudi ta formula "draga" glede virov z velikim naborom podatkov. Da bi zmanjšali vpliv na zmogljivost, je celotna formula INDEX in MATCH ovita v IF takole:
=IF(F5>ct,"",formula)
kjer ima imenovano območje "ct" (D8) to formulo:
=COUNTIF(data,"*"&search&"*")
To preverjanje ustavi, da se del formule INDEX in AGGREGATE zažene, ko so izvlečene vse ujemajoče se vrednosti.
Formula matrike z MALO
Če vaša različica Excela nima funkcije AGREGATE, lahko uporabite nadomestno formulo, ki temelji na SMALL in IF:
=IF(F5>ct,"",INDEX(data,SMALL(IF(ISNUMBER(SEARCH(search,data)),ROW(data)-ROW($B$5)+1),F5)))
Opomba: to je matrična formula, ki jo je treba vnesti s tipko control + shift + enter.
