Создаем в Excel выпадающий список на основе динамического диапазона

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

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

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

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

Выделите диапазон вариантов для выпадающего списка (A1:A5 в нашем примере выше) и на Главной (Home) вкладке нажмите кнопку Форматировать как таблицу (Home – Format as Table). Дизайн можно выбрать любой – это роли не играет.

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

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

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

По имени мы сможем потом адресоваться к таблице на любом листе этой книги

По имени мы сможем потом адресоваться к таблице на любом листе этой книги

Теперь выделите ячейки, где вы хотите создать выпадающие списки (в нашем примере выше это D2), и нажмите кнопку Проверка данных (Data Validation) на вкладке Данные (Data). В открывшемся окне на вкладке Параметры (Settings) выберите вариант Список (List) и введите в поле Источник (Source) вот такую формулу: =ДВССЫЛ("Таблица1[Сотрудники]") (=INDIRECT("Таблица1[Сотрудники]")).

Введите в поле Источник формулу

Введите в поле Источник формулу

Смысл этой формулы прост. Выражение Таблица1[Сотрудники] – это ссылка на столбец с данными для списка из нашей умной таблицы. Но проблема в том, что Excel почему-то не хочет понимать прямых ссылок в поле Источник (Source), т.е. нельзя написать в поле Источник выражение вида =Таблица1[Сотрудники]. Поэтому мы идем на тактическую хитрость – вводим ссылку как текст (в кавычках) и используем функцию ДВССЫЛ (INDIRECT), которая преобразовывает текстовую ссылку в настоящую, «живую».

Осталось только нажать на ОК. Если теперь дописать к нашей таблице новые элементы, то они будут автоматически в нее включены, а значит – добавятся к нашему выпадающему списку. С удалением – то же самое.

Если вам лень возиться с вводом формулы ДВССЫЛ, то можно чуть упростить процесс. После создания умной таблицы просто выделите мышью диапазон с элементами для выпадающего списка (A2:A5) и введите в поле адреса имя для этого диапазона (без пробелов), например Стажеры, и нажмите на Enter.

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

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

Фактически этим мы создаем именованный динамический диапазон, который ссылается на данные из нашей умной таблицы. Теперь имя этого диапазона можно ввести в окне создания выпадающего списка в поле Источник (Source).

Имя диапазона можно ввести в окне создания выпадающего списка

Имя диапазона можно ввести в окне создания выпадающего списка

Top