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

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

=$A$1:INDEX($A:$A,lastrow)

Резюме

Один из способов создать динамический именованный диапазон в Excel - использовать функцию ИНДЕКС. В показанном примере именованный диапазон «данные» определяется следующей формулой:

=$A$2:INDEX($A:$A,COUNTA($A:$A))

который преобразуется в диапазон $ A $ 2: $ A $ 10.

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

Объяснение

На этой странице показан пример динамического именованного диапазона, созданного с помощью функции INDEX вместе с функцией COUNTA. Динамические именованные диапазоны автоматически расширяются и сужаются при добавлении или удалении данных. Они являются альтернативой использованию таблицы Excel, размер которой также изменяется при добавлении или удалении данных.

Функция ИНДЕКС возвращает значение в заданной позиции в диапазоне или массиве. Вы можете использовать INDEX для получения отдельных значений или целых строк и столбцов в диапазоне. Что делает INDEX особенно полезным для динамических именованных диапазонов, так это то, что он фактически возвращает ссылку. Это означает, что вы можете использовать INDEX для создания смешанной ссылки, например $ A $ 1: A100.

В показанном примере именованный диапазон «данные» определяется следующей формулой:

=$A$2:INDEX($A:$A,COUNTA($A:$A))

который преобразуется в диапазон $ A $ 2: $ A $ 10.

Как работает эта формула

Прежде всего обратите внимание, что эта формула состоит из двух частей, которые находятся по обе стороны от оператора диапазона (:). Слева у нас есть начальная ссылка для диапазона, жестко закодированная как:

$A$2

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

INDEX($A:$A,COUNTA($A:$A))

Здесь мы загружаем INDEX весь столбец A для массива, а затем используем функцию COUNTA, чтобы вычислить «последнюю строку» в диапазоне. COUNTA здесь хорошо работает, потому что в столбце A 10 значений, включая строку заголовка. Таким образом, COUNTA возвращает 10, что напрямую переходит в INDEX как номер строки. Затем INDEX возвращает ссылку на $ A $ 10, последнюю использованную строку в диапазоне:

INDEX($A:$A,10) // resolves to $A$10

Итак, окончательный результат формулы - это диапазон:

$A$2:$A$10

Двумерный диапазон

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

=$A$2:INDEX($1:$1048576,COUNTA($A:$A),COUNTA($1:$1))

Как и раньше, COUNTA используется для определения «последней строки», и мы снова используем COUNTA, чтобы получить «последний столбец». Они передаются в индекс как row_num и column_num соответственно.

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

Примечание. Excel 2003 поддерживает только 65535 строк.

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

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