Dla tych, którzy są dobrze zorientowani w Excelu, najprawdopodobniej bardzo dobrze znasz funkcję VLOOKUP . Funkcja WYSZUKAJ.PIONOWO służy do znajdowania wartości w innej komórce w oparciu o pasujący tekst w tym samym wierszu.
Jeśli nadal nie znasz funkcji VLOOKUP, możesz sprawdzić mój poprzedni wpis dotyczący korzystania z funkcji WYSZUKAJ.PIONOWO w programie Excel.
Tak potężny, jak VLOOKUP ma ograniczenie, aby dopasować tabelę odniesienia do struktury, aby formuła działała.
W tym artykule pokażemy ograniczenia, w których nie można użyć funkcji WYSZUKAJ.PIONOWO, i wprowadź inną funkcję w programie Excel o nazwie INDEX-MATCH, która może rozwiązać problem.
INDEX MATCH Excel Przykład
Korzystając z poniższego przykładu arkusza kalkulacyjnego Excel, mamy listę nazw właścicieli samochodów i nazwę samochodu. W tym przykładzie spróbujemy pobrać identyfikator samochodu na podstawie modelu samochodu wymienionego pod wieloma właścicielami, jak pokazano poniżej:
Na oddzielnym arkuszu o nazwie CarType mamy prostą bazę danych samochodów z identyfikatorem, modelem samochodu i kolorem .
Przy tej konfiguracji tabeli funkcja WYSZUKAJ.PIONOWO może działać tylko wtedy, gdy dane, które chcemy odzyskać, znajdują się w kolumnie po prawej stronie tego, co próbujemy dopasować (pole Model samochodu ).
Innymi słowy, z tą strukturą tabeli, ponieważ próbujemy dopasować ją do modelu samochodu, jedyną informacją, którą możemy uzyskać, jest Kolor (Nie ID, ponieważ kolumna ID znajduje się po lewej stronie kolumny Model samochodu ).
Dzieje się tak, ponieważ przy użyciu funkcji WYSZUKAJ.PIONOWO wartość wyszukiwania musi pojawić się w pierwszej kolumnie, a kolumny odnośników muszą znajdować się po prawej stronie. Żaden z tych warunków nie jest spełniony w naszym przykładzie.
Dobra wiadomość jest taka, że INDEX-MATCH będzie nam w stanie pomóc w osiągnięciu tego celu. W praktyce jest to połączenie dwóch funkcji programu Excel, które mogą pracować indywidualnie: funkcja INDEX i funkcja MATCH .
Jednak na potrzeby tego artykułu będziemy mówić tylko o połączeniu tych dwóch elementów w celu replikowania funkcji VLOOKUP .
Formuła może wydawać się początkowo trochę długa i zastraszająca. Jednakże, gdy użyjesz go kilka razy, nauczysz się składni na pamięć.
Oto pełna formuła w naszym przykładzie:
= INDEX (CarType! $ A $ 2: $ A 5 $, MATCH (B4, CarType! $ B 2: $ B 5, 0))
Oto podział dla każdej sekcji
= INDEX ( - "=" wskazuje początek formuły w komórce, a INDEX jest pierwszą częścią funkcji Excel, której używamy.
CarType! $ A $ 2: $ A 5 $ - kolumny na arkuszu CarType, w którym znajdują się dane, które chcielibyśmy pobrać. W tym przykładzie identyfikator każdego modelu samochodu.
MATCH ( - druga część funkcji programu Excel, z której korzystamy.
B4 - komórka zawierająca wyszukiwany tekst, którego używamy ( model samochodu ) .
CarType! $ B $ 2: $ B $ 5 - Kolumny na arkuszu CarType z danymi, których użyjemy do dopasowania z tekstem wyszukiwania.
0)) - Aby wskazać, że tekst wyszukiwania musi dokładnie pasować do tekstu w dopasowanej kolumnie (np. CarType! $ B 2: $ B $ 5 ). Jeśli dopasowanie dokładne nie zostanie znalezione, formuła zwróci wartość # N / A.
Uwaga : zapamiętaj podwójny nawias zamykający na końcu tej funkcji ")) i przecinki między argumentami.
Osobiście odłączyłem się od VLOOKUP i teraz używam INDEX-MATCH, ponieważ jest w stanie zrobić coś więcej niż VLOOKUP.
Funkcje INDEX-MATCH mają również inne zalety w porównaniu do VLOOKUP :
- Szybsze obliczenia
Kiedy pracujemy z dużymi zestawami danych, w których samo obliczenie może zająć dużo czasu dzięki wielu funkcjom WYSZUKAJ.PIONOWO, przekonasz się, że po zamianie wszystkich tych formuł na INDEKS-MATCH, ogólne obliczenia będą obliczane szybciej.
- Nie ma potrzeby liczenia kolumn relatywnych
Jeśli nasza tabela referencyjna zawiera tekst kluczowy, który chcemy przeszukać w kolumnie C, a dane, które powinniśmy uzyskać, znajdują się w kolumnie AQ, będziemy potrzebować znać / policzyć, ile kolumn znajduje się między kolumną C a kolumną AQ podczas korzystania z VLOOKUP .
Dzięki funkcjom INDEX-MATCH możemy bezpośrednio wybrać kolumnę indeksu (tj. Kolumnę AQ), w której musimy pobrać dane i wybrać kolumnę, która ma zostać dopasowana (np. Kolumna C).
- Wygląda bardziej skomplikowany
Funkcja WYSZUKAJ.PIONOWO jest dość powszechna w dzisiejszych czasach, ale niewiele osób wie o korzystaniu z funkcji INDEX-MATCH.
Dłuższy ciąg w funkcji INDEX-MATCH pomaga sprawić, że wyglądasz jak ekspert w obsłudze złożonych i zaawansowanych funkcji programu Excel. Cieszyć się!
