Задача: Создать выпадающий список в Excel таким образом, чтобы в него автоматически попадали все новые значения.

Сделаем это при помощи формул, чтобы этот способ можно было использовать не только в Excel 2007 и старше, но и в Excel 2003.

Подробно об обычных способах создания выпадающего списка вы можете прочитать в этой статье. Здесь мы рассмотрим нестандартные подходы.

Чтобы создать автоматически пополняемый выпадающий список, мы будем использовать динамический диапазон. Давайте в начале разберемся, что это такое, чтобы четко понимать, что мы будем делать.

Динамический диапазон в Excel – это диапазон ячеек, который автоматически адаптируется при изменении данных в этих ячейках. Это означает, что если данные добавляются или удаляются, диапазон будет соответственно расширяться или сужаться, без необходимости вручную обновлять ссылки на ячейки.

Примером использования динамического диапазона может служить список, в который регулярно добавляются данные. Если вы используете стандартный диапазон, например A1:A10, и добавите данные в A11, этот новый элемент не будет включен в исходный диапазон. Однако, если вы используете динамический диапазон, он автоматически обновится, чтобы включить A11.

Как обычно, создадим на листе нашей рабочей книги диапазон допустимых значений для выпадающего списка. Расположим их в столбце с таким расчетом, чтобы в конец этого перечня можно было дописывать новые позиции. Пусть это будет столбец А.

Динамический выпадающий список при помощи функции СМЕЩ

Попробуем при помощи функции СМЕЩ (OFFSET) имитировать работу "умной" таблицы Excel. Это нам может быть полезно также в тех случаях, когда приходится использовать старые версии Excel до 2007 года.

источник данных для выпадающего списка при помощи функции СМЕЩ

Формула источника будет выглядеть следующим образом:

=СМЕЩ(A2,0,0,СЧЁТЗ(A2:A200),1)

Давайте разберемся, как она работает.

Функция СМЕЩ формирует ссылку на диапазон заданного размера. Размер этот определяется четырьмя параметрами - A, B, C, D и начальной точкой отсчета.

как работает функция СМЕЩ

А2 - это начальная ячейка, по отношению к которой будет выделен наш диапазон. Он всегда задается двумя координатами - левой верхней и правой нижней. Подразумевается, что начало диапазона (левый верхний его угол) будет находиться или в начальной ячейке, или чуть ниже и правее нее. На нашем рисунке это зелёная область. Параметр А показывает, на сколько ячеек вниз нам нужно опуститься. Параметр В - на сколько сдвинуться вправо. Если, как в нашем случае, они оба равны нулю, значит в нашей начальной ячейке и будет находиться левая верхняя точка.

И теперь нужно определить правую нижнюю точку. Теперь уже от начальной точки диапазона делаем С шагов вниз и D шагов вправо. В нашем примере D=1. То есть нам нужен только один столбец. Осталось только определиться, сколько шагов вниз нужно сделать, сколько строк взять в наш список. Нам нужна последняя заполненная строка в столбце А. Точнее, не сама строка, а ее порядковый номер.

Здесь нам поможет вторая функция - СЧЕТЗ (COUNTA). Она подсчитывает количество значений в заданном нами диапазоне A2:A200. Можно взять и более удаленные координаты, но думается, что 200 строк в списке будет вполне достаточно. Естественно, вместо ссылки можно использовать именованный диапазон.

В нашем примере мы имеем 8 значений. Значит, возьмем 8 строк, начиная с А2. То есть, A2:A9.

Если в ячейке А10 появится новое значение, тогда значений станет уже 9. Соответственно диапазон автоматически расширится до A2:A10. И так далее.

создание динамического диапазона в Excel

Но будьте внимательны! Если вы случайно пропустите строку и введете новое значение не в конец списка, а несколько ниже, то наша стройная система даст сбой. Диапазон значений для раскрывающегося списка увеличится, но захватит пустую ячейку. А наша последняя запись останется вне списка, расширение не дотянется до нее. Ведь в нашем случае мы имеем 9 значений и 10 строк. Естественно, последняя строка из списка выпадает.

Динамический диапазон при помощи ИНДЕКС и СЧЁТА

Создать динамический диапазон можно с помощью функции СМЕЩ, как показано в предыдущем примере, или с использованием функции ИНДЕКС в сочетании с СЧЁТ или СЧЁТА. Это позволяет диапазону динамически изменяться в зависимости от количества данных в столбце или строке.

Вот пример формулы с использованием ИНДЕКС и СЧЁТА для создания динамического диапазона:

=ИНДЕКС(A:A; 1):ИНДЕКС(A:A; СЧЁТА(A:A))

Эта формула создаст диапазон, который начинается в A1 и продолжается до последней заполненной ячейки в столбце A. При добавлении новых данных в столбец A, диапазон автоматически включит эти данные.

Возможно, этот способ создания автоматически пополняемого выпадающего списка будет для вас удобен. Тем более, что на его основе можно делать и другие интересные вещи.

Например, вы можете создавать многоуровневые зависимые выпадающие списки, где значения каждого следующего списка зависят от предыдущего.

Надеюсь, эта информация была полезна.

Если у вас есть вопросы, оставляйте их в комментариях.