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ą”.

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.

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

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.

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:

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:

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:

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.

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.

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