Podsumowanie
Aby wyodrębnić wiele dopasowań do oddzielnych komórek, w oddzielnych kolumnach możesz użyć formuły tablicowej opartej na INDEKS i MAŁE. W pokazanym przykładzie formuła w F5 to:
(=IFERROR(INDEX(names,SMALL(IF(groups=$E5,ROW(names)-MIN(ROW(names))+1),COLUMNS($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: ta formuła wykorzystuje dwa nazwane zakresy: „nazwy” odnoszą się do C5: C11, a „grupy” odnoszą się do B5: B11. Nazwy te są również zdefiniowane na powyższym zrzucie ekranu.
Istota tej formuły jest taka: używamy funkcji SMALL, aby wygenerować numer wiersza odpowiadający „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=$E5,ROW(names)-MIN(ROW(names))+1)
Ten fragment kodu testuje nazwane „grupy” zakresu pod kątem wartości w E5. Jeśli zostanie znaleziony, zwraca numer wiersza z tablicy względnych numerów wierszy utworzonej za pomocą:
ROW(names)-MIN(ROW(names))+1
Ostatecznym wynikiem jest tablica zawierająca liczby, w których występuje zgodność, i FALSE, gdy nie:
(1; FALSE; FALSE; FALSE; FALSE; 6; FALSE)
Ta tablica przechodzi do SMALL. Wartość k dla SMALL (n-ty) pochodzi z rozszerzającego się zakresu:
COLUMNS($E$5:E5)
Po skopiowaniu w tabeli wynikowej zakres rozszerza się, powodując przyrost 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 COLUMNS zwraca wartość k, która nie istnieje, SMALL zgłasza błąd #NUM. Dzieje się tak po wystąpieniu wszystkich dopasowań. Aby pominąć błąd, zawijamy formułę w funkcję JEŻELI BŁĄD, aby wychwycić błędy i zwrócić pusty ciąg („”).
