Динамический именованный диапазон в Excel

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

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

Что такое динамический именованный диапазон

Именованный диапазон  — это диапазон, которму присвоено некоторое имя (желательно понятное) вместо стандартных адресов вроде A1:A100. Динамический именованный диапазон отличается тем, что его размер определяется формулой. Такой диапазон автоматически расширяется при добавлении данных и сжимается при их удалении.

Вместо адресов ячеек используется логика на базе функций СЧЁТЗ, ИНДЕКС, СМЕЩ, ПОИСКПОЗ и, в отдельных случаях, ДВССЫЛ. Благодаря этому диапазон всегда охватывает фактическое количество данных.

Как создать динамический диапазон
через Диспетчер имён

1. Перейдите на вкладку Формулы.
2. Откройте Диспетчер имён.
3. Нажмите Создать.
4. Введите имя диапазона без пробелов. Можно использовать символ _ (подчеркивание).
5. В поле Диапазон укажите формулу, определяющую границы.
6. Нажмите ОК.

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

Основные подходы к созданию динамических диапазонов

Через СМЕЩ

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

СМЕЩ(Лист1!$A$1;0;0;СЧЁТЗ(Лист1!$A:$A);1)

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

Через ИНДЕКС

Более производительный и стабильный вариант  — использование функции ИНДЕКС. Пример динамического диапазона по столбцу A:

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

Функция ИНДЕКС пересчитывается только когда изменяются аргументы функции. Такой подход лучше подходит для крупных отчётов.

Примеры динамических диапазонов

Пример 1. Сумма по увеличивающимся списку

Создадим для столбца с суммами продаж динамический диапазон с именем ПРОДАЖИ, в этом случае формула

=СУММ(ПРОДАЖИ)

будет учитывать числа в новых ячейках без ручного изменения адресов ячеек в формуле.

Пример 2. Динамический выпадающий список

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

Пример 3. Диапазон для диаграммы

Можно указать динамический именованный диапазон в качестве источника данных диаграммы. В этом случае график будет автоматически расширяться при добавлении новых строк или столбцов.

Пример 4. Работа с пропусками в данных

С помощью функций ИНДЕКС и ПОИСКПОЗ можно находить последнюю непустую ячейку даже в столбцах с пропусками (пустыми ячейками). Таким способом можно задавать динамический диапазон до фактического конца данных.

Пример 5. Динамический диапазон с помощью умной таблицы Excel

Динамический диапазон будет автоматически создан при создании Умной Таблицы (горячая клавиша Ctrl+T). Такой подход, не требующий без сложных формул, часто является самым надёжным и простым.

Где используются динамические именованные диапазоны

• В формулах для автоматических расчётов.
• В диаграммах для автоматического обновления графиков.
• В выпадающих списках для динамических справочников.
• В подготовке данных для сводных таблиц.
• В аналитических отчётах с регулярно обновляемыми данными.

Важные нюансы и ограничения

• Функции СМЕЩ и ДВССЫЛ являются волатильными функциями и могут замедлять пересчёт больших файлов.
• Функция ИНДЕКС в большинстве случаев предпочтительнее по производительности.
• Функции СЧЁТЗ считает любые непустые ячейки, включая текст, а СЧЁТ  — только числовые значения.
• Всегда проверяйте получившийся диапазон через Диспетчер имён.

Итог

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

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