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

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

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

Например, есть ли функция, которую вы можете выполнить в строке, которая вернет ссылку последней активной строки? Что-то вроде = Последнее (A: A), которое вернет ссылку или номер строки

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

One Solution collect form web for “Сделать формулу массива в excel вычислять только по активным строкам”

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

Который, как уже упоминалось, практически нестабилен.

С уважением

  • Сравнение счетов в Windows Excel 7 между двумя разными листами и объединение в один список
  • Предотвратить преобразование значений Excel в даты при открытии файлов HTML
  • Как сохранить двойные кавычки и точку с запятой в файле csv при открытии файла в excel 2010?
  • Дублирующая формула в Excel 2010
  • Как создать динамически растущий граф, который игнорирует пустые хвостовые ячейки?
  • Как объединить два списка в один длинный список?
  • Рассчитайте первое появление одного критерия на основании второго критерия
  • Как я могу выполнить эту функцию с помощью VBA в excel?
  • SUMIF с несколькими критериями соответствия в Excel
  • Окраска частичного текста внутри ячейки в Excel
  • Как изменить формат даты по умолчанию?
  • Как я могу суммировать свои данные по неделям?
  • Давайте будем гением компьютера.