Поиск конца данных и управление строками в Excel

Иван Корнев·11.04.2026·⏱4 мин

Чтобы быстро найти последнюю заполненную строку в столбце, используйте формулу =ПОИСКПОЗ(2;1/(A:A<>"")) (для старых версий) или =СТРОКА(ПОСЛЕДНЯЯ_ЯЧЕЙКА(A:A)) через макросы, а для получения следующей пустой строки добавьте к результату единицу. В современных версиях Excel 365 наиболее надежным способом является комбинация функций FILTER и ROWS. Эти методы позволяют автоматически определять границы данных для динамических отчетов и сводных таблиц без ручного выделения диапазонов.

Почему стандартные методы часто ошибаются

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

Частая ошибка: Использование СЧЁТЗ(A:A) для определения номера последней строки. Если в ячейке A50 есть данные, а в A51–A100 пусто, функция вернет число 1 (или количество заполненных), что не равно номеру строки 50. Для нумерации строк нужны функции работы с позициями, а не просто подсчет.

Надежные формулы для поиска последней строки

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

Универсальный метод (работает во всех версиях)

Эта формула игнорирует пустые ячейки внутри столбца и находит позицию последнего заполненного значения. Она требует ввода как формула массива в старых версиях (Ctrl+Shift+Enter), но в Excel 365 работает автоматически.

Для столбца A:

=ПОИСКПОЗ(2;1/(A:A<>""))

Как это работает: Выражение 1/(A:A<>"") создает массив единиц для заполненных ячеек и ошибок для пустых. Функция ПОИСКПОЗ ищет значение 2 (которого нет), поэтому она останавливается на последней единице в списке, возвращая её номер позиции.

Метод для Excel 365 и 2021 (современный)

Если у вас новая версия, удобнее использовать функцию ФИЛЬТР (FILTER). Она чище читается и быстрее считается на больших объемах.

=СТРОКА(ПОСЛЕДНЯЯ_ЯЧЕЙКА(ФИЛЬТР(A:A; A:A<>"")))

Или более простой вариант для получения номера строки:

=МАКС(ЕСЛИ(A:A<>""; СТРОКА(A:A)))

(Вводите как обычную формулу в Excel 365).

Лайфхак: Если нужно найти последнюю строку не во всем столбце, а в конкретном диапазоне (например, A2:A500), замените A:A на $A$2:$A$500. Абсолютные ссылки ускорят пересчет файла.

Как найти следующую пустую строку

Знание номера последней заполненной строки позволяет легко вычислить адрес следующей свободной ячейки, куда можно дописать новые данные.

Найдите номер последней строки (используя формулы выше).
Прибавьте к результату 1.

Пример полной формулы для получения адреса следующей ячейки в столбце A:

=АДРЕС(ПОИСКПОЗ(2;1/(A:A<>"")) + 1; 1)

Эта формула вернет текстовое значение, например, $A$51.

Если вам нужно сразу получить значение из следующей строки (проверка, пуста ли она):

=ИНДЕКС(A:A; ПОИСКПОЗ(2;1/(A:A<>"")) + 1)

Работа с несколькими столбцами одновременно

Часто данные занимают несколько колонок (например, A:C), и они могут заканчиваться на разных строках. Чтобы найти общую границу таблицы (самую нижнюю заполненную ячейку среди всех столбцов), используйте функцию МАКС.

Формула для диапазона A:C:

=МАКС(ЕСЛИ(A:C<>""; СТРОКА(A:C)))

В Excel 365 эта формула вводится просто нажатием Enter. В старых версиях требуется подтверждение Ctrl+Shift+Enter. Она просканирует все три столбца и вернет номер строки самой нижней ячейки, содержащей данные.

Задача	Формула (для столбца A)	Примечание
Номер последней строки	`=ПОИСКПОЗ(2;1/(A:A<>""))`	Игнорирует дырки в данных
Адрес последней ячейки	`=АДРЕС(ПОИСКПОЗ(2;1/(A:A<>"")); 1)`	Возвращает текст "$A$..."
Номер следующей строки	`=ПОИСКПОЗ(2;1/(A:A<>"")) + 1`	Готова для вставки новых данных
Последняя строка (простой счет)	`=СЧЁТЗ(A:A)`	Только если нет пустых ячеек внутри

Частые ошибки

Игнорирование заголовков: Формулы считают данные с первой строки. Если у вас есть заголовок в A1, а данные начинаются с A2, результат формул будет корректным (номер строки), но при использовании ИНДЕКС для выборки значения нужно учитывать смещение.
Форматированные пустые ячейки: Если ячейка выглядит пустой, но в ней стоит пробел или формула, возвращающая "", функции A:A<>"" могут счесть её заполненной. Очищайте такие ячейки полностью (Delete), а не просто стирайте содержимое, если там были формулы.
Целый столбец в старых Excel: Использование ссылок типа A:A в формулах массива в Excel 2010–2016 может сильно замедлить файл. Лучше ограничивать диапазон реально используемыми пределами, например A1:A10000.

FAQ

Как найти последнюю строку, если в столбце есть формулы, возвращающие пустоту? Стандартные проверки <>"" могут видеть такие ячейки как заполненные. Используйте проверку на длину текста: =ПОИСКПОЗ(2;1/(ДЛСТР(A:A)>0)). Это исключит ячейки с формулами вида =ЕСЛИ(...; ""; ...).

Можно ли сделать это без формул? Да, нажмите Ctrl + ↓ (стрелка вниз), находясь в первой ячейке столбца. Курсор перейдет к последней заполненной ячейке перед разрывом. Если разрывов нет — к самому низу листа (строка 1048576). Этот метод неудобен для автоматизации, но быстр для ручной проверки.

Почему формула возвращает ошибку #Н/Д? Ошибка #Н/Д (или #N/A) означает, что в указанном диапазоне полностью отсутствуют данные (все ячейки пустые). Оберните формулу в ЕСЛИОШИБКА(...; 0), чтобы получать ноль вместо ошибки.