WYSZUKAJ PIONOWO

Funkcja WYSZUKAJ.PIONOWO przez wielu uznawana jest za najbardziej przydatną w Excelu, w tym również przeze mnie. Ma ona bardzo wiele zastosowań, choć jej podstawową funkcję można określić jako wyszukiwanie w tabeli wartości przypisanej do podanego elementu.

Do czego służy funkcja WYSZUKAJ.PIONOWO w Excelu?

Powyżej zapisana definicja jest bardzo ogólna i nie daje wyobrażenie, jak w praktyce może przydać się ta funkcja, dlatego zaprezentuję poniżej jej najpopularniejsze zastosowania, posługując się przykładami.

 

Przeszukiwanie tabeli za pomocą WYSZUKAJ.PIONOWO

Wyobraź sobie, że pracujesz na tabeli opisującej faktury. Jedna kolumna to numer faktury, inna kolumna to kwota na fakturze, itd. Tabela składa się z 10.000 wierszy. Dostajesz maila od współpracownika/przełożonego/klienta z numerem faktury i prośbą o sprawdzenie, czy została zapłacona i na jaką kwotę została wystawiona. Co robisz?

Rysunek 1. Przykładowa tabela z listą faktur.

W tej sytuacji, jednym z rozwiązań jest użycie w Excelu narzędzia Znajdź (czyli skrót CTRL + F), wpisanie w nim numery faktury i w ten sposób szybko odnajdziesz potrzebne informacje. Inny pomysł może być taki, żeby podany numer faktury wpisać w filtrze, w kolumnie z numerami faktur – wtedy również szybko wykonasz swoje zadanie, prawda? Myślę, że tak.

No to po co używać tej sławnej funkcji WYSZUKAJ.PIONOWO? W powyższym przypadku niczego nie ułatwi, ani nie przyspieszy, ale… wyobraź sobie, że dostajesz w mailu listę 200 numerów faktur zamiast tylko jednego. Widzisz już swoją frustrację, gdy musisz 200 razy używać narzędzia Znajdź, aby odnaleźć właściwe dane, zaznaczyć je, skopiować i wkleić do maila (lub osobnego arkusza)? A może 200 razy filtrujesz tabelę, zaznaczasz potrzebne komórki, kopiujesz i wklejasz do maila? Lepiej nawet nie myśleć co by było, gdyby na tej liście było 1.000, a nie dwieście numerów… I właśnie wtedy z pomocą przychodzi WYSZUKAJ.PIONOWO!

Rysunek 2. Dane wyszukane za pomocą WYSZUKAJ.PIONOWO (zamiast 200 faktur na zdjęciu pokazałem tylko kilka z nich, żeby zdjęcie nie było ogromne 😉 ).

Na powyższym przykładzie, podane numery faktur zostają przekopiowane do Excela (nie ważne jak dużo ich jest), a w kolumnie obok nich zapisana jest funkcja WYSZUKAJ.PIONOWO (przeciągnięta do wszystkich numerów). Obojętnie czy tych numerów masz 10, czy 10.000, to rozwiązanie działa tak samo szybko i precyzyjnie. Taką listę informacji możesz potem skopiować do maila, albo do osobnego arkusza, który załączysz do wiadomości.

Podsumujmy jeszcze zalety korzystania z funkcji WYSZUKAJ.PIONOWO w stosunku do innych, wymienionych narzędzi Excela (zakładając, że mamy do wyszukania wiele wartości):

    • jest zdecydowanie szybsza
    • pozwala uniknąć „błędów ludzkich” – gdy masz wielokrotnie coś wyszukać -> zaznaczyć -> skopiować -> wkleić, to statystycznie patrząc, masz duże szanse dokonać nawet małej pomyłki, ale w Excelu mały błąd, to zawsze błąd i konsekwencje może mieć duże.
    • Raz przygotowaną formułę możesz wykorzystać wielokrotnie – jeśli np. dostajesz podobnie pytanie kilka razy w tygodniu, to kopiujesz już raz napisaną formułę (nie tworzysz jej za każdym razem od początku).
    • W każdej chwili możesz zmienić zasady wyszukiwania. Powiedzmy że ręcznie wyszukałeś/aś 200 pozycji, zapisując czy faktura została zapłacona i jej kwotę , a godzinę później dostajesz wiadomość, że w przypadku faktur zapłaconych potrzebna jest jeszcze data zapłaty… W przypadku ręcznej pracy, musisz znów przejść 200 razy swój rytuał wyszukiwania. W przypadku WYSZUKAJ.PIONOWO w kilkanaście sekund dodajesz nową kolumnę, która wyświetla daty płatności dla poszukiwanych faktur.

Rysunek 3. Dodanie kolumny „Data zapłaty” i znalezienie dat za pomocą WYSZUKAJ.PIONOWO.

Oczywiście omawiane w tej sekcji przeszukiwanie tabeli może odbywać się w różnym kontekście, na różnych tabelach, ale konkretne, inne przykłady zaprezentuję Ci w kolejnych sekcjach tego artykułu.

Zadania z WYSZUKAJ.PIONOWO

Sprawdź za darmo calculatic.pl gdzie znajdziesz wiele więcej zastosowań dla WYSZUKAJ.PIONOWO!

SPRAWDŹ ZA DARMO
 

Wybór jednej z kilku opcji w Excelu za pomocą WYSZUKAJ.PIONOWO

Jest to jedno z moich ulubionych zastosowań tej funkcji, szczególnie gdy uczę na szkoleniach kursantów sprawnego posługiwania się danymi.

Powiedzmy, że mamy w Excelu tabelę z kwotami płatności za nasze produkty/usługi, czyli naszą sprzedaż, ale że jesteśmy dużą firmą, to sprzedajemy je również za granicą. Za granicą klienci płacą we własnych walutach, więc na nasze konto oprócz złotówek spływają też dolary, euro, korony duńskie, funty brytyjskie, itd. Przychodzi moment podsumowania wyników sprzedaży (np. koniec miesiąca) i jak teraz zsumować euro ze złotówkami? Jeśli mam 3.000 euro i 2.000 złotych, to jak teraz policzyć ich sumę? Oczywiście – muszę przeliczyć je na jedną walutę i niech to będą PLN.

Rysunek 4. W pierwszej kolumnie są kwoty, a w drugiej kolumnie zapisano w jakiej walucie te kwoty są podane.

To jakiego kursu użyje do przeliczeń, to już mniej istotna kwestia (np. kurs na koniec miesiąca, kurs na dzień dokonywania analizy). Ważne jest, żeby wszystkie kwoty przeliczyć za pomocą kursu odpowiedniej waluty. No dobra, ale co jeśli mam 15.000 transakcji, które odbywały się w sumie w 10 różnych walutach? Można próbować zrobić to ręcznie, np. sortując tabelę według waluty, a potem tworząc osobną formułę dla każdej z nich, albo można pewnie wymyślić jeszcze inne, uciążliwe metody, ale w tym miejscu przypominam o wymienionych wcześniej zaletach WYSZUKAJ.PIONOWO:

    • szybkość – mam w końcu 15.000 wierszy i 10 różnych walut.
    • Brak „błędów ludzkich” – powtórzę: 15.000 wierszy i 10 różnych walut
    • Wielokrotne wykorzystanie – przecież co miesiąc muszę szykować takie podsumowanie
    • Mogę zmienić zasady wyszukiwania – może do dodatkowej analizy będę musiał przeliczyć kwoty również po średniomiesięcznym kursie.

Użycie funkcji WYSZUKAJ.PIONOWO zajmie mi może więcej niż 5 sekund, ale w sprzyjających okolicznościach może to być mniej niż 1 minuta! Co jest w takim razie potrzebne? Potrzebujemy stworzyć tabelkę pomocniczą, w której w jednej kolumnie będzie nazwa waluty (PLN, EUR, USD, itd), w drugiej będzie kurs wymiany tej waluty na PLN. Kursy bierzesz z tego źródła, z którego zawsze korzystasz (zapewne z jakiejś strony internetowej) i wklejasz do tej nowej tabeli.

Rysunek 5. Nazwa waluty w pierwszej kolumnie, a kurs (każdej z walut w stosunku do PLN) w drugiej kolumnie.

Następnie tworzysz formułę z funkcją WYSZUKAJ.PIONOWO, która jest tak samo mało skomplikowana jak we wcześniej omawianym przykładzie i nie ma tu znaczenia czy walut masz 5, czy 10, czy 100, czy nawet 1.000 – zapis funkcji jest tak samo krótki i szybki.

Rysunek 6. Na pasku formuły widać funkcję WYSZUKAJ.PIONOWO, która przelicza wszystkie kwoty na złotówki.

A co, jeśli chcesz zmienić kurs na inny, bo musisz użyć tym razem kursu średniomiesięcznego? W tabelce pomocniczej podstawiasz nową wartość kursu, a cała reszta oblicza się sama w ułamku sekundy. Przydatne?

Rysunek 7. W stosunku do rysunku 6. zmieniłem kurs EUR w komórce F3, a kwoty przeliczane z EUR automatycznie się zaktualizowały.

Łączenie danych z dwóch tabel Excela w jedną – WYSZUKAJ.PIONOWO to potrafi

Tym razem chcę Cie przekonać, że ta WYSZUKAJ.PIONOWO potrafi skutecznie łączyć dane z różnych źródeł w Excelu. Możesz powiedzieć „co to za problem, kopiuje dane z jednej tabeli i wklejam je pod drugą tabelą. Problem rozwiązany”. Oczywiście nieraz stykamy się z taką sytuacją, gdzie to rozwiązanie jest zupełnie wystarczające, bo tabele wyglądają bliźniaczo, wszystkie kolumny są w tej samej kolejności, żadnych z kolumn nie brakuje.

Ja chce opowiedzieć o nieco trudniejszym przypadku. Wyobraź sobie, że masz tabelę z listą pracowników pewnego oddziału Twojej firmy i musisz komuś zaraportować (przesłać po prostu plik) informacje o ilości dni chorobowych każdej z tych osób od początku roku, ilości wykorzystanego urlopu i ocenach półrocznych, wystawianych przez ich przełożonych.

Rysunek 8. Lista pracowników, dla których mamy odszukać dane.

W czym problem? Wyzwania w tym zadaniu to:

    • dość duża ilość pracowników, których dane trzeba podać – 300 osób. Ciężko jest te dane ręcznie spisywać.
    • informacje o chorobowych i urlopach są w jednym pliku, a oceny półroczne w innym pliku. Rzadko się zdarza tak, żeby wszystkie dane, które są nam w pracy potrzebne znajdowały się dokładnie w jednym pliku, w jednej tabeli. Praca w Excelu wymaga zazwyczaj umiejętnego wyszukiwania i łączenia danych z wielu różnych źródeł.
    • Nie możemy liczyć na to, że lista pracowników działu, którą ktoś nam podał i którą mamy uzupełnić zawiera dokładnie te same osoby (w tej samej kolejności) co tabele, z których dane będziemy pobierać. Dlaczego nie? Oczywiście to by było piękne, gdyby tak było w rzeczywistości i pewnie nieraz się zdarza, ale w naszym przypadku istnieje ryzyko, że chociażby na podanej nam liście nie ma już jakiejś zwolnionej osoby, a jest ona w tabeli z urlopami (bo ta tabela zawiera też dane z poprzednich miesięcy, gdy ta osobna jeszcze pracowała). Brak chociaż jednej osoby na jednej z list, powoduje że posortowanie danych nie da nam dwóch dokładnie takich samych list, które można łatwo skopiować i wkleić.

Rysunek 9. Zwróć uwagę na to, że plik z urlopami i chorobowymi zawiera nieco inną listę ID pracowników (inna kolejność, może nieco więcej albo mniej pracowników).

Rysunek 10. Lista z ocenami pracowników różni się również od list z dwóch pozostałych arkuszy Excela. Rozpisałem się o wyzwaniach, więc teraz jak je szybko wypełnić?

    • musimy w każdej tabeli mieć kolumnę z jakimś identyfikatorem pracownika (np. ID pracownika). Za pomocą tej wartości będzie pracowników rozróżniać/identyfikować.
    • następnie na liście z pracownikami, którą podane nam do uzupełnienia wpisujemy funkcje WYSZUKAJ.PIONOWO, która będzie szukać wartości urlopu i dni chorobowego z jednego pliku.
    • w kolumnie obok wprowadzamy kolejne WYSZUKAJ.PIONOWO, które będzie szukać oceny półrocznej w innym pliku Excela.
    • Przeciągamy formuły do końca listy pracowników i gotowe.

Rysunek 11. WYSZUKAJ.PIONOWO odnajduje potrzebne dane. W przypadku jednego pracownika pojawiły się błędy #N/D – oznacza to, że nie został znaleziony w pliku Excela z urlopami i chorobowymi, co jest istotną informacją, którą należy przeanalizować.

Porównanie dwóch list, czyli czy wartości z jednej tabeli znajdują się w drugiej

Mamy plik Excel z tabelą, w której znajduje się lista zamówień złożonych przez naszych klientów w ostatnim miesiącu. W drugim pliku Excela mamy listę zrealizowanych zleceń z ostatniego miesiąca. Jeśli wszystko poszło dobrze, to wszystkie złożone zamówienia zostały też przez nas zrealizowane. Innymi słowy, wszystkie zamówienia z jednego pliku znajdują się też w drugim. My chcemy się upewnić, że tak jest, bo w prawdziwym świecie nie zawsze wygląda to tak kolorowo.

Rysunek 12. Lista złożonych zamówień w jednym pliku Excela.

Rysunek 13. Lista zrealizowanych zamówień w innym pliku Excela.

Po pierwsze, musimy jakoś identyfikować każde zamówienie – służy do tego po prostu numer zamówienia. Sprawdzamy za pomocą WYSZUKAJ.PIONOWO, czy każdy z numerów z jednego pliku znajduje się gdzieś w drugim pliku (dane mogą być wymieszane, to nie jest problem). Jeśli jakiegoś numeru zamówienia brakuje, to funkcja WYSZUKAJ.PIONOWO wyświetla błąd – dla nas jest to jednoznaczna informacja, że tego zamówienia brakuje w drugiej tabeli Excela.

Rysunek 14. Funkcja WYSZUKAJ.PIONOWO wyświetla błąd gdy zamówienia nie ma w „zrealizowanych”, a gdy jest, to wyświetla ponownie numer tego zamówienia.

Jak działa funkcja WYSZUKAJ.PIONOWO?

W poprzedniej sekcji opisałem do czego może WYSZUKAJ.PIONOWO posłużyć, a teraz wytłumaczę jak prawidłowo ją zapisać, aby działała.

Zanim przedstawię Ci kolejne argumenty tej funkcji, ważne jest żeby zrozumieć istotę jej działania. Jeśli czegoś szukamy, to musimy mieć jakiś „trop” albo „klucz”, za pomocą którego szukamy. Jeśli szukamy pracownika, to szukamy go np. za pomocą numeru pracownika albo numeru PESEL. Gdy już mamy tę „wartość kluczową” to musimy wskazać miejsce, gdzie chcemy szukać, czyli po prostu tabelę, gdzie znajdują się potrzebne nam dane. Jako, że tabele mają zazwyczaj wiele kolumn (a nas często interesuje zawartość tylko jednej lub kilku z nich), to wskazuję tę kolumnę (np. z wynagrodzeniem). Wówczas WYSZUKAJ.PIONOWO wyświetla nam wartość dla wskazanej kluczowej wartości (ID pracownika), ze wskazanej kolumny (Wynagrodzenie), we wskazanej tabeli. A oto lista argumentów tej funkcji:

Rysunek 15. Rozrysowany schemat działania funkcji WYSZUKAJ.PIONOWO w Excelu.

  1. Wartość kluczowa – innymi słowy jest to identyfikator, czyli coś co jednoznacznie identyfikuje nam szukany element. Może to być PESEL pracownika, numer produktu, numer zamówienia, itd. Nie może to być np. imię pracownika, bo wiele osób może mieć tak samo na imię – ważne jest, żeby ta wartość kluczowa jednoznacznie wskazywała szukany element.
  2. Tabela – pracujemy zazwyczaj na wielu plikach Excela, a one mają wiele tabel. W związku z tym, funkcja oczekuje od nas wskazania tej tabeli, która zawiera jednocześnie wartość kluczową (czyli to co podaliśmy w argumencie nr 1) oraz to co ostatecznie chcemy znaleźć.
  3. Numer kolumny – WYSZUKAJ.PIONOWO przeszukuje tabelę z góry na dół, aż znajdzie wartość kluczową (to co podaliśmy w argumencie nr 1). Wtedy wiadomo, że cały wiersz opisuje ten szukany przez nas element. Ale tabela ma np. 30 kolumn, a funkcja może wyświetlić wartość tylko jednej z nich, więc ten argument, to jest wskazanie numeru kolumny w ramach zaznaczonej tabeli.
  4. Rodzaj wyszukiwania – ten argument pozwala włączyć jeden z dwóch „trybów” funkcji WYSZUKAJ.PIONOWO. Co to za tryby? Powiedzmy, że wpisując tutaj „0” albo „FAŁSZ” uruchamiasz tryb, który cały czas, do tej pory opisywałem. Wpisanie tam „1” lub „PRAWDA” powoduje, że funkcja działa nieco inaczej. Więcej o tym drugim trybie przeczytasz w jednej z kolejnych sekcji, ponieważ jest on stosunkowo rzadko używany.

WYSZUKAJ PIONOWO – ćwiczenia i przykłady

Do tej pory pokazałem do czego funkcja może się przydać w Excelu oraz jak działa (jak jest zbudowana). Teraz czas połączyć te dwa tematy, pokazując jak w praktyce jej używać. Posłużę się w tym celu przykładami podanymi już wcześniej, ale tym razem opiszę od A do Z jak zapisać funkcję WYSZUKAJ.PIONOWO.

Wyszukiwanie informacje na temat faktur w Excelu

Będę opisywał przykład zaprezentowany wcześniej na rysunku 2. Dla podanych numerów faktur, chcę znaleźć informacje o kwocie faktury oraz czy została opłacona. Cała formuła wprowadzona do komórki G2 wygląda tak:

=WYSZUKAJ.PIONOWO($F2;$A:$D;2;0)

Rysunek 16. Funkcja WYSZUKAJ.PIONOWO zbierająca informacje na temat faktur.

Teraz opiszę krok po kroku, jak tę funkcję się zapisuje. Po pierwsze, zapisuję znak „=” i nazwę funkcji, wraz z nawiasem otwierającym:

=WYSZUKAJ.PIONOWO(

Pierwszy argument, to wartość, według której szukam (identyfikator) kwoty faktury oraz informacji o płatności. Szukam tego oczywiście na podstawie podanego numeru faktury. Zapisuję formułę w komórce G2, więc numer faktury biorę z odpowiadającej jej komórce w kolumnie F:

=WYSZUKAJ.PIONOWO(F2

Formuła będzie przeciągana w prawo, do kolumny „Zapłacona?”, z tego powodu w adresie F2 blokuję kolumnę dolarem:

=WYSZUKAJ.PIONOWO($F2

Następny argument funkcji, to tabela, w której chcesz szukać. Tabela znajduje się oczywiście po lewej stronie, więc ją zaznaczam. Często zamiast zaznaczać konkretne komórki, w tym przypadku A1:D12, zaznacza się całe kolumny. Dlaczego? Na wypadek, gdyby w przyszłości przybyło danych i zajmowały one więcej niż te obecne 12 wierszy. Pamiętaj, żeby oddzielić od siebie argumenty średnikiem albo przecinkiem, w zależności od wersji językowej Excela.

=WYSZUKAJ.PIONOWO($F2;$A:$D

Trzeci argument to numer kolumn w ramach zaznaczonej tabeli. Chcemy w tej chwili znaleźć „Kwotę” (pozostałymi informacjami zajmiemy się za chwilę), a kwoty są w drugiej kolumnie naszej tabeli:

=WYSZUKAJ.PIONOWO($F2;$A:$D;2

Ostatni argument to zazwyczaj 0 albo inaczej FAŁSZ. W osobnej sekcji powiemy, kiedy ta wartość może być inna:

=WYSZUKAJ.PIONOWO($F2;$A:$D;2;0)

Możesz tę formułę przeciągnąć na dół. Jednak, gdy przeciągniesz ją w prawo, to w kolejnej kolumnie musisz dostosować trzeci argument, czyli numer kolumny. Dlaczego? Bo te kolejne informacje są w kolumnie nr 3 (zazwyczaj ręcznie zmienia się tę wartość w formule):

Formula w kolumnie „Zapłacona?” (kolumna H):

=WYSZUKAJ.PIONOWO($F2;$A:$D;3;0)

Rysunek 17. WYSZUKAJ.PIONOWO tym razem pobiera dane z kolumny nr 3.

 

Przeliczenie walut w Excelu za pomocą funkcji WYSZUKAJ.PIONOWO

Znów omówimy sobie zaprezentowane wcześniej zadanie z przeliczaniem kwoty sprzedaży w różnych walutach na złotówki, czyli to co widać na rysunku 6. Przejdźmy przez formułę krok po kroku.

Rysunek 18. Gotowa formuła w zadaniu z przeliczeniem walut w Excelu.

Funkcja WYSZUKAJ.PIONOWO ma za zadanie znaleźć odpowiedni kurs, co jest najtrudniejszą częścią zadania (potem wystarczy tylko przemnożyć to przez kwotę sprzedaży). Wstawiam funkcję i jej pierwszy argument do komórki C2. Moja funkcja ma zdecydować, który kurs będzie użyty w obliczeniach, a kurs przypisany jest do waluty. W związku z tym, muszę kursu szukać za pomocą waluty, której nazwa jest podana w kolumnie B:

=WYSZUKAJ.PIONOWO(B2

Gdzie będę szukał teraz tej waluty? Oczywiście w tabelce po prawej stronie, w której za pomocą nazwy waluty, znajdę jej kurs wymiany na złotówki:

=WYSZUKAJ.PIONOWO(B2;$E$2:$F$11

Teraz muszę wskazać, jaki jest numer interesującej mnie kolumny, ale w ramach zaznaczonej tabelki. Mnie interesuje kurs, a on jest w kolumnie F, czyli w kolumnie nr 2 (licząc w ramach zaznaczonej tabeli):

=WYSZUKAJ.PIONOWO(B2;$E$2:$F$11;2

Na końcu funkcji dopisuje standardowo 0 (czyli inaczej FAŁSZ):

=WYSZUKAJ.PIONOWO(B2;$E$2:$F$11;2;0)

Powyższa formuła, wstawiona do komórki Excela, wyszuka kurs, co było najważniejszą częścią tego zadania, niemniej jednak musimy ostatecznie wyliczyć kwotę w złotówkach. Aby to zrobić, przemnóż otrzymany kurs przez kwotę w kolumnie A, a otrzymasz kwoty w złotówkach:

=WYSZUKAJ.PIONOWO(B2;$E$2:$F$11;2;0)*A2

 

WYSZUKAJ.PIONOWO znajduje urlopy i oceny w osobnych arkuszach Excela

Jest to ćwiczenie, które prezentowane było na początku artykułu, gdzie dla podanych pracowników (mamy ich ID) należy znaleźć informacje o urlopie i ocenie półrocznej. Problem polega na tym, że dane o urlopach są w innym arkuszu i o ocenie także w innym. W tym przykładzie założymy, że te dane są w 3 różnych arkuszach, ale równie dobrze mogłyby być to 3 różne pliki Excela.

Rysunek 19. Formuła pobierająca urlopy z innego arkusza Excela.

Wstawiamy funkcję WYSZUKAJ.PIONOWO do arkusza, w którym mamy podane numery ID pracowników, do komórki B2:

=WYSZUKAJ.PIONOWO(B2

Jako, że nasza formuła znajduje się w kolumnie „Urlopy”, to zaczniemy od wyszukiwania ich właśnie. Tabela, którą teraz zaznaczymy, to tabelka w arkuszu z urlopami:

=WYSZUKAJ.PIONOWO(B2;Urlopy!A:B

Przypominam, że kolejne argumenty w Excelu oddzielamy średnikiem (lub przecinkiem). Gdy zaznaczamy tabelę w innym arkuszu, to pojawia się w formule nazwa tego arkusza (w naszym przypadku „Urlopy”). Trzeci argument to numer kolumny w ramach zaznaczonej tabeli:

=WYSZUKAJ.PIONOWO(B2;Urlopy!A:B;2

Ostatni argument to standardowe 0, czyli FAŁSZ:

=WYSZUKAJ.PIONOWO(B2;Urlopy!A:B;2;0)

Jeśli teraz chcemy wyszukać również ocen półrocznych, to najlepiej zapisać funkcję WYSZUKAJ.PIONOWO od początku w kolejnej kolumnie. Czemu nie przeciągnąć jej po prostu w prawo? Można tak zrobić, ale trzeba wtedy zmienić na tyle dużo rzeczy, że zazwyczaj jest łatwiej zacząć od początku.

Rysunek 20. WYSZUKAJ.PIONOWO wyszukuje wartości w innym arkuszu.

Do C2 wprowadzamy formułę:

=WYSZUKAJ.PIONOWO(

Pierwszy argument to ponownie identyfikator pracownika:

=WYSZUKAJ.PIONOWO(A2

Drugi argument to tabela z ocenami pracowników, która jest w arkuszu o nazwie „Oceny”:

=WYSZUKAJ.PIONOWO(A2;Oceny!A:B

Trzeci argument to numery kolumny, z której chcesz pobrać informacje, czyli:

=WYSZUKAJ.PIONOWO(A2;Oceny!A:B;2

Na końcu standardowe 0, czyli inaczej FAŁSZ:

=WYSZUKAJ.PIONOWO(A2;Oceny!A:B;2;0)

Obie formuły można przeciągnąć w dół, dla wszystkich pracowników.

 

Pozwól WYSZUKAJ.PIONOWO sprawdzić, czy zamówienia się zgadzają

Jest to ćwiczenie, którego historia została wcześniej już opisana. Mamy tabelę ze złożonymi przez klientów zamówieniami oraz ze zrealizowanymi zamówieniami. Chcemy sprawdzić, czy wszystkie zostały zrealizowane. Dane mamy w dwóch osobnych arkuszach Excela.

Rysunek 21. Funkcja WYSZUKAJ.PIONOWO wyświetla błąd, gdy nie może znaleźć wskazanej wartości.

Wprowadź do D2 funkcję WYSZUKAJ.PIONOWO, której pierwszy argument, to będzie nr zamówienia:

=WYSZUKAJ.PIONOWO(A2

Kolejny argument, to wskazanie tabeli, w której chcemy sprawdzić, czy to zamówienie występuje. Zaznaczamy więc w drugim arkuszu tabelę z numerami zamówień, a konkretnie, w praktyce zazwyczaj zaznacza się tylko jedną kolumnę z numerami zamówień. Dlaczego tylko jedną? Możesz zaznaczyć więcej, ale nie będą one do niczego przydatne, bo nie szukamy informacji o tych zamówieniach (np. kwoty), ale jedynie chcemy sprawdzić, czy te numery znajdują się w tej drugiej tabeli, a do tego wystarczy nam jedna kolumna:

=WYSZUKAJ.PIONOWO(A2;'Zrealizowane zamowienia'!A:A

Teraz wskazujemy numer kolumny. Jeśli zaznaczyliśmy przed chwilą tylko jedną kolumnę, to teraz musimy dać numer 1:

=WYSZUKAJ.PIONOWO(A2;'Zrealizowane zamowienia'!A:A;1

Na końcu standardowe 0:

=WYSZUKAJ.PIONOWO(A2;'Zrealizowane zamowienia'!A:A;1;0)

Przeciągnij formułę. Możesz zobaczyć w niektórych komórkach błąd „#N/D”, co oznacza, że taki numer zamówienia nie został znaleziony, czyli w naszym zadania oznacza to, że zamówienie nie zostało jeszcze zrealizowane. Jeśli jako wynik pokazuje się powtórzony numer zamówienia, to znaczy, że zamówienie zostało odnalezione.

Zadania z WYSZUKAJ.PIONOWO

Sprawdź za darmo calculatic.pl gdzie znajdziesz wiele więcej zastosowań dla WYSZUKAJ.PIONOWO!

SPRAWDŹ ZA DARMO
 

Ważna zasady używania w Excelu WYSZUKAJ.PIONOWO

Powiedzieliśmy już sobie bardzo dużo na temat zastosowania i sposobu działania funkcji WYSZUKAJ.PIONOWO. Muszę jednak uściślić pewne zasady korzystania z jej, o których do tej pory milczałem, żeby nie utrudniać Ci ogólnego zrozumienia, jak działa funkcja WYSZUKAJ.PIONOWO w Excelu.

 

Wartość kluczowa w pierwszej kolumnie

We wszystkich dotychczasowych przykładach wartość kluczowa, czyli to co podajemy w pierwszym argumencie funkcji, znajdowało się w pierwszej kolumnie przeszukiwanej tabeli (podanej w drugim argumencie). Gdy używaliśmy ID pracownika, to w przeszukiwane tabeli kolumna ID pracownika był pierwsza. Gdy działaliśmy na numerach faktur, to znajdowały się one także w pierwszej kolumnie przeszukiwanej tabeli. To nie był przypadek.

Rysunek 22. ID pracownika jest w pierwszej kolumnie zaznaczanej tabeli.

Rysunek 23. Nr faktury, za pomocą którego WYSZUKAJ.PIONOWO szuka, jest w pierwszej kolumnie zaznaczonej tabeli. Jeśli te kluczowe wartości byłyby w innej kolumnie niż pierwsza, to funkcja nie zadziałałaby, bo i tak szukała by ich w pierwszej kolumnie. Dlaczego tak jest? Żeby łatwiej było używać WYSZUKAJ.PIONOWO, żeby nie było trzeba za każdym razem wskazywać gdzie jest kolumna z kluczowymi wartościami. Innymi słowy, Excel „umawia się” z nami, że zawsze będziemy pilnowali, żeby ta wartość  była w pierwszej kolumnie, dzięki czemu funkcja WYSZUKAJ.PIONOWO nie będzie tak bardzo skomplikowana.

Chcę podkreślić jeszcze jedną ważną rzecz – to musi być pierwsza kolumna w ramach tego, co zaznaczymy w drugim argumencie funkcji. Czyli, jeśli wartość kluczowa jest w tabeli w kolumnie trzeciej, ale my zaczniemy nasze zaznaczanie od tej trzeciej kolumny, to dla nas ona będzie pierwszą kolumną (w ramach naszego zaznaczenia).

Rysunek 24. W tym przypadku, drugi argument WYSZUKAJ.PIONOWO, to jest zakres od kolumny C do D. W związku z tym, kolumna C („Nr faktury”) jest pierwszą kolumną dla naszej funkcji.

 

Wyszukuje pierwsze wystąpienie identyfikatora

Jeśli szukamy wynagrodzenia pracownika, za pomocą jego ID, to jego ID będzie naszym tzw. identyfikatorem, czy też wartością kluczową. Wyobraź sobie teraz, że przez przypadek to samo ID znajduje się przy dwóch pracownikach. Którego z nich wówczas odnajdzie funkcja WYSZUKAJ.PIONOWO? Znajdzie tego, które jest wyżej w tabeli. Czyli funkcja szuka z góry na dół, aż znajdzie pierwsze wystąpienie ID i dalej już nie szuka.

Rysunek 25. Powtórzony identyfikator – WYSZUKAJ.PIONOWO znajduje pierwszy z nich.

Z tego powodu, gdy używamy WYSZUKAJ.PIONOWO, to w przeszukiwanej tabeli identyfikatory nie powinny się powtarzać, bo jeśli się powtarzają to funkcja i tak znajdzie tylko jeden z nich i to przypadkowy z nich bo ten, który akurat w tabeli jest najwyżej.

Jeśli identyfikatory powtarzają się w przeszukiwanej tabeli, to być może powinieneś/powinnaś szukać danych w innej tabeli, albo za pomocą innego identyfikatora (z innej kolumny) albo po prostu ta funkcja nie przyniesie Ci żadnej korzyści w takim przypadku.

 

Zawsze blokuj drugi argument dolarami

Drugi argument to tabela, w której szukamy danych. Moja prosta rada, to zawsze blokuj ten argument dolarami (skrót F4). Dlaczego? Krótko mówiąc, bardzo często jest to potrzebne. Wtedy gdy to nie jest konieczne, to zablokowanie i tak nie zaszkodzi. Zamiast zastanawiać się za każdym razem, czy jest to teraz konieczne, czy nie, lepiej wyrobić sobie nawyk, żeby zawsze blokować ten argument. Sytuacje, gdy blokowanie przeszkadzałoby są bardzo rzadkie, i dotyczą bardzo skomplikowanego użycia funkcji WYSZUKAJ.PIONOWO, więc jeśli ktoś używa jej w taki sposób, to na pewno nie ma też problemu z posługiwaniem się dolarami i wie kiedy je wstawić.

A dlaczego te dolary są zazwyczaj potrzebne? Bo jeśli chcesz wyszukać wiele danych, to szukasz ich w tym samym źródle (przeszukujesz tę samą tabelę) i to źródło trzeba zablokować, żeby po przeciągnięciu formuły, funkcja wyszukiwała cały czas dokładnie w tej samej tabeli. Jeśli chcesz czegoś szukać w kilku osobnych tabelach, to wtedy i tak zapisujesz z osobna kilka razy nową formułę z WYSZUKAJ.PIONOWO.

Rysunek 26. Nie zablokowano drugiego argumentu – wówczas po przeciągnięciu formuły o 4 wiersze w dół, również zaznaczona tabela przesunęła się o 4 wiersze w dół i w ten sposób pomija dane na samej górze.

 

Wielkość znaków nie ma znaczenia

Podobnie jak większość narzędzi Excela, funkcja WYSZUKAJ.PIONOWO nie zwraca uwagi na wielkość liter. Co to znaczy? Jeśli szukasz kogoś o nazwisku „Nowak”, to jeśli funkcja zobaczy w tabeli „nowak” pisane przez małe „n”, to będzie to dla niej dokładnie to samo, co „Nowak” przez duże „N”.

WYSZUKAJ.PIONOWO i błędy

Często zdarza się tak, że WYSZUKAJ.PIONOWO nie znajduje kilku z szukanych wartości bo po prostu ich nie ma w przeszukiwanej tabeli. Wówczas, jako wynik, pokazuje błąd „#N/D”, co oznacza „niedostępny”. Taki błąd nie jestem naszym błędem, w sensie, że coś źle zrobiliśmy, tylko jest to sposób, w jaki funkcja nas informuje, że nie może czegoś znaleźć.

Niemniej jednak, błędy w Excelu źle wyglądają, sugerują, że ktoś źle zapisał formułę, a także uniemożliwiają czasem działanie kolejnych formuł. Dlatego właśnie, warto takie błędy zamienić na coś „bardziej strawnego”. Nie będziemy takich błędów ręcznie zamieniać na coś innego, bo nie po to używamy formuły, żeby potem ręcznie wpisywać wartości. Użyjemy dodatkowej funkcji, która „z automatu” będzie zamieniać błędy na to, co chcemy wyświetlić.

Rysunek 27. Tabela z zamówieniami, gdzie WYSZUKAJ.PIONOWO wyświetliła błędy.

Ta funkcja nazywa się JEŻELI.BŁĄD. Omówię ją na przykładzie, który opisywany był już wcześniej, mianowicie na tabeli ze złożonymi i zrealizowanymi zamówieniami. Widać tam błędy, bo niektóre złożone zamówienia jeszcze nie zostały zrealizowane. Powiedzmy, że w tej sytuacji, zamiast błędu, chcę wyświetlić tekst „nie zrealizowane”. Funkcja używa dwóch argumentów:

  1. Wartość, która ewentualnie zwraca błąd – w praktyce, tutaj po prostu umieszcza się całą funkcję WYSZUKAJ.PIONOWO. Dlaczego użyłem słowa „ewentualnie”? Bo błąd może się pojawić, ale nie musi, nie wiemy tego.
  2. Wartość, na którą błąd ma być zamieniony – może to być tekst, może to być liczba, może to być adres komórki, ale może to być też jakaś kolejna funkcja – co tylko chcesz.

W naszym przykładzie, funkcja WYSZUKAJ.PIONOWO wyglądała następująco:

=WYSZUKAJ.PIONOWO(A2;'Zrealizowane zamowienia'!A:A;1;0)

Dodajemy do tej formuły funkcję JEŻELI.BŁĄD, ale robimy to od pierwszej komórki do ostatniej (a nie tylko tam gdzie jest błąd) – dlaczego? Bo w praktyce nie wiemy gdzie błąd może się pojawić, więc wszystkie komórki potrzebują mieć „zabezpieczenie” w postaci JEŻELI.BŁĄD:

=JEŻELI.BŁĄD(WYSZUKAJ.PIONOWO(A2;'Zrealizowane zamowienia'!A:A;1;0)

Tak jak pisałem, WYSZUKAJ.PIONOWO jest pierwszym argumentem, a drugim będzie wartość wyświetlana zamiast błędu:

=JEŻELI.BŁĄD(WYSZUKAJ.PIONOWO(A2;'Zrealizowane zamowienia'!A:A;1;0);"nie zrealizowane")

Po przeciągnięciu formuły, widać że wszystkie błędy zostały zamienione, a tam gdzie błędu nie było, tam żadna zmiana nie zaszła.

Rysunek 28. JEŻELI.BŁĄD w formule.

WYSZUKAJ.PIONOWO i wyszukiwanie przybliżone

W poprzednich sekcjach, wspominałem nieraz, że ostatni (czwarty) jej argument to będzie standardowe 0 (czyli FAŁSZ). Teraz wyjaśnię, kiedy zamiast tego „standardowego” 0 zapisujemy coś innego.

 

Dwa tryby WYSZUKAJ.PIONOWO w Excelu

Cyfra „0” i „1” uruchamiają dwa różne tryby funkcji WYSZUKAJ.PIONOWO. Funkcja wciąż wyszukuje, ale nieco inaczej. Tryb aktywowany za pomocą „0” jest używany dużo częściej, dlatego do tej pory właśnie na nim się skupiałem. Tryb „1” będziemy traktować trochę jak wyjątek, aczkolwiek w określonych sytuacjach jest bardzo przydatny.

Możesz się spotkać (nawet w samym Excelu) z nazywaniem trybu „0” wyszukiwaniem „dokładnym”, a trybu „1” wyszukiwaniem „przybliżonym”. Nazwa „przybliżony” zazwyczaj zniechęca użytkowników Excela w ogóle do zapoznania się z nim bo przecież każdy z nas chce mieć „dokładne rezultaty”, a nie tylko „przybliżone”. Oczywiście ta nazwa, to pewne uproszczenie, które wyjaśniam poniżej.

 

Czym jest tryb „przybliżony”

Tryb „0”, czyli „dokładny” szuka wartości, którą podasz w argumencie nr 1 i jeśli nie znajdzie w tabeli dokładnie takiej samej wartości, to wyświetla błąd. Myślę, że działanie tego trybu jest jasne. W przypadku „1”, czyli „przybliżonego”, oczywiście nie ma sensu szukać np. faktury, która ma numer zbliżony do tego, którego potrzebuje, bo choć numery podobne, to będzie to kompletnie inna faktura. W takiej sytuacji, „przybliżone” szukanie rzeczywiście nie ma sensu. Więc kiedy ma? Poniżej przykład.

Rysunek 29. Tabela zamówień wraz z tabelką z progami rabatów.

Mamy listę zamówień od klientów, znamy kwoty tych zamówień. Przyznajemy klientom rabaty, w zależności od kwoty zamówienia (im wyższe zamówienie, tym większy rabat). System jest dość prosty, bo mamy po prostu kilka progów – jeśli zamówienie przekroczy dany próg, to automatycznie wskakuje odpowiedni rabat. Patrząc na załączoną tabelę, widzimy, że od 10.000 zł do 30.000 zł rabat wynosi 1%. Czyli nie ważne czy ktoś zamówi na 11.000 zł, czy na 13.500 zł, czy na 18.400 zł, to dostaje i tak 1% rabatu od tej kwoty. Więc żeby ustalić procentowy rabat, nie musimy wiedzieć dokładnie jakie jest zamówienie, ale wystarczy nam przybliżenie, czyli informacja między jakie progi wpada (między 10.000 a 30.000, a może miedzy 30.000, a 50.000, itd.).

 

Jaka jest zaleta trybu „przybliżonego”?

Dlaczego WYSZUKAJ.PIONOWO staje się w tym momencie przydatne? Jeśli chcielibyśmy użyć tej funkcji w trybie „0”, czyli „dokładnym”, to musielibyśmy w tabelce obok mieć przygotowane wszystkie możliwe kwoty zamówienia, a wiadomo, że każde zamówienie jest inne i ktoś może zamówić np. na kwotę 56.283,34 zł. Dużo prościej przygotować tabelę, w której są wymienione tylko progi (bo progów zazwyczaj nie ma wiele). Innymi słowy, gdy wyszukujemy za pomocą liczby i potrzebujemy jedynie dowiedzieć się do jakiego zakresu ona „wpada”, czyli między jakie progi, to wtedy przydaje nam się WYSZUKAJ.PIONOWO w trybie „1” („przybliżonym”). W innych przypadkach, użyjemy raczej trybu „dokładnego”.

 

Czy nie lepiej użyć funkcji JEŻELI?

Ktoś może zapytać „ale czemu w ogóle używać tej funkcji, nie mogę tego zrobić inaczej?”. Można to rozwiązać inaczej i zazwyczaj najprostszą alternatywą jest użycie funkcji JEŻELI. Pamiętaj jednak, że jeżeli różnych rabatów/progów masz np. 5, to trzeba by użyć czterech JEŻELI zagnieżdżonych w sobie. Jeśli tych progów byłoby 10, to trzeba użyć 9 JEŻELI (czyli zawsze o jeden mniej niż liczba progów), co jest już karkołomnym zadaniem. WYSZUKAJ.PIONOWO jest zawsze tak samo prosta, niezależnie od tego, ilu progów używasz.

 

Jak użyć trybu przybliżonego, żeby dobrze działał?

Używanie trybu „przybliżonego” wymaga zastosowania się do pewnych reguł:

    1. Szukasz za pomocą liczb, a nie tekstów (mogą to być także daty, bo Excel traktuje daty podobnie jak liczby).
    2. Nie interesuje Cie znalezienie dokładnie tej liczby, ale to, do jakiego przedziału wpada.
    3. Skoro interesują Cie przedziały, to musisz mieć przygotowaną tabelkę, gdzie wypisane są kolejne progi, a do tych progów przypisane są interesujące Cię wartości (w naszym przykładzie są to procentowe stawki rabatu).
    4. W tej pomocniczej tabelce, progi muszą być zapisane od najmniejszych do największych. Jeśli kolejność będzie inna, to funkcja w wielu przypadkach będzie zwracać błędne wyniki.
    5. WYSZUKAJ.PIONOWO analizuje progi w tabeli z góry na dół, aż znajdzie próg, który równa się szukanej wartości (co zdarza się oczywiście rzadko), albo próg większy od szukanej wartości. Gdy znajdzie pierwszy próg, który jest większy to cofa się w tabeli o jeden wiersz i wyświetla wartość do niego przypisaną. Można to opisać takimi słowami „szuka w tabeli wartości, która jest najbliższa tej, podanej w argumencie nr 1, ale szuka tylko spośród wartości mniejszych”, czyli jeśli szukasz liczby 79.999, a w tabeli jest 80.000, to chociaż tak blisko jest jej do 80.000, to jej nie wskaże, bo 80.000 jest większa od wartości szukanej (79.999).

Rysunek 30. Oto jak wyglądać będzie gotowa formuła w naszym przykładzie.

Dwa warunki w WYSZUKAJ.PIONOWO

Jeśli ktoś wspomina o wielu warunkach w tej funkcji WYSZUKAJ.PIONOWO, to zazwyczaj ma na myśli wiele wartości w jej pierwszym argumencie. Na przykład, chcę znaleźć jakąś osobę, ale nie mam do dyspozycji jej PESELu, ani innego identyfikatora, więc myśle sobie „poszukam według nazwiska”. No ale kilka osób może mieć takie samo nazwisko i nie wiem, czy znajdę wtedy właściwą osobę. Myślę sobie dalej „to może poszukam za pomocą połączenia imienia i nazwiska” – dobry plan, bo są już dużo mniejsze szanse, że będzie więcej osób o tym samym imieniu i nazwisku. Oczywiście mogę do tego dorzucić coś jeszcze, jak np. miejsce urodzenia, itd. Właśnie o takich wielokrotnych warunkach będę pisał poniżej.

Rysunek 31. Widać tu dodatkową kolumnę na początku przeszukiwanej tabeli oraz „podwójny” pierwszy argument w funkcji WYSZUKAJ.PIONOWO.

Rozwiązanie powyższego problemu jest dosyć proste. Funkcja WYSZUKAJ.PIONOWO nie pozwala na wstawienie wielu warunków, nie ma też innej funkcji w Excelu, która mogłaby to zrobić. Muszę więc moje wiele warunków połączyć w jeden duży. Będę więc w pierwszym argumencie wstawiał połączenie szukanych wartości (np. imienia i nazwiska). Żeby mogło to zadziałać, to w przeszukiwanej tabeli muszę mieć też kolumnę, w której imię i nazwisko są połączone. Tworzę więc taką kolumnę, łącząc te dwie wartości ze sobą i przeciągając ją w dół. Pamiętaj, żeby to była teraz pierwsza kolumna z lewej strony w Twojej tabeli. Możesz teraz wyszukiwać takiej połączonej wartości w kolumnie łączącej imiona i nazwiska.

Możesz w analogiczny sposób łączyć wiele wartości i wiele kolumn.

WYSZUKAJ.PIONOWO według fragmentu tekstu

W przykładach, które prezentowałem do tej pory, identyfikator, za pomocą którego szukaliśmy (pierwszy argument WYSZUKAJ.PIONOWO) był podawany zazwyczaj jako zaznaczenie komórki.Gdy szukaliśmy faktur, zaznaczaliśmy komórkę z numerem faktury, gdy szukaliśmy pracownika, to zaznaczaliśmy komórkę z ID pracownika, itd. Zdarzają się jednak sytuacje, gdy wartość, za pomocą której szukamy, otoczona jest w przeszukiwanej tabeli innymi danym. Zobaczmy to na przykładzie poniżej.

Rysunek 32. Tytuły przelewów zawierające nie tylko numer faktury.

Mamy w tabli listę faktur (ich numerów). W drugiej tabeli mamy listę przelewów, wraz z tytułami przelewów. Chcemy sprawdzić, czy kolejne faktury zostały opłacone, jednak tytuły przelewów, oprócz numery faktury zawierają dodatkowe wyrażenia, jak np. „płatność za fakturę…”, „faktura…”, itd. Żeby funkcja WYSZUKAJ.PIONOWO mogła odnaleźć odpowiednie przelewy, trzeba jej powiedzieć „szukaj numerów faktur wewnątrz tytułów przelewów, pamiętając że oprócz numeru faktury może tam być coś dopisane.

Taki problem rozwiązuje się za pomocą znaku specjalnego „*” (gwiazdka). Jest to ta sama gwiazdka za pomocą, które mnożymy liczby, ale jeśli występuje w cudzysłowie, to wtedy ma inną funkcję – oznacza ona dowolną ilość dowolnych znaków. Jeśli więc przewidujemy, że zarówno przed numerem faktury, jak i po nim, mogą znajdować się jakieś zapisy w tytule przelewu, to gwiazdkę należy wstawić zarówno z lewej jak i z prawej strony i połączyć je z numerem faktury:

"*" & numer_faktury & "*"

Rysunek 33. Jeśli, mimo wszystko, formuła nie może znaleźć faktury, to wyświetla po prostu błąd #N/D.

W powyższym szablonie wstawione zostały znaki „&” – one „łączą” gwiazdki z Twoim numerem faktury i są konieczne do zapisania w formule. W naszym przykładzie, ostateczna formuła będzie wyglądać następująco:

=WYSZUKAJ.PIONOWO("*"&D2&"*";$A:$B;2;0)

 

Powiązane wpisy poradnikowe