Раскрывающийся список с возможностью поиска в Excel

Обновлено: 21.11.2024

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

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

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

<Р>

Как искать в списках проверки данных

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

Вы можете выбрать результат с помощью мыши или клавиш со стрелками и нажать Enter.

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

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

Несколько ошибок

Какой бы замечательной ни была эта новая функция, я хотел бы улучшить несколько моментов.

Введите ключевое поведение

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

Технически это НЕ ошибка.То же самое происходит, если вы используете Alt+Стрелка вниз, чтобы открыть список и найти элемент с помощью клавиш со стрелками.

Однако я думаю, что ввод данных был бы быстрее, если бы клавиша "Ввод" работала так же, как при вводе данных или формулы в ячейку.

Частичное совпадение слов

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

Например, мой список содержит слово Arrowroot. Если я ищу «root», результаты не возвращаются.

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

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

Доступность

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

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

Он будет развернут на других каналах Microsoft 365 в ближайшие недели/месяцы.

Функция поиска также доступна в веб-версии Excel. Он был выпущен для этой версии в 2021 году, и я рассказывал об этом в своем предыдущем посте и видео о 21 новой функции Excel, выпущенной в 2021 году.

Что делать, если я застрял на старой версии Excel?

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

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

Обзор видео о поиске по списку

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

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

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

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

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

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

Список параметров и функций поиска

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

  • Выбрать следующую ячейку. После нажатия клавиши «Ввод» или кнопки «Ввод значения» выбирается ячейка под активной ячейкой. Это поведение можно изменить в раскрывающемся меню направления.
    • Вниз — выбирает ячейку под активной ячейкой.
    • Справа – выбирает ячейку справа от активной ячейки.
    • Нет — выбор не меняется.
    • Закрыть — закрывает окно поиска по списку.
    • Вставить — копирует входное значение в буфер обмена и вставляет его в активную ячейку с помощью метода VBA SendKeys. Окно поиска по списку закроется. Это единственный вариант, который сохраняет историю отмен в Excel.

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

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

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

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

    Обновление за ноябрь 2016 г.

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

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

    Обновление за апрель 2017 г.

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

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

    1. Добавлена ​​функция автоматического открытия для автоматического открытия формы при выборе ячейки, содержащей проверку данных. Вы можете включить или выключить этот параметр с помощью переключателя в меню параметров.
    2. Теперь надстройка работает с проверкой данных, созданной с помощью формул (СМЕЩЕНИЕ И ИНДЕКС) и списков, разделенных запятыми. Он должен работать со всеми типами списков проверки данных.
    3. Обновлено поведение клавиши Escape для закрытия окна поиска по списку. Если в поле поиска есть текст, Escape очищает поле поиска. Если поле поиска пусто, Escape закрывает форму.

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

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

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

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

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

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

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

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

    Доступно в Hero Tools

    Надстройка поиска по списку также доступна в нашей надстройке Hero Tools.

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

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

    Я надеюсь, что надстройка поиска по списку сэкономит вам время на поиск списков проверки данных. Конечная цель — ускорить поиск нужного значения в длинных списках данных. Пожалуйста, оставьте комментарий ниже с любыми вопросами или предложениями. Спасибо! 🙂

    Подробнее о раскрывающихся списках

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

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

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

    Используя проверку данных, вы можете создавать полезные раскрывающиеся списки в Excel. Если ваш список содержит много значений, возможно, стоит сделать его доступным для поиска. Видео ниже объясняет, как это можно сделать — VBA не требуется! Загрузите рекомендуемый файл здесь.

    Формулы, использованные в видео

    Рабочий столбец
    =IF(ISNUMBER(SEARCH(‘Drop Down List’!$A$2,[@Product])),MAX($B$1:B1)+1,0)

    Значения раскрывающегося списка
    =IFNA(INDEX(ProductList[Product],MATCH(ROW(D1),ProductList[Workings],0))","")

    Формула именованной ссылки
    =OFFSET('Список продуктов'!$D$2. MAX(Список продуктов[Рабочие работы]))


    ОБУЧЕНИЕ НА МЕСТЕ ИЛИ ОНЛАЙН

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

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

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

    Отличное онлайн-обучение промежуточного уровня в Excel. Инструменты и упражнения, разработанные и используемые Честером, превосходны. Его стиль преподавания очень хорошо реагировал на мои потребности (применения, которые я хотел, мой стиль обучения). Я настоятельно рекомендую его - независимо от вашего уровня знаний в Excel и к какому уровню вы стремитесь!

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

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

    В прошлом году у меня был курс Excel среднего/специального уровня с Честером, и он мне так понравился, что я так высоко оценил этот курс, что впоследствии организовал для своих коллег курсы Excel для начинающих, основы, промежуточный курс и курсы Microsoft PP/word. Даже в виртуальной среде Честер четок в своих выступлениях и указаниях, исключительно хорошо осведомлен и легко адаптируется к постоянно меняющимся спискам участников или возможностям и требованиям внутри команды. Будет здорово применить наши новые навыки с пользой. Мой совет людям, будет делать заметки! Спасибо Честер! Кэтрин П.

    Сотрудник недавно прошел однодневное онлайн-обучение с Честером. Ему это очень понравилось, и он получил большой опыт обучения. Полностью рекомендую Blue Pecan Computer Training и обязательно буду использовать их снова для будущих тренировок.

    Честер Тагвелл
    Владелец и преподаватель курса

    Blue Pecan Computer Training Limited
    Зарегистрированный офис: Piper House 4 Dukes Court, Bognor Rd, Chichester, West Sussex. PO19 8FX

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

    Это отличный прием Excel для работы с большими раскрывающимися списками.

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

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

    Посмотрите видео — выпадающий список с возможностью поиска в Excel

    Современный Excel — более простой метод

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

    У вас нет версии Excel 365? Пожалуйста, прочитайте или посмотрите предыдущее видео.

    Создайте поле со списком

    1. Перейдите на вкладку "Разработчик" на ленте (у вас нет вкладки "Разработчик"? Щелкните ленту правой кнопкой мыши, выберите "Настроить ленту" и отметьте "Разработчик" справа).
    2. Нажмите кнопку «Вставить» в группе «Элементы управления» и выберите поле со списком (элемент управления ActiveX).
      1. Нажмите и перетащите, чтобы нарисовать этот элемент управления на листе. Вы всегда можете переместить элемент управления и изменить его размер позже, поэтому не беспокойтесь о 100% точности.
      2. Теперь нам нужно связать значение поля со списком с ячейкой. После вставки поля со списком вы уже должны находиться в режиме конструктора. Нажмите кнопку «Свойства» на вкладке «Разработчик». Если он не активен, нажмите кнопку "Режим разработки", а затем "Свойства".
      3. Появится окно свойств. В свойстве LinkedCell введите адрес ячейки, с которой вы хотите связать поле со списком.
        1. Давайте посмотрим, что у нас есть на данный момент. Нажмите кнопку «Режим дизайна», чтобы выйти из режима дизайна. Введите текст в поле со списком, и вы должны увидеть значение, появившееся в ячейке, с которой вы его связали.

        Определить, какие имена содержат искомые символы

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

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

        Можно использовать приведенную ниже функцию ПОИСК.

        В этой функции ячейка $G$1 — это ячейка, с которой связано поле со списком. Итак, это содержит строку символов, которую мы ищем. Ячейка A2 — это первое имя в списке имен.

        На изображении ниже видно, что искомые символы встречаются на пятой позиции имен в ячейках A2 и A3, на позиции 1 в ячейке A6 и вообще отсутствуют в других ячейках.

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

        Функция ISNUMBER затем может быть добавлена ​​к функции SEARCH, чтобы она возвращала TRUE или FALSE, когда имя идентифицировано или нет. Затем перед функцией ЕЧИСЛО добавляется двойное отрицание, чтобы преобразовать ИСТИНА или ЛОЖЬ в 1 или 0.

        Сколько имен содержит эти символы

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

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

        Функция COUNTIF используется здесь для подсчета вхождений возвращаемых имен. Эта функция использует ссылку на столбец «ЕСЛИ найдено» в B. Она имеет фиксированную начальную точку $B$2, но относительный номер строки в конечной точке $B2. Это гарантирует, что ссылка будет увеличиваться по мере копирования формулы.

        Подсчитывается, сколько раз 1 встречается в диапазоне до точки текущей строки.

        Затем к нему можно добавить функцию ЕСЛИ, чтобы она выполняла СЧЁТЕСЛИ, только если ячейка содержит 1, в противном случае в ячейке отображалось пустое значение.

        Вернуть имена для раскрывающегося списка с возможностью поиска

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

        Следующий шаг — создать диапазон ячеек, содержащих только эти имена. Этот диапазон ячеек можно использовать в раскрывающемся списке.
        Чтобы найти эти имена, мы можем использовать удивительную комбинацию функций ИНДЕКС и ПОИСКПОЗ.

        Здесь функция ИНДЕКС используется для возврата имени из диапазона A2:A88 из строки, заданной функцией ПОИСКПОЗ.

        Функция ROWS используется для возврата текущего номера строки. Таким образом, для первого имени в списке это строка 1 этого диапазона. Второе имя — строка 2 и так далее. Это работает, чтобы предоставить MATCH текущее имя, которое мы ищем (1-е, 2-е и т. д.).

        Затем функция ПОИСКПОЗ ищет это число в диапазоне C2:C88. Он сообщает обратно в ИНДЕКС с позицией имен, а ИНДЕКС извлекает имя.

        Затем к этому можно добавить функцию ЕСЛИОШИБКА, чтобы ячейка оставалась пустой, если больше нет имен для поиска.

        Определить динамический именованный диапазон для списка с возможностью поиска

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

        Перейдите на вкладку "Формулы" на ленте, а затем выберите "Определить имя". Введите имя для диапазона (я назвал его employees), а затем введите формулу, как показано ниже, в поле Refers to.

        Для создания динамического именованного диапазона мы использовали функцию ИНДЕКС со СЧЁТЕСЛИ. Функция ИНДЕКС может возвращать значение или ссылку, и в этом случае она нам нужна для последнего.

        Динамический диапазон начинается с $D$2, а затем функция СЧЁТЕСЛИ используется для нахождения последнего имени в списке для функции ИНДЕКС. «?*» используется в качестве критерия для подсчета количества ячеек, содержащих текст.

        Назначение динамического именованного диапазона полю со списком

        1. Перейдите на вкладку "Разработчик" на ленте, а затем нажмите кнопку "Режим разработки".
        2. Теперь мы находимся в режиме конструктора, щелкните поле со списком, а затем нажмите кнопку "Свойства".
        3. Введите имя, которое вы дали именованному диапазону, в поле ListFillRange.
          1. Закройте окно свойств.

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

          1. Дважды щелкните поле со списком (для этого вам все еще нужно находиться в режиме конструктора).
          2. Вы перейдете к окну кода, где нужно ввести небольшой фрагмент кода VBA. Введите строку ниже между строками Private Sub и End Sub.

          Настройте часть Combobox1 на имя вашего поля со списком.

          Если вы заинтересованы в изучении VBA, ознакомьтесь с нашим онлайн-курсом Excel VBA. Это фантастический навык, если вы активно пользуетесь Excel.

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

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