Последняя непустая ячейка в столбце

используя следующую простую формулу гораздо быстрее

=LOOKUP(2,1/(A:A<>""),A:A)

Для Excel 2003:

=LOOKUP(2,1/(A1:A65535<>""),A1:A65535)

это дает вам следующие преимущества:

• это не массив формула
• это не летучие формула

объяснение:

• (A:A<>"") возвращает массив {TRUE,TRUE,..,FALSE,..}
• 1/(A:A<>"") изменяет это массив в {1,1,..,#DIV/0!,..}.
• С LOOKUP ждет отсортированный массив по возрастанию порядок, и с учетом того, что если LOOKUP функция не может найти точное соответствие, она выбирает самое большое значение в lookup_range (в нашем случае {1,1,..,#DIV/0!,..}), меньше или равно значению (в нашем случае 2), формула находит последний 1 в массиве и возвращает соответствующее значение из result_range (третий параметр - A:A).

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

=LOOKUP(2,1/(NOT(ISBLANK(A:A))),A:A)

картинка ниже показывает разницу:

вот еще один вариант: =OFFSET($A;COUNTA(A:A)-1;0)

вот это:

=SUMPRODUCT(MAX(($A:$A<>"")*(ROW(A:A))))

для поиска последней непустой строки, используя столбец A в качестве опорного столбца

=SUMPRODUCT(MAX((:<>"")*(COLUMN(1:1))))

для поиска последнего непустого столбца с помощью строки 1 в качестве опорной строки

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

я протестировал вышеуказанные методы с Excel 2010, как "изначально", так и в "режиме совместимости" (для более старых версий Excel), и они работают. Опять же, с ними вам не нужно делать ни одного из Ctrl+Shift+Enter. Путем использовать способ sumproduct работает в Excel мы можем получить наши руки вокруг необходимости выполнять массив-операции, но мы делаем это без массива-формулы. Я надеюсь, что кто-то там может оценить красоту, простоту и элегантность этих предлагаемых решений sumproduct так же, как и я. Однако я не подтверждаю эффективность памяти вышеупомянутых решений. Просто они просты, выглядят красиво, помогают по назначению и достаточно гибки, чтобы распространить их использование на другие цели:)

надеюсь, что это помогает!

всего наилучшего!

это работает в Excel 2003 (и позже с незначительным редактированием, см. ниже). Нажмите Ctrl + Shift+Enter (не просто Enter), чтобы ввести это как формулу массива.

=IF(ISBLANK(A65536),INDEX(A1:A65535,MAX((A1:A65535<>"")*(ROW(A1:A65535)))),A65536)

имейте в виду, что Excel 2003 является можете чтобы применить формулу массива ко всему столбцу. Это дает#NUM!; непредсказуемые результаты могут произойти! (EDIT: противоречивая информация от Microsoft: то же самое мая или не может быть правдой о Excel 2007; проблемы мая были зафиксированы в 2010 году.)

вот почему я применяю формулу массива к диапазону A1:A65535 и дать специальное обращение к последней ячейке, которая составляет A65536 в Excel 2003. Не могу просто сказать A:A или даже A1:A65536 как последний автоматически возвращается к A:A.

если вы абсолютно уверены A65536 пусто, то вы можете пропустить IF детали:

=INDEX(A1:A65535,MAX((A1:A65535<>"")*(ROW(A1:A65535))))

обратите внимание, что если вы используете Excel 2007 или 2010, последняя строка номер 1048576 не 65536, поэтому отрегулируйте выше по мере необходимости.

если в середине ваших данных нет пустых ячеек, то я бы просто использовал более простую формулу,=INDEX(A:A,COUNTA(A:A)).

Я знаю, что этот вопрос старый, но я не удовлетворен предоставленными ответами.

• LOOKUP, VLOOKUP и HLOOKUP имеют проблемы с производительностью и никогда не должны использоваться.

• функции массива имеют много накладных расходов, а также могут иметь проблемы с производительностью, поэтому его следует использовать только в крайнем случае.

• COUNT и COUNTA сталкиваются с проблемами, если данные не являются смежно непустыми, т. е. у вас есть пробелы и затем данные снова в рассматриваемом диапазоне

• непрямой является изменчивым, поэтому он должен использоваться только в крайнем случае

• смещение изменчиво, поэтому его следует использовать только в крайнем случае

• любые ссылки на последнюю строку или столбец (например, 65536-я строка в Excel 2003) не являются надежными и приводят к дополнительным накладным расходам

Это то, что я использую

• когда тип данных является смешанным:=max(MATCH(1E+306,[RANGE],1),MATCH("*",[RANGE],-1))

• когда известно, что данные содержат только цифры: =MATCH(1E+306,[RANGE],1)

• когда известно, что данные содержат только текст: =MATCH("*",[RANGE],-1)

матч имеет самые низкие накладные расходы и является энергонезависимым, так что если вы работаете с большим количеством данных, это лучше всего использовать.

альтернативное решение без формул массива, возможно более надежной, чем предыдущий ответ с (подсказкой к) решению без формул массива, составляет

=INDEX(A:A,INDEX(MAX(($A:$A<>"")*(ROW(A:A))),0))

посмотреть ответ в качестве примера. Слава бред и Барри Гудини, кто помог решить этот вопрос.

приведены возможные причины предпочтения формулы без массива в:

1. официальная страница Microsoft (ищите "недостатки использования формул массива").
   Формулы массива могут казаться волшебными, но они также имеют некоторые недостатки:

   • иногда вы можете забыть нажать CTRL + SHIFT+ENTER. Не забывайте нажимать эту комбинацию клавиш всякий раз, когда вы вводите или редактируете формулу массива.
   • другие пользователи могут не понять ваши формулы. Формулы массива относительно недокументированы, поэтому если другим пользователям необходимо изменить ваши книги, вы должны либо избегать формул массива, либо убедиться, что эти пользователи понимают, как их изменить.
   • в зависимости от скорости обработки и памяти вашего компьютера, большие формулы массива может замедлить расчеты.

2. Формула Массива Ересь.

=MATCH("*";A1:A10;-1) для текстовых данных

=MATCH(0;A1:A10;-1) для числовых данных

=INDEX(A:A, COUNTA(A:A), 1) принято от здесь

Ive пробовал все энергонезависимые версии, но не одна версия, приведенная выше, работала.. excel 2003 / 2007update. Конечно, это можно сделать в Excel 2003. Не как массив или стандартная формула. Я либо получаю только пустую, 0 или #значение ошибки. Поэтому я прибегаю к летучим методам .. Это сработало..

=LOOKUP (2,1/(T4: T369""), T4:T369)

@Julian Kroné .. Использование "; "вместо", " не работает! Я думаю, что вы используете Libre Office, а не MS excel? УВАЖАТЬ это так раздражающе волевой я использую его только в крайнем случае

поместите этот код в модуль VBA. Спасать. В разделе функции, определяемые пользователем, найдите эту функцию.

Function LastNonBlankCell(Range As Excel.Range) As Variant
    Application.Volatile
    LastNonBlankCell = Range.End(xlDown).Value
End Function

для текстовых данных:

EQUIV("";A1:A10;-1)

для числовых данных:

EQUIV(0;A1:A10;-1)

это дает вам относительный индекс последней непустой ячейки в выбранном диапазоне (здесь A1:A10).

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

INDIRECT("A" & (nb_line_where_your_data_start + EQUIV(...) - 1))

У меня тоже такая же проблема. Эта формула также работает одинаково хорошо:-

=INDIRECT(CONCATENATE("$G$",(14+(COUNTA($G:$G535)-1))))

14 - номер строки первой строки в строках, которые вы хотите подсчитать.

Хронический Клык

я использовал HLOOKUP

A1 свидание; A2:A8 есть прогнозы, захваченные в разное время, я хочу последний

=Hlookup(a1,a1:a8,count(a2:a8)+1)

Это использует стандартную формулу hlookup с массивом поиска, определяемым количеством записей.

Если вы знаете, что между ними не будет пустых ячеек, самый быстрый способ-это.

=INDIRECT("O"&(COUNT(O:O,"<>""")))

Он просто подсчитывает непустые ячейки и ссылается на соответствующую ячейку.

его можно использовать для специфического ряда также.

=INDIRECT("O"&(COUNT(O4:O34,"<>""")+3))

это возвращает последнюю непустую ячейку в диапазоне O4: O34.

для Microsoft office 2013

"последний, но один" из непустой строки:

=OFFSET(Sheet5!$C,COUNTA(Sheet5!$C:$C)-2,0)

"последняя" непустая строка:

=OFFSET(Sheet5!$C,COUNTA(Sheet5!$C:$C)-1,0)

эта формула работала со мной для Office 2010:

=LOOKUP(2;1/(A1: A100""); A1:A100)

A1: первая ячейка A100: обратитесь к последней ячейке в сравнении

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

=MAX(IFERROR(MATCH("*",A:A,-1),0),IFERROR(MATCH(9.99999999999999E+307,A:A,1),0))

первое совпадение найдет последнюю текстовую ячейку, а второе совпадение найдет последнюю числовую ячейку. Функция IFERROR возвращает ноль, если первое совпадение находит все числовые ячейки или если второе совпадение находит весь текст ячейки.

в основном это небольшое изменение смешанного текста и числового решения W5ALIVE.

при тестировании времени это было значительно быстрее, чем эквивалентные варианты поиска.

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

=INDIRECT("A"&MAX(IFERROR(MATCH("*",A:A,-1),0),IFERROR(MATCH(9.99999999999999E+307,A:A,1),0)))

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

=INDEX(MAX((A:A<>"")*(ROW(A:A))),1)

единственное различие заключается в том,что", 1" возвращает первое значение, а", 0" возвращает весь массив значений (все, кроме одного из которых не нужны). Я все еще предпочитаю адресовать ячейку к функции индекса там, другими словами, возвращая значение ячейки с помощью:

=INDIRECT("A"&INDEX(MAX((A:A<>"")*(ROW(A:A))),1))

большой поток!

Если вы не боитесь использовать массивы, то следующая очень простая формула для решения проблемы:

=SUM (IF (A:A"",1,0))

вы должны нажать CTRL + SHIFT + ENTER, потому что это формула массива.

при поиске в столбце (A) используйте :

=INDIRECT("A" & SUMPRODUCT(MAX((A:A<>"")*(ROW(A:A)))))

Если ваш диапазон A1:A10 вы можете использовать:

=INDIRECT("A" & SUMPRODUCT(MAX(($A:$A10<>"")*(ROW($A:$A10)))))

в этой формуле :

SUMPRODUCT(MAX(($A:$A10<>"")*(ROW($A:$A10))))

возвращает последний не пустой номер строки, а косвенный() возвращает значение ячейки.

простой, который работает для меня:

=F7-INDEX(A:A,COUNT(A:A))

хорошо, поэтому у меня была та же проблема, что и у asker, и попробовал оба верхних ответа. Но только получение ошибок формулы. Оказалось, что мне нужно было обменять", " на ";" для того, чтобы формулы работали. Я использую XL 2007.

пример:

=LOOKUP(2;1/(A:A<>"");A:A)

или

=INDEX(A:A;MAX((A:A<>"")*(ROW(A:A))))

для отслеживания версий (добавление буквы v в начало числа), я нашел, что это хорошо работает в Xcelsius (SAP Dashboards)

="v"&MAX(A2:A500)