К сожалению, использование volatile INDIRECT
и OFFSET
почти повсеместно рекомендовано в таких случаях на различных сайтах Excel, хотя существует совершенно хороший, минимально изменчивый (фактически, насколько я понимаю, изменчивый только при открытой рабочей книге) установка с использованием INDEX
.
Чтобы определить последнюю непустую строку в столбце, скажем, в столбце A, будет полезно узнать тип данных записей в этом столбце, а также наличие присутствующих пустых строк ("").
Хотя существуют доступные настройки, которые будут работать независимо от типа данных рассматриваемых записей, они обязательно имеют такой тип, что должны обрабатывать каждый элемент в пределах диапазона, передаваемого им. Как таковые - и особенно если диапазон, в котором необходимо рассчитать, достаточно велик - они могут быть чрезвычайно ресурсоемкими (второе предположение, приведенное Кайлом, является хорошим примером того, что нужно обрабатывать более миллиона ячеек, независимо от того, последняя непустая ячейка находится в строке 1 или строке 1048576).
Также стоит сохранить значение последней непустой строки как Определенное имя, говорят в LRow .
Возможные определения для LRow следующие:
1) Если нет нулевых строк и все записи не числовые, вы можете определить LRow как:
=MATCH("Ω",A:A)
2) Если нет нулевых строк и все записи являются числовыми, вы можете определить LRow как:
=MATCH(9.9E+307,A:A)
3) Если нет нулевых строк и записи имеют смешанный тип данных, то есть некоторые числовые, некоторые нечисловые, вы можете определить LRow как:
=MAX(MATCH("Ω",A:A),MATCH(9.9E+307,A:A))
Если в диапазоне есть нулевые строки, то решения становятся обязательно более сложными, и больше невозможно избежать конструкции, в которой каждая ячейка в массиве обрабатывается индивидуально. Таким образом, лучшее, что мы можем сделать, - это минимизировать диапазон, который мы передаем для обработки.
Следовательно, мы имеем:
4) Если есть пустые строки, вы можете определить LRow как:
=MATCH(1,0/(LEN(A1:INDEX(A:A,MAX(MATCH("Ω",A:A),MATCH(9.9E+307,A:A))))>0))
(Конечно, вы можете сделать это еще более эффективным, если тип данных последовательно числовой или не числовой, как указано выше.)
который требует записи массива (CSE) и который, в общем, значительно уменьшит обрабатываемый диапазон, особенно по сравнению с чем-то вроде:
=MATCH(1,0/(LEN(A:A)>0))
что губительно с точки зрения эффективности.
Обратите внимание, что дополнительные функции внутри первого имеют очень небольшой ущерб: гораздо важнее уменьшить количество обрабатываемых ячеек, чем беспокоиться о дополнительном вызове функции или двух.
Теперь вы можете использовать это в конструкции INDEX
для динамического определения вашего диапазона. Например:
=A1:INDEX(A:A,LRow)
что, как уже упоминалось, является едва ли изменчивым на всех.
С уважением