Как получить последннюю заполненную ячейку формулой?
Очень часто при работе с большими таблицами возникает вопрос: как узнать последнюю заполненную ячейку в столбце? Обычно это необходимо для того, чтобы суммировать или вычислять среднее только в пределах заданной таблицы, без учета пустых строк, т.к. в случае с вычислением среднего пустые строки могут повлиять на расчеты. Так же определить последнюю ячейку формулой бывает необходимо, если в отдельную ячейку в начале таблицы надо выводить последнее записанное в таблицу значение.
По сути способов узнать последнюю заполненную ячейку формулами не так много. Я в этой статье покажу два варианта: в первом формула проще для понимания, но менее универсальна в использовании - она требует точно знать данные какого типа хранятся в столбце: числа или текст, т.к. ориентируется исключительно на тип данных. Вторая формула более универсальна, но может дольше работать.
Формула ниже по сути будет отбирать только числа и вернет номер самой нижней строки, в которой расположено любое число, даже если это нуль:
А эта формула вернет номер строки с последней ячейкой, в которой записан любой текст
Тоже самое и с текстом, только тут мы задаем текст "яяя", который в бинарной сетке будет в самом низу, т.к. буква "я" имеет самый большой числовой код. А три этих буквы подряд дают по сути "самый большой текст".
Но чаще всего как раз заранее неизвестно, какие именно данные будут в ячейках: текстовые или числовые. Т.е. по факту в столбце могут быть абсолютно любые данные. В таких случаях можно применить один из следующих вариантов формулы:
=МАКС(ПОИСКПОЗ({"яяя";3E+307}; A1:A100 ))
=MAX(MATCH({"яяя",3E+307},A1:A100)) =ПРОСМОТР(2;1/( A1:A100 <>"");СТРОКА(A1:A100 ))
=LOOKUP(2,1/(A1:A100<>""),ROW(A1:A100))
- здесь идет сравнение каждого значения в указанном диапазоне и если ячейка не пустая - возвращаетсяA1:A100 <>""ИСТИНА .(TRUE) 1/( - здесь единица делится на полученные значенияA1:A100 <>"")ИСТИНА . Звучит как бред, но. Для Excel ИСТИНА это 1, а ЛОЖЬ - 0. Таким образом мы получаем массив значений(TRUE) 1 и#ДЕЛ/0 . Т.е. максимальное число в массиве у нас - 1. А принцип работы функции ПРОСМОТР(#DIV/0) (LOOKUP) очень похож на ПОИСКПОЗ, только она всегда стремиться найти наибольшее подходящее значение, меньшее или равное искомому. А в качестве искомого мы задаем 2, т.е. оно заведомо больше любого значения в массиве для поиска:=ПРОСМОТР(2;1/( A1:A100 <>"")
Таким образом ПРОСМОТР всегда будет нам возвращать позицию последней заполненной ячейки. Последний аргумент функции ПРОСМОТР - массив, равный по размеру просматриваемому(
Как видно, недостатки есть в любой из приведенных формул, так что выбор в любом случае за Вами и зависеть он будет напрямую от поставленной задачи.
Вот один из примеров, как можно применить определение последней ячейки в реальных формулах. Например, вычисление среднего значения:
Числовые данные начинаются с ячейки
В данном случае мы первой ячейкой указываем
В данном случае можно применить поиск последней ячейки именно с числом, т.к.
Т.е. по шагам формулу можно представить так:
=СРЗНАЧ(
=СРЗНАЧ(
4,5
В приложенном к теме примере записаны все приведенные формулы, а так же пара примеров того, как эти формулы можно использовать в других формулах для определения конца диапазона.
Tips_General_Last_Cell_Formula.xls (38,5 KiB, 3 445 скачиваний)
Также см.:
Как определить последнюю ячейку на листе через VBA?
Что такое формула массива
Статья помогла? Поделись ссылкой с друзьями!

Поиск по меткам
Access apple watch Multex Power Query и Power BI VBA управление кодами Бесплатные надстройки Дата и время Записки ИП Надстройки Печать Политика Конфиденциальности Почта Программы Работа с приложениями Разработка приложений Росстат Тренинги и вебинары Финансовые Форматирование Функции Excel акции MulTEx ссылки статистикаКомментарии, не имеющие отношения к комментируемой статье, могут быть удалены без уведомления и объяснения причин. Если есть вопрос по личной проблеме - добро пожаловать на Форум
Отличная формула! Работает даже с ячейками, содержащими #ДЕЛ/0
Воспользовался для расчета рейтинга.