Одна из распространенных проблем при работе с таблицами состоит в том, что иногда мы не знаем точный размер области листа, заполненной нашими данными. Кроме того, в будущем в таблицу могут заноситься новые данные, или из нее могут быть удалены какие-то строки, т.е. размер таблицы может меняться.
Стандартное решение такой задачи - делая ссылку на таблицу, захватить пустые ячейки - выделить "с запасом". Такой способ имеет два серьезных недостатка:
· | пустых строк может не хватить |
· | пустые ячейки, хоть и не содержат данных, но в некоторых ситуациях весьма заметно замедляют обработку данных |
Красивым решением будет использовать динамический именованный диапазон - его размеры меняются автоматически, в зависимости от количества непустых ячеек в заданной области данных. Рассмотрим следующий пример - вот такую таблицу, количество записей в которой в будущем может меняться:
Чтобы создать динамический именованный диапазон, ссылающийся на эту таблицу, откроем меню Вставка - Имя - Присвоить (Insert - Name - Define) и введем туда Имя диапазона (например Железо) и формулу, которая будет ссылаться на заполненный диапазон:
=СМЕЩ($A$1;0;0;СЧЁТЗ($A:$A);3)
Функция СМЕЩ (OFFSET) выдает ссылку на диапазон, смещенный относительно исходного на заданное количество ячеек и имеет несколько аргументов:
· | Ссылка - исходная ячейка, от которой идет отсчет - с нее начинается наш диапазон ($A$1) |
· | Смещение по строкам и столбцам - сдвиг начала диапазона относительно Ссылки - по нулям |
· | Высота - количество ячеек по вертикали, из которых состоит наш диапазон - определяем количество непустых ячеек в столбце А при помощи функции подсчета значений СЧЁТЗ($A:$A) |
· | Ширина - количество ячеек по горизонтали - в нашей таблице три столбца, поэтому = 3 |
Теперь динамическую ссылку Железо можно использовать в любой операции (фильтрации, консолидации, построении сводных таблиц, создания выпадающих списков в ячейках и т.д.) вместо выделения диапазона мышью.