Formuła ogólna
=IF(F5>ct,"",INDEX(data,AGGREGATE(15,6,(ROW(data)-ROW($B$5)+1)/ISNUMBER(SEARCH(search,data)),F5)))
Podsumowanie
Aby wyodrębnić wszystkie dopasowania na podstawie częściowego dopasowania, możesz użyć formuły tablicowej opartej na funkcjach INDEKS i AGREGUJ, z obsługą ISNUMBER i SEARCH. W pokazanym przykładzie formuła w G5 to:
=IF(F5>ct,"",INDEX(data,AGGREGATE(15,6,(ROW(data)-ROW($B$5)+1)/ISNUMBER(SEARCH(search,data)),F5)))
z następującymi nazwanymi zakresami: „search” = D5, „ct” = D8, „data” = B5: B55.
Uwaga: to jest formuła tablicowa, ale nie wymaga Control + Shift + Enter, ponieważ AGREGATUJ może natywnie obsługiwać tablice.
Wyjaśnienie
Rdzeniem tej formuły jest funkcja INDEKS, a AGREGACJA służy do obliczenia „n-tego dopasowania” dla każdego wiersza w obszarze wyodrębniania:
INDEX(data,nth_match_formula)
Prawie cała praca polega na ustalaniu i raportowaniu, które wiersze w „danych” pasują do szukanego ciągu, oraz na zgłaszaniu pozycji każdej pasującej wartości do INDEKSU. Odbywa się to za pomocą funkcji AGREGUJ skonfigurowanej w następujący sposób:
AGGREGATE(15,6,(ROW(data)-ROW($B$5)+1)/ISNUMBER(SEARCH(search,data)),F5)
Pierwszy argument, 15, mówi AGGREGATE, aby zachowywał się jak SMALL i zwracał n-tą najmniejszą wartość. Drugi argument, 6, to opcja ignorowania błędów. Trzeci argument to wyrażenie, które generuje tablicę pasujących wyników (opisanych poniżej). Czwarty argument, F5, działa jak „k” w SMALL, określając „n-tą” wartość.
AGGREGATE działa na tablicach, a poniższe wyrażenie buduje tablicę dla trzeciego argumentu wewnątrz AGGREGATE:
(ROW(data)-ROW($B$5)+1)/ISNUMBER(SEARCH(search,data))
Tutaj funkcja WIERSZ jest używana do generowania tablicy względnych numerów wierszy, a ISNUMBER i SEARCH są używane razem w celu dopasowania szukanego ciągu do wartości w danych, co generuje tablicę wartości PRAWDA i FAŁSZ.
Sprytnym rozwiązaniem jest podzielenie numerów wierszy przez wyniki wyszukiwania. W takiej operacji matematycznej TRUE zachowuje się jak 1, a FALSE jak zero. W rezultacie numery wierszy powiązane z dodatnim dopasowaniem są dzielone przez 1 i przetrwają operację, podczas gdy numery wierszy powiązane z niepasującymi wartościami są niszczone i stają się błędami # DIV / 0. Ponieważ AGGREGATE ma ignorować błędy, ignoruje błędy # DIV / 0 i zwraca „n-tą” najmniejszą liczbę spośród pozostałych wartości, używając liczby z kolumny F zamiast „nth”.
Zarządzanie wydajnością
Podobnie jak wszystkie formuły tablicowe, ta formuła jest „droga” pod względem zasobów z dużym zestawem danych. Aby zminimalizować wpływ na wydajność, cała formuła INDEKSU i DOPASUJ jest opakowana w JEŚLI w następujący sposób:
=IF(F5>ct,"",formula)
gdzie nazwany zakres „ct” (D8) zawiera następującą formułę:
=COUNTIF(data,"*"&search&"*")
To sprawdzenie zatrzymuje działanie części INDEKS i AGREGACJA formuły po wyodrębnieniu wszystkich pasujących wartości.
Formuła tablicowa z SMALL
Jeśli Twoja wersja programu Excel nie ma funkcji AGREGUJ, możesz użyć alternatywnej formuły opartej na MAŁY i JEŻELI:
=IF(F5>ct,"",INDEX(data,SMALL(IF(ISNUMBER(SEARCH(search,data)),ROW(data)-ROW($B$5)+1),F5)))
Uwaga: to jest formuła tablicowa i należy ją wprowadzić za pomocą Ctrl + Shift + Enter.
