Выпадающий список с быстрым поиском ➤ Создаем выпадающий список в Excel

DZEN · 29.11.2025 07:47

Ключевые темы и таймкоды

Введение в выпадающие списки в Excel

0:00

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

Способы создания выпадающих списков

0:51

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

Пример создания списка с помощью функции «Поиск»

1:13

Исходная таблица содержит два столбца: имена и номера телефонов.
Функция «Поиск» используется для поиска заданного набора символов в тексте.
Результат функции: порядковый номер первого символа или ошибка, если символы не найдены.

Работа с динамическими массивами

2:18

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

Преобразование значений в формат «истина/ложь»

3:54

Функция «ЕЧисло» преобразует значения в формат «истина/ложь».
Функция «Фильтр» используется для фильтрации значений.

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

5:22

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

Подстановка номера телефона

6:39

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

Сортировка и удаление дубликатов

8:39

Функция «Сорт» сортирует данные по алфавиту.
Функция «Уник» удаляет дубликаты из списка.

Создание выпадающих списков для всей таблицы

9:29

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

Второй способ создания выпадающего списка

11:36

Создание вспомогательного столбца для поиска комбинации символов.
Конвертация значений в «истина/ложь» с помощью функции «ЕЧисло».
Формирование списка с помощью функции «Если», которая преобразует «истина» в последователь

Перенос значений в таблицу

12:55

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

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

13:32

Выделяем действительные значения из диапазона.
Создаём именованный диапазон для динамического изменения количества элементов.
На вкладке «Формулы» выбираем инструмент «Диспетчер имён» и создаём новое имя для диапазона.

Использование функции СМЕЩ

14:03

Функция СМЕЩ возвращает ссылку на диапазон, отстоящий от начальной ячейки на заданное количество строк и столбцов.
Указываем абсолютную ссылку на начальную ячейку диапазона.
Четвёртый аргумент функции — максимальное значение из вспомогательного столбца исходной таблицы, полученное с помощью функции МАКС.
Последний аргумент — единица, так как диапазон имеет ширину в один столбец.

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

14:53

Указываем имя именованного диапазона в качестве источника данных для выпадающего списка.
Снимаем флажок «Выводить сообщения об ошибке» на соответствующей вкладке.
Выпадающий список готов к использованию.

Дополнительные возможности

15:13

С помощью функции ВПР можно подтянуть соответствующий номер телефона.
Призыв оценить видео и подписаться на канал.