Generička formula
=IF(F5>ct,"",INDEX(data,AGGREGATE(15,6,(ROW(data)-ROW($B$5)+1)/ISNUMBER(SEARCH(search,data)),F5)))
Sažetak
Da biste izdvojili sva podudaranja na temelju djelomičnog podudaranja, možete upotrijebiti formulu niza koja se temelji na funkcijama INDEX i AGGREGATE, uz podršku od BROJ i SEARCH. U prikazanom primjeru formula u G5 je:
=IF(F5>ct,"",INDEX(data,AGGREGATE(15,6,(ROW(data)-ROW($B$5)+1)/ISNUMBER(SEARCH(search,data)),F5)))
sa sljedećim imenovanim rasponima: "search" = D5, "ct" = D8, "data" = B5: B55.
Napomena: ovo je formula niza, ali ne zahtijeva control + shift + enter, jer AGGREGATE može nativno obrađivati nizove.
Obrazloženje
Jezgra ove formule je funkcija INDEX, a AGREGATE se koristi za utvrđivanje "n-tog podudaranja" za svaki redak u području izdvajanja:
INDEX(data,nth_match_formula)
Gotovo sav posao sastoji se u pronalaženju i izvještavanju koji se retci u "podacima" podudaraju s nizom pretraživanja, te izvještavanju o položaju za svaku podudarnu vrijednost u INDEX. To se radi s AGGREGATE funkcijom konfiguriranom ovako:
AGGREGATE(15,6,(ROW(data)-ROW($B$5)+1)/ISNUMBER(SEARCH(search,data)),F5)
Prvi argument, 15, govori AGREGATE da se ponaša MALO i vraća n-te najmanje vrijednosti. Drugi argument, 6, opcija je zanemarivanja pogrešaka. Treći je argument izraz koji generira niz rezultata koji se podudaraju (opisano u nastavku). Četvrti argument, F5, djeluje poput "k" u MALOM kako bi odredio vrijednost "n-e".
AGGREGATE djeluje na nizovima, a donji izraz gradi niz za treći argument unutar AGGREGATE:
(ROW(data)-ROW($B$5)+1)/ISNUMBER(SEARCH(search,data))
Ovdje se funkcija ROW koristi za generiranje niza relativnih brojeva redaka, a ISNUMBER i SEARCH koriste se zajedno za podudaranje niza pretraživanja sa vrijednostima u podacima, što generira niz TRUE i FALSE vrijednosti.
Pametna bit je dijeljenje brojeva redaka prema rezultatima pretraživanja. U ovakvoj matematičkoj operaciji TRUE se ponaša kao 1, a FALSE kao nula. Rezultat su brojevi redaka povezani s pozitivnim podudaranjem podijeljeni s 1 i preživljavaju operaciju, dok su brojevi redaka povezani s nepodudarajućim vrijednostima uništeni i postali # DIV / 0 pogreške. Budući da je AGGREGATE postavljen tako da ignorira pogreške, on zanemaruje greške # DIV / 0 i vraća "n-ti" najmanji broj u preostalim vrijednostima, koristeći broj u stupcu F za "nth".
Upravljanje izvedbom
Kao i sve formule niza, i ova je formula "skupa" u smislu resursa s velikim nizom podataka. Da bi se utjecaji na izvedbu sveli na minimum, cijela formula INDEX i MATCH umotana je u IF ovako:
=IF(F5>ct,"",formula)
gdje imenovani raspon "ct" (D8) sadrži ovu formulu:
=COUNTIF(data,"*"&search&"*")
Ova provjera zaustavlja pokretanje INDEX-a i AGREGATE-a dijela formule nakon što su izvučene sve odgovarajuće vrijednosti.
Formula niza s MALIM
Ako vaša verzija Excela nema funkciju AGREGATE, možete upotrijebiti alternativnu formulu koja se temelji na SMALL i IF:
=IF(F5>ct,"",INDEX(data,SMALL(IF(ISNUMBER(SEARCH(search,data)),ROW(data)-ROW($B$5)+1),F5)))
Napomena: ovo je formula niza i mora se unijeti sa control + shift + enter.
