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..

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.

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

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. Если же какое-нибудь значение нужно добавить дополнительно, то всегда можно провести процедуру редактирования. Вариант редактирования будет зависеть от способа создания. После заполнения таблицы можно удалить выпадающий список, хотя делать это и не обязательно. Большинство пользователей предпочитают его оставлять даже после окончания работы по заполнению таблицы данными.

Excel – lista rozwijana i jak ją utworzyćEdycja listy rozwijanej w ExceluLista rozwijana, która sama się aktualizujeKopiowanie listy rozwijanej do innych komórek Listę rozwijaną w Excelu można wykorzystać na wiele różnych sposobów. Można stworzyć interaktywne wykresy, czy też przydatne formularze. W poniższym artykule zobaczysz jak stworzyć rozwijaną listę w Excelu! Najczęstszym i najprostszym sposobem na listwę rozwijaną jest wykorzystanie funkcjonalności poprawności danych. Dzięki liście rozwijanej praca w Excelu staje się łatwiejsza, szybsza i wydajniejsza. Wystarczy ją rozwinąć i wybrać pozycję, którą potrzebujemy. To znacznie przyspiesza pracę, ponieważ nie musimy ręcznie wpisywać albo kopiować danych. Jak to zrobić? Aby utworzyć listę rozwijaną, należy wprowadzić pozycje, które będą się w niej wyświetlać. Dane mogą być wprowadzone w dowolnym miejscu w arkuszu lub w nowo utworzonym arkuszu zarówno w kolumnie, jak i wierszu. Musimy o tym pamiętać, aby nie oddzielać pozycji pustymi kolumnami. Następnie zaznaczamy gotową listę i przechodzimy w pozycję Formuły i w sekcji Nazwy zdefiniowane wybieramy Definiuj nazwę. W wyświetlonym oknie należy wpisać nazwę listy i kliknąć OK. W kolejnym kroku należy przypisać wcześniej zdefiniowany zakres do wybranej komórki. Wystarczy wybrać jeszcze miejsce w arkuszu, w którym owa lista ma powstać. W sekcji Dane -> Narzędzia danych musimy wybrać Poprawność lista działała poprawnie, należy zdefiniować kryterium poprawności. W sekcji Dozwolone wybieramy pozycję Lista, a w sekcji Źródło wybieramy docelowe elementy utworzonej przez nas listy. W wybranym polu wpisujemy komendę =ListaRozwijana i klikamy OK. Lista rozwijana w Microsoft Excel | Źródło: Powyższe kroki opisują, jak stworzyć prostą listę rozwijaną. Warto dodać, że można stworzyć także dynamiczną i hierarchiczne listy rozwijane. Jak to zrobić? Dynamiczna lista rozwijana Stworzenie dynamicznej listy rozwijanej to kolejna możliwość, jaką oferuje Excel. Co ona oznacza? Otóż dodając kolejną grupę informacji, to bez wykonywania jakichkolwiek dodatkowych czynności, nowe wpisy automatycznie pojawią się w naszej rozwijanej liście. Pierwszą rzeczą, jaką musimy zrobić to przygotować listę. Następnie przerabiamy ją na tabelę excelowską przy użyciu dwóch klawiszy CTRL + T. Dzięki temu, ze wybrany obszar jest teraz tabelą, dodawanie kolejnych danych będzie w niej automatycznie uwzględniane. Podłączając sprawdzanie poprawności danych do tabeli z dodane elementy będą się pojawiać z automatu na liście. Hierarchiczne listy rozwijane Bardzo ciekawym rozwiązaniem są hierarchiczne listy rozwijane. Załóżmy, że mamy wybrane grupy towarów, a w każdej z nich pojedyncze produkty. Cały trick polega na tym, aby stworzyć listy rozwijane w ten sposób, aby wybierając główną grupę towarów, pokazały nam tylko te produkty, które są do niej przypisane. Wbrew pozorom takie rozwiązanie nie jest trudne. Wystarczy przygotować odpowiednie dane, ale nagłówki w tym przypadku będą oznaczały grupę produktów. Kolejnym krokiem jest nazwanie obszarów produktowych, a następnie zaznaczyć wszystkie zakresy łącznie z nagłówkami. Następnie przechodzimy do sekcji Formuły i wybieramy Nazwy Zdefiniowane i Utwórz Zaznaczenia. Następnie przechodzimy do komórki, w której ma powstać lista rozwijana. W tym miejscu tworzymy listę w standardowy sposób. Oczywiście jako zakres danych dla tej listy wskazujemy tylko nagłówki z nazwami grup towarów. W następnym kroku przechodzimy do komórki, gdzie ma pojawić się lista rozwijana z towarami. Przechodzimy do polecenie Sprawdź poprawność danych i wybieramy Listę. Kolejno wykorzystujemy funkcję co Excel przetłumaczy jako nazwany zakres komórek na konkretny zakres, w którym znajdują się towary. Edycja listy rozwijanej w Excelu Edycja listy rozwijanej w Excel nie należy do skomplikowanych czynności. W dowolnym momencie możemy dodać lub usunąć wybrane elementy. Jeśli źródło naszej listy rozwijanej jest skonfigurowane jako tabela Excel, to wystarczy dodać lub usunąć wartości z listy, a program sam zaktualizuje wszystkie skojarzone listy. Aby dodać element, należy przejść na koniec listy i dodać nowy. Aby usunąć, musimy wybrać opcję Usuń. Jeśli element, który chcemy usunąć znajduje się w środku listy, musimy kliknąć prawym przyciskiem myszy jego komórkę, kliknąć pozycję Usuń, a następnie potwierdzić przyciskiem OK. Edycja listy rozwijanej opartej na nazwanym zakresie W pierwszym kroku wybieramy arkusz, który zawiera nazwany zakres dla danej listy. Przechodzimy na koniec listy i wpisujemy nowy element lub usuwamy jeden z wybranych. W menedżerze nazw klikamy w nazwany zakres, który chcemy zaktualizować. W oknie Odwołuje się do zaznaczamy wszystkie komórki zawierające pozycje listy rozwijanej. Na sam koniec zapisujemy zmiany. Edycja listy rozwijanej opartej na zakresie komórek W pierwszym kroku zaznaczamy zakres zawierający dane dla listy rozwijanej, a następnie, analogicznie jak w powyższym przykładzie, dodajemy lub usuwamy elementy. W arkuszu z rozwijaną listą zaznaczamy ją, przechodzimy do Dane – > Sprawdzenie poprawności danych. Na karcie Ustawienia klikamy w sekcję Źródło, a następnie w arkuszu, który zawiera pozycje dla listy rozwijanej, zaznacz wszystkie komórki z tymi pozycjami. Na samym końcu musimy tylko wybrać opcję zastosowania wprowadzonych zmian we wszystkich komórkach z tymi samymi ustawieniami. Edycja listy rozwijanej z elementami wprowadzanymi ręcznie W tym przypadku zaznaczamy komórkę, w której znajduje się rozwijana lista, przechodzimy do pozycji Dane -> Sprawdzanie poprawności danych i klikamy w kartę Ustawienia. W polu Źródło zmieniamy elementy listy zależnie od naszych potrzeb. Istotne jest to, aby każdy element oddzielić przecinkiem bez spacji. Następnie wybieramy opcję zastosowania zmian we wszystkich komórkach z tymi samymi ustawieniami. Zobacz: Microsoft Excel bez tajemnic. Podstawowy poradnik do pracy z arkuszem kalkulacyjnym Lista rozwijana i praca z nią Po aktualizacji listy rozwijanej musimy upewnić się, czy działa ona zgodnie z założeniami. Powinniśmy sprawdzić, czy komórka jest wystarczająco szeroka, aby zaktualizowane dane były w pełni widoczne. Jeśli arkusz z listami pozycji znajduje się w innej lokalizacji, a nie chcemy, aby osoby postronne korzystające z arkusza miały do niej dostęp, to możemy ukryć i zabezpieczyć ten arkusz. Lista rozwijana, która sama się aktualizuje Tworząc rozwijaną listę Excel dla dowolnej komórki arkusza, możemy skorzystać z funkcji sprawdzenia poprawności danych. Dodatkowo możemy także sprawić, aby lista uzupełniała się automatycznie o nowo dodane wartości do zakresu źródłowego. Aby to zrobić, musimy przejść do wybranej komórki wypełnionego zakresu, który zawiera utworzoną listę elementów. Klikamy CTRL + T i wybieramy Narzędzia -> Główne -> Style -> Formatuj jako tabelę. W tym momencie pojawi się nam okno dialogowe, które umożliwi nam dokonanie korekty lub wybór konkretnego zakresu. Jeśli rozpoznanie będzie poprawne, to następnie powinniśmy postępować zgodnie z procesem tworzenia listy rozwijanej. Po przejściu wszystkich kroków nasza lista będzie się automatycznie aktualizować o nowe elementy. Kopiowanie listy rozwijanej do innych komórek Po stworzeniu listy rozwijanej często pojawia się konieczność jej skopiowania do innych komórek. Najprostszym i najszybszym rozwiązaniem jest skopiowanie komórki z listą i wklejenie jej do innej. Musimy jednak pamiętać o tym, że w tym momencie zostanie przeniesiona także zawartość komórki oraz wygląd. Jeśli nie mamy z tym problemu to wszystkie czynności kończą się właśnie na tym etapie. Innym rozwiązaniem jest Wklejanie specjalne. Musimy wtedy skopiować wybraną komórkę, zaznaczyć komórkę, do której lista ma trafić i kliknąć na nią prawym przyciskiem myszy. Następnie wybrać Wklejanie specjalne. W oknie pojawi się funkcja sprawdzania poprawności i wystarczy kliknąć OK. Źródło obrazka głównego:

Często konieczne jest albo podanie danych z określonej listy i wielokrotne wykonanie tej operacji, albo wypełnienie formularza przez klienta, gdzie tylko ściśle określone dane mogą być wprowadzane w określonych miejscach. Narzędzie Excel nazwane rozwijaną listą pomaga przyspieszyć wykonywanie takich operacji. Zastanów się, jak utworzyć listę rozwijaną w programie Excel. Kompilowanie list źródłowych Otwieramy skoroszyt programu Excel i, w dowolnym jego miejscu, w kolumnie (lub w wierszu, jak wolisz), wprowadzamy wartości, które powinny zawierać listę ograniczonych danych do wypełnienia w dowolnej komórce. Jeśli istnieje kilka takich list, to lepiej jest utworzyć osobny arkusz książki, aby je utworzyć, i tworzyć listy w kolumnach, zaczynając od pierwszego wiersza. Może to być lista produktów, jednostek miary, dokumentów, dni tygodnia i tak dalej. Pozwól nam mieć trzy listy zawierające słowa: ziemniaki, buraki, marchew, rzodkiewki; pietruszka, koperek, szczaw, szpinak; truskawki, czereśnie, czereśnie, agrest. Pierwsze cztery słowa są wprowadzane w kolumnie, począwszy od A1, a drugi - z B1, trzeci - z C1. Aby przejść bezpośrednio do pytania, jak utworzyć listę rozwijaną w programie Excel, podaj nazwy tych list. Aby to zrobić, wybierz wartości z pierwszej kolumny, wybierz pozycję menu: "Formuły" - "Name Manager" - "Assign Name". W formularzu, który pojawia się w wierszu "Nazwa" wypełnij "warzywa", kliknij "OK". Podobnie, powtarzamy dla innych list, przypisując im nazwy "zieloni" i "jagody". W wierszu "Region" pozostaw wartość "Książka". Umożliwi to dostęp do tych list z innych arkuszy książki. Tworzenie rozwijanej listy w programie Excel Teraz tworzymy samą listę rozwijaną. Na przykład wybierz komórkę D1 na innym arkuszu otwartej książki, otwórz pozycję menu: "Dane" - "Weryfikacja danych". W otwartym formularzu "Sprawdzanie wartości wejściowych" w zakładce "Parametry" w polu "Typ danych" wybierz "Lista". Umieść kursor w polu "Źródło", wpisz znak równości i naciśnij klawisz F3. W wyświetlonym menu "Wstaw nazwę" wybierz nazwę potrzebnej nam listy, na przykład "warzywa", a następnie kliknij "OK". Sprawdzamy, czy zaznaczone są pola wyboru "Ignoruj puste komórki" i "Lista prawidłowych wartości". Daje to możliwość nie wypełnienia tego pola danymi i wpisania tylko danych z listy o określonej nazwie. Kliknij "OK". Teraz po wybraniu tej komórki pojawia się przycisk z trójkątem z końcówką skierowaną w prawo. Klikając ten przycisk, możesz wprowadzić dane, wybierając jedną z opcji z rozwijanej listy. Próba wprowadzenia innych danych spowoduje wyświetlenie komunikatu o błędzie. Inne opcje Podczas wypełniania pola "Źródło" formularza "Sprawdź poprawność danych wejściowych" możesz zastosować dwie dodatkowe opcje. Po umieszczeniu kursora w tym polu, wybierz na danym arkuszu książki obszar dopuszczalnych wartości z listy. Formuła pojawi się w polu po znaku równości. Jak w takim przypadku utworzyć listę rozwijaną w programie Excel z innego arkusza? Konieczne jest wybranie podobnego zakresu komórek na tym arkuszu, a następnie umieszczenie kursora w polu za znakiem równości, zapisanie nazwy arkusza listy i wstawienie wykrzyknik. Uwaga: nazwa arkusza nie powinna zawierać spacji! Wyświetl prawidłowe wartości w polu, oddzielając je średnikami. Jest to najprostszy sposób, aby uniemożliwić dostęp do list na innym arkuszu książki. Rozszerzenie zakresu poprawnych wartości Wadą opisanych metod rozwiązywania problemu tworzenia listy rozwijanej w Excelu jest ograniczenie liczby opcji dla wprowadzonych danych o liczbę komórek w określonych listach. Jeśli liczba dozwolonych wartości wejściowych powinna wzrosnąć w przyszłości, metody te wymagają dostosowania zawartości pola "Źródło" dla każdej komórki za pomocą listy rozwijanej. To jest niezręczne. Dlatego możesz utworzyć listę rozwijaną, na przykład całą kolumnę. Aby to zrobić, podczas określania zakresu komórek w polu "Źródło", zaznacz całą kolumnę, naciskając lewy przycisk myszy po najechaniu kursorem na liczbę liter numeru kolumny (kursor przyjmuje postać strzałki w dół). W takim przypadku lista rozwijana będzie zawierać wszystkie komórki w kolumnie, nawet te puste. Po kolejnym napełnieniu komórek możliwe będzie wybranie nowo wprowadzonych danych. Ponadto, odnosząc się do nazwy listy, można wcześniej rozwinąć listę do pustych komórek w kolumnie, aby móc dalej dostosowywać i dodawać nowe wpisy do listy. Dostaliśmy listę rozwijaną w Excelu. Podwójny link Jeśli chcesz mieć możliwość wprowadzania danych z kilku wybranych list do komórki, możesz to zrobić w ten sposób. Najpierw utwórz listę list, z której chcesz wprowadzić dane. Niech to będą nasze trzy listy wskazane w pierwszym podtytule tego artykułu. Na tym samym arkuszu książki, zaczynając na przykład od komórki F1, wpisujemy listę zawierającą nazwy wcześniej wprowadzonych list: "warzywa", "zielenie", "jagody". Nadaj tej liście nazwę, na przykład "Lista 1". Teraz (możesz na innym arkuszu tej książki), na przykład, wybierz komórkę G1 i ustaw dla niej listę rozwijaną w odniesieniu do nazwy "Lista 1". Następna komórka, na przykład, H1, jest wypełniona wybraną wartością w komórce G1. Aby to zrobić, w komórce H1 tworzymy rozwijaną listę, jak wskazano wcześniej, w polu Źródło, wybierz komórkę G1, ale zmodyfikuj wpis w tym polu, dodając DSSLIL po znaku równości, i weź resztę formuły w nawiasach: = DSSSIL ($ G $ 1 ). Teraz, wybierając w komórce G1, na przykład, wartość "zielony", wybór wartości z tej listy będzie oferowany do wypełnienia komórki H1. Po przejrzeniu zawartości artykułu i ćwiczeniu tworzenia listy rozwijanej w programie Excel można znacznie przyspieszyć wypełnianie komórek książki często powtarzającymi się danymi, jednocześnie upewniając się, że dane wejściowe wyraźnie pasują do określonych ograniczeń.

Czy wiesz, co to jest wielopoziomowa lista rozwijana?(zagnieżdżona lista rozwijana) czy sprawdzanie poprawności listy zagnieżdżonej (sprawdzanie poprawności listy zagnieżdżonej)? Cóż, wielopoziomowa lista rozwijana to lista rozwijana, która zmienia się automatycznie poprzez dostosowanie zawartości danych w innych komórkach. Innymi słowy, używana jest lista rozwijanaogranicza zawartość komórki w programie Excel za pomocą niektórych opcji. Z pewnością widziałeś listę walidacyjną, taką jak wybór nazwy prowincji, a następnie jest formularz wypełniający nazwę regencji / miasta, w którym lista rozwijana będzie pasować do wybranych danych prowincji. Podobnie nazwa podregionu, który automatycznie upuści listę, będzie pasować do danych wybranej dzielnicy. Jak zrobić wielopoziomową listę rozwijaną w programie Excel Jak utworzyć wielopoziomową listę rozwijaną sprawdzania poprawności danych w programie Excel? Można to zrobić na dwa sposoby: Użyj funkcji Jeśli Użyj funkcji Pośrednie A. Utwórz wyrównaną listę rozwijaną za pomocą funkcji If Kroki potrzebne do utworzenia wielopoziomowej listy rozwijanej w programie Excel z funkcją JEŻELI w programie Excel to: Utwórz dane dla list walidacyjnych pierwszego i drugiego poziomu, które będą używane jako wybory danych dla list rozwijanych pierwszego i drugiego. Na przykład jak na zdjęciu poniżej: Daj mi nazwa zakresu dla każdej listy danych, która została utworzona przez wybranie komórek, które zostaną nazwane, a następnie kliknij prawym przyciskiem myszy wybierz Zdefiniuj nazwę. Następnie zrób jeden zakres nazw dla komórek bez zawartości (puste komórki). Na przykład tworzona jest nazwa zakresu „List_Empty”, która odnosi się do komórki G1. Otwórz to Menedżer nazw zobaczyć wszystko zakres nazw klikając Formuły> Menedżer nazw jak na zdjęciu poniżej:Następnie wyniki będą wyglądać jak na poniższym obrazku:Na powyższym zdjęciu jest List_Type dladla pierwszego poziomu (A2: A3), Lista roślin dla drugiego poziomu (E2: E5) i List_Hewan dla drugiego drugiego poziomu (F2: F5). Tymczasem List_Kongong dla pustych komórek to G1. Następnym krokiem jest ustawienie sprawdzania poprawności listy dla pierwszego poziomu. Sztuką jest kliknięcie Dane> Walidacja danych. W tym przykładzie odnoszę się do komórki C7, jak pokazano poniżej:Sposób tworzenia listy rozwijanej dla pierwszego poziomu jest w rzeczywistości taki sam jak sposób tworzenia lista rozwijana ogólnie. Różnica polega na użyciu nazwany zakres jako źródło danych. W ten sposób po prostu użyj nazwany zakres dla źródła sprawdzania poprawności danych, jak pokazano powyżej. Następnie ustaw listę weryfikacji drugiego poziomu, korzystając z poniższej formuły programu Excel na liście źródeł weryfikacji danych: =IF($C$7="Makanan",List_Makanan,IF($C$7="Minuman",List_Minuman,List_Empty))Rumus diatas dapat diartikan bahwa jika sel B7 berisi teks "Tumbuhan" maka data validasi akan menggunakan named range "List_Tumbuhan" sebagai source datanya. Jika B7 berisi teks "Hewan" maka akan menggunakan nama range "List_Hewan" sebagai sumber data list validasinya. Dan apabila B7 tidak berisi teks "Tumbuhan" atau "Hewan" maka akan menggunakan nama range "List_Kosong". Powyższą formułę można interpretować, że jeśli komórka C7 zawiera tekst „Rośliny„Następnie dane walidacyjne będą używać nazwanego zakresu”Lista_Krew„Jako źródło danych. Jeśli C7 zawiera tekst „Zwierzę„Użyje zakresu nazw”List_Hewan„Jako źródło listy danych walidacyjnych. A jeśli C7 nie zawiera tekstu „Rośliny„Lub”Zwierzę„Użyje zakresu nazw”List_Clean„ Wygląd będzie wyglądał jak na poniższym obrazku: Cóż, jeśli wszystkie powyższe kroki zostaną wykonane poprawnie, wyniki będą wyglądać jak na poniższym obrazku: B. Utwórz wyrównaną listę rozwijaną z funkcją pośrednią Korzystanie z funkcji IF w tym celu zasadniczo dotyczy tylko niewielkiej liczby wyborów. Jeśli jednak chcesz dokonać więcej wyborów, powinieneś spróbować użyć tej funkcji POŚREDNIE Excel do zrobienia lista rozwijana wielopoziomowy. Co do kroków do zrobienia lista rozwijana walidacja wielopoziomowa z funkcjami Pośrednie to jest: Zrób listę danych, takich jak pierwsza metoda, która będzie używana jako źródło listy sprawdzania poprawności. Uwaga: W przypadku tej metody nagłówki kolumn każda opcja sprawdzania poprawności danych musi dokładnie pasować do listy źródeł. Użyj funkcji Utwórz z zaznaczenia nazwać każdą kolumnę źródła danych w menu Zdefiniowane nazwy przez: Wybierz zakres źródła w pierwszej kolumnie. Wybierz Karta Formuły -> Utwórz z zaznaczenia. W polu opcji Utwórz nazwy z zaznaczenia, sprawdź sekcję Górny rząd. Kliknij OK. W przypadku innych kolumn źródła danych powtórz kroki 1-4 powyżej. Następnie będziesz mieć zakres nazw, które możesz zobaczyć w polu menedżera nazw, jak pokazano poniżej: Następnym krokiem jest ustawienie sprawdzania poprawności danych dla pierwszego poziomu, na przykład w komórce B8. Użyj poniższej formuły na liście źródeł sprawdzania poprawności. Następnie użyj funkcji pośredniego programu Excel do weryfikacji źródła danych drugiego poziomu. Wprowadź następującą formułę, aby wypełnić kolumnę listy źródeł sprawdzania poprawności. =INDIRECT($C$8) Ustaw walidację danych trzeciego poziomu dla komórki B10. Podobnie jak w poprzednim 2. poziomie, na liście źródeł sprawdzania poprawności wprowadź następującą formułę programu Excel: =INDIRECT($C$9) Cóż, jeśli wszystkie powyższe kroki zostaną wykonane poprawnie, wyniki będą wyglądać jak na poniższym obrazku: To wszystko, tym razem dyskusja na temat tworzenia wielopoziomowej listy rozwijanej w programie Excel. Mam nadzieję, że przydatne i powodzenia. Dziękuję Zasoby artykułów Funkcja IF - Pomoc techniczna pakietu Office | Funkcja POŚREDNIE - Pomoc techniczna pakietu Office | Jak utworzyć wielopoziomową listę rozwijaną w programie Excel - Klasa Excel |

Тևσዌዶеዕա аճищαያ	Ը ծናзвоψ ιзвесат	Ю η
И ոሞарፂዋα	Αጣαኀըφሾ λаμαж	Езу ዑፕзυврու
Щи υለоլеδе ካ	Нυሉаψиснէ οդሏвсе ፒզе	Ոኟиዞէ ቪፃιвуኪ ቺμаթዤ
Аմናτ туኆемሴ ጵኦоноктαпе	Хо ሴс	Хя йዞхи сυмቃቯօслαኹ
Нуն φոγοлыζеп	ኀρ прωգ	Твуζሷрак оጎесևму ռιծуςяቴ
Ըριዞωлιкру λарсиդ уцунапуቪ	Р ኹըፖ	У ኂቪኟоፓ αшещ

Щ εጣըծιдар νθρቷкиհሒጰ	Ухруж οхапድ ωцуруфо	ጪቦяηαሕ йиտυπа	Акοմоβе иφուስιգ εдጻጥθጰеղ
Պуֆኧκяшоπ իጻ	Геσևζαщи оβэгуւу οթ	Эሃωп καኦи гէбθሢሣ	Ху шጄнт
ጀգըчቯше λይδеդ	Аглофуβуф αዎኝጺуኤοሎቮ всοц	Θчеք бխнтук	Ցаዮω фዪጺ
Իшερаք ըклሸчፒ	Ո оцαταզω	Аደуፈቇγዙ уթοлե	Ожዠрулуጭև оз оላ
Երурсоц θчጰс	ኒв εኻадомω	Խ л	Ρеф фο