Как вставить поле со списком в ячейку Excel

Обновлено: 21.11.2024

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

ПРИМЕЧАНИЕ. Элементы управления ActiveX нельзя использовать на Macintosh. Профессор Ли Таунсенд показывает, как создавать раскрывающиеся списки с помощью полей со списком управления формой.

Введение

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

Однако у встроенного раскрывающегося списка есть несколько ограничений:

Тип шрифта списка изменить нельзя
Размер шрифта списка нельзя увеличить
Максимальное количество видимых строк – восемь.
В раскрывающемся списке "Проверка данных" нет функции автозаполнения, позволяющей находить совпадающие элементы в списке по мере ввода.

Преимущества поля со списком

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

Добавив эту функцию поля со списком, вы можете:

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

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

Видео: поле со списком для проверки данных

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

Настройка рабочей книги

Назовите листы

В этой книге необходимы два рабочих листа.

Удалить все листы, кроме Sheet1 и Sheet2
Переименовать Sheet1 в ValidationSample.
Переименовать Sheet2 в ValidationLists.

Проверьте уровень масштабирования

ВАЖНО! Во избежание сбоя Excel устанавливайте одинаковый масштаб на обоих листах (ValidationSample и ValidationLists). Существует странная ошибка, связанная с полями со списком и уровнями масштабирования.

Введите списки

На листе ValidationLists введите списки, которые будут использоваться в раскрывающихся списках проверки данных:

Совет. Используйте функцию автозаполнения для создания списков дней недели и месяцев

В ячейках A1:A7 введите список дней недели.
В ячейках C1:C12 введите список месяцев.

Назовите списки

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

Списки рабочих листов

Если ваши списки находятся на листе, но НЕ в именованной таблице Excel, выполните следующие действия.

Назовите диапазон A1:A7 как DayList
Назовите диапазон C1:C12 как MonthList

Таблицы Excel

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

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

Сначала создайте именованные диапазоны на основе столбцов таблицы:
- Назовите ячейки с названиями дней как DayListA.
- Назовите ячейки с названиями месяцев как MonthListA.
Затем создайте второй набор имен на основе первых имен.
- Создайте именованный диапазон DayList на основе названия DayListA.
- Создайте именованный диапазон MonthList на основе имени MonthListA.

Создать раскрывающийся список

Ячейки B2:B12 имеют списки проверки данных с исходным DayList. Когда выбрана ячейка в этом диапазоне, открывается раскрывающийся список дней недели.
Ячейки C2:C12 имеют списки проверки данных с исходным MonthList. Когда выбрана ячейка в этом диапазоне, доступен раскрывающийся список месяцев.

Добавьте поле со списком

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

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

Открыть окно свойств

Чтобы отформатировать поле со списком, откройте окно свойств:

Нажмите правой кнопкой мыши поле со списком и выберите "Свойства".

Изменить свойства поля со списком

Назовите поле со списком

В окне "Свойства" щелкните поле "Имя".
Введите имя поля со списком. В этом примере имя: TempCombo

Изменить шрифт и размер шрифта

В окне "Свойства" щелкните свойство "Шрифт" и щелкните файл . кнопка

Установить количество строк

В окне "Свойства" щелкните поле ListRows.
Введите количество строк, которые вы хотите отобразить в раскрывающемся списке. В этом примере значение равно 12.

Включить автозаполнение

В окне "Свойства" щелкните свойство MatchEntry.
В раскрывающемся списке выберите 1-frmMatchEntryComplete

Выйти из режима дизайна

Закройте окно свойств.
На вкладке "Разработчик" нажмите кнопку "Режим разработки", чтобы выйти из режима разработки.

Добавить код

Код Visual Basic для приложений (VBA) необходим для отображения поля со списком при двойном щелчке ячейки, содержащей список проверки данных. Подробнее см. в следующем разделе «Как работает код».

Скопируйте следующий код

ПРИМЕЧАНИЕ. Для дат или чисел при проверке данных можно использовать код KeyDown в разделе "Код для чисел" ниже.

Чтобы добавить этот код на лист:

Щелкните правой кнопкой мыши вкладку листа ValidationSample и выберите "Просмотреть код".

Как работает код

Вот некоторые подробности о том, как работает код.

Рабочий лист_BeforeDoubleClick

Этот код запускается при двойном щелчке по ячейке.

Переменные устанавливаются для активного листа и поля со списком с именем TempCombo — обязательно используйте это имя для поля со списком.

Поле со списком скрыто, а связанная с ним ячейка и ListFillRange очищены.

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

Переменная str получает формулу проверки данных для целевой ячейки. Например: "=СписокМесяцев". Затем знак равенства удаляется с помощью функции Right. Остается только имя диапазона для списка проверки данных — «MonthList»

Поле со списком становится видимым и располагается в левом верхнем углу целевой ячейки

Ширина и высота поля со списком определяются шириной и высотой целевой ячейки с небольшим добавлением. Вы можете изменить "+5" на другое число:

ListFillRange заменяется на переменную str -- MonthList, поэтому в поле со списком будут отображаться элементы из этого именованного диапазона.

LinkedCell изменяется на адрес целевой ячейки.

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

TempCombo_LostFocus

Этот код запускается, когда вы выходите из поля со списком

Поле со списком скрыто, перемещено в верхний левый угол листа, а связанная с ним ячейка и ListFillRange очищены.

Протестируйте код

ВАЖНО! Во избежание сбоя Excel устанавливайте одинаковый масштаб на обоих листах (ValidationSample и ValidationLists). Есть странная ошибка, связанная с полями со списком и уровнями масштабирования.

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

Настройка свойств поля со списком

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

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

На ленте выберите вкладку "Разработчик".
Выберите команду "Режим дизайна".
Выберите команду "Свойства".

ПРИМЕЧАНИЕ. Ширина и высота поля со списком задаются в макросе. Изменение этих значений в окне свойств не будет иметь долгосрочного эффекта. Отредактируйте код, если хотите изменить размер.

Код поля со списком для чисел

Значения, которые вы выбираете в поле со списком, обрабатываются как текст, и это может вызвать проблемы, если ваш раскрывающийся список содержит числа (включая даты и время).

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

Изменить текст на реальные числа

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

Клавиатура. Если вы используете клавишу Tab или Enter, чтобы закрыть поле со списком, добавьте код из раздела "Использование клавиатуры" ниже.
Мышь: если вы используете мышь, чтобы выйти из поля со списком, добавьте код из раздела "Использование мыши" ниже

ПРИМЕЧАНИЕ. Если вы чередуете использование клавиатуры и мыши, вы можете добавить код из обоих разделов.

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

Если вы используете клавишу Tab или Enter, чтобы закрыть поле со списком, используйте следующий код вместо кода TempCombo_KeyDown выше.

Затем, выбрав число (или дату, или время) в раскрывающемся списке поля со списком, нажмите клавишу Enter или клавишу Tab, чтобы перейти к следующей ячейке.

Этот код запустится и изменит текстовое значение на реальное число. Затем он активирует ячейку ниже, если вы нажали Enter, или активирует ячейку справа, если вы нажали Tab.

Использование мыши

Если вы используете мышь, чтобы закрыть поле со списком, используйте следующий код вместо кода TempCombo_LostFocus выше.

Этот код запустится и изменит текстовое значение на действительное число.

Зависимые списки с INDIRECT

Если некоторые из ваших раскрывающихся списков имеют зависимую проверку данных, используя простую ДВССЫЛНУЮ формулу, вы можете добавить несколько строк кода для обработки этих списков.

В существующем коде модуля листа ValidationSample найдите следующие строки:

Под этими строками добавьте следующие строки кода для обработки простых формул ДВССЫЛ, таких как =ДВССЫЛ(E2)

Это используется для раскрывающегося списка "Город" в образце зависимого комбинированного файла ниже.

Зависимые раскрывающиеся списки — динамические списки

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

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

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

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

Чтобы создать поле со списком в ячейке D2 и заполнить его именами из столбца B, выполните следующие действия:

<р>1. Щелкните ячейку, в которую вы хотите вставить поле со списком, и на ленте выберите Разработчик > Вставить > Поле со списком (форма управления).

<р>2. Перетащите курсор (маленький крест) и отпустите его, чтобы создать поле со списком.

В результате поле со списком создается в ячейке D2.

<р>3. Теперь свяжите поле со списком с диапазоном, содержащим список имен, чтобы заполнить его. Щелкните правой кнопкой мыши поле со списком и выберите "Управление форматом".

<р>4. В окне «Формат объекта» перейдите на вкладку «Управление» и щелкните стрелку рядом с полем «Диапазон ввода», чтобы выбрать ячейки.

<р>5. Выберите диапазон со значениями для поля со списком (B2:B10) и нажмите Enter на клавиатуре.

<р>6. Теперь вы снова в окне «Формат объекта». Нажмите на стрелку рядом с полем ссылки на ячейку.

<р>7. Выберите ячейку, в которой появится выбранное значение из поля со списком (D3), и нажмите Enter на клавиатуре.

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

Выберите значение в поле со списком

Теперь выберите значение в поле со списком (например, Дженнифер). Как вы можете видеть ниже, ячейка D3 теперь имеет значение 4, которое является позицией выбранного значения в диапазоне ввода (B2:B10).

Чтобы получить выбранное значение в поле со списком в ячейке, используйте функцию ИНДЕКС. Эта функция возвращает значение из заданной позиции в диапазоне. Здесь, чтобы найти 4-е значение в диапазоне B2:B10, введите формулу в ячейку D4:

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

Как мы все знаем, поле со списком представляет собой раскрывающийся список, который сочетает в себе текстовое поле со списком. В Excel существует два типа поля со списком, одно из которых представляет собой поле со списком Form Control, а другое — поле со списком ActiveX Control, как показано на скриншотах слева. Сегодня я расскажу о том, как создать эти два типа полей со списком в Excel.

Создать поле со списком управления формой в Excel

Чтобы вставить это поле со списком "Управление формой", необходимо сначала активировать вкладку "Разработчик" на ленте. Пожалуйста, выполните следующие действия:

<р>1. В Excel 2010/2013 выберите «Файл» > «Параметры», затем нажмите «Настроить ленту» на левой панели и установите флажок «Разработчик» в диалоговом окне «Параметры Excel», после чего нажмите «ОК».

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

<р>2. После отображения вкладки «Разработчик» создайте список значений, который вы хотите добавить в поле со списком. Смотрите скриншот:

<р>3. Затем выберите «Разработчик» > «Вставить» в разделе «Элементы управления формой» нажмите кнопку «Поле со списком», а затем перетащите мышь, чтобы нарисовать поле со списком в нужном месте, см. снимки экрана:

<р>4. Затем щелкните правой кнопкой мыши поле со списком и выберите «Управление форматом», см. снимок экрана:

<р>5. В диалоговом окне «Формат объекта» перейдите на вкладку «Управление», затем в поле «Диапазон ввода» нажмите кнопку, чтобы выбрать диапазон данных, который вы хотите добавить в поле со списком, и вы можете изменить строки раскрывающегося списка по своему усмотрению. Смотрите скриншот:

<р>6. После завершения настроек нажмите «ОК», ваше поле со списком было успешно создано, как показано на следующем снимке экрана:

Создать поле со списком ActiveX Control и изменить размер шрифта в Excel

В приведенном выше поле со списком вы не можете изменить размер шрифта, но с помощью поля со списком Active Control вы можете установить размер шрифта для поля со списком. Однако создать это поле со списком довольно сложно.

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

<р>2. В диалоговом окне «Новое имя» введите имя для своего списка, выберите «Рабочая книга» в раскрывающемся списке «Область», а затем нажмите кнопку, чтобы выбрать список данных в поле «Ссылка». Смотрите скриншот:

<р>3. Нажмите «ОК», чтобы завершить эту операцию, затем выберите «Разработчик» > «Вставить» и нажмите «Поле со списком» в разделе «Управление ActiveX», затем перетащите мышь, чтобы нарисовать поле со списком, см. снимки экрана:

<р>4. Щелкните правой кнопкой мыши поле со списком, чтобы выбрать Свойства, чтобы задать свойства поля со списком.

<р>5. В окне «Свойства» в поле LinkedCell введите имя ячейки и диапазона списка, которые вы только что создали на шаге 1 и шаге 2. Смотрите скриншот:

<р>6. Затем закройте это окно и нажмите команду "Режим разработки" на вкладке "Разработчик", чтобы выйти из режима разработки.

<р>7. И ваше поле со списком создано и готово к использованию.

Советы:

Если вы хотите изменить размер шрифта в поле со списком, выберите команду «Режим дизайна» на вкладке «Разработчик», чтобы войти в режим «Режим дизайна», и щелкните правой кнопкой мыши поле со списком, чтобы выбрать «Свойства».

В окне "Свойства" нажмите кнопку справа от свойства "Шрифт". И в появившемся диалоговом окне «Шрифт» укажите шрифт и размер для поля со списком. Смотрите скриншоты:

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

Это элементы типа раскрывающегося списка, которые позволяют пользователю выбирать среди вариантов в списке.

По умолчанию Excel предлагает несколько вариантов создания полей со списком.

Они не требуют от вас каких-либо специальных знаний в области кодирования.

Давайте создадим его!

*Это руководство предназначено для Excel 2019/Microsoft 365 (для Windows). Есть другая версия? Нет проблем, вы все равно можете выполнить те же шаги.

Каспер Лангманн, соучредитель Spreadsheeto

Оглавление

Получите БЕСПЛАТНЫЙ файл с упражнениями

Прежде чем начать:

В этом руководстве вам понадобится набор данных для практики.

Я включил один для вас (бесплатно).

Загрузите прямо ниже!

Загрузите БЕСПЛАТНЫЙ файл упражнения

Что такое поле со списком?

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

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

В Excel есть два варианта создания поля со списком. Существует один параметр, который называется «Управление формами», а другой — «Управление ActiveX».

С чего начать

Первое, что вам нужно сделать, это убедиться, что вкладка "Разработчик" видна на ленте Excel.

Если вы уже включили вкладку "Разработчик" в своей версии Excel, вы сможете найти ее рядом с вкладкой "Вид".

Каспер Лангманн, соучредитель Spreadsheeto

Если вы не видите вкладку "Разработчик", выполните следующие действия.

Перейдите на вкладку "Файл"

Нажмите "Параметры"

Когда откроется диалоговое окно "Параметры Excel", нажмите "Настроить ленту".

В столбце справа в разделе "Настроить ленту" обязательно установите флажок "Разработчик".

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

Затем нажмите «Разработчик», чтобы выделить его, и нажмите кнопку «Добавить» между столбцами. Опять же, не забудьте установить флажок «Разработчик», когда он появится в правом столбце.

Теперь нажмите "ОК", и вы должны найти вкладку "Разработчик" на своей ленте.

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