Podsumowanie
Aby wyodrębnić wiele dopasowań do oddzielnych komórek, w oddzielnych wierszach możesz użyć formuły tablicowej opartej na INDEKS i MAŁE. W pokazanym przykładzie formuła w E5 to:
(=IFERROR(INDEX(names,SMALL(IF(groups=E$4,ROW(names)-MIN(ROW(names))+1),ROWS($E$5:E5))),""))
To jest formuła tablicowa i należy ją wprowadzić za pomocą Control + Shift + Enter.
Po wprowadzeniu formuły w pierwszej komórce przeciągnij ją w dół i w poprzek, aby wypełnić pozostałe komórki.
Wyjaśnienie
Uwaga: w tej formule używane są dwa nazwane zakresy: „nazwy” odnoszą się do C4: C11, a „grupy” do B4: B11. Nazwy te są również zdefiniowane na powyższym zrzucie ekranu.
Istota tego wzoru jest taka: używamy funkcji SMALL, aby uzyskać numer wiersza, który odpowiada „n-temu dopasowaniu”. Gdy mamy numer wiersza, po prostu przekazujemy go do funkcji INDEKS, która zwraca wartość w tym wierszu.
Sztuczka polega na tym, że SMALL pracuje z tablicą, która jest dynamicznie konstruowana przez IF w tym bicie:
IF(groups=E$4,ROW(names)-MIN(ROW(names))+1)
Ten fragment kodu testuje nazwane „grupy” zakresu pod kątem wartości w E4. Jeśli zostanie znaleziony, zwraca „znormalizowany” numer wiersza z tablicy numerów wierszy utworzonej za pomocą tej części formuły:
ROW(names)-MIN(ROW(names))+1
Wynikiem jest tablica zawierająca numery wierszy, w przypadku których występuje dopasowanie, i FALSE, jeśli nie. Tablica wygląda mniej więcej tak:
(1; FALSE; FALSE; FALSE; FALSE; 6; FALSE)
Ta tablica przechodzi do SMALL. Wartość k dla SMALL (n-ty) pochodzi z rozszerzającego się zakresu:
ROWS($E$5:E5)
Po skopiowaniu w dół tabeli wynikowej zakres rozszerza się, powodując wzrost k (n-tego). Funkcja SMALL zwraca każdy pasujący numer wiersza, który jest dostarczany do funkcji INDEKS jako numer_wiersza, z nazwanym zakresem „nazwy” jako tablicą.
Obsługa błędów
Gdy ROWS zwraca wartość k, która nie istnieje, funkcja SMALL zgłasza błąd #NUM. Dzieje się tak po wystąpieniu wszystkich dopasowań. Aby powstrzymać błąd, używamy IFERROR do wychwytywania błędu i zwracania pustego ciągu („”).
