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

Чем хорош такой выпадающий список в ячейке?

1. Это основа простого пользовательского интерфейса. Проще этого нет ничего. Никаких форм - выпадающий список встроен непосредственно в ячейку Excel
2. Легко создавать, не требует никакого программирования
3. Позволяет безопасно получить от пользователя строго регламентированный ввод, так как пользователь лимитирован значениями из списка

Я предлагаю разобрать самый удобный и современный способ сделать это, расчитанный на Excel 2007 и выше.

Шаг 1 - создание таблицы-источника

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

Выделяем этот диапазон и выполняем команду ВСТАВКА -> Таблица, отмечаем галку Таблица с заголовками, жмём OK.

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

Умные таблицы заслуживают отдельного разговора, но в нашем конкретном случае они нам любезны тем, что:

• Умеют расти вниз, путём обычного дописывания значений снизу. То есть таблица "понимает", что снизу приписали ещё одно новое значение и автоматически себя расширяет без дополнительных команд от пользователя
• Умеют возвращать формулой свои столбцы (об этом ниже)

Шаг 2 - даём таблице название

На вкладке КОНСТРУКТОР даём нашей умной таблице имя. Этот шаг можно было бы не выполнять, так как Excel автоматически даёт всем объектам имена, в частности для таблиц это "Таблица1", "Таблица2" и т.д., но давайте воспитывать в себе правильные привычки и вовремя давать нашим таблицам осознанные имена-идентификаторы, это может нам сильно облегчить жизнь впоследствии. Дайте нашей таблице имя tblAgents, где tbl - префикс, который мы будем давать всем нашим умным таблицам, а Agents - говорит о том, что мы выбираем имя торгового агента.

Шаг 3 - создаём именованный диапазон для нашего списка

Теперь необходимо создать именованный диапазон, который будет возвращать столбец из tblAgents. Выбираем команду ФОРМУЛЫ -> Диспетчер имен и Создать...

Создаём диапазон с именем listAgents, который ссылается на следующую формулу =tblAgents[Имя], где в квадратных скобках название заголовка единственной колонки нашей таблицы.

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

Шаг 4 - Создание выпадающего списка

Ну и в качестве заключительного шага выбираем любую ячейку, в которой нам необходимо иметь выпадающий список, выполняем команду ДАННЫЕ -> Проверка данных..., задаём Тип данных равным Список, а в поле источник через клавишу F3 выбираем именованный диапазон listAgents...

...и получаем желаемый результат:

если пользователь пытается выбрать значение не из списка, то получает такое сообщение об ошибке, а Excel не даёт сохранить в ячейке введенное значение.

А тут можно ознакомиться с любопытным примером.

Читайте также:

• Каскадные выпадающие списки

• Универсальные динамические каскадные выпадающие списки без VBA!

• Каскадные комбинированные списки

• Динамические каскадные списки на основе сводных таблиц

• Выпадающий список с контекстным поиском