Сделать формулу массива в 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 )

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

С уважением

  • Использование функций в критериях COUNTIF
  • Ошибка проверки данных, скрытая от окон замораживания?
  • Как я могу заставить «Calculated Columns» работать в excel?
  • Предотвратить преобразование значений Excel в даты при открытии файлов HTML
  • Нужно объединить строки в excel, учитывая три идентификатора без ущерба исходным данным
  • Как отключить автоматический формат номера Excel
  • В любом случае, если Excel 2010 обрабатывает цитируемое значение «08540» как текст без номера при открытии CSV
  • Фильтровать столбец Excel с разделителями-запятыми
  • Как вы планируете время на оси X в excel?
  • Почему Office 2010 не открывается файл Excel?
  • Как я могу гиперссылки из PowerPoint в Excel, ссылки на конкретную вкладку и названный диапазон или ячейку?
  • Interesting Posts

    Как я могу запустить другую команду, но с теми же аргументами?

    Почему SSL-квитирование дает исключение «Не удалось создать DH keypair»?

    {Debian} Отключение и повреждение внешнего диска при загрузке больших файлов с помощью файла seafile

    Java Swing; В двух classах, где поставить инструкции if и новых исполнителей действий?

    Параметр опционального анализа файлов Windows Bat

    Какова цель отверстий с надписью «Не закрывать» на жестких дисках?

    Как создать пользовательскую функцию ослабления с помощью Core Animation?

    VirtualBox 4.3.12, Windows 7 в качестве гостевой и хост-операционной системы: хост -> гостевой буфер не работает

    Что означает «синхронизированный»?

    «Ошибка анализа: проблема с парсией пакета» при установке приложения Android

    Как я могу определить, какая оперативная память будет соответствовать моему компьютеру?

    Есть ли способ сбросить хром: // флаги по умолчанию?

    Перенос лицензии Windows 8 и правильной не-переустановки

    Как остановить предупреждение в Notepad ++, если файл был изменен извне

    Гибридные жесткие диски, как они работают?

    Давайте будем гением компьютера.