Google Sheets6 мин·
Как сделать выпадающий список с поиском в Google Sheets
Выпадающий список с автодополнением и поиском: chip-based data validation, FILTER для фильтрации списка, Apps Script для автокомплита.
Проблема обычного выпадающего списка
В списке 500 городов. Скроллить до нужного — мучение. В Excel можно начать набирать, и список отфильтруется. В Google Sheets стандартная проверка данных так не работает. Разберём решения.
Способ 1: Новая проверка данных (2023+)
Google Sheets обновил проверку данных. Теперь:
1. Данные → Проверка данных → Добавить правило
2. Критерий: Раскрывающийся список (из диапазона или указать вручную)
3. Стиль отображения: «Чип» или «Стрелка»
4. Нажмите на ячейку — появится поисковое поле
Введите первые буквы — список отфильтруется автоматически.
💡 Совет: Этот способ работает только в новом интерфейсе Google Sheets. Если у вас старый — обновите настройки.
Способ 2: Вспомогательный столбец с FILTER
Создайте ячейку для ввода текста (E1). В соседнем столбце динамический список:
=FILTER(Справочник!A:A;REGEXMATCH(LOWER(Справочник!A:A);LOWER(E1)))
💡 Совет: Пользователь вводит «мос» в E1 — список показывает: Москва, Московская область, Мосрентген. LOWER — чтобы поиск был без учёта регистра.
Способ 3: Named Functions + LAMBDA
Данные → Именованные функции → Создать функцию ПОИСК_В_СПИСКЕ, которая комбинирует FILTER + SORT + UNIQUE. Затем используйте эту функцию в проверке данных через «Ваша формула».
Зависимые списки
Первый список — страна. Второй — город, зависящий от страны.
1. Первый список: обычная проверка данных со странами
2. Второй: проверка данных → диапазон:
=FILTER(Города!B:B; Города!A:A=A2)
A2 — ячейка с выбранной страной.
В Excel
В Excel выпадающий список фильтруется при вводе по умолчанию (начиная с Excel 365). Для старых версий — зависимые списки через ДВССЫЛ (INDIRECT) и именованные диапазоны.
Заключение
Новая проверка данных Google Sheets решает проблему поиска. Для сложных сценариев — FILTER с вспомогательной ячейкой. Для зависимых списков — FILTER по критерию. Fast Formula поможет с формулами для фильтрации и зависимых данных.
🤖
Не хотите запоминать формулы?
Опишите задачу на русском — AI сгенерирует формулу с правильными ссылками на вашу таблицу. 5 запросов бесплатно.
Попробовать бесплатно →Читайте также
ТОП-20 формул Excel, которые должен знать каждый бухгалтер
Подборка самых нужных формул Excel для бухгалтерии: СУММЕСЛИ, ВПР, ЕСЛИ, СЧЁТЕСЛИ и другие. С примерами и объяснениями на русском.
Как сделать ВПР (VLOOKUP) в Google Таблицах — полное руководство
Подробное руководство по VLOOKUP в Google Sheets: синтаксис, примеры, ошибки, альтернативы (INDEX/MATCH, XLOOKUP). С картинками и объяснениями.