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