Excel список данных с поиском

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

Классический выпадающий список в ячейке листа 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+стрелка вниз, чтобы развернуть выпадающий список без мыши.

P.S. 

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

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

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

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

Ссылки по теме

• Что такое динамические массивы в Excel
• Разбор трех основных функций динамических массивов: СОРТ, ФИЛЬТР и УНИК
• 4 способа создать выпадающий список на листе Excel

Microsoft 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 не должны​ Попробую пока предложенный​ представления (цветов, шрифтов​Поле со списком​У кого мало времени​ не видна( Вообщем,​Спасибо за отзыв,​

​ как +/-​

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

​ и нужно быстро​

lumpics.ru

Выпадающий список в 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​

excel2.ru

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

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

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

​Стандартный​- сколько строк​ или вам нужен​: Всем доброго времени​ звёздочку после Criteria1:=»=​: >>3. Что бы​ часто приходится заполнять​ списке — разбивать​ Мы поместим этот​B1​ диапазон ячеек, находящегося​Преимущество​ и мер измерения.​ располагались визуально вместе.​​ Name — Define)​​Данные (Data)​​If IsEmpty(Target) Then​​Способ 3.​​ показывать в выпадающем​​ товар, который еще​ суток!​​Union([Mylist].Parent.[A1], [Mylist]).AutoFilter Field:=1,​​ форма не вылазила​​ таблицы, выбирая данные​​ его на двух-трехуровневую​ раскрывающийся список в​​мы будем выбирать​​ в другой книге​: наглядность перечня элементов​Присваиваем каждому из списков​

​Выделяем данные, которые планируем​Создайте новый именованный диапазон​если у вас​ 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)​

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

office-guru.ru

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

​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 & «​ИНДЕКС​ не элемент управления,​

CyberForum.ru

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

planetaexcel.ru

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

Видео

​ использованием 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​ же выделяются только​ на использовании Именованного​​ данных ​​ а затем кликаем​
   
   ​В итоге должно получиться​ первичного выпадающего списка.​ в ячейке лежит​ На скриншоте черным​
3. ​ то мы можем​ Тогда при дописывании​nerv​​ из виду, помогите​​ файла. Представьте список​1. То, что​

​ узнали, как можно​​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​Для этого:​ помощью Проверки данных​ появляется вкладка с​

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

planetaexcel.ru

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

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

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

excelworld.ru

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

​ Чертим в 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 и вниз​

excelworld.ru

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

Способ 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​ ввести данные ПРАВИЛЬНО​
• ​ целом впечатление хорошее)​ ли такое будет​ добавлять данные… Но​

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

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

• ​ выпадающего списка функциями​
• ​выбрать на вкладке​​Диспетчера имен (Name Manager)​​меня интересует​​- цвет текста​​-​​: Уважаемый EducatedFool!​ всего ОДИН раз​
• ​ Очень даже) Мож​ : )​​ спасибо.. Тема какраз​​ перешел на лист1,​ Выделите ячейку​​ алфавита. Второй выпадающий​​ и учесть новые​ Выделим ячейку​Далее, кликаем по ячейке,​​ ИНДЕКС и ПОИСКПОЗ​

​Данные (Data)​

Ссылки по теме

• ​. Затем повторим то​Возможность ввода с автоподстановкой​ и фона, соответственно​
• ​флажок​Скажите, когда эту​ и в последующем​
• ​ чуть позже выложу​В идеале (я​
• ​ та что мне​ данные, проверка данных,​B1​
• ​ список должен содержать​ элементы перечня позволяет​B1​

planetaexcel.ru

​ и в контекстном​

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

Поисковая функция в Excel

Поисковая функция в программе Microsoft Excel предлагает возможность найти нужные текстовые или числовые значения через окно «Найти и заменить». Кроме того, в приложении имеется возможность расширенного поиска данных.

Способ 1: простой поиск

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

1. Находясь во вкладке «Главная», кликаем по кнопке «Найти и выделить», которая расположена на ленте в блоке инструментов «Редактирование». В появившемся меню выбираем пункт «Найти…». Вместо этих действий можно просто набрать на клавиатуре сочетание клавиш Ctrl+F.



2. После того, как вы перешли по соответствующим пунктам на ленте, или нажали комбинацию «горячих клавиш», откроется окно «Найти и заменить» во вкладке «Найти». Она нам и нужна. В поле «Найти» вводим слово, символы, или выражения, по которым собираемся производить поиск. Жмем на кнопку «Найти далее», или на кнопку «Найти всё».



3. При нажатии на кнопку «Найти далее» мы перемещаемся к первой же ячейке, где содержатся введенные группы символов. Сама ячейка становится активной.

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

   Поисковые символы не обязательно должны быть самостоятельными элементами. Так, если в качестве запроса будет задано выражение «прав», то в выдаче будут представлены все ячейки, которые содержат данный последовательный набор символов даже внутри слова. Например, релевантным запросу в этом случае будет считаться слово «Направо». Если вы зададите в поисковике цифру «1», то в ответ попадут ячейки, которые содержат, например, число «516».

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

   

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

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

Способ 2: поиск по указанному интервалу ячеек

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

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



2. Набираем на клавиатуре комбинацию клавиш Ctrl+F, после чего запуститься знакомое нам уже окно «Найти и заменить». Дальнейшие действия точно такие же, что и при предыдущем способе. Единственное отличие будет состоять в том, что поиск выполняется только в указанном интервале ячеек.

Способ 3: Расширенный поиск

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

К тому же, в выдачу может попасть не только содержимое конкретной ячейки, но и адрес элемента, на который она ссылается. Например, в ячейке E2 содержится формула, которая представляет собой сумму ячеек A4 и C3. Эта сумма равна 10, и именно это число отображается в ячейке E2. Но, если мы зададим в поиске цифру «4», то среди результатов выдачи будет все та же ячейка E2. Как такое могло получиться? Просто в ячейке E2 в качестве формулы содержится адрес на ячейку A4, который как раз включает в себя искомую цифру 4.

Но, как отсечь такие, и другие заведомо неприемлемые результаты выдачи поиска? Именно для этих целей существует расширенный поиск Excel.

1. После открытия окна «Найти и заменить» любым вышеописанным способом, жмем на кнопку «Параметры».



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

   По умолчанию, функции «Учитывать регистр» и «Ячейки целиком» отключены, но, если мы поставим галочки около соответствующих пунктов, то в таком случае, при формировании результата будет учитываться введенный регистр, и точное совпадение. Если вы введете слово с маленькой буквы, то в поисковую выдачу, ячейки содержащие написание этого слова с большой буквы, как это было бы по умолчанию, уже не попадут. Кроме того, если включена функция «Ячейки целиком», то в выдачу будут добавляться только элементы, содержащие точное наименование. Например, если вы зададите поисковый запрос «Николаев», то ячейки, содержащие текст «Николаев А. Д.», в выдачу уже добавлены не будут.

   

   По умолчанию, поиск производится только на активном листе Excel. Но, если параметр «Искать» вы переведете в позицию «В книге», то поиск будет производиться по всем листам открытого файла.

   

   В параметре «Просматривать» можно изменить направление поиска. По умолчанию, как уже говорилось выше, поиск ведется по порядку построчно. Переставив переключатель в позицию «По столбцам», можно задать порядок формирования результатов выдачи, начиная с первого столбца.

   

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

   

   Ещё более точно поиск можно задать, нажав на кнопку «Формат».

   

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

   

   Если вы хотите использовать формат какой-то конкретной ячейки, то в нижней части окна нажмите на кнопку «Использовать формат этой ячейки…».

   

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

   

   После того, как формат поиска настроен, жмем на кнопку «OK».

   

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



3. Как только настройки поиска установлены, следует нажать на кнопку «Найти всё» или «Найти далее», чтобы перейти к поисковой выдаче.

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