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

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

Зачем нужен выпадающий список с поиском?

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

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

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

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

Шаг 1: Подготовка данных

1. Введите данные в столбец Excel. Например, если вы хотите создать список продуктов, введите названия продуктов в одном столбце (например, A2:A50).

Шаг 2: Создание именованного диапазона

1. Выделите весь диапазон данных (например, A2:A50).
2. Перейдите на вкладку «Формулы» и нажмите кнопку «Определить имя».
3. Присвойте диапазону уникальное имя, например, Продукты.

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

1. Выберите ячейку, в которой вы хотите разместить выпадающий список (например, C2).
2. Перейдите на вкладку «Данные» и нажмите «Проверка данных».
3. В диалоговом окне «Проверка данных» выберите «Список» в качестве критерия.
4. В поле «Источник» введите =Продукты (имя вашего диапазона, созданного на шаге 2).

Шаг 4: Включение функции поиска

Чтобы добавить функцию поиска к выпадающему списку, потребуется немного VBA-кода. Вот пример, как это сделать:

1. Откройте редактор VBA: Нажмите Alt + F11 для открытия редактора Visual Basic.
2. Вставьте код VBA

В редакторе VBA, в левом меню найдите и дважды щелкните на ваш рабочий лист (например, Sheet1). Вставьте следующий код:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Target.Address = "$C$2" Then
        With Me.ComboBox1
            .ListFillRange = "Продукты"
            .LinkedCell = Target.Address
            .Top = Target.Top
            .Left = Target.Left
            .Width = Target.Width
            .Height = Target.Height
            .Visible = True
            .Activate
        End With
    Else
        Me.ComboBox1.Visible = False
    End If
End Sub

Private Sub Worksheet_Activate()
    With Me.ComboBox1
        .Visible = False
        .Style = fmStyleDropDownList
    End With
End Sub

3. Создание элемента ComboBox:
   • На вкладке «Разработчик» выберите «Вставка» и создайте элемент ActiveX ComboBox на вашем листе, затем назначьте ему имя ComboBox1.
4. Сохраните и закройте редактор VBA.

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

Поиск выпадающем списке с использованием формул

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

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

Вот как это можно сделать:

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

1. Подготовка данных:
   1. Введите данные в столбец Excel. Например, если вы создаете список продуктов, введите их названия в одном столбце (например, A2:A50).
2. Фильтрация списка:
   1. Рядом с выпадающим списком, например, в ячейке D2, создайте поле ввода для поиска.
   1. В ячейке E2 используйте формулу для фильтрации данных на основе ввода в ячейку D2:

=ЕСЛИ(ЕЧИСЛО(ПОИСК($D$2; $A$2:$A$50)); $A$2:$A$50; "")

Если у вас доступна функция ФИЛЬТР, то используйте эту формулу:

=ФИЛЬТР(A2:A50;ЕЧИСЛО(ПОИСК($D$2; $A$2:$A$50)))

В столбце E начиная с ячейки Е2, вы увидите список значений из вашего перечня продуктов. Он будет динамически изменяться, как только вы введете что-либо в D2.

3. Создание выпадающего списка:
   • Выберите ячейку, в которой хотите разместить выпадающий список с поиском (например,C2).Перейдите на вкладку «Данные» и нажмите «Проверка данных».В диалоговом окне выберите «Список» в качестве критерия.В поле «Источник» введите диапазон данных =$E$2:$E$50.Поставьте галочку в пункте Игнорировать пустые ячейки.
   • Нажмите ОК.

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

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

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

Вот краткое руководство по созданию выпадающего списка с поиском в Excel365:

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

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

Если у вас остались вопросы или нужна помощь, не стесняйтесь обращаться. Удачи в работе с Excel!