Сложный выпадающий список в excel

Связанные (зависимые) выпадающие списки

Способ 1. Функция ДВССЫЛ (INDIRECT)

Этот фокус основан на применении функции ДВССЫЛ (INDIRECT), которая умеет делать одну простую вещь — преобразовывать содержимое любой указанной ячейки в адрес диапазона, который понимает Excel. То есть, если в ячейке лежит текст «А1», то функция выдаст в результате ссылку на ячейку А1. Если в ячейке лежит слово «Маша», то функция выдаст ссылку на именованный диапазон с именем Маша и т.д. Такой, своего рода, «перевод стрелок»

Возьмем, например, вот такой список моделей автомобилей Toyota, Ford и Nissan:

Выделим весь список моделей Тойоты (с ячейки А2 и вниз до конца списка) и дадим этому диапазону имя Toyota на вкладке Формулы (Formulas) с помощью Диспетчера имен (Name Manager). Затем повторим то же самое со списками моделей Ford и Nissan, задав соответственно имена диапазонам Ford и Nissan.

При задании имён помните о том, что имена диапазонов в Excel не должны содержать пробелов, знаков препинания и начинаться обязательно с буквы. Поэтому если бы в одной из марок автомобилей присутствовал бы пробел (например Ssang Yong), то его пришлось бы заменить в ячейке и в имени диапазона на нижнее подчеркивание (т.е. Ssang_Yong).

Теперь создадим первый выпадающий список для выбора марки автомобиля. Выделите пустую ячейку (на картинке выше — зелёную) и нажмите кнопку Проверка данных (Data Validation) на вкладке Данные (Data). Затем из выпадающего списка Тип данных (Allow)  выберите вариант Список (List) и в поле Источник (Source) выделите ячейки с названиями марок (желтые ячейки в нашем примере). После нажатия на ОК первый выпадающий список готов.

Теперь создадим второй выпадающий список, в котором будут отображаться модели выбранной в первом списке марки. Также как в предыдущем случае, выделите пустую ячейку, где должен быть список моделей (на картинке она синяя) и откройте окно Проверки данных, но в поле Источник нужно будет ввести вот такую формулу:

=ДВССЫЛ(F2)

где F2 — адрес ячейки с первым выпадающим списком (замените на свой).

Все. После нажатия на ОК содержимое второго списка будет выбираться по имени диапазона, выбранного в первом списке.

Минусы такого способа:

• Надо руками создавать много именованных диапазонов (если у нас много марок автомобилей).
• В качестве вторичных (зависимых) диапазонов не могут выступать динамические диапазоны задаваемые формулами типа СМЕЩ (OFFSET). Для первичного (независимого) списка их использовать можно, а вот вторичный список должен быть определен жестко, без формул. Однако, это ограничение можно обойти, создав справочник соответствий марка-модель (см. Способы 3 и 4).
• Имена вторичных диапазонов должны совпадать с элементами первичного выпадающего списка. Т.е. если в нем есть текст с пробелами, то придется их заменять на подчеркивания с помощью функции ПОДСТАВИТЬ (SUBSTITUTE), т.е. формула будет выглядеть как:

=ДВССЫЛ(ПОДСТАВИТЬ(F2;» «;»_»))

Способ 2. Умные таблицы

Этот подход очень похож на предыдущий, но использует вместо именованных диапазонов — «умные» динамические таблицы, которые будут выступать источником данных для вторичного списка:

Таким образом, мы:

1. Сначала преобразуем наши справочники в «умные» таблицы, используя сочетание клавиш Ctrl+T или команду Главная — Форматировать как таблицу (Home — Format as Table) и
2. Даём им имена (Фрукты, Овощи, Зелень) на вкладке Конструктор (Design) в поле Имя таблицы (Table Name).
3. Создаём первый и второй (связанный) выпадающие списки точно так же, как в предыдущем способе с функцией ДВССЫЛ (INDIRECT).

Главным плюсом и отличием такого способа является возможность легко добавлять новые товары в каждую категорию — динамическая «умная» таблица автоматически расширится, и нам не придется вручную исправлять ссылку на диапазон (как это требуется в предыдущем способе).

Способ 3. Отсортированный справочник

Этот способ требует наличия отсортированного списка соответствий марка-модель вот такого вида:

Для создания первичного выпадающего списка можно марок можно воспользоваться обычным способом, описанным выше, т.е.  выбрать на вкладке Данные (Data) команду Проверка данных (Data validation) и указать в качестве источника жёлтые ячейки с марками

А вот для зависимого списка моделей придется создать именованный диапазон с функцией СМЕЩ (OFFSET), который будет динамически ссылаться только на ячейки моделей определенной марки. Для этого:

• Нажмите сочетание клавиш Ctrl+F3 или воспользуйтесь кнопкой Диспетчер имен (Name manager) на вкладке Формулы (Formulas).
• Создайте новый именованный диапазон с любым именем (например Модели) и в поле Ссылка (Reference) в нижней части окна введите руками следующую формулу:

=СМЕЩ($A$1;ПОИСКПОЗ($G$7;$A:$A;0)-1;1;СЧЁТЕСЛИ($A:$A;$G$7);1)

=OFFSET($A$1;MATCH($G$7;$A:$A;0)-1;1;COUNTIF($A:$A;$G$7);1)

Ссылки должны быть абсолютными (со знаками $). После нажатия Enter к формуле будут автоматически добавлены имена листов — не пугайтесь.

Работает это следующим образом. Функция СМЕЩ (OFFSET) умеет выдавать ссылку на диапазон нужного размера, сдвинутый относительно исходной ячейки на заданное количество строк и столбцов. В более понятном варианте синтаксис этой функции таков:

=СМЕЩ(начальная_ячейка; сдвиг_вниз; сдвиг_вправо; высота_диапазона_в_строках; ширина_диапазона_в_столбцах)

Таким образом:

• начальная ячейка — берем первую ячейку нашего списка, т.е. А1
• сдвиг_вниз — нам считает функция ПОИСКПОЗ (MATCH), которая, попросту говоря, выдает порядковый номер ячейки с выбранной маркой (G7) в заданном диапазоне (столбце А)
• сдвиг_вправо = 1, т.к. мы хотим сослаться на модели в соседнем столбце (В)
• высота_диапазона_в_строках  — вычисляем с помощью функции СЧЕТЕСЛИ (COUNTIF), которая умеет подсчитать количество встретившихся в списке (столбце А) нужных нам значений — марок авто (G7)
• ширина_диапазона_в_столбцах = 1, т.к. нам нужен один столбец с моделями

В итоге должно получиться что-то вроде этого:

Осталось добавить выпадающий список на основе созданной формулы к ячейке G8. Для этого:

• выделяем ячейку G8 
• выбираем на вкладке Данные (Data) команду Проверка данных (Data validation)
• из выпадающего списка выбираем вариант проверки Список (List) и вводим в качестве Источника (Source) знак равно и имя нашего диапазона, т.е.  =Модель

И наш связанный список моделей готов.

Способ 4. Неотсортированный справочник

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

Первый уровень выпадающего списка (диапазон А2:А14 на рисунке выше) здесь делается классическим образом через команду Данные — Проверка данных — Список (Data — Validation — List) и в качестве источника указать зелёные ячейки с названиями категорий.

А для связанных выпадающих списков во втором столбце (B2:B14) в поле Источник (Source) мы используем хитрую формулу:

=СМЕЩ($E$2;1;ПОИСКПОЗ(A2;$E$2:$G$2;0)-1;СЧЁТЗ(СМЕЩ($E$2;1;ПОИСКПОЗ(A2;$E$2:$G$2;0)-1;10;1));1)

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

 Ссылки по теме

• 4 способа создать выпадающий список в ячейках листа
• Автоматическое создание выпадающих списков при помощи инструментов надстройки PLEX
• Выбор фото из выпадающего списка
• Выпадающий список с автоматическим удалением уже использованных элементов
• Динамическая выборка данных для выпадающего списка функциями ИНДЕКС и ПОИСКПОЗ 

Для моделирования сложных иерархических данных создадим Многоуровневый связанный список.

Потребность в создании иерархических данных появляется при решении следующих задач:

• 
  Отдел
  
  –
  
  Сотрудники отдела
  
  . При выборе отдела из списка всех отделов компании, динамически должен формироваться список, содержащий всех сотрудников этого отдела (двухуровневая иерархия);
• 
  Город – Улица – Номер дома
  
  . При заполнении адреса проживания из списка городов нужно выбирать
  
  город
  
  , затем из списка всех улиц этого города –
  
  улицу
  
  , затем, из списка всех домов на этой улице –
  
  номер дома
  
  (трехуровневая иерархия).

В этой статье рассмотрен

Многоуровневый связанный список. Двухуровневый связанный список

или просто

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

рассмотрен в статьях

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

и

Расширяемый Связанный список.

Материал статьи один из самых сложных на сайте

Excel2.ru

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

Многоуровневый связанный список

будем реализовывать с помощью инструмента

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

(

) с условием проверки

Список

.Создание

Многоуровневого связанного списка

рассмотрим на конкретном примере.

Примечание

: Рассмотренный в этой статье Многоуровневый связанный список на самом деле правильнее назвать Трехуровневым, т.к. создать четырехуровневый связанный список, используя рассмотренный здесь подход, очень проблематично. Для тех, кому требуется создать структуру с 4-мя и более уровнями, см. статью

Многоуровневый связанный список типа Предок-Родитель

.

Постановка задачи

Имеется перечень

Регионов

. Для каждого

Региона

имеется свой перечень

Стран

. Для каждой

Страны

имеется свой перечень

Городов

.

Пользователь должен иметь возможность, выбрав определенный

Регион

, в соседней ячейке выбрать из

Выпадающего (раскрывающегося) списка

нужную ему

Страну

из этого

Региона

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

Город

из этой

Страны

(см.

файл примера

).

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

Сначала выберем, например,

Регион

«Америка» с помощью

Выпадающего списка

.

Затем выберем

Страну

«США» из

Региона

«Америка».

Причем перечень стран в

выпадающем списке

будет содержать только страны из выбранного на предыдущем шаге

Региона

«Америка».

И, наконец, выберем

Город

«Атланта» из

Страны

«США».

Причем перечень городов в

выпадающем списке

будет содержать только города из выбранной на предыдущем шаге

Страны,

т.е. из «США».

Решение

Итак, приступим к созданию

Трехуровневого связанного списка

. Таблицу, в которую будут заноситься данные с помощью

Трехуровневого

связанного списка

, разместим на листе

Таблица

.

Список

Регионов

и перечни

Стран

разместим на листе

Страны

.

Обратите внимание, что названия

Регионов

(диапазон

А2:А12

на листе

Страны

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

Стран

(

В1:

L

1

).

Это требование обеспечивается формулой (см. статьи о

Транспонировании

).

=ДВССЫЛ(АДРЕС(СТРОКА($A$1)-СТОЛБЕЦ($A$1)+СТОЛБЕЦ();1))

с помощью которой формируются заголовки столбцов. Введем ее в диапазон ячеек

В1:

L

1

.

Список

Стран

и перечни

Городов

разместим на листе

Города

.

Откуда же возьмется перечень стран на листе

Города

? Очевидно, что после заполнения листа

Страны

названиями стран, необходимо, что они каким-то чудесным образом переместились на лист

Города

. Это чудесное перемещение организуем формулами. Список

Стран

сформируем на листе

Города

в столбце

А

с помощью решения приведенного в статье

Объединение списков

. Значения для этого списка будем брать из

Именованного диапазона

Диап_Стран

(его нужно предварительно создать через

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

)

. Именованный диапазон

Диап_Стран

образуем формулой:

=СМЕЩ(страны!$B$2;;;90;СЧЁТЕСЛИ(страны!$B$1:$Z$1;»?*»))

Для формирования списка

Стран

нам также понадобится

Именованная формула

Строки_Столбцы_Стран

=ЕСЛИ(ЕПУСТО(Диап_Стран);»»;—((СТОЛБЕЦ(Диап_Стран)-1)&ВЫБОР(ДЛСТР(СТРОКА(Диап_Стран)-1);»0″;»»)&СТРОКА(Диап_Стран)-1))

Окончательная формула в столбце

А

на листе

Города

выглядит так:

=ЕСЛИОШИБКА(ИНДЕКС(Диап_Стран;—ПРАВСИМВ(НАИМЕНЬШИЙ(Строки_Столбцы_Стран;СТРОКА(Z1));2);—ЛЕВСИМВ(НАИМЕНЬШИЙ(Строки_Столбцы_Стран;СТРОКА(Z1));ДЛСТР(НАИМЕНЬШИЙ(Строки_Столбцы_Стран;СТРОКА(Z1)))-2));»»)

сформирует необходимый нам список

Стран

.

Теперь создадим

Динамический диапазон

для формирования

Выпадающего списка

содержащего названия

Регионов

. Для этого необходимо:

• нажать кнопку меню «
  
  Присвоить имя
  
  »
  
  (
  
  );
• в поле
  
  Имя
  
  ввести
  
  Регионы
  
  ;
• в поле
  
  Диапазон
  
  ввести формулу

=страны!$A$2:ИНДЕКС(страны!$A:$A;СЧЁТЗ(страны!$A:$A))

• Нажать ОК.

Формула подсчитывает количество элементов в столбце

А

на листе

Страны

(функция

СЧЁТЗ()

) и определяет ссылку на последний элемент в столбце (функция

ИНДЕКС()

), тем самым формируется диапазон, содержащий все значения

Регионов

. Пропуски в столбце

А

не допускаются.

Аналогичным образом создадим

Динамический диапазон

Список_Стран

для формирования

выпадающего списка

содержащего названия стран:

=города!$A$2:ИНДЕКС(города!$A:$A;СЧЁТЗ(города!$A:$A))

Создадим

Именованную формулу

Позиция_региона

для определения позиции, выбранного пользователем региона, в созданном выше диапазоне

Регионы:

=ПОИСКПОЗ(A5;Регионы;0)

Т.к. в формуле использована

относительная адресация

, то важно перед созданием формулы сделать активной ячейку

B5

на листе

Таблица

.

Аналогичным образом создадим

именованную формулу

для определения позиции, выбранной пользователем страны, в диапазоне

Список_Стран

=ПОИСКПОЗ(таблица!B5;Список_Стран;0)

. Перед созданием формулы нужно сделать активной ячейку

С5

на листе

Таблица

.

Создадим

Именованные константы

МаксСтран

равную 20 и

МаксГородов

равную 30. Константы соответствует максимальному количеству стран в регионе и, соответственно, максимальному количеству городов в стране. Эти значения произвольны и их можно изменить.

Создадим

именованный диапазон

Выбранный_Регион

для определения диапазона на листе

Страны

, содержащего страны выбранного региона:

=СМЕЩ(страны!$A$2;;Позиция_региона;МаксСтран)

Теперь, например, при выборе региона

Америка

функция

СМЕЩ()

вернет ссылку на диапазон

страны!$B$2:$B$20

Создадим аналогичный диапазон

Выбранная_Страна

для определения диапазона на листе

Города

, содержащего города выбранного региона:

=СМЕЩ(города!$A$2;;Позиция_страны;МаксГородов)

Создадим две последние именованные формулы

Страны

и

Города

:

=СМЕЩ(страны!$A$2;;Позиция_региона;СЧЁТЗ(Выбранный_Регион)) =СМЕЩ(города!$A$2;;Позиция_страны;СЧЁТЗ(Выбранная_Страна))

Эти формулы нужны для того, чтобы в

выпадающих списках

не отображались пустые строки.Наконец сформируем

связанный выпадающий

список

для ячеек из столбца

Страна

налисте

Таблица

.

• выделяем диапазон
  
  B
  
  5:
  
  B
  
  22
  
  налисте
  
  Таблица
  
  ;
• вызываем инструмент

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

  ,

• устанавливаем тип данных
  
  Список
  
  ,
• в поле
  
  Источник
  
  вводим:
  
  =Страны
  
  .

Также создадим

связанный выпадающий

список

для ячеек из столбца

Город

(диапазон

С5:С22

, в поле

Источник

вводим:

=Города

)

На листе

Таблица

после выбора

Региона

и

Страны

теперь есть возможность выбора

Города

.

Для добавления новых

Регионов

и их

Стран

достаточно ввести новый

Регион

в столбец

A

(лист

Страны

), в строке

1

автоматически отобразится соответствующий заголовок. Под появившимся заголовком в строке

1

введите страны нового

Региона

.Для добавления новых

Городов,

на листе

Города

в строке

1

найдите нужное название страны (оно автоматически появится там после добавления страны на листе

Страны

). Под этим заголовком введите название города.

СОВЕТ:

В этой статье города (и страны) размещены в нескольких столбцах. Обычно однотипные значения размещают в одном столбце (списке). В статье

Многоуровневый связанный список в MS EXCEL на основе таблицы

все исходные данные размещены на одном листе, а однотипные данные (названия городов) — в одном столбце. Это облегчает написание формул и позволяет создать списки с большим количеством уровней иерархии (4-6).

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

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

Создание раскрывающегося списка

Путь: меню «Данные» — инструмент «Проверка данных» — вкладка «Параметры». Тип данных – «Список».

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

1. Вручную через «точку-с-запятой» в поле «Источник».



2. Ввести значения заранее. А в качестве источника указать диапазон ячеек со списком.



3. Назначить имя для диапазона значений и в поле источник вписать это имя.

Любой из вариантов даст такой результат.



Выпадающий список в Excel с подстановкой данных

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

1. Выделяем диапазон для выпадающего списка. В главном меню находим инструмент «Форматировать как таблицу».



2. Откроются стили. Выбираем любой. Для решения нашей задачи дизайн не имеет значения. Наличие заголовка (шапки) важно. В нашем примере это ячейка А1 со словом «Деревья». То есть нужно выбрать стиль таблицы со строкой заголовка. Получаем следующий вид диапазона:



3. Ставим курсор в ячейку, где будет находиться выпадающий список. Открываем параметры инструмента «Проверка данных» (выше описан путь). В поле «Источник» прописываем такую функцию:

Протестируем. Вот наша таблица со списком на одном листе:

Добавим в таблицу новое значение «елка».

Теперь удалим значение «береза».

Осуществить задуманное нам помогла «умная таблица», которая легка «расширяется», меняется.

Теперь сделаем так, чтобы можно было вводить новые значения прямо в ячейку с этим списком. И данные автоматически добавлялись в диапазон.

1. Сформируем именованный диапазон. Путь: «Формулы» — «Диспетчер имен» — «Создать». Вводим уникальное название диапазона – ОК.



2. Создаем раскрывающийся список в любой ячейке. Как это сделать, уже известно. Источник – имя диапазона: =деревья.
3. Снимаем галочки на вкладках «Сообщение для ввода», «Сообщение об ошибке». Если этого не сделать, Excel не позволит нам вводить новые значения.



4. Вызываем редактор Visual Basic. Для этого щелкаем правой кнопкой мыши по названию листа и переходим по вкладке «Исходный текст». Либо одновременно нажимаем клавиши Alt + F11. Копируем код (только вставьте свои параметры).

Private Sub Worksheet_Change(ByVal Target As Range)
 
Dim lReply As Long
 
    If Target.Cells.Count > 1 Then Exit Sub
    If Target.Address = "$C$2" Then
     If IsEmpty(Target) Then Exit Sub
       If WorksheetFunction.CountIf(Range("Деревья"), Target) = 0 Then
          lReply = MsgBox("Добавить введенное имя " & _
                         Target & " в выпадающий список?", vbYesNo + vbQuestion)
          If lReply = vbYes Then
              Range("Деревья").Cells(Range("Деревья").Rows.Count + 1, 1) = Target
          End If
       End If
     End If
End Sub
 

5. Сохраняем, установив тип файла «с поддержкой макросов».



6. Переходим на лист со списком. Вкладка «Разработчик» — «Код» — «Макросы». Сочетание клавиш для быстрого вызова – Alt + F8. Выбираем нужное имя. Нажимаем «Выполнить».

Когда мы введем в пустую ячейку выпадающего списка новое наименование, появится сообщение: «Добавить введенное имя баобаб в выпадающий список?».

Нажмем «Да» и добавиться еще одна строка со значением «баобаб».

Выпадающий список в Excel с данными с другого листа/файла

Когда значения для выпадающего списка расположены на другом листе или в другой книге, стандартный способ не работает. Решить задачу можно с помощью функции ДВССЫЛ: она сформирует правильную ссылку на внешний источник информации.

1. Делаем активной ячейку, куда хотим поместить раскрывающийся список.
2. Открываем параметры проверки данных. В поле «Источник» вводим формулу: =ДВССЫЛ(“[Список1.xlsx]Лист1!$A$1:$A$9”).

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

Как сделать зависимые выпадающие списки

Возьмем три именованных диапазона:

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

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



2. Когда поставили курсор в поле «Источник», переходим на лист и выделяем попеременно нужные ячейки.



3. Теперь создадим второй раскрывающийся список. В нем должны отражаться те слова, которые соответствуют выбранному в первом списке названию. Если «Деревья», то «граб», «дуб» и т.д. Вводим в поле «Источник» функцию вида =ДВССЫЛ(E3). E3 – ячейка с именем первого диапазона.



Выбор нескольких значений из выпадающего списка Excel

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

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

Private Sub Worksheet_Change(ByVal Target As Range)
    On Error Resume Next
    If Not Intersect(Target, Range("Е2:Е9")) Is Nothing And Target.Cells.Count = 1 Then
        Application.EnableEvents = False
        If Len(Target.Offset(0, 1)) = 0 Then
            Target.Offset(0, 1) = Target
        Else
            Target.End(xlToRight).Offset(0, 1) = Target
        End If
        Target.ClearContents
        Application.EnableEvents = True
    End If
End Sub
 

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

Private Sub Worksheet_Change(ByVal Target As Range)
    On Error Resume Next
    If Not Intersect(Target, Range("Н2:К2")) Is Nothing And Target.Cells.Count = 1 Then
        Application.EnableEvents = False
        If Len(Target.Offset(1, 0)) = 0 Then
            Target.Offset(1, 0) = Target
        Else
            Target.End(xlDown).Offset(1, 0) = Target
        End If
        Target.ClearContents
        Application.EnableEvents = True
    End If
End Sub
 

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


Private Sub Worksheet_Change(ByVal Target As Range)
    On Error Resume Next
    If Not Intersect(Target, Range("C2:C5")) Is Nothing And Target.Cells.Count = 1 Then
        Application.EnableEvents = False
        newVal = Target
        Application.Undo
        oldval = Target
        If Len(oldval) <> 0 And oldval <> newVal Then
            Target = Target & "," & newVal
        Else
            Target = newVal
        End If
        If Len(newVal) = 0 Then Target.ClearContents
        Application.EnableEvents = True
    End If
End Sub


Не забываем менять диапазоны на «свои». Списки создаем классическим способом. А всю остальную работу будут делать макросы.

Выпадающий список с поиском

1. На вкладке «Разработчик» находим инструмент «Вставить» – «ActiveX». Здесь нам нужна кнопка «Поле со списком» (ориентируемся на всплывающие подсказки).



2. Щелкаем по значку – становится активным «Режим конструктора». Рисуем курсором (он становится «крестиком») небольшой прямоугольник – место будущего списка.



3. Жмем «Свойства» – открывается перечень настроек.



4. Вписываем диапазон в строку ListFillRange (руками). Ячейку, куда будет выводиться выбранное значение – в строку LinkedCell. Для изменения шрифта и размера – Font.

Скачать пример выпадающего списка

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