Как заполнить поле со списком данными именованного диапазона в Excel?

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

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

Выпадающий список можно создать несколькими способами. Например, с помощью валидации данных.

Это делается путем выбора ячейки, в которой появляется выпадающий список.

Нажмите в меню Данные — Проверка.

Откроется окно «Проверка вводимых значений».

В Условии проверки выберите Тип данных — Список, после чего ниже появится поле выбора Источника (данных), которое необходимо заполнить.

Рассмотрите несколько вариантов заполнения поля «Источник».

Мы используем свойство комбинированного списка RowSource для заполнения поля списка значениями из диапазона ячеек рабочего листа, так что диапазон «A1:A5» в активном рабочем листе уже заполнен известными значениями:

Если вы хотите присвоить элементу управления ComboBox значения из диапазона ячеек любого рабочего листа, добавьте ссылку на него перед именем диапазона, например, «Sheet1!A1:A5», поле списка будет заполнено значениями ячеек «A1:A5», расположенных на листе «Sheet1». Лист будет назван по имени метки.

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

Иногда необходимо заполнить элементы управления ListBox и ComboBox уникальными значениями из ряда ячеек с повторяющимся содержимым. Посмотрите, как отсортировать уникальные элементы из списка с помощью объектов Collection и Dictionary.

Вы можете скачать файл Excel, содержащий приведенные выше примеры. Файл упакован в ZIP-архив. Чтобы проверить, работает ли код, прямо в редакторе VBA установите курсор в тело каждой процедуры и нажмите кнопку «Run sub».

Основные параметры списков

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

1. Флаг «распространять изменения на другие клетки с тем же состоянием». Этот маркер очень полезен, если на листе есть несколько ячеек с одинаковыми настройками. Установив значения в одной ячейке с помощью параметра маркера, вы можете распространить их на другие аналогичные ячейки.
2. Вкладка «Сообщение для ввода». Если вы заполните это поле, то при установке курсора в нужную ячейку появится сообщение. При необходимости сообщение всегда можно отключить, сняв соответствующий флажок.
3. Вкладка «Сообщение об ошибке». На этой вкладке можно настроить сообщение, которое будет отображаться в случае неправильных данных. Здесь вы указываете тип отображаемого сообщения и указываете, должно ли оно отображаться в случае неправильного ввода. «Стоп», «Сообщение» — не позволяет вводить данные, отличные от значений в предложенном списке, а «Предупреждение» — это возможность дополнительно вводить собственные значения, кроме предложенных в списке.

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