Szybki wybór z listy rozwijanej programu Excel. Utwórz listę w Excelu

Opcja Jasna Opcja Porównaj tekst Dim bu Jako wartość logiczna Prywatny sub Worksheet_SelectionChange (ByVal Target As Range) Jeśli Target.CountLarge > 1 Następnie wyjdź z Sub Jeśli Target.Row = 1 Następnie Me.TextBox1.Visible = False: Me.ListBox1.Visible = False: Exit Sub If Target.Column = 3 Następnie „numer kolumny, w której wpisujemy wartości bu = True With Me.TextBox1 .Top = Target.Top: .Text = Target.Value: .Aktywuj End With Me. ListBox1 .Top = Target .Top + 5 If (.Top + .Height + ActiveWindow.PointsToScreenPixelsY(0) * Application.InchesToPoints(1) * 15 / 1440) > _ (ActiveWindow.Application.Height + ActiveWindow.Application.Top) Następnie _ .Top = .Top - .Height + Target.Height "* ActiveWindow.Zoom / 100 .Clear End With bu = False Me.TextBox1.Visible = True: Me.ListBox1.Visible = True Inaczej Me.TextBox1.Visible = Fałsz: Me.ListBox1 .Visible = False End If End Sub Private Sub TextBox1_Change() If Len(TextBox1.Text) = 0 Or bu Następnie Exit Sub "jeśli nie ma znaków do wyszukania - wyjdź z Dim x, i As Long, txt As String, lt As Long, s As String txt = TextBox1.Text: lt = Len(TextBox1.Text) "Gdzie szukamy wartości x = Sheets("nomenklatura";).Columns(1).SpecialCells( 2).Offset(1).Value "(! JĘZYK: Dla i = 1 Do UBound(x, 1)" поиск по первым буквам "If txt = Mid(x(i, 1), 1, lt) Then s = s & x(i, 1) & "~" For i = 1 To UBound(x, 1) "поиск по любому вхождению If InStr(x(i, 1), txt) Then s = s & "~" & x(i, 1) Next i ListBox1.List = Split(s, "~";) End Sub Private Sub TextBox1_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer) If KeyCode = 13 Or KeyCode = 9 Then With Me.TextBox1 ActiveCell.Value = .Value .Visible = False: ListBox1.Visible = False End With ActiveCell(2, 1).Select End If End Sub Private Sub ListBox1_Click() If ListBox1.ListIndex = -1 Then Exit Sub Application.EnableEvents = False bu = True With Me.ListBox1 ActiveCell.Value = .Value Me.TextBox1.Text = .Value Me.TextBox1.Visible = False: .Visible = False End With Application.EnableEvents = True bu = False End Sub Private Sub Worksheet_Change(ByVal Target As Range) Dim lReply As Long If Target.Column = 2 Then Exit Sub If Not Intersect(Target, Range("C2:C100000";)) Is Nothing Then If IsEmpty(Target) Then Exit Sub If WorksheetFunction.CountIf(Sheets("номенклатура";).Columns(1), Target) = 0 Then lReply = MsgBox("Добавить введенное имя " & Target & " в выпадающий список", vbYesNo + vbQuestion) If lReply = vbYes Then Worksheets("номенклатура";).Range("номенклатура";).Cells(Worksheets("номенклатура";).Range("номенклатура";).Rows.Count + 1, 1) = Target End If End If End If Sheets("номенклатура";).Range("номенклатура";).Sort Key1:=Sheets("номенклатура";).Range("A1";), Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal "этот код и поможет отсортировать в алфавитном порядке" End Sub !}

Listy rozwijane sprawiają, że wprowadzanie danych do arkuszy kalkulacyjnych jest łatwe i wygodne. Po prostu kliknij strzałkę i wybierz opcję. Do komórek programu Excel możesz dodawać listy rozwijane zawierające opcje takie jak Tak i Nie, Mężczyźni i Kobiety lub dowolną inną niestandardową listę opcji.

Dodaj listę rozwijaną do komórki w programie Excel dość prosty, ale proces nie jest intuicyjny. Listy rozwijane tworzone są przy użyciu funkcji sprawdzania poprawności danych. Zamierzamy utworzyć listę rozwijaną z zestawem przedziałów wiekowych, aby pokazać, jak to się robi.

Aby rozpocząć, wprowadź listę przedziałów wiekowych w kolejnych komórkach według kolumny lub wiersza. Nasze przedziały wiekowe wpisaliśmy w komórkach od A9 do A13 w tym samym arkuszu, jak pokazano poniżej. Możesz także dodać listę opcji do innego arkusza w tym samym skoroszycie.

Teraz nazwiemy nasz zakres komórek, aby ułatwić dodanie ich do listy rozwijanej. Aby to zrobić, zaznacz wszystkie komórki zawierające elementy listy rozwijanej, a następnie wprowadź nazwę zakresu komórek w polu Nazwa nad siatką. Nazwaliśmy nasz asortyment Wiek.

Teraz wybierz komórkę, do której chcesz dodać listę rozwijaną i przejdź do zakładki Dane.

Na karcie Narzędzia danych Dane Kliknij przycisk Walidacja danych.

Zostanie otwarte okno dialogowe Sprawdzanie poprawności danych. Na karcie Opcje wybierz opcję Lista z listy rozwijanej Typ danych.

Będziemy teraz używać nazwy, którą przypisaliśmy do zakresu komórek zawierających opcje naszej listy rozwijanej. Wchodzić =Wiek w polu „Źródło” (jeśli nazwałeś zakres komórek w inny sposób, zamień „Wiek” na tę nazwę). Upewnij się, że pole wyboru Ignoruj ​​puste komórki wyraźny.

Możesz dodać wyskakujący komunikat, który będzie wyświetlany po wybraniu komórki zawartej na liście rozwijanej. W tym celu kliknij zakładkę Wiadomość do wpisania w oknie dialogowym Walidacja danych. Upewnij się, że pole wyboru Pokaż podpowiedź, jeśli komórka jest bieżąca zainstalowany. Wpisz wiadomość w polach Tytuł i Wiadomość, a następnie kliknij OK.

Po wybraniu komórki zawierającej listę rozwijaną po prawej stronie komórki pojawi się przycisk strzałki w dół. Jeśli dodałeś komunikat wejściowy, pojawi się on pod komórką. Przycisk strzałki w dół pojawia się tylko po zaznaczeniu komórki.

Kliknij przycisk strzałki w dół, aby wyświetlić listę opcji i wybierz jedną.

Jeśli zdecydujesz, że chcesz usunąć listę rozwijaną z komórki, otwórz okno dialogowe Sprawdzanie danych w sposób opisany powyżej i kliknij przycisk Wyczyść wszystko, który jest dostępny niezależnie od wybranej karty w oknie dialogowym.

Opcje w oknie dialogowym Sprawdzanie danych zostają zresetowane do wartości domyślnych. Kliknij OK, aby usunąć menu rozwijane i przywrócić komórkę do wartości domyślnych.

Jeżeli została wybrana opcja, to po usunięciu listy rozwijanej komórka zostanie wypełniona wartością tej opcji.

Wykonaj ten proces, aby w razie potrzeby dodać listy rozwijane do innych komórek. Możesz użyć tej samej listy dla wielu list rozwijanych.

Jeśli masz wiele list rozwijanych, które chcesz dodać do arkusza, zalecamy umieszczenie list opcji w oddzielnym arkuszu programu Excel i ukrycie arkusza, aby zapobiec zmianom opcji.

Wypełniając tabele, często trzeba wprowadzać zduplikowane wartości. Excel pozwala znacznie uprościć tę pracę i jednocześnie zmniejszyć liczbę błędów wejściowych. W tym celu wykorzystywane są listy rozwijane. Wymaganą listę tworzymy raz, a następnie w dowolnej komórce wstawiamy listę rozwijaną, z której możemy wybrać żądaną wartość.

Jak tworzyć listy w programie Excel 2007

Na przykład stworzyłem listę miast w regionie moskiewskim. Wybierz listę i utwórz nazwany zakres. W tym celu po kliknięciu prawym przyciskiem myszy z menu kontekstowego wybierz „Nazwa zakresu”.

Ustaw nazwę „City_M_O” i kliknij „OK”.

Przejdźmy teraz do komórki, w której chcemy mieć listę rozwijaną i przejdź do zakładki „Dane” górnego panelu. Tutaj potrzebujemy przycisku „Weryfikacja danych”, znajdującego się w grupie „Praca z danymi”. Wybierz element „Sprawdzanie danych”.


W wyświetlonym oknie wybierz typ danych „Lista” i w polu „Źródło” wpisz „=Miasto_M_O”, czyli nazwę określonego przez nas zakresu, w którym znajduje się lista.


To wszystko. We wskazanej przez nas komórce pojawi się lista rozwijana, z której możemy wybrać dowolną wartość. W razie potrzeby możemy użyć tej samej listy w innych komórkach.


A oryginalną listę możemy ukryć np. na innym arkuszu, żeby nie zepsuć wyglądu stołu. System będzie nadal działał, ponieważ nasza lista znajduje się w nazwanym zakresie, widocznym w całym skoroszycie programu Excel.

Jak to zrobić w Excelu 2003

Tutaj, aby nadać nazwę zakresowi, musimy przejść do menu „Wstaw”.


A okno nazewnictwa wygląda trochę inaczej.


Przechodzimy także do potrzebnej nam komórki i z menu „Dane” wybieramy „Sprawdź”. Okno, które się otworzy, będzie takie samo jak w Excelu 2007.
Pokonaj Excela i do zobaczenia wkrótce!

Do pracy z dużą ilością danych, przeprowadzania różnych obliczeń i porządkowania informacji w formie tabel użytkownicy korzystają głównie z programu Excel. Aby przeprowadzić obliczenia i rejestrację, wszystkie niezbędne dane wprowadza się ręcznie w odpowiedniej komórce arkusza, a aby nieco uprościć ten proces, można utworzyć listę rozwijaną w programie Excel. Ale ustawienie tego wiąże się również z początkowym ręcznym wprowadzeniem wszystkich danych, a następnie przyjrzymy się bardziej szczegółowo, jak utworzyć listę rozwijaną w Excelu, tak aby sama lista prawidłowych wartości znajdowała się na innym arkuszu , bez zaśmiecania głównego arkusza danymi.

Lista rozwijana w programie Excel tworzona jest przede wszystkim po to, aby uniemożliwić wprowadzenie błędnych informacji poprzez podanie do wprowadzenia wyłącznie wartości z wygenerowanej listy. Tworzenie listy rozwijanej w programie Excel jest dość proste. Najtrudniej jest przygotować pełną listę dopuszczalnych wartości do wprowadzenia, formatując ją w postaci jednej kolumny lub wiersza bez pustych komórek.

Rozważmy najpierw prosty przykład utworzenia listy rozwijanej w programie Excel 2010, tworząc listę prawidłowych wartości na tym samym arkuszu. Na przykład utwórzmy listę wskazującą pewien zakres liczb, który ogranicza liczby do ustawienia w potrzebnej nam komórce. Następnie umieść kursor w miejscu, w którym powinna znajdować się lista rozwijana Excel i przejdź do zakładki "Dane", gdzie w sektorze „Praca z danymi” znajdź punkt „Sprawdzanie danych”. Kliknięcie strzałki spowoduje wyświetlenie dodatkowego menu zawierającego tę samą pozycję „Sprawdzanie danych”.

Po wybraniu tej pozycji pojawia się okno, w którym należy skonfigurować weryfikację wprowadzanych danych w zakładce „Opcje”. Aby to zrobić, w kolumnie "Typ danych:" wybierać "Lista" i kliknięcie odpowiedniej ikony w kolumnie "Źródło:", wybierz zakres komórek z przygotowaną przez nas listą akceptowalnych wartości. Następnie, gdy aktywujemy wybraną komórkę, pojawi się strzałka, a po kliknięciu pojawi się lista prawidłowych wartości, które można wybrać.

W razie potrzeby możesz także dostosować różne wskazówki dotyczące tego, co dokładnie jest wskazane w komórce za pomocą listy rozwijanej. Podczas konfigurowania listy rozwijanej w programie Excel dostępne są dwie opcje "Tytuł:" I "Wiadomość:", które można wpisać w zakładce „Wiadomość do wpisania” po wybraniu pozycji w menu „Sprawdzanie danych”.

Przyjrzyjmy się teraz, jak utworzyć listę rozwijaną w programie Excel 2010, jeśli lista prawidłowych wartości wejściowych znajduje się na innym arkuszu tego samego dokumentu Excel. W rzeczywistości wszystko jest bardzo proste. Robimy to samo, co opisano powyżej, a gdy już dotrzemy do wyboru źródła danych, po prostu przechodzimy do wymaganego arkusza i wybieramy zakres komórek.

Jest bardziej skomplikowany sposób. Zakresowi komórek na innym arkuszu możesz nadać nazwę. W tym celu zaznacz je i przejdź do zakładki „Formuły” i wybierz „Przypisz nazwę”. Teraz, aby wybrać zakres prawidłowych wartości, po prostu podaj nazwę zakresu komórek po znaku równości.

Najłatwiejszy sposób wykonania tego zadania jest następujący. Naciskając prawy przycisk według komórki pod kolumną danych dzwonić menu kontekstowe. Pole zainteresowań jest tutaj Wybierz z listy rozwijanej. To samo można zrobić, naciskając kombinację klawiszy Alt+strzałka w dół.

Jednak ta metoda nie zadziała, jeśli chcesz utworzyć listę w innej komórce, która nie należy do zakresu i w więcej niż jednej komórce przed lub po. Zrobi to następująca metoda.

Metoda standardowa

Wymagany wybierz zakres komórek, z którego zostanie utworzony Lista rozwijana, Następnie Wstawić – Nazwa – Przydzielać(Excel 2003). W nowszej wersji (2007, 2010, 2013, 2016) przejdź do zakładki Formuły, gdzie w sekcji Konkretne nazwy znajdź przycisk Menedżer nazw.

naciśnij przycisk Tworzyć, wprowadź nazwę, możesz użyć dowolnej nazwy, po której OK.

Wybierz komórki(lub kilka), w których chcesz wstawić rozwijaną listę wymaganych pól. Z menu wybierz Dane – Typ danych – Lista. W polu Źródło wprowadź wcześniej utworzoną nazwę lub po prostu określ zakres, który będzie równoważny.

Teraz wynikowa komórka może być Kopiuj w dowolnym miejscu arkusza, będzie zawierał listę niezbędnych elementów tabeli. Możesz go także rozciągnąć, aby uzyskać zakres z listami rozwijanymi.

Ciekawostką jest to, że gdy zmienią się dane w zakresie, zmieni się także oparta na nich lista, czyli będzie dynamiczny.

Korzystanie z elementów sterujących

Metoda opiera się na wstawić kontrola zwana „ pole kombi", który będzie reprezentował zakres danych.

Wybierz kartę Deweloper(dla Excela 2007/2010), w innych wersjach konieczne będzie aktywowanie tej zakładki na wstążce w parametry – Dostosuj swój kanał.

Przejdź do tej zakładki - kliknij przycisk Wstawić. W elementach sterujących wybierz Pole kombi(nie ActiveX) i kliknij ikonę. Rysować prostokąt.

Kliknij go prawym przyciskiem myszy - Format obiektu.

Łącząc się z komórką, wybierz pole, w którym chcesz umieścić numer seryjny elementu na liście. Następnie kliknij OK.

Korzystanie z kontrolek ActiveX

Wszystko tak jak w poprzednim, wystarczy wybrać Pole kombi(AktywnyX).

Różnice są następujące: kontrolka ActiveX może występować w dwóch wariantach – tryb debugowanie, co pozwala na zmianę parametrów, oraz - tryb wejście, możesz jedynie pobierać z niego próbki danych. Zmiana trybów odbywa się za pomocą przycisku Tryb projektowania w zakładce Deweloper.

W przeciwieństwie do innych metod, pozwala to melodia czcionki, kolory i przeprowadzić szybkie wyszukiwanie.