W tym artykule omówimy, jak dopasować wszystkie wartości z tabeli i pobrać je w różnych komórkach. Jest to podobne do wyszukiwania wielu wartości.
Formuła ogólna
{=INDEX(nazwy,SMALL(JEŻELI(grupy=nazwa_grupy,WIERSZ(nazwy)-MIN(WIERSZ(nazwy))+1),KOLUMNY(rozszerzanie zakresów))), “--Lista kończy się--”)}
Za dużo funkcji i zmiennych!!!. Zobaczmy, jakie są te zmienne.
Nazwy: To jest lista nazwisk.
Grupy: Lista grup, do których należą te nazwy.
Nazwa grupy: odniesienie do nazwy grupy.
Rozszerzanie zakresów: jest to rozszerzający się zakres, który jest używany do uzyskania rosnącej liczby po skopiowaniu w prawo.
Przykład: Wyodrębnij nazwiska pracowników w różnych kolumnach zgodnie z ich firmą.
Załóżmy, że masz tabelę pracowników pogrupowaną według ich firmy. Pierwsza kolumna zawiera nazwiska pracowników, a druga kolumna zawiera nazwę firmy.
Teraz musimy umieścić imię i nazwisko każdego pracownika w różnych kolumnach zgodnie z ich firmą. Innymi słowy, musimy je rozgrupować.
Tutaj nazwałem A2:A10 jako Pracownik i B2:B10 jako Firmę, aby ta formuła była łatwa do odczytania.
Zapisz tę formułę tablicową w F2. Użyj klawiszy CTRL+SHIFT+ENTER, aby wprowadzić tę formułę.
{=INDEX(Pracownik,SMALL(IF(Firma=$E2,WIERSZ(Pracownik)-MIN(WIERSZ(Pracownik))+1),COLUMNS($E$1:E1))), „--Lista kończy się--” )}
Skopiuj tę formułę we wszystkich komórkach. Wyodrębni każdą indywidualną nazwę w różnych kolumnach zgodnie z ich grupą.
Jak widać na powyższym obrazku, każdy pracownik jest posegregowany w różne komórki.
Jak działa ta formuła?
Aby zrozumieć formułę, spójrzmy na formułę w G2
Czyli =JEŻELIBŁĄD(INDEKS(Pracownik,MAŁY(JEŻELI(Firma=$E3,WIERSZ(Pracownik)-MIN(WIERSZ(Pracownik))+1),KOLUMNY($E$1:F2))),"--Lista kończy się--")
Mechanika jest prosta i prawie taka sama jak wielokrotna formuła WYSZUKAJ.PIONOWO. Sztuczka polega na tym, aby uzyskać numer indeksu każdego pracownika z różnych grup i przekazać go do formuły INDEX. Odbywa się to przez tę część formuły.
JEŚLI(Spółka=$E3,WIERSZ(Pracownik)-MIN(WIERSZ(Pracownik))+1):
Ta część zwraca tablicę indeksów i false dla nazwy firmy w $E3, która zawiera „Rankwatch”.
{FAŁSZ;2;FAŁSZ;4;FAŁSZ;FAŁSZ;7;FAŁSZ;9}.
Jak? Zburzmy go od środka.
Tutaj dopasowujemy nazwę firmy w $E3 do każdej wartości w Zakres firmy (firma=$E3).
Zwraca tablicę wartości prawda i fałsz.{FALSE;PRAWDA;FAŁSZYWE;PRAWDA;FAŁSZ;FAŁSZ;PRAWDA;FAŁSZYWE;PRAWDA}.
Teraz funkcja JEŻELI uruchamia swoje instrukcje PRAWDA dla PRAWDA, czyli ROW(Pracownik)-MIN(ROW(Pracownik))+1. Ta część zwraca tę część zwraca tablicę indeksów zaczynając od 1 do liczby pracowników {1;2;3;4;5;6;7;8;9}. Funkcja if wyświetla wartości tylko dla TRUE, co z kolei daje nam {FALSE;2;FALSE;4;FALSE;FALSE;7;FALSE;9}.
Obecna formuła jest uproszczona do
=JEŻELIBŁĄD(INDEKS(Pracownik,MAŁY( {FAŁSZ;2;FAŁSZ;4;FAŁSZ;FAŁSZ;7;FAŁSZ;9},KOLUMNY($E$1:F2))),"--Lista kończy się--"). Jak wiemy funkcja small zwraca n-tą najmniejszą wartość z tablicy. KOLUMNY($E$1:F2) to zwraca 2. Funkcja MAŁA zwraca drugą najmniejszą wartość z powyższej tablicy, czyli 4.
Teraz formuła jest uproszczona =JEŻELI.BŁĄD(INDEKS(Pracownik,4),"--Lista kończy się--"). Teraz funkcja INDEX po prostu zwraca czwartą nazwę z pracownik tablica, która daje nam „Sam”.
Więc tak, w ten sposób wyodrębnij nazwy z grup w różnych kolumnach za pomocą funkcji INDEX, SMALL, ROW, COLUMNS i IF. Jeśli masz jakiekolwiek wątpliwości dotyczące tej funkcji lub jeśli nie działa dla Ciebie, daj mi znać w sekcji komentarzy poniżej.
Pobieranie pliku:
Powiązane artykuły:
WYSZUKAJ.PIONOWO Wiele wartości
Użyj INDEKSU i DOPASUJ, aby wyszukać wartość
Wartość wyszukiwania z wieloma kryteriami
Popularne artykuły:
Funkcja WYSZUKAJ.PIONOWO w programie Excel
LICZ.JEŻELI w Excelu 2016
Jak korzystać z funkcji SUMIF w programie Excel?