Minuty czytania: 4

WYSZUKAJ PIONOWO nie działa?

Próbowałeś wiele razy, a mimo wszystko nie wychodzi? Kolejne błędy? Straciłeś cierpliwość? Prawdopodobnie popełniasz jeden z 3 błędów, który powoduje, że WYSZUKAJ PIONOWO nie działa. Przeczytaj ten artykuł, dowiedz się jakie to błędy, jak je wyeliminować i raz na zawsze mieć tę funkcję „ogarniętą”.

wyszukaj pionowo nie działa

Dla przypomnienia…

WYSZUKAJ PIONOWO ma cztery argumenty:

  • szukana_wartość – to wspólny klucz, po którym łączysz dane z tabeli źródłowej i docelowej,
  • tabela_tablica – to tabela źródłowa, skąd „zaciągane” są dane,
  • nr_indeksu_kolumny – to numer kolumny w tabeli źródłowej (!), z której informacje chcemy „zaczytać”,
  • [przeszukiwany_zakres] – to wartość PRAWDA lub FAŁSZ, która mówi czy szukamy zbliżonego, czy dokładnego dopasowania.

Więcej na temat samej funkcji znajdziesz w tym artykule.

Błąd nr 1 – źle wskazany zakres danych źródłowych

Wskazując (zaznaczając) zakres danych źródłowych (2-gi argument) musimy zacząć od kolumny, w której funkcja „ma szansę znaleźć” nasz wspólny dla obu tabel klucz. Innymi słowy, pierwszą kolumną (patrząc od lewej strony) w naszym źródłowym zakresie danych powinna być kolumna, w której znajduje się szukana_wartość, czyli argument nr 1. Najłatwiej to zrozumieć na przykładzie.

Z Działu Szkoleń otrzymałem tabelę z ocenami okresowymi pracowników.

wyszukaj pionowo - tabela źródłowa

Mam swoją tabelę z wynagrodzeniami i stażem pracy.

wyszukaj pionowo - tabela docelowa

Teraz chcę do mojej tabeli – za pomocą funkcji WYSZUKAJ PIONOWO – zaczytać dane o ocenach poszczególnych pracowników. Muszę zatem wpisać odpowiednią formułę i przeciągnąć (skopiować) ją dla każdego wiersza,  w którym mam dane o pracowniku.

wyszukaj pionowo - przykładowa formuła

Jak widzisz, w drugim argumencie zaznaczony przeze mnie zakres ma adres $B$3:$D$32. Zatem w pierwszej kolumnie zakresu mam nr pracownika, bo tylko po numerze pracownika mam szansę zaczytać odpowiednie dane o ocenach.

Często popełniamy błąd zaznaczając zakres np. tak:

wyszukaj pionowo - błędnie zaznaczony zakres

mylnie uważając, że skoro szukamy ocen, to znajdują się one właśnie w tym zakresie. Owszem, ale WYSZUKAJ PIONOWO działa inaczej. Najpierw znajduje odpowiedni klucz dla obu tabel – w tym przypadku – nr pracownika, a następnie zwraca korespondującą do tego klucza wartość, w naszym przypadku ocenę.

Błąd nr 2 – niewłaściwy format danych

Spójrz na te dwa numery pracowników:

wyszukaj pionowo - różne formaty danych

Wyglądają na takie same? Otóż wyglądają, ale nie są 😉 Pierwszy z nich ma format liczbowy, a drugi tekstowy. To częsta „przypadłość” danych zrzucanych z różnego rodzaju systemów (np. SAP) Dane z programu mają format tekstowy, my mamy liczbowy. I klops…

WYSZUKAJ PIONOWO nie działa, właśnie z powodu różnego formatu naszego klucza w tabelach: źródłowej i docelowej. Funkcja zwraca wtedy błąd #N/D! (tzw. błąd wartości niedostępnej) Aby sobie z tym poradzić wystarczy zadbać o jednakowy format klucza w obu tabelach.

Można to zrobić co najmniej na dwa sposoby.

  1. Sposób „ręczny” – w tabeli, gdzie klucz jest wpisany jako wartości tekstowe – zamieniamy je na liczbowe w ten sposób.
  2. Sposób z formułą – wprowadzamy dodatkową formułę do naszej funkcji WYSZUKAJ PIONOWO:
  • WARTOŚĆ() – jeżeli chcemy zamienić nasze dane z tekstu na liczby
  • TEKST() – jeżeli chcemy zamienić nasze dane liczbowe na tekst (musimy pamiętać o parametrze „@” w 2-gim argumencie tej funkcji)

Błąd nr 3 – brak adresowania bezwzględnego

Umiejętność adresowania jest tym dla tworzenia formuł czym woda dla ryby – niby się rusza bez niej ale na pewno nie jest to pływanie. Sorry za tendencyjne metafory 😉

Wróćmy do naszego przykładu z ocenami pracowników. Jeżeli napiszę moją formułę tak:

wyszukaj pionowo - błąd adresowania względnego

To teoretycznie wszystko wygląda dobrze…aż do momentu kiedy nie skopiuję jej w dół i nie okaże się, że dla pracownika o numerze 00013 zamiast oceny pojawia się błąd. Dlaczego? Podświetlmy sobie formułę klikając F2.

wyszukaj pionowo - błąd adresowania względnego 2

Czy widzisz już w czym problem? Wraz z przenoszeniem formuły w dół, zmieniał się również zakres danych źródłowych, ponieważ nie zastosowaliśmy adresowania bezwzględnego (z dolarami) Skoro nasza tabela z ocenami ma stały zakres, to aby upewnić się, że zawsze będziemy się do niego odwoływać w naszej formule – musimy ten zakres zablokować – użyć adresowania bezwzględnego.

wyszukaj pionowo - adresowanie bezwzględne

Bezbłędna WYSZUKAJ PIONOWO

Wierzę, że czytając ten artykuł pokonałeś swoją ostatnią przeszkodę na drodze do bezbłędnego stosowania funkcji WYSZUKAJ PIONOWO. Pamiętaj o tych 3 zasadach, a jest duża szansa że od teraz będzie Ci szło znacznie lepiej:

  1. Zakres danych źródłowych (tabela_tablica) zawsze zaczyna się od kolumny, w której mamy wartość naszego klucza (najczęściej jakiś identyfikator, numer faktury czy PESEL)
  2. Upewnij się, że format danych Twojego klucza w tabelach: źródłowej i docelowej jest taki sam – albo wartości, albo tekst.
  3. Stosuj adresowanie bezwzględne, jeżeli wskazujesz zakres danych docelowych (tabela_tablica)

A może są inne problemy z WYSZUKAJ PIONOWO, z którymi masz problem? Jeżeli tak – koniecznie napisz w komentarzu. Może trzeba napisać kolejny artykuł 😉


Masz inne problemy z Excelem? Zapraszam Cię na grupę – Keep Calm And Use Excel na Facebooku. To miejsce, w którym pomagamy sobie nawzajem w codziennych wyzwaniach z Excelem 😉


28 Comments

adzik · 6 kwietnia 2020 at 09:22

Wyszukaj pionowo zwraca mi w całej tabeli wartość pierwszego wiersza. Czyli dostaję całą kolumnę danych identycznych jak w pierwszym wierszu.

    Łukasz Pietrzak · 6 kwietnia 2020 at 19:20

    Zmień trzeci argument funkcji, który odpowiada za numer kolumny z której dane mają być zwracane. Zapewne teraz masz tam 1.

    ewa · 23 września 2021 at 21:27

    U mnie pomogło ponowne zapisanie całego arkusza – chyba był jakiś problem z trybem zgodności bo wyskoczyło okienko. Wtedy wszystkie wartości się przeliczyły na prawidłowe

Wojtek · 2 lipca 2020 at 09:00

Cześć Łukasz, a ja mam inny problem – chcę wyszukać pionowo dane z tabeli przestawnej ale wartość w polu szukanym na taką ikonę minusa (zwiń/rozwiń). I jak ustawiam formułę to w oknie kreatora formuły jak wybiorę to pole to pokazuje mi właściwy wynik, ale jak zatwierdzę całą formułę to wyskakuje mi ND. Wszystkie formaty sprawdzone, wszystko jest liczbowe. Kluczem jest ten „minus” ponieważ inne pola w tej tabeli które nie mają dalszych szczegółów funkcjonują normalnie w „wyszukaj pionowo”. Wiesz może jak to rozwiązać?

    Łukasz Pietrzak · 2 lipca 2020 at 16:17

    Cześć Wojtek. Rozwiązania są co najmniej 2. #1 Najpierw rozwiń wszystkie dane w tabeli przestawnej. Odpowiednią opcję znajdziesz na wstążce ANALIZA – zwiń/rozwiń pole. Następnie w WYSZUKAJ.PIONOWO podaj (najlepiej wpisując ręcznie) zakres danych źródłowych. #2 Skopiuj dane z tabeli przestawnej do osobnego arkusza i wklej je tam jako wartości (tak, żeby nie powstała nowa tabela przestawna) Wtedy bez problemu skorzystasz z danych przy WYSZUKAJ.PIONOWO. Oba rozwiązania mają swoje wady. Generalnie przy podawaniu danych z tabeli przestawnej do źródła danych WYSZUKAJ.PIONOWO trzeba uważać, bo przecież tabele przestawne najczęściej tworzymy po to, aby stosować je DYNAMICZNIE, a nie STATYCZNIE. Mam nadzieję, że te wyjaśnienia jakoś Ci pomogły.

Wojtek · 3 lipca 2020 at 06:18

Cześć Łukasz. Dziękuję za odpowiedź. Rozwiązanie nr 2 odpada bo to „musi” być tabela przestawna, rozwiązanie nr 1 nie pomogło. Ale przez całkowity przypadek temat sam się rozwiązał – napiszę w czym rzecz, może się komuś kiedyś przyda. Chodzi o to że pole w przestawnej, to z minusem (zwiń/rozwiń), mimo że zawiera dane liczbowe, jest polem tekstowym, a baza zawierająca właśnie te dane do wyszukania jest cała w formatach liczbowych. Jak w bazie dodałem do tych pól co miałem przeszukiwać ` (tyldę) przed cyfrą a potem usunąłem tą tyldę to wszystko poszło z automatu i formuła zagrała koncertowo. Dziwne jest to że sama zmian formatu na tekstowy nic nie daje, trzeba wstawić tyldę a potem ją wykasować i komórka zmienia format na tekst.

Trzymaj się i jeszcze raz dzięki 🙂

Kamil · 7 września 2020 at 13:24

Cześć Łukasz,
mam identyczny problem jaki był poruszony w pierwszym komentarzu. Przy rozciągnięciu formuły w dół tabeli, w każdej komórce zwraca wartość z pierwszego wiersza. Wartość argumentu funkcji jest prawidłowa i powinna zwracać dane z kolumny, z której oczekuję że zostaną zwrócone.

    Łukasz Pietrzak · 8 września 2020 at 08:24

    Napisz proszę jak wygląda Twoja formuła

Marek · 10 listopada 2020 at 21:45

Cześć 🙂
Mój problem polega na tym, że funkcja wyszukaj.pionowo znajduje wartości w danym zakresie, ale są one błędne.
Plik wygląda następująco:
Zakres A:L => tablica
N:N => wypisane unikatowe wartości z kolumny H za pomocą makra + filtrowanie zaawansowane
O;O => wyszukaj.pionowo wartości sąsiedniej w kolumnie N w tablicy A:L
Podejrzewam, że jest to związane z wartościami unikalnymi, ale nie potrafię znaleźć słabego punktu.

    Łukasz Pietrzak · 11 listopada 2020 at 14:07

    Cześć. Poproszę o plik na [email protected]. Niestety patrząc na sam opis problemu, nie umiem pomóc.

Robert · 23 marca 2021 at 10:46

Powiem Tak, Dzięki tej uwadze udało się!!!!
U mnie był brak adresowania bezwzględnego. Taka prosta sprawa – a cała robota wywalona.
Dzięki za prosty – acz bardzo cenny artykuł.

    Łukasz Pietrzak · 23 marca 2021 at 16:23

    No i super! Po to piszemy artykuły i nagrywamy filmy 😉

Ola · 1 kwietnia 2021 at 10:06

A ja mam taki problem, że nie wyszukuje mi na liście wszystkich pozycji, które znajdują się w tabeli szukanej. Jedną znajdzie A przy innej pokazuje błąd. Format dla wszystkich jest ten sam

    Łukasz Pietrzak · 1 kwietnia 2021 at 19:47

    Bez danych ciężko powiedzieć w czym problem. Napisz proszę na [email protected]

Patryk · 20 lipca 2021 at 06:35

ja mam taki problem 😉
podczas używania funkcji Wyszukaj.pionowo odsyła mi nie zgodne dane z tabeli pobieranej
Arkusz 1
Wartość A1 – 82b0035 <– Nazwa artykułu
Wartość B1 – =Wyszukaj.pionowao(A1;Arkusz 2!A:B;2) <—- Wartość przy 82b0035 to Brake Disc
wynik – Pokazuje totalnie inna wartość która nie jest przypisana do wyszukiwanego Numeru/Tekstu

    Łukasz Pietrzak · 6 sierpnia 2021 at 15:32

    Cześć. Sprawdź czy przypadkiem, nie masz wcześniej w bazie do której się odwołujesz – takiej samej wartości 82b0035 z innym przypisaniem.

ewa · 23 września 2021 at 21:26

U mnie pomogło ponowne zapisanie całego arkusza – chyba był jakiś problem z trybem zgodności bo wyskoczyło okienko. Wtedy wszystkie wartości się przeliczyły na prawidłowe

    Kamila · 11 stycznia 2022 at 17:44

    Coś sie nie wczytało, więc jeszcze raz 🙃
    Cześć, również mam problem z tą funkcją. Jeśli dane z pola 'szukana wartość’ nie pokrywają się idealnie z danymi w 'tabela tablica’ funkcja zwraca błąd wartości niedostępnej. Dla przykladu: wpisujemy w pole 'szukana wartość’ kolumnę z liczbami od 1 do 1000 (posortowane od najmniejszej), a do 'tabela tablica’ kolumnę z liczbami od 1 do 1000, ale nie mamy usunięty wiersz z wartością 15. Funkcja zwraca błąd wartości niedostępnej na pozycji od 15 do 1000. Czy jest narzędzie, które umiałoby te brakujące pozycje „przeskakiwać”? Będę wdzięczna za pomoc, pozdrawiam 🙂

      Łukasz Pietrzak · 11 stycznia 2022 at 18:41

      Cześć. Kamila mam wrażenie, że używasz funkcji niepoprawnie wskazując w „szukana wartość” całą kolumnę, zamiast adresu pojedynczej komórki. Napisz proszę jak wygląda Twoja formuła – będzie łatwiej zidentyfikować potencjalny problem 🙂
      P.S. nie wiem w czym problem, ale dostaliśmy od Ciebie około 5 komentarzy o różnej, albo zbliżonej treści.

Kamila · 11 stycznia 2022 at 17:28

Cześć, mam podobny problem jak Ola. Zakres wskazywany w polu 'szukana wartosc’ ma więcej pozycji niż w 'tabela tablica’. Dane w obu kolumnach są posortowane od najmniejszej wartości. Od momentu gdy funkcja nie znajdzie w tablicy szukanej wartości zwraca już do końca błąd wartości niedostępnej. Czy da się usprawnić formułę, żeby ten błąd występował przy faktycznym braku pozycji w tabeli, a nie przy każdej pozycji poniżej? Dla zobrazowania podam przykład. Mam listę produktów , każdy ma unikalny numer, od 1 do 100. W drugiej tabeli mam zapotrzebowanie na poszczególne produkty z podanym unikalnym numerem. Chciałabym do pierwszej tabeli wstawić kolumnę i przy zastosowaniu funkcji przeciągnąć dane o ilości. Wpisuje w polu 'szukana wartość’ kolumnę z numerami od 1 do 100, a 'tabela tablica’ kolumnę z wybranymi numerami od 1 do 100, niech to będzie 1,2,3,40,80,100. Funkcja wyszukajpionowo od 4 wiersza, którego nie znalazła w tablicy, zwraca błąd również na pozycjach 40,80,100. W przypadku kilku pozycji, tak jak w przykładzie, można sobie ręcznie wszystko wpisać, ale w przypadku operowania na ogromnych bazach fajnie byłoby mieć jakieś narzędzie. Będę wdzięczna za pomoc, pozdrawiam 🙂

    Łukasz Pietrzak · 11 stycznia 2022 at 18:43

    Podaj proszę jak wygląda Twoja formuła.

Mateusz · 17 lutego 2022 at 14:11

Cześć, ja mam problem nieco innego rodzaju. Mam spreadsheet z ~11000 wierszy i napisałem dosyć skomplikowaną funkcję, która ma zwracać konkretną wartość na podstawie kilku kryteriów. Generalnie to ona działa tylko w kilku punktach zwraca N/D. Co ciekawe, gdy poszczególne funkcje zaszyte w funkcji jeżeli (min. wyszukaj) zwinę do wyszukiwanych wartości (klawisz F9) i w ten sposób zredukuję argumenty funkcji jeżeli do minimum (czyli np. (prawda, 7, N/D), to zaczyna działać i zwraca mi 7. Ale jeśli testu logicznego nie ogarnę po kolei klawiszem F9 to wyświetla mi fałsz.

Oto funkcja:
=JEŻELI(PORÓWNAJ(MODUŁ.LICZBY(MIN(MODUŁ.LICZBY(FILTRUJ(MODUŁ.LICZBY($G$5:$G$77);$D$5:$D$77=U7)-V7))-V7);X.WYSZUKAJ(MODUŁ.LICZBY(MIN(MODUŁ.LICZBY(FILTRUJ(MODUŁ.LICZBY($G$5:$G$77);$D$5:$D$77=U7)-V7))-V7);FILTRUJ(MODUŁ.LICZBY($G$5:$G$77);$D$5:$D$77=U7);FILTRUJ(MODUŁ.LICZBY($G$5:$G$77);$D$5:$D$77=U7);”FAŁSZ”;0;1));WYSZUKAJ.PIONOWO(U7&MODUŁ.LICZBY(MIN(MODUŁ.LICZBY(FILTRUJ(MODUŁ.LICZBY($G$5:$G$77);$D$5:$D$77=U7)-V7))-V7);WYBIERZ({1\2};$D$5:$D$77&MODUŁ.LICZBY($G$5:$G$77);$M$5:$M$77);2;0);WYSZUKAJ.PIONOWO(U7&MODUŁ.LICZBY(MIN(MODUŁ.LICZBY(FILTRUJ(MODUŁ.LICZBY($G$5:$G$77);$D$5:$D$77=U7)-V7))+V7);WYBIERZ({1\2};$D$5:$D$77&MODUŁ.LICZBY($G$5:$G$77);$M$5:$M$77);2;0))

    Łukasz Pietrzak · 19 lutego 2022 at 11:28

    Nooo Mateusz… 🙂 Szacun za tę formułę ale bez danych źródłowych i wyjaśnienia, co chciałeś osiągnąć – będzie mi strasznie ciężko Ci pomóc. Moja rekomendacja (i wydaje mi się dobra praktyka) jest taka, żeby zamiast jednej wielostopniowej formuły o takim poziomie złożoności – rozdzielać działania na kilka etapów / obliczeń. Ryzyko błędu w przypadku takich złożonych formuł rośnie wg moich doświadczeń wykładniczo.

      Mateusz · 10 marca 2022 at 13:04

      Dziękuję, trochę czasu nad nią spędziłem 😀

      Niestety, danych źródłowych podać nie mogę – poufne. Generalnie zamysł jest taki – mam punkt A. Dla tego punktu mam kilka powiązanych wartości czyli np. 7-1-1; 7-2-1, 7-3-3 itd., załóżmy format A-X-Y. I teraz tak – w jednej tabeli dla punktu 7 wyszukuję wartość minimalną spośród Y. I ona sobie tam czeka. W drugiej tabeli mam znowu A-Z. Ale tutaj haczyk, Z i X powinny się pokrywać, ale nie muszą – i w tym przypadku wyszukuję najbardziej zbliżoną wartość X dla punktu A i dla kombinacji A-X chcę, aby funkcja zwracała Y. Mam nadzieję, że zrozumiałe 😀

      Nieco zmodyfikowałem formułę i używam funkcji X.Wyszukaj i to problem rozwiązało dla wielu arkuszy ale znowu zaczęło mi się wysypywać. I właśnie funkcja X.wyszukaj mi wypluwa fałsz:
      =X.WYSZUKAJ(AC4811;FILTRUJ($G$5:$G$214;$D$5:$D$214=U4811);FILTRUJ($G$5:$G$214;$D$5:$D$214=U4811);”FAŁSZ”), gdzie w komórce AC4811 jest funkcja:
      =MODUŁ.LICZBY(MIN(MODUŁ.LICZBY(FILTRUJ($G$5:$G$214;$D$5:$D$214=U4811)-V4811))-V4811)

      Jeśli w x.wyszukaj zaznaczę komórkę AC i kliknę F9 zamieniając odwołanie na wartość to wypluwa prawdę, tak to wypluwa fałsz… Zmiana wartości w komórkach na Liczbowe i redukcja cyfr po przecinku nic nie daje… Ileś tysięcy wierszy działa i zaczyna się wysrywać 🙁

        Mateusz · 10 marca 2022 at 14:04

        Jak coś – ja problem obszedłem bo dane mi się powtarzają więc na początku korzystam z ww. funkcji JEŻELI, potem już lecę inaczej jednak chciałbym, żeby to działało bez kombinacji bo a nóż coś się może zmienić w „międzyczasie”, a dziesiątek tysięcy wierszy ręcznie sprawdzał nie będę….

        Łukasz Pietrzak · 11 marca 2022 at 20:30

        Prześlij proszę ten opisywany przez Ciebie przypadek w Excelu dla fikcyjnych danych na adres [email protected]. Może coś wspólnie wymyślimy 😉

Ania · 29 listopada 2022 at 18:36

Po jednoczesnym otwarciu pliku z formułą wyszukaj pionowo oraz drugiego pliku z danymi zaciąganymi plik z formułą się rozsypuje pokazując błąd adr

    Łukasz Pietrzak · 1 grudnia 2022 at 15:11

    Prześlij proszę screena lub pliki na adres [email protected]. Sprawdzimy w czym problem, ale prawdopodobnie to błędne adresowanie.

Dodaj komentarz

Avatar placeholder

Twój adres e-mail nie zostanie opublikowany. Wymagane pola są oznaczone *