• Zapisz się do newslettera, aby otrzymywać powiadomienia o nowościach na blogu
    Zapisując się, wyrażasz zgodę na przesyłanie Ci informacji o nowościach na tym blogu. Zgodę możesz w każdej chwili wycofać (szczegóły).

Uzupełnianie danych na podstawie wyboru z listy rozwijanej

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 🙂

Szukasz szkolenia z Excela dla HR?

Mogę je dla Ciebie poprowadzić tradycyjnie lub on-line

Najbliższy termin: 23-24 września 2019
(szkolenie stacjonarne, Warszawa)

Zobacz szczegóły

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.

I 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!

 

 

 

Spodobał Ci się ten artykuł?

Jeśli TAK, zapisz się na newsletter, aby otrzymywać powiadomienia
o o nowościach na blogu.

Zapisując się, wyrażasz zgodę na przesyłanie Ci informacji o nowościach na tym blogu. Zgodę możesz w każdej chwili wycofać (szczegóły).

Tagi , , , , , , , , .Dodaj do zakładek Link.

8 odpowiedzi na „Uzupełnianie danych na podstawie wyboru z listy rozwijanej

  1. Ola mówi:

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

  2. Stasiu mówi:

    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

    • Malina mówi:

      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ś!

  3. Darek Sternal mówi:

    Malina, jak Ty mi zaimponowałaś dzisiaj 🙂

  4. Pawel mówi:

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

    • Malina mówi:

      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 🙂

Pozostaw odpowiedź Malina Anuluj pisanie odpowiedzi

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