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:
- Formatka – tutaj jest arkusz, który widzi użytkownik
- 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
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)
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
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
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!
Od razu wiedziałam, że to WYSZUKAJ.PIONOWO. 😛
Super, o to chodziło 🙂
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
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ś!
Malina, jak Ty mi zaimponowałaś dzisiaj 🙂
Darek – no bardzo się cieszę 😀 🙂
Super to działa 🙂 Dziękuję. A jest możliwość aby zacząć wpisywać np.nazwisko i pojawiały się wyniki?
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 🙂
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”.
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…
Dzięki za odpowiedź.
Trzeba zatem czekać – może się coś w tym temacie zmieni.
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 😉
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
Hej, do tego trzeba byłoby napisać makro działające na zdarzenie zmiany komórki. Nie widzę innej opcji…
Witam czy z poziomy jest tak prosto jak z pionowym
Jeśli chodzi o funkcję wyszukaj.poziomo to tak, logika jest analogiczna 🙂
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
Hej,
jeśli dobrze rozumiem, to tutaj można Wyszukaj.poziomo lub Indeks i Podaj.Pozycję 🙂
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 ???
Hej, trzeba byłoby je jakoś rozróżnić, np. po ID. 🙂
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
Hej 🙂 Pod wpisem jest plik do pobrania – może to Ci pomoże? Ja nie zajmuję się tworzeniem arkuszy na zlecenie ;(
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.