Кто-нибудь знает формулу, чтобы найти значение последней непустой ячейки в столбце в Microsoft Excel?
Это работает как с текстом, так и с числами, и не заботится о наличии пустых ячеек, т.е. возвращает последнюю непустую ячейку.
Он должен быть введен в массив, это означает, что вы нажимаете Ctrl-Shift-Enter после того, как вы вводите или вставляете его. Ниже приведен столбец A:
=INDEX(A:A,MAX((A:A<>"")*(ROW(A:A))))
Использование следующей простой формулы намного быстрее
=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$1;COUNTA(A:A)-1;0)
Вдохновленный великим лидерством, данным ответом Дуга Гленси, я придумал способ сделать то же самое без необходимости формулы массива. Не спрашивайте меня, почему, но я стараюсь избегать использования формул массива, если это вообще возможно (не по какой-либо конкретной причине, это просто мой стиль).
Вот он:
=SUMPRODUCT(MAX(($A:$A<>"")*(ROW(A:A))))
Для поиска последней непустой строки с использованием столбца А в качестве ссылочного столбца
=SUMPRODUCT(MAX(($1:$1<>"")*(COLUMN(1:1))))
Для нахождения последнего непустого столбца с помощью строки 1 в качестве опорного строки
Это может быть дополнительно использовано в сочетании с функцией индекса для эффективного определения динамических именованных диапазонов, но это что-то для другого поста, поскольку это не связано с непосредственным вопросом, рассматриваемым здесь.
Я тестировал вышеуказанные методы с помощью Excel 2010, как "изначально", так и в "режиме совместимости" (для более старых версий Excel), и они работают. Опять же, с ними вам не нужно делать какие-либо из Ctrl + Shift + Enter. Используя способ, с помощью которого sumproduct работает в Excel, мы можем обойти необходимость переноса операций с массивами, но мы делаем это без формулы массива. Я надеюсь, что кто-то там может оценить красоту, простоту и элегантность этих предлагаемых решений sumproduct столько, сколько я. Тем не менее, я не подтверждаю эффективность работы этих решений. Просто, что они просты, выглядят красиво, помогают по назначению и достаточно гибки, чтобы расширить их использование в других целях:)
Надеюсь, это поможет!
Все самое лучшее!
Я знаю, что этот вопрос старый, но я не удовлетворен предоставленными ответами.
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 имеет самые низкие издержки и является энергонезависимым, поэтому, если вы работаете с большим количеством данных, это лучше всего использовать.
Это работает в 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)).
Альтернативное решение без формул массива, возможно более надежное, чем предыдущий ответ с решением (подсказка к) без формул массива, является
=INDEX(A:A,INDEX(MAX(($A:$A<>"")*(ROW(A:A))),0))
См. этот ответ в качестве примера. Kudos to Brad и barry houdini, которые помогли решить этот вопрос.
Возможные причины предпочтения формулы без массива приведены в:
Официальная страница Microsoft (посмотрите на "Недостатки использования формул массива" ).
Формулы массива могут казаться волшебными, но они также имеют некоторые недостатки:
если вы ищете в столбце (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))))
возвращает номер последней непустой строки, а косвенный() возвращает значение ячейки.
=INDEX(A:A, COUNTA(A:A), 1) взято из здесь
=MATCH("*";A1:A10;-1) для текстовых данных
=MATCH(0;A1:A10;-1) для числовых данных
Ive пробовал все нелетучие версии, но ни одна версия, приведенная выше, не работала. excel 2003/2007update. Конечно, это можно сделать в excel 2003. Не как массив или стандартная формула. Я либо получаю только пустое, 0 или # значение ошибки. Поэтому я прибегаю к неустойчивым методам. Это сработало.
= LOOKUP (2,1/(Т4: T369 < > ""), Т4: T369)
@Julian Kroné.. Используя ";" вместо "," НЕ работает! Я думаю, что вы используете Libre Office, а не MS Excel? LOOKUP настолько раздражающе волею, что я использую его только в крайнем случае
Поместите этот код в модуль VBA. Сохранить. В соответствии с функциями пользователь определит эту функцию.
Function LastNonBlankCell(Range As Excel.Range) As Variant
Application.Volatile
LastNonBlankCell = Range.End(xlDown).Value
End Function
Я использовал HLOOKUP
A1 имеет дату;
A2:A8 имеет прогнозы, сделанные в разное время, я хочу, чтобы последние
=Hlookup(a1,a1:a8,count(a2:a8)+1)
В нем используется стандартная формула hlookup с массивом поиска, определяемым количеством записей.
для текстовых данных:
EQUIV("";A1:A10;-1)
для числовых данных:
EQUIV(0;A1:A10;-1)
Это даст вам относительный индекс последней не пустой ячейки в выбранном диапазоне (здесь A1: A10).
Если вы хотите получить это значение, обратитесь к нему через INDIRECT после создания -textually - абсолютную ссылку на ячейку, например:
INDIRECT("A" & (nb_line_where_your_data_start + EQUIV(...) - 1))
У меня была такая же проблема. Эта формула также работает одинаково хорошо: -
=INDIRECT(CONCATENATE("$G$",(14+(COUNTA($G$14:$G$65535)-1))))
14 - номер строки первой строки в строках, которые вы хотите подсчитать.
Хронический клобук
Если вы знаете, что между ними не должно быть пустых ячеек, это самый быстрый способ.
=INDIRECT("O"&(COUNT(O:O,"<>""")))
Он просто подсчитывает непустые ячейки и ссылается на соответствующую ячейку.
Он также может использоваться для определенного диапазона.
=INDIRECT("O"&(COUNT(O4:O34,"<>""")+3))
Возвращает последнюю непустую ячейку в диапазоне O4: O34.
Для Microsoft Office 2013
"Последнее, но одно" из непустой строки:
=OFFSET(Sheet5!$C$1,COUNTA(Sheet5!$C:$C)-2,0)
"Последняя" непустая строка:
=OFFSET(Sheet5!$C$1,COUNTA(Sheet5!$C:$C)-1,0)
Эта формула работала со мной для офиса 2010:
= ПРОСМОТР (2; 1/(А1: А100 < > ""); А1: А100)
A1: первая ячейка A100: обратитесь к последней ячейке при сравнении
Я думаю, что ответ от 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))
Отличная тема!
Если вы не боитесь использовать массивы, то следующая очень простая формула для решения проблемы:
= СУММ (ЕСЛИ (A: <> "", 1,0))
Вы должны нажать CTRL + SHIFT + ENTER, потому что это формула массива.
Простой, который работает для меня:
=F7-INDEX(A:A,COUNT(A:A))
Хорошо, поэтому у меня была такая же проблема, как и у меня, и попробовал оба лучших ответа. Но только получение ошибок формулы. Оказалось, что мне нужно было обменять "," на ";" для того, чтобы формулы работали. Я использую 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)