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

Кто-нибудь знает формулу, чтобы найти значение последней непустой ячейки в столбце в Microsoft Excel?

Ответ 1

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

Он должен быть введен в массив, это означает, что вы нажимаете Ctrl-Shift-Enter после того, как вы вводите или вставляете его. Ниже приведен столбец A:

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

Ответ 2

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

=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)

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

Ответ 3

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

Ответ 4

Вдохновленный великим лидерством, данным ответом Дуга Гленси, я придумал способ сделать то же самое без необходимости формулы массива. Не спрашивайте меня, почему, но я стараюсь избегать использования формул массива, если это вообще возможно (не по какой-либо конкретной причине, это просто мой стиль).

Вот он:

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

Для поиска последней непустой строки с использованием столбца А в качестве ссылочного столбца

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

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

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

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

Надеюсь, это поможет!

Все самое лучшее!

Ответ 5

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

LOOKUP, VLOOKUP и HLOOKUP имеют проблемы с производительностью и никогда не должны использоваться.
Функции массива имеют много накладных расходов и могут также иметь проблемы с производительностью, поэтому его следует использовать только в качестве последнего средства.
COUNT и COUNTA сталкиваются с проблемами, если данные не смежно не пустые, т.е. у вас есть пробелы, а затем данные снова в соответствующем диапазоне
INDIRECT нестабилен, поэтому его следует использовать только в качестве последнего средства
OFFSET является изменчивым, поэтому его следует использовать только в качестве последнего средства
любые ссылки на последнюю возможную строку или столбец (например, 65536-я строка в Excel 2003) не являются надежными и приводят к дополнительным накладным расходам

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

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

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

Ответ 6

Это работает в 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)).

Ответ 7

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

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

См. этот ответ в качестве примера. Kudos to Brad и barry houdini, которые помогли решить этот вопрос.

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

Официальная страница Microsoft (посмотрите на "Недостатки использования формул массива" ).
Формулы массива могут казаться волшебными, но они также имеют некоторые недостатки:
- Иногда вы можете забыть нажать CTRL + SHIFT + ENTER. Не забудьте нажимать эту комбинацию клавиш всякий раз, когда вы вводите или редактируете формулу массива.
- Другие пользователи могут не понимать ваши формулы. Формулы массива относительно недокументированы, поэтому, если другим людям необходимо изменить свои книги, вы должны либо избегать формул массива, либо убедиться, что эти пользователи понимают, как их изменить.
- В зависимости от скорости обработки и памяти вашего компьютера формулы больших массивов могут замедлять вычисления.
Array Formula Heresy.

Ответ 8

если вы ищете в столбце (A), используйте:

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

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

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

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

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

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

Ответ 9

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

Ответ 10

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

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

Ответ 11

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

= LOOKUP (2,1/(Т4: T369 < > ""), Т4: T369)

@Julian Kroné.. Используя ";" вместо "," НЕ работает! Я думаю, что вы используете Libre Office, а не MS Excel? LOOKUP настолько раздражающе волею, что я использую его только в крайнем случае

Ответ 12

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

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

Ответ 13

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

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

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

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

Ответ 14

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

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

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

EQUIV(0;A1:A10;-1)

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

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

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

Ответ 15

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

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

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

Хронический клобук

Ответ 16

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

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

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

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

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

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

Ответ 17

Для Microsoft Office 2013

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

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

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

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

Ответ 18

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

= ПРОСМОТР (2; 1/(А1: А100 < > ""); А1: А100)

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

Ответ 19

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

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

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

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

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

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

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

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

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

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

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

Отличная тема!

Ответ 20

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

= СУММ (ЕСЛИ (A: <> "", 1,0))

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

Ответ 21

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

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

Ответ 22

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

Пример:

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

или

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

Ответ 23

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

="v"&MAX(A2:A500)