Obecný vzorec
=IF(F5>ct,"",INDEX(data,AGGREGATE(15,6,(ROW(data)-ROW($B$5)+1)/ISNUMBER(SEARCH(search,data)),F5)))
souhrn
Chcete-li extrahovat všechny shody na základě částečné shody, můžete použít maticový vzorec založený na funkcích INDEX a AGGREGATE s podporou ISNUMBER a SEARCH. V zobrazeném příkladu je vzorec v G5:
=IF(F5>ct,"",INDEX(data,AGGREGATE(15,6,(ROW(data)-ROW($B$5)+1)/ISNUMBER(SEARCH(search,data)),F5)))
s následujícími pojmenovanými rozsahy: "search" = D5, "ct" = D8, "data" = B5: B55.
Poznámka: toto je maticový vzorec, ale nevyžaduje control + shift + enter, protože AGGREGATE může nativně zpracovat pole.
Vysvětlení
Jádrem tohoto vzorce je funkce INDEX, přičemž AGGREGATE slouží k výpočtu „n-té shody“ pro každý řádek v oblasti extraktu:
INDEX(data,nth_match_formula)
Téměř celá práce spočívá ve zjišťování a vykazování, které řádky v „datech“ odpovídají vyhledávacímu řetězci, a hlášení polohy každé odpovídající hodnoty do INDEXU. To se provádí pomocí funkce AGGREGATE nakonfigurované takto:
AGGREGATE(15,6,(ROW(data)-ROW($B$5)+1)/ISNUMBER(SEARCH(search,data)),F5)
První argument, 15, říká AGGREGATE, aby se choval jako MALÝ a vrátil n-té nejmenší hodnoty. Druhý argument, 6, je možnost ignorovat chyby. Třetí argument je výraz, který generuje pole odpovídajících výsledků (popsáno níže). Čtvrtý argument, F5, se chová jako „k“ v SMALL k určení „n-té“ hodnoty.
AGGREGATE pracuje na polích a níže uvedený výraz vytváří pole pro třetí argument uvnitř AGGREGATE:
(ROW(data)-ROW($B$5)+1)/ISNUMBER(SEARCH(search,data))
Zde se funkce ROW používá ke generování pole relativních čísel řádků a ISNUMBER a SEARCH se používají společně k porovnání vyhledávacího řetězce s hodnotami v datech, což generuje pole TRUE a FALSE hodnot.
Chytrý bit je vydělit čísla řádků výsledky vyhledávání. V matematické operaci, jako je tato, se TRUE chová jako 1 a FALSE se chová jako nula. Výsledkem je, že čísla řádků přidružená k pozitivní shodě se vydělí 1 a přežijí operaci, zatímco čísla řádků přidružená k neodpovídajícím hodnotám se zničí a stanou se chybami # DIV / 0. Protože AGGREGATE je nastaven na ignorování chyb, ignoruje chyby # DIV / 0 a vrací „n-té“ nejmenší číslo ze zbývajících hodnot, přičemž pro „n-tý“ používá číslo ve sloupci F.
Správa výkonu
Stejně jako všechny maticové vzorce je i tento vzorec „drahý“, pokud jde o zdroje s velkou sadou dat. Aby se minimalizovaly dopady na výkon, celý vzorec INDEX a MATCH je zabalen do IF takto:
=IF(F5>ct,"",formula)
kde pojmenovaný rozsah „ct“ (D8) obsahuje tento vzorec:
=COUNTIF(data,"*"&search&"*")
Tato kontrola zastaví spuštění části INDEX a AGGREGATE vzorce po extrahování všech odpovídajících hodnot.
Pole vzorce s MALÝM
Pokud vaše verze aplikace Excel nemá funkci AGGREGATE, můžete použít alternativní vzorec založený na MALÉM a IF:
=IF(F5>ct,"",INDEX(data,SMALL(IF(ISNUMBER(SEARCH(search,data)),ROW(data)-ROW($B$5)+1),F5)))
Poznámka: toto je maticový vzorec a musí být zadán pomocí kláves Control + Shift + Enter.
