fbpx

Uzupełnianie danych na podstawie wyboru z listy rozwijanej

03.06.2019 | ECP2, HR, Triki

Czyli bajeranckie zastosowanie WYSZUKAJ.PIONOWO

Na podstawie wyboru z listy rozwijanej do formatki mają się wpisać określone dane. Jedni użyją tego mechanizmu do pomocy przy tworzeniu świadectw pracy, inni do pracy z umowami, jeszcze inni – do tworzenia ofert dla klientów.

Najlepsze jest to, że niezależnie od zastosowania – potrzebujemy tego samego mechanizmu, aby osiągnąć ten sam efekt:

A tym mechanizmem jest nic innego, jak ukochana przez wszystkich (no… prawie wszystkich) funkcja WYSZUKAJ.PIONOWO! I o niej dzisiaj 🙂

Formatka

Mechanizm tworzenia takiego cuda omówię na danych potrzebnych do stworzenia świadectwa pracy.

Formatka składa się z 2 arkuszy:

  1. Formatka – tutaj jest arkusz, który widzi użytkownik
  2. Dane – tutaj są dane, które pobiera formatka po wyborze pracownika z listy rozwijanej

A tak wyglądają po kolei oba arkusze.

Arkusz samej formatki:

Formatka

Formatka

Lista rozwijana w tej formatce jest już stworzona. O tym, jak zrobić, aby jej źródło było w innym arkuszu i do tego aby się sama powiększała – pisałam tutaj.

arkusz z danymi pracowników:

Tabela z danymi (tbPracownicy)

Tabela z danymi (tbPracownicy)

Istotne jest, że dane pracowników przechowywane są w obiekcie tabela, nazwanym tbPracownicy. Będzie to za chwilę wykorzystywane w formule.

Formuła

Formuła będzie wykorzystywała funkcję WYSZUKAJ.PIONOWO w swoim klasycznym zastosowaniu. Czyli na podstawie imienia i nazwiska pracownika (zakładam, że jest ono unikalne w tych danych!) będzie wyszukiwała odpowiednią informację. Jaką? Będziemy chcieli uzupełnić wszystkie dane o pracowniku w formatce, czyli kolejno imiona rodziców, datę i miejsce urodzenia, nazwę pracodawcy itd. Będziemy musieli więc napisać tyle formuł, ile jest tych danych. Dla każdego wiersza oddzielnie. Oczywiście zrobimy to sprytnie tak, żeby się nie narobić :).

Dane na formatce są ułożone w identycznej kolejności, co dane w tabeli tbPracownicy. Wykorzystamy to i napiszemy jedną formułę, która będzie wyświetlała inny numer kolumny tabeli tbPracownicy. Ten odpowiedni numer, w zależności od wiersza, w którym jest formuła.

Przyporządkowanie danych

Przyporządkowanie danych

Czyli żeby wyświetlić imię matki będziemy chcieli pobrać dane z kolumny drugiej naszej tabeli z danymi, dla imienia ojca z trzecie, dla daty zatrudnienia z czwartej itd.

Żeby sobie ułatwić to zadanie, w arkuszu Formatka, w kolumnie np. H stworzę sobie kolumnę pomocniczą, którą potem ukryję przed użytkownikiem. W tej kolumnie po prostu wpiszę numerki kolumn, które będę chciała wyświetlać z tabeli z danymi. O tak:

Kolumna pomocnicza z numerami kolumn

Kolumna pomocnicza z numerami kolumn

Użyłam tutaj ręcznie wpisanych numerów kolumn, ponieważ nie planuję zmieniać kolejności w danych źródłowych, więc takie rozwiązanie w zupełności mi wystarczy. Oczywiście, można użyć tutaj funkcji PODAJ.POZYCJĘ, aby namierzyć, skąd WYSZUKAJ.PIONOWO miałaby pobierać dane, jednak trzeba wtedy zadbać o identyczne nagłówki w obu miejscach, a moje takie nie są.

Tę kolumnę pomocniczą wykorzystamy do następującej formuły we wszystkich komórkach z zakresu D3:D20, czyli naszych uzupełnianych informacjach:

=WYSZUKAJ.PIONOWO($D$3;tbPracownicy;H5;0)

Funkcja wyświetli nam dane po wyborze pracownika z listy rozwijanej. Jeśli pracownik nie będzie wybrany – wyświetli błąd #N/D!. Aby temu zapobiec, mozna zastosować lekką modyfikację tej formuły, np. taką:

=JEŻELI($D$3="";"";WYSZUKAJ.PIONOWO($D$3;tbPracownicy;H5;0))

I to tyle :). Cała magia :).

Efekt jest taki:

Tadam!

A oto plik z gotowcem do pobrania:

MalinowyExcel Uzupełnianie danych po wyborze z listy new dw.xlsx

 

I film na YB:

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.
  • Webinar Listy rozwijane, podczas którego pokazuję jak zrobić listę rozwijaną zarówno taką, jak we wpisie (czyli sprawdzanie poprawności), jak i formant formularza.
  • Webinar Walidacja danych – triki, pokazujący ciekawe zastosowania sprawdzania poprawności, którego lista rozwijana w komórce (użyta w tym wpisie) jest opcją. ALe narzędzie to potrafi dużo, duuuużo więcej!

 

 

 

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

23 komentarze

  1. Od razu wiedziałam, że to WYSZUKAJ.PIONOWO. 😛

    Reply
    • Super, o to chodziło 🙂

      Reply
  2. Droga Malinko!
    Ja mam podobny formularz a nawet dwa różne, lecz podobne.
    Prezentuję wynik ekonomiczny pewnych danych za miesiąc lub za okres.

    w opcji za miesiąc użyłem dwie funkcje: Indeks oraz podaj pozycję. Z listy wybieram miesiąc i wyskakują mi dane, które są mi potrzebne.

    W opcji za okres czyli narastająco użyłem dwie funkcje: Suma i indeks. W miejscu wybierz miesiąc w poprzedniej opcji wpisuję po prostu ilość miesięcy licząc od stycznia.

    Cieszę się, że moje rozwiązanie i twoje są podobne chociaż użyłem innych funkcji i o tyle myślę, że Cię to zainteresuje

    Pozdrawiam Cię Malinko gorąco

    Reply
    • Stasiu 🙂 Jak najbardziej Indeks i Podaj.Pozycję tutaj też śmiało by pasowało :). A gdyby dane były ułożone w innej kolejności – to pewnie byłoby jedynym rozwiązaniem ;). Dziękuję, ze się podzieliłeś!

      Reply
  3. Malina, jak Ty mi zaimponowałaś dzisiaj 🙂

    Reply
    • Darek – no bardzo się cieszę 😀 🙂

      Reply
  4. Super to działa 🙂 Dziękuję. A jest możliwość aby zacząć wpisywać np.nazwisko i pojawiały się wyniki?

    Reply
    • Hej, niestety nie ma tutaj takiej możliwości. Funkcja pobiera dane na podstawie wpisanej wartości do komórki. Wpisanej, czyli zatwierdzonej. Podczas wpisywania dopiero nadajemy wartość komórki, a zatwierdzamy to np. Enterem.
      Choć na pewno taka opcja byłaby super 🙂

      Reply
  5. To ja się podczepię pod temat – rozumiem, że nie ma takiej możliwości, jeśli wartość wpisujemy w komórce, ale czy jest możliwość dorobienia takiej funkcjonalności na rozwijanej liście, tzn. działało by to jak w funkcji filtrowania danych.
    Czyli klikamy na rozwijaną listę, tak żeby się rozwinęła i wtedy wpisujemy znaki, które wyszukiwałyby odpowiednią wartość z listy – np. wpisując „ad” znalazłoby „Adama”.

    Reply
    • Cicho liczę, że Microsoft zrobi taką funkcjonalność w listach sprawdzania poprawności – wielu osobom by się to przydało! Na tę chwilę, trzeba kombinować ;(. Leila Gharani na jednym ze swoich filmów, tutaj: https://www.youtube.com/watch?v=Z-h2UER3b_0 Prezentowane rozwiązanie działa za pomocą formuł, natomiast funkcje w nim użyte dostępne są w wersji Excela Insiders. Czyli żeby ich użyć trzeba dołączyć do programu niejawnych testów (Insiders) ;(. Nie wiem, czy to coś pomogło…

      Reply
      • Dzięki za odpowiedź.
        Trzeba zatem czekać – może się coś w tym temacie zmieni.

        Reply
        • Dokładnie – mam taką nadzieję, bo nie my pierwsi potrzebujący :). Ja jeszcze dorzuciłabym do tego koncertu życzeń przewijanie listy rozwijanej za pomocą scrolla na myszce 😉

          Reply
  6. Cześć. Mam na imię Michał i piszę z prośbą o poradę. Co trzeba zrobić aby odwrócić ten proces. Czyli wpisuje dane w jednym arkuszu a w drugim tworzy mi się lista. Np chciałbym aby moi pracownicy podawali mi dane z produkcji. Mają do wypisania takie dane jak: Nr. zlecenia,maszyna,nr.pracownika,nr narzędzia. Na podstawie tych danych w drugim arkuszu tworzyłaby się automatycznie tabela a w arkuszu w którym wpisują te dane po zatwierdzeniu dane by się kasowały

    Reply
    • Hej, do tego trzeba byłoby napisać makro działające na zdarzenie zmiany komórki. Nie widzę innej opcji…

      Reply
  7. Witam czy z poziomy jest tak prosto jak z pionowym

    Reply
    • Jeśli chodzi o funkcję wyszukaj.poziomo to tak, logika jest analogiczna 🙂

      Reply
  8. Witam czy z poziomy jest tak prosto jak z pionowym np mam nazwę rozwijaną danego np produktu o stałej nazwie z listy wybieram model danego producenta a obok kolumna cena i chciał bym aby po wyborze z listy modelu produktu uzupełniło cenę w sąsiedniej kolumnie

    Reply
    • Hej,
      jeśli dobrze rozumiem, to tutaj można Wyszukaj.poziomo lub Indeks i Podaj.Pozycję 🙂

      Reply
  9. Cześć. Mam na imię Artur. Mam pytanie:
    Może się zdarzyć tak, że będą miał na liście np. trzy Anny Kowalskie (dokładnie takie samo imię i nazwisko). Formatka wybiera dane pierwszej osoby z listy. Czy jest jakiś pomysł na rozwiązanie takiego problemu ???

    Reply
    • Hej, trzeba byłoby je jakoś rozróżnić, np. po ID. 🙂

      Reply
  10. Malina – czy zrobiłabyś mi taką małą tabelkę? Nie mogę sobie z tym poradzić…
    dużo mniej skomplikowana niż Twoja, dla ciebie pewnie 5 minut – dla mnie masakra, siedzę już 2 godziny

    Reply
    • Hej 🙂 Pod wpisem jest plik do pobrania – może to Ci pomoże? Ja nie zajmuję się tworzeniem arkuszy na zlecenie ;(

      Reply
  11. Co zrobić w takim przypadku. W tabeli z danymi nie dla wszystkich pozycji z kolumny pierwszej są uzupełnione wszystkie dane. Tzn mam spis adresów i w kolejnych kolumnach ilość lokali 1, 2, 3 itd. Wiadomo że liczba lokali na poszczególnych budynkach się różni. W druku ktory jest kilkunastostronicowy po wybraniu konkretnego adresu mam w 1 kolumnie numerację lokali. Najwuekszy budynek ma 400 lokali problem jak zrobic by po wybraniu budynku który ma np 95 lokali nie pokazywało ani nie drukowało kolejnych pustych stron tabeli formularza.

    Reply

Submit a Comment

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

Pin It on Pinterest