Jak zrobić listę rozwijaną w programie Excel

Lista rozwijana to niezwykle przydatne narzędzie, które może ułatwić pracę z informacjami. Umożliwia jednoczesne przechowywanie kilku wartości w komórce, z którymi można pracować, jak z każdym innym. Aby wybrać ten, którego potrzebujesz, po prostu kliknij ikonę strzałki, po czym wyświetli się lista wartości uXNUMXbuXNUMXbis. Po wybraniu konkretnego, komórka jest nim automatycznie wypełniana, a formuły na jej podstawie przeliczane.

Excel udostępnia wiele różnych metod generowania menu rozwijanych, a dodatkowo pozwala na ich elastyczne dostosowywanie. Przeanalizujmy te metody bardziej szczegółowo.

Proces tworzenia listy

Aby wygenerować wyskakujące menu, kliknij pozycje menu na ścieżce „Dane” – „Walidacja danych”. Otworzy się okno dialogowe, w którym należy znaleźć kartę „Parametry” i kliknąć ją, jeśli nie była wcześniej otwierana. Ma wiele ustawień, ale pozycja „Typ danych” jest dla nas ważna. Ze wszystkich znaczeń „Lista” jest tym właściwym.

Liczba metod wprowadzania informacji do wyskakującej listy jest dość duża.

1. Niezależne wskazanie elementów listy oddzielonych średnikiem w polu „Źródło” znajdującym się na tej samej zakładce tego samego okna dialogowego.
   

   

2. Wstępne wskazanie wartości. Pole Źródło zawiera zakres, w którym dostępne są wymagane informacje.
   

   

3. Określanie nazwanego zakresu. Metoda, która powtarza poprzednią, ale wystarczy tylko wstępnie nazwać zakres.
   

   

Każda z tych metod przyniesie pożądany rezultat. Przyjrzyjmy się metodom generowania list rozwijanych w rzeczywistych sytuacjach.

Na podstawie danych z listy

Załóżmy, że mamy tabelę opisującą rodzaje różnych owoców.

Aby utworzyć listę w menu rozwijanym na podstawie tego zestawu informacji, musisz wykonać następujące czynności:

1. Wybierz komórkę zarezerwowaną dla przyszłej listy.
2. Znajdź kartę Dane na wstążce. Tam klikamy „Zweryfikuj dane”.
   

   

3. Znajdź element „Typ danych” i zmień wartość na „Lista”.
   

   

4. W polu oznaczającym opcję „Źródło” wprowadź żądany zakres. Należy pamiętać, że należy podać odniesienia bezwzględne, aby podczas kopiowania listy informacje się nie przesuwały.
   

Ponadto istnieje funkcja do generowania list jednocześnie w więcej niż jednej komórce. Aby to osiągnąć, należy wybrać je wszystkie i wykonać te same czynności, jak opisano wcześniej. Ponownie, musisz upewnić się, że napisano odniesienia bezwzględne. Jeśli adres nie ma znaku dolara obok nazw kolumn i wierszy, należy je dodać, naciskając klawisz F4, aż znak $ znajdzie się obok nazw kolumn i wierszy.

Z ręcznym zapisem danych

W powyższej sytuacji lista została spisana poprzez podświetlenie wymaganego zakresu. Jest to wygodna metoda, ale czasami konieczne jest ręczne rejestrowanie danych. Pozwoli to uniknąć powielania informacji w skoroszycie.

Załóżmy, że stajemy przed zadaniem stworzenia listy zawierającej dwie możliwe opcje: tak i nie. Do wykonania zadania konieczne jest:

1. Kliknij komórkę listy.
2. Otwórz „Dane” i tam znajdź znajomą nam sekcję „Sprawdzanie danych”.
   

   

3. Ponownie wybierz typ „Lista”.
   

   

4. Tutaj musisz wpisać „Tak; Nie” jako źródło. Widzimy, że informacje są wprowadzane ręcznie za pomocą średnika do wyliczenia.

Po kliknięciu OK mamy następujący wynik.

Następnie program automatycznie utworzy rozwijane menu w odpowiedniej komórce. Wszystkie informacje, które użytkownik określił jako pozycje na liście podręcznej. Zasady tworzenia listy w kilku komórkach są podobne do poprzednich, z wyjątkiem tego, że informacje należy określić ręcznie za pomocą średnika.

Tworzenie listy rozwijanej za pomocą funkcji PRZESUNIĘCIE

Oprócz metody klasycznej możliwe jest wykorzystanie funkcji SPRZEDAŻdo generowania menu rozwijanych.

Otwórzmy arkusz.

Aby użyć funkcji dla listy rozwijanej, musisz wykonać następujące czynności:

1. Wybierz interesującą komórkę, w której chcesz umieścić przyszłą listę.
2. Otwórz kolejno zakładkę „Dane” i okno „Walidacja danych”.
   

   

3. Setlista". Odbywa się to w taki sam sposób, jak w poprzednich przykładach. Na koniec stosuje się następującą formułę: =PRZESUNIĘCIE(A$2$;0;0;5). Wprowadzamy go tam, gdzie określone są komórki, które będą używane jako argument.

Następnie program utworzy menu z listą owoców.

Składnia tego jest następująca:

=PRZESUNIĘCIE(odniesienie,odsunięcie_linii,odsunięcie_kolumny,[wysokość];[szerokość])

Widzimy, że ta funkcja ma 5 argumentów. Najpierw podawany jest adres pierwszej komórki, która ma zostać przesunięta. Następne dwa argumenty określają liczbę wierszy i kolumn do przesunięcia. Mówiąc o nas, argument Height to 5, ponieważ reprezentuje wysokość listy. 

Lista rozwijana w Excelu z podmianą danych (+ za pomocą funkcji PRZESUNIĘCIE)

W danym przypadku SPRZEDAŻ pozwoliło na stworzenie wyskakującego menu znajdującego się w ustalonym zakresie. Wadą tej metody jest to, że po dodaniu towaru będziesz musiał samodzielnie edytować formułę.

Aby utworzyć dynamiczną listę z obsługą wprowadzania nowych informacji, musisz:

1. Wybierz interesującą Cię komórkę.
2. Rozwiń zakładkę „Dane” i kliknij „Walidacja danych”.
3. W oknie, które zostanie otwarte, ponownie wybierz element „Lista” i określ następującą formułę jako źródło danych: =СМЕЩ(A$2$;0;0;СЧЕТЕСЛИ($A$2:$A$100;”<>”))
4. Kliknij przycisk OK.

Zawiera funkcję COUNTIF, aby od razu określić, ile komórek jest wypełnionych (choć ma znacznie większą liczbę zastosowań, po prostu piszemy to tutaj w konkretnym celu).

Aby formuła działała normalnie, konieczne jest prześledzenie, czy na ścieżce formuły znajdują się puste komórki. Nie powinny.

Lista rozwijana z danymi z innego arkusza lub pliku Excel

Klasyczna metoda nie działa, jeśli potrzebujesz uzyskać informacje z innego dokumentu lub nawet arkusza zawartego w tym samym pliku. W tym celu używana jest funkcja POŚREDNI, który pozwala wpisać w odpowiednim formacie link do komórki znajdującej się w innym arkuszu lub ogólnie – do pliku. Musisz wykonać następujące czynności:

1. Aktywuj komórkę, w której umieszczamy listę.
2. Otwieramy okno, które już znamy. W tym samym miejscu, w którym wcześniej wskazaliśmy źródła dla innych zakresów, podana jest formuła w formacie = ADR.POŚR("[List1.xlsx]Arkusz1!$A$1:$A$9"). Oczywiście zamiast List1 i Sheet1 możesz wstawić odpowiednio nazwy swoich książek i arkuszy. 

Uwaga! Nazwa pliku jest podana w nawiasach kwadratowych. W takim przypadku program Excel nie będzie mógł użyć pliku, który jest aktualnie zamknięty, jako źródła informacji.

Należy również zauważyć, że sama nazwa pliku ma sens tylko wtedy, gdy wymagany dokument znajduje się w tym samym folderze, w którym zostanie wstawiona lista. Jeśli nie, musisz podać pełny adres tego dokumentu.

Tworzenie zależnych list rozwijanych

Lista zależna to taka, na której zawartość wpływa wybór użytkownika na innej liście. Załóżmy, że mamy przed sobą otwarty stół, który zawiera trzy zakresy, z których każdemu nadano nazwę.

Aby wygenerować listy, na których wynik ma wpływ opcja wybrana na innej liście, należy wykonać poniższe czynności.

1. Utwórz pierwszą listę z nazwami zakresów.
   

   

2. W punkcie wejścia źródła wymagane wskaźniki są podświetlane jeden po drugim.
   

   

3. Utwórz drugą listę w zależności od rodzaju rośliny, którą wybrała osoba. Alternatywnie, jeśli określisz drzewa na pierwszej liście, wówczas informacje na drugiej liście będą „dąb, grab, kasztan” i nie tylko. W miejscu wprowadzania źródła danych należy wpisać wzór =ADR.POŚR(E3). E3 – komórka zawierająca nazwę zakresu 1.=ADR.POŚR(E3). E3 – komórka z nazwą listy 1.

Teraz wszystko jest gotowe.

Jak wybrać wiele wartości z rozwijanej listy?

Czasami nie jest możliwe nadanie pierwszeństwa tylko jednej wartości, dlatego należy wybrać więcej niż jedną. Następnie musisz dodać makro do kodu strony. Użycie kombinacji klawiszy Alt + F11 otwiera Edytor Visual Basic. A kod jest tam wstawiony.

Prywatny arkusz podrzędny_zmiana (według wartości docelowej jako zakres)

    On Error Resume Next

    If Not Intersect(Target, Range(«Е2:Е9»)) Jest Nic I Target.Cells.Count = 1 Then

        Application.EnableEvents = Fałsz

        Jeśli Len (Przesunięcie docelowe (0, 1)) = 0 Wtedy

            Cel.Przesunięcie (0, 1) = Cel

        więcej

            Cel.End (xlToRight) .Offset (0, 1) = Cel

        End If

        Cel.WyczyśćZawartość

        Application.EnableEvents = True

    End If

End Sub 

Aby zawartość komórek była widoczna poniżej, wstawiamy do edytora poniższy kod.

Prywatny arkusz podrzędny_zmiana (według wartości docelowej jako zakres)

    On Error Resume Next

    Jeżeli nie przecina się (cel, zakres(«Н2:К2»)) jest niczym, a cel.liczba komórek.liczba = 1, to

        Application.EnableEvents = Fałsz

        Jeśli Len (Przesunięcie docelowe (1, 0)) = 0 Wtedy

            Cel.Przesunięcie (1, 0) = Cel

        więcej

            Cel.Koniec (xlDown) .Przesunięcie (1, 0) = Cel

        End If

        Cel.WyczyśćZawartość

        Application.EnableEvents = True

    End If

End Sub

I wreszcie ten kod służy do pisania w jednej komórce.

Prywatny arkusz podrzędny_zmiana (według wartości docelowej jako zakres)

    On Error Resume Next

    If Not Intersect(Target, Range(«C2:C5»)) Jest Nic I Target.Cells.Count = 1 Then

        Application.EnableEvents = Fałsz

        newVal = Cel

        Aplikacja.Cofnij

        oldval = cel

        Jeśli Len (stara wartość) <> 0 I stara wartość <> nowa wartość Wtedy

            Cel = Cel i «,» i nowaWart

        więcej

            Cel = nowaWart

        End If

        Jeśli Len (newVal) = 0, to Target.ClearContents

        Application.EnableEvents = True

    End If

End Sub

Zakresy można edytować.

Jak zrobić listę rozwijaną z wyszukiwaniem?

W takim przypadku musisz początkowo użyć innego typu listy. Otworzy się zakładka „Programista”, po czym należy kliknąć lub dotknąć (jeśli ekran jest dotykowy) na elemencie „Wstaw” – „ActiveX”. Posiada pole kombi. Zostaniesz poproszony o narysowanie tej listy, po czym zostanie ona dodana do dokumentu.

Ponadto jest konfigurowany za pomocą właściwości, w których zakres jest określony w opcji ListFillRange. Komórka, w której wyświetlana jest wartość zdefiniowana przez użytkownika, jest konfigurowana przy użyciu opcji LinkedCell. Następnie wystarczy wpisać pierwsze znaki, ponieważ program automatycznie zasugeruje możliwe wartości.

Lista rozwijana z automatyczną podmianą danych

Istnieje również funkcja, która automatycznie podmienia dane po dodaniu ich do zakresu. Łatwo to zrobić:

1. Utwórz zestaw komórek na przyszłą listę. W naszym przypadku jest to zestaw kolorów. Wybieramy to.
   

   

2. Następnie musi być sformatowany jako tabela. Musisz kliknąć przycisk o tej samej nazwie i wybrać styl tabeli.
   

   

   

Następnie musisz potwierdzić ten zakres, naciskając przycisk „OK”.

Wybieramy wynikową tabelę i nadajemy jej nazwę poprzez pole wejściowe znajdujące się na górze kolumny A.

To wszystko, istnieje tabela i może być wykorzystana jako podstawa listy rozwijanej, do której potrzebujesz:

1. Wybierz komórkę, w której znajduje się lista.
2. Otwórz okno dialogowe Sprawdzanie danych.
   

   

3. Ustawiamy typ danych na „Lista”, a jako wartości podajemy nazwę tabeli poprzez znak =.
   

   

   

Wszystko, komórka jest gotowa, a nazwy kolorów są w niej pokazane, tak jak pierwotnie potrzebowaliśmy. Teraz możesz dodawać nowe pozycje po prostu wpisując je w komórce znajdującej się nieco niżej, zaraz po ostatniej.

Zaletą tabeli jest to, że zakres automatycznie zwiększa się po dodaniu nowych danych. W związku z tym jest to najwygodniejszy sposób dodania listy.

Jak skopiować listę rozwijaną?

Do kopiowania wystarczy użyć kombinacji klawiszy Ctrl + C i Ctrl + V. Tak więc lista rozwijana zostanie skopiowana wraz z formatowaniem. Aby usunąć formatowanie, musisz użyć specjalnej pasty (w menu kontekstowym ta opcja pojawia się po skopiowaniu listy), gdzie ustawiona jest opcja „warunki dotyczące wartości”.

Wybierz wszystkie komórki zawierające listę rozwijaną

Aby wykonać to zadanie, musisz użyć funkcji „Wybierz grupę komórek” w grupie „Znajdź i wybierz”.

Następnie otworzy się okno dialogowe, w którym należy wybrać pozycje „Wszystkie” i „Te same” w menu „Walidacja danych”. Pierwsza pozycja wybiera wszystkie listy, a druga wybiera tylko te, które są podobne do niektórych.