Сделать формулу массива в Excel рассчитывать только на активные строки

Question

Когда Excel вычисляет формулы по столбцу (например, при поиске), он достаточно умен, чтобы рассчитывать только до последней активной строки, вместо того, чтобы выполнять все пустые строки.

Кажется, что с формулами массива это поведение меняется и вычисляется по всему столбцу (миллион строк в Excel 2010). Есть ли способ заставить формулу массива вычислять только по активному диапазону? (кроме замены ссылок на столбцы щедрой ссылкой, которая будет охватывать активный диапазон, например, A:1:A10000, замена A:A в электронной таблице, которую вы знаете, никогда не будет иметь 10000 строк.

Например, есть ли функция, которую вы можете выполнить в строке, которая будет возвращать ссылку на последнюю активную строку? Нечто вроде = Last(A:A), которое вернуло бы либо ссылку, либо номер строки

Если у вас есть лист с непрерывным диапазоном сверху вниз, то, я полагаю, вы могли бы использовать =INDIRECT("B1:B"&COUNTA(B:B))) , но он не идеален (будучи изменчивым, не обновляется, когда столбцы добавляются в электронную таблицу, и работает только с электронными таблицами, столбец которых не содержит пустых строк.

Answer 1

К сожалению, использование 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)

что, как уже упоминалось, является едва ли изменчивым на всех.

С уважением