Połącz tabele (VLOOKUP)

Co to jest VLOOKUP

Wbudowana funkcja VLOOKUP jest jedną z najpotężniejszych funkcji w Excel. Należy do trzech najpopularniejszych w Excel — po SUM i AVERAGE. Jej zadaniem jest odnalezienie żądanej wartości w tabeli danych i wyświetlenie jej w określonej komórce.

Składnia funkcji obejmuje wartość wyszukiwania (czego szukasz), tabelę z jedną lub większą liczbą kolumn (gdzie szukać), numer indeksu kolumny (z której kolumny mają zostać zwrócone dane) oraz typ wyszukiwania (dodatkowy argument co pozwala wybrać wynik przybliżony lub dokładny).

Funkcja VLOOKUP jest powszechnie używana do analiz finansowych, zarządzania danymi i pracy z bazami danych. Korzystając z tej funkcji, użytkownicy mogą szybko i łatwo znaleźć informacje w dużym zbiorze danych, co pozwala zaoszczędzić czas i poprawić dokładność.

Funkcja VLOOKUP szuka wartości w pionie, czyli pomiędzy wierszami (oznacza to pierwsza litera V - Vertical). W Excel istnieje również podobna funkcja HLOOKUP, która ma podobną funkcjonalność, ale wykonuje wyszukiwanie poziome (H - Horizontal) według kolumn.

Przykład problemu

Załóżmy, że mamy dwie tabele z danymi - tabela kredytów i tabela zabezpieczeń :

Musimy automatycznie wprowadzić zabezpieczenie do tabeli kredytów na podstawie numeru umowy, w celu dalszego raportowania.

Rozwiązanie

W Excel w standardowym zestawie funkcji w kategorii Wyszukiwanie i odniesienie jest funkcją WYSZUKAJ . Funkcja ta wyszukuje określoną wartość (w naszym przykładzie numer umowy) w skrajnej lewej kolumnie określonej tabeli (tabeli zabezpieczeń) przesuwając się z góry na dół i po ich znalezieniu wyświetla wartość sąsiedniej komórki (rodzaj zabezpieczenia kredytu). Schematycznie działanie funkcji wygląda następująco:

Skorzystajmy więc z funkcji WYSZUKAJ . Wybierz komórkę, w której zostanie wprowadzona ( E2 ) i otwórz kreator formuł (menu Insert - Function ). W kategorii (Wyszukiwanie i odniesienie) znajdź funkcję WYSZUKAJ i naciśnij OK . Pojawi się okno do wpisania argumentów funkcji:

Wypełnij je jeden po drugim:

• Lookup Value - numer umowy, którego funkcja będzie szukać w skrajnej lewej kolumnie tabeli prowizji. W naszym przypadku – numer umowy „KF568” z komórki C2 .
• Table Array - tabela, z której uzyskamy wymagane wartości, jest naszą tabelą podaży. Jako odniesienie użyjemy zakresu $G$2:$H$11 (zakres jest oznaczony znakiem dolara ($) , aby przy kopiowaniu formuły nie przesunęła się na dół).
• Column index number - numer seryjny (nie litera!) kolumny w tabeli przepisu, od którego weźmiemy nazwę przepisu. Pierwsza kolumna tabeli świadczeń ma numer porządkowy 1, zatem musimy pobrać typ świadczenia z drugiej kolumny (numer 2).
• Range Lookup - w tym polu możemy wpisać tylko dwie wartości: TRUE lub FALSE:
  
  1) Jeśli wprowadzimy wartość 0 Lub FALSE , oznacza to w rzeczywistości, że dozwolone jest tylko wyszukiwanie z dokładnym dopasowaniem , czyli jeśli funkcja nie odnajdzie numeru umowy w tabeli aprowizacji (jeżeli nie istnieje) to wyświetli się błąd #Nie dotyczy (brak danych).
  
  2) Jeśli wprowadzimy wartość 1 Lub TRUE , będzie to oznaczać, że dozwolone jest wyszukiwanie nie tylko dokładne, ale także przybliżony zbieg okoliczności . W większości przypadków takie przybliżone podstawienie może spowodować fałszywy wynik, gdy WYSZUKAJ zastąpią wartości innych umów. Dlatego powinieneś używać wyszukiwania przybliżonego tylko dla wartości liczbowych (wstępnie posortowanych w kolejności rosnącej).

Pozostaje nacisnąć OK i skopiuj wprowadzoną funkcję w całej kolumnie.

Wady funkcji VLOOKUP

Główną wadą jest to, że wyszukiwanie żądanej wartości może odbywać się tylko w pierwszej kolumnie danego zakresu, a funkcja może zwrócić żądaną wartość tylko z kolumn po prawej stronie.

Drugą wadą VLOOKUP jest to, że funkcja przestaje działać, jeśli usuniesz lub dodasz kolumnę do tabeli przeglądowej. Wstawiony lub usunięty element zmieni wynik formuły, ponieważ składnia funkcji wymaga podania całego zakresu i konkretnego numeru kolumny, z której chcesz wyodrębnić dane.

Ponadto funkcja VLOOKUP ma limit długości wyszukiwania wynoszący 255 znaków, w przeciwnym razie zostanie zwrócony błąd #VALUE!

Problemy podczas pracy z funkcją

Funkcjonować WYSZUKAJ zwraca błąd (#nie dotyczy) Jeśli:

1. Dozwolone jest wyszukiwanie dokładne (argument Range Lookup=0) i szukanej wartości nie ma w tabeli (Table) .
2. Dozwolone wyszukiwanie w przybliżeniu (Range Lookup=1), ale w tabeli (Table) , w którym szukamy wartości, nazwy nie są sortowane rosnąco.
3. Format komórki, z której pobieramy numer transakcji (np. C2 w naszym przypadku) i format komórki pierwszej kolumny (G2:G11) tabele są różne (na przykład numeryczne i tekstowe). Sytuacja ta jest szczególnie typowa, gdy zamiast nazw tekstowych stosuje się kody numeryczne (numery rachunków, kody identyfikacyjne, daty itp.). W takim przypadku możesz użyć funkcji VALUE I TEXT do konwersji formatów danych. To wygląda tak:
   =VLOOKUP(TEXT(C2);$G$2:$H$11;0) .
4. Funkcja nie znajduje wartości, ponieważ kod zawiera spacje i niewidoczne znaki niedrukowalne (podawanie taśmy itp.). W takim przypadku możesz skorzystać z funkcji tekstowej (TRIM) I (CLEAN) aby je usunąć:
   =VLOOKUP(TRIM(CLEAN(C2));$G$2:$H$11;0) .

Tłumienie błędów

Aby ukryć komunikat o błędzie (#nie dotyczy) , jeśli funkcja nie może znaleźć dokładnego dopasowania, możesz jej użyć IFERROR . Ta funkcja sprawdza, czy wystąpił błąd (#nie dotyczy) jest efektem pracy WYSZUKAJ , a jeśli tak, wyświetla pustą taśmę ( „” ) lub zero, jeśli nie - wynik pracy WYSZUKAJ .

Artykuły na ten temat:

• Ulepszona funkcja VLOOKUP (VLOOKUP2)
• VLOOKUP dla kombinacji według dwóch warunków (VLOOKUP3)
• Szybkie łączenie dużych tabel (VLOOKUP2D)