Как сделать выпадающий список в Эксель

ЛогоВыпадающие списки – очень удобный объект, который используется практически во всех приложениях. Кроме этого, они очень популярны на веб-страницах, поскольку намного проще данные выбирать, чем вносить их вручную. Особенно подобные формы востребованы при регистрации. Но их также часто используют и в Экселе. В данной статье мы рассмотрим, как сделать выпадающий список в редакторе Excel.

Для этой цели существует несколько способов. Рассмотрим их более внимательно. Первым делом необходимо создать таблицу с небольшим перечнем.

Начальная таблица

Простой способ

Для того чтобы произошла подстановка данных из таблицы, достаточно выполнить следующее.

  1. Перейдите на первую пустую клетку после вашего списка.

Пустая клетка

  1. Сделайте правый клик. Затем выберите указанный пункт.

Правый клик

  1. В результате этого появится следующий список.

Новый список

  1. Для перехода по нему достаточно нажать на горячие клавиши [knopka]Alt[/knopka]+[knopka]↓[/knopka].

Эту комбинацию можно будет использовать всегда. В дальнейшем необязательно вызывать контекстное меню.

  1. Затем для выбора можно использовать только стрелочки ([knopka]↓[/knopka] и [knopka]↑[/knopka]). Для того чтобы вставить нужный продукт (в нашем случае), достаточно нажать на клавишу [knopka]Enter[/knopka].

Комбинации кнопок

Данный способ отлично работает и тогда, когда в таблице содержатся повторяющиеся значения. При этом в выпадающем списке будут только уникальные записи.

Обратите внимание на то, что этот метод не работает, если вы выберите клетку, выше которой нет никакой информации.

Стандартный

В этом случае необходимо:

  1. Выделить нужные ячейки. Перейти на вкладку «Формулы». Нажать на кнопку «Определенные имена». Выбрать пункт «Диспетчер имён».

Диспетчер имён

  1. Затем кликнуть на «Создать».

Создать

  1. Далее нужно будет указать желаемое имя (нельзя использовать символ тире или пробел). В графе диапазон произойдет автозаполнение, поскольку нужные ячейки были выделены в самом начале. Для сохранения нажмите на «OK».

Клик по OK

  1. Затем закройте это окно.

Новое окно

  1. Выберите ячейку, в которой будет раскрываться будущий список. Откройте вкладку «Данные». Кликните на указанную иконку (на треугольник). Нажмите на пункт «Проверка данных».

Проверка данных

  1. Нажмите на «Тип данных». Необходимо задать значение «Список».

Тип данных

  1. Вследствие этого появится поле «Источник». Кликните туда.

Источник

  1. Затем выделите нужные ячейки. Ранее созданное имя автоматически подставится. Для продолжения нажимаем на «OK».

Нажатие на OK

  1. Благодаря этим действиям вы увидите вот такой элемент.

Новый элемент

Если вы сделаете активной другую ячейку, то иконка выпадающего списка исчезнет. Она отображается только тогда, когда эта клетка становится активной. Поэтому не стоит пугаться и думать, что у вас всё исчезло.

Иконка исчезла

Как включить режим разработчика

Для того чтобы использовать более продвинутые объекты, нужно использовать вкладку «Разработчик». По умолчанию она отключена. Для того чтобы её активировать, необходимо следовать следующей инструкции.

  1. Нажмите на меню «Файл».

Файл

  1. Перейдите в раздел «Параметры».

Параметры

  1. Откройте категорию «Настроить ленту». Затем поставьте галочку напротив пункта «Разработчик». Для сохранения информации кликните на «OK».

Разработчик

Элементы управления

Для создания списков при помощи этого инструмента, необходимо сделать следующее:

  1. Выделите свою таблицу данных. Перейдите на вкладку «Разработчик». Кликните на иконку «Вставить». Нажмите на указанный элемент.

Пункт Вставить

  1. Также изменится иконка указателя.

Иконка указателя

  1. Выделите какой-нибудь прямоугольник. Именно таких размеров и будет ваша будущая кнопка. Её необязательно делать слишком большой. В нашем случае это только пример.

Пример работы

  1. После этого сделайте правый клик мышкой по этому элементу. Затем выберите пункт «Формат объекта».

Формат объекта

  1. В окне «Форматирование объекта» необходимо:
    • Указать диапазон значений для формирования списка.
    • Выбрать ячейку, в которую будет выводиться результат.
    • Указать количество строк будущего списка.
    • Нажать на «OK» для сохранения.

Форматирование объекта

  1. Кликните на этот элемент. После этого вы увидите варианты для выбора.

Варианты

  1. Вследствие этого вы увидите какое-нибудь число. 1 – соответствует первому слову, а 2 – второму. То есть в этой ячейке выводится лишь порядковый номер выбранного слова.

Числа

ActiveX

Для того чтобы воспользоваться этим элементом, необходимо выполнить следующие операции.

  1. Перейдите на вкладку «Разработчик». Нажмите на иконку «Вставить». На этот раз выберите другой инструмент. Он выглядит точно так же, но находится в другой группе.

Раздел Разработчик

  1. Обратите внимание на то, что у вас включится режим конструктора. Кроме этого, изменится внешний вид указателя.

Режим конструктора

  1. Нажмите куда-нибудь. В этом месте появится выпадающий список. Если вы хотите его увеличить, то для этого достаточно потянуть за его края.

Увеличение списка

  1. Кликните на указанную иконку.

Клик по иконке

  1. Благодаря этому в правой части экрана появится окно «Properties», в котором вы сможете изменить различные настройки для выбранного элемента.

При этом значения и свойства будут меняться в зависимости от того, какой элемент будет активен в данный момент. Здесь вы сможете изменить всё, даже шрифт.

  1. В поле «ListFilRange» укажите диапазон ячеек, в котором находятся ваши данные для будущего списка. Заполнение данных должно быть очень аккуратным. Достаточно указать одну неправильную букву, и вы увидите ошибку.

ListFilRange

  1. Далее необходимо кликнуть правой кнопкой мыши по созданному элементу. Выберите «Объект Combobox». Затем – «Edit».

Объект Combobox

  1. Благодаря этим действиям вы увидите, что внешний вид объекта стал другим. Исчезнет возможность изменения размера.

Новый вид

  1. Теперь вы можете спокойно выбрать что-нибудь из этого списка.

Выбор элемента

  1. Для завершения необходимо отключить «Режим конструктора». После этого книга примет стандартный внешний вид.

Отключение

  1. Также необходимо закрыть окно свойств.

Закрытие окна

Убрать объекты ActiveX довольно просто.

  1. Перейдите на вкладку «Разработчик».
  2. Активируйте «Режим конструктора».

Активация Режим конструктора

  1. Кликните на этот объект.

Клик по объекту

  1. Нажмите на горячую клавишу [knopka]Delete[/knopka].
  2. И всё сразу же исчезнет.

Нажатие на Delete

Связанные списки

Для того чтобы создать двухуровневые списки, нужно выполнить несколько простых операций:

  1. Создайте какую-нибудь похожую таблицу. Главное условие – нужно добавить для каждого пункта несколько дополнительных вариантов выбора.

Создание новой таблицы

  1. Затем выделите первую строку. Не целиком, а только возможные варианты. Вызовите контекстное меню при помощи правого клика. Выберите пункт «Присвоить имя…».

Присвоить имя…

  1. Укажите желаемое имя и сохраните настройку. Вставка диапазона ячеек произойдет автоматически, поскольку вы предварительно выбрали нужные клетки.

Выбор клеток

  1. Повторяем те же самые действия и для остальных строчек. Выберите любую клетку, в которой будет расположен будущий список товаров. Откройте вкладку «Данные» и нажмите на инструмент «Проверка данных».

Пункт Проверка данных

  1. В этом окне необходимо выбрать пункт «Список».

Список

  1. Затем кликнуть на поле «Источник» и выбрать нужный диапазон ячеек.

Клик по Источник

  1. Для сохранения используйте кнопку «OK».

Сохранение

  1. Выберите вторую ячейку, в которой будет создан динамический список. Перейдите на вкладку «Данные» и повторите те же самые действия.

Вкладка Данные

В графе «Тип данных» снова указываем «Список». В поле источник укажите следующую формулу.

[kod]=ДВССЫЛ(B11)[/kod]

В качестве аргумента указываем ссылку на ту ячейку, в которой у нас подгружается ассортимент товаров. Более подробно о ней можно прочитать на сайте Microsoft.

  1. Обязательно сохраните все внесенные изменения.

Пункт Список

После нажатия на «OK» вы увидите ошибку источника данных. Ничего страшного тут нет. Кликните на «Да».

Дело в том, что в данный момент в клетке «Товар» у нас ничего не выбрано. Как только там будет какое-нибудь слово, нужный список автоматически загрузится.

Товар

  1. Выберите что-нибудь из предлагаемых товаров.

Выбор товаров

  1. Только после этого вы увидите, что во второй ячейке появились варианты выбора для соответствующего пункта.

Варианты выбора

  1. Попробуйте указать что-то другое из вашего ассортимента. И вы увидите, что список сразу же изменится. Это очень удобно, поскольку ситуации, когда второе «меню» зависит от первого, бывают очень часто.

Второе меню

Связанные с поиском списки

В вышеописанном методе нужно было создавать дополнительные «имена» для каждого пункта. Но можно сделать и иначе. Представьте, что у вас на листе расположено огромное количество данных. Кроме этого, эта информация может меняться, дополняться или удаляться. Как быть в этом случае? Вручную каждый раз всё настраивать заново – очень плохая затея.

В подобных ситуациях нужно использовать динамическое построение выпадающих списков. Это делается следующим образом.

  1. Создайте какую-нибудь таблицу, которая будет выглядеть следующим образом. В правой части мы указали уникальные записи в первом столбце. В нашем случае – это города.

Создание новой таблицы

  1. Выберите любую клетку. Перейдите на вкладку «Данные» и нажмите на инструмент «Проверка данных».

Инструмент Проверка данных

  1. Выбираем нужный тип данных. В источнике указываем необходимый диапазон. Сохраняем при помощи кнопки «OK».

Выбор типа данных

  1. Кликните на другую ячейку и повторите описанные ранее действия по вызову такого же окна.

Настройка данных

  1. Указываем точно такой же тип данных, но в источнике на этот раз указываем следующую формулу.

[kod]=СМЕЩ($B$1;ПОИСКПОЗ($F$6;$B:$B;0)-1;1;СЧЁТЕСЛИ($B:$B;$F$6);1)[/kod]

После сохранения появится ошибка о том, что источник пустой. Нажимаем на кнопку «Да».

Клик по Да

  1. Выберите что-нибудь из предлагаемых вариантов.

Предлагаемые варианты

  1. Затем проверьте, что во второй ячейке появились именно те записи, которые соответствуют выбранному городу.

Нужные записи

  1. Попробуйте выбрать другой город – список изменится автоматически.

Другой город

Данный способ удобен, когда в таблице находится очень большое количество информации. При этом она может размещаться хаотично, а не последовательно, как в нашем примере.

Самое главное, чтобы значения в первом столбце писались одинаково. Достаточно допустить одну опечатку, и эта строка не попадет во вторую ячейку, поскольку происходит дословный поиск значений.

Мультивыбор

Для того чтобы сделать двойной выбор, нужно выполнить следующее.

  1. Сделайте правый клик по названию листа, на котором расположена таблица и будущий выпадающий список.

Мультивыбор

  1. В контекстном меню выберите пункт «Просмотреть код».

Меню Просмотреть код

  1. В появившемся окне вбейте следующий код.

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

Ввод кода

  1. Затем вернитесь к книге и выберите что-нибудь.

Выбор произвольного элемента

  1. Повторите это действие еще раз.

Функция в работе

  1. Благодаря этому коду теперь можно выбирать несколько значений. Все описанные выше методы этого сделать не позволяли, так как всегда происходило затирание ранее выбранного слова.

Результат работы кода

  1. Для очистки этой клетки нужно использовать клавишу [knopka]Delete[/knopka].

Нажатие на кнопку Delete

Во всех остальных случаях значения будут только накапливаться.

Заключение

В данной статье мы рассмотрели всевозможные способы для создания выпадающих списков в редакторе Excel. Если у вас что-то не получается, возможно, вы используете неподходящую таблицу. Еще раз внимательно посмотрите на используемые формулы, поскольку там могут быть опечатки. Также очень важно проверить правильность ссылок на ячейки. Если у вас отсутствует вкладка «Разработчик», значит, вы её неправильно активировали или же упустили этот момент из виду.

Видеоинструкция

Некоторым пользователям намного проще воспринимать информацию при помощи видео. Именно для них мы подготовили специальный ролик, в котором описываются все ранее указанные методы.

Оцените статью
OS Helper
Добавить комментарий