Выпадающий список в Excel — удобный инструмент ввода данных. Ранее мы разобрались, как создать выпадающий список. Однако, если ваш выпадающий список постоянно пополняется, нужно сделать динамический выпадающий список в excel.
Динамический или растущий список excel — это список, который при добавлении новых позиций не нужно корректировать. Другими словами, если вы добавили в список новые позиции, они автоматически окажутся в вашем выпадающем списке. Без дополнительных манипуляций.
Для Excel версии от 2007 и новее
Самый простой способ создать динамический выпадающий список excel — при помощи “умной” таблицы.
Разберемся на примере.
Есть список городов, который в дальнейшем будет пополняться.
Шаг 1. Превратим список в “умную” таблицу. Для этого щелкнем на любой ячейке в списке и нажмем сочетание клавиш Ctrl + T. Excel автоматически выделит диапазон списка.
Если у вашего списка есть заголовок (как в примере), оставьте галочку “Таблица с заголовками”, в противном случае снимите ее.
Также, вместо сочетания клавиш Ctrl + T можно воспользоваться меню: Вкладка Главная — блок Стили — Форматировать как таблицу — выбрать любой формат.
Должна получиться вот такая таблица.
Шаг 2. Щелкнем на любой ячейке таблицы-списка и перейдем на вкладку Конструктор. Здесь в блоке Свойства можно увидеть имя созданной “умной” таблицы.
Оно-то и понадобится нам для создания динамического выпадающего списка excel. Давайте изменим его.
Шаг 3. Выделим ячейку, в которую нужно вставить выпадающий список. Далее перейдем на вкладку Данные — блок Работа с данными — Проверка данных.
И в поле источник укажем следующую формулу.
=ДВССЫЛ(«города[Города]»)
Выражение города[Города] — это ссылка на столбец с данными для списка из нашей “умной” таблицы. Проблема в том, что Excel не понимает прямых ссылок в поле Источник, т.е. нельзя написать в поле Источник выражение вида =города[Города]. Поэтому мы идем на хитрость — вводим ссылку как текст (в кавычках) и используем функцию ДВССЫЛ (англ. INDIRECT), которая преобразовывает текстовую ссылку в настоящую.
Нажмем Ок.
Динамический выпадающий список excel готов.
Осталось проверить, как работает пополнение этого списка. Добавим в список еще один город — Сочи. И сразу увидим, что он появился в выпадающем списке.
Динамический выпадающий список Excel версии от 2003 и ранее
В старших версиях Excel еще не было “умных” таблиц, поэтому применить первый способ невозможно. Придется выкручиваться при помощи функции СМЕЩ (англ. OFFSET), которая имитирует создание динамического диапазона.
Откроем вкладку Формулы — блок Определенные имена — Присвоить имя.
Присвоим имя диапазону и напишем следующую формулу:
Давайте разберемся в синтаксисе формулы СМЕЩ.
=СМЕЩ(ссылка; смещение по строкам; смещение по столбцам; [высота]; [ширина])
=СМЕЩ($A$2;0;0;СЧЁТЗ(A2:A100);1)
$A$2 — это ссылка, т.е. начало диапазона
0 — Смещение по строкам, сдвиг начальной ячейки на заданное количество строк, здесь не требуется.
0 — смещение по столбцам, тоже не требуется.
СЧЁТЗ(A2:A100) — подсчитывает количество непустых ячеек в столбце с городами, т.е.определяет количество строк в выпадающем списке. Обратите внимание, что в данном случае диапазон нужно брать с запасом, т.к. после ячейки А100 значения перестанут добавляться в список.
1 — размер диапазона, который получается в итоге, т.е. один столбец.
Теперь нужно выделить ячейки, в которые хотите поместить динамический выпадающий список excel, и перейти на вкладку Данные — блок Работа с данными — Проверка данных.
В поле Тип данных выбрать Список, а в поле Источник написать:
=города
Теперь попробуем добавить в выпадающий список новые позиции.
Как видите, динамический выпадающий список excel прекрасно работает. Новая позиция в списке — город Мурманск — добавилась в него автоматически.
Статья взята с моего сайта https://excel-analytics.ru/