Именованные диапазоны в Excel — это отличный инструмент. Они позволяют делать такие вещи, как выпадающие списки в пункте
Проверка данных. Или можно присвоить имя диапазону с данными и в дальнейшем ссылаться на него вместо того, чтобы указывать координаты (A1:B5).
Одна из неприятностей, связанных с поддержкой списков — необходимость править диапазон в
Формулы > Диспетчер имён после каждого добавления/удаления строк данных в исходном диапазоне. Чтобы избежать подобной ситуации, можно создать динамический диапазон, применив формулы вместо жёстко заданных координат. Чаще всего используется функция СМЕЩ, как показано ниже. Запрос «Excel динамический диапазон» в любом поисковике вернёт сотни ссылок, большинство из которых будут вариантами формулы:
=СМЕЩ(Лист!$A$1, 0, 0, СЧЁТЗ ($A:$A), 1)
СМЕЩ возвращает диапазон, модифицированный относительно базового – пункт
Ссылка. Смещ
построкам и Смещ
постолбцам смещают начало диапазона на соответствующее число строк и столбцов. Высота и Ширина задают количество строк и столбцов в диапазоне.
Ссылка: обычно указывается верхняя левая ячейка именованного диапазона.
Смещ
построкам: обычно 0, т.к. стартовую позицию мы уже определили.
Смещ
постолбцам: так же обычно 0, по той же самой причине.
Высота: количество строк нашего диапазона (здесь будет формула, см. ниже).
Ширина: количество столбцов в нашем диапазоне (минимум 1).
Понятно, что функция СМЕЩ сама по себе не слишком полезна для наших целей. Если вы собираетесь использовать статические числа, то откажитесь от СМЕЩ, непосредственно прописав диапазон. Причина, по которой мы используем СМЕЩ — возможность замены параметров (аргументов) формулами. Они и позволяют получить динамическую часть.
Типичная формула динамического диапазона, которую можно найти на просторах интернета, использует СЧЁТ (для числовых данных) или СЧЁТЗ (для текста). Обе эти функции подсчитывают количество непустых ячеек. Если мы подсчитаем количество непустых ячеек в одном столбце и введём это число в параметр Высота, то мы получим диапазон от начальной ячейки до последней ячейки с записью. Но это в теории.
В действительности, если вы внимательно читали, мы получим диапазон высотой в то количество строк, которое мы получили от СЧЁТЗ. Если в диапазоне присутствуют пустые ячейки, то выходной диапазон окажется короче, чем необходимо, и последние ячейки потеряются. Помните об этой тонкости.
Наиболее часто динамический диапазон используется в следующих случаях:
- в ссылках в формулах для других таблиц
- для определения исходных диапазонов сводных таблиц
- для определения исходных диапазонов диаграмм
- для определения наборов переменных выпадающих списков
Если у нас есть таблица, в которой меняется количество строк, и есть формулы, использующие ссылку на эту таблицу, то применение динамического диапазона вполне логичный шаг. Допустим у нас есть «база данных», в которую мы заносим каждое обращение клиента, и мы хотим подсчитать количество обращений определённого клиента. Тогда можно сделать нечто такое: