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

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

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

Создание дополнительного списка

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

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

Выделяем данные, которые планируем занести в раскрывающийся список. Кликаем правой кнопкой мыши, и в контекстном меню выбираем пункт «Присвоить имя…».

Открывается форма создания имени. В поле «Имя» вписываем любое удобное наименование, по которому будем узнавать данный список. Но, это наименование должно начинаться обязательно с буквы. Можно также вписать примечание, но это не обязательно. Жмем на кнопку «OK».

Переходим во вкладку «Данные» программы Microsoft Excel. Выделяем область таблицы, где собираемся применять выпадающий список. Жмем на кнопку «Проверка данных», расположенную на Ленте.

Открывается окно проверки вводимых значений. Во вкладке «Параметры» в поле «Тип данных» выбираем параметр «Список». В поле «Источник» ставим знак равно, и сразу без пробелов пишем имя списка, которое присвоили ему выше. Жмем на кнопку «OK».

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

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

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

В открывшемся окне переходим в подраздел «Настройка ленты», и ставим флажок напротив значения «Разработчик». Жмем на кнопку «OK».

После этого, на ленте появляется вкладка с названием «Разработчик», куда мы и перемещаемся. Чертим в Microsoft Excel список, который должен стать выпадающим меню. Затем, кликаем на Ленте на значок «Вставить», и среди появившихся элементов в группе «Элемент ActiveX» выбираем «Поле со списком».

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

Затем мы перемещаемся в «Режим конструктора». Жмем на кнопку «Свойства элемента управления».

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

Далее, кликаем по ячейке, и в контекстном меню последовательно переходим по пунктам «Объект ComboBox» и «Edit».

Выпадающий список в Microsoft Excel готов.

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

Связанные списки

Также, в программе Excel можно создавать связанные выпадающие списки. Это такие списки, когда при выборе одного значения из списка, в другой графе предлагается выбрать соответствующие ему параметры. Например, при выборе в списке продуктов картофеля, предлагается выбрать как меры измерения килограммы и граммы, а при выборе масла растительного — литры и миллилитры.

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

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

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

Во второй ячейке тоже запускаем окно проверки данных, но в графе «Источник» вводим функцию «=ДВССЫЛ» и адрес первой ячейки. Например, =ДВССЫЛ($B3).

Как видим, список создан.

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

Всё, таблица создана.

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

Источник: officeassist.ru

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

Я собираюсь вам рассказать о фантастически полезном и эффектном приёме. Это одна из самых интересных вещей, которую мне доводилось видеть в Excel. Кроме того, она достаточно легко реализуется. Если вы разберётесь, как она работает, то вы сразу узнаете об Excel необычно много. Я постараюсь рассказать всё максимально подробно, последовательно и внятно.

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

Итак, речь пойдёт о выпадающем списке (так называемый combo box), в который встроена возможность динамического поиска по подстроке, которую пользователь вводит с клавиатуры. Посмотрите пример, в котором мы имеем топ 300 крупнейших городов России. На анимированной иллюстрации видно, как мы динамически сужаем список выбора, вводя подстроку “кр” или “ниж”, экономя огромное количество времени. Более того, список меняется после ввода каждого нового символа! Выглядит чрезвычайно привлекательно и профессионально, не так ли? Давайте разбираться, как это устроено.

Файл примера

Скачать

Пошаговая инструкция

Предварительные замечания

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

Шаг 1. Готовим таблицу для списка

Подготовьте таблицу с четырьмя колонками: Город (или то, что вам нужно), Статус , Индекс , Фильтр . Заполните столбец Город значениями. В остальных трёх колонках будут формулы, которые мы обсудим ниже. Я всем рекомендую использовать умную таблицу, так как это значительно проще.

Шаг 2. Формулы для столбца Статус

На примере ячейки F2 рассмотрим формулу, аналогичную для всего столбца Статус (столбец F ). Из F2 формулу можно протягивать вниз до конца, а в случае умной таблицы Excel это сделает за вас. Это также относится ко всем формулам, которые мы будем обсуждать в этой статье.

$B$2 – ячейка, с которой будет связан выпадающий список (добавляется на шаге 6). Что значит связано? Всё, что вы введёте в выпадающий список, тут же отразится в ячейке B2 .

Формула ПОИСК вернёт ошибку, если содержимое B2 не найдено в $E2 . ЕОШИБКА перехватит ошибку и вернёт ИСТИНА, если действительно была ошибка, и – ЛОЖЬ, если строка таки была найдена. Функция НЕ делает из истины ложь и наоборот (инверсирует результат). Таким образом, мы получим в этом столбце ИСТИНА, если подстрока найдена в текущем городе, и наоборот. Обратите внимание, что пустая подстрока содержится в любой строке, поэтому все ячейки столбца Статус имеют значения ИСТИНА, когда мы не ввели ещё ничего в B2 .

= НЕ( ЕОШИБКА ( ПОИСК ( $B$2 ; $E2 ) ) )

=NOT( ISERROR( SEARCH( $B$2; $E2) ) )

= НЕ ( ЕОШИБКА ( ПОИСК ( $B$2 ; [@ Город ]) ) )

=NOT( ISERROR( SEARCH( $B$2; [@Город]) ) )

[@ Город ] – на языке структурных формул умных таблиц это ссылка на ячейку столбца Город в той же строке, в которой находится сама формула. Поскольку ссылка идёт внутри таблицы, то имя самой таблицы в формуле можно не использовать. В остальном всё – тоже самое.

Шаг 3. Формула для столбца Индекс

Если B2 содержит подстроку поиска, то в столбце Статус не все ячейки примут значение ИСТИНА. Статус ИСТИНА будет только там, в чьи названия городов входит соответствующая подстрока. А в столбце Индекс мы рассчитываем номер по порядку для всех строк, которые содержат искомую подстроку. Например, на рисунке ниже B2 содержит “ни”, что заставляет столбец Статус быть истинным у строк с городами Нижний Новгород , Калининград , Магнитогорск и т.д., а в столбце Индекс мы начинаем считать факты срабатываний в F : Нижний Новгород – первое срабатывание, Калининград – второе и так далее.

Функция ЕСЛИ отсекает все значения в F , которые не равны ИСТИНА. Функция СЧЁТЕСЛИ подсчитывает количество значений ИСТИНА в F .

Обычный диапазон	Умная таблица

= ЕСЛИ( $F2 ; СЧЁТЕСЛИ ( $F$2:$F2 ; ИСТИНА ); “”)

=IF( $F2; COUNTIF( $F$2:$F2; TRUE ); “”)

= ЕСЛИ ( [@ Статус ]; СЧЁТЕСЛИ ( $F$2 :[@ Статус ]; ИСТИНА ); “”)

=IF( [@Статус]; COUNTIF( $F$2:[@Статус]; TRUE ); “”)

Обратите внимание, что диапазон условия в СЧЁТЕСЛИ введен скользящий – вторая координата не закреплена – и во время протягивания она растёт пропорционально таблице. За счёт этого трюка мы получаем механизм подсчёта значения ИСТИНА. Например, 6-я строка будет подсчитывать ИСТИНУ по диапазону $F$2:$F6 (там одно значение – от Нижнего Новгорода ), а 41-я строка будет подсчитывать ИСТИНУ уже по диапазону $F$2:$F41 (а там уже 2 значения – от Нижнего Новгорода и от Калининграда ). Вот суть механизма. Это полезный приём, который стоит запомнить.

Шаг 4. Формула для столбца Фильтр

Теперь наша задача, опираясь на столбец Индекс , сформировать в столбце H отфильтрованный список городов, который необходимо показывать в выпадающем списке.

Обычный диапазон	Умная таблица

= ЕСЛИОШИБКА( ИНДЕКС ( стлГород ; ПОИСКПОЗ ( ЧСТРОК ( $G$2:$G2 ); стлИндекс ; 0) ); “”)

=IFERROR( INDEX( стлГород; MATCH( ROWS($G$2:$G2); стлИндекс; 0) ); “” )

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

= ЕСЛИОШИБКА ( ИНДЕКС ( [ Город ]; ПОИСКПОЗ ( ЧСТРОК ( $G$2 :[@ Индекс ]); [ Индекс ]; 0) ); “”)

=IFERROR( INDEX( [Город]; MATCH( ROWS($G$2:[@Индекс]); [Индекс]; 0) ); “” )

Не путайте: [ Индекс ] – ссылка на весь столбец, а [@ Индекс ] – ссылка на ячейку из этого столбца в текущей строке. Никакие дополнительные именованные диапазоны нам создавать нет никакой необходимости, так как мы пользуемся встроенным в умные таблицы сервисом при ссылке на столбцы.

Формула ЧСТРОК ( $G$2:$G2 ) используется для генерации последовательных номеров от 1 (для второй строки) до N (в строке N+1), равному количеству найденных подстрок. Просто генерируется диапазон соответствующего размера, а формула ЧСТРОК возвращает его высоту в строках.

Формула ПОИСКПОЗ ищет номер реальной строки, содержащий соответствующий индекс. Например, в столбце Фильтр мы видим Магнитогорск на третьей позиции, но в реальности он взят из E45 , так как в G45 стоит цифра 3, которую мы и нашли через ПОИСКПОЗ . То есть ПОИСКПОЗ сказал нам, что Магнитогорск находится в 45-й строке, а извлекли мы его оттуда уже при помощи формулы ИНДЕКС .

Если же при извлечении возникает ошибка (текущая строка находится ниже строки N+1), то формула возвращает пустую строку. За это отвечает ЕСЛИОШИБКА .

Источник: perfect-excel.ru

Надстройка: выпадающий список с поиском (комбо)

Надстройка для облегчения ввода значений в ячейку Excel

Автор: nerv
Last Update: 27/03/2012

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

А, может, иметь дело с одними теми же, но не структурированными данными?

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

Как это работает:

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

Посмотрим, что он умеет:

• Не содержит повторов (уникальный). Легко выявить однотипные данные;
• Отсортирован по возрастанию. Возможность быстро найти то, что нужно;
• После вызова сразу готов к поиску/выбору из списка. Лишние движения ни к чему;
• Позволяет искать с использованием специальных подстановочных символов (*.

и т.п.);

Осуществлять быстрый поиск по “шаблону”. Если ячейка, из которой был вызван список, содержит информацию, поиск будет произведен по ней;

Появляется рядом с текущей/активной ячейкой и не “убегает” за пределы экрана;

Навигация привычными стандартными клавишами: Up [Вверх], Down [Вниз], Page Up [На страницу Вверх ], Page Down [На страницу вниз];

Корректная работа со всеми типами данных: строки, даты, числа;

Обработка ошибок формул листа. Никаких пустых строк в списке;

Обработка защиты ячеек листа. В защищенные ячейки ввод запрещен;

Информация об общем количестве списка и найденных по запросу элементах;

Быстрый вызов по нажатию Ctrl+Enter;

Быстрое закрытие: клавиша Esc;

Быстрый ввод клавишей Enter

Помимо всего вышеперечисленного, позволяет сэкономить на размере файла за счет формирования списка “на лету”, который создается в разы быстрее, если данные упорядочены или частично упорядочены по возрастанию.

Отличия версии 1.6 от 1.5:

• новая, более мощная/быстрая процедура сортировки;
• переход после ввода на следующую ячейку (в зависимости от установок Excel);

• использования и формирования списка (подробнее во вложении “how to use”);
• поиска с учетом регистра и без него;
• маски поиска;
• заголовков.

Обычный диапазон	Умная таблица

Вложение	Размер	Загрузки	Последняя загрузка
nerv_DropDownList_1.6.zip	28.74 КБ	19	5 лет 45 недель назад

• 135886 просмотров

Комментарии

Автор этой надстройки – не я.
Тот, кто её написал, уже давно в комментах на сайте не отвечает, – а я не планирую поддерживать чужие решения.
Потому, комменты к этой статье я сейчас закрою.

На вопросы по этой надстройке больше отвечать некому.
Надстройка не поддерживается, не дорабатывается, – если хотите её использовать, то используйте в таком виде, в каком она сейчас есть.
Если нужно что-то аналогичное, – оформляйте заказ на сайте, сделаем «с нуля»

Уважаемый Игорь!
При работе у меня был сформирован список к примеру с 500 позиций в одном столбце, но уникальных элементов 100, возможно ли скопировать или экспортировать эти 100 уникальных элементов?
Спасибо за надстройку.

Люди добрые! Оч полезная надстройка! Но вот у меня одна проблема когда в двух соседних столбцах расположен “автопоиск” выдает ошибку (Unknown error). Допустим автопоиск настроен на ячейку А2 и Второй на В2. записан макрос на автозапуск
Sub va()
‘
‘ va Макрос
‘

‘
Application.Run “nerv_DropDownList.DropDownListShow”
End Sub

и этот макрос запускается командой
Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Dim lReply As Long

If Target.Cells.Count > 1 Then Exit Sub
If Not Application.Intersect(Range(“A2:A2000”), Target) Is Nothing Then
Application.Run “va”
End If
If Target.Cells.Count > 1 Then Exit Sub
If Not Application.Intersect(Range(“B2:B2000”), Target) Is Nothing Then
Application.Run “va”

End Sub
При переходе на ячейку А2 выходит окно поиска ввожу данные нажимаю enter, происходит переход на след ячейку B2 и выскакивает ошибка Unknown error!
Помогите советом что сделать !?

>Подскажите, как сделать так, чтобы можно было формировать запрос из другого файла
Тот же вопрос: можно ли в качестве “Шаблона Подстановки” использовать другую книгу?
Благодарю за шикарную надстройку.

ИНДЕКС() + ПОИСКПОЗ() Вам в помощь.

Саша, а зачем что-то привязывать.
Подключите файл как надстройку к Excel, – и он сам будет запускаться каждый раз вместе с Excel

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

Здравствуйте!
А сколько строк эта надстройка может обработать?
Можно ли сделать чтобы обрабатывала 200 000

Скажите, пожалуйста, как выбрать сразу несколько значений. Чтобы в одной ячейке было “Апельсин; Лайм”. Это возможно?

Здравствуйте! Могли бы Вы обновить файл nerv_DropDownList_1.6.zip? Файл скачивается, запускается excel, но ничего не открывается и ошибки не выдает. Спасибо!

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

Отличная штука, спасибо!

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

>> /////и как обеспечить проверку вводимых данных (запрет на ввод данных не из списка).

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

Excel 2007 – не получается так задать, макрос обходит запрет

Источник: excelvba.ru

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

Классический выпадающий список в ячейке листа Excel, сделанный через Данные – Проверка (Data – Validation) – простая и удобная штука, которую ежедневно применяют очень многие пользователи. Однако, у этого списка есть один весьма серьезный недостаток – в нём нет быстрого поиска по первым символам, т.е. фильтрации (отбора) только тех значений, куда введённый фрагмент входит как подстрока. Это серьезно ухудшает удобство пользования даже если в списке всего пара-тройка десятков позиций, а при нескольких сотнях убивает юзабилити напрочь.

Давайте рассмотрим как всё же реализовать подобный трюк. В качестве подопытного кролика возьмём список 250 лучших фильмов по версии IMDb:

Конечная цель – создать выпадающий список (ячейка G3), в котором можно будет быстро находить нужные фильмы, введя только жанр, год или фрагмент названия, например “гамп”.

Шаг 1. Определяем, кто нам нужен

Сначала нам нужно понять, какие из исходных ячеек нужно показывать в списке, т.е. определить содержится ли введённый в выпадающем списке текст (например, жанр “детектив”) в названии фильма. Для этого добавим слева от исходных данных еще один столбец с функцией ПОИСК (SEARCH ) , которая ищет заданную подстроку в тексте и выдает либо порядковый номер символа, где он был обнаружен, либо ошибку, если его там нет:

Теперь завернем нашу формулу в функцию проверки ЕЧИСЛО (ISNUMBER) , которая превратит числа в логическую ИСТИНУ (TRUE) , а ошибки – в ЛОЖЬ (FALSE) :

Теперь сделаем так, чтобы ЛОЖЬ превратилась в 0, а вместо ИСТИНА в столбце появились последовательно возрастающие индексы-числа 1,2,3. и т.д. Это можно сделать с помощью добавления к нашей же формуле ещё парочки функций:

Здесь функция ЕСЛИ (IF) проверяет что мы имеем (ИСТИНУ или ЛОЖЬ), и

• если была ИСТИНА, то выводит максимальное значение из всех вышестоящих чисел + 1
• если была ЛОЖЬ, то выводит 0

Шаг 2. Отбираем в отдельный список

Дальше – проще. Теперь банальной функцией ВПР (VLOOKUP) просто выведём все найденные названия (я добавил столбец с порядковыми номерами для удобства):

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

Шаг 3. Создаем именованный диапазон

Теперь создадим именованный диапазон, который будет ссылаться на отобранные фильмы. Для этого выбрем на вкладке Формулы команды Диспетчер имен – Создать (Formulas – Name Manager – Create) :

Имя диапазона может быть любым (например, Фильмы), а самое главное – это функция СМЕЩ (OFFSET) , которая и делает всю работу. Напомню её синтаксис, если вы подзабыли:

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

• В качестве начальной ячейки задаём первую ячейку списка отобранных элементов (E2).
• Сдвиги вниз и вправо у нас отсутствуют, т.е. равны нулю.
• Высота диапазона у нас соответствует максимальному значению индекса из столбца А.
• Ширина диапазона – 1 столбец.

Осталось сделать выпадающий список.

Шаг 4. Создаем выпадающий список

Выделим жёлтую ячейку (G2) и выберем на вкладке Данные команду Проверка данных (Data – Validation) . В открывшемся окне выбрем Список (List) в поле Тип данных (Allow) , а в качестве источника введем имя нашего созданного диапазона со знаком равно перед ним:

Чтобы Excel не ругался при вводе на неточное совпадение наших фраз с исходным списком, на вкладке Сообщение об ошибке (Error Alert) в этом окне нужно выключить флажок Выводить сообщение об ошибке (Show error alert) :

Вот и всё. Можно жать на ОК и наслаждаться результатом:

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

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

Всё, что мы делали на Шагах 1-3 заменяется одной(!) формулой, где новая функция ФИЛЬТР (FILTER) отбирает из исходного диапазона A2:A251 только те фильмы, которые содержат заданную подстроку.

А дальше останется при создании выпадающего списка указать в качестве источника первую ячейку диапазона отобранных фильмов (C2) и добавить к ней знак #, чтобы получить ссылку на весь динамический массив:

И всё. Никаких именованных диапазонов и медленных СМЕЩ, никаких танцев с дополнительными столбцами и формулами. Песня!

Источник: www.planetaexcel.ru

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

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

Как это работает.
На листе жмете Ctrl+Enter и рядом с ячейкой появляется список, который позволяет не только выбирать, но и производить поиск по интересующим Вас данным. Для ввода данных достаточно нажать просто Enter, а для закрытия формы – Esc. Вот перечень всего, что умеет вызванный список:

• Не содержит повторов (уникальный). Легко выявить однотипные данные;
• Отсортирован по возрастанию. Возможность быстро найти то, что нужно;
• После вызова сразу готов к поиску/выбору из списка. Лишние движения ни к чему;
• Позволяет искать с использованием специальных подстановочных символов (*.

и т.п.);

Осуществлять быстрый поиск по “шаблону”. Если ячейка, из которой был вызван список, содержит информацию, поиск будет произведен по ней;

Появляется рядом с текущей/активной ячейкой и не “убегает” за пределы экрана;

Навигация привычными стандартными клавишами: Up [Вверх], Down [Вниз], Page Up [На страницу Вверх ], Page Down [На страницу вниз];

Корректная работа со всеми типами данных: строки, даты, числа;

Обработка ошибок формул листа (спасибо EducatedFool). Ни каких пустых строк в списке;

Обработка защиты ячеек листа (спасибо EducatedFool). В защищенные ячейки ввод запрещен;

Информация об общем количестве списка и найденных по запросу элементах;

Быстрый вызов Ctrl+Enter;

Быстрое закрытие по Esc;

Быстрый ввод выбранных данных по Enter.

Помимо всего вышеперечисленного, позволяет сэкономить на размере файла за счет формирования списка “на лету”, который создается в разы быстрее, если данные упорядочены или частично упорядочены по возрастанию.

Отличия версии 1.6 от 1.5:

• новая, более мощная/быстрая процедура сортировки;
• переход после ввода на следующую ячейку (в зависимости от установок Excel).

• использования и формирования списка (подробнее во вложении “how to use”);
• поиска с учетом регистра и без него;
• маски поиска;
• заголовков.

nerv_DropDownList_1.6.zip (28,7 KiB, 7 174 скачиваний)

Author: nerv
Last Update: 27.03.2012

Источник: www.excel-vba.ru

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

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

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

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

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

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

1. Вручную через «точку-с-запятой» в поле «Источник».
2. Ввести значения заранее. А в качестве источника указать диапазон ячеек со списком.
3. Назначить имя для диапазона значений и в поле источник вписать это имя.

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

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

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

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

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

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

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

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

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

1. Сформируем именованный диапазон. Путь: «Формулы» – «Диспетчер имен» – «Создать». Вводим уникальное название диапазона – ОК.
2. Создаем раскрывающийся список в любой ячейке. Как это сделать, уже известно. Источник – имя диапазона: =деревья.
3. Снимаем галочки на вкладках «Сообщение для ввода», «Сообщение об ошибке». Если этого не сделать, Excel не позволит нам вводить новые значения.
4. Вызываем редактор Visual Basic. Для этого щелкаем правой кнопкой мыши по названию листа и переходим по вкладке «Исходный текст». Либо одновременно нажимаем клавиши Alt + F11. Копируем код (только вставьте свои параметры).
5. Сохраняем, установив тип файла «с поддержкой макросов».
6. Переходим на лист со списком. Вкладка «Разработчик» – «Код» – «Макросы». Сочетание клавиш для быстрого вызова – Alt + F8. Выбираем нужное имя. Нажимаем «Выполнить».

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

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

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

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

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

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

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

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

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

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

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

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

1. Создаем стандартный список с помощью инструмента «Проверка данных». Добавляем в исходный код листа готовый макрос. Как это делать, описано выше. С его помощью справа от выпадающего списка будут добавляться выбранные значения.
2. Чтобы выбранные значения показывались снизу, вставляем другой код обработчика.
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.

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

Источник: exceltable.com