В прошлой заметке я изложил алгоритм создания выпадающего списка с поиском для одной ячейки. В этой статье расскажу о том, как можно использовать такой список для заполнения данных в таблице.
При создании списка для одной ячейки мы сформировали динамический массив значений.
Логично, что нам потребуется создать аналогичные массивы и для других ячеек будущей таблицы. Обычно размеры такой таблицы известны заранее, поэтому их сразу можно обозначить цветом.
Так как для каждой ячейки конечной таблицы нам нужно будет создать свой динамический массив данных, то первое, что приходит на ум - это транспонировать массив во вспомогательной таблице.
В этом нам поможет функция ТРАНСП (TRANSPOSE). В итоге диапазон станет горизонтальным.
Теперь мы можем простым автозаполнением раскопировать формулу на нужный нам диапазон, то есть ровно на столько строк, сколько будет в итоговой таблице. То есть для каждой ячейки новой таблицы будет создан свой динамический массив данных.
Выпадающий список пока создан только для первой ячейки новой таблицы. Отредактируем его и изменим ссылку в источнике данных.
Сейчас здесь находится абсолютная ссылка на динамический диапазон. Изменим ее на смешанную, то есть нам нужно, чтобы номер строки мог изменяться - меняем $E$2 на $E2.
Ну а теперь просто скопировать первую ячейку таблицы (1), например, с помощью сочетания клавиш Ctrl + C. Затем выделим весь оставшийся диапазон ячеек (2) и сделаем специальную вставку либо через меню Вставить (3), либо с помощью сочетания клавиш Ctrl + Alt+ V. В появившемся окне выберем условия на значения (4).
Все готово. Теперь у каждой ячейки новой таблицы есть свой выпадающий список. Если мы ничего не вводим, то получаем список из всех имеющихся имен.
Ну и осталось подтянуть телефоны в соседний столбец.
Воспользуемся для этого еще одной относительно новой функцией ПРОСМОТРX (XLOOKUP). Она используется для поиска информации в диапазоне по строкам. Искать мы будем имя из соседней ячейки А4 новой таблицы, а производить поиск будем в столбце [Имя] исходной таблицы. При этом мы должны получить телефон из соседнего столбца [Телефон] исходной таблицы. Ну а если ничего не найдено, то выведем текст «нет телефона». Раскопируем функцию на весь столбец новой таблицы и получим результат.
Задача выполнена.
Есть еще один способ создания выпадающего списка с поиском без использования динамических массивов. И о нем я расскажу в следующей заметке.
Ссылки на мои ресурсы по Excel
★ YouTube-канал по Excel и Word
★ Телеграм