В предыдущих заметкам я рассмотрел самый простой вариант реализации выпадающего списка с поиском.
Давайте рассмотрим второй, более сложный, но и более универсальный вариант.
Так как нам нужно обойтись без динамических массивов, то создадим вспомогательный столбец (1), в котором с помощью той же функции ПОИСК будем искать введенную комбинацию символов (2). При этом не забываем сделать ссылку на ячейку с будущим списком К2 абсолютной.
Далее по аналогии с помощью функции ЕЧИСЛО конвертируем полученные значения в ИСТИНУ или ЛОЖЬ.
Теперь преобразуем ЛОЖЬ в 0, а ИСТИНУ в последовательность чисел, с помощью которых в дальнейшем сформируем список. То есть сделаем так, чтобы вместо каждого значения ИСТИНА была порядковая цифра - 1,2,3 и т.д. Сделать это довольно просто с помощью обычной функции ЕСЛИ - если вычисленное формулой значение ИСТИНА, то выбираем максимальное значение в столбце и прибавляем к нему единицу, иначе, то есть в случае ЛЖИ, выводим ноль.
В результате получим пронумерованный перечень подходящих значений, который нужно перенести в отдельную таблицу. Именно эту таблицу мы и будем использовать для формирования элементов выпадающего списка.
Сделать это можно, например, с помощью функции ВПР. Для этого вручную добавим вспомогательный столбец с порядковыми номерами (в столбце F) и будем его использовать в функции ВПР.
Размножим формулу автозаполнением на весь диапазон, который по размеру равен исходной таблице.
Осталось выделить только действительные (неошибочные) значения и сформировать из них диапазон, который в дальнейшем будем использовать для выпадающего списка.
Так как количество элементов в этом диапазоне будет все время меняться в зависимости от введенных в ячейку K2 символов, то применим небольшую хитрость - создадим именованный диапазон.
Для этого на вкладке Формулы (1) выберем инструмент Диспетчер имен (2) и создадим новое имя (3). В качестве имени диапазона можно указать что угодно, например, «фио» (4). Ну а так как размеры диапазоны все время будут изменяться, то без формул (5) тут не обойтись. Нам пригодится функция СМЕЩ (OFFSET).
Данная функция возвращает ссылку на диапазон, отстоящий от начальной ячейки на заданное число строк и столбцов, то есть как раз то, что нам нужно, ведь начальная ячейка нам известна.
Укажем абсолютную ссылку на начальную ячейку G2, ну а так как сдвиг вниз и вправо у нас отсутствует, то следующие два аргумента будут равны нулю. Четвертый аргумент будет определять размер диапазона и это будет максимальное значение из вспомогательного столбца первой таблицы. Получить его очень просто с помощью функции МАКС. Ну и так как диапазон у нас имеет ширину в один столбец, то последний аргумент будет единицей.
=СМЕЩ($G$2;0;0;МАКС($A:$A);1)
Теперь осталось лишь в ячейке K2 сделать выпадающий список. В качестве источника данных укажем имя нашего именованного диапазона (4). Ну и не забываем снять флажок Выводить сообщение об ошибке, на соответствующей вкладке (5).
Все готово и список также будет работать.
С помощью функции ВПР можно подтянуть и соответствующий номер телефона.
Выпадающий список с быстрым поиском готов, но он будет работать только в одной ячейке. На а о том, как его можно адаптировать к заполнению данных в таблице, речь пойдет в следующей заметке.
Ссылки на мои ресурсы по Excel
★ YouTube-канал по Excel и Word
★ Телеграм
