Очень часто при работе с большими(и не очень) таблицами возникает вопрос: как узнать последнюю заполненную ячейку в столбце? Если в случае с VBA все ясно, то формулами это не всегда так…Однако даже это предусмотрели разработчики нашего любимого Excel-я

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

Данная формула вернет Вам номер строки, в которой последней расположено число:

=ПОИСКПОЗ(3E+307;A1:A100)

А эта формула вернет номер строки, в которой последней расположены текстовые данные:

=ПОИСКПОЗ(«яяя»;A1:A100)

Но что же делать, если Вы не знаете точно, какие именно у Вас данне будут в ячейках? Выход есть. Даже два выхода.

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

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

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

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

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