fbpx

Jak przyporządkować dane (WYSZUKAJ.PIONOWO)?

09.08.2016 | ECP2, Księgowość

Częstym pytaniem, jakie mi zadajecie w mailach, na szkoleniach jest: Jak zrobić, aby na podstawie określonych danych znaleźć inne dane? Albo inna wersja: Co zrobić, gdy mam tabelę z danymi i na ich podstawie, chcę do niej dokleić dane z innej tabeli?

Żeby lepiej zobrazować o co chodzi, weźmy przykład: wybrałam kilku pracowników (imię i nazwisko) i chcę przyporządkować do nich datę zatrudnienia. Potrzebne dane mam w innej tabeli. Dla uproszczenia formuły – wszystkie dane będą w tym samym arkuszu. Oto formatka:

Formatka

Formatka

Chodzi o to, by w pierwszej tabelce, na podstawie imienia i nazwiska pracownika, przyporządkować datę zatrudnienia. Wszystkie potrzebne dane są w drugiej tabelce i to właśnie z niej będziemy je pobierać. Weźmy np. Ninę Dębicką. Aby dopasować jej datę zatrudnienia, najpierw odnajdujemy ją w drugiej tabeli. Kiedy już ją znajdziemy – wyświetlamy datę zatrudnienia z drugiej kolumny przeszukiwanej tabeli (nie interesuje nas, gdzie ta tabela jest w arkuszu). Chcemy odnaleźć dokładnie Ninę Dębicką, a nie kogoś innego.

Wyróżnione słowa w akapicie powyżej to 4 argumenty funkcji WYSZUKAJ.PIONOWO (w wersji angielskiej: VLOOKUP), którą użyjemy do rozwiązania tego zadania. Da się to zrobić na wiele innych sposobów, jednak to jest zdecydowanie najpopularniejsze.

Trochę teorii…

Funkcja WYSZUKAJ.PIONOWO posiada 4 argumenty:

  1. szukana wartość, na podstawie której chcemy przyporządkować dane(w przykładzie: imię i nazwisko),
  2. zakres/tabela, w której będziemy szukać (w przykładzie: $D$3:$E$8),
  3. nr kolumny, z której wartość chcemy wyświetlić, jak już znajdziemy szukaną wartość (w przykładzie: 2),
  4. sposób wyszukiwania: jeśli szukamy dokładnie, tak jak w przykładzie, to tutaj wpisujemy zero (0).

Ostatni argument zazwyczaj budzi dużo kontrowersji, często użytkownicy wiedzą, żeby tam wpisać zero, ale nie wiedzą dlaczego. Prezentowany przykład nie jest idealny do wytłumaczenia tego zagadnienia, więc napiszę jeszcze artykuł o innym zastosowaniu WYSZUKAJ.PIONOWO, takim, gdy wpisujemy 1 (albo nic) w ostatnim argumencie tej funkcji. Mam nadzieję, że wtedy wątpliwości się rozjaśnią.

Co jeszcze koniecznie należy pamiętać o tej funkcji to to, że zakres, który przeszukujemy (drugi argument), zawsze powinien zawierać szukaną informację (u nas: pracownika) w pierwszej kolumnie. W przykładzie tak właśnie jest, więc wszystko pięknie działa. Gdyby jednak zamienić kolejność kolumn przeszukiwanego zakresu (najpierw data zatrudnienia, a potem pracownik) – funkcja zwróciłaby błąd (#N/D).

Odnośnie przeszukiwanego zakresu (u nas to druga tabelka), warto też pamiętać, że nie ma znaczenia, gdzie w arkuszu on się znajduje. Może być w dowolnym miejscu. Trzeci argument – nr kolumny – zawsze musi odnosić się tylko do zakresu, a nie do kolumny arkusza. Zobaczcie, u nas w przykładzie przeszukiwany zakres jest w kolumnach D i E, ale kolumna, którą wyświetlam ma numer 2, ponieważ ten numer jest numerem kolumn zakresu, a nie arkusza. Nasz zakres ma 2 kolumny: 1- Pracownik, 2 – Data zatrudnienia. Zobaczcie na rysunku:

Kolumny zakresu

Funkcja

Ok. To po tych obszernych wyjaśnieniach przejdę do samej funkcji. Należy ją wpisać w komórce B3 i skopiować w dół (aby zachować formatowanie polecam sposób, który opisałam tutaj). Sama funkcja wygląda tak:

=WYSZUKAJ.PIONOWO(A3;$D$3:$E$8;2;0)

Dla ułatwienia w kreatorze:

Kreator WYSZUKAJ.PIONOWO

Kreator WYSZUKAJ.PIONOWO

I tak, jak wcześniej opisywałam, argumenty po kolei to:

  1. najpierw szukamy A3 – Niny Dębickiej
  2. gdzie szukamy –  w zakresie $D$3:$E$8 (zablokowanym, żeby został takki  sam dla każdej osoby)
  3. chcemy  zwrócić/wyświetlić wartość w drugiej kolumnie (2) dla szukanej osoby
  4. szukamy dokładnie – tutaj wpisujemy 0 (zero).

Wynik jest taki:

Wynik

Wynik

Jeszcze plik do pobrania oczywiście:
Plik do pobrania:
MalinowyExcel_Jak porównać dane dw.xlsx

 

I film na YouToube’ie:

Powiązane produkty:

  • Webinar WYSZUKAJ.PIONOWO, podczas którego dokładnie omawiam zasadę działania funkcji, o której piszę w tym wpisie. Dodatkowo, oprócz zastosowania dokładnego, omawiam też zastosowanie przybliżone, które można świetnie wykorzystać np. do przyporządkowywania klas wynagrodzenia pracownikom. Tym samym funkcja ta świetnie nadaje się jako alternatywa dla wielokrotnie zagnieżdżonego JEŻELI.

 

Jeżeli chcesz lepiej poznać Excela na poziomie średniozaawansowanym zapisz się na listę zainteresowanych kursem Excel w codziennej pracy cz. 2! Podczas oczekiwania na kurs będę Ci wysyłała informacje, ciekawostki i excelowe triki. Bezpłatnie! 

Pomogłam Ci? Postaw mi wirtualną kawę! →

Przeczytaj podobne wpisy

Kategorie

14 komentarzy

  1. dzień dobry poproszę do tego tematu plik video.Brak jest dziękuje uprzejmię

    Reply
  2. Witam 🙂 mam taka prośbę o podpowiedz jaka funkcje najlepiej zastosować… jeśli chce wyszukać dane z innej tabeli, ale.. wyszukiwane dane w drugiej tabeli nie są “unikalne” tzn
    chce wyszukać jaka jest wartość netto faktury o numerze FS5251 z tym, że w tabeli która przeszukuję mam trzy faktury o numerze FS5251 z tym, ze każda z innej daty lub dla innego kontrahenta i oczywiście z inna wartością netto…
    dziękuję i pozdrawiam 🙂

    Reply
    • Aga,
      a podaj proszę przykład co chcesz uzyskać. Najlepiej jak podeślesz pliczek na maila. Podaj proszę taki przykład: co Ty byś zrobiła w sytuacji, gdy te faktury się powtarzają? Jaką wartość byś chciała uzyskać?
      Pozdrawiam
      Malina

      Reply
      • Ja też mam pewne pytanie co do odpowiedniej formuły proszę o kontakt na maila

        Reply
      • Cześć Malina, a co wtedy gdy po zastosowaniu funkcji wyszukaj pionowo pojawią się wartość #N/D! , co to znaczy ?

        Reply
        • Hej! Przyczyn tego może być bardzo dużo – zaczynając od tego, że wartości po prostu nie ma, poprzez niezablokowanie zakresu przeszukiwanego, do różnych typów danych. Jesteś może na moim mailingu? Tam opisuję dlaczego W.P może nie działać 🙂

          Reply
  3. Dzień dobry,

    A czy można korzystać z tej reguły dla zestawu danych na zasadzie dla kolumn A, B w jednej tabeli dokleić E z innej tabeli, gdzie mamy C, D, E jeśli (A, B) = (C, D)?

    Czyli np
    (A) = Nina, (B) = Dębicka <- pierwsza tabela. (C) = Nina, (D) Dębicka, (E) = 2015-04-28 <- druga tabela

    i chcemy dla zestawu (A, B) porównać wystąpienia zestawu danych w (C, D) drugiej tabeli i dokleić do pierwszej kolumnę E z drugiej, jeśli nastąpiło dopasowanie.

    Reply
    • Hmmm, można tylko trzeba zrobić z tego unikalny klucz np. w kolumnie pomocniczej (musi być w obu tabelach). Tak będzie najprościej. Czyli w sumie wracamy do punktu wyjścia 😉
      Można oczywiście innymi sposobami, np. Power Query i ustawić, żeby dopasowanie było wg 2 kolumn, a nie jednej. WYSZUKAJ.PIONOWO niestety szuka wg jednej wartości, która dodatkowo musi być w pierwsze kolumnie. W PQ nie ma tego ograniczenia…

      Reply
  4. Witam, dziękuję za informacje, a moje pytanie jest takie. kolumnę z numerami rejestracyjnymi dopasowuję do nazwisk kierowców. Funkcja działa i jest super, ale tylko w wierszu pierwszym , w którym wpisywałam funkcję. Po przeciągnięciu jej w dół nazwisko jednego kierowcy przeciągnęło się do wszystkich wierszy. W zaznaczonym obszarze są wszystkie nazwiska, a mimo to nie pojawiły się przy odpowiednich rejestracjach. Nie wiem jaki popełniam błąd.

    Reply
    • Hej! Zobacz czy masz zablokowany dolarami przeszukiwany zakres. Jeśli masz – bardzo dobrze. Jeśli nadal nie działa – sprawdź, czy nazwisko kierowcy jest odwołaniem do komórki (a nie np. ręcznie wpisane). Powinno pomóc 🙂

      Reply
  5. Witam chcę z ciągu tekstu z różnymi wartościami tj. tekst i liczby, znajdującego się wszystko w jednej kolumnie wyszukać po nazwie np. “wiadro” “młotek” “siekiera” jak i inne przedmioty (powtarzające się ale z innymi cenami) ale chcę wyszukać tylko po nazwach by przenieść przedmioty o danej nazwie do innej kolumny bądź arkusza pozdrawiam

    Reply
    • Hej, zależy jak to dokładnie wygląda. Może wystarczy zastosować *, może warto rozdzielić te teksty do innych kolumn?

      Reply
  6. Witaj. Szukałem, bo potrzebowałem, jak zamienić wiersz na kolumnę i odwrotnie i…napotkałem gaik Maliny. To czego w tym gaiku Maliny dowiedziałem się na poszukiwany temat z miejsca, od razu sprawdziłem i tam gdzie trzeba było to co trzeba, to czego się dowiedziałem w pełni potwierdziło się. Coś mi się wydaje, że znajdę tu pomoc w wielu jeszcze innych sprawach. Na razie dzięki, dzięki, dzięki. Jak coś jest super to określa się to krótko: Malina. I ta strona to po prostu, Malina.

    Reply
    • Ooooo, jak mi miło ♥♥♥
      I cudne określenie GAIK MALINY 🙂 Zakochałam się!!! 🙂
      Bardzo się cieszę, że mogłam pomóc i mam nadzieję, że w przyszłości tez pomogę 🙂

      Reply

Submit a Comment

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