Как получить последннюю заполненную ячейку формулой?

Очень часто при работе с большими таблицами возникает вопрос: как узнать последнюю заполненную ячейку в столбце? Обычно это необходимо для того, чтобы суммировать или вычислять среднее только в пределах заданной таблицы, без учета пустых строк, т.к. в случае с вычислением среднего пустые строки могут повлиять на расчеты. По сути способов не так много. Я в этой статье покажу два варианта: в первом формула проще для понимания, но менее универсальна в использовании - она требует точно знать данные какого типа хранятся в столбце: числа или текст, т.к. ориентируется исключительно на тип данных. Вторая формула более универсальна, но может дольше работать.
Формула ниже по сути будет отбирать только числа и вернет номер самой нижней строки, в которой расположено любое число, даже если это нуль:
=ПОИСКПОЗ(3E+307;A1:A100)
=MATCH(3E+307,A1:A100)
А эта формула вернет номер строки с последней ячейкой, в которой записан любой текст
=ПОИСКПОЗ("яяя";A1:A100)
=MATCH("яяя",A1:A100)
Принцип работы этих формул основан на последнем аргументе функции ПОИСКПОЗ - интервальный просмотр. Если его не указывать, то принимается значения по умолчанию для этого аргумента. По умолчанию он равен 1, что означает искать наибольшее значение, которое меньше или равно искомому. Для "правильной" работы с этим параметром справка Excel рекомендует отсортировать по возрастанию массив значений, в которых осуществляется поиск искомого значения. Но в нашем случае сортировка как раз не нужна. Происходит следующее: в случае с числом мы задает максимально возможное число(3E+307), которого заведомо в искомых значениях быть не может. ПОИСКПОЗ сверяет каждое значение с этим числом. Определяет, что значение в массиве меньше искомого(но не равно ему!) и запоминает его позицию. Но т.к. ПОИСКПОЗ стремится найти самый подходящий вариант - то просматривает значения дальше, предполагая, что массив отсортирован по возрастанию и дальше пойдут значения ЕЩЕ БОЛЬШЕ предыдущего и там возможно есть значение, равное искомому. Но наш массив не отсортирован и значения там расположены абы как. Да и значения там все меньше указанного. В результате ПОИСКПОЗ доходит до последнего числа в указанном массиве и возвращает именно его позицию, т.к. дальше искать нечего и ПОИСКПОЗ считает, что это максимально подходящее число. Опять же потому, что считает, что значения у нас отсортированы.
Тоже самое и с текстом, только тут мы задаем текст "яяя", который в бинарной сетке будет в самом нижу, т.к. буква "я" имеет самый большой числовой код. А три этих буквы подряд дают по сути "самый большой текст".

Но чаще всего как раз заранее неизвестно, какие именно данные будут в ячейках: текстовые или числовые. Т.е. по факту в столбце могут быть абсолютно любые данные. В таких случаях можно применить один из следующих вариантов формулы:

1. =МАКС(ПОИСКПОЗ({"яяя";3E+307};A1:A100))
   =MAX(MATCH({"яяя",3E+307},A1:A100))
2. =ПРОСМОТР(2;1/(A1:A100<>"");СТРОКА(A1:A100))
   =LOOKUP(2,1/(A1:A100<>""),ROW(A1:A100))

Первая формула вводится как формула массива (ввод формулы в ячейку завершается нажатием не просто Enter, а сочетанием клавиш Ctrl+Shift+Enter). Но есть и еще один недостаток: если в столбце нет какого-либо типа данных - формула вернет #Н/Д. Обхитрить можно, если захватить в расчет заголовок, в котором будет текст или число, в зависимости от того, какие данные предположительно могут отсутствовать. Или сделать двойной заголовок - в одном число, в другом текст. Основной принцип работы ПОИСКПОЗ в данном случае описан выше. Могу лишь добавить, что ввод её как формулу массива заставляет формулу искать как позицию самого дальнего числа, так и позицию самого дальнего текста. А функция МАКС(MAX) отбирает из найденных двух позиций максимальное значение.

Вторая формула вводится в ячейку обычным методом и вроде как не имеет никаких подводных камней. Кроме одного: не стоит указывать в качестве диапазона ВЕСЬ СТОЛБЕЦ с данными - формула может очень долго пересчитываться. Особенно это сказывается в файлах версии 2007 Excel, где строк больше миллиона. Предыдущие формулы лишены этого недостатка. Хотя я в любом случае советовал бы указывать явно диапазон "с запасом".
Принцип её работы похож на ПОИСКПОЗ с небольшими дополнениями:

• A1:A100<>"" - здесь идет сравнение каждого значения в указанном диапазоне и если ячейка не пустая - возвращается ИСТИНА(TRUE).
• 1/(A1:A100<>"") - здесь единица делится на полученные значения ИСТИНА(TRUE). Звучит как бред, но. Для Excel ИСТИНА это 1, а ЛОЖЬ - 0. Таким образом мы получаем массив значений 1 и #ДЕЛ/0(#DIV/0). Т.е. максимальное число в массиве у нас - 1. А принцип работы функции ПРОСМОТР(LOOKUP) очень похож на ПОИСКПОЗ, только она всегда стремиться найти наибольшее подходящее значение, меньшее или равное искомому. А в качестве искомого мы задаем 2, т.е. оно заведомо больше любого значения в массиве для поиска: =ПРОСМОТР(2;1/(A1:A100<>"")

Таким образом ПРОСМОТР всегда будет нам возвращать позицию последней заполненной ячейки. Последний аргумент функции ПРОСМОТР - массив, равный по размеру просматриваемому(A1:A100), из которого будет возвращено значение. Мы задаем в качестве этого массива значений для возврата массив номеров строк: СТРОКА(A1:A100). Т.е. если в массиве A1:A100 последнее значение будет в ячейке A9, то ПРОСМОТР вернет значение для СТРОКА(A9).

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

Вот один из примеров, как можно применить определение последней ячейки в реальных формулах. Например, вычисление среднего значения:
=СРЗНАЧ(A2:ИНДЕКС(A1:A100;ПОИСКПОЗ(9E+307;A1:A100)))
Числовые данные начинаются с ячейки A2. В A1 заголовок, а где заканчиваются данные неизвестно - они постоянно изменяются: удаляются, дополняются.
В данном случае мы первой ячейкой указываем A2 - начало числовых данных. А вот далее уже идет вычисление последней ячейки:
ПОИСКПОЗ(9E+307;A1:A100)
В данном случае можно применить поиск последней ячейки именно с числом, т.к. СРЗНАЧ(AVERAGE) в любом случае игнорирует текст и лишние ячейки нам ни к чему. ПОИСКПОЗ(MATCH) возвращает номер последней ячейки в диапазоне A1:A100. Но чтобы получить именно ссылку на эту ячейку, а не просто её строку мы используем ИНДЕКС(INDEX):
ИНДЕКС(A1:A100;ПОИСКПОЗ(9E+307;A1:A100))
Т.е. по шагам формулу можно представить так:
=СРЗНАЧ(A2:ИНДЕКС(A1:A100;ПОИСКПОЗ(9E+307;A1:A100))) =>
=СРЗНАЧ(A2:ИНДЕКС(A1:A100;9)) =>
=СРЗНАЧ(A2:A9) =>
4,5

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

Скачать пример

  Tips_General_Last_Cell_Formula.xls (38,5 KiB, 2 246 скачиваний)