Формула Excel: динамический именованный диапазон со смещением -

Общая формула

=OFFSET(origin,0,0,COUNTA(range),COUNTA(range))

Резюме

Один из способов создать динамический именованный диапазон с формулой - использовать функцию СМЕЩЕНИЕ вместе с функцией СЧЁТ. Динамические диапазоны также известны как расширяющиеся диапазоны - они автоматически расширяются и сужаются для размещения новых или удаленных данных.

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

В показанном примере формула для динамического диапазона:

=OFFSET(B5,0,0,COUNTA($B$5:$B$100),COUNTA($B$4:$Z$4))

Объяснение

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

Первый аргумент в OFFSET представляет первую ячейку данных (источник), которой в данном случае является ячейка B5. Следующие два аргумента представляют собой смещения для строк и столбцов, и их значение равно нулю.

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

Для высоты мы используем функцию COUNTA для подсчета непустых значений в диапазоне B5: B100. Это предполагает отсутствие пустых значений в данных и значений, превышающих B100. COUNTA возвращает 6.

Для ширины мы используем функцию COUNTA для подсчета непустых значений в диапазоне B5: Z5. Это предполагает отсутствие ячеек заголовков и заголовков за пределами Z5. COUNTA возвращает 6.

На данный момент формула выглядит так:

=OFFSET(B5,0,0,6,6)

С этой информацией OFFSET возвращает ссылку на B5: G10, что соответствует диапазону высотой 6 строк и шириной 6 столбцов.

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

Вариант с полными ссылками на столбец / строку

Вы также можете использовать полные ссылки на столбцы и строки для высоты и ширины, например:

=OFFSET($B$5,0,0,COUNTA($B:$B)-2,COUNTA($4:$4))

Обратите внимание, что высота регулируется с помощью -2, чтобы учесть значения заголовка и заголовка в ячейках B4 и B2. Преимущество этого подхода - простота диапазонов внутри COUNTA. Недостаток заключается в огромном размере полных столбцов и строк - необходимо соблюдать осторожность, чтобы не допустить ошибочных значений за пределами диапазона, поскольку они могут легко сбить счет.

Определение последней строки

Есть несколько способов определить последнюю строку (последнее относительное положение) в наборе данных, в зависимости от структуры и содержания данных на листе: