Как сделать в эксель поиск по первым буквам в выпадающем списке

Как сделать в эксель поиск по первым буквам в выпадающем списке

Инструмент для суперудобного и быстрого ввода повторяющихся данных. Механика его работы проста: ставим активную ячейку в столбец, куда нужно ввести данные и выбираем соответствующую команду:

Появляется компактное окно с выпадающим списком, который поддерживает моментальный поиск и фильтрацию «на лету» по первым введенным символам:

Достаточно набрать небольшую часть требуемого текста и нажать клавишу Enter или Tab , чтобы подтвердить ввод остального.

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

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

Универсальный выпадающий список в Excel с поиском ⁠ ⁠

Уже сегодня выпадающие списки с поиском в Excel в основном создают с помощью динамических массивов – быстро, просто, динамично. Единственная проблема заключается в том, что динамические массивы пока, к сожалению, доступны лишь пользователям Office 365, а стандартным пользователям без платной подписки они станут доступными скорее всего лишь в новом пакете Microsoft Office 2021-го года (где-то в октябре).

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

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

689 постов 15K подписчиков

Правила сообщества

2. Публиковать посты соответствующие тематике сообщества

3. Проявлять уважение к пользователям

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

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

Важно — сообщество призвано помочь, а не постебаться над постами авторов! Помните, не все обладают 100 процентными знаниями и навыками работы с Office. Хотя вы и можете написать, что вы знали об описываемом приёме раньше, пост неинтересный и т.п. и т.д., просьба воздержаться от подобных комментариев, вместо этого предложите способ лучше, либо дополните его своей полезной информацией и вам будут благодарны пользователи.

Утверждения вроде «пост — отстой», это оскорбление автора и будет наказываться баном.

Давно хотел спросить — а почему посты не текстовые? Смотреть 16 минут то, что можно прочитать за пару минут, и настроить за несколько минут, не очень радует.

Подскажите, пожалуйста, где можно поискать специалиста по LibreOffice? Объясню зачем: есть задача перейти с MS Office на LibreOffice:

Перевести формирования отчетов из MS Office (Excel/Word) на LibreOffice (Calc Writer). Предлагаемое решение: Переписать функции библиотеки runtime.EXCEL и runtime.WORD. Пояснения: Для формирования отчетов используется набор процедур и функций двух библиотек (runtime.EXCEL и runtime.WORD), с помощью которых формируется текст скрипта на VBS.

Готовый текст скрипта включает в себя все необходимое для формирования офисного документа, начиная от создания необходимого com-объекта, формирование необходимых данных в открытом документе и, при необходимости, сохранение готового документа.

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

Как сделать в эксель поиск по первым буквам в выпадающем списке

Я собираюсь вам рассказать о фантастически полезном и эффектном приёме. Это одна из самых интересных вещей, которую мне доводилось видеть в 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), то формула возвращает пустую строку. За это отвечает ЕСЛИОШИБКА .

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

​Смотрите также​команду​ же самое со​ по первым символам​Большим и жирным плюсом​Отображать вкладку Разработчик на​ надстройку можно будет​ он уже может​ свое (извращенное) виденье​ считаю), в форму​ нужна. ​ список, и вставил​

​(в ней мы​ только те фамилии,​

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

​ Динамический диапазон. Для​. При выделении ячейки​ меню последовательно переходим​При работе в программе​Проверка данных (Data validation)​ списками Форд и​

​ из моих данных​ этого способа является​ ленте (Office Button​ посмотреть у Вас​ получить к ним​ проблемы ^_^​ должны подставляться данные​Сейчас по колдовал​ диапозон).​ будем выбирать страну),​ которые начинаются с​ этого при создании​ справа от ячейки​ по пунктам «Объект​ Microsoft Excel в​

​выбрать из выпадающего списка​ Ниссан, задав соответственно​в первую очередь.​ возможность быстрого перехода​ — Excel Options​ на сайте?​ доступ практически мгновенно​

​GIG_ant​ «на лету» c​ с это надстройкой. ​Проблема в том,​ откройте вкладку​ буквы, выбранной первым​ Имени Список_элементов в​ появляется квадратная кнопка​ ComboBox» и «Edit».​ таблицах с повторяющимися​ вариант проверки​ имена диапазонам​

​RAN​ к нужному элементу​ — Show Developer​Serge​ : )​: Да просто у​ того же столбца,​ в принципе штука​

​ что необходимый город​Data​ списком. Для решения​ поле Диапазон необходимо​ со стрелкой для​Выпадающий список в Microsoft​ данными, очень удобно​Список (List)​Ford​: Оказывается на новый​ в списке при​ Tab in the​

​: А что, отсюда​nerv​ меня все ближайшие​ например, где работает​ хорошая, но как​ теперь найти сложно.​(Данные), нажмите​ такой задачи может​ записать формулу =СМЕЩ(Список!$A$1;;;СЧЁТЗ(Список!$A:$A))​

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

​ выбора элементов из​ Excel готов.​ использовать выпадающий список.​и указать в​и​ форум переехало не​ вводе первых букв​ Ribbon)​ она Вам не​: Пока не забыл,​ клавиши уже заняты​ сам пользователь. Т.о.​ оказалось не совсем​Как сделать что​

​Data Validation​ быть использована структура​Использование функции СЧЁТЗ() предполагает,​ выпадающего списка.​Чтобы сделать и другие​ С его помощью​

​ качестве​Nissan​ все.​ с клавиатуры(!), чего​Найдите значок выпадающего списка​ нравится?​ опять же про​ под разные фишки)).​ у него есть​ то , что​ бы в этом​(Проверка данных), а​ Связанный список или​ что заполнение диапазона​

​Недостатки​ ячейки с выпадающим​ можно просто выбирать​Источника (Source)​.​

​Поглядите комментарии Здесь​ нет у всех​ среди элементов управления​Kroha​

​ позиционирование: если опустить​ Ну уж вставить​ возможность сформировать свой​ я хочу. А​ списке появилась строка​ затем в выпадающем​ Вложенный связанный список.​

​ ячеек (​этого подхода: элементы​ списком, просто становимся​ нужные параметры из​=Марки​

​При задании имен помните​ http://oldplanetaexcelforum.ru/tip.php?aid=98​

​ остальных способов. Приятным​ форм (не ActiveX!).​: Все же хотелось​ на 1-ин и​ другое сочетание клавиш​ список, а не​ хочу я что​

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

​ поиска, при наборе​ меню выберите​ПРЕДСТАВЬТЕ СИТУАЦИЮ:​A:A​ списка легко потерять​ на нижний правый​ сформированного меню. Давайте​или просто выделить​ о том, что​user0​ моментом, также, является​ Ориентируйтесь по всплывающим​ бы в виде​ более экранов вниз,​ я думаю не​ работать по раннее​ бы при нажатии​

​ в которой напр.​Data Validation​Мы хотим создать​), который содержит элементы,​ (например, удалив строку​ край готовой ячейки,​

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

​ подсказкам -​ надстройки!​ то форма уже​ составит труда.​ созданному. Но эт​

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

​ раскрывающийся список различными​

​ они на том​ Excel не должны​ Попробую пока предложенный​ представления (цветов, шрифтов​Поле со списком​У кого мало времени​ не видна( Вообщем,​Спасибо за отзыв,​

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

Выпадающий список в MS EXCEL на основе Проверки данных

​ надо думать, но​ буду думать как​GIG_ant​ строкой поиска и​ Е), ЕК (всегорода​Data Validation​ выбрать страну и​ примера, лист Динамический​B1​Также, в программе Excel​Скачать последнюю версию​ список).​ препинания и начинаться​На всякий продублирую​При использовании этого способа,​Щелкните по значку​ ухватить суть -​

​ эт уже не​ еще наворотить и​: 1. Если двойной​

​ что бы поиск​ на ЕК) и​(Проверка вводимых значений).​ соответствующий ей город.​ диапазон).​); не удобно вводить​

​ можно создавать связанные​ Excel​А вот для зависимого​ обязательно с буквы.​ здесь код и​ также возможно указывать​ и нарисуйте небольшой​

​ смотрим обучающее видео:​ сегодня : )​

А. Простейший выпадающий список — ввод элементов списка непосредственно в поле Источник

​ сделать поуниверсальней.​ клик не удобен​ начинался с первых​ так далее.​Мы хотим дать пользователю​ При этом с​

​Используем функцию ДВССЫЛ()​​ большое количество элементов.​​ выпадающие списки. Это​Самым удобным, и одновременно​ списка моделей придется​ Поэтому если бы​​ комментарий автора на​​ в качестве​ горизонтальный прямоугольник -​

​Кому интересны подробности и​igorss​С.М.​ можно назначить сочетание​ букв, а не​Эт вообще возможно?​

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

​ListFillRange​​ будущий список.​ нюансы всех описанных​: Супер!​: Антон,​ клавиш.​​ со скобок. ​​Спасибо.​ вариантов, поэтому в​ необходимо ограничить доступные​ перечень элементов, расположенных​ маленьких (3-5 значений)​
​ при выборе одного​​ создания выпадающего списка,​

Б. Ввод элементов списка в диапазон (на том же листе, что и выпадающий список)

​ с функцией​ марок автомобилей присутствовал​ «Выпадающий список с​не только одномерные​Щелкните по нарисованному списку​ способов — дальше​Пару вопросов для​1. я удалил​

​2. Форма закрывается​asd192​Судя по количеству​​ поле​​ пользователям варианты стран​ на другом листе,​

​ неизменных списков.​​ значения из списка,​ является метод, основанный​СМЕЩ​ бы пробел (например​ добавлением новых элементов»​
​ диапазоны. Можно, например​​ правой кнопкой мыши​ по тексту.​ образовательных целей​ бы​ при изменении выделения​: Вроде как то,​ просмотров, и нулевому​Allow​​ и городов, из​​ является использование функции​Преимущество​ в другой графе​ на построении отдельного​(OFFSET)​ Ssang Yong), то​ (ссылка на новую​ задать диапазон из​ и выберите команду​

​Один щелчок правой кнопкой​1. nerv, как​Private Sub Worksheet_SelectionChange(ByVal​ на листе если​ что вам нужно​ количеству ответов, то​(Тип данных) выберите​ которых они могут​ ДВССЫЛ(). На листе​: быстрота создания списка.​ предлагается выбрать соответствующие​

​ списка данных.​, который будет динамически​ его пришлось бы​ версию сайта).​ двух столбцов и​

B. Ввод элементов списка в диапазон (на любом листе)

​Формат объекта (Format control)​ мыши по пустой​ в Вашем коде​ Target As Range)​ выделенная ячейка не​ (ближе к концу​

​ что я хочу​List​ выбирать. В первой​ Пример, выделяем диапазон​

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

​200?’200px’:»+(this.scrollHeight+5)+’px’);»>Private Sub Worksheet_Change(ByVal Target​ нескольких строк, указав​. В появившемся диалоговом​ ячейке под столбцом​ реализована сортировка?​If Intersect([A:A], Target)​ относится к первому​ видео)​

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

• ​ ячейки моделей определенной​​ и в имени​​ As Range)​
• ​ дополнительно, что выводить​ окне задайте​
• ​ с данными, команда​2. что нужно​ Is Nothing Or​

​ столбцу, или клавиша​http://www.planetaexcel.ru/tip.php?aid=34​ikki​ поле​

• ​ выбор страны, а​
• ​ содержать выпадающий список,​ диапазоне на листе​ списке продуктов картофеля,​

​ выпадающее меню, а​
​ марки. Для этого:​ диапазона на нижнее​Dim lReply As​ нужно два столбца​Формировать список по диапазону​​ контекстного меню​​ изменить в коде​ Target.Count > 1​ ESC.​boulingist​

​: простыми способами -​Source​ во второй будут​ вызываем Проверку данных,​ EXCEL, а затем​ предлагается выбрать как​ также делаем отдельным​Нажмите​

​ подчеркивание (т.е. Ssang_Yong).​ Long, i As​ (свойство​​- выделите ячейки​​Выбрать из раскрывающегося списка​ что-бы выполнялся не​ Then Unload MainForm​3. Что бы​: Супер ссылка. сейчас​

​ нет.​

​(Источник), где необходимо​ доступны только принадлежащие​ в Источнике указываем​ в поле Источник​ меры измерения килограммы​ списком данные, которые​Ctrl+F3​Теперь создадим первый выпадающий​ Integer​ColumnCount​ с наименованиями товаров,​

​(Choose from drop-down list)​​ ввод значения в​End Sub​ форма не вылазила​ просматриваю всё. Огромное​можно реализовывать через​ указать имя диапазона​

​ выбранной стране города.​ =ДВССЫЛ(«список!A1:A4»).​ инструмента Проверки данных​

​ и граммы, а​ в будущем включим​или воспользуйтесь кнопкой​ список для выбора​Dim counter As​=2). Тогда можно получить​

• ​ которые должны попасть​или нажать сочетание​
• ​ ячейку, а вызывалась​Есть кнопка Закрыть​ за края экрана​ спасибо. ​
• ​ ComboBox, обработку нажатий​ со странами. Введите​ Думаю, это понятно?​
• ​Недостаток​ указать ссылку на​ при выборе масла​ в это меню.​

​Диспетчер имен (Name manager)​ марки автомобиля. Выделите​ Integer, MyName As​ весьма привлекательные результаты,​ в список​ клавиш​ другая процедура, т.е.​ и Esc, зачем​ можно наворотить код​Теперь бы еще​ клавиш, перезаполнение списка​ в этом поле​Итак, давайте начнём наш​: при переименовании листа​

​ этот диапазон.​ растительного – литры​ Эти данные можно​на вкладке​ пустую ячейку и​

​ String, NameRange As​
​ окупающие все потраченные​Связь с ячейкой​ALT+стрелка вниз​ по форме нашел​ третий вариант ?​ с использованием дополнительных​ с календарем так​ и т.п.​ «=Country» и жмите​ простой пример с​ – формула перестает​Предположим, что элементы списка​ и миллилитры.​ размещать как на​Формулы (Formulas)​ откройте меню​ String​ на дополнительные настройки​- укажите ячейку​. Способ не работает,​ значение из списка​Это лишнее событие​ функций (ZVI приводил​ же разобраться. ​геморрой еще тот.​ОК​

​ того, как можно​​ работать. Как это​
​ шт;кг;кв.м;куб.м введены в​Прежде всего, подготовим таблицу,​ этом же листе​. В версиях до​Данные — Проверка (Data​If Target.Cells.count >​ усилия:​ куда нужно выводить​ если ячейку и​ нажал Enter -​ не позволит, например,​

​ пример с календарем.),​GIG_ant​проще так:​. Теперь нам нужно​ создать связанный (или​ можно частично обойти​ ячейки диапазона​ где будут располагаться​ документа, так и​ 2003 это была​ — Validation)​

​ 1 Then Exit​Способ 1.​ порядковый номер выбранного​ столбец с данными​ результат — вызов​ ничего скопипастить на​ но я решил​: Добрый день Планетяне.​1) иметь отсортированный​ сделать второй раскрывающийся​ зависимый) выпадающий список​ см. в статье​A1:A4​ выпадающие списки, и​ на другом, если​ команда меню​или нажмите кнопку​ Sub​

Создаем связанные выпадающие списки в Excel – самый простой способ!

​Примитивный​​ пользователем элемента.​ отделяет хотя бы​ процедуры.​ листе.​ не загромождать код.​По роду своей​ список.​ список, чтобы пользователи​ в Excel? В​ Определяем имя листа.​, тогда поле Источник​ отдельно сделаем списки​ вы не хотите,​Вставка — Имя -​Проверка данных (Data Validation)​If Target.Address =​Способ 2.​Количество строк списка​

​ одна пустая строка​nerv​2. убрал бы​nerv​ работы мне довольно​2) при большом​ могли выбрать город.​​ ячейке​​Ввод элементов списка в​ будет содержать =лист1!$A$1:$A$4​ с наименованием продуктов​​ чтобы обе таблице​​ Присвоить (Insert -​на вкладке​ «$D$2» Then​

​Стандартный​- сколько строк​ или вам нужен​: Всем доброго времени​ звёздочку после Criteria1: data:image/gif;base64,R0lGODdhAQABAIAAAP///wAAACwAAAAAAQABAAACAkQBADs=» data-src=»https://img.my-excel.ru/excel-vybor-neskolkih-znachenij-iz-vypadajushhego_7_1.jpg» alt=»Связанный выпадающий список в Excel» width=»565″ height=»165″>

​Выделяем данные, которые планируем​Создайте новый именованный диапазон​если у вас​ Exit Sub​Элемент управления​ списке. По умолчанию​ ни разу не​Вроде как более-менее​ Criteria1:=»=*» & tbIn.Text​ за края экрана​ из различных списков​ иерархию. соответственно, работать​ ячейку​

​ страну, а в​Если необходимо перенести диапазон​ и простота его​ именованный диапазон, как​ занести в раскрывающийся​ с любым именем​ Excel 2007 или​counter = Len(Target)​Способ 4.​ — 8, но​​ вводился выше:​​ слепил форму с​​ & «*»​​ можно наворотить код​ (клиенты, товары и​ с двумя связанными​​B2​​ ячейке​

​ с элементами выпадающего​​ модификации. Подход годится​​ это мы уже​ список. Кликаем правой​ (например​​ новее. Затем из​​MyName = Left(Target,​

​Элемент ActiveX​​ можно больше, чего​​Выделите ячейки с данными,​​ выпадающим списоком​​Искать «Яблоки» на​ с использованием дополнительных​ т. д.). Если​​ списками — выбирать​​. А теперь внимание​B2​ списка в другую​

​ для редко изменяющихся​

​ делали ранее с​​ кнопкой мыши, и​​Модели​ выпадающего списка​

​ counter)​Сложность​ не позволяет предыдущий​

​ которые должны попасть​+ Форма открывается​ букву «л» ?​ функций (ZVI приводил​ список небольшой использую​​ сначала регион, а​​ – фокус! Нам​– принадлежащий ей​ книгу (например, в​​ списков.​​ обычными выпадающими списками.​​ в контекстном меню​​) и в поле​Тип данных (Allow)​For i =​​низкая​​ способ.​

​ в выпадающий список​​ рядом с текущей/активной​​GIG_ant​

​ пример с календарем.),​ стандартный екселевский выбор​ потом — города​ нужно проверить содержимое​​ город, как на​​ книгу Источник.xlsx), то​​Недостатки​​В первой ячейке создаём​ выбираем пункт «Присвоить​​Ссылка (Reference)​​выберите вариант​ 1 To Range(«People»).count​средняя​После нажатия на​ (например, наименованиями товаров).​​ ячейкой. Нет нужды​​: По первому пункту​ но я решил​ из списка через​ из этого региона.​ ячейки с названием​ примере:​ нужно сделать следующее:​​: если добавляются новые​​ список точно таким​ имя…».​в нижней части​Список (List)​NameRange = Left(Range(«People»).Cells(i),​высокая​ОК​Если у вас Excel​ бегать глазами​​ частично согласен, хотя​​ не загромождать код.​ проверку данных. Но​boulingist​​ страны (ячейка B1),​​Для начала нужно создать​в книге Источник.xlsx создайте​ элементы, то приходится​​ же образом, как​​Открывается форма создания имени.​ окна введите руками​​и в поле​​ counter)​Возможность настройки шрифта, цвета​списком можно пользоваться.​ 2003 или старше​+ Создается список​ тогда выбранное найденное​На счет этого​ при большом размере​: Прошу простить не​ чтобы получить индекс​

​ базу данных. На​
​ необходимый перечень элементов;​

​ вручную изменять ссылку​ делали это ранее,​ В поле «Имя»​​ следующую формулу:​​Источник (Source)​If LCase(NameRange) =​ и т.д.​Чтобы вместо порядкового номера​​ — выберите в​​ из активного столбца.​ значение можно будет​ согласен. Пусть будет​

​ исходного списка данный​ совсем далекого человека​ соответствующий базе данных​

​ втором листе я​в книге Источник.xlsx диапазону​ на диапазон. Правда,​ через проверку данных.​ вписываем любое удобное​​=СМЕЩ($A$1;ПОИСКПОЗ($G$7;$A:$A;0)-1;1;СЧЁТЕСЛИ($A:$A;$G$7);1)​​выделите ячейки с​ LCase(MyName) Then​нет​ элемента выводилось его​

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

​ в качестве источника​Во второй ячейке тоже​
​ наименование, по которому​
​=OFFSET($A$1;MATCH($G$7;$A:$A;0)-1;1;COUNTIF($A:$A;$G$7);1)​

​ названиями марок (желтые​

как в сделать выпадающий список с функцией поиска?

​Application.EnableEvents = False​​нет​
​ название можно дополнительно​Вставка — Имя -​
​ создавался откуда угодно,​ ячейку на листе,​ Но тем не​ так как пролистывать​ но что есть​ пользователь выберет​ которые хочу дать​ элементов присвойте Имя,​ можно определить сразу​ запускаем окно проверки​ будем узнавать данный​Ссылки должны быть абсолютными​ ячейки в нашем​Range(«D2») = Range(«People»).Cells(i)​да​ использовать функцию​ Присвоить​ в том числе​ а не только​ менее, согласитесь, не​ выпадающий список до​ комбобокс, и есть​Portugal​
​ пользователям на выбор​ например СписокВнеш;​ более широкий диапазон,​
​ данных, но в​ список. Но, это​ (со знаками $).​ примере). После нажатия​Application.EnableEvents = True​Количество отображаемых строк​ИНДЕКС (INDEX)​(Insert — Name -​ с уже заготовленных​ в первом столбце.​
​ очень удобно) Я​
​ нужного значения долго​
​ ли пошаговая инструкция​, то мы должны​ в первом раскрывающемся​откройте книгу, в которой​ например,​

​ графе «Источник» вводим​​ наименование должно начинаться​ После нажатия Enter​
​ на​Exit For​всегда 8​, которая умеет выводить​
​ Define),​
​ «шаблонов»​
​ Может так даже​ бы и сам,​
​ и муторно.​ как реализовать задумочку​ обратиться к базе​ списке, а в​ предполагается разместить ячейки​A1:A100​ функцию «=ДВССЫЛ» и​ обязательно с буквы.​ к формуле будут​

​ОК​​End If​любое​ содержимое нужной по​если Excel 2007​+ Список не​ и лучше.​ если взялся за​Поэтому решил сделать​

​ мою?​​ с индексом​ соседнем столбце указал​ с выпадающим списком;​
​. Но, тогда выпадающий​​ адрес первой ячейки.​​ Можно также вписать​
​ автоматически добавлены имена​
​первый выпадающий список​Next​
​Быстрый поиск элемента по​ счету ячейки из​ или новее -​
​ содержит повторов (уникальный)​По второму: Если​
​ подобный «проект» не​ форму для поиска​
​ikki​3​
​ числовой индекс, который​
​выделите нужный диапазон ячеек,​ список может содержать​ Например, =ДВССЫЛ($B3).​ примечание, но это​ листов — не​ готов:​

​If WorksheetFunction.CountIf(Range(«People»), Target)​​ первым буквам​ диапазона:​ откройте вкладку​

​+ Список отсортирован​​ убрать звездочку то​ стал воротить с​ и вставки значения​: панель инструментов «Элементы​
​, в которой хранятся​ соответствует одному из​ вызовите инструмент Проверка​ пустые строки (если,​
​Как видим, список создан.​ не обязательно. Жмем​ пугайтесь :)​Теперь создадим второй выпадающий​ = 0 Then​нет​Этот способ частично напоминает​
​Формулы (Formulas)​ по возрастанию. Если​ искать будет с​ API. С другой​​ в ячейку.​​ управления» — кнопка​
​ названия городов Португалии.​​ списков городов. Списки​​ данных, в поле​ например, часть элементов​Теперь, чтобы и нижние​ на кнопку «OK».​
​Функция​​ список, в котором​​lReply = MsgBox(«Добавить​нет​​ предыдущий. Основное отличие​​и воспользуйтесь кнопкой​ данные на листе​ начала слова в​

​ стороны, не уверен,​​Во вложении мое​ «Поле со списком»​ Мы воспользуемся функцией​

​ городов располагаются правее​​ Источник укажите =ДВССЫЛ(«[Источник.xlsx]лист1!СписокВнеш»);​
​ была удалена или​ ячейки приобрели те​Переходим во вкладку «Данные»​СМЕЩ (OFFSET)​ будут отображаться модели​

​ введенное имя «​​да​ в том, что​Диспетчер имен (Name Manager)​ предварительно отсортированы, то​ справочние , что​ но может быть​ видение решения данного​
​у​ВПР​ в столбцах​При работе с перечнем​ список только что​ же свойства, как​ программы Microsoft Excel.​умеет выдавать ссылку​ выбранной в первом​ & _​Необходимость использования дополнительной функции​ на лист добавляется​, затем​ список формируется почти​ не всегда удобно.​ можно как-то стандартными​

​ вопроса.​​меня​(VLOOKUP) для поиска​D​ элементов, расположенным в​
​ был создан). Чтобы​

​ и в предыдущий​​ Выделяем область таблицы,​ на диапазон нужного​ списке марки. Также​
​Target & «​ИНДЕКС​ не элемент управления,​

Альтернатива выпадающему списку (форма с поиском)

​Создать​​ моментально. Для сравнения​
​ Например если есть​ способами отследить «Видимую​Принимаю все предложения​пошаговой — нет.​ значения из ячейки​,​ другой книге, файл​ пустые строки исчезли​ раз, выделяем верхние​ где собираемся применять​ размера, сдвинутый относительно​ как в предыдущем​ в выпадающий список?»,​нет​ а элемент ActiveX​. Введите имя (можно​ в файле, во​ куча ООО «. «​
​ область экрана»? Эт​ и замечания по​но примерно так:​B1​
​F​ Источник.xlsx должен быть​ необходимо сохранить файл.​
​ ячейки, и при​ выпадающий список. Жмем​ исходной ячейки на​

​ случае, откройте окно​​ vbYesNo + vbQuestion)​да​»Поле со списком»​ любое, но обязательно​ 2-м столбце 1864​
​ то нужно будет​ в качестве информации​ доработке данной формы.​0) изучить основы​в таблице с​

​и​​ открыт и находиться​Второй недостаток: диапазон источника​ нажатой клавише мышки​ на кнопку «Проверка​ заданное количество строк​Проверки данных​If lReply =​нет​из раскрывающегося набора​

​ без пробелов и​​ записи, не более​ набирать ООО «​ к размышлению :​vikttur​ VBA;​ названиями стран. После​H​ в той же​
​ должен располагаться на​ «протаскиваем» вниз.​ данных», расположенную на​ и столбцов. В​
​, но в поле​

​ vbYes Then​
​Возможность создания связанных выпадающих​ под кнопкой​ начать с буквы!)​ 1-ой сек. и​
​ и наименование предприятия,​ )​
​: GIG_ant, были же​1) изучить свойства​ того как индекс​. Так, например, рядом​ папке, иначе необходимо​

​ том же листе,​​Всё, таблица создана.​ Ленте.​ более понятном варианте​Источник​Range(«People»).Cells(Range(«People»).Rows.count + 1,​ списков​Вставить (Insert)​ для выделенного диапазона​

​ мы имеем 1859​​ а в моем​GIG_ant​ темы по поиску​ и события объекта​ будет известен, мы​ с​ указывать полный путь​ что и выпадающий​Мы разобрались, как сделать​Открывается окно проверки вводимых​

​ синтаксис этой функции​нужно будет ввести​ 1) = Target​нет​с вкладки​ (например​ уникальных.​

​ случае можно набрать​: По поводу сортировки​ значения по первым​ ComboBox;​ выберем список, который​France​ к файлу. Вообще​ список, т.к. для​ выпадающий список в​ значений. Во вкладке​ таков:​ вот такую формулу:​Range(«People»).Sort Key1:=Range(«A1»), _​

​да​​Разработчик (Developer)​Товары​+ Навигация привычными​ часть наименования и​
​ я думал, можно​ буквам. Там может​2) вставить элемент​ станет источником данных​стоит индекс​ ссылок на другие​ правил Проверки данных нельзя​
​ Экселе. В программе​ «Параметры» в поле​=СМЕЩ(начальная_ячейка; сдвиг_вниз; сдвиг_вправо; размер_диапазона_в_строках;​=ДВССЫЛ(F3)​Order1:=xlAscending, Header:=xlNo​нет​:​). Нажмите​ «стандартными» клавишами: Up,​

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

​ размер_диапазона_в_столбцах)​или =INDIRECT(F3)​Else​balzaminka​Механизм добавления тот же​ОК​ Down, Page Up,​С.М.​ сортировать список. Но​Если уже смотрел​3) написать макросы​ выпадающего списка. Для​, который соответствует списку​ или использовать Личную​ другие листы или​ простые выпадающие списки,​ параметр «Список». В​Таким образом:​где F3 — адрес​

​Target = «»​​: Добрый день!​ — выбираем объект​.​ Page Down​: По п.2 :​ исходя из опыта​ и нужны советы​ обработки нужных событий;​ этого напишем такую​ городов​ книгу макросов Personal.xlsx​ книги (это справедливо​ так и зависимые.​ поле «Источник» ставим​начальная ячейка — берем​
​ ячейки с первым​End If​Модераторам — извиняюсь​

​ из списка и​​Выделите ячейки (можно сразу​+ Информация о​А если сделать​ работы со списками/справочниками,​ по данному решению​4) отладить, протестировать,​

​ формулу:​2​ или Надстройки.​ для EXCEL 2007​ При этом, можно​ знак равно, и​ первую ячейку нашего​ выпадающим списком (замените​End If​ за свою невнимательность,​ рисуем его на​ несколько), в которых​ количестве элементов списка​ поиск по первым​ хочу сказать что​ — что ж,​ запустить;​=CHOOSE(VLOOKUP(B1,Sheet3!$A$3:$B$5,2,FALSE),England,France,Portugal)​. Позже Вы увидите,​Если нет желания присваивать​ и более ранних).​

​ использовать различные методы​​ сразу без пробелов​
​ списка, т.е. А1​ на свой).​End If​
​ приношу извинения всем​ листе. А вот​

​ хотите получить выпадающий​​+ Быстрый вызов​ буквам слов: пробел​
​ в большинстве своем​ я промазал :)​5) enjoy it!​=ВЫБОР(ВПР(B1;Sheet3!$A$3:$B$5;2;ЛОЖЬ);England;France;Portugal)​
​ как этот индекс​ имя диапазону в​

​Избавимся сначала от второго​
​ создания. Выбор зависит​ пишем имя списка,​сдвиг_вниз — нам считает​Все. После нажатия на​End Sub​

​ за свои ошибки. ​ дальше начинаются серьезные​ список и выберите​ Ctrl+Enter​ & шаблон ?​ они являются статическими,​

​GIG_ant​​впрочем — интернет​Что же делает эта​ будет использован.​ файле Источник.xlsx, то​ недостатка – разместим​ от конкретного предназначения​ которое присвоили ему​ функция​

​ОК​fanishe​Вопрос — как​ отличия от предыдущего​

​ в меню (на​​+ Быстрое закрытие​
​(только тогда от​ то есть изначально​
​: Недавно обсуждался вопрос​ большой, задача не​
​ формула? Она ищет​Если Вы работаете в​ формулу нужно изменить​ перечень элементов выпадающего​
​ списка, целей его​
​ выше. Жмем на​ПОИСКПОЗ (MATCH)​содержимое второго списка​
​: доброго времени суток,​ сделать в обычной​ способа.​ вкладке)​
​ Esc​ автофильтра придётся отказаться).​
​ заполняются и относительно​ по усовершенствованию выпадающего​ слишком оригинальная, скорее​
​ значение из ячейки​ Excel 2010, то​

​ на =ДВССЫЛ(«[Источник.xlsx]лист1!$A$1:$A$4»)​​ списка на другом​ создания, области применения,​ кнопку «OK».​, которая, попросту говоря,​ будет выбираться по​ а можно ли​ таблице выпадающий список​Во-первых, созданный выпадающий ActiveX​Данные — Проверка (Data​+ Быстрый ввод​

​Маугли​ редко дополняются новыми​ списка (тема: Интересные​ всего, где-то уже​B1​ можете создать лист-источник​СОВЕТ:​ листе.​ и т.д.​Выпадающий список готов. Теперь,​ выдает порядковый номер​ имени диапазона, выбранного​ объекту Range передать​ с контекстным поиском?​ список может находится​

​ — Validation)​​ Enter​
​: Антон, предложил своим​ элементами. При добавлении​ вопросы по VBA),​ есть готовый работающий​
​в списке стран​ в отдельной рабочей​

​Если на листе​​В правилах Проверки данных (также​Автор: Максим Тютюшев​ при нажатии на​ ячейки с выбранной​

​ в первом списке.​​ список. который находится​buchlotnik​ в двух принципиально​. Из выпадающего списка​- Хромает позиционирование​ ребятам ваш варинт​ ничего не стоит​ а я со​

​ пример​​ и возвращает соответствующий​ книге. Если же​ много ячеек с​ как и Условного​При заполнении ячеек данными,​ кнопку у каждой​ маркой (G7) в​Минусы​ на другом листе?​: поиском религия пользоваться​

​ разных состояниях -​​Тип данных (Allow)​ при масштабе отличном​
​ post_270006.xls -балдеют от​ и сортирнуть.​ своей стороны решил​boulingist​ индекс, который затем​ у Вас версия​ правилами Проверки данных,​
​ форматирования) нельзя впрямую​ часто необходимо ограничить​ ячейки указанного диапазона​ заданном диапазоне (столбце​такого способа:​Pelena​ не позволяет?​ режиме отладки, когда​выберите вариант​ от 100%​ удовольствия.Спасибо!​
​Хотя для удобства​ предложить другой вариант.​
​: я правильно понимаю,​ использует функция​
​ Excel 2003 года,​

​ то можно использовать​ указать ссылку на​ возможность ввода определенным​ будет появляться список​

​ А)​​В качестве вторичных (зависимых)​:​тыц​ можно настраивать его​Список (List)​Жду Ваших замечаний​nerv​ наверно следует добавить​ Для удобства. Авось​

​ что речь идет​​CHOOSE​ и Вы планируете​ инструмент Выделение группы​ диапазоны другого листа​ списком значений. Например,​ параметров, среди которых​сдвиг_вправо = 1, т.к.​ диапазонов не могут​fanishe​тыцтыц​

​ параметры и свойства,​​и введите в​
​ и предложений по​: Сижу леплю форму.​
​ код сортировки.​ кому то понадобится.​ об excel?​
​(ВЫБОР), чтобы выбрать​ использовать именованный диапазон,​ ячеек (Главная/ Найти​ (см. Файл примера):​ имеется ячейка, куда​ можно выбрать любой​ мы хотим сослаться​ выступать динамические диапазоны​, прочитайте Правила форума​user0​ двигать его по​

​ строчку​​ данному вопросу :​ Как показал эксперимент​

​nerv​nerv​ikki​
​ 1-й, 2-й или​ то значения должны​ и выделить/ Выделение​Пусть ячейки, которые должны​
​ пользователь должен внести​ для добавления в​ на модели в​ задаваемые формулами типа​ и создайте свою​: Доброго дня,​ листу и менять​
​Источник (Source)​ )​
​ у меня (да​: >>Но исходя из​: 1. Вызов по​: не знаю, как​ 3-й именованный диапазон.​ находиться в той​ группы ячеек). Опция​ содержать Выпадающий список,​ название департамента, указав​ ячейку.​ соседнем столбце (В)​СМЕЩ (OFFSET)​
​ тему в соответствующем​Полагаю той вопрос​ размеры и -​знак равенства и​
​С уважением, nerv​ и у Вас​
​ опыта работы со​ двойному клику (лично​
​ Вы, а я​Вот так будет выглядеть​
​ же книге, можно​ Проверка данных этого​

​ размещены на листе​ где он работает.​Второй способ предполагает создание​

​размер_диапазона_в_строках — вычисляем с​. Для первичного (независимого)​ разделе. Эта тема​ возникал не раз,​
​ режиме ввода, когда​

​ имя диапазона (т.е.​

​igorss,​ похоже тоже) беда​ списками/справочниками, хочу сказать​
​ для меня) крайне​
​ именно об Excel’е​ наш второй раскрывающийся​ на другом листе.​ инструмента позволяет выделить​ Пример,​ Логично, предварительно создать​
​ выпадающего списка с​

​ помощью функции​​ списка их использовать​ закрыта​ но не нашел​

​ единственное, что можно​=Товары​>>nerv, как в​

​ с позиционированием при​​ что в большинстве​ не удобен. Я​ толкую​
​ список:​Мы будем использовать именованные​ ячейки, для которых​
​а диапазон с перечнем​ список департаментов организации​

​ помощью инструментов разработчика,​СЧЕТЕСЛИ (COUNTIF)​ можно, а вот​Этот фокус основан на​

​ простого решения поиском..​ — выбирать из​).​ Вашем коде реализована​
​ изменении масштаба :​ своем они являются​ вообще стараюсь не​

​правда, «панель инструментов»​​В результате мы получим​ диапазоны и сделаем​ проводится проверка допустимости​ элементов разместим на​
​ и позволить пользователю​ а именно с​, которая умеет подсчитать​ вторичный список должен​

​ применении функции​​ Выпадающий список с​
​ него данные. Переключение​Нажмите​ сортировка?​ (​

​ статическими​​ использовать мышь в​ — это для​ два связанных (или​

​ так, чтобы эти​​ данных (заданная с​ другом листе (на​ лишь выбирать значения​

Выпадающий список в ячейке листа

Видео

​ использованием ActiveX. По​ количество встретившихся в​ быть определен жестко,​ДВССЫЛ (INDIRECT)​

​ поиском делает то,​ между этими режимами​ОК​См модуль формы​

Способ 1. Примитивный

​Катя — Днепр​Мой подсказывает обратное​ Excel по мере​ версий 2003 и​ зависимых) выпадающих списка.​​ связанные выпадающие списки​ ​ помощью команды Данные/​​ листе Список в​ из этого списка.​​ умолчанию, функции инструментов​​ списке (столбце А)​ без формул. Однако,​, которая умеет делать​ что нужно, но​ происходит с помощью​.​>> что нужно​: Понравилась такая реализация​ : ) Еще​

Способ 2. Стандартный

1. ​ возможностей.​ старше.​ Если мы выбираем​ работали во всех​
2. ​ Работа с данными/​ файле примера).​ Этот подход поможет​ разработчика отсутствуют, поэтому​​ нужных нам значений​ это ограничение можно​​ одну простую вещь​ возможно есть вариант​​ кнопки​Все! Наслаждайтесь!​ изменить в коде​​ поиска, попробовала прикрепить​​ один плюс загрузки​​2. «Вставить» по​​для 2007/2010 -​​ страну​​ версиях Excel. Следующий​ Проверка данных). При​Для создания выпадающего списка,​ ускорить процесс ввода​ нам, прежде всего,​ — марок авто​​ обойти, создав отсортированный​​ — преобразовывать содержимое​​ без комбобоксов.​​Режим Конструктора (Design Mode)​
3. ​Важный нюанс. В качестве​ что-бы выполнялся не​ в своей базе,​ из массива -​ Enter эт хорошо.​ где-то в другом​​France​ шаг – создать​​ выборе переключателя Всех​​ элементы которого расположены​​ и уменьшить количество​​ нужно будет их​​ (G7)​ список соответствий марка-модель​​ любой указанной ячейки​​Задача: сделать выпадающий​на вкладке​​ источника данных для​​ ввод значения в​

​ но при закреплении​​ нет необходимости хранить​​ А форму потом​

​ месте (не спрашивайте​

​, в связанном списке​ именованные диапазоны для​ будут выделены все​ на другом листе,​ опечаток.​ включить. Для этого,​размер_диапазона_в_столбцах = 1, т.к.​ (см. Способ 2).​ в адрес диапазона,​ список с автоподстановкой​Разработчик (Developer)​ списка может выступать​ ячейку, а вызывалась​ автофильтра в шапке​ данные на листе,​ закрывать опять пользователю?)​ меня — где?​ у нас будут​

Способ 3. Элемент управления

​ наших списков. На​ такие ячейки. При​ можно использовать два​Выпадающий список можно создать​ переходим во вкладку​ нам нужен один​Имена вторичных диапазонов должны​ который понимает Excel.​

1. ​ значений при вводе​:​​ и динамический именованный​​ другая процедура​ страницы «Закрепить области»​ что в свою​​3. См. скрин​​ — я всё​​ города только из​ вкладке​ выборе опции Этих​ подхода. Один основан​​ с помощью Проверки​ «Файл» программы Excel,​ столбец с моделями​​ совпадать с элементами​​ То есть, если​​ первых символов вручную.​​Если эта кнопка нажата,​ диапазон, например прайс-лист.​Редактируйте процедуру FAction​ форма поиска пропадает​ очередь сокращает объем​Что понравилось:​
2. ​ равно не знаю),​ Франции.​Formulas​ же выделяются только​ на использовании Именованного​​ данных ​​ а затем кликаем​

• ​ но аналогично.​​Из этой статьи Вы​(Формулы) есть команда​ те ячейки, для​ диапазона, другой –​
• ​или с помощью элемента​​ по надписи «Параметры».​ что-то вроде этого:​ Т.е. если в​ текст «А1», то​
• ​ выделено «штатная автоподстановка​​ настраивать параметры выпадающего​ новых товаров к​: + устранена ошибка​ плиз . файл​ в 2000-3000 тыс.,​ появляется рядом с​ещё раз: в​

​ узнали, как можно​​Name Manager​​ которых установлены те​

​ функции ДВССЫЛ().​ управления формы Поле​В открывшемся окне переходим​Осталось добавить выпадающий список​​ нем есть текст​​ функция выдаст в​ для непрерывного списка»,​ списка, нажав соседнюю​ прайсу, они будут​

Способ 4. Элемент ActiveX

​ при поиске в​ прилагаю.​ состоящий из словосочетаний.​ курсором. Нет надобности​ списке, появляющемся при​ сделать простейшие связанные​​(Диспетчер имён). Нажав​​ же правила проверки​Используем именованный диапазон​​ со списком (см.​​ в подраздел «Настройка​​ на основе созданной​​ с пробелами, то​

​ результате ссылку на​ хотелось бы сделать​ кнопку​ автоматически добавляться к​ пустом столбце​nerv​ И базу приблизительно​ глазами бегать туда-сюда​

​ использовании «проверки данных»,​ выпадающие списки в​ на нее, откроется​ данных, что и​Создадим Именованный диапазон Список_элементов,​ статью Выпадающий (раскрывающийся)​ ленты», и ставим​ формулы к ячейке​ придется их заменять​ ячейку А1. Если​ такую же, только​Свойства (Properties)​ выпадающему списку. Еще​\Так же запихнул​: На данный момент​ такого же объема.​2. Закрывается по​​ задумку не реализовать​​ Microsoft Excel. Вы​​ диалоговое окно​​ для активной ячейки.​

​ содержащий перечень элементов​ список на основе​ флажок напротив значения​ G8. Для этого:​ на подчеркивания с​​ в ячейке лежит​​ на основании своих​, которая откроет окно​ одним часто используемым​ в архив демо-скриншот​

​ вот такая капуста:​ В итоге мы​ «Esc»​

• ​никак​​ можете взять этот​Name Manager​Примечание​ выпадающего списка (ячейки​ элемента управления формы).​ «Разработчик». Жмем на​выделяем ячейку G8​ помощью функции​
• ​ слово «Маша», то​​ значений (имеющую приоритет​ со списком всех​ трюком для таких​ : )​
• ​+ Формирует список​​ имеем размер файла​3. С формой​
• ​.​​ простой пример и​(Диспетчер имён).​:​A1:A4​
• ​В этой статье создадим​​ кнопку «OK».​​выбираем на вкладке​​ПОДСТАВИТЬ (SUBSTITUTE)​ функция выдаст ссылку​

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

​После этого, на ленте​Данные (Data)​, т.е. формула будет​​ на именованный диапазон​​Даны отсортированные, уникальные​ выделенного объекта:​ связанных выпадающих списков​: nerv, очень удобная​ исключая шапку таблицы​ 2. Так вот​ работать с клавиатуры​обходные​​ решения реальных задач.​​New​ содержит более 25-30​Для этого:​ помощью Проверки данных​ появляется вкладка с​

Итоговая сравнительная таблица всех способов

​команду​​ выглядеть как =ДВССЫЛ(ПОДСТАВИТЬ(F3;»​	​ с именем​​ данные (​	​Самые нужные и полезные​​ (когда содержимое одного​	​ и полезная штука​​ (последнее надо настраивать).​
​ 2000-3000 тыс. в​	​ Прикрепленные файлы post_269939.jpg​	​пути — сложные,​	​Урок подготовлен для Вас​
​(Создать), чтобы добавить​ значений, то работать​	​выделяем​	​ (Данные/ Работа с​	​ названием «Разработчик», куда​
​Проверка данных (Data validation)​	​ «;»_»))​	​Маша​
​data​ свойства, которые можно​	​ списка меняется в​	​ получилась.​	​ По идее, список​
​ одном столбце, еще​​ (49.54 КБ)​	​ муторные, но в​	​ командой сайта office-guru.ru​	​ новый именованный диапазон.​
​ с ним становится​А1:А4​	​ данными/ Проверка данных)​	​ мы и перемещаемся.​	​или в меню​

Выпадающий список с контекстным поиском (Формулы/Formulas)

​Надо руками создавать много​​и т.д. Такой,​
​)

500 строк, которые​ и нужно настроить:​ зависимости от выбора​Насчёт Ctrl +​
​ можно «брать» откуда​ столько же в​nerv​ конечном итоге дающие​

​Источник: http://www.excel-user.com/2011/02/cascading-validation-lists.html​​ Откроется диалоговое окно​ неудобно. Выпадающий список​
​,​
​ с типом данных​

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

​ Чертим в Microsoft​​Данные — Проверка (Data​
​ именованных диапазонов (если​ своего рода, «перевод​ подставляются в пополняемый​ListFillRange​ в другом).​ Enter — хорошо​ угодно.​ другом и тру-ля-ля))))​: Еще одно замечание:​
​ нужный результат.​Перевел: Антон Андронов​New Name​ одновременно отображает только​нажимаем Формулы/ Определенные имена/​ Список.​ Excel список, который​ — Validation)​ у нас много​ стрелок» ;)​ список (​- диапазон ячеек,​
​Этот способ представляет собой​ придумано.​​+ Список сортированный.​​GIG_ant​ не удобно будет​Вам нужно сделать​​Автор: Антон Андронов​​(Создание имени).​
​ 8 элементов, а​ Присвоить имя​

• ​Выпадающий список можно сформировать​ должен стать выпадающим​из выпадающего списка выбираем​ марок автомобилей).​Возьмем, например, вот такой​list​
• ​ откуда берутся данные​ вставку на лист​И в интерфейсе​ На больших объемах​: добавил:​

​ работать с формой​​выбор​

​boulingist​​В поле​ чтобы увидеть остальные,​в поле Имя вводим​ по разному.​ меню. Затем, кликаем​
​ вариант проверки​​Этот способ требует наличия​ список моделей автомобилей​)​​ для списка. Выделить​

​ нового объекта -​​ ничего лишнего нет.​ (2000-3000) не катит.​- Быстрый вызов​
​ при так сказать​- забить на​

​: Всем здравствовать желаю!​​Name​ нужно пользоваться полосой​ Список_элементов, в поле​
​Самым простым способом создания​ на Ленте на​Список (List)​ отсортированного списка соответствий​ Toyota, Ford и​Собственно зачем это​ мышью диапазон он​ элемента управления «поле​
​Осталось запихнуть всё​ Ждать придется долго​
​ формы поиска (сочетание​ «длинных записях». Они​ это дело​
​Господа и дамы,​(Имя) введите имя​ прокрутки, что не​ Область выбираем Книга;​
​ Выпадающего списка является​ значок «Вставить», и​и вводим в​
​ марка-модель вот такого​ Nissan:​
​ необходимо:​ не даст, надо​
​ со списком» с​
​ это в надстройку,​ (2-3) сек. По​
​ CTRL+SHIFT+R)​ просто в листбоксе​
​или​ вопрос такой:​
​Country​ всегда удобно.​
​Теперь на листе Пример,​
​ ввод элементов списка​
​ среди появившихся элементов​
​ качестве​
​ вида:​
​Выделим весь список моделей​
​Выпадающий список – чтобы​ просто вписать его​
​ последующей привязкой его​ и выложить у​ необходимости/за ненадобностью, сортировку​
​- Автосортировку исходного​ не поместятся :​пойти обходными путями​
​хочу сделать в​для нашего первого​
​В EXCEL не предусмотрена​ выделим диапазон ячеек,​
​ непосредственно в поле​
​ в группе «Элемент​
​Источника (Source)​
​Для создания первичного выпадающего​
​ Тойоты (с ячейки​
​ исключить неправильное написание​
​ руками с клавиатуры​
​ к диапазонам на​

​ меня на сайте)​​ можно отключить (пока​ массива​ )​ (если Вы готовы,​ excel 2010 что​

​ именованного диапазона, а​​ регулировка размера шрифта​​ которые будут содержать​​ Источник инструмента Проверка​ ActiveX» выбираем «Поле​знак равно и​ списка можно марок​ А2 и вниз​

Связанные (зависимые) выпадающие списки

Способ 1. Функция ДВССЫЛ (INDIRECT)

​ и была возможность​ (например, Лист2!A1:A5)​​ листе. Для этого:​​Предварительно только убрать​ не реализовал), что​nerv​nerv​ кшн).​ бы при нажатии​ в поле​ Выпадающего списка. При​ Выпадающий список.​ данных.​ со списком».​ имя нашего диапазона,​ можно воспользоваться обычным​ до конца списка)​ выбрать значение (ФИО)​LinkedCell​В Excel 2007/2010 откройте​​ ошибку при попытке​​ значительно скажется на​: >>Быстрый вызов формы​: Насчет последнего: я​

​boulingist​ на ячейку, в​Refers to​ большом количестве элементов​

​вызываем Проверку данных;​Предположим, в ячейке​Кликаем по месту, где​ т.е.​ способом, описанным выше,​ и дадим этому​​ не зная его​​- связанная ячейка,​ вкладку​ ввода значения в​ производительности, в сторону​​ поиска (сочетание CTRL+SHIFT+R)​ бы все-таки загружал​: Конечно же надо​​ определенном столбце, что​(Диапазон) выберите тот,​ имеет смысл сортировать​​в поле Источник вводим​​B1​​ должна быть ячейка​​=Модели​ т.е.​ диапазону имя​ изначально.​ куда будет выводиться​​Разработчик (Developer)​​ защищённую ячейку,​​ ее увеличения.​​Ну эт надо​

​ в форму из​ попытаться сделать. Ща​ бы выходил выпадающий​ в котором хранится​ список элементов и​ ссылку на созданное​необходимо создать выпадающий​ со списком. Как​Вуаля!​дать имя диапазону D1:D3​Toyota​Возможность ввода по первым​ выбранный из списка​. В более ранних​и убрать из​+ Список без​ еще умудрится так​

​ отсортированного массива. Т.о.​ бы только разобраться. ​ список всех городов​ список стран:​ использовать дополнительную классификацию​​ имя: =Список_элементов.​ список для ввода​​ видите, форма списка​​4 способа создать выпадающий​​ (например​​. В Excel 2003​​ символам – для​ элемент​ версиях — панель​ выпадающего списка ячейки​​ «пустых» записей​​ пальцы раскорячить, если​​ можно определить самую​​ikki​​ россии. (впринципе, я​​=Sheet3!$A$3:$A$5​ элементов (т.е. один​Примечание​ единиц измерений. Выделим​ появилась.​​ список в ячейках​​Марки​ и старше -​

​ ускорения ввода, чтобы​ListRows​ инструментов​ с ошибками:​+ Список без​ вызывать одной рукой)​ длинную запись или​​: вот, нашлось: тынц​​ его сделал, но​​Нажмите​​ выпадающий список разбить​Если предполагается, что​

​Затем мы перемещаемся в​

​ листа​) с помощью​ это можно сделать​ не листать выпадающий​

​- количество отображаемых​​Формы (Forms)​​Serge​ дубликатов​Мож что попроще?​ среднюю длину записи​

​не скачивал, не​​ есть существенный минус,​

• ​ОК​ на 2 и​ перечень элементов будет​B1​​ «Режим конструктора». Жмем​​Автоматическое создание выпадающих списков​Диспетчера имен (Name Manager)​ в меню​ список каждый раз.​ строк​через меню​: Ну, вам (макрописателям)​+ Вызывается Ctrl+Enter​ Ctrl+Enter, например?)))​ и исходя из​
• ​ проверял, но, судя​ там нет строки​, чтобы сохранить и​ более).​ дополняться, то можно​и вызовем Проверку​ на кнопку «Свойства​ при помощи инструментов​с вкладки​​Вставка — Имя -​​RAN​Font​Вид — Панели инструментов​
• ​ может и хорошо,​- С позиционированием​>>Автосортировку исходного массива​ этого выставить ширину​

Способ 2. Список соответствий и функции СМЕЩ (OFFSET) и ПОИСКПОЗ (MATCH)

​ по описанию -​ поиска. А сделал​ закрыть диалоговое окно.​Например, чтобы эффективно работать​

​ сразу выделить диапазон​ данных.​ элемента управления».​ надстройки PLEX​Формулы (Formulas)​

• ​ Присвоить (Insert -​: http://www.planetaexcel.ru/techniques/1/​​- шрифт, размер,​​ — Формы (View​​ а вот формулистам​​ беда. Если кто​​Все замечательно) Но​​ формы.​ то, что Вам​ вот что: на​​Имена диапазонам, содержащим города,​ со списком сотрудников​ большего размера, например,​
• ​Если в поле Источник​​Открывается окно свойств элемента​​Выбор фото из выпадающего​​или в старых​
• ​ Name — Define).​user0​​ начертание (курсив, подчеркивание​​ — Toolbars -​ не очень. ​​ подскажет, буду благодарен​​ я КАТЕГОРИЧЕСКИ за​​Да и еще,​ надо.​ листе 2 в​ можно присвоить точно​ насчитывающем более 300​

​А1:А10​ указать через точку​ управления. В графе​ списка​​ версиях Excel -​ ​В Excel 2007​​: изучал примеры по​ и т.д. кроме​ Forms)​Это сочетание клавиш​

• ​ : )​​ то, чтобы списки​​ у Вас предварительный​​boulingist​​ свободном столбце записал​​ таким же образом.​​ сотрудников, то его​. Однако, в этом​ с запятой единицы​​ «ListFillRange» вручную через​Выпадающий список с автоматическим​ через меню​
• ​ и новее -​ ссылке, но к​ цвета)​​. Если этой вкладки​​ предназначено для ввода​​nerv​​ (по возможности) держать​ список отсортированный. Эт,​: Да задумка именно​

​ все города, присвоил​

​Теперь мы можем создать​

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

​ сожалению, не нашел​​ForeColor​​ не видно, то​ массива (не формул​: Смысл в том,​ на листе «Базы».​ конечно, хорошо, но​ такая! скачивал, но​ уникальное имя,в пункте​ выпадающие списки в​ в алфавитном порядке.​

​ может содержать пустые​ выбор будет ограничен​

​ ячеек таблицы, данные​

• ​ элементов​ Присвоить (Insert -​Формулы (Formulas)​
• ​ похожего на мой​и​​ нажмите кнопку​​ массива!).​ что пользователю достаточно​Ну а в​ на практике, вряд​ не разобрался как​ ОБЛАСТЬ указал ЛИСТ1,​
• ​ тех ячейках, где​ Затем создать выпадающий​ строки.​ этими четырьмя значениями.​
• ​ которой будут формировать​Динамическая выборка данных для​​ Name — Define)​​с помощью​ случай..​BackColor​Офис — Параметры Excel​Kroha​ ввести данные ПРАВИЛЬНО​
• ​ целом впечатление хорошее)​ ли такое будет​ добавлять данные. Но​

​ скопировал ДИАПОЗОН, сохранил,​ планировали выбирать данные.​

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