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

Рубрика: 7. Полезняшки Excel

Если вы часто обновляете рабочий лист, записывая новые данные в столбцы, вам пригодится способ, позволяющий ссылаться на последнее значение в том или ином столбце (обычно именно это значение меняется чаще всего). [1] Например (рис. 1), на листе отслеживается размер трех фондов в столбцах B:D. Обратите внимание: обновление информации происходит не в одно и то же время. Цель — получить сумму самых последних данных по каждому из фондов. Эти значения вычисляются в диапазоне G4:G6.

%d1%80%d0%b8%d1%81-1-%d1%84%d0%be%d1%80%d0%bc%d1%83%d0%bb%d0%b0-%d0%b4%d0%bb%d1%8f-%d0%b2%d0%be%d0%b7%d0%b2%d1%80%d0%b0%d1%89%d0%b5%d0%bd%d0%b8%d1%8f-%d0%bf%d0%be%d1%81%d0%bb%d0%b5%d0%b4%d0%bd%d0%b5

Рис. 1. Формула для возвращения последней непустой ячейки в столбцах B:D на основе подсчета ячеек

Скачать заметку в формате Word или pdf, примеры в формате Excel

В этих формулах используется функция СЧЁТЗ, подсчитывающая количество непустых ячеек в столбце С. Это значение используется как второй аргумент функции ИНДЕКС. Например, в столбце В последнее значение находится в строке 7, а функция ИНДЕКС возвращает седьмое значение из этого столбца.

Приведенные выше формулы работают в большинстве случаев, но не всегда. Если в столбце разбросано несколько пустых ячеек, то определение последней непустой ячейки оказывается более сложной задачей, так как функция СЧЁТЗ не считает пустые ячейки.

Метод с формулой массива. Следующая формула массива возвращает содержимое последней непустой ячейки строк столбца В, даже если в этом столбце есть пустые ячейки (рис. 2): {=ИНДЕКС(B:B;МАКС(СТРОКА(B:B)*(B:B<>"")))}. Для ввода формулы массива нажмите Ctrl+Shift+Enter, а не просто Enter.

%d1%80%d0%b8%d1%81-2-%d1%84%d0%be%d1%80%d0%bc%d1%83%d0%bb%d0%b0-%d0%bc%d0%b0%d1%81%d1%81%d0%b8%d0%b2%d0%b0-%d1%81%d0%bf%d1%80%d0%b0%d0%b2%d0%b8%d1%82%d1%81%d1%8f-%d1%81-%d0%bf%d1%83%d1%81%d1%82

Рис. 2. Формула массива справится с пустыми значениями в диапазоне

В том виде, в каком эта формула приведена выше, вы не сможете использовать ее в том же столбце, в котором она записана. При попытке сделать это возникнет циклическая ссылка. Чтобы использовать формулу в ячейке В1, измените ссылки так, чтобы они начинались со строки 2, а не охватывали целый столбец. Например, для возврата последней непустой ячейки в диапазоне В2:В1000 используйте запись В2:В1000.

Следующая формула массива напоминает предыдущую, но возвращает последнюю непустую ячейку в строке (в данном случае в строке 2): {=ИНДЕКС(2:2;МАКС(СТОЛБЕЦ(2:2)*(2:2<>"")))}.

Метод с применением обычной формулы (не формулой массива). Данная формула возвращает последнюю непустую ячейку в столбце В: =ПРОСМОТР(2;1/(B:B<>"");B:B). Эта формула игнорирует ячейки с ошибками, поэтому, если в последней непустой ячейке содержится ошибка (например, #ДЕЛ/0!), формула возвратит последнюю непустую ячейку, не содержащую ошибку.

Аналогично, следующая формула возвращает последнюю непустую ячейку из строки 2, не содержащую ошибок: =ПРОСМОТР(2;1/(2:2<>"");2:2).

[1] По материалам книги Джон Уокенбах. Excel 2013. Трюки и советы. – СПб.: Питер, 2014. – С. 133, 134.

Комментарии: 3 комментария

Здравствуйте!

Огромное спасибо за вашу работу!

Если не сложно, поясните, пожалуйста, как работает в приведенном примере функция «просмотр». Прочтение описания функции в excel мне не помогло.

Спасибо!

Олег, подробнее см. Продвинутый поиск с помощью ВПР и не только, раздел «Возвращение последнего из найденных значений»

Спасибо еще раз! Нужный мне ответ найден. Интересная формула)


Прокомментировать