Aby stworzyć listę rozwijaną w Excelu, musisz pójść do wstążki o nazwie „Dane”, potem musisz kliknąć na „Poprawność danych”. Wtedy otworzy ci się okienko które pozwoli ci stworzyć listę rozwijaną. W tym okienku trzeba wybrać „lista” i zaznaczyć z czego ma ta lista się składać. Obejrzyj ten film poniżej aby Adresowanie mieszane. Adresowanie mieszane zawiera w sobie elementy adresowania względnego i bezwzględnego. Polega na umieszczeniu znaku $ tylko przed literą kolumny lub numerem wiersza. Odniesienia mieszane są stosowane, gdy w danym arkuszu chcemy skonfigurować formuły, które będą kopiowane między różnymi wierszami i kolumnami. http://malinowyexcel.pl/Po obejrzeniu tego filmu będziesz umieć tworzyć listę rozwijaną w Excelu, za pomocą dwóch metod: sprawdzanie poprawności - lista w ko SZULTASET. Temat: Lista rozwijana z połączeniem opcji wyszukiwania. Wykorzystałabym tu raczej formant Combi (ActiveX) - wówczas można próbować ograniczać taką listę. W Accessie działa to bardzo ładnie, w Excelu też z pewnością się da. Userform nie jest potrzebny, można osadzać formant bezpośrednio w arkuszu. W sekcji Pola okienka Wizualizacje wybierz listę rozwijaną w wartości osi X. Zmień datę z Hierarchii dat na Data. Usługa Power BI aktualizuje wykres kolumnowy, aby pokazać zysk dla każdego miesiąca. W okienku Wizualizacje zmień typ wizualizacji na Wykres liniowy. Teraz można łatwo zobaczyć, że grudzień 2014 miał największy zysk. pada abad keberapa teknik sulam terkenal di indonesia. Minuty czytania: 4 Lista rozwijana – 3 proste sposoby Listę rozwijaną w Excelu możesz wykorzystać na wiele sposobów. Od stworzenia formularza, po interaktywne wykresy. W tym artykule poznasz 3 proste sposoby na stworzenie listy. Dowiesz się jakie są różnice i zalety każdej z nich. Na końcu artykuły znajdziesz plik z opisywanymi tu przykładami. Lista rozwijana w komórce Najprostszym i często wykorzystywanym sposobem na listę rozwijaną, jest wykorzystanie funkcjonalności – poprawność danych. Załóżmy, że chcesz stworzyć listę rozwijaną, z której będzie możliwe wybranie jakiegoś miasta. Przygotuj listę miast w dowolnym zakresie w arkuszu. Kolejne kroki jakie musisz zrobić: Zaznacz komórkę, w której ma powstać lista na wstążkę DANE i wybierz POPRAWNOŚĆ zakładce USTAWIENIA, wybierz z listy rozwijanej LISTĘ. W polu ŹRÓDŁO, kliknij w ikonę z komórkami. To pozwoli Ci zaznaczyć zakres z miastami, które wcześniej wpisałeś. Jeżeli wiesz jak nadawać NAZWY – w tym miejscu zamiast wskazywać zakres – możesz podać wcześniej zdefiniowaną nazwę. Po zaznaczeniu zakresu, ponownie kliknij w ikonę z komórkami. To pozwoli Ci powrócić do menu USTAWIENIA, w oknie dialogowym SPRAWDZANIE POPRAWNOŚCI DANYCH. Teraz wystarczy, że klikniesz w przycisk OK. Lista rozwiana jest gotowa. Klikając w komórkę z listą, będziesz mógł zobaczyć wszystkie wcześnie wpisane miasta. Lista rozwijana jako kontrolka formularza Drugim sposobem na listę jest wykorzystanie kontrolki formularza. Na karcie DEWELOPER (z tego artykułu dowiesz się jak ją włączyć) kliknij polecenie WSTAW. Pojawi się menu z dwoma grupami: kontrolki formularza i formanty ActiveX (o tych drugich później) W ramach kontrolek, możesz używać takich jak: przycisk, pasek przewijania, pole wyboru (tzw. checkbox) i właśnie lista, która tu nazywa się pole kombi. Kliknij w nią, a następnie trzymając lewy przycisk myszy „narysuj” jej kształt w wybranym przez Ciebie miejscu arkusza. Jak wypełnić formant, miastami? Wykonaj następujące kroki: Kliknij prawym przyciskiem myszy, w FORMATUJ FORMANT z menu się okno dialogowe FORMATOWANIE karcie FORMANT, kliknij ikonę dla pola ZAKRES jak wcześniej, możesz teraz zaznaczyć zakres komórek, w którym wpisałeś nazwy miast. Wracasz do menu, ponownie klikając w jeszcze wybrać komórkę, która będzie pojawiała się liczba, reprezentująca pozycję miasta na liście (po jego wybraniu). W tym celu wybierz ikonę przy polu ŁĄCZE KOMÓRKI. Jest to opcja, którą możesz wykorzystać później do różnych obliczeń czy interakcji z użytkownikiem. Na przykład przy wykorzystaniu takich formuł jak: INDEKS i Lista rozwijana jako formant ActiveX Ostatni sposób na listę z opcjami do wyboru to tzw. pole kombi (formant ActiveX) Do pewnego momentu postępujesz analogicznie, jak przy kontrolce formularza: klikasz kartę DEWELOPER, wybierasz WSTAW i z sekcji FORMANTY ACTIVEX wybierasz POLE KOMBI. Podobnie jak wcześniej rysujesz oczekiwany kształt listy w wybranym przez Ciebie miejscu arkusza. W tym miejscu zaczynają się różnice. Po pierwsze, po narysowaniu formantu zauważysz z pewnością dwie rzeczy: Klikając w formant, nie możesz jeszcze używać listy. Jesteś w tzw. trybie projektowania i aby to zmienić, musisz odkliknąć ikonę TRYB PROJEKTOWANIA na wstążce DEWELOPER. W pasku formuły pojawił się dziwny zapis: =OSADŹ(„ Zapis sugeruje, że masz do czynienia z kodem VBA. Ale nie musisz znać VBA, aby wykorzystać formant. Co musisz zrobić? W TRYBIE PROJEKTOWANIA, wybierz formant prawym przyciskiem menu kontekstowego wybierz się dość rozbudowane menu w języku angielskim. Interesują Cię dwie pozycje: ListFillRange i LinkedCell. Pełnią one dokładnie te same funkcje jak w przypadku kontrolki formularza. W pierwszym przypadku wypełniasz listę danymi, a w drugim wskazujesz komórkę, która będzie połączona z wyborem konkretnej wartości z listy. Niestety nie możesz wybrać zakresów za pomocą myszki. Musisz wpisać odpowiedni adres zakresu. W przypadku kontrolki ActiveX, po wybraniu jakieś pozycji z listy, pojawi się ona bezpośrednio w komórce połączonej (LinkedCell), a nie jako wartość reprezentująca jej pozycję na liście (jak w przypadku kontrolki formularza) Chcesz lepiej poznać Excela? Zapisz się na Excelness News Podsumowanie Poznałeś trzy sposoby na stworzenie listy rozwijanej. Wszystkie przykłady znajdziesz w tym pliku. Czy któryś z nich jest lepszy od innego? Najkrócej mówiąc nie. Wszystko zależy od tego czego konkretnie potrzebujesz. Najczęściej będziesz wykorzystywał opcję ze sprawdzaniem poprawności. Dzięki tej funkcjonalności stworzysz listę zarówno do formularzy, jak i opcję do interakcji z użytkownikiem w przypadku bardziej skomplikowanych raportów. Wybrana wartość, zawsze pojawia się w komórce, w której stworzysz daną listę. Kontrolka formularza daje Ci co najmniej dwie nowe możliwości. Po pierwsze do każdej kontrolki możesz przypisać makro. Po drugie po wybraniu wartości z listy, w połączonej komórce nie pojawia się ta wartość tylko liczba reprezentująca pozycję tej wartości na liście. Wbrew pozorom, w niektórych przypadkach to może być kluczowa funkcja . Najbardziej złożona opcja to ta z kontrolką ActiveX. Pozwala na oprogramowanie jej przez kod VBA. To daje Ci najwięcej możliwości i zapewnia największą elastyczność. Przykładowo, możesz wypełniać listę dynamicznie w kodzie VBA, nie odwołując się do jakiegokolwiek zakresu komórek. Wierzę, że teraz tworzenie formularzy i raportów z listami rozwianymi będzie dla Ciebie codziennością 😉 Pochwal się w komentarzu, którą z opcji zacząłeś wykorzystywać. Masz problemy z Excelem? Uzyskaj pomoc na grupie Keep Calm And Use Excel. Tworzenie list rozwijanych nie tylko oszczędza czas przy wyborze opcji w procesie wypełniania tabel, ale także chroni przed nieumyślnym wprowadzaniem niepoprawnych danych. To bardzo wygodne i praktyczne narzędzie. Dowiedzmy się, jak go aktywować w programie Excel i jak z niego korzystać, a także poznać kilka innych niuansów jego obsługi. Spis treści Korzystanie z rozwijanych listProcedura tworzeniaOperacjeDodanie nowego przedmiotuUsuń elementCałkowite usunięciePytania i odpowiedzi Korzystanie z rozwijanych list Rozwijane listy lub, jak mówią, listy rozwijane są najczęściej używane w tabelach. Za ich pomocą można ograniczyć zakres wprowadzanych wartości do tablicy tablicowej. Pozwalają one na wprowadzanie wartości tylko z wcześniej przygotowanej listy. To jednocześnie przyspiesza procedurę wprowadzania danych i chroni przed błędami. Procedura tworzenia Przede wszystkim dowiedzmy się, jak utworzyć listę rozwijaną. Najłatwiej to zrobić za pomocą narzędzia o nazwie Data Validation . Wybierz kolumnę tablicy tabeli w komórkach, w których planujesz umieścić listę rozwijaną. Przechodzimy do zakładki "Dane" i klikamy przycisk "Sprawdź dane" . Jest zlokalizowany na taśmie w bloku "Praca z danymi" . Otworzy się okno narzędzia "Sprawdź wartości" . Przejdź do sekcji "Ustawienia" . W obszarze "Typ danych" z listy wybierz opcję "Lista" . Następnie przechodzimy do pola "Źródło" . Tutaj musisz określić grupę nazw przeznaczonych do użycia na liście. Nazwy te można wprowadzić ręcznie i można podać do nich odsyłacz, jeśli są już umieszczone w dokumencie Excel w innym miejscu. Jeśli ręcznie wybrano dane wejściowe, każdy element listy musi zostać wprowadzony do regionu za pomocą średnika ( ; ). Jeśli chcesz pobrać dane z istniejącej tablicy tablicowej, powinieneś przejść do arkusza, w którym się znajduje (jeśli jest umieszczony na drugiej), umieścić kursor w obszarze "Źródło" okna sprawdzania poprawności danych, a następnie wybrać tablicę komórek, w których znajduje się lista. Ważne jest, aby każda oddzielna komórka miała oddzielny element listy. Następnie współrzędne określonego zakresu powinny być wyświetlane w obszarze "Źródło" . Inną opcją do nawiązania połączenia jest przypisanie tablicy z listą nazwy. Wybierz zakres, w którym wyświetlane są wartości danych. Na lewo od linii formuły znajduje się domena nazw. Domyślnie po podświetleniu zakresu wyświetlane są współrzędne pierwszej wybranej komórki. My, dla naszych celów, wpisujemy właśnie tam nazwę, którą uważamy za właściwszą. Główne wymagania dotyczące nazwy są takie, że są unikatowe w książce, nie mają żadnych spacji i muszą zaczynać się od litery. Teraz to ta nazwa będzie identyfikować zakres, który wcześniej wyodrębniliśmy. Teraz w oknie weryfikacji danych w obszarze "Źródło" musisz ustawić symbol "=" , a następnie zaraz po nim wpisać nazwę, którą przypisaliśmy do tego zakresu. Program natychmiast identyfikuje relację między nazwą i tablicą i wyświetla listę, w której się znajduje. Ale znacznie skuteczniejsze będzie korzystanie z listy, jeśli zostanie ona przekonwertowana na "inteligentną" tabelę. W takiej tabeli łatwiej będzie zmienić wartości, automatycznie zmieniając pozycje listy. Tak więc ten zakres faktycznie zamienia się w tabelę odnośników. Aby przekonwertować zakres na "inteligentny" stół, wybierz go i przejdź do zakładki "Strona główna" . Tam klikamy przycisk "Formatuj jako tabelę" , który umieszczony jest na wstążce w bloku "Style" . Otwiera dużą grupę stylów. Jeśli chodzi o funkcjonalność tabeli, wybór konkretnego stylu nie ma wpływu, dlatego należy wybrać dowolny z nich. Następnie otwiera się małe okno, które zawiera adres wybranej tablicy. Jeśli wybór został wykonany prawidłowo, nic nie zmieni. Ponieważ nasz asortyment nie ma nagłówków, wówczas "Tabela z nagłówkami" nie powinna zawierać znacznika. Chociaż w twoim przypadku, być może, tytuł zostanie zastosowany. Musimy więc kliknąć przycisk "OK" . Następnie zakres zostanie sformatowany jako tabela. Jeśli ją wybierzesz, w obszarze nazw będzie można zobaczyć, że nazwa została mu przypisana automatycznie. Tej nazwy można użyć do wstawienia do obszaru "Źródło" w oknie weryfikacji danych zgodnie z algorytmem opisanym wcześniej przez nas. Ale jeśli chcesz użyć innej nazwy, możesz ją zastąpić po prostu wpisując przestrzeń nazw. Jeśli lista znajduje się w innej książce, to dla poprawnego odzwierciedlenia konieczne jest zastosowanie funkcji " Alfa" . Określony operator ma tworzyć "superabsolute" łącza do elementów arkusza w formie tekstowej. W rzeczywistości procedura będzie wykonywana prawie dokładnie tak samo jak w opisanych wcześniej przypadkach, tylko w polu "Źródło" po znaku "=" powinna wskazywać nazwę operatora - "DVSSYL" . Następnie adres zakresu, łącznie z nazwą książki i arkuszem, powinien być wskazany w nawiasach jako argument tej funkcji. Właściwie, jak pokazano na poniższym obrazku. W tym celu możemy zakończyć procedurę, klikając przycisk "OK" w oknie weryfikacji danych, ale jeśli chcesz, możesz poprawić formularz. Przejdź do pola wyboru "Wiadomości do wprowadzenia" danych. Tutaj, w obszarze "Wiadomość", możesz wpisać tekst, który zobaczy użytkownik, wskazując kursorem pozycję na liście z rozwijanej listy. Zapisujemy komunikat, który uważamy za konieczny. Następnie przejdź do sekcji "Komunikat o błędzie" . Tutaj, w obszarze "Wiadomość", możesz wprowadzić tekst, który użytkownik będzie obserwować, gdy spróbuje wprowadzić niepoprawne dane, czyli wszelkie dane, które nie znajdują się na liście rozwijanej. W obszarze "Widok" możesz wybrać ikonę, któremu ma towarzyszyć ostrzeżenie. Wprowadź tekst wiadomości i kliknij "OK" . Lekcja: Jak utworzyć listę rozwijaną w programie Excel Operacje Teraz zastanówmy się, jak pracować z narzędziem, które stworzyliśmy powyżej. Jeśli umieścimy kursor na dowolnym elemencie arkusza, do którego została zastosowana lista rozwijana, zobaczymy komunikat informacyjny, który wprowadziliśmy wcześniej w oknie weryfikacji danych. Ponadto po prawej stronie komórki pojawia się ikona trójkąta. Służy do dostępu do wyboru elementów listy. Klikamy na ten trójkąt. Po kliknięciu, menu obiektów listy zostanie otwarte. Zawiera wszystkie elementy, które zostały wcześniej wprowadzone przez okno weryfikacji danych. Wybierz opcję, która według nas jest właściwa. Wybrana opcja jest wyświetlana w komórce. Jeśli spróbujemy wpisać dowolną wartość w komórce, której nie ma na liście, akcja zostanie zablokowana. W takim przypadku, jeśli wprowadziłeś komunikat ostrzegawczy w oknie weryfikacji danych, pojawi się on na ekranie. Musisz kliknąć przycisk "Anuluj" w oknie ostrzeżenia i wprowadzić poprawne dane przy następnej próbie. W ten sposób, jeśli to konieczne, wypełnij całą tabelę. Dodanie nowego przedmiotu Ale co jeśli chcesz dodać nowy element? Czynności zależą od sposobu wygenerowania listy w oknie sprawdzania poprawności danych: wprowadzane ręcznie lub wyciągnięte z tablicy tabeli. Jeśli dane do utworzenia listy są wyciągane z tablicy tablicowej, przejdź do niej. Wybierz komórkę zakresu. Jeśli nie jest to inteligentna tabela, ale prosty zakres danych, musisz wstawić wiersz do środka tablicy. Jeśli używasz inteligentnej tabeli, w tym przypadku wpisz wymaganą wartość w pierwszym wierszu poniżej, a ta linia zostanie natychmiast uwzględniona w tablicy tabeli. Jest to tylko korzyść ze "inteligentnego" stołu, o którym wspominaliśmy powyżej. Załóżmy jednak, że mamy do czynienia z bardziej złożonym przypadkiem, używając zwykłego zakresu. Wybierz komórkę w środku określonej tablicy. Oznacza to, że powyżej tej komórki i pod nią musi być więcej rzędów tablicy. Klikamy na wyznaczony fragment prawym przyciskiem myszy. Z menu wybierz opcję "Wklej ..." . Otworzy się okno, w którym można wybrać obiekt wstawiania. Wybierz opcję "Linia" i kliknij przycisk "OK" . Tak więc dodaje się pusty ciąg. Wprowadzamy do niej wartość, którą chcemy wyświetlić na liście rozwijanej. Następnie wracamy do tablicy tablic, w której znajduje się lista rozwijana. Kliknięcie trójkąta po prawej stronie dowolnej komórki tablicy powoduje, że żądana wartość została dodana do już istniejących elementów listy. Teraz, jeśli chcesz, możesz go wstawić do elementu tabeli. Ale co, jeśli lista wartości nie zostanie wyciągnięta z oddzielnej tabeli, ale została wniesiona ręcznie? Aby dodać element w tym przypadku, istnieje również algorytm działań. Wybierz cały zakres tabeli, w którym umieszczona jest lista rozwijana. Przejdź do zakładki "Dane" i ponownie kliknij przycisk "Sprawdzanie danych" w grupie "Praca z danymi" . Rozpocznie się okno sprawdzania danych wejściowych. Przechodzimy do sekcji "Opcje" . Jak widzisz, wszystkie ustawienia tutaj są dokładnie takie same jak te, które umieściliśmy wcześniej. W tym przypadku będziemy zainteresowani obszarem "Źródło" . Dodajemy tam do listy, która już ma średnik ( ; ) tę wartość lub wartości, które chcemy zobaczyć na liście rozwijanej. Po dodaniu kliknij "OK" . Teraz, jeśli otworzymy listę rozwijaną w tablicy tabeli, zobaczymy tam wartość dodaną. Usuń element Usunięcie elementu listy odbywa się dokładnie według tego samego algorytmu, co dodanie. Jeśli dane są pobierane z tablicy tablicowej, przejdź do tej tabeli i kliknij prawym przyciskiem myszy komórkę, w której znajduje się wartość do usunięcia. W menu kontekstowym wybierz opcję "Usuń ..." . Okno do usuwania komórek jest prawie takie samo, jak to, które widzieliśmy podczas dodawania. Następnie ponownie ustaw przełącznik w pozycji "Linia" i kliknij "OK" . Wiersz z tablicy tabeli, jak widać, został usunięty. Teraz wróć do tabeli, gdzie znajdują się komórki z rozwijaną listą. Klikamy na trójkąt po prawej stronie dowolnej komórki. Na otwartej liście widzimy, że brakuje usuniętego elementu. Co zrobić, jeśli wartości zostały dodane do okna weryfikacji danych ręcznie, zamiast korzystania z dodatkowej tabeli? Wybierz zakres tabelaryczny z rozwijaną listą i przejdź do pola wyboru, tak jak robiliśmy to wcześniej. W określonym oknie przejdź do sekcji "Ustawienia" . W obszarze "Źródło" podświetl kursor wartością, którą chcesz usunąć. Następnie kliknij przycisk Usuń na klawiaturze. Po usunięciu elementu kliknij "OK" . Teraz nie będzie na liście rozwijanej, tak jak widzieliśmy w poprzedniej wersji tabeli. Całkowite usunięcie Jednocześnie zdarzają się sytuacje, gdy lista rozwijana musi zostać całkowicie usunięta. Jeśli nie obchodzi cię, że wprowadzone dane zostały zapisane, bardzo łatwo je usunąć. Wybierz całą tablicę, w której znajduje się lista rozwijana. Przechodzimy do zakładki "Strona główna" . Klikamy na ikonę "Wyczyść" , która jest umieszczona na taśmie w bloku "Edycja" . W menu, które zostanie otwarte, wybierz pozycję "Wyczyść wszystko" . Jeśli wybierzesz tę akcję, wszystkie wartości zostaną usunięte w wybranych elementach arkusza roboczego, formatowanie zostanie wyczyszczone, a główny cel zadania zostanie osiągnięty: lista rozwijana zostanie usunięta, a teraz możesz ręcznie wprowadzić wartości w komórkach. Ponadto, jeśli użytkownik nie musi zapisywać wprowadzonych danych, istnieje inna opcja usunięcia listy rozwijanej. Wybieramy zakres pustych komórek, który jest równoważny zakresowi elementów tablicy z rozwijaną listą. Przechodzimy do zakładki "Strona główna" i tam klikamy ikonę "Kopiuj" , która jest zlokalizowana na taśmie w obszarze "Schowek" . Ponadto zamiast tej akcji możesz kliknąć wybrany fragment prawym przyciskiem myszy i kliknąć opcję "Kopiuj" . Jeszcze łatwiej jest zastosować zestaw przycisków Ctrl + C zaraz po wyborze. Następnie wybierz fragment tablicy tablicowej, w której znajdują się rozwijane elementy. Kliknij przycisk "Wstaw" znajdujący się na wstążce na karcie "Strona główna" w sekcji "Schowek" . Druga opcja polega na kliknięciu zaznaczenia prawym przyciskiem myszy i zatrzymaniu zaznaczenia w opcji "Wklej" w grupie "Parametry wstawiania" . Na koniec można po prostu podać żądane komórki i wpisać kombinację klawiszy Ctrl + V. W przypadku dowolnego z powyższych działań zamiast komórek zawierających wartości i listy rozwijane zostanie wstawiony czysty fragment. W razie potrzeby można wstawić w ten sam sposób, nie pusty zakres, ale skopiowany fragment z danymi. Wadą list rozwijanych jest to, że nie można ręcznie wprowadzać danych, których nie ma na liście, ale można je kopiować i wklejać. W tym samym czasie weryfikacja danych nie działa. Co więcej, jak się dowiedzieliśmy, sama struktura listy rozwijanej zostanie zniszczona. Często nadal trzeba usunąć listę rozwijaną, ale jednocześnie pozostawić wprowadzone wartości i sformatować ją. W takim przypadku musisz wykonać więcej poprawnych działań, aby usunąć określone narzędzie do napełniania. Wybierz cały fragment, w którym znajdują się elementy z listą rozwijaną. Przechodzimy do zakładki "Dane" i klikamy ikonę "Weryfikacja danych" , którą, jak pamiętamy, umieszczamy na taśmie w grupie "Praca z danymi" . Otworzy się okno do sprawdzenia danych wejściowych, które już są nam znane. Będąc w dowolnej sekcji określonego narzędzia, musimy wykonać pojedynczą akcję - kliknij przycisk "Wyczyść wszystko" . Znajduje się w lewym dolnym rogu okna. Następnie okno weryfikacji danych można zamknąć, klikając standardowy przycisk zamykania w prawym górnym rogu jako krzyżyk lub przycisk "OK" w dolnej części okna. Następnie wybierz dowolną komórkę, w której poprzednio została umieszczona lista rozwijana. Jak widać, teraz nie ma podpowiedzi podczas wybierania elementu lub trójkąta do wywoływania listy po prawej stronie komórki. W tym samym czasie formatowanie i wszystkie wcześniej wprowadzone wartości pozostały nietknięte przy pomocy listy. Oznacza to, że z powodzeniem zarządzamy zadaniem: narzędzie, którego już nie potrzebujemy, jest usuwane, ale wyniki jego pracy pozostały nienaruszone. Jak widać, lista rozwijana może znacznie ułatwić wprowadzanie danych do tabel, a także zapobiegać wprowadzaniu niepoprawnych wartości. Spowoduje to zmniejszenie liczby błędów podczas wypełniania tabel. Если же какое-нибудь значение нужно добавить дополнительно, то всегда можно провести процедуру редактирования. Вариант редактирования будет зависеть от способа создания. После заполнения таблицы можно удалить выпадающий список, хотя делать это и не обязательно. Большинство пользователей предпочитают его оставлять даже после окончания работы по заполнению таблицы данными. Rozwijane listy są bardzo popularną opcją wykorzystywaną w arkuszu kalkulacyjnym. Ułatwiają pracę z dużą ilością danych w Excelu i pozwalają wyeliminować błędy. W dzisiejszym artykule pokażemy zarówno jak utworzyć prostą listę, której elementy wpisywane są ręcznie do komórek a także dynamiczną, rozwijaną listę przy tworzeniu której wykorzystuje się odpowiednie funkcje Excela. Jesteś ciekawy? Zapraszamy! Zrzuty ekranu w tym artykule zostały wykonane w programie Excel dla Office 365. Jeżeli masz inną wersję programu Excel, wygląd może się nieco różnić ale nie martw się funkcje pozostają takie same. Zaczynamy! Tworzenie rozwijanej listy z elementów wpisanych ręcznie oraz danych pobranych z innych komórek. Dysponujemy zestawieniem z numerami zamówień oraz ich statusem realizacji. Chcemy aby na naszej rozwijanej liści znajdowały się dane, które mamy już zapisane w arkuszu kalkulacyjnym. W tym przypadku utworzymy rozwijaną listę w kolumnie G czyli „status”. Zaznaczamy wybrany obszar (pomijamy nagłówki). Możemy użyć w tym celu także skrótu klawiszowego CTRL+SHIFT+strzałka w dół. Jest to bardzo szybki sposób i przydaje się w pracy z dużą ilością danych. W górnym pasku nawigacji przechodzimy do zakładki „Dane” a następnie w sekcji „Narzędzia danych” wybieramy opcję „Poprawność danych”. Wyświetli się nowe okno dialogowe. W polu „Dozwolone” wybieramy opcję „Lista”. Elementy listy wprowadzamy ręcznie oddzielając je średnikami. Zatwierdzamy klikając przycisk „OK”. Możemy mieć także sytuację, w której będziemy odwoływać się do danych z innej tablicy. Wówczas zaznaczamy obszar w którym będzie znajdować się rozwijana lista a następnie w górnym menu w zakładce „Dane” wybieramy opcję „Poprawność danych” i w nowym oknie dialogowym w polu „Źródło” zamiast wprowadzać ręcznie słowa, wystarczy zaznaczyć zakres komórek z tablicy źródłowej (w naszym przypadku będzie to kolumna I). Gotowe! Została utworzona rozwijana lista na tych komórkach, które były przez nas zaznaczone. Działa? 🙂 Tworzenie dynamicznych rozwijanych list w arkuszu kalkulacyjnym. Dynamiczna rozwijana lista to taka, której elementy będą się aktualizować po zmianie danych w komórkach z których zestawienie jest tworzone. Można dopisywać do niej elementy jak również usuwać je. Dysponujemy zestawieniem zatrudnionych pracowników wraz z ich wynagrodzeniem. Naszym zadaniem jest utworzenie dynamicznej rozwijanej listy z zarobkami. Użyjemy funkcji PRZESUNIĘCIE, która składa się z pięciu argumentów i zwraca się do zakresu, który jest podaną liczbą wierszy lub kolumn. Ustawiamy się w dowolnym miejscu w arkuszu kalkulacyjnym. Zależy nam na tym aby zbudować zakres zaczynający się od komórki K7 a kończący na K14. Dlatego też pierwszym argumentem jaki wskażemy będzie komórka K7. Następnie blokujemy jej adres, wciskając przycisk F4 lub klawisz funkcyjny czyli Fn a następnie F4. Pojawią się symbole dolara zarówno przed literką kolumny jak i cyfrą wiersza. Naciskamy średnik i przechodzimy do drugiego argumentu. Drugi argument mówi funkcji o ile wierszy w górę/dół ma się przesunąć, wskazując początek obszaru, który będzie tą funkcją budowany. W naszym przypadku będzie to komórka K7 więc nie chcemy się przesuwać ani w górę ani w dół, dlatego też wpisujemy wartość zero i stawiamy kolejny średnik. Argument „kolumny” mówi funkcji o ile ma się przesunąć w lewo lub w prawo budując początek obszaru. W tej sytuacji my także wpisujemy wartość zero i naciskamy średnik. Czwarty argument mówi funkcji jakiej wysokości będzie obszar, który zaczyna się od komórki K7 (wcześniej zdefiniowanej przez poprzednie 3 argumenty). Definiowany obszar musi mieć wysokość równą ilości pozycji w kolumnie K. W naszym przypadku wypełnionych jest 8 komórek więc moglibyśmy wpisać wartość 8 ale w momencie dodawania kolejnych zarobków poniżej komórki K14, dane te nie pojawiły by się automatycznie w rozwijanej liście. Dlatego też użyjemy funkcji Zaznaczamy zakres od komórki K7 do miejsca w którym będziemy chcieli ewentualnie wpisywać jeszcze jakieś dane. My podamy zakres od K7 do K20. Blokujemy ten obszar, klikając F4 (lub Fn+F4), zamykamy nawias i wstawiamy kolejny średnik. Ostatni argument mówi funkcji jakiej szerokości będzie obszar, który budujemy. W naszym przypadku obszar będzie składał się z jednej kolumny dlatego też wpisujemy wartość 1. Na koniec zamykamy nawias i klikamy przycisk „Enter”. Pojawia się błąd. Wejdziemy do edycji formuły, klikając w komórkę dwukrotnie. Następnie używając przycisku F9 lub Fn+F9 będziemy mogli podejrzeć co się pod tą funkcją kryje. Wracamy do formuły używając skrótu CTRL+Z. Teraz kopiujemy formułę, klikamy „Enter” a następnie zaznaczamy obszar, w którym ma zostać zastosowana rozwijana lista i przechodzimy do zakładki „Dane”. Wybieramy opcję „Poprawność danych”. W polu „Dozwolone” wybieramy opcję „Lista” natomiast w obszarze „Źródło” wklejamy skopiowaną formułę. Zatwierdzamy przyciskiem „OK”. Działa! 🙂 Teraz nowe, dostawione stawki wynagrodzenia w kolumnie K pojawią się także na rozwijanej liście w kolumnie G. Więcej Pytania? Masz dodatkowe pytania? Skorzystaj z możliwości skomentowania poniżej lub wyślij nam wiadomość korzystając z sekcji MAM PYTANIE Już od jakiegoś czasu chodziło mi po głowie, aby napisać o tym triku artykuł. Dodatkowo zmotywował mnie Waldkorg, bo dziś właśnie o niego zapytał. Trik, który bardzo sobie chwalą użytkownicy formularzy. Trik, który ułatwia i przyspiesza im pracę. Trik, dzięki któremu twoje formatki będą im przyjazne. Kiedyś wykorzystałam go, gdy tworzyłam formularz, za pomocą którego handlowcy zamawiali towar. Spośród całego asortymentu mieli oni wybrać te produkty, które zamierzali sprzedać. Każdy handlowiec najpierw określał grupę produktową, a następnie konkretny produkt z tej grupy. Na formularzu miała się znaleźć cała nazwa grupy oraz konkretny indeks produktu. Ponieważ wpisywanie tego z palca byłoby zbyt czasochłonne (i denerwujące), zaproponowałam bardzo sprytne i proste rozwiązanie – 2 listy rozwijane. Pierwsza była listą wszystkich kategorii produktów, druga zaś listą wszystkich produktów znajdujących się w wybranej kategorii. Stworzyłam więc listę rozwijaną zależną od wyboru dokonanego na poprzedniej liście (tutaj znajdziesz wpis o tym, jak stworzyć dwie zależne listy rozwijane). Taki sam efekt chce uzyskać Waldkorg, który do budżetu domowego potrzebuje kategorii i podkategorii wydatków. Przykładowe dane znajdują się na poniższym obrazku: Zależna lista rozwijana Czyli np. jeśli wybierzemy kategorię Rozrywka, to na liście podkategorii powinny się znaleźć: Kino, Teatr, Basen. Bardzo sprytne rozwiązanie, jeśli w swoim budżecie domowym chcecie analizować więcej szczegółów. Przyznam, że w mojej propozycji budżetu domowego ograniczam się tylko do kategorii, dlatego że taki podział wydatków w zupełności mi wystarcza (nazwę wydatku/przychodu traktuję jak podkategorię). Jeśli jednak potrzebujecie podzielić to na podkategorie, to metoda, którą opiszę dalej będzie idealna. Korzystajcie śmiało! Formatka – tabela z danymi do zależnej listy rozwijanej Najpierw należy stworzyć listę kategorii (A3:A5) i tabelę z danymi dla podkategorii. Lista kategorii to po prostu nazwy kategorii wypisane pod sobą – ona więc jest bardzo prosta. Tabela podkategorii jest trochę trudniejsza, ponieważ powinna ona zawierać nazwę kategorii powtórzoną tyle razy, ile jest podkategorii i oczywiście podkategorie. I ważna sprawa: cała ta tabela powinna być posortowana według kategorii! Będzie to niezwykle istotne przy późniejszym pisaniu formuły. Tabela powinna wyglądać tak (zakres G2:H15): Formatka No dobrze. Teraz po kolei opiszę kroki tworzenia zależnej listy rozwijanej. 1. Nazwanie zakresów dla zależnej listy rozwijanej Jest to krok nieobowiązkowy, bez niego bowiem bez problemu sobie poradzimy. Lubię jednak stosować nazwy, ponieważ znacznie ułatwiają one zarówno pisanie, jak i czytanie formuł. Nazwiemy 2 zakresy. Listę wszystkich kategorii i roboczą listę kategorii. Będą to zakresy A3:A5 (lista kategorii w zielonej tabelce na pierwszym obrazku) i G3:G15 (lista powtórzonych kategorii w fioletowej tabelce roboczej). Żeby nazwać listę kategorii: Zaznacz zakres A3:A5. W polu nazwy (pole po lewej stronie od paska formuły) wpisz nazwę Kategorie. Zatwierdź Enterem. Te same czynności wykonaj dla zakresu roboczej listy kategorii G3:G15, którą nazwij ListaRob. Tego zakresu będziemy używać w formule. 2. Stworzenie listy rozwijanej dla kategorii To będzie proste: Zaznacz komórkę, w której chcesz umieścić listę. U mnie to A12. Z menu Dane wybierz Poprawność danych. Pojawi się okno Sprawdzanie poprawności danych. Jako wartości dozwolone wybierz lista. Jako źródło wpisz: =Kategorie (obrazek poniżej). Sprawdzanie poprawności – kategorie Zatwierdź OK. Wynik jest następujący: Lista rozwijana kategorii 3. Stworzenie (zależnej) listy rozwijanej dla podkategorii Tutaj zaczyna się zabawa. Tworzyć listę umiemy – zrobiliśmy to przed chwilą dla kategorii. Pytanie tylko: „Jak powiedzieć Excelowi, aby wybrał tylko te wartości, które są przyporządkowane do konkretnej kategorii?”. Jak się zapewne domyślacie, skorzystam tutaj z tabeli roboczej i oczywiście z formuł. Zacznijmy od tego, co umiemy, czyli od stworzenia listy rozwijanej w komórce B12. Zaznacz więc tę komórkę i wybierz Dane/ Poprawność danych, a jako kryterium poprawności – Lista. W źródło listy wpisz taką formułę: =PRZESUNIĘCIE($H$2; Wygląd okna Sprawdzanie poprawności danych: Sprawdzanie poprawności dla podkategorii Jak widać, cały trik listy zależnej polega na użyciu funkcji PRZESUNIĘCIE. No dobra, prawie cały. Pomagają jej funkcje i Funkcja PRZESUNIĘCIE pozwala dynamicznie określić zakresy. Na początku ustalamy komórkę, od której ma się zacząć przesuwanie zakresu, a w kolejnych argumentach ustalamy jego rozmiary. W naszym przykładzie zakres będzie przesuwał się po kolumnie Podkategoria w tabeli roboczej (G2:H15). Przesuwanie zaczniemy od komórki H2, która jest jednocześnie pierwszym argumentem naszej funkcji. W formule ją zablokowałam, ponieważ zakładam, że listę rozwijaną będziemy wykorzystywać w wielu komórkach. Ponieważ tabela robocza jest posortowana po Kategorii, to zakres, który ma być źródłem do listy rozwijanej, będzie zaczynał się tam, gdzie pierwsze wystąpienie wybranej kategorii. Np. dla kategorii Jedzenie chcemy wyświetlić zakres H6:H11, dla Transport – zakres H12:H15 itd. Zauważmy, że cały czas poruszamy się po kolumnie H, a jedyne co się zmienia to początek zakresu i jego wysokość (czyli liczba elementów na liście). Początek zakresu zostanie przesunięty względem komórki H2 o tyle komórek w dół (o taką liczbę), ile wynosi numer pozycji pierwszego wystąpienia kategorii w kolumnie Kategoria. Łatwiej będzie na przykładzie: zakres dla kategorii Jedzenie jest przesunięty o 4 komórki w dół względem komórki H2 (zaczyna się w 4. komórce pod zakresem H2). W 4. komórce kolumny Podkategoria (nie uwzględniam nagłówka, czyli interesuje mnie nazwany zakres ListaRob) jest również słowo Jedzenie (jego pierwsze wystąpienie). Wykorzystujemy ten fakt właśnie do określenia początku zakresu. Posłuży nam do tego funkcja (wpisana w drugim argumencie funkcji PRZESUNIĘCIE): Wysokość zakresu ustali funkcja Zliczy ona wszystkie wystąpienia kategorii, czyli słowa Jedzenie. Słowo to występuje tyle razy, ile będzie pozycji w naszym zakresie. Liczba pozycji w zakresie to jego wysokość. Oto funkcja: Oczywiście obie funkcje zawiera już funkcja PRZESUNIĘCIE opisana wyżej. Dodatkowo zauważcie, że zarówno w funkcji jak i w odwołałam się do nazwanego zakresu ListaRob. Tak jak wspomniałam wcześniej, nie trzeba używać nazw zakresów, można wpisać po prostu $H3:$H15. Użycie nazwy jest jednak łatwiejsze i czytelne. I tyle. Jedna formułka, no niekoniecznie prosta, i załatwia sprawę! Dwa zastosowania tego triku już podałam. Ciekawa jestem natomiast, gdzie wy to wykorzystacie? A poniżej wersja wideo opisywanego case’a: Powiązane produkty Nazwy w Excelu – wstęp – pokazuję tutaj jak stworzyć nazwy komórek, zakresów i – co jest arcyciekawe – formuł! To bardzo proste i przydatne, zobaczysz 🙂 Listy rozwijane – pokazuję jak zrobić listę rozwijaną zarówno taką, jak we wpisie (czyli sprawdzanie poprawności), jak i formant formularza, oraz jak je wykorzystać np. na wykresach. Walidacja danych – triki – opowiadam o ciekawych zastosowaniach 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! Zagnieżdżanie funkcji – podczas tego webinaru pokazuję jak w prosty sposób zagnieżdżać funkcje jedna w drugiej i trzeciej… Redakcja językowa: Aleksandra Wasiak Chcesz mieć dwie listy rozwijane, gdzie wartości możliwe do wyboru na drugiej liście będą zależały od wartości jaką wybierzesz na pierwszej liście? Wymaga to odrobinę pracy, ale jest możliwe. Nawet możesz zrobić tak, by listy drugiego poziomu były dynamiczne, to znaczy, że ich długość będzie się zmieniać w zależności od ilości pozycji, które wpiszesz. Najprostsze podejście do tego wykorzystuje tabele z Excela i opcję Poprawność danych. Pierwszą listę dodaje się bardzo szybko, tylko musisz mieć najpierw odpowiednio przygotowane dane. Potrzebujesz tyle list grup/produktów/działów etc. z nagłówkami (obok siebie) ile będzie elementów na pierwszej liście rozwijanej. Ważne, żeby nagłówki były tymi nazwami, które chcesz, żeby wyświetlały się na pierwszej liście. Przykładowe przygotowane dane mogą wyglądać jak na obrazie obok. Teraz wystarczy zaznaczyć komórkę, gdzie chcesz mieć pierwszą listę, a następnie wybrać opcję Poprawność danych z karty Dane. Domyślnie są tu dozwolone wszystkie wartości, ale nam zależy, żeby były dozwolone wartości tylko z podanej listy. Jak już wybierzesz pozycję z listy rozwijanej jako Lista, to potrzebujesz jeszcze tą listę wpisać. Możesz wpisywać wartości ręcznie i rozdzielać je średnikiem, ale dużo wygodniej zaznaczyć zakres komórek, w którym masz już tą listę. Pamiętasz nagłówki naszych danych? To właśnie one mają robić za pierwszą listę rozwijaną, więc wystarczy je wpisać do pola Źródło (Jak klikniesz w nie, wystarczy, że zaznaczysz odpowiedni obszar myszką). To wystarczy, żebyś mógł cieszyć się pierwszą listą rozwijaną. Do zbudowania drugiej, która będzie zależna od wyboru pozycji na tej liście potrzebujesz więcej przygotowań. Po pierwsze wszystkie listy jakie stworzyłeś potrzebujesz zamienić na tabele (każda lista to ma być osobna tabela). Ponieważ potrzebowaliśmy, żeby listy były obok siebie, to nie możesz po prostu kliknąć gdzieś w ich obszar i nacisnąć Ctrl + T by się dodała tabela, ponieważ Excel będzie chciał zaznaczyć cały obszar danych. Musisz niestety każdą listę, każdą kolumnę (łącznie z nagłówkiem) zaznaczać osobno i korzystać ze skrótu klawiszowego Ctrl + T, albo wybrać opcję Tabela z karty Wstawianie. Przy wstawianiu tabel upewnij się, że masz zaznaczoną opcję Moja tabela ma nagłówki. W przeciwnym razie Excel sam wstawi Ci domyślny nagłówek co spowoduje, że na liście będziesz miał swój wcześniej utworzony nagłówek jako jedną z pozycji listy. Teraz potrzebujesz zmienić nazwę tabel na dokładnie taką jaka widnieje w nagłówku i jaką możesz wybierać na pierwszej liście rozwijanej. Żeby to zrobić wchodzisz w obszar tabeli (zaznaczasz w niej komórkę). Wtedy pokaże Ci się karta Projektowanie (Narzędzia Tabel) i na niej po prawej stronie zobaczysz aktualną nazwę tabeli, żeby ją zmienić wystarczy kliknąć w to pole i ustawić taką wartość jak potrzebujesz (czyli nazwę z nagłówka tabeli). Jeśli zrobisz tak z wszystkimi tabelami to możesz w końcu stworzyć drugą listę rozwijaną. Będziesz potrzebował do niej funkcji która będzie się odwoływała do wartości z pierwszej listy rozwijanej. Dlatego tak ważne było, żeby nazwy tabel i pozycji z listy rozwijanej były takie same, bo teraz funkcja przekształci nazwę tabeli na odwołanie do jej danych. Czyli znowu wstawiasz listę w opcji Poprawność danych tylko, że tym razem wpisujesz w niej funkcję = Teraz jak zmienisz wartość z pierwszej listy zmieni się druga lista, ale dopiero wtedy, gdy znowu ją rozwiniesz, czyli np.: jeśli w naszym przykładzie wybrałeś Warzywa i Brokuł, a następnie zmienisz wartość z pierwszej listy na Owoce, to na drugiej liście zostanie Brokuł. Musisz jeszcze raz wybrać wartość na drugiej liście, żeby poprawnie pokazywała na Owoce. Ponieważ odwołania w drugiej liście dotyczą się tabeli, to jeśli tabela zmieni swój rozmiar to również lista rozwijana będzie pokazywać inne wartości. Przykładowo możemy sobie dodać nowy Ser – Manchego na koniec odpowiedniej tabeli. Teraz jak wybierzesz Sery na pierwszej liście, na drugiej liście będzie już widoczny Manchego. To rozwiązanie ma dwa niedociągnięcia. Raz wybrana wartość na drugiej liście nie zmienia się niezależnie co wybierzesz na pierwszej liście. Drugim jest to, że jeśli w tabelach będą puste pola to będą one wyświetlane na listach, tak jak widzisz na załączonym obrazie. Wszystkie materiały użyte w lekcji znajdziesz poniżej.

excel jak zrobić listę rozwijaną