Funkcją, która sprawia wiele kłopotów początkujacym użytkownikom Excela, a która po jej opanowaniu okazuje się być jednym z większych udogodnień przy pracy z dużą ilością danych, jest WYSZUKAJ.PIONOWO (VLOOKUP).
Dlaczego najpierw jej nienawidzimy, a później nie możemy bez niej żyć?
W zrozumienie mechanizmu tej funkcji trzeba włożyć na początku trochę wysiłku – jej składnia to cztery istotne elementy, z których każdy wymaga poprawnego i przemyślanego wprowadzenia. Co więcej, funkcja ma kilka pułapek, o omijaniu których musimy pamiętać, jeśli chcemy, aby formuła działała prawidłowo. Łatwo zatem, szczególnie na początku, o pomyłki, po których pojawi się zapewne znienawidzony komunikat #N/A, potrafiący skutecznie zniechęcić do stosowania tej funkcji.
Jeśli jednak poświęcimy trochę czasu na opanowanie mechanizmów WYSZUKAJ.PIONOWO (najłatwiej oczywiście robić to „na mokro”, testując nasze umiejętności w praktyce), odwdzięczy się nam ona najpiękniej, jak potrafi, czyli robiąc za nas pracę, która normalnie zajęłaby nam mnóstwo czasu.
Do czego można wykorzystać funkcję WYSZUKAJ.PIONOWO?
Przede wszystkim WYSZUKAJ.PIONOWO służy, jak sama nazwa wskazuje, do wyszukiwania danych w zbiorach rekordów. Może też pomóc w kategoryzowaniu rekordów w tabelach z dużą ilością danych, czy też w nadawaniu im atrybutów na podstawie stworzonych wcześniej słowników, a także w przenoszeniu danych z jednego arkusza do drugiego. W połączeniu z innymi funkcjami, można stworzyć mechanizmy, które pomogą nam w opanowaniu danych spływających z różnych źródeł. Jest to szczególnie istotne dla osób, które pracują na dużych zbiorach informacji z wielu systemów.
Czy WYSZUKAJ.PIONOWO stosują też zaawansowani użytkownicy?
I tak, i nie. Na szybko bardzo często wstawia się ją do raportów, jednak większość zaawansowanych użytkowników przeskoczyła ze standardowych formuł Excel (w tym WYSZUKAJ.PIONOWO) na analogiczne polecenia w dodatku Power Query. Jego zaletą jest automatyzacja raportowania wraz z dodawaniem kolejnych danych. Odpowiednikiem WYSZUKAJ.PIONOWO w Power Query jest Scalanie (Merge).
Jak zatem działa funkcja WYSZUKAJ.PIONOWO?
Przyjrzyjmy się na początek składni funkcji.
Pierwszym argumentem jest szukana wartość, czyli to, co funkcja ma odszukać. Następnie wskazujemy zakres, czyli tabela/tablica, w którym wyszukiwanie ma się odbyć. Kolejnym argumentem jest numer kolumny z wskazanego przez nas zakresu, w którym znajduje się pożądana przez nas informacja. Jako ostatni argument podajemy interesujący nas sposób wyszukiwania, czyli to, czy funkcja ma podać nam dokładny, czy przybliżony wynik (przeszukiwany zakres). Oto, jaki wynik daje nam formuła z obrazka: 
O czym należy pamiętać, żeby funkcja zadziałała prawidłowo?
Szukana wartość – musi być w takim samym formacie, jaki wystąpi później w zakresie, do którego odwołujemy się w kolejnym argumencie funkcji. Nie możemy więc wyszukać liczby przechowywanej jako tekst w tabeli, gdzie dana liczba będzie już przechowywana jako liczba. Należy więc zamienić tekst na liczbę.
Porada: w bardziej skomplikowanych przypadkach, na przykład tam, gdzie wyszukiwana wartość będzie w postaci formuły i WYSZUKAJ.PIONOWO odmówi współpracy, zazwyczaj pomoże sztuczka z mnożeniem przez 1. W wolnej komórce wpisujemy po prostu formułę ‘=[komórka z wyszukiwaną wartością]*1’ i do wyszukiwania użyjemy właśnie tej nowej, „oszukanej” komórki.
Tabela, czyli nasz zakres wyszukiwania – musi rozpoczynać się kolumną, w której znajduje się nasza wyszukiwana wartość. Dodatkowo, jeśli będziemy potem kopiować WYSZUKAJ.PIONOWO do kolejnych komórek, warto zablokować zakres wyszukiwania dodając znaki $ w adresie zakresu, inaczej zakres będzie wędrował i w efekcie funkcja zadziała prawidłowo jedynie w pierwszej komórce.
Numer kolumny – to chyba najprostszy argument, należy tylko dobrze policzyć kolumny w zakresie wyszukiwania. Warto wiedzieć, że ten argument może również wskazywać na komórkę, która podaje numer kolumny. Przydaje się to w bardziej skomplikowanych konstrukcjach z wykorzystaniem WYSZUKAJ.PIONOWO.
Sposób wyszukiwania – WYSZUKAJ.PIONOWO daje tutaj dwie możliwości: wyszukiwanie dokładne (FALSE bądź 0) i wyszukiwanie przybliżone (TRUE bądź 1). Należy pamiętać, że ten argument nie jest wymagany, co więcej, jeśli go nie podamy, to funkcja zwróci nam przybliżony wynik. Jeśli zatem zależy nam na dokładnym wyszukiwaniu, musimy pamiętać, żeby wpisać tutaj FALSE (FAŁSZ) bądź 0. Druga opcja, czyli wyszukiwanie przybliżone, ma w mojej opinii mniej zastosowań, należy jednak pamiętać, że wymaga, aby zakres wyszukiwania był przesortowany rosnąco. Wówczas, jak na poniższym przykładzie, możemy znaleźć najbliższe dopasowanie, jeżeli szukana liczba nie występuje w tabeli. Przykład z kodami pocztowymi został rozwinięty we wpisie o aktualizacji bazy kodów pocztowych w programie Mapa Polski Excel.
Mając w głowie powyższe wskazówki, możemy zacząć ćwiczyć stosowanie funkcji WYSZUKAJ.PIONOWO w praktyce.
Przykłady stosowania funkcji WYSZUKAJ.PIONOWO
Wyobraźmy sobie, że nasza firma współpracuje z siedmioma hurtowniami, które na koniec miesiąca wysyłają nam swój raport sprzedaży naszych produktów. Dane te są interesujące z wielu względów, pozwalają między innymi śledzić dynamikę sprzedaży na rynku hurtowym, a także oszacować stany poszczególnych artykułów w poszczególnych hurtowniach. Szybka ich konsolidacja pozwoli więc na bieżące analizowanie trendów. Problem w tym, że każda hurtownia ma swój system i swoje nazwy naszych produktów, otrzymujemy więc siedem arkuszy .xls lub .csv, z których każdy jest „w innym języku”.
Jak sobie z tym poradzić? Zakładając, że hurtownie nie zmieniają narzędzi do raportowania czy też systemów księgowych zbyt często, wystarczy przygotować sobie wcześniej mapowanie, czyli słownik w formie tabeli, który przetłumaczy nam nazwy z poszczególnych raportów na kodowanie, które obowiązuje w naszej firmie.
Tak przygotowani, możemy za pomocą WYSZUKAJ.PIONOWO przypisać właściwą nazwę do poszczególnych rekordów z raportu hurtowni. W kolejnych kolumnach możemy przypisać za pomocą tej samej funkcji kolejne atrybuty do produktów, na przykład markę, do której dany artykuł należy, czy też jego cenę, co z kolei pozwala na dalsze obliczenia i analizy. Możliwości ograniczone są jedynie przez nasze potrzeby i wyobraźnię.
Zastosowanie funkcji WYSZUKAJ.PIONOWO i słownika pozwala na sprawne przystosowanie danych ze wszystkich siedmiu arkuszy i skonsolidowanie ich w zbiorczą tabelę. Otrzymujemy w ten sposób jednolity zbiór danych, gotowy do tworzenia raportów, pivotów, dashboardów i wykresów (o tym, jak w inny sposób połączyć dane z różnych źródeł, piszemy też w artykule o tabelach i wykresach przestawnych).
WYSZUKAJ.PIONOWO jest zatem bardzo przydatnym narzędziem, a sprawne posługiwanie się nim znacznie ułatwia pracę z dużą ilością danych.
-1024x269.png)
jestem na tym etapie że kilka razy skorzystałem z tej formuły, ale muszę ją zrozumieć bardziej, bo robiłem arkusz z formułami i wyczerpałem możliwości z funkcją Jeżeli, skoro mogę to co potrzebuję zrobić z wyszukaj pionowo, nauczę się tego. Pozdrawiam.
Czym to się różni od Filtrowania dostępnego z pozycji wstążki?
Funkcją WYSZUKAJ.PIONOWO dodaje się dane z innej tabeli, a filtrowanie działa w ramach jednej tabeli.
Pytanie za 100 pkt. jeżeli mamy tabelę z numerami rachunków bankowych (26 cyfr) jak zastosować WYSZUKAJ.PIONOWO w takim przypadku?
Proponuję zamienić format komórki na liczbowy lub tekstowy, żeby nie działać na domyślnie wybieranym wówczas formacie naukowym.
Niestety te opcje zawodzą, jak sami pisałeś WYSZUKAJ.PIONOWO nie działa na liczbach przechowywanych jako tekst, natomiast w formacie liczbowym nie można przechować tak dużej liczby (29 cyfr)
To proponuję zamienić wszystko na tekst (np. poprzedzić każdy wpis apostrofem ’) i łączyć po polach tekstowych, na których WYSZUKAJ.PIONOWO też działa.
Witam mam taki kłopot – lista rozwijana i w kolumnie obok wyszukaj.pionowo ma wyrzucać wartości z tabeli. problem jest taki że po wyborze pokazuje błąd ale wystarczy że kliknę w pasek formuły w dam enter to wyskakuje poprawna wartość. potem zmieniam pozycję z listy rozwijanej i wartość się nie aktualizuje, ale znów klikam w formułę, enter i wyskakuje poprawna. Co jest nie tak? Z góry dzięki.
Sprawdziłbym w pierwszej kolejności, czy arkusz nie ma ręcznego odświeżania: Formuły > Opcje obliczania > Automatyczne
Witam,
Nurtuje mnie pewna trudność. Otóż „nazwa”, która została zdefiniowana przy użyciu funkcji WYSZUKAJ.PIONOWO (lub ADR.POSR) działa świetnie w komórkach jednak nie ma możliwości użycia jej w wykresie np. jako nazwy serii. Może ktoś się już borykał z tym problemem? Niestety w poszukiwaniu rozwiązania dotarłem już chyba na krańce internetów 🙂
Aby użyć nazwy zdefiniowanej w wykresie należy posłużyć się nazwą zdefiniowaną oraz nazwą pliku, np. raport.xlsx!nazwa.
A co w przypadku jeśli chcieli byśmy powielić tablicę, żeby wartość która jest poza tablicą opisywana jako N/A była zamieniana na pierwszą wartość z tablicy i tak w kółko???
Jeśli dobrze rozumiem, chodzi o automatyczne uzupełnianie kolumny z kluczem (ID), żeby nie generowały się błędy. Prawdopodobnie jest to możliwe za pomocą makr lub z wykorzystaniem Power Query. Proszę opisać dokładniej, jak miałoby to działać.
zakres bazowy komórek A1:A5, w każdej jakiś tekst np aaa, bbb, ccc…..
wybieram sobie teraz inny zakres np D6:D14 i chciałbym aby w tym zakresie kolejno ustawiały się pozycje z zakresu bazowego
wyglądało by to tak
aaa
bbb
ccc
ddd
eee
aaa
bbb
ccc
jeśli użyje funkcji transpozycja ustawi mi zakres tylko od aaa do eee reszta w dół to błędy.
Chciał bym to zrobić przy użyciu wyłącznie excela
Wydaje mi się, że formułami będzie trudno do tego dojść. Kombinowałbym, aby zakres tabeli był powiązany z numerem wiersza lub wykonać to ręcznie, wkleić wartości i przeciągnąć w dół za pomocą polecenia Narzędzia główne > Wypełnij > Seria danych.
Dziękuję za jasny opis bardzo przydatnej funkcji. Bardzo użyteczna w przypadku zestawień materiałów, listy linii itp. tabel z których czasem trzeba wyłuskać potrzebne dane.
Witam,
co zrobić w sytuacji gdy przeszukiwany zakres jest zbyt duży? Pojawia mi się komunikat, że pliki w tej wersji mogą zawierać tylko formuły, które odwołują się do komórek znajdujących się w arkuszach zawierających do 256 kolumn lub do 65 536 wierszy.
Oznacza to, że plik źródłowy lub przeszukiwany jest zapisany w wersji Excela 1997-2003 z rozszerzeniem .xls. Zanim wstawimy WYSZUKAJ.PIONOWO, lepiej będzie zapisać oba pliki jako Excel z rozszerzeniem .xlsx.
Witam,
Buduję dosyć prosty raport jednak mam problem z importem danych. Nie wiem czy moje pytanie dodaję pod właściwym tematem ale ten wydaje mi się odpowiedni.
Mianowicie…
W pierwszej zakładce mam kolumnę user ID ( C3 ) i Imię,Nazwisko ( D3 ). W drugiej zakładce mam wszystkie dane czyli już wpisane user ID ( A2 ) i do każdego user ID mam dopisane imię i nazwisko ( w osobnej kolumnie B2 ). Czyli taka ”baza danych”
Teraz tak. W pierwszej zakładce ( C3 ), scanuję barcode z user ID, więc w komórce pokaże mi się ten user iD np. 1112. i teraz, chciałbym aby automatycznie, Excel pobrał mi imię i nazwisko z drugiej zakładki które zostało przypisane do danego user ID.
Tych user ID będzie więcej i każdy będzie miał przypisane swoje imię.
Niewiem jak to zrobić, próbowałem już z vlookup ale nie daję rady tego dostosować tak aby działało.
Bardzo proszę o pomoc.
Pozdrawiam
Darek
sorki, podałem zły e-mail:)
Odpowiedź wysłałem.
Witam,
Niestety u mnie zwraca błędy od pewnego momentu. Jest to już ciag błędów do końca tabeli. Błąd pojawia się w momencie kiedy faktycznie nie może znaleźć określonej wartości (bo jej brakuje), ale poniżej są kolejne pozycje do których funkcja powinna zwrócić wartość, ale niestety poniżej wszystko jest już błędami.
Pozdrawiam, Kasia
Hej Kasia, zawsze możesz podesłać do mnie plik na kontakt@skuteczneraporty.pl. Zerknij też do bezpłatnego kursu https://skuteczneraporty.pl/13-godzinny-kurs-excel-za-darmo/, gdzie lekcja WYSZUKAJ.PIONOWO w ramach kursu Formuły i operacje na danych ma swoją oddzielną lekcję z przykładami.
Cześć, mam pytanie, jak użyć wyszukaj.pionowo (ewentualnie x.wyszukaj) między dwoma plikami w chmurze (OneDrive/Sharepoint)?
Jeżeli się nie da, jak inaczej sprostać potrzebie?
Hej Michał, proponuję otworzyć 1. plik przez przeglądarkę i kliknąć: Edytuj w aplikacji Desktopowej. To samo zrobić dla 2. pliku. Zbudować WYSZUKAJ.PIONOWO standardowo i pliki złapią pełną ścieżkę OneDrive. U mnie było to: 'https://…-my.sharepoint.com/personal/…/Documents/…
Cześć,
z tym sharepoint jest możliwość połączenia plików za pomocą wyszukaj.pionowo natomiast ja mam z tym taki problem, że u mnie działa to tylko i wyłącznie gdy mam oba pliki odpalone,
jak zamykam ten z ktorego pobiera dane to przestanie odnajdywać lokalizację i wyskakuje ND.
Rzeczywiście, trzeba otworzyć oba pliki. Obejściem może być Scalanie w Power Query. Ostatnio prowadziłem o scalaniu Webinar: https://excelbi.pl/excelowa-funkcja-wyszukaj-pionowo-vs-scalanie-w-power-query-wideo-z-webinaru-24-11-2021/
A tu, jak podłączyć się do OneDrive: https://excelbi.pl/jak-pobrac-do-power-query-dane-z-onedrive-sharepoint-i-forms/