Aby utworzyć listę rozwijaną zależną od wielu poziomów, wykonaj następujące czynności: Najpierw utwórz dane dla listy rozwijanej zależnej od wielu poziomów. 1. Najpierw utwórz pierwszą, drugą i trzecią listę rozwijaną danych, jak pokazano na poniższym zrzucie ekranu: Po drugie, utwórz nazwy zakresów dla każdej rozwijanej 6.W tym kroku możesz wstawić pierwszą listę rozwijaną. Kliknij komórkę, w której chcesz wstawić listę rozwijaną, a następnie kliknij Dane > Walidacja danych > Walidacja danych, oraz w Walidacja danych okno dialogowe, wybierz Lista z Dopuszczać rozwijaną, a następnie wprowadź tę formułę: =Unikalna kategoria do Źródło pole tekstowe, patrz zrzut ekranu: Utwórz listę rozwijaną w programie Excel: Korzystanie z zakresu. Jeśli potrzebujesz bardziej elastycznego rozwiązania, możesz pobierać elementy listy z zakresu innych komórek w programie Excel. Aby to zrobić: 1. Najpierw wypisz wszystkie elementy, które chcesz umieścić na liście w dowolnej kolumnie komórek. Tematy o zrobić lista rozwijać, Jak zrobić listę rozwijaną za pomocą combo box?, excel 2003 - lista rozwijana, Excell - Lista rozwijana awarii samochodowych., EXCEL-Lista rozwijana - EXCEL-Lista rozwijana z odniesieniem do danej wartości. Utwórz listę rozwijaną z możliwością wyszukiwania (dla programu Excel 2019 i nowszych wersji) Jeśli używasz programu Excel 2019 lub nowszego, metody opisanej w tej sekcji można również użyć, aby umożliwić przeszukiwanie listy rozwijanej w programie Excel. Zakładając, że utworzyłeś listę rozwijaną w komórce A2 Arkusza 2 2. w Opcje programu Excel okno dialogowe w obszarze Wybierz polecenia z rozwijaną listę, wybierz Wszystkie polecenia, a następnie przewiń w dół, aby znaleźć Aparat fotograficzny, wybierz go i kliknij Dodaj >> przycisk, kamera została dodana w prawym polu listy, patrz zrzut ekranu: 3. Samouczki dotyczące programu Outlook. Jak zainstalować lub odinstalować. na głównej liście rozwijanej ( ), a następnie przejdź do zależnej listy rozwijanej ( ), aby kliknąć ikonę strzałki, sprawdzić, czy owoce znajdują się na liście, a następnie wybrać jedną pozycję z zależnej listy rozwijanej. 2. naciśnij aby rozpocząć Wybierz komórki, które chcesz umieścić na liście rozwijanej, kliknij Dane > Walidacja danych > Walidacja danych. 4. w Walidacja danych dialog, wybierz Lista in Dopuszczać lista pod Ustawienia wybierz listę tabeli przestawnej jako plik Źródło danych. 5. kliknij OK. Teraz lista rozwijana bez duplikatów została utworzona. Co zrobić aby przy tworzeniu formularza i wyborze elementu formantu "lista rozwijana" nastąpiło autouzupełnienie. Przykład: Wybieram z listy rozwijanej imię i nazwisko właściciela firmy. Po wyborze miejsca mają uzupełnić mi się następujące pola: 1. Kod pocztowy miejscowości, w której dana osoba ma siedzibę. 2. Niektóre funkcje programu Excel mogą powodować zamieszanie. I tutaj rozwiązaliśmy problem z rozwijaną listą w Excelu. W naszym poprzednim artykule „Jak utworzyć pytania wielokrotnego wyboru w formularzu programu Excel”, Wprowadziliśmy kroki, aby utworzyć listę rozwijaną w komórkach. Ale czasami, gdy wprowadzisz do komórki znJM2. Minuty czytania: 4 Listy rozwijane możesz zrobić na kilka sposobów. Dzięki lekturze tego artykułu dowiesz się, jak dzięki nim zautomatyzować pracę. Dynamiczna lista rozwijana pozwoli Ci ją przyspieszyć. Na koniec dowiesz się jak tworzyć hierarchiczne listy rozwijane. Hierarchiczne, to znaczy takie, gdzie lista wyboru z jednej listy zależy od wyborów z innej. Brzmi ciekawie? Zapraszam do lektury! Wszystkie opisywane w artykule funkcjonalności znajdziesz w filmie przygotowanym przez Jacka. Prosta lista rozwijana Załóżmy, że masz taką sytuację – dysponujesz bazą sprzedaży i chciałbyś zliczać łączną wartość sprzedaży netto dla poszczególnych grup towarów. Przygotujemy tabelę, w której będzie możliwe wybranie poszczególnych towarów z listy oraz automatyczne podsumowanie sprzedaży, wg tego wyboru. W kolejnych krokach pokażę Ci jak się za to zabrać. Przygotuj wszystkie unikatowe elementy listy, które mają pokazywać się na liście rozwijanej. Następnie ustaw się na komórce, gdzie ma się znaleźć lista rozwijana i wybierz POPRAWNOŚĆ DANYCH z menu górnego DANE w SEKCJI NARZĘDZIA DANYCH. W menu POPRAWNOŚĆ DANYCH, wybierz zakładkę KRYTERIA POPRAWNOŚCI. O co tutaj chodzi? Domyślnie do każdej komórki Excela możesz wpisać dowolną wartość. Natomiast my chcemy, aby do komórki z listą wyboru, mogły być wpisane tylko wartości zdefiniowane wcześniej przez nas. Dlatego, wybieramy polecenie LISTA i klikając na ikonę ŹRÓDŁO wskazujemy zakres komórek, gdzie umieściliśmy nasze unikalne kategorie towarów. Zatwierdzamy i nasza lista rozwijana jest gotowa. Dynamiczna lista rozwijana Prosta lista rozwijana to nie koniec możliwości. Pewnie zauważyłeś, że w momencie dodania kolejnej wartości do listy unikalnych towarów, lista rozwijana nie zaktualizuje się o te dodatkowe wartości. Stworzymy zatem listy dynamiczne. Co to oznacza? Gdy dodamy kolejną grupę towarów – to nie wykonując żadnych dodatkowych czynności – nowy wpis automatycznie pokaże się na naszej liście rozwijanej. Jak się do tego zabrać? Pierwszą rzeczą jaką musisz zrobić to przygotować listę. Kolejnym elementem całej układanki jest przerobienie tej listy na tabelę excelową. Jednym z najprostszych sposobów jest użycie skrótu CTRL + T. Dzięki temu, że ten obszar jest teraz tabelą, dodanie kolejnych elementów będzie w niej automatycznie uwzględniane. Jeśli podłączysz teraz sprawdzanie poprawności do tabeli, automatycznie dodane rekordy będą pojawiać się na liście. Początek drogi jest analogiczny jak poprzednio. To znaczy: Ustaw się na komórce, gdzie ma się znaleźć lista POPRAWNOŚĆ DANYCH z menu górnego DANE w SEKCJI NARZĘDZIA menu POPRAWNOŚĆ DANYCH, wybierz zakładkę KRYTERIA polecenie LISTA i klikając na ikonę ŹRÓDŁO wskaż tabelę, którą utworzyłeś ale tylko elementy listy bez nagłówka. Excel rozpozna, że chodzi tylko o same wpisy, a nie o klikając OK. Teraz, gdy dodasz kolejny wpis na liście, Twoja lista rozwijana automatycznie uwzględni tę wartość! Hierarchiczne listy rozwijane Naprawdę ciekawym rozwiązaniem są hierarchiczne listy rozwijane. Co to takiego? Załóżmy że, mamy główne grupy towaru, a w każdej grupie towaru znajdują się poszczególne towary. Na przykład w grupie “AGD”, mamy: lodówka, mikrofalówka, okap. W grupie “Meble” mamy: biurko, fotel biurowy, itd. Cały trick polega na tym, aby stworzyć listy rozwijane w taki sposób, że jeśli wybierzesz główną grupę towaru (na przykład AGD), to pokażą się tylko towary przypisane do tej grupy. Wbrew pozorom, takie rozwiązanie nie jest trudne. Tak jak poprzednio, musisz przygotować dane, tyle że tym razem nagłówki tabel będą oznaczały grupę produktów, a poniżej będą poszczególne towary, czyli elementy listy rozwijanej. Kolejny krok to nazwanie obszarów dla poszczególnych grup towarów. Zaznacz wszystkie zakresy łącznie z nagłówkami. Musisz to zrobić używając przycisku CTRL na klawiaturze oraz lewego przycisku myszy. Następnie przejdź na wstążkę FORMUŁY i wybierz z sekcji NAZWY ZDEFINIOWANE polecenie UTWÓRZ Z ZAZNACZENIA. Sprawdź czy jest wybrana opcja GÓRNY WIERSZ i zatwierdź wybór. Utworzyłeś 3 nazwy grup towarów (AGD, meble i oświetlenie) Sprawdzisz to w MENEDŻERZE NAZW na wstążce FORMUŁY. Przejdź teraz do komórki, gdzie ma powstać lista rozwijana z możliwością wyboru grupy towarów. W tym miejscu tworzysz listę w standardowy, opisany wcześniej sposób. Oczywiście jako zakres danych dla tej listy wskażesz tylko nagłówki z nazwami grup towarów. Następnie przejdź do komórki, gdzie ma pojawić się lista rozwijana z towarami. Zawartość tej listy będzie zależna od wybranej w poprzedniej komórce grupy towarów. Podobnie jak wcześniej, przejdź do polecenia POPRAWNOŚĆ DANYCH i wybierz LISTĘ. W źródło danych nie możesz wpisać, ani wskazać zakresu z: “AGD, meble, oświetlenie” ponieważ Excel potraktowałby to jako elementy listy. Zamiast tego wykorzystaj dodatkową funkcję która niejako “przetłumaczy” wcześniej nazwany zakres komórek (np. AGD) na konkretny zakres komórek, w której znajdują się towary z tej właśnie grupy. Jako argument tej funkcji musisz oczywiście wskazać komórkę, w której umieściłeś listę rozwijaną z grupami towarów. Zatwierdzasz i gotowe! Wybierając z listy rozwijanej grupa towarów “Oświetlenie”, zobaczysz tylko produkty, które należą do tej kategorii. Podsumowanie Ten artykuł pokazał Ci jak wiele ciekawych funkcjonalności kryje w sobie prosta lista rozwijana. Jak w większości przypadków, połączenie jej z innymi funkcjonalnościami Excela daje najlepsze efekty. Listy są często wykorzystywane, w tworzeniu interaktywnych raportów i dashboardów. Mam nadzieję, że uda Ci się wykorzystać opisywane tu możliwości w Twoich zestawieniach i raportach. Opisz proszę w komentarzu, do czego Ty wykorzystujesz listy rozwijane.. 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 Spisie treści Jak utworzyć listę rozwijaną w programie Excel? Tworzenie dynamicznej listy rozwijanej w programie Excel (przy użyciu PRZESUNIĘCIA) Kopiuj listy rozwijane wklejania w programie Excel Ostrożność podczas pracy z listą rozwijaną programu Excel Jak wybrać wszystkie komórki, które mają w sobie listę rozwijaną? Tworzenie zależnej/warunkowej listy rozwijanej programu Excel Lista rozwijana to doskonały sposób, aby dać użytkownikowi możliwość wyboru z predefiniowanej listy. Może być używany podczas nakłaniania użytkownika do wypełnienia formularza lub podczas tworzenia interaktywnych kokpitów Excel. Listy rozwijane są dość powszechne na stronach internetowych/aplikacjach i są bardzo intuicyjne dla użytkownika. Obejrzyj wideo - tworzenie listy rozwijanej w programie Excel W tym samouczku dowiesz się, jak utworzyć listę rozwijaną w programie Excel (zajmuje to tylko kilka sekund) wraz ze wszystkimi niesamowitymi rzeczami, które możesz z nią zrobić. W tej sekcji poznasz dokładne kroki tworzenia listy rozwijanej w programie Excel: Korzystanie z danych z komórek. Ręczne wprowadzanie danych. Korzystanie z formuły PRZESUNIĘCIE. #1 Używanie danych z komórek Załóżmy, że masz listę przedmiotów, jak pokazano poniżej: Oto kroki, aby utworzyć listę rozwijaną programu Excel: Wybierz komórkę, w której chcesz utworzyć listę rozwijaną. Przejdź do Dane -> Narzędzia danych -> Walidacja danych. W oknie dialogowym Sprawdzanie poprawności danych, na karcie Ustawienia, wybierz Lista jako kryteria sprawdzania poprawności. Po wybraniu opcji Lista pojawi się pole źródłowe. W polu źródłowym wprowadź = $ A $ 2: $ A $ 6 lub po prostu kliknij w polu Źródło i wybierz komórki za pomocą myszy i kliknij OK. Spowoduje to wstawienie listy rozwijanej w komórce C2. Upewnij się, że opcja rozwijana w komórce jest zaznaczona (która jest zaznaczona domyślnie). Jeśli ta opcja nie jest zaznaczona, komórka nie wyświetla listy rozwijanej, jednak możesz ręcznie wprowadzić wartości na liście. Notatka: Jeśli chcesz tworzyć listy rozwijane w wielu komórkach za jednym razem, zaznacz wszystkie komórki, w których chcesz je utworzyć, a następnie wykonaj powyższe kroki. Upewnij się, że odwołania do komórek są bezwzględne (takie jak $A$2), a nie względne (takie jak A2 lub A$2 lub $A2). #2 Wprowadzając dane ręcznie W powyższym przykładzie odwołania do komórek są używane w polu Źródło. Możesz również dodawać elementy bezpośrednio, wprowadzając je ręcznie w polu źródłowym. Załóżmy na przykład, że chcesz wyświetlić dwie opcje, Tak i Nie, na liście rozwijanej w komórce. Oto jak możesz bezpośrednio wpisać go w polu źródła walidacji danych: Spowoduje to utworzenie listy rozwijanej w wybranej komórce. Wszystkie pozycje wymienione w polu źródłowym, oddzielone przecinkami, są wymienione w różnych wierszach w rozwijanym menu. Wszystkie pozycje wprowadzone w polu źródłowym, oddzielone przecinkiem, są wyświetlane w różnych wierszach na liście rozwijanej. Notatka: Jeśli chcesz tworzyć listy rozwijane w wielu komórkach za jednym razem, zaznacz wszystkie komórki, w których chcesz je utworzyć, a następnie wykonaj powyższe kroki. #3 Korzystanie z formuł Excela Oprócz wybierania komórek i ręcznego wprowadzania danych możesz również użyć formuły w polu źródłowym, aby utworzyć listę rozwijaną programu Excel. Dowolna formuła zwracająca listę wartości może służyć do tworzenia listy rozwijanej w programie Excel. Załóżmy na przykład, że masz zestaw danych, jak pokazano poniżej: Oto kroki, aby utworzyć listę rozwijaną programu Excel za pomocą funkcji PRZESUNIĘCIE: Spowoduje to utworzenie listy rozwijanej zawierającej wszystkie nazwy owoców (jak pokazano poniżej). Notatka: Jeśli chcesz utworzyć listę rozwijaną w wielu komórkach za jednym razem, zaznacz wszystkie komórki, w których chcesz ją utworzyć, a następnie wykonaj powyższe kroki. Upewnij się, że odwołania do komórek są bezwzględne (takie jak $A$2), a nie względne (takie jak A2 lub A$2 lub $A2). Jak działa ta formuła? W powyższym przypadku do stworzenia listy rozwijanej użyliśmy funkcji PRZESUNIĘCIE. Zwraca listę przedmiotów z ra Zwraca listę elementów z zakresu A2:A6. Oto składnia funkcji PRZESUNIĘCIE: = PRZESUNIĘCIE(odwołanie, wiersze, kolumny, [wysokość], [szerokość]) Przyjmuje pięć argumentów, w których podaliśmy referencję jako A2 (punkt początkowy listy). Wiersze/kolumny są określone jako 0, ponieważ nie chcemy przesunąć komórki odniesienia. Wysokość jest określona jako 5, ponieważ lista zawiera pięć elementów. Teraz, gdy używasz tej formuły, zwraca ona tablicę zawierającą listę pięciu owoców w A2:A6. Zauważ, że jeśli wprowadzisz formułę w komórce, zaznaczysz ją i naciśniesz F9, zobaczysz, że zwraca ona tablicę nazw owoców. Tworzenie dynamicznej listy rozwijanej w programie Excel (przy użyciu PRZESUNIĘCIA) Powyższa technika używania formuły do ​​tworzenia listy rozwijanej może zostać rozszerzona, aby utworzyć również dynamiczną listę rozwijaną. Jeśli użyjesz funkcji PRZESUNIĘCIE, jak pokazano powyżej, nawet jeśli dodasz więcej pozycji do listy, lista rozwijana nie zaktualizuje się automatycznie. Będziesz musiał ręcznie zaktualizować go za każdym razem, gdy zmienisz listę. Oto sposób na uczynienie go dynamicznym (i to nic innego jak drobna poprawka w formule): Wybierz komórkę, w której chcesz utworzyć listę rozwijaną (w tym przykładzie komórka C2). Przejdź do Dane -> Narzędzia danych -> Walidacja danych. W oknie dialogowym Sprawdzanie poprawności danych, na karcie Ustawienia, wybierz Lista jako kryteria sprawdzania poprawności. Po wybraniu opcji Lista pojawi się pole źródłowe. W polu źródłowym wprowadź następującą formułę: =OFFSET($A$2,0,0,COUNTIF($A$2:$A$100"")) Upewnij się, że opcja rozwijana w komórce jest zaznaczona. Kliknij OK. W tej formule zastąpiłem argument 5 argumentem Funkcja zlicza niepuste komórki w zakresie A2:A100. W związku z tym funkcja PRZESUNIĘCIE dostosowuje się, aby uwzględnić wszystkie niepuste komórki. Notatka: Aby to zadziałało, NIE MOŻE być żadnych pustych komórek pomiędzy wypełnionymi komórkami. Jeśli chcesz utworzyć listę rozwijaną w wielu komórkach za jednym razem, zaznacz wszystkie komórki, w których chcesz ją utworzyć, a następnie wykonaj powyższe kroki. Upewnij się, że odwołania do komórek są bezwzględne (takie jak $A$2), a nie względne (takie jak A2 lub A$2 lub $A2). Kopiuj listy rozwijane wklejania w programie Excel Możesz skopiować i wkleić komórki z walidacją danych do innych komórek, a także skopiuje walidację danych. Na przykład, jeśli masz listę rozwijaną w komórce C2 i chcesz ją zastosować również do C3:C6, po prostu skopiuj komórkę C2 i wklej ją w C3:C6. Spowoduje to skopiowanie listy rozwijanej i udostępnienie jej w C3:C6 (wraz z listą rozwijaną skopiuje również formatowanie). Jeśli chcesz tylko skopiować listę rozwijaną, a nie formatowanie, oto kroki: Spowoduje to skopiowanie tylko listy rozwijanej, a nie formatowania skopiowanej komórki. Ostrożność podczas pracy z listą rozwijaną programu Excel Musisz zachować ostrożność podczas pracy z listami rozwijanymi w programie Excel. Gdy kopiujesz komórkę (niezawierającą listy rozwijanej) do komórki zawierającej listę rozwijaną, lista rozwijana jest tracona. Najgorsze jest to, że Excel nie wyświetli żadnego ostrzeżenia ani monitu, aby poinformować użytkownika, że ​​lista rozwijana zostanie nadpisana. Jak wybrać wszystkie komórki, które mają w sobie listę rozwijaną? Czasami trudno jest określić, które komórki zawierają listę rozwijaną. Dlatego warto oznaczyć te komórki, nadając im wyraźną ramkę lub kolor tła. Zamiast ręcznie sprawdzać wszystkie komórki, istnieje szybki sposób na wybranie wszystkich komórek, które mają w sobie listy rozwijane (lub dowolną regułę sprawdzania poprawności danych). Spowoduje to natychmiastowe wybranie wszystkich komórek, do których zastosowano regułę sprawdzania poprawności danych (dotyczy to również list rozwijanych). Teraz możesz po prostu sformatować komórki (nadać obramowanie lub kolor tła), aby były widoczne wizualnie i przypadkowo nie skopiowały na nią innej komórki. Oto kolejna technika Jona Acampory, której możesz użyć, aby zawsze wyświetlać ikonę strzałki w dół. Możesz również zobaczyć kilka sposobów na zrobienie tego w tym wideo autorstwa pana Excela. Tworzenie zależnej/warunkowej listy rozwijanej programu Excel Oto wideo na temat tworzenia zależnej listy rozwijanej w programie Excel. Jeśli wolisz czytać niż oglądać wideo, czytaj dalej. Czasami możesz mieć więcej niż jedną listę rozwijaną i chcesz, aby elementy wyświetlane w drugiej liście były zależne od tego, co użytkownik wybrał w pierwszej liście rozwijanej. Są to tak zwane listy rozwijane zależne lub warunkowe. Poniżej znajduje się przykład rozwijanej listy warunkowej/zależnej: W powyższym przykładzie, gdy pozycje wymienione w „Rozwijanej 2” są zależne od wyboru dokonanego w „Rozwijanej 1”. Zobaczmy teraz, jak to stworzyć. Oto kroki, aby utworzyć zależną / warunkową listę rozwijaną w programie Excel: Teraz, gdy dokonasz wyboru na liście rozwijanej 1, opcje wymienione na liście rozwijanej 2 zostaną automatycznie zaktualizowane. Pobierz przykładowy plik Jak to działa? - Warunkowa lista rozwijana (w komórce E3) odwołuje się do = Oznacza to, że po wybraniu „Owoce” w komórce D3 rozwijana lista w E3 odwołuje się do nazwanego zakresu „Owoce” (poprzez funkcję a zatem wyświetla wszystkie elementy w tej kategorii. Ważna uwaga podczas pracy z warunkowymi listami rozwijanymi w programie Excel: Po dokonaniu wyboru, a następnie zmianie listy rozwijanej rodzica, lista rozwijana zależna nie zmieni się i dlatego będzie błędnym wpisem. Na przykład, jeśli jako kraj wybierzesz Stany Zjednoczone, a następnie jako stan wybierzesz Floryda, a następnie wrócisz i zmienisz kraj na Indie, stan pozostanie Floryda. Oto świetny samouczek Debry dotyczący czyszczenia zależnych (warunkowych) list rozwijanych w programie Excel po zmianie wyboru. Jeśli główna kategoria zawiera więcej niż jedno słowo (na przykład „Owoce sezonowe” zamiast „Owoce”), należy użyć formuły =INDIRECT(SUBSTITUTE(D3”, „”_”)), zamiast prosta funkcja pokazana powyżej. Powodem tego jest to, że Excel nie zezwala na spacje w nazwanych zakresach. Dlatego podczas tworzenia nazwanego zakresu przy użyciu więcej niż jednego słowa program Excel automatycznie wstawia podkreślenie między słowami. Tak więc nazwany zakres „Owoce sezonowe” to „Owoce sezonowe”. Użycie funkcji SUBSTITUTE w ramach funkcji zapewnia, że ​​spacje są zamienione na podkreślenia. W dzisiejszym poście nauczymy się jak stworzyć rozrastająco się listę rozwijaną w Excelu. Załóżmy, że mamy listę kilku sprzedawców a chcemy mieć możliwość dopisania do niej sprzedawcy spoza naszej listy, który automatycznie zostanie dołączony do listy rozwijanej. Zagadnienie to omówimy na przykładowych danych z rysunku nr 1. rys. nr 1 — Przykładowe dane Przede wszystkim musimy stworzyć sobie listę unikatowych sprzedawców, aby żaden z nich się nie powtórzył. Aby stworzyć listę unikatowych wartości, używam funkcji Pierwszym argumentem funkcji jest Zakres, czyli od komórki powyżej naszej formuły do komórki powyżej naszej formuły. Wpisujemy zakres $E$1:E1, pierwsza wartość blokujemy bo jest to wartość skrajna a druga ma nam się rozrastać w dół. Drugim argumentem funkcji są kryteria, czyli wszystkie wartości z naszej tabeli (wartości z listy sprzedawców). Zapis funkcji będzie wyglądał następująco: = Zatwierdzamy funkcję. Jako początkowy wynik otrzymujemy 0. Możemy sobie rozwinąć ten wynik za pomocą skrótu klawiszowego F9, aby otrzymać ={0;0;0;0;0}. Taki wynik jest następstwem tego, że żaden sprzedawca nie pojawił się w komórce powyżej aktualnej komórki z formułą. Pożądany efekt uzyskamy dodając kolejne kroki, bo chcemy aby nasza formuła zwróciła nam kolejnego sprzedawcę. Musimy skorzystać z funkcji która będzie szukała wartości 0 w zakresie z funkcji na zasadzie dokładnego dopasowania. Zapis funkcji będzie wyglądał następująco: = czyli funkcja znajdzie pierwszą wartość 0 jaka się pojawi. Musimy pamiętać, że jest to formuła tablicowa i należy ją zatwierdzić klawiszami Ctrl+Shift+Enter. Otrzymamy wartość 1, ale nas nie interesuje cyfra, tylko wartość jaka znajduje się na pierwszej pozycji (nazwa sprzedawcy). Aby uzyskać taki wynik musimy użyć funkcji INDEKS po kolumnie ze sprzedawcami. Zapis formuły będzie wyglądał następująco: =INDEKS(tSprzedawcy[Sprzedawca] zatwierdzamy Ctrl+Shift+Enter. Tak przygotowaną formułę przeciągamy w dół (rys. nr 2). rys. nr 2 — funkcja INDEKS Dzięki tej formule otrzymujemy listę sprzedawców bez duplikatów, nie uwzględnia ona powtarzających się wartości. Robimy ją tak długą, żeby mieć pewność, że nie zabraknie nam w niej miejsca. Jednak musimy ją sobie ograniczyć tylko do prawidłowych wartości. Możemy to zrobić za pomocą funkcji Pierwszym argumentem funkcji jest Zakres, czyli komórki E2:E13, musimy pamiętać zablokować bezwzględnie zakres za pomocą skrótu klawiszowego F4. Drugim argumentem to szukane wartości, czyli "?*". Są to dwa symbole wieloznaczne w Excelu, wykorzystywane między innymi w funkcji Zapis formuły będzie wyglądał następująco: = "?*") Zapis ten oznacza, że szukamy komórki w której pojawi się przynajmniej jeden z tych znaków. Jak sobie zatwierdzimy formułę, otrzymamy cyfrę 4 (ilość pozycji spełniających warunek). Funkcja ta nie zwraca uwagę na wartość 0 i na błędy (rys. nr 3). rys. nr 3 — Funkcja Listę rozwijaną tworzy się często za pomocą funkcji PRZESUNIĘCIE, ale ta funkcja jest nietrwała i często się przelicza. My stworzymy listę rozwijaną za pomocą funkcji INDEKS, która zwraca odwołanie do zakresu (blokujemy za pomocą klawisza F4) i numer wiersza G2. Zapis formuły będzie wyglądał następująco: =$E$2:INDEKS($E$2:$E$13;$G$2). Funkcja INDEKS zwróci nam ostatnią prawidłową wartość – w naszym przypadku "Wojciech" (rys. nr 4). rys. nr 4 — Podejrzenie formuły klawiszem F9 W formule otrzymujemy zakres od komórki E2 do wyniku funkcji INDEKS, czyli jeśli podejrzymy sobie wyniki formuły za pomocą klawisza F4 to okazuje się, że wynikiem są nazwy wszystkich unikalnych sprzedawców (rys. nr 5). rys. nr 5 — Podejrzenie wyników formuły za pomocą klawisza F4 Końcowy zapis formuły będzie wyglądał następująco: =$E$2:INDEKS($E$2:$E$13; "?*")) Zastąpiliśmy odwołanie do komórki G2, zapisem formuły, z której powstał tam wynik. Otrzymaliśmy cały zbudowany zakres. Formułę tą musimy skopiować w trybie edycji komórki. Korzystając ze skrótu klawiszowego Ctrl+F3 przechodzimy do okna Menedżera nazw (rys. nr 6) rys. nr 6 — okno Menedżer nazw Kolejnym krokiem jest stworzenie nowej nazwy. Zmieniamy nazwę na Sprzedawcy, a następnie w miejscu Odwołuje się do wkleić naszą skopiowaną formułę (rys. nr 7). Zatwierdzamy klikając OK. rys. nr 7 — Okno Nowa nazwa Zamykamy Menedżera nazw. Następnie zaznaczamy kolumnę z nazwami sprzedawców i wybieramy polecenie Poprawność danych z karty Dane (rys. nr 8). rys. nr 8 — polecenie Poprawność danych Otworzy nam się okno Sprawdzanie poprawności danych, gdzie jako kryterium poprawności wybieramy Listę, a jako źródło ustawiamy nazwę Sprzedawcy(rys. nr 9). rys. nr 9 — Sprawdzanie poprawności danych Aby wybrać źródło, rozwijamy okno Wklejanie nazwy za pomocą klawisza F3 i wybieramy nazwę naszego zakresu (rys. nr 10). rys. nr 10 — Okno Wklejanie nazwy Zatwierdzamy przyciskiem OK. W efekcie przy każdej komórce z nazwą sprzedawcy utworzyła nam się lista rozwijana. rys. nr 11 — Listy rozwijane w każdej komórce Zapomniałem zrobić jednej ważnej rzeczy przy sprawdzaniu poprawności danych, mianowicie w karcie Alert o błędzie musimy odznaczyć Pokazuj alerty po wprowadzeniu nieprawidłowych danych (rys. nr 12). Przypomniałem sobie o tej funkcjonalności oglądając, któryś z filmów Maliny z malinowego bloga o Excelu. Musimy pamiętać o tej czynności, ponieważ chcemy mieć możliwość wprowadzania dodatkowych wartości spoza listy. rys. nr 12 — Pokazuj alerty po wprowadzeniu nieprawidłowych danych Teraz kiedy dopiszemy kolejnego sprzedawcę, będzie on uwzględniony w liście rozwijanej w każdej komórce z nazwami sprzedawców. Możemy budować sobie listę, wybierać sprzedawców z list i wstawiać ich jeszcze raz lub dodawać nowych. Książka Mistrz Excela + promo na 35 urodziny Chcę Cię poinformować, że w końcu udało mi zebrać środki i dopiąć wszystkich formalności, żeby powstało II wydanie mojej książki Mistrz Excela (zostałem wydawcą) II wydanie jest wzbogacone o rozdział (nr 22) wprowadzający w genialny dodatek (Power Query) do Excela służący do pobierania, łączenia i wstępnej obróbki danych z wielu źródeł. Książka Mistrz Excela to historia Roberta, który musi poznać dobrze Excela na potrzeby nowej pracy. Książka jest napisana w formie rozmów Roberta z trenerem, dzięki temu jest przystępniejsza w odbiorze niż standardowe książki techniczne pisane językiem "wykładowym". Rozmowy zostały podzielone na 22 tematyczne rozdziały, które krok po kroku wprowadzają Cię w tajniki Excela. Robert zaczyna naukę od poznania ciekawych aspektów sortowania i filtrowania danych w Excelu, przechodzi przez formatowanie warunkowe, tabele przestawne, funkcje wyszukujące i wiele innych tematów, by na koniec poznać wstępne informacje o VBA i Power wszystko to na praktycznych przykładach i z dużą ilością zdjęć. Żebyś mógł śledzić postępy Roberta, do książki dołączone są pliki Excela, na których pracuje Robert. Aktualnie w promocji urodzinowej możesz mieć Mistrza Excela w obniżonej cenie, jeśli tylko wpiszesz kod 35URODZINY Na powyższej stronie znajdziesz dokładniejszy opis książki, opinie osób, które kupiły I wydanie oraz podgląd pierwszego rozdziału książki, żeby upewnić się, czy forma rozmów przy nauce Excela jest dla książka Ci się spodoba poinformuj o niej swoich znajomych. W ramach promocji na moje 35 urodziny możesz też mieć każdy z moich kursów wideo na Udemy za zaledwie 35 zł. Linki do kursów zamieszczam poniżej. W każdym kursie są udostępnione filmy do podglądu, byś mógł się przekonać czy dany kurs jest dla Ciebie. Power Query Mistrz Excela Dashboardy Mistrz Formuł VBA Microsoft Power BI

excel jak zrobić listę rozwijaną