Выпадающие списки - это очень удобный инструмент, который позволяет исключить ошибку при вводе однотипных данных. В этой заметке расскажу о приеме, который позволит автоматически расширять данные списка без дополнительной его корректировки.
Обычно данные для выпадающего списка выносятся на отдельный лист, чтобы они не путались с данными основной таблицы. Затем создается список с помощью инструмента проверки данных.
Если в процессе работы возникает необходимость дополнить список, то придется корректировать диапазон значений (Источник) с помощью инструмента проверки данных, что очень неудобно.
Автоматизировать процесс добавления новых значений поможет формула на базе функции СМЕЩ. Данная функция возвращает ссылку на диапазон, отстоящий от указанной ячейки на заданное число строк и столбцов. Функция СМЕЩ имеет пять аргументов.
В первую очередь указывается ссылка на ячейку, от которой будет осуществляться смещение. В моем случае это первая ячейка со значениями выпадающего списка - А1. Затем указывается смещение по строкам и столбцам относительно ранее указанной ячейки. То есть если бы мне нужно было вернуть значение ячейки B4, то я бы указал смещение по строкам равным 3, а по столбцам 1. В данном случае мне не нужно смещаться по строкам и столбцам, а необходимо вернуть значения всего диапазона, начиная от указанной ячейки. Поэтому эти два аргумента будут равны нулю.
Ну и два последних аргумента - высота и ширина не являются обязательными, но именно они нам сейчас и помогут решить задачу. Фактически нам нужен будет только аргумент Высота, так как он определяет размер диапазона. То есть если мы зададим высоту равную 4, то будут возвращены значения диапазона A1:A4, но нам нужны все значения столбца А.
И как же узнать, сколько ячеек столбца А включить в диапазон?
Давайте для этого задействуем функцию СЧЁТЗ, которая подсчитает количество непустых ячеек во всем столбце А, ведь именно количеству непустых ячеек и равна высота необходимого нам диапазона.
=СМЕЩ(A1;0;0;СЧЁТЗ(A:A);1)
В итоге мы получаем диапазон значений, начинающийся с ячейки А1 и имеющий высоту, рассчитанную с помощью функции СЧЁТЗ, шириной в один столбец.
Ну а как эту формулу привязать к выпадающему списку?
Поможет в этом именованный диапазон. Давайте создадим новый именованный диапазон, который и будет определяться составленной формулой. Назовем его, например, «Список». Далее необходимо указать в соответствующем поле диапазон, для которого имя задается, но мы сюда подставим ранее составленную формулу. И здесь важно указать полную ссылку на ячейку, ведь если в документе есть несколько листов, то мы должны явно указать тот лист, на котором находятся данные для выпадающего списка.
Теперь осталось лишь создать выпадающий список в нужном диапазоне ячеек основной таблицы. В нем сошлемся на ранее созданный именованный диапазон.
Если в дальнейшем в список на втором листе будут добавляться данные, то они автоматически включатся в именованный диапазон, а значит появятся и в выпадающем списке в основной таблице.
Ссылки на мои ресурсы по Excel
★ YouTube-канал по Excel и Word
★ Телеграм