В excel последнее значение в столбце

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

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

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

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

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

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

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

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

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

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

Схематический регистр товаров, выданных с магазина:

Товарный регистр.

Чтобы иметь возможность постоянно наблюдать, какой товар зарегистрирован последним, в отдельную ячейку E1 введем формулу:

Результат выполнения формулы для получения последнего значения:

Последнее значение в столбце B.

Разбор принципа действия формулы для поиска последнего значения в столбце:

Главную роль берет на себя функция =ИНДЕКС(), которая должна возвращать содержимое ячейки таблицы где пересекаются определенная строка и столбец. В качестве первого аргумента функции ИНДЕКС выступает неизменяемая константа, а именно ссылка на целый столбец (B:B). Во втором аргументе находится номер строки с последним заполненным значением столбца B. Чтобы узнать этот номер строки используется функция СЧЁТЗ, которая возвращает количество непустых ячеек в диапазоне. Соответственно это же число равно номеру последней непустой строки в столбце B и используется как второй аргумент для функции ИНДЕКС, которая сразу возвращает последнее значение столбца B в отдельной ячейке E1.

Внимание! Все записи в столбце B должны быть неразрывны (без пустых ячеек до последнего значения).

Стоит отметить что данная формула является динамической. При добавлении новых записей в столбец B результат в ячейке E1 будет автоматически обновляться.

Видео: 3 части серии как найти последнюю ячейку с VBA

Видео лучше всего просматривать в полноэкранном режиме HD

Загрузите файл, содержащий код:

Excel-2016-File-Icon-16x16-3.png Find Last Cell VBA Примеры.xlsm (79.6 KB)

Свойство Range.End

Свойство End объекта Range возвращает объект Range, представляющий ячейку в конце или начале заполненной значениями области исходного диапазона по строке или столбцу в зависимости от указанного направления. Является в VBA Excel программным аналогом сочетания клавиш — Ctrl+стрелка (вверх, вниз, вправо, влево).

Возвращаемая свойством Range.End ячейка в зависимости от расположения и содержания исходной:

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

Способ 1 — простая нумерация

Способ 1 — простая нумерация

Ставим в первую ячейку цифру 1, во вторую — цифру 2. Затем выделяем обе ячейки и тянем вниз, чтобы заполнить всё до конца.

Если строк много, можно воспользоваться быстрым способом. Наводим мышку на правый нижний угол ячейки и делаем двойной клик по крестику — это сразу заполнит весь диапазон до последней строки.

Как это работает. Так как в первую ячейку мы поставили единицу, а во вторую — двойку, Excel счёл это как последовательность. Скорее всего, следующее число будет 3, за ним 4, а потом 5 и так далее.

Ежедневные советы от диджитал-наставника Checkroi прямо в твоем телеграме!Подписывайся на канал
Подписаться

30 функций Excel за 30 дней: ПРОСМОТР (LOOKUP)

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

​(ВПР) и в​ столбца.​ из массива.​​Вчера в марафоне​​Имеется книга excel с​ поиск… И решений​​ вхождения искомого в​​ значение из второго​​ что в русском​​ сделать имеющейся в​ используется функция СЧЁТЗ,​

​ горячих клавиш CTRL​и в столбце​ заполненной ячейки в​​ название месяца из​​2​ допускается не сортировать,​HLOOKUP​result_vector​Функция​30 функций Excel за​

Функция 16: LOOKUP (ПРОСМОТР)

​ рабочими листами 1​​ таких задачек было​​ просматриваемый массив.​ аргумента “Вектор_просмотра”.​ языке нет такого​ стандартном наборе функций​

Функция ПРОСМОТР в Excel

Как можно использовать функцию LOOKUP (ПРОСМОТР)?

​ которая возвращает количество​​ + стрелка в​​С​ списке. Список не​ диапазона E2:E8.​найдено не будет,​ кроме этого могут​

  • ​(ГПР). Если искомое​(вектор_результатов) – диапазон,​
  • ​LOOKUP​ 30 дней​
  • ​ и 2,на листе1​ уйма…​Пояснение​

Синтаксис LOOKUP (ПРОСМОТР)

​Пояснение​​ слова.​​ функцией​ непустых ячеек в​ низ (↓). Но​. При создании Именованного​ должен содержать пропусков​Ранее мы уже решали​ то функция выберет​ попадаться текстовые значения.​ значения отсутствует, то​ состоящий из одной​(ПРОСМОТР) возвращает результат,​

​мы развлекались с​ столбце А есть​

​fallmer​
​: Почему именно “​

  • ​: Почему именно “​​Примечание​ПРОСМОТР()​ диапазоне. Соответственно это​ чаще всего пользователю​
  • ​ диапазона необходимо, чтобы​​ (пустых строк). Решение​ похожую задачу с​ последнюю найденную​=LOOKUP(9.99999999999999E+307,D:D)​
  • ​ функция возвратит максимальное​​ строки или одного​ в зависимости от​ функцией​ данные А1:A50 .​
  • ​: В общем формула​​1​​9E+307​​: Вообще-то достаточно использовать​​.​ же число равно​

​ приходится с последним​ активной ячейкой на​

​ будет работать для​
​ помощью функции​

  • ​1​​=ПРОСМОТР(9,99999999999999E+307;D:D)​ значение, не превышающее​ столбца.​ искомого значения. С​
  • ​REPT​ Как из этого​
    • ​ =ПРОСМОТР(9E+307;C:C), возвращает мне​”? Да просто так​”? Потому что это​ и “​
    • ​1. Для​ номеру последней непустой​ значением столбца выполнять​ листе была любая​ числовых и текстовых​
  • ​VLOOKUP​, и возвратит соответствующее​

Ловушки LOOKUP (ПРОСМОТР)

  • ​В этом примере мы​​ искомое.​​диапазоны аргументов​ ее помощью Вы​(ПОВТОР), создавая диаграммы​ столбца извлечь последнее​​ значение последней заполненой​​ 🙂 С таким​​ максимально возможное число​​яя​текстовых​ строки в столбце​ различные вычислительные операции​ ячейка в столбце​
  • ​ списков.​(ВПР). Сегодня воспользуемся​ значение из столбца​ будем использовать векторную​Массив или вектор, в​lookup_vector​
  • ​ сможете:​ внутри ячейки и​ значение по​ ячейки в столбце​ же успехом можно​​ в​​”, но тогда возникает​

Пример 1: Находим последнее значение в столбце

​значений:​​ B и используется​​ в Excel. Поэтому​С​Рассмотрим диапазон значений, в​ функцией​

​ E.​​ форму​​ котором выполняется поиск,​(просматриваемый_вектор) и​Найти последнее значение в​ используя ее для​мере появления значений​ C.​ использовать число 2​Excel​ мизерная возможность попасть​Code =ПРОСМОТР(“яяя”;A:A)​ как второй аргумент​ лучше его получить​

​.​ который регулярно заносятся​LOOKUP​=LOOKUP(2,1/(D2:D8​LOOKUP​

​ должен быть отсортирован​
​result_vector​

Функция ПРОСМОТР в Excel

Пример 2: Находим последний месяц с отрицательным значением

​ столбце.​ простого подсчета. Сегодня​ в ячейках столбца​​Подскажите пожалста, как​​ или 3 или​. Поэтому функция найти​ на таблицу, в​В английской версии:​ для функции ИНДЕКС,​ в качестве значения​Формулу поместим в ячейки​ новые данные. Пусть​(ПРОСМОТР) в векторной​=ПРОСМОТР(2;1/(D2:D8​(ПРОСМОТР). В столбце​

​ по возрастанию, иначе​(вектор_результатов) должны быть​Найти последний месяц с​​ понедельник, и нам​​ ( на листе1​ сделать, чтобы формула​ 100500, например. Главное​ его может только​​ которой будет такое​​Code =LOOKUP(“яяя”,A:A)​ которая сразу возвращает​​ для отдельной ячейки.​​B3С3​ это будут курсы​ форме, чтобы преобразовать​​Пояснение:​​ D записаны значения​ функция может вернуть​​ одного размера.​​ отрицательным показателем продаж.​

​ в очередной раз​​ столбце А каждая​​ возвращала значение предпоследней​ что бы первый​ в каком-то невероятном​​ слово. Так называются​​Как это работает​ последнее значение столбца​Схематический регистр товаров, выданных​

​. Вообще, формулу из-за​
​ валют на определенную​

Функция ПРОСМОТР в Excel

​ успеваемость учащихся из​​В данной формуле​ продаж, а в​​ неправильный результат.​​Форма массива имеет вот​​Конвертировать успеваемость учащихся из​ пора надеть свои​ ячейка столбца имеет​​ ячейки​​ аргумент функции был​​ случае, в реальной​​ город и река​​: Функция​​ B в отдельной​ с магазина:​ наличия относительной адресации​ дату (см. файл​​ процентов в буквенную​​ вместо аргумента​​ столбце E –​​Если первое значение в​ такой синтаксис:​​ процентов в буквенную​​ шляпы мыслителей.​ свою формулу) .​И если не​ не менее делимого​​ жизни пользователь такими​​ в Кемеровской области.​ПРОСМОТР()​ ячейке E1.​Чтобы иметь возможность постоянно​ можно разместить только​

Пример 3: Преобразовываем успеваемость учащихся из процентов в буквенную систему оценок

​ примера).​ систему оценок. В​lookup_vector​​ названия месяцев. В​​ просматриваемом массиве/векторе больше,​LOOKUP(lookup_value,array)​​ систему оценок.​​В 16-й день марафона​ На лист2​ трудно поясните как​ в выражении​ числами просто не​ В детстве я​​ищет сверху вниз​​Внимание! Все записи в​​ наблюдать, какой товар​​ в строках 1-6​Выведем в ячейки​ отличие от​(просматриваемый_вектор) подставлено выражение​ некоторые месяцы дела​ чем искомое значение,​

​ПРОСМОТР(искомое_значение;массив)​Функция​ мы займёмся изучением​в ячейку А1.​ это действует?​1/Диапазон​ оперирует.​ был в этом​ в указанном столбце​

​ столбце B должны​
​ зарегистрирован последним, в​

Функция ПРОСМОТР в Excel

​ и ниже 25​B3С3​
​VLOOKUP​
​1/(D2:D8, которое образует в​

​ шли не очень​

office-guru.ru>

Параметры

Параметр Описание
Direction Константа из коллекции XlDirection, задающая направление перемещения. Обязательный параметр.

Константы XlDirection:

Константа Значение Направление
xlDown -4121 Вниз
xlToLeft -4159 Влево
xlToRight -4161 Вправо
xlUp -4162 Вверх

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

Вы всегда можете задать вопрос специалисту Excel Tech Community или попросить помощи в сообществе Answers community.

К началу страницы

Нужна дополнительная помощь?

Рейтинг
( 1 оценка, среднее 5 из 5 )
Загрузка ...