📌 Проблема выпадающих списков: как создать динамический выпадающий список для диапазона

Концепция проверки данных с использованием выпадающих списков является безусловно удобным инструментом.

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

О том, как решить проблему и создать динамический выпадающий список для диапазона рассмотрим сегодня в обзоре 😉.

📢 Файл с примером размещен в конце статьи 🔽

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

▶️ Проблема выпадающих списков

При обычной ссылке на ячейку выпадающий список не является динамическим.

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

Например, представленный выпадающий список ссылается на диапазоны ячеек от A2:А7:

⚠️ Однако, если при добавлении новых записей их не будет в выпадающем списке:

Проблема: вновь добавленные значения не отображаются в выпадающем списке

🔔 Проблема легко поправима если преобразовать диапазон в таблицу:

📌 Как правильно создать таблицу в Excel? (Урок 15)

Excel на ИЗИ: ✅ Приемы эффективной работы в Microsoft Excel 27 мая 2022

🔔 Кроме того, наилучшей практикой является использовать именованные диапазоны:

В этом случае выпадающий список будет отображать все вновь вводимые данные:

🔔 Но, как быть, если по условиям задачи требуется работать именно с диапазонами, а ни таблицей!?

▶️ Как создать динамический выпадающий список для диапазона

При работе с диапазонами создать динамический выпадающий список можно используя сочетание функций СМЕЩ и СЧЁТЗ:

Например,

Формула создает динамический диапазон, начиная с ячейки $A$2 вниз до последней непустой ячейки перед $A$1048576.

Представленная формула вычисляет размер диапазона на основе количества непустых ячеек в данном столбце (от $A$2 до $A$1048576), а затем возвращает ссылку на этот диапазон с помощью функции смещения СМЕЩ(OFFSET).

⚙️ Краткое описание формулы:

🔘 Функция СЧЁТЗ подсчитывает количество непустых ячеек в указанном диапазоне:

Полученное значение используется в качестве параметра для функции СМЕЩ, чтобы определить, сколько строк или столбцов нужно сместиться относительно исходной ячейки.

🔘 Параметры функции СМЕЩ

$A$2 - ячейка, с которой начнется смещение;
Два 0 (нуля) после указания ссылки означают, что смещения по вертикали и горизонтали не требуется (0 строк);
СЧЁТЗ($A$2:$A$1048576) - определяет количество строк, на которое нужно сместиться относительно ячейки, с которой начнется смещение. Количество строк равно числу непустых ячеек в диапазоне.

➡️ В результате, формула возвращает массив значений с учетом числа непустых ячеек в указанном столбце, которое определяется функцией СЧЁТЗ: