Как найти последнее число в экселе

За определенный период времени ведется регистр количества проданного товара в магазине. Необходимо регулярно отслеживать последний выданный из магазина товар. Для этого нужно отобразить последнюю запись в столбце наименования товаров. Чтобы просто посмотреть на последнее значение столбца, достаточно переместить курсор на любую его ячейку и нажать комбинацию горячих клавиш CTRL + стрелка в низ (↓). Но чаще всего пользователю приходится с последним значением столбца выполнять различные вычислительные операции в Excel. Поэтому лучше его получить в качестве значения для отдельной ячейки.

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

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

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

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

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



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

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

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

Поиск последней непустой ячейки в строке или столбце функцией ПРОСМОТР

На практике часто возникает необходимость быстро найти значение последней (крайней) непустой ячейки в строке или столбце таблицы. Предположим, для примера, что у нас есть вот такая таблица с данными продаж по нескольким филиалам:

Задача: найти значение продаж в последнем месяце по каждому филиалу, т.е. для Москвы это будет 78, для Питера — 41 и т.д.

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

Универсальным решением будет использование функции ПРОСМОТР (LOOKUP):

У этой функции хитрая логика:

• Она по очереди (слева-направо) перебирает непустые ячейки в диапазоне (B2:M2) и сравнивает каждую из них с искомым значением (9999999).
• Если значение очередной проверяемой ячейки совпало с искомым, то функция останавливает просмотр и выводит содержимое ячейки.
• Если точного совпадения нет и очередное значение меньше искомого, то функция переходит к следующей ячейке в строке.

Легко сообразить, что если в качестве искомого значения задать достаточно большое число, то функция пройдет по всей строке и, в итоге, выдаст содержимое последней проверенной ячейки. Для компактности, можно указать искомое число в экспоненциальном формате, например 1E+11 (1*10¹¹ или сто миллиардов).

Если в таблице не числа, а текст, то идея остается той же, но «очень большое число» нужно заменить на «очень большой текст»:

Применительно к тексту, понятие «большой» означает код символа. В любом шрифте символы идут в следующем порядке возрастания кодов:

1. латиница прописные (A-Z)
2. латиница строчные (a-z)
3. кириллица прописные (А-Я)
4. кириллица строчные (а-я)

Поэтому строчная «я» оказывается буквой с наибольшим кодом и слово из нескольких подряд «яяяяя» будет, условно, «очень большим словом» — заведомо «большим», чем любое текстовое значение из нашей таблицы.

Вот так. Не совсем очевидное, но красивое и компактное решение. Для поиска последней непустой ячейки в столбцах работает тоже «на ура».

Ссылки по теме

• Поиск и подстановка по нескольким условиям (ВПР по 2 и более критериям)
• Поиск ближайшего похожего текста (max совпадений символов)
• Двумерный поиск в таблице (ВПР 2D)

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

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

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

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

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

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

Как можно использовать функцию 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​

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

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

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

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

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

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

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

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

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

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

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

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

office-guru.ru

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

​ то функция выдаст​lookup_value​LOOKUP​ функции​Алексей матевосов (alexm)​Serge 007​. Вот пример применения​3. Для​

​ городе и даже​ текст «​ быть неразрывны (без​ отдельную ячейку E1​ в соответствующих столбцах​последние котировки валют.​(ВПР) для функции​

Задача

​ оперативной памяти компьютера​​ хорошо, и в​​ сообщение об ошибке​(искомое_значение) – может​(ПРОСМОТР) имеет две​LOOKUP​: В ячейку А1​: Код =ПРОСМОТР(9E+307;A2:A10;A1:A9)​ другого числа в​

Решение

​смешанных​ купался в этой​яяя​ пустых ячеек до​ введем формулу:​ (​

​ Т.к. таблица постоянно​LOOKUP​​ массив, состоящий из​​ ячейках со значениями​

​#N/A​ быть текстом, числом,​ синтаксические формы –​(ПРОСМОТР). Это близкий​ на лист2 вставьте​Функция ПРОСМОТР ищет​ первом аргументе, при​​(текстово-числовых​​ реке :)​​» и не найдя​​ последнего значения).​Результат выполнения формулы для​ВС​ пополняется, то необходимо​(ПРОСМОТР) не важно,​​1​​ продаж появились отрицательные​

​(#Н/Д).​​ логическим значением, именем​​ векторную и массива.​ друг​ одну из двух​ максимально возможное число​ делимом отличном от​)​2. Для​​ его, останавливается на​​Стоит отметить что данная​

​ получения последнего значения:​).​

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

​ чтобы проценты находились​

​и значений ошибки​ числа.​

excel2.ru

Как получить последнее значение в столбце таблицы Excel

​В форме массива функция​ или ссылкой.​ В векторной форме​VLOOKUP​ формул​ в Excel (9E+307)​ единицы:​значений:​числовых​ последней ячейке в​ формула является динамической.​Разбор принципа действия формулы​Альтернативным вариантом является использование​ последнюю заполненную ячейку​ в первом столбце​#DIV/0​Чтобы найти последний месяц​LOOKUP​поиск выполняется в соответствии​ функция ищет значение​(ВПР) и​1. =ИНДЕКС (Лист1!A1:A100;ПОИСКПОЗ​ в массиве чисел​Семён Елизаров​Code =ПРОСМОТР(1;1/(A:A<>»»);A:A)​значений:​

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

​ которой есть хоть​ При добавлении новых​

​ для поиска последнего​ формул массива:​ и вывести ее​ таблицы. Вы можете​(#ДЕЛ/0).​

​ с отрицательным числом,​(ПРОСМОТР) может быть​

​ с размерностью массива:​ в заданном столбце​HLOOKUP​

​ (1;ИНДЕКС (—(Лист1!A1:A100=»»);0);0)-1) «обычная​

​ A2:A10 сверху вниз​: Приветствую!​В английской версии:​Code =ПРОСМОТР(9E+307;1:1)​ какой-то текст. Так​ записей в столбец​ значения в столбце:​=ДВССЫЛ(«B»&МАКС(СТРОКА(B7:B25)*(B7:B25<>»»)))​ значение.​ выбрать абсолютно любой​1​ формула с​ использована для поиска​если в массиве больше​ или строке, а​(ГПР), но работает​ формула»​ и останавливается на​Такая задачка. В​Code =LOOKUP(1;1/(A:A<>»»);A:A)​В английской версии:​ как мы не​ B результат в​​=ДВССЫЛ(«C»&МАКС(СТРОКА(C7:C25)*(C7:C25<>»»)))​Как показано в статье​ столбец.​говорит о том,​LOOKUP​ последнего значения в​ столбцов, чем строк,​

​ в форме массива​ она немного по-другому.​2. =ИНДЕКС (Лист1!A1:A100;ПОИСКПОЗ​ последнем найденном числе​ столбце H присутствует​

​Как это работает​Code =LOOKUP(9E+307,1:1)​ указали третий аргумент​ ячейке E1 будет​Главную роль берет на​Формулы могут быть введены​ Последняя заполненная ячейка​

exceltable.com

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

​В следующем примере баллы​ что в соответствующей​(ПРОСМОТР) будет проверять​ столбце.​ то поиск происходит​

​ – в первой​Итак, давайте изучим теорию​ (1;—(Лист1!A1:A100=»»);0)-1) формула массива.​ больше 0. Потом​​ всегда последнее значение​​: Функция​Как это работает​ этой функции «Вектор_результатов»,​​ автоматически обновляться.​
​ себя функция =ИНДЕКС(),​ в любые столбцы.​ значение последней заполненной​ указаны в столбце​​ ячейке диапазона D2:D8​​ для каждого значения​
​Справка Excel приводит значение​​ в первой строке;​​ строке или столбце​

​ и испытаем на​

​ Завершить ее ввод​

​ из смещённого на​

​ в данном примере​​ПРОСМОТР()​​: Функция​​ то функция возвращает​Если Вам необходимо в​ которая должна возвращать​​За определенный период времени​​ ячейки в списке​ D, они отсортированы​ находится значение меньше​ продаж справедливость утверждения,​9,99999999999999E+307​если количество строк и​ массива.​ практике функцию​ нажатием трех клавиш​ одну ячейку (Вам​ число 2122,48, как​
​ищет слева направо​​ПРОСМОТР()​​ значение из второго​​ таблицах, которые имеют​ содержимое ячейки таблицы​ ведется регистр количества​ можно вывести с​​ в порядке возрастания,​​0​ что оно меньше​как наибольшее число,​ столбцов одинаково или​Векторная форма имеет следующий​LOOKUP​ Ctrl+Shift+Enter​
​ ведь надо значение​​ сделать так чтобы​ в указанной строке​​ищет слева направо​​ аргумента «Вектор_просмотра».​ неодинаковое количество ячеек​ где пересекаются определенная​ проданного товара в​ помощью формулы =ИНДЕКС(Список;СЧЁТЗ(Список))​ а соответствующие им​, а ошибка​0​ которое может быть​ строк больше, то​ синтаксис:​(ПРОСМОТР). Если у​
​Александр​​ предпоследней ячейки?) диапазона​​ последнее значение в​

​ число «​

​ в указанной строке​

​Пояснение​

​ в строках и/или​​ строка и столбец.​​ магазине. Необходимо регулярно​​Имя Список определяет Именованный​ буквы – в​#DIV/0​​(неравенство в формуле).​​ записано в ячейке.​ поиск происходит в​LOOKUP(lookup_value,lookup_vector,result_vector)​ Вас есть дополнительная​: Готовой функции нет.​ A1:A9 возвращает значение.​ столбце, всегда было​1​ число «​: Почему именно «​ столбцах, например таких:​
​ В качестве первого​​ отслеживать последний выданный​​ диапазон с адресом​​ столбце C, слева​(#ДЕЛ/0) – что​ Далее мы делим​​ В нашей формуле​​ первом столбце.​ПРОСМОТР(искомое_значение;просматриваемый_вектор;вектор_результатов)​ информация или примеры​Придётся написать функцию​fallmer​ в ячейке H1?​» и найдя его,​

​9E+307​​яяя​​находить последние заполненные​​ аргумента функции ИНДЕКС​​ из магазина товар.​

​B$7:B$25​

​ от столбца, по​

​ больше или равное​

​1​​ оно будет задано,​​функция возвращает последнее значение​​lookup_value​ по использованию этой​ извлечения на VBA​​: хм, странно, но​​vikttur​ останавливается на последней​» и не найдя​»? Во-первых, потому что​ ячейки и извлекать​ выступает неизменяемая константа,​ Для этого нужно​.​ которому производится поиск.​0​на полученный результат,​ как искомое значение.​
​ из найденной строки/столбца.​​(искомое_значение) – может​​ функции, пожалуйста, делитесь​​ и заставить её​ у меня эта​: Ищем наибольшее значение,​ ячейке в которой​ его, останавливается на​ функция сравнивает при​ из них значения,​ а именно ссылка​ отобразить последнюю запись​При определении адреса этого​​=LOOKUP(C10,D4:D8,C4:C8)​​. В итоге наша​ в итоге имеем​ Предполагается, что такое​В функции​ быть текстом, числом,​

excelworld.ru

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

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

​=ПРОСМОТР(C10;D4:D8;C4:C8)​​ задача сводится к​ либо​ большое число найдено​
​LOOKUP​

​ логическим значением, именем​​Функция​ появлении значений в​ перед предпоследним​ его​ Так как мы​ которой есть хоть​

planetaexcel.ru

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

​ а символ «​​Excel​ (B:B). Во втором​ товаров. Чтобы просто​ адресация, которая позволяет​Урок подготовлен для Вас​
​ тому, чтобы найти​1​ не будет, поэтому​(ПРОСМОТР) нет опции​
​ или ссылкой.​LOOKUP​ ячейках столбца A»​

​fallmer​​=ПРОСМОТР(9E+307;H31:H80)​
​ указали третий аргумент​ какое-то число. Так​я»​Вы, к сожалению,​ аргументе находится номер​ посмотреть на последнее​ использовать этот диапазон​ командой сайта office-guru.ru​ последнюю​, либо сообщение об​ функция возвратит последнее​ для поиска точного​lookup_vector​

​(ПРОСМОТР) возвращает значение​​Abram pupkin​: извиняюсь, все ок,​Z​ этой функции «Вектор_результатов»,​

​ как мы не​​в русском языке​ не найдёте функции​ строки с последним​ значение столбца, достаточно​

CyberForum.ru

Поиск последнего значения в excel

​ для вывода значения​Источник: http://blog.contextures.com/archives/2011/01/17/30-excel-functions-in-30-days-16-lookup/​1​ ошибке​ значение в столбце​ совпадения, которая есть​(просматриваемый_вектор) – диапазон,​ из одной строки,​
​: ПОИСК ПОСЛЕДНЕГО ЗНАЧЕНИЯ​ в формуле накосячил​: Скажу по секрету​ то функция возвращает​ указали третий аргумент​ последний и все​ типа​
​ заполненным значением столбца​

​ переместить курсор на​​ последней заполненной ячейки​Перевел: Антон Андронов​в созданном виртуальном​#DIV/0​
​ D.​ в​ состоящий из одной​
​ одного столбца или​ посмотрите здесь​ когда под себя​ — на форуме​ значение из него,​

​ этой функции «Вектор_результатов»,​​ предыдущие при сравнении​
​ВЕРНУТЬ.ПОСЛЕДНЮЮ.ЯЧЕЙКУ()​ B. Чтобы узнать​ любую его ячейку​ в столбце​Автор: Антон Андронов​ массиве, и на​

​(#ДЕЛ/0).​​В данном примере числа​VLOOKUP​

​ строки или одного​

Поиск последней ячейки — все о данных

Поиск последней использованной строки, столбца или ячейки — одна из наиболее распространенных задач при написании макросов и приложений VBA. Как и все в Excel и VBA, есть много разных способов.

Выбор правильного метода в основном зависит от того, как выглядят ваши данные.

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

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

# 1 — Метод Range.End

Range.End () очень похож на сочетание клавиш Ctrl + Arrow. В VBA вы можете использовать этот метод, чтобы найти последнюю непустую ячейку в той же строке или столбце.

Диапазон: конец примера кода VBA

Sub Last_Cell () ‘Найти последнюю непустую ячейку в той же строке или столбце Dim lRow As Long Dim lCol As Long’ Найти последнюю непустую ячейку в столбце A (1) lRow = Cells (Rows.Count, 1) . End (xlUp) .Row ‘Найти последнюю непустую ячейку в строке 1 lCol = Cells (1, Columns.Count) .End (xlToLeft) .Column MsgBox «Последняя строка:» & lRow & vbNewLine & _ «Последний столбец : «& lCol Fine Sub

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

Оператор Rows.Count возвращает количество всех строк на листе. Поэтому на практике мы указываем последнюю ячейку в столбце A листа (ячейка A1048567) и идем вверх, пока не найдем первую непустую ячейку.

То же самое и с поиском в последнем столбце. Начните с последнего столбца строки, затем двигайтесь влево, пока не будет найдена последняя непустая ячейка в столбце. Columns.Count возвращает общее количество столбцов на листе. Итак, начнем с последнего столбца и будем двигаться влево.

Аргумент метода End указывает, в каком направлении двигаться. Возможные варианты: xlDown, xlUp, xlToLeft, xlToRight.

Pro of Range.End

• Range.End прост в использовании и понимании, поскольку он работает так же, как сочетания клавиш Ctrl + Arrow.
• Его можно использовать для поиска первой пустой ячейки или последней непустой ячейки в той же строке или столбце.

Минусы Range.End

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

Вот справочные статьи для Range.End

• Страница справки MSDN для Range.End
• Руководство MSDN для перечислений xlDirection

# 2 — Метод Range.Find

Метод Range.Find: я предпочитаю этот метод для поиска последней строки, столбца или ячейки. Он самый универсальный, но и самый устрашающий.

У Range.Find много аргументов, но пусть это вас не пугает. Как только вы узнаете, что они делают, вы можете использовать Range.Find для многих вещей в VBA.

Range.Find — это в основном способ программирования меню поиска в Excel. Он делает то же самое, и большинство аргументов Range.Find — это пункты меню «Найти.

Гамма — найти образец кода

Ниже приведен код для поиска последней непустой строки.            

Sub Last_Cell_Find () ‘Находит последнюю непустую ячейку в листе / диапазоне. Dim lRow As Long Dim lCol As Long lRow = Cells.Find (What: = «*», _ After: = Range («A1»), _ LookAt: = xlPart, _ LookIn: = xlFormulas, _ SearchOrder: = xlByRows, _ SearchDirection: = xlPrevious, _ MatchCase: = False) .Row MsgBox «Последняя строка:» & lRow End Sub

Range.Find метод. Пояснения

Метод Find ищет первую непустую ячейку («*»). Звездочка — это подстановочный знак, который выполняет поиск любого текста или числа в ячейке.

Начиная с ячейки A1, вернитесь назад (xlPrevious) и фактически начните искать в последней ячейке рабочего листа. Затем он перемещается справа налево (xlByRows) и прокручивает каждую строку, пока не найдет непустую ячейку. Если обнаружено непустое значение, он останавливается и возвращает номер строки.

Вот подробное объяснение каждой темы.

• Что: = ”*” — звездочка — это подстановочный знак, который соответствует любому тексту или числу в ячейке. По сути, это то же самое, что найти непустую ячейку.
• After: = Range («A1») — запускает поиск после ячейки A1, первой ячейки листа. Это означает, что A1 не будет искать. Поиск начнется после A1, и следующая ячейка, которую он ищет, зависит от SearchOrder и SearchDirection. Этот аргумент можно изменить, чтобы он начинался в другой ячейке, просто помните, что поиск фактически начинается в ячейке, следующей за указанной.
• LookAt: = xlPart — проверяет любую часть текста в ячейке. Другой вариант — xlWhole, который попытается сопоставить все содержимое ячейки.
• LookIn: = xlFormulas — заставляет Find искать формулы, что является важным аргументом. Другой вариант — xlValues, который будет искать только значения. Если у вас есть формулы, которые возвращают пробелы (= IF (A2> 5, «Ok», «»), вы можете рассматривать это как непустую ячейку. Если вы укажете LookIn как xlFormulas, эта формула будет считаться непустой даже если возвращаемое значение пустое.
• SearchOrder: = xlByRows — указывает Find выполнять поиск в каждой строке целиком перед переходом к следующей. Направление поиска слева направо или справа налево зависит от аргумента SearchDirection. Другой вариант — xlByColumns, который используется при поиске последнего столбца.
• SearchDirection: = xlPrevious — указывает направление поиска, XlPrevious означает, что поиск будет выполняться справа налево или снизу вверх. Другой вариант — xlNext, который движется в противоположном направлении.
• MatchCase: = False — указывает Find не учитывать прописные или строчные буквы. Если установлено значение True, это помогает. Этот аргумент не требуется для этого сценария.

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

Pro of Range.Find

• Поиск выполняет поиск во всем диапазоне последней непустой строки или столбца. Он НЕ ограничен одной строкой или столбцом.
• Последняя строка в наборе данных может содержать пробелы, и Range.Find все равно найдет последнюю строку.
• Аргументы можно использовать для поиска в разных направлениях и определенных значений, а не только пустых ячеек.

Минусы Range.Find

• это ужасно. Метод содержит 9 аргументов. Хотя требуется только один из этих аргументов (What), вы должны привыкнуть использовать по крайней мере первые 7 аргументов. В противном случае метод Range.Find по умолчанию будет использовать последние использованные настройки в поле поиска. Это важно. Если вы не предоставите необязательные аргументы для LookAt, LookIn и SearchOrder, метод Find будет использовать последние использованные параметры в поле поиска Excel.
• Для поиска последней ячейки требуются две инструкции. Один для поиска последней строки и один для поиска последнего столбца. Поэтому вам нужно объединить их, чтобы найти последнюю ячейку.

Macro Recorder поможет!

Range.Find по-прежнему остается моим любимым методом поиска последней ячейки из-за его универсальности. Но есть что написать и вспомнить. К счастью, вам это не нужно.

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

1. Запустить средство записи макросов
2. Нажмите Ctrl + F
3. Затем нажмите кнопку «Найти далее»

Код для метода Find со всеми аргументами будет сгенерирован регистратором макросов.

Используйте настраиваемую функцию для метода поиска

Вы также можете использовать пользовательскую функцию (UDF) для метода поиска. Последняя особенность Рона де Брюэна — прекрасный тому пример. Вы можете скопировать эту функцию в любой проект или модуль кода VBA и использовать ее для возврата последней строки, столбца или ячейки.

# 3 — Range.SpecialCells (xlCellTypeLastCell)

Метод SpecialCells делает то же самое, что и нажатие сочетания клавиш Ctrl + End, и выбирает последнюю использованную ячейку на листе.

Пример кода SpecialCells (xlCellTypeLastCell)

Sub Range_SpecialCells_Method () Диапазон MsgBox («A1»). SpecialCells (xlCellTypeLastCell) .Address End Sub

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

Часто нажимайте Ctrl + End на клавиатуре, и вы попадете в ячейку в нижней части листа, которая определенно не используется. Это может произойти по разным причинам. Распространенной причиной является изменение свойств форматирования этой ячейки. Простое изменение размера шрифта или цвета заливки ячейки помечает ее как используемую ячейку.

Профи диапазона

• Вы можете использовать этот метод, чтобы найти «используемые» строки и столбцы в конце листа и удалить их. Сравнение результата Range.SpecialCells с результатом Range.Find для непробелов может помочь вам быстро определить, есть ли на вашем листе неиспользуемые строки или столбцы.
• Удаление неиспользуемых строк / столбцов может уменьшить размер файла и увеличить полосу прокрутки.

Минусы Range.SpecialCells

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

Другие способы найти последнюю ячейку

Что ж, это должно охватывать основы поиска последней использованной или непустой ячейки на листе. Если лист содержит объекты (таблицы, диаграммы, сводные таблицы, срезы и т.д.), Вам может потребоваться использовать другие методы для поиска последней ячейки. Я объясню эти методы в отдельном посте.

Оставьте комментарий ниже, если у вас есть какие-либо вопросы или вы все еще не можете найти последнюю ячейку, я буду рад помочь!

As a starting point: if we know the position of the last number, we can use LEFT to get the string to that point. Suppose that the position is 5:

=LEFT(A1, 5)

But, we don’t know the position of the last number. Now, what if the only valid number was 0, and it only appeared once: then we could use FIND to locate the position of the number:

=LEFT(A1, FIND(0, A1))

But, we have more than one valid number. Suppose that we had all the numbers from 0 through 9, but each number could only appear once — then we could use MAX on a FIND array, to tell us which of the numbers is the last one:

=LEFT(A1, MAX(FIND({0,1,2,3,4,5,6,7,8,9}, A1)))

Unfortunately, FIND will throw a #VALUE! error any number doesn’t appear, which will then make MAX return the same error. So, we need to fix that with IFERROR:

=LEFT(A1, MAX(IFERROR(FIND({0,1,2,3,4,5,6,7,8,9}, A1), 0)))

However, numbers can appear more than once. As such, we need a method to find the last occurrence of a value in a string (since FIND and SEARCH will, by default, return the first occurrence).

The SUBSTITUTE function has 3 mandatory arguments — Initial String, Value to be Replaced, Value to Replace with — and one Optional argument — the occurrence to replace. Normally, this is omitted, so that all occurrences are replaced. But, if we know how many times a character appears in a string, then we can replace just the last instance with a special/uncommon sub-string to search for.

To count how many times a character appears in a String, just start with the length of the String, then subtract the length when you SUBSTITUTE all copies of that character for Nothing:

=LEN(A1) - LEN(SUBSTITUTE(A1, 0, ""))

This means we can now replace the last occurrence of the character with, for example, ">¦<", and then FIND that:

=FIND(">¦<", SUBSTITUTE(A1, 0, ">¦<", LEN(A1) - LEN(SUBSTITUTE(A1, 0, ""))))

Of course, we want to do this for all the numbers from 0 to 9, and take the MAX value (remembering our IFERROR), so we need to put the Array of values back in:

=MAX(IFERROR(FIND(">¦<", SUBSTITUTE(A1, {0,1,2,3,4,5,6,7,8,9}, ">¦<", LEN(A1) - LEN(SUBSTITUTE(A1, {0,1,2,3,4,5,6,7,8,9}, "")))), 0))

Then, we plug that all back into our initial LEFT function:

=LEFT(A1, MAX(IFERROR(FIND(">¦<", SUBSTITUTE(A1, {0,1,2,3,4,5,6,7,8,9}, ">¦<", LEN(A1) - LEN(SUBSTITUTE(A1, {0,1,2,3,4,5,6,7,8,9}, "")))), 0)))