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 – tabelę kredytów i tabelę zabezpieczeń:

Musimy automatycznie wstawić 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 odwołanie znajduje się funkcja VLOOKUP. Funkcja ta wyszukuje określoną wartość (w naszym przykładzie numer umowy) w skrajnej lewej kolumnie określonej tabeli (tabeli zabezpieczeń) przesuwając się od góry do dołu i po ich znalezieniu wyświetla wartość sąsiedniej komórki (rodzaj zabezpieczenia kredytu). Schematycznie działanie funkcji wygląda następująco:

Użyjmy więc funkcji VLOOKUP. Wybierz komórkę, w której zostanie wpisana (E2) i otwórz kreator formuł (menu Insert - Function). W kategorii (Wyszukiwanie i odniesienie) znajdź funkcję VLOOKUP i kliknij OK. Pojawi się okno do wpisania argumentów funkcji:

Wypełnij je jeden po drugim:

• Wyszukaj Value- numer umowy, której 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. Dla porównania użyjemy zakresu $G$2:$H$11 (zakres jest oznaczony znakiem dolara ($), aby podczas kopiowania formuły nie przesuwał się na dół).
• Column numer indeksu- 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 Wyszukaj- w tym polu możemy wpisać tylko dwie wartości: TRUE lub FALSE:
  
  1) Jeżeli wpiszemy wartość 0 lub FALSE, to tak naprawdę oznacza to, że wyszukiwanie dozwolone jest tylko z dokładnym dopasowaniem, czyli jeśli funkcja nie znajdzie numeru umowy w tabeli prowizyjnej (jeśli nie istnieje) to wyświetli się błąd #N/A (brak danych).
  
  2) Jeśli wpiszemy wartość 1 lub TRUE, będzie to oznaczać, że dozwolone jest nie tylko dokładne wyszukiwanie, ale także przybliżony zbieg okoliczności. W większości przypadków to przybliżone podstawienie może powodować fałszywe wyniki, gdy VLOOKUP podstawia wartości innych transakcji. Dlatego powinieneś używać wyszukiwania przybliżonego tylko dla wartości liczbowych (wstępnie posortowanych w kolejności rosnącej).

Pozostaje nacisnąć OK i skopiować wprowadzoną funkcję po 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ą

Funkcja VLOOKUP zwraca błąd (#nie dotyczy), jeśli:

1. Dozwolone jest wyszukiwanie dokładne (argument Range Lookup=0), a szukanej wartości nie ma w tabeli (Table).
2. Dozwolone wyszukiwanie w przybliżeniu (Range Lookup=1), ale w tabeli (Table), w której sprawdzamy wartości, nazwy nie są posortowane rosnąco.
3. Różni się format komórki, z której pobieramy numer transakcji (w naszym przypadku np. C2) i format komórki pierwszej kolumny (G2:G11) tabeli są różne (np. numeryczna i tekstowa). 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:
   =WYSZUKAJ.(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 użyć funkcji tekstowych (TRIM) i (CLEAN), aby je usunąć:
   =VLOOKUP(TRIM(CLEAN(C2));$G$2:$H$11;0).

Tłumienie błędów

Aby pominąć komunikat o błędzie (#N/A), gdy funkcja nie może znaleźć dokładnego dopasowania, możesz użyć funkcji IFERROR. Ta funkcja sprawdza, czy błąd (#N/A) jest wynikiem VLOOKUP i jeśli tak, zwraca pusty ciąg znaków („”) lub wartość null, w przeciwnym razie jest wynikiem VLOOKUP.

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)