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

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

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

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

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

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

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

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

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

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

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

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

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

Функция =ПОСЛЕДНЕЕВСТОЛБЦЕ(ЯЧЕЙКА) имеет один аргумент.

• ЯЧЕЙКА — ссылка на любую ячейку из столбца в котором необходимо найти последнее заполненное значение.

Пример

Поиск последнего значения в столбце.

Код на VBA

Вы можете самостоятельно внедрить код ниже в Вашу книгу или скачать всю надстройку по ссылке ниже.

Public Function ПОСЛЕДНЕЕВСТОЛБЦЕ(ЯЧЕЙКА As Range) If ЯЧЕЙКА.Parent.Cells(Sheets(1).Rows.Count, ЯЧЕЙКА.Column) <> "" Then _ ПОСЛЕДНЕЕВСТОЛБЦЕ = ЯЧЕЙКА.Parent.Cells(Sheets(1).Rows.Count, ЯЧЕЙКА.Column).Value If ЯЧЕЙКА.Parent.Cells(Sheets(1).Rows.Count, ЯЧЕЙКА.Column) = "" Then _ ПОСЛЕДНЕЕВСТОЛБЦЕ = ЯЧЕЙКА.Parent.Cells(Sheets(1).Rows.Count, ЯЧЕЙКА.Column).End(xlUp).Value End Function

Надстройка
VBA-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

Если Вы скачали или приобрели надстройку PLEX для Microsoft Excel и у Вас есть вопросы или пожелания — Вам сюда.

Plex что бы хотелось ещ. (Zloy Asano) 29.01.2024 15:38:55
Оффтопьте на здоровье
Разъеснение по взлому X. (БМВ) 30.01.2024 11:47:54
Обсуждение функционала, правил и т.д.
Режим Read only при вхо. (БМВ) 25.01.2024 19:29:58
Сейчас на форуме
Всего зарегистрированных пользователей: 130582
Приняло участие в обсуждении: 48257
Всего тем: 152745
Всего сообщений: 1201669
есть новые сообщения
нет новых сообщений

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

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

ООО «Планета Эксел» ИНН 7735603520 ОГРН 1147746834949

ИП Павлов Николай Владимирович ИНН 633015842586 ОГРНИП 310633031600071