Jeśli od jakiegoś czasu czytasz wskazówki dotyczące programu Excel, niezmiennie znajdujesz osobę, która mówi o używaniu funkcji Excel INDEX () i MATCH () zamiast funkcji WYSZUKAJ.PIONOWO w programie Excel. Mówiąc za siebie, zawsze było zbyt trudno opanować DWIE nowe funkcje jednocześnie. Ale to jest fajna sztuczka. Daj mi pięć minut, a spróbuję to wyjaśnić prostym angielskim.
30-sekundowy przegląd funkcji WYSZUKAJ.PIONOWO
Powiedzmy, że masz tabelę danych pracowników. Pierwsza kolumna to numer pracownika, a pozostałe kolumny to różne dane dotyczące pracownika. Za każdym razem, gdy masz numer pracownika w arkuszu, możesz użyć funkcji WYSZUKAJ.PIONOWO, aby zwrócić określone dane dotyczące pracownika. Składnia to WYSZUKAJ.PIONOWO (wartość, zakres danych, nr kolumny, FALSE). W programie Excel jest napisane: „Idź do zakresu danych. Znajdź wiersz, który ma (wartość) w pierwszej kolumnie zakresu danych. Zwróć (nr kol.) Wartość z tego wiersza. Gdy już to zrozumiesz, to jest bardzo proste i potężne.
Problem
Pewnego dnia masz sytuację, w której masz nazwisko pracownika, ale potrzebujesz numeru pracownika. Na poniższym obrazku masz nazwisko w A10 i musisz znaleźć numer pracownika w B10.
Gdy pole klucza znajduje się po prawej stronie danych, które chcesz pobrać, funkcja WYSZUKAJ.PIONOWO nie będzie działać. Gdyby tylko WYSZUKAJ.PIONOWO zaakceptowałoby -1 jako numer kolumny, nie byłoby problemu. Ale tak nie jest. Jednym z typowych rozwiązań jest tymczasowe wstawienie nowej kolumny A, skopiowanie kolumny nazw do nowej kolumny A, wypełnienie funkcji WYSZUKAJ.PIONOWO, Wklej wartości specjalne, a następnie usunięcie tymczasowej kolumny A. Specjaliści od programu Excel prawdopodobnie mogą wykonać ten ruch we śnie.
Mam zamiar zasugerować podjęcie wyzwania i spróbować zastosować tę jednoetapową metodę. Tak, będziesz musiał przyczepić formułę do ściany przez kilka tygodni, ale zrobiłeś to również za pomocą funkcji WYSZUKAJ.PIONOWO dawno temu, prawda?
Myślę, że jest to tak trudne, ponieważ używasz dwóch funkcji, których prawdopodobnie nigdy wcześniej nie używałeś. Więc pozwól, że podzielę to na dwie części.
Najpierw jest funkcja INDEKS (). To strasznie nazwana funkcja. Kiedy ktoś mówi „indeks”, nie wywołuje to w mojej głowie niczego podobnego do tego, co robi ta funkcja. Indeks wymaga trzech argumentów.
=INDEX(data range, row number, column number)
W języku angielskim Excel przechodzi do zakresu danych i zwraca wartość z przecięcia (numer wiersza) -tego wiersza i (-ej kolumny)-tej kolumny. Hej, pomyśl o tym - to całkiem proste, prawda? =INDEX($A$2:$C$6,4,2)poda wartość w B5.
Stosując Index () do naszego problemu, można zrozumieć, że aby powrócić liczbę pracowników z zakresu, należy użyć tego: =INDEX($A$2:$A$6,?,1). Właściwie ten fragment wydaje się tak trywialny, że wydaje się bezużyteczny. Ale kiedy zastąpisz znak zapytania funkcją MATCH (), masz rozwiązanie.
Oto składnia:
=MATCH(Value, Single-column data range, FALSE)
Mówi programowi Excel: „Przeszukaj zakres danych i podaj względny numer wiersza, w którym znajduje się dopasowanie dla (dane). Aby znaleźć wiersz z pracownikiem w A10, należy użyć =MATCH(A10,$B$2:$B$6,FALSE). Tak, jest to bardziej złożone niż indeks , ale powinno być dokładnie w alei profesjonalistów WYSZUKAJ.PIONOWO. Jeśli A10 zawiera „Miller, Bob”, to DOPASOWANIE zwróci, że znajduje się w 3. wierszu zakresu B2: B6.
Oto jest - funkcja MATCH () informuje funkcję Index, w którym wierszu ma szukać - gotowe. Weź funkcję indeksu, zamień nasz znak zapytania na funkcję PODAJ.POZYCJĘ i możesz teraz zrobić odpowiednik funkcji WYSZUKAJ.PIONOWO, gdy pole klucza nie znajduje się w lewej kolumnie. Oto funkcja, której należy użyć:
=INDEX($A$2:$A$6,MATCH(A10,$B$2:$B$6,FALSE),1)
Karteczka na mojej ścianie przedstawia to jako dwie linie. Najpierw napisałem wyjaśnienie funkcji MATCH (). Poniżej napisałem wyjaśnienie dla INDEKSU (). Następnie narysowałem kształt lejka między nimi, aby wskazać, że funkcja MATCH () spada do drugiego argumentu funkcji INDEKS ().
Przez pierwsze kilka razy musiałem to zrobić, kusiło mnie, by wbić tam nową tymczasową kolumnę A, ale zamiast tego przeszedłem ból, robiąc to w ten sposób. Jest szybszy i wymaga mniej manipulacji. Więc następnym razem, gdy będziesz żałować, że nie możesz wstawić liczby ujemnej do funkcji WYSZUKAJ.PIONOWO, wypróbuj tę dziwną kombinację INDEKSU i PODAJ.POZYCJĘ, aby rozwiązać swoje problemy.
