Выпадающие списки – очень удобный объект, который используется практически во всех приложениях. Кроме этого, они очень популярны на веб-страницах, поскольку намного проще данные выбирать, чем вносить их вручную. Особенно подобные формы востребованы при регистрации. Но их также часто используют и в Экселе. В данной статье мы рассмотрим, как сделать выпадающий список в редакторе Excel.
Для этой цели существует несколько способов. Рассмотрим их более внимательно. Первым делом необходимо создать таблицу с небольшим перечнем.
Простой способ
Для того чтобы произошла подстановка данных из таблицы, достаточно выполнить следующее.
- Перейдите на первую пустую клетку после вашего списка.
- Сделайте правый клик. Затем выберите указанный пункт.
- В результате этого появится следующий список.
- Для перехода по нему достаточно нажать на горячие клавиши [knopka]Alt[/knopka]+[knopka]↓[/knopka].
Эту комбинацию можно будет использовать всегда. В дальнейшем необязательно вызывать контекстное меню.
- Затем для выбора можно использовать только стрелочки ([knopka]↓[/knopka] и [knopka]↑[/knopka]). Для того чтобы вставить нужный продукт (в нашем случае), достаточно нажать на клавишу [knopka]Enter[/knopka].
Данный способ отлично работает и тогда, когда в таблице содержатся повторяющиеся значения. При этом в выпадающем списке будут только уникальные записи.
Обратите внимание на то, что этот метод не работает, если вы выберите клетку, выше которой нет никакой информации.
Стандартный
В этом случае необходимо:
- Выделить нужные ячейки. Перейти на вкладку «Формулы». Нажать на кнопку «Определенные имена». Выбрать пункт «Диспетчер имён».
- Затем кликнуть на «Создать».
- Далее нужно будет указать желаемое имя (нельзя использовать символ тире или пробел). В графе диапазон произойдет автозаполнение, поскольку нужные ячейки были выделены в самом начале. Для сохранения нажмите на «OK».
- Затем закройте это окно.
- Выберите ячейку, в которой будет раскрываться будущий список. Откройте вкладку «Данные». Кликните на указанную иконку (на треугольник). Нажмите на пункт «Проверка данных».
- Нажмите на «Тип данных». Необходимо задать значение «Список».
- Вследствие этого появится поле «Источник». Кликните туда.
- Затем выделите нужные ячейки. Ранее созданное имя автоматически подставится. Для продолжения нажимаем на «OK».
- Благодаря этим действиям вы увидите вот такой элемент.
Если вы сделаете активной другую ячейку, то иконка выпадающего списка исчезнет. Она отображается только тогда, когда эта клетка становится активной. Поэтому не стоит пугаться и думать, что у вас всё исчезло.
Как включить режим разработчика
Для того чтобы использовать более продвинутые объекты, нужно использовать вкладку «Разработчик». По умолчанию она отключена. Для того чтобы её активировать, необходимо следовать следующей инструкции.
- Нажмите на меню «Файл».
- Перейдите в раздел «Параметры».
- Откройте категорию «Настроить ленту». Затем поставьте галочку напротив пункта «Разработчик». Для сохранения информации кликните на «OK».
Элементы управления
Для создания списков при помощи этого инструмента, необходимо сделать следующее:
- Выделите свою таблицу данных. Перейдите на вкладку «Разработчик». Кликните на иконку «Вставить». Нажмите на указанный элемент.
- Также изменится иконка указателя.
- Выделите какой-нибудь прямоугольник. Именно таких размеров и будет ваша будущая кнопка. Её необязательно делать слишком большой. В нашем случае это только пример.
- После этого сделайте правый клик мышкой по этому элементу. Затем выберите пункт «Формат объекта».
- В окне «Форматирование объекта» необходимо:
- Указать диапазон значений для формирования списка.
- Выбрать ячейку, в которую будет выводиться результат.
- Указать количество строк будущего списка.
- Нажать на «OK» для сохранения.
- Кликните на этот элемент. После этого вы увидите варианты для выбора.
- Вследствие этого вы увидите какое-нибудь число. 1 – соответствует первому слову, а 2 – второму. То есть в этой ячейке выводится лишь порядковый номер выбранного слова.
ActiveX
Для того чтобы воспользоваться этим элементом, необходимо выполнить следующие операции.
- Перейдите на вкладку «Разработчик». Нажмите на иконку «Вставить». На этот раз выберите другой инструмент. Он выглядит точно так же, но находится в другой группе.
- Обратите внимание на то, что у вас включится режим конструктора. Кроме этого, изменится внешний вид указателя.
- Нажмите куда-нибудь. В этом месте появится выпадающий список. Если вы хотите его увеличить, то для этого достаточно потянуть за его края.
- Кликните на указанную иконку.
- Благодаря этому в правой части экрана появится окно «Properties», в котором вы сможете изменить различные настройки для выбранного элемента.
При этом значения и свойства будут меняться в зависимости от того, какой элемент будет активен в данный момент. Здесь вы сможете изменить всё, даже шрифт.
- В поле «ListFilRange» укажите диапазон ячеек, в котором находятся ваши данные для будущего списка. Заполнение данных должно быть очень аккуратным. Достаточно указать одну неправильную букву, и вы увидите ошибку.
- Далее необходимо кликнуть правой кнопкой мыши по созданному элементу. Выберите «Объект Combobox». Затем – «Edit».
- Благодаря этим действиям вы увидите, что внешний вид объекта стал другим. Исчезнет возможность изменения размера.
- Теперь вы можете спокойно выбрать что-нибудь из этого списка.
- Для завершения необходимо отключить «Режим конструктора». После этого книга примет стандартный внешний вид.
- Также необходимо закрыть окно свойств.
Убрать объекты ActiveX довольно просто.
- Перейдите на вкладку «Разработчик».
- Активируйте «Режим конструктора».
- Кликните на этот объект.
- Нажмите на горячую клавишу [knopka]Delete[/knopka].
- И всё сразу же исчезнет.
Связанные списки
Для того чтобы создать двухуровневые списки, нужно выполнить несколько простых операций:
- Создайте какую-нибудь похожую таблицу. Главное условие – нужно добавить для каждого пункта несколько дополнительных вариантов выбора.
- Затем выделите первую строку. Не целиком, а только возможные варианты. Вызовите контекстное меню при помощи правого клика. Выберите пункт «Присвоить имя…».
- Укажите желаемое имя и сохраните настройку. Вставка диапазона ячеек произойдет автоматически, поскольку вы предварительно выбрали нужные клетки.
- Повторяем те же самые действия и для остальных строчек. Выберите любую клетку, в которой будет расположен будущий список товаров. Откройте вкладку «Данные» и нажмите на инструмент «Проверка данных».
- В этом окне необходимо выбрать пункт «Список».
- Затем кликнуть на поле «Источник» и выбрать нужный диапазон ячеек.
- Для сохранения используйте кнопку «OK».
- Выберите вторую ячейку, в которой будет создан динамический список. Перейдите на вкладку «Данные» и повторите те же самые действия.
В графе «Тип данных» снова указываем «Список». В поле источник укажите следующую формулу.
[kod]=ДВССЫЛ(B11)[/kod]
В качестве аргумента указываем ссылку на ту ячейку, в которой у нас подгружается ассортимент товаров. Более подробно о ней можно прочитать на сайте Microsoft.
- Обязательно сохраните все внесенные изменения.
После нажатия на «OK» вы увидите ошибку источника данных. Ничего страшного тут нет. Кликните на «Да».
Дело в том, что в данный момент в клетке «Товар» у нас ничего не выбрано. Как только там будет какое-нибудь слово, нужный список автоматически загрузится.
- Выберите что-нибудь из предлагаемых товаров.
- Только после этого вы увидите, что во второй ячейке появились варианты выбора для соответствующего пункта.
- Попробуйте указать что-то другое из вашего ассортимента. И вы увидите, что список сразу же изменится. Это очень удобно, поскольку ситуации, когда второе «меню» зависит от первого, бывают очень часто.
Связанные с поиском списки
В вышеописанном методе нужно было создавать дополнительные «имена» для каждого пункта. Но можно сделать и иначе. Представьте, что у вас на листе расположено огромное количество данных. Кроме этого, эта информация может меняться, дополняться или удаляться. Как быть в этом случае? Вручную каждый раз всё настраивать заново – очень плохая затея.
В подобных ситуациях нужно использовать динамическое построение выпадающих списков. Это делается следующим образом.
- Создайте какую-нибудь таблицу, которая будет выглядеть следующим образом. В правой части мы указали уникальные записи в первом столбце. В нашем случае – это города.
- Выберите любую клетку. Перейдите на вкладку «Данные» и нажмите на инструмент «Проверка данных».
- Выбираем нужный тип данных. В источнике указываем необходимый диапазон. Сохраняем при помощи кнопки «OK».
- Кликните на другую ячейку и повторите описанные ранее действия по вызову такого же окна.
- Указываем точно такой же тип данных, но в источнике на этот раз указываем следующую формулу.
[kod]=СМЕЩ($B$1;ПОИСКПОЗ($F$6;$B:$B;0)-1;1;СЧЁТЕСЛИ($B:$B;$F$6);1)[/kod]
После сохранения появится ошибка о том, что источник пустой. Нажимаем на кнопку «Да».
- Выберите что-нибудь из предлагаемых вариантов.
- Затем проверьте, что во второй ячейке появились именно те записи, которые соответствуют выбранному городу.
- Попробуйте выбрать другой город – список изменится автоматически.
Данный способ удобен, когда в таблице находится очень большое количество информации. При этом она может размещаться хаотично, а не последовательно, как в нашем примере.
Самое главное, чтобы значения в первом столбце писались одинаково. Достаточно допустить одну опечатку, и эта строка не попадет во вторую ячейку, поскольку происходит дословный поиск значений.
Мультивыбор
Для того чтобы сделать двойной выбор, нужно выполнить следующее.
- Сделайте правый клик по названию листа, на котором расположена таблица и будущий выпадающий список.
- В контекстном меню выберите пункт «Просмотреть код».
- В появившемся окне вбейте следующий код.
Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
If Not Intersect(Target, Range(«B11»)) Is Nothing And Target.Cells.Count = 1 Then
Application.EnableEvents = False
NewSelectWord = Target
Application.Undo
BeforeWord = Target
If Len(BeforeWord) <> 0 And BeforeWord <> NewSelectWord Then
Target = Target & «,» & NewSelectWord
Else
Target = NewSelectWord
End If
If Len(NewSelectWord) = 0 Then Target.ClearContents
Application.EnableEvents = True
End If
End Sub
- Затем вернитесь к книге и выберите что-нибудь.
- Повторите это действие еще раз.
- Благодаря этому коду теперь можно выбирать несколько значений. Все описанные выше методы этого сделать не позволяли, так как всегда происходило затирание ранее выбранного слова.
- Для очистки этой клетки нужно использовать клавишу [knopka]Delete[/knopka].
Во всех остальных случаях значения будут только накапливаться.
Заключение
В данной статье мы рассмотрели всевозможные способы для создания выпадающих списков в редакторе Excel. Если у вас что-то не получается, возможно, вы используете неподходящую таблицу. Еще раз внимательно посмотрите на используемые формулы, поскольку там могут быть опечатки. Также очень важно проверить правильность ссылок на ячейки. Если у вас отсутствует вкладка «Разработчик», значит, вы её неправильно активировали или же упустили этот момент из виду.
Видеоинструкция
Некоторым пользователям намного проще воспринимать информацию при помощи видео. Именно для них мы подготовили специальный ролик, в котором описываются все ранее указанные методы.