Выпадающий список в excel с поиском: С выпадающий список с поиском в excel

Как найти выпадающие списки в Excel читать подробную статью

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

Уровень мастерства: Начинающий

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

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

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

Поэтому я разработал очень простую надстройку, которая помогает решить эту проблему …

Скачать бесплатную надстройку 

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

• Ноябрь 2016 Обновление
• Апрель 2017 Обновление

Содержание

1. Поиск списков проверки с помощью поиска по списку
2. Параметры и функции поиска в списке
3. Работает со списками без проверки данных
4. Ноябрь 2016 Обновление
5. Апрель 2017 Обновление
6. Загрузите надстройку поиска по списку (это бесплатно!)
7. Как мои коллеги могут использовать поиск по списку?
8. Как мы можем улучшить поиск по списку?

Поиск списков проверки с помощью поиска по списку

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

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

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

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

Параметры и функции поиска в списке

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

• Select Next Cell— после нажатия клавиши «Ввод» или «Ввод значения» выбирается ячейка под активной ячейкой. Это поведение можно изменить в раскрывающемся меню направления.
  • Down — выбирает ячейку под активной ячейкой.
  • Right — выбор ячейки справа от активной ячейки.
  • None — не меняет выбор.
  • Close — закрывает окно поиска по списку.
  • Paste — копирует входное значение в буфер обмена и вставляет его в активную ячейку с помощью метода VBA SendKeys. Окно поиска по списку закрывается. Это единственная опция, которая сохраняет историю отмен в Excel.
• Sort Order — выпадающий список можно отсортировать по возрастанию (A-Z), по убыванию (Z-A) или оригинальному порядку, нажимая кнопки переключения в меню параметров. Это только сортирует список в окне поиска списка. Он не сортирует список проверки данных в ячейке.
• List Info — кнопка Info отображает дополнительную информацию о раскрывающемся списке. В настоящее время отображается общее количество элементов в списке.
• Create List of Unique Values — добавлена ​​новая кнопка, которая копирует содержимое раскрывающегося списка в буфер обмена. Затем вы можете вставить список в любой диапазон в рабочей книге. Это быстрый способ создания списка уникальных значений при использовании поиска по списку в ячейке, которая НЕ содержит проверки данных. Вы также можете отфильтровать список, введя поиск, а затем скопировать отфильтрованный список в буфер обмена.

ВАЖНО. Примечание.

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

Параметр Вставить — это обходной путь, который использует метод SendKeys для копирования и вставки выбранного значения. Это имитирует то, что пользователь будет делать для копирования / вставки, и НЕ очищает историю отмен в Excel.

Работает со списками без проверки данных

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

Это похоже на нажатие Alt + Стрелка вниз в ячейке, чтобы увидеть список значений в этом столбце. Тем не менее, список не должен быть непрерывным. Даже если столбец содержит пробелы, Поиск по списку все равно загрузит все уникальные значения в текущей области данных или списке.

Ноябрь 2016 Обновление

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

Вот список возможностей:

1. Добавлена ​​опция «Вставить» в список направлений. Это скопирует входное значение в буфер обмена и вставит его в активную ячейку. Параметры Paste используют метод SendKeys в VBA для выполнения вставки. Это означает, что история отмен не будет очищена при использовании опции вставки.
2. Настройки для меню параметров и раскрывающегося списка направления ввода теперь сохраняются в реестре. Ваши настройки будут сохранены и загружены при следующем открытии Excel и надстройки.
3. Добавлены улучшения для таблиц Excel. Когда активная ячейка находится в таблице, а ячейка не содержит проверки, будет загружен уникальный список значений, исключая заголовки таблицы и итоговую строку.
4. Добавлена ​​функция копирования списка, которая копирует содержимое выпадающего списка в буфер обмена. Эта функция используется для создания списка уникальных значений из столбца / таблицы, когда активная ячейка не содержит проверки. Это также работает, когда список фильтруется поисковым запросом, чтобы копировать только отфильтрованные результаты.

Апрель 2017 Обновление

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

Вот список новых функций в обновлении апреля 2017 года:

1. Он добавил функцию автоматического открытия, чтобы автоматически открывать форму, когда выбрана ячейка, содержащая проверку данных. Вы можете включить или отключить эту опцию с помощью кнопки-переключателя в меню параметров.
2. Теперь надстройка работает с проверкой данных, созданной с помощью формул (OFFSET & INDEX) и разделенных запятыми списков. Он должен работать со всеми типами списков проверки данных.
3. Обновлено поведение клавиши Escape, чтобы закрыть окно поиска по списку. Если в окне поиска есть текст, Escape очищает окно поиска. Если поле поиска пустое, Escape закрывает форму.

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

Загрузите надстройку поиска по списку (это бесплатно!)

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

Скачать надстройку

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

Сайт загрузки также содержит инструкции по установке и видео.

Как мои коллеги могут использовать поиск по списку?

Надстройка поиска по списку установлена ​​на вашем компьютере, и только вы сможете увидеть вкладку XL Campus и использовать поиск по списку. Если вы хотите, чтобы ваши коллеги могли использовать Поиск по списку, есть два способа сделать это.

1. Отправьте им ссылку на эту страницу, чтобы загрузить и установить Поиск по списку на своем компьютере. Они смогут использовать Поиск по списку в любом файле Excel, который они открыли на своем компьютере.
2. Импортируйте пользовательскую форму поиска по списку в проект VB в файле Excel. Вы можете добавить форму поиска по списку в любую из ваших книг. Это должна быть книга с макросами. Вам также потребуется создать или импортировать модуль кода, который содержит макрос, чтобы открыть пользовательскую форму поиска по списку. Затем добавьте кнопку на лист или ленту, которая открывает форму.

Как мы можем улучшить поиск по списку?

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

Пожалуйста, оставьте комментарий ниже с любыми вопросами или предложениями. Спасибо!

Как сделать выпадающий список в Эксель: 3 простых способа 

Наверх

06.09.2021

Автор: Алексей Иванов

1 звезда2 звезды3 звезды4 звезды5 звезд

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

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

1 Применение комбинации клавиш 

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

1. Выбрать ячейку под списком, в котором содержатся его значения. 
2. Нажать сочетание клавиш «Alt» + «стрелка вниз». 
3. Выбрать нужное значение.

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

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

• Эксплуатация

  Как файл из Эксель сохранить в ПДФ: 4 основных способа 

2 Использование меню 

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

1. Выписать в столбик данные, которые будут использоваться в списке. Это может быть, например, количество ядер и потоков для процессора. 
2. Выбрать ячейку, в которой будет находиться нужный объект. 
3. Перейти на вкладку «Данные». 
4. Найти и выбрать функцию «Проверка данных». 
5. Выбрать в поле типа данных пункт «Список». 
6. Указать в источнике адреса уже введенных значений. Или ввести их самостоятельно, через запятую. В этом случае экономится место в таблице, но повышается риск сделать ошибку еще на этапе создания списка. И изменять значения будет сложнее. 

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

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

Автоматическое заполнение, скорее всего, будет некорректным. Но при выборе значений вручную они будут выбираться из нужного диапазона — это становится возможным благодаря знакам доллара перед каждой частью адреса источника ($I$6: $I$8).

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

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

• Эксплуатация

  Как построить график в Эксель: пошаговая инструкция

3 Создание нового элемента управления 

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

1. Включить режим разработчика. Для это следует кликнуть правой кнопкой мыши по ленте Excel, открыть настройки и поставить галочку напротив соответствующего пункта. 
2. Перейти к появившейся новой вкладке. В меню «Вставить» выбрать поле со списком. 
3. Кликнуть по любому месту таблицы. После того, как там появилось нужное поле, открыть его контекстное меню и выбрать «Формат объекта». Здесь следует указать связь с ячейкой, в которой будет находиться номер значения в списке (48/96 — второй, поэтому в ячейке G2 находится «2»). А еще — диапазон, откуда берутся данные. 

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

Способ является сравнительно сложным, и его не стоит рассматривать в качестве основного. Зато после создания готового выпадающего списка в Экселе его можно будет расположить в любом месте таблицы. Для перетаскивания придется включить режим «Конструктора» на вкладке «Разработчик» и использовать правую кнопку мыши. 

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

• Как выбрать монитор для компьютера и наслаждаться «картинкой»
• Настраиваем VPN сразу на роутере: как это сделать?

Автор

Алексей Иванов

Была ли статья интересна?

Поделиться ссылкой

Нажимая на кнопку «Подписаться»,
Вы даете согласие на обработку персональных данных

Рекомендуем

Реклама на CHIP Контакты

Раскрывающийся список с возможностью поиска в Excel (очень просто с динамическими массивами) — Xelplus

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

«Есть ли в Excel функция, позволяющая сделать список проверки данных доступным для поиска?»

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

Что мы подразумеваем под доступным для поиска списком проверки данных? Давайте посмотрим на пример ниже.

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

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

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

Демонстрация решения

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

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

Методы решения проблемы

Быстрый поиск в Интернете «раскрывающихся списков Excel с возможностью поиска» даст ссылки на веб-сайты, демонстрирующие решения с использованием VBA. У меня есть аналогичное решение с использованием VBA, которое рассматривается в моем курсе VBA с использованием пользовательских форм.

«Я не хочу использовать VBA для решения этой дилеммы», — говорите вы?

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

Магия динамических массивов

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

Больше нет!

Мы решим эту проблему с помощью динамических массивов.

На момент написания этого сообщения (январь 2019 г.) динамические массивы были доступны только подписчикам Office 365 на канале «Office Insider», но в ближайшем будущем они будут доступны всем пользователям Office 365.

Создание решения

Мы хотим иметь раскрывающийся список с возможностью поиска на листе с именем « Отчет » в ячейке B5 в образце файла.

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

У нас есть еще один лист с именем « MasterData », который содержит список всех клиентов и связанных с ними компаний.

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

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

Если ввести буквы « GAR » в ячейке B5 , нам нужен сгенерированный список, содержащий все без исключения элементы из столбца «Клиент», содержащие буквы « GAR ».

Незначительная проблема

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

Из-за этого ограничения нам необходимо создать таблицу подготовки данных, которая будет отбирать информацию из столбца «Клиент» и помещать ее в столбец 9.0071 D листа « MasterData» .

Элемент #1 — поиск текста с помощью функции ПОИСК . Это избавит нас от необходимости переключаться между листами во время разработки решения.

При поиске всех предметов, содержащих буквы « GAR », введите буквы « GAR » в ячейку D1 .

В ячейке D2 мы создадим функцию для выполнения поиска.

Функция ПОИСК имеет три параметра:

• « Find_Text » — это текст или ячейка, содержащая текст, который нужно найти
• « Within_Text » — это ячейка или массив ячеек для поиска
• «[Start_Num] » — это необязательный параметр, который позволяет вам определить позицию символа в пределах « Within_Text ” параметр, который вы хотите начать поиск. Если этот параметр не объявлен, поиск начинается с позиции символа 1 (крайняя слева).

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

=ПОИСК(D1,A2)

Нам представлен результат « 1 ».

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

Если мы изменим критерий поиска на букву « M », нам будет возвращено число « 6 », поскольку « M » встречается в шестой позиции символа имени «Гэри Миллер».

Если мы изменим критерии поиска на что-то, чего нет в тексте поиска, например, на букву « V », мы получим #ЗНАЧ! Ошибка .

Давайте обновим нашу формулу, включив в нее полный диапазон имен клиентов.

=ПОИСК(D1,A2:A32)

Результат привлекает внимание!

В этом сила динамических массивов.

Динамические массивы позволяют нам писать одну формулу, но возвращать множество результатов. В этом случае мы хотим найти текст в ячейке D1 в каждом элементе списка клиентов. Ответы появляются в ячейках D2:D32 .

Это известно как «рассыпанный массив».

Если мы изменим критерии поиска на « ROB », мы видим, что буквы появляются в четырех именах клиентов, что дает числа, и #ЗНАЧ! ошибки для всех остальных элементов списка.

Элемент № 2 — преобразование чисел/ошибок в истинные/ложные ответы с помощью функции ISNUMBER а там, где есть ошибки, мы хотим вывести слово « False ».

Обновление 9Функция 0071 ПОИСК в ячейке D2 выглядит следующим образом:

=ЧИСЛО(ПОИСК(D1,A2:A32))

Теперь у нас есть список ответов True/False.

Элемент № 3 — фильтрация списка для отображения только верных ответов с использованием функции ФИЛЬТР ” — Список элементов для фильтрации.

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

« [If_Empty] » — необязательный параметр того, что отображать, если из фильтра не возвращаются элементы, такие как пустой текст или сообщение.

Нам нужно обновить нашу формулу в ячейке D2 , чтобы отфильтровать столбец «Клиент» на основе проверки, ранее выполненной с помощью формулы ISNUMBER/ПОИСК . Поскольку формула возвращает ответ True/False, любой элемент с ответом True будет включен в результирующий список, а любой элемент с ответом False будет отфильтрован из списка.

Обновите формулу следующим образом:

=ФИЛЬТР(A2:A32,ISNUMBER(ПОИСК(D1,A2:A32)»,»Не найдено»)

Посмотрите результат обновленной формулы.

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

Если мы ищем что-то, чего нет в списке Клиентов, например « Лейла », формула возвращает сообщение « Не найдено ».

Переход от разработки к производству

Ячейка D1 на листе « MasterData » была нашим тестовым сайтом для поиска текста. Наше официальное место поиска находится на листе « Report ».

Давайте удалим то, что у нас есть в ячейке D2 на листе « MasterData », и поместим на его место заголовок, например « Data Validation Prep. ”

Теперь нам нужно обновить ссылку в ПОИСК функция для поиска данных, введенных в ячейку B5 листа « Отчет ». Обновите формулу следующим образом.

= ФИЛЬТР (A2: A32, INUMBER (ПОИСК (Отчет! B5, A2: A32))», «Не найдено»)

Если мы поместим текст « GAR » в ячейку B5 « Report », таблица подготовки данных в столбце D листа « MasterData » обновляется соответствующим образом.

Расширенная функциональность динамического выпадающего списка

Если список клиентов в столбце A листа « MasterData » содержит повторяющиеся элементы, другая функция, которую можно включить в нашу логику, — это функция UNIQUE . Соблюдайте следующую формулу.

=УНИКАЛЬНЫЙ(ФИЛЬТР(A2:A32,IНОМЕР(ПОИСК(Отчет!B5,A2:A32))»,»Не найдено»))

Будет создан список элементов без дубликатов.

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

=СОРТИРОВАТЬ(ФИЛЬТР(A2:A32,INUMBER(ПОИСК(Отчет!B5,A2:A32))»,Не найдено»))

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

из таблицы подготовки данных

Чтобы создать раскрывающийся список проверки данных, выберите Данные (вкладка) -> Работа с данными (группа) -> Проверка данных .

На вкладке Настройки в диалоговом окне Проверка данных выберите « Список » в раскрывающемся списке Разрешить .

В поле Источник введите первую ячейку в таблице подготовки данных на листе « MasterData ». Поскольку мы хотим захватить весь разбросанный массив, который начинается в ячейке D2 , добавьте « # ” символ фунта (или хэштег) в конец ссылки.

=MasterData!$D$2#

Настройка параметров проверки данных

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

Если мы введем что-то вроде « ROB » в ячейку B5 , мы получим следующее сообщение об ошибке.

Чтобы разрешить ввод ранее не определенных данных и подавить это сообщение об ошибке, выберите Данные (вкладка) -> Работа с данными (группа) -> Проверка данных .

На вкладке « Оповещение об ошибке » снимите флажок « Показывать оповещение об ошибке после ввода неверных данных ».

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

Возврат информации о компании

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

Мы можем добиться этого с помощью проверенных и надежных функций Excel, таких как ВПР или ИНДЕКС/ПОИСКПОЗ , но поскольку мы используем новую функцию динамического массива Excel, давайте еще раз воспользуемся функцией ФИЛЬТР .

На листе « Report » щелкните ячейку C5 и введите следующую формулу.

=ФИЛЬТР(MasterData!B2:B32,MasterData!A2:A32-Report!B5””)

На английском это выглядит следующим образом:

Возврат элемента из столбца Company (столбец B ), где имя в столбце «Клиент» (столбец A ) соответствует имени, указанному в ячейке B5 листа « Отчет ». Если имя не найдено, вернуть пустой текст (две двойные кавычки).

Добавление новых клиентов в лист основных данных

Чтобы использовать автоматическое включение новой информации в нашу таблицу «Клиенты», нам нужно преобразовать нашу традиционную простую таблицу в официальную таблицу данных Excel.

Выберите любую ячейку в таблице Customer/Company и нажмите CTRL-T (или Главная (вкладка) -> Стили (группа) -> Форматировать как таблицу -> выберите стиль ) и нажмите OK .

Мы переименуем нашу таблицу, выбрав Работа с таблицами — Дизайн (вкладка) -> Свойства (группа) -> и введите имя « TableCustomers » в поле «Имя таблицы». Этот шаг необязателен, но рекомендуется.

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

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

Практическая рабочая тетрадь

Скачать рабочую тетрадь можно ЗДЕСЬ.

ПОЛНЫЙ курс по функциям Excel в 365 и Office 2021

Освойте НОВЫЕ формулы Excel

ФИЛЬТР, СОРТИРОВКА, УНИКАЛЬНОСТЬ: от уровня новичка до уровня эксперта!

Начать обучение

Related Posts

ИНДЕКС И ПОИСКПОЗ для гибкого поиска
Раскрывающийся список с возможностью поиска
Функция Excel OFFSET для динамических вычислений
Зависимое поле со списком Excel
Excel Unstack Data Trick

Поиск:

Полный курс

Основные функции Excel в Office 365 / Office 2021

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

Мы все используем Google как часть нашей повседневной жизни. Одной из его функций является подсказка при поиске, когда Google действует умно и дает нам список предложений, пока мы печатаем.

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

Ниже представлено видео этого урока (на случай, если вы предпочитаете смотреть видео, а не читать текст).

Для целей этого урока я использую данные 20 ведущих стран по ВВП.

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

Примерно так, как показано ниже:

Чтобы продолжить, загрузите файл примера отсюда

поисковая строка.

Установка данных.

Написание короткого кода VBA, чтобы заставить его работать.

Шаг 1. Настройка поля поиска

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

Вот шаги для этого:

1. Перейдите на вкладку «Разработчик» -> «Вставка» -> «Элементы управления ActiveX» -> «Поле со списком» (элемент управления ActiveX).
   • Возможно, вы не найдете вкладку разработчика на ленте. По умолчанию он скрыт и должен быть включен. Нажмите здесь, чтобы узнать, как получить вкладку разработчика на ленте в Excel.
2. Переместите курсор в область рабочего листа и щелкните в любом месте. Он вставит поле со списком.
3. Щелкните правой кнопкой мыши поле со списком и выберите «Свойства».
4. В диалоговом окне свойств внесите следующие изменения:
   • AutoWordSelect: False
   • LinkedCell: B3
   • ListFillRange: DropDownList (мы создадим именованный диапазон с этим именем) MatchEntry: 2 – fmMatchEntryNone

(ячейка B3 связана с полем со списком, что означает, что все, что вы вводите в поле со списком, вводится в поле B3)

4. Перейдите на вкладку «Разработчик» и нажмите «Режим разработки». Это позволит вам ввести текст в поле со списком. Кроме того, поскольку ячейка B3 связана с полем со списком, любой текст, который вы вводите в поле со списком, также будет отражаться в ячейке B3 в режиме реального времени.

Шаг 2. Установка данных

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

Для этого мы будем использовать

• Три вспомогательных столбца.
• Один динамический именованный диапазон.

Вспомогательный столбец 1

Поместите следующую формулу в ячейку F3 и перетащите ее на весь столбец (F3:F22)

 =--ЧИСЛО(ЕСЛИОШИБКА(ПОИСК($B$3,E3,1)," ")) 

Эта формула возвращает 1, когда текст в поле со списком присутствует в названии страны слева. Например, если вы введете UNI, тогда будут доступны только значения для Uni ted States и Uni 9.0072 ted Королевство равно 1, а все остальные значения равны 0.

Вспомогательный столбец 2

Поместите следующую формулу в ячейку G3 и перетащите ее на весь столбец (G3:G22)

 =IF(F3=1 ,COUNTIF($F$3:F3,1),"") 

Эта формула возвращает 1 для первого вхождения, где текст поля со списком соответствует названию страны, 2 для второго вхождения, 3 для третьего и так далее. Например, если вы наберете UNI, в ячейке G3 будет отображаться 1, поскольку это соответствует США, а G9будет отображаться 2, поскольку это соответствует Соединенному Королевству. Остальные ячейки будут пустыми.

Вспомогательный столбец 3

Поместите следующую формулу в ячейку h4 и перетащите ее на весь столбец (h4:h32)

 =ЕСЛИОШИБКА(ИНДЕКС($E$3:$E$22,ПОИСКПОЗ(СТРОКИ($G $3:G3),$G$3:$G$22,0)),"") 

Эта формула объединяет все совпадающие имена вместе без пустых ячеек между ними. Например, если вы наберете UNI, в этом столбце будут показаны 2 и 9 вместе, а остальные ячейки будут пустыми.

Создание динамического именованного диапазона

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

Примечание : На шаге 1 мы ввели DropDownList в опции ListFillRange. Теперь мы создадим именованный диапазон с тем же именем .

Вот шаги для его создания:

1. Перейдите в Формулы -> Диспетчер имен.
2. В диалоговом окне диспетчера имен нажмите «Создать». Откроется диалоговое окно «Новое имя».
3. В поле Имя введите DropDownList
4. В поле Относится к введите формулу:  =$H$3:INDEX($H$3:$H$22,MAX($G$3:$G$22),1)

Шаг 3 – Запуск кода VBA

Мы почти закончили.

Заключительная часть — написание короткого кода VBA. Этот код делает раскрывающийся список динамическим, чтобы он отображал совпадающие элементы/имена по мере ввода в поле поиска.

Чтобы добавить этот код в книгу:

1. Щелкните правой кнопкой мыши вкладку «Рабочий лист» и выберите «Просмотреть код».
2. В окне VBA скопируйте и вставьте следующий код:

     Private Sub ComboBox1_Change()   ComboBox1.