Вы можете задаться вопросом, есть ли способ вернуть последнее (не самое большое) значение в столбце. Например, если есть значения от A1 до A5, вы можете захотеть вернуть значение из A5. Позже, если значения были добавлены в A6 — A8, то должно быть возвращено значение в A8.

Есть несколько способов найти решение. Первый — использовать следующую формулу:

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

Эта формула возвращает последнее числовое значение в столбце при условии, что значения начинаются с (в данном случае) A1. Этот подход работает, только если все значения в столбце являются числовыми. Если значения не являются числовыми или если есть пустые ячейки, перемешанные со значениями, то необходим другой подход. Один из способов — скопировать следующую формулу в столбец B справа от ячеек, которые могут содержать значения:

=IF(ISNUMBER(A2),IF(A2<>0,ROW(A2),""),"")

В этом случае формула возвращает номер строки любой ячейки в A, которая содержит числовое значение больше нуля. Затем можно использовать следующую формулу для получения последнего значения в столбце A:

=INDEX(A:A,MAX(B:B))

Эта формула работает, потому что она возвращает самый большой номер строки из столбца B, а затем использует его как индекс для возврата соответствующего значения из столбца A.

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

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

Поначалу может быть неочевидно, как работает эта формула. Часть ISBLANK возвращает массив, который содержит значение True или False для каждой ячейки в столбце A, в зависимости от того, пуста эта ячейка или нет. Это значение True или False (которое на самом деле равно 1 или 0) вычитается из 1, так что в результате вы получаете значения True, равные 0, а значения False, равные 1.

Следующим шагом является использование значений 1 или 0 в качестве делимого в 1 / x. Это эффективно «инвертирует» значение, и вы получаете либо 1 (1/1), либо # DIV / 0! ошибка (1/0). Помните, что это массив, который затем используется в качестве параметра функции ПРОСМОТР. В массиве ищется значение 2, которого там не существует. (Помните, что массив содержит только значения 1 или значение ошибки.) Функция ПРОСМОТР фактически игнорирует все значения ошибок в массиве и поэтому возвращает смещение в диапазоне A: A последнего элемента массива, содержащего значение 1. Это соответствует последней непустой ячейке диапазона.

Как вы понимаете, возврат последнего значения в столбце временами может быть немного сложным. Чистый подход — просто разработать свою собственную функцию VBA, которая возвращает желаемое значение. В этом случае вы можете запрограммировать функцию, чтобы возвращать любое значение, а не только числовые значения. Прекрасный пример такой функции доступен на веб-сайте Джона Уокенбаха. Обратите внимание на следующее:

http://spreadsheetpage.com/index.php/tip/determining_the_last_non_empty_cell_in_a_column_or_row/

ExcelTips — ваш источник экономичного обучения Microsoft Excel.

Этот совет (2512) применим к Microsoft Excel 97, 2000, 2002 и 2003. Вы можете найти версию этого совета для ленточного интерфейса Excel (Excel 2007 и новее) здесь:

link: / excelribbon-Retrieving_the_Last_Value_in_a_Column [Получение последнего значения в столбце].