Как перейти на последнюю заполненную строку в excel
Перейти к содержимому

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

  • автор:

Последняя заполненная ячейка в EXCEL

Рассмотрим диапазон значений, в который регулярно заносятся новые данные.

Диапазон без пропусков и начиная с первой строки

В случае, если в столбце значения вводятся, начиная с первой строки и без пропусков, то определить номер строки последней заполненной ячейки можно формулой: =СЧЁТЗ(A:A))

Формула работает для числовых и текстовых диапазонов (см. Файл примера )

Значение из последней заполненной ячейки в столбце выведем с помощью функции ИНДЕКС() : =ИНДЕКС(A:A;СЧЁТЗ(A:A))

Ссылки на целые столбцы и строки достаточно ресурсоемки и могут замедлить пересчет листа. Если есть уверенность, что при вводе значений пользователь не выйдет за границы определенного диапазона, то лучше указать ссылку на диапазон, а не на столбец. В этом случае формула будет выглядеть так: =ИНДЕКС(A1:A20;СЧЁТЗ(A1:A20))

Диапазон без пропусков в любом месте листа

Если список, в который вводятся значения расположен в диапазоне E8:E30 (т.е. не начинается с первой строки), то формулу для определения номера строки последней заполненной ячейки можно записать следующим образом: =СЧЁТЗ(E9:E30)+СТРОКА(E8)

Формула СТРОКА(E8) возвращает номер строки заголовка списка. Значение из последней заполненной ячейки списка выведем с помощью функции ИНДЕКС() : =ИНДЕКС(E9:E30;СЧЁТЗ(E9:E30))

Диапазон с пропусками (числа)

В случае наличия пропусков (пустых строк) в столбце, функция СЧЕТЗ() будет возвращать неправильный (уменьшенный) номер строки: оно и понятно, ведь эта функция подсчитывает только значения и не учитывает пустые ячейки.

Если диапазон заполняется числовыми значениями, то для определения номера строки последней заполненной ячейки можно использовать формулу =ПОИСКПОЗ(1E+306;A:A;1) . Пустые ячейки и текстовые значения игнорируются.

Так как в качестве просматриваемого массива указан целый столбец ( A:A ), то функция ПОИСКПОЗ() вернет номер последней заполненной строки. Функция ПОИСКПОЗ() (с третьим параметром =1) находит позицию наибольшего значения, которое меньше или равно значению первого аргумента (1E+306). Правда, для этого требуется, чтобы массив был отсортирован по возрастанию. Если он не отсортирован, то эта функция возвращает позицию последней заполненной строки столбца, т.е. то, что нам нужно.

Чтобы вернуть значение в последней заполненной ячейке списка, расположенного в диапазоне A2:A20 , можно использовать формулу: =ИНДЕКС(A2:A20;ПОИСКПОЗ(1E+306;A2:A20;1))

Диапазон с пропусками (текст)

В случае необходимости определения номера строки последнего текстового значения (также при наличии пропусков), формулу нужно переделать: =ПОИСКПОЗ(«*»;$A:$A;-1)

Пустые ячейки, числа и текстовое значение Пустой текст («») игнорируются.

Диапазон с пропусками (текст и числа)

Если столбец содержит и текстовые и числовые значения , то для определения номера строки последней заполненной ячейки можно предложить универсальное решение: =МАКС(ЕСЛИОШИБКА(ПОИСКПОЗ(«*»;$A:$A;-1);0); ЕСЛИОШИБКА(ПОИСКПОЗ(1E+306;$A:$A;1);0))

Функция ЕСЛИОШИБКА() нужна для подавления ошибки возникающей, если столбец A содержит только текстовые или только числовые значения.

Другим универсальным решением является формула массива : =МАКС(СТРОКА(A1:A20)*(A1:A20<>«»))

После ввода формулы массива нужно нажать CTRL + SHIFT + ENTER . Предполагается, что значения вводятся в диапазон A1:A20 . Лучше задать фиксированный диапазон для поиска, т.к. использование в формулах массива ссылок на целые строки или столбцы является достаточно ресурсоемкой задачей.

Значение из последней заполненной ячейки, в этом случае, выведем с помощью функции ДВССЫЛ() : =ДВССЫЛ(«A»&МАКС(СТРОКА(A1:A20)*(A1:A20<>«»)))

Как обычно, после ввода формулы массива нужно нажать CTRL + SHIFT + ENTER вместо ENTER .

СОВЕТ: Как видно, наличие пропусков в диапазоне существенно усложняет подсчет. Поэтому имеет смысл при заполнении и проектировании таблиц придерживаться правил приведенных в статье Советы по построению таблиц .

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

Argument ‘Topic id’ is null or empty

Сейчас на форуме

© Николай Павлов, Planetaexcel, 2006-2023
info@planetaexcel.ru

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

ООО «Планета Эксел»
ИНН 7735603520
ОГРН 1147746834949
ИП Павлов Николай Владимирович
ИНН 633015842586
ОГРНИП 310633031600071

Поиск и сброс последней ячейки на листе

При сохранении книги Excel сохраняет только часть каждого листа, которая содержит данные или форматирование. Пустые ячейки могут содержать форматирование, которое приводит к тому, что последняя ячейка в строке или столбце выходит за пределы диапазона ячеек, содержащих данные. Это приводит к тому, что размер файла книги будет больше, чем необходимо, и может привести к большему объему печатных страниц при печати листа или книги.

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

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

  • Чтобы найти последнюю ячейку, содержащую данные или форматирование, щелкните в любом месте листа и нажмите клавиши CTRL+END.

Примечание: Чтобы выделить самую последнюю ячейку в строке или столбце, нажмите клавишу END, а затем нажмите клавишу СТРЕЛКА ВПРАВО или СТРЕЛКА ВНИЗ.

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

  1. Выполните одно из следующих действий:
  2. Чтобы выделить все столбцы справа от последнего столбца, содержащего данные, щелкните заголовок первого столбца, удерживая нажатой клавишу CTRL, а затем щелкните заголовки столбцов, которые нужно выбрать.

Совет: Можно также щелкнуть заголовок первого столбца, а затем нажать клавиши CTRL+SHIFT+END.
Совет: Можно также щелкнуть заголовок первой строки, а затем нажать клавиши CTRL+SHIFT+END.

Дополнительные сведения

Вы всегда можете задать вопрос эксперту в Excel Tech Community или получить поддержку в сообществах.

покупка

Как быстро перейти к концу столбца или строки в Excel?

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

Перейти к последнему столбцу или строке в используемом диапазоне с помощью Kutools for Excel

Перейти к концу столбца или строки в активном диапазоне с помощью ярлыков

Щелкните любые данные столбца, который вы хотите переместить в конец, и удерживайте Ctrl нажмите и нажмите док стрелка внизКлавиша позволяет быстро перейти к концу столбца в диапазоне данных.
док перейти в конец 1

документ стрелка вправо

Если вы хотите перейти в конец строки в активном диапазоне данных, удерживайте Ctrl нажмите и нажмите .

док перейти в конец 2

Функции: Если между данными есть пустая строка или столбец, они перемещаются только к последним данным над первой пустой строкой или столбцом. Смотрите скриншот:

Перейти к последнему столбцу или строке в используемом диапазоне с помощью Kutools for Excel

В некоторых случаях вы хотите перейти к последнему столбцу, строке или ячейке в используемом диапазоне вместо последних данных, используя Kutools for ExcelАвтора Выбрать последнюю ячейку утилита может вам помочь.

После бесплатная установка Kutools for Excel, сделайте следующее:

док перейти в конец 3

Включите лист, который вы хотите разместить в последнем столбце или строке в используемом диапазоне, щелкните Кутулс > Выберите > Выбрать последнюю ячейку > Выбрать последнюю ячейку/Последняя строка/Последний столбец. Смотрите скриншот:

Выбрать последнюю ячейку Последняя строка Последний столбец
док перейти в конец 4 док перейти в конец 5 док перейти в конец 6

Добавить комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *