fbpx
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 😉



5 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.

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 🙂

Dodaj komentarz

Twój adres email nie zostanie opublikowany. Pola, których wypełnienie jest wymagane, są oznaczone symbolem *