Vlookup ( Wyszukaj.pionowo ) – czyli gdybym miał wybrać jedną najważniejszą funkcję…

Wyszukiwanie jakiejś wartości w tabeli, a potem przenoszenie jej w inne miejsce w arkuszu. To codzienność. Mamy nazwę produktu i musimy zerknąć w cennik, ile on kosztuje. Mamy nazwiska uczniów i chcemy wyciągnąć ich ocenę z jakiegoś przedmiotu. W końcu możemy mieć też przychód roczny i wybrać któryś próg podatkowy. Te wszystkie problemy pięknie rozwiązuje vlookup zwany po polsku wyszukaj.pionowo. (przypominam, że Arkusze Google tolerują pisanie funkcji zarówno po polsku jak i po angielsku).

Tu znajdziesz plik źródłowy. W nim są wszystkie przykłady

Jak działa vlookup?

Składnia funkcji na początku może być myląca, bo nie wiadomo o co chodzi z tymi indeksami i posortowaniem. =WYSZUKAJ.PIONOWO(kryteria_wyszukiwania; zakres; indeks; sortowany)

=VLOOKUP(kryteria_wyszukiwania; zakres; indeks; posortowany)

Na oglądanym przykładzie widać, że kod produktu pobierany jest z komórki A6, a następnie funkcja wyszukuje go w cenniku i podaje jego nazwę (druga kolumna zakresu G2:I14) oraz cenę (trzecia kolumna tego zakresu).

Dane do korzystania z vlookupa zwykle układa się tak żeby identyfikatory (unikalne nazwy lub kody) były po lewej stronie. Wówczas wyszukiwanie przebiega gładko.

Co jeśli identyfikator nie stoi w pierwszej kolumnie?

Możemy oczywiście skopiować i wkleić odpowiednie kolumny tak by nam pasowało, ale nie zawsze mamy prawo modyfikacji i nie zawsze jest miejsce w arkuszu. W tej sytuacji możemy się posłużyć zapisem klamrowym opisywanym we wcześniejszym wpisie.

Załóżmy zatem, że mamy cennik, którego nie mamy prawa ruszyć, a chcemy móc wyszukiwać kod produktu do znanej nam jego nazwy. W takiej sytuacji trzeba zrobić zakres tymczasowy. Będzie on miał postać: {H2:H14\G2:G14} – dla przypomnienia – klamrowy nawias pozwala umieścić wewnątrz kilka zakresów, które będą traktowane jako jeden. Teraz funkcja vlookup widzi H2:H14 w jednej kolumnie i na prawo od niego G2:G14. W tej sytuacji vlookup będzie szukał identyfikatorów w kolumnie H, a wartości poda z kolumny G (bo prosimy go by wyświetlił nam wartość z drugiej kolumny. =vlookup(B6;{H2:H14\G2:G14};2;FALSE)

Wyszukiwanie w innych arkuszach i innych plikach.

Funkcja vlookup może oczywiście działać pomiędzy różnymi zakładkami w tym samym pliku (wówczas musimy przed nazwą zakresu podać nazwę arkusza z wykrzyknikiem – np. Arkusz1!B7:B100, ale może też korzystać z danych trzymanych w innych plikach. Wówczas tam gdzie normalnie pojawiał się zakres, wstawiamy funkcję IMPORTRANGE, której zadaniem będzie ściągnięcie danych z innego pliku.

Zatem możemy zrobić formułę:

=vlookup(a1;importrange(“url arkusza”;”Arkusz1!a1:d1000″);4;false)

Wówczas funkcja vlookup znajdzie nam odpowiednią wartość w kolumnie D (czyli 4 kolumnie) w innym pliku w zakładce Arkusz1, tak jak by to było w naszym pliku. Ważne, że musimy mieć prawa czytania tego pliku i pozwolić na dostęp (świeżo po wpisaniu pierwszej funkcji odnoszącej się do nowego pliku, wyskoczy błąd #REF i okienko w którym musimy kliknąć “nadaj dostęp”).

Wyciąganie na raz wartości z kilku kolumn

Standardowo wartość indeks to numer kolumny z tabeli którą przeszukujemy i chcemy wyświetlić.

Ale to nie musi być tylko jedna kolumna. Jeśli zagnieździmy formułę vlookup z arrayformula, możemy zacząć wpisywać listę indeksów, które mają wylądować obok siebie. W formacie {indeks1 \ indeks2 \ indeks 3}. W takiej sytuacji jednym poleceniem możemy wywołać wartości z kilku kolumn.

Dodając Arrayformula możemy też rozciągnąć vlookup w dół, czyli wpisać cały zakres kryteriów wyszukiwania. Zamiast A1 to A1:A100 i uzyskamy 100 wyszukiwań.

Łącząc to z poprzednim sposobem – możemy jednym poleceniem wypełnić wszystkie potrzebne nam komórki:

A co zrobić jeśli funkcją vlookup chcemy wyciągnąć 100 kolumn z jednego wiersza?

Wiemy już, że możemy wyciągnąć zawartość kilku kolumn przy pomocy zapisu klamrowego. {2\4\6} da nam zawartość trzech kolumn z tabeli. Ale co zrobić jeśli chcemy wyciągnąć ich 100? Trzeba pisać taki wielki ciąg liczb: {1\2\3\4\5…} ?

Na szczęście nie. Możemy się posłużyć formułą sequence, która zrobi to za nas.

=SEQUENCE(ile wierszy; ile kolumn; liczba początkowa; przeskok)

W tym wypadku musimy wyprodukować liczby od 1 do 100 w jednym wierszu. Więc formuła będzie wyglądać tak:

=sequence(1;100)

Dwóch ostatnich zmiennych mogę nie wpisywać bo domyślnie wynoszą one 1).

=arrayformula( vlookup(A1;tabela;sequence(1;100);FALSE) )

Tak zrobiona formuła wyciągnie nam 100 kolumn z tabeli o nazwie tabela.

Jak wydobyć szybko kolumny parzyste lub nieparzyste?

Podobnie jak w poprzednim przykładzie – w miejsce indeksu wstawimy funkcję sequence.

Tabelę parzystych od 2 do 100 uzyskamy używając =sequence(1;50;2;2)

Tabelę nieparzystych od 1 do 99: =sequence(1;50;1;2)

=arrayformula( vlookup(A1;tabela;sequence(1;50;2;2);FALSE) )

Jak wykorzystać vlookup do określania progów rabatowych, przedziałów i innych działań, które robi się “wg tabelki”, ale wartości przypadają gdzieś pomiędzy…

Tu użyjemy w końcu ostatniego parametru formuły vlookup określającego czy tabela jest posortowana (true / false).

Przykład – zamów powyżej 1000zł, a dostaniesz rabat 10%, a powyżej 5000 zł – rabat 15%, 10 000 zł – rabat 20%, 20 000 zł – rabat 25% itd. Można to zrobić za pomocą formuły IF, ale jeśli wartości jest dużo to szkoda czasu. Mamy też mniej pisania, bo funkcja odnosi się do liczb w arkuszu.

W arkuszu wygląda to tak:

W komórce b28 wpisujemy kwotę “z palca” – 13 500zł. Nie występuje ona w tabeli, więc żeby vlookup nie wypluł nam błędu, ustawiamy ostatni parametr na true. Od tej pory formuła uznaje, że tabela jest posortowana i będzie przypisywać wartości idąc od góry, aż trafi na ostatnią spełnioną. W tym wypadku wychodzi, że ostatnim spełnionym progiem jest 10 000 i dostajemy rabat 20%.