Домашняя страница Undo Do Save Карта сайта Обратная связь Поиск по форуму
МИР MS EXCEL - Гость.xls

Вход

Регистрация

Напомнить пароль

 

= Мир MS Excel/Статьи об Excel

МЕНЮ САЙТА
  • 1
  • 2
  • 3

КАТЕГОРИИ РАЗДЕЛА

ОПРОСЫ
Какой версией Excel Вы пользуетесь?
Всего ответов: 57632
Главная » Статьи » Эффективная работа в Excel » Приёмы работы с формулами

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

     Если Вам необходимо в таблицах, которые имеют неодинаковое количество ячеек в строках и/или столбцах, например таких:



находить последние заполненные ячейки и извлекать из них значения, то в Excel Вы, к сожалению, не найдёте функции типа ВЕРНУТЬ.ПОСЛЕДНЮЮ.ЯЧЕЙКУ()

Вот как это сделать имеющейся в стандартном наборе функций функцией ПРОСМОТР().

1. Для текстовых значений:


 

Code
=ПРОСМОТР("яяя";A:A)

В английской версии:

Code
=LOOKUP("яяя",A:A)


Как это работает: Функция ПРОСМОТР() ищет сверху вниз в указанном столбце текст "яяя" и не найдя его, останавливается на последней ячейке в которой есть хоть какой-то текст. Так как мы не указали третий аргумент этой функции "Вектор_результатов", то функция возвращает значение из второго аргумента "Вектор_просмотра".

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

Примечание: Вообще-то достаточно использовать и "яя", но тогда возникает мизерная возможность попасть на таблицу, в которой будет такое слово. Так называются город и река в Кемеровской области. В детстве я был в этом городе и даже купался в этой реке :)

2.  Для числовых значений:

 

Code
=ПРОСМОТР(9E+307;1:1)

В английской версии:

Code
=LOOKUP(9E+307,1:1)


Как это работает: Функция ПРОСМОТР() ищет слева направо в указанной строке число "9E+307" и не найдя его, останавливается на последней ячейке в которой есть хоть какое-то число. Так как мы не указали третий аргумент этой функции "Вектор_результатов", то функция возвращает значение из второго аргумента "Вектор_просмотра".

Пояснение: Почему именно "9E+307"? Потому что это максимально возможное число в Excel. Поэтому функция найти его может только в каком-то невероятном случае, в реальной жизни пользователь такими числами просто не оперирует.

3.  Для смешанных (текстово-числовых) значений:

 

Code
=ПРОСМОТР(1;1/(A:A<>"");A:A)

В английской версии:

Code
=LOOKUP(1;1/(A:A<>"");A:A)


Как это работает: Функция ПРОСМОТР() ищет слева направо в указанной строке число "1" и найдя его, останавливается на последней ячейке в которой есть это число. Так как мы указали третий аргумент этой функции "Вектор_результатов", то функция возвращает значение из него, соответствующее позиции последнего вхождения искомого в просматриваемый массив.

Пояснение: Почему именно "1"? Да просто так :) С таким же успехом можно использовать число 2 или 3 или 100500, например. Главное что бы первый аргумент функции был не менее делимого в выражении 1/Диапазон. Вот пример применения другого числа в первом аргументе, при делимом отличном от единицы:



Категория: Приёмы работы с формулами | Добавил: Serge_007 (14.01.2012)
Просмотров: 115463 | Комментарии: 36 | Теги: последняя ячейка, Поиск, вернуть значение, найти последнюю ячейку, последняя заполненная ячейка, формулы эксель, формулы Excel | Рейтинг: 4.5/12


Всего комментариев: 361 2 »
+1  
1    Shiri   (22.02.2012 10:18) [ Материал]
   I'm not easily ipmresesd but you've done it with that posting.

+2  
2    Формуляр   (26.03.2012 16:28) [ Материал]
   Добавлю свои 5 коп. :
если нужен номер строки удобней использовать
Code
=MATCH("",A:A,-1)

0  
3    Туся   (25.06.2012 13:15) [ Материал]
   Спасибо БОЛЬШОЕ, ОООЧЕНЬ помогло! Как раз то, что искала и так быстро!

0  
4    anger47   (10.07.2013 11:22) [ Материал]
   Спасибо, тоже помогло!

Спам-сообщение скрыто. Показать
0   Спам
5    Kaskad   (06.09.2013 05:44) [ Материал]
   И от меня Спасибо hands

Спам-сообщение скрыто. Показать
0   Спам
6    Николай67   (13.09.2013 13:10) [ Материал]
   Огромное спасибо. Работает супер. То что нужно.

0   Спам
7    Дмитрий   (18.09.2013 11:32) [ Материал]
   Огромное СПАСИБО. Без Вас бы не справился :)

Спам-сообщение скрыто. Показать
0   Спам
8    Kim   (08.10.2013 06:35) [ Материал]
   от себя тоже добавлю СПАСИБИЩЕ не только за формулу, но и за пояснение

Спам-сообщение скрыто. Показать
0   Спам
9    Агдам   (18.02.2014 21:29) [ Материал]
   А как решить проблему когда в последней ячейке оказалось число <0? В этом случае результатом становится пустая ячейка.

Спам-сообщение скрыто. Показать
0   Спам
10    Serge_007   (18.02.2014 22:19) [ Материал]
   Формула вернет любое последнее значение, независимо от того отрицательное оно или положительное. Пустой ячейки не будет

Спам-сообщение скрыто. Показать
0   Спам
11    Агдам   (18.02.2014 22:50) [ Материал]
   Serge 007, на форуме http://www.cyberforum.ru/ms-excel/thread388235.html, вы сказали, что "Функция ПРОСМОТР ищет максимально возможное число в Excel (9E+307) в массиве чисел A2:A10 сверху вниз и останавливается на последнем найденном числе больше 0"
У меня когда когда число <0 то функция выдает пустую ячейку, когда>0, все нормально. Как решить проблему?

Спам-сообщение скрыто. Показать
0   Спам
12    Serge_007   (18.02.2014 22:56) [ Материал]
   Нет никакой проблемы. Что бы убедиться в этом, скачайте файл из статьи и в нем измените последнее значение на отрицательное

Спам-сообщение скрыто. Показать
0   Спам
13    elly9512   (11.03.2014 14:53) [ Материал]
   Подскажите, пожалуйста, если надо выбрать ячейку с датой, то какую комбинацию надо ввести в искомое значение. Пересмотрела много всего, не подходит, выдает #Н/Д. Спасибо.

Спам-сообщение скрыто. Показать
0   Спам
14    Serge_007   (11.03.2014 15:05) [ Материал]
   Для дат и для чисел формула одинакова

Спам-сообщение скрыто. Показать
0   Спам
15    elly9512   (11.03.2014 15:15) [ Материал]
   Спасибо, большое, все получилось! ступила....

Спам-сообщение скрыто. Показать
0   Спам
16    mr_vads   (17.03.2014 18:11) [ Материал]
   Спасибо, прикрутил "поискпоз" к Вашему методу, но если последние значения повторяются - выдает номер строки только первого вхождения (из последних повторов). Следовательно, Ваш метод нашел значение, но не последнее; и если его использовать в комбинации с другими функциями - ошибки неизбежны.
Формуляр предложил отличную формулу =MATCH("",A:A,-1), при этом можно даже использовать =MATCH(,A:A,-1)
=)

Спам-сообщение скрыто. Показать
0   Спам
17    Serge_007   (17.03.2014 18:50) [ Материал]
   mr_vads, мой метод возвращает последнее ЗНАЧЕНИЕ строки или столбца и неважно повторяется оно в них или нет

А метод Формуляра действительно возвращает НОМЕР последней заполненной СТРОКИ, но не ЗНАЧЕНИЕ

Читайте статью внимательнее ;)

Спам-сообщение скрыто. Показать
0   Спам
18    mr_vads   (21.03.2014 00:27) [ Материал]
   может быть, что "поискпоз" сбил с толку. хотя надо проверить ("смещ" например). в любом случае, спасибо!

Спам-сообщение скрыто. Показать
0   Спам
19    mr_vads   (21.03.2014 00:29) [ Материал]
   и кстати, (а может и нет), цветной код трудночитаем

Спам-сообщение скрыто. Показать
0   Спам
20    Serge_007   (21.03.2014 09:35) [ Материал]
   Капча (цветной код) показывается только для незарегистрированных пользователей

Спам-сообщение скрыто. Показать
0   Спам
21    Serkot   (03.04.2014 15:05) [ Материал]
   Добрый день, формула =ПРОСМОТР(9E+307;1:1) прекрасна работала, но решил внести небольшие изменения, в обрабатываемых ячейках поместил не числа, а формулы (суммы из других строк) и все остановилось, вместо чисел стал возвращаться 0. Подскажите как быть, очень хорошо начались решаться проблемы и вот опять стопор.

Спам-сообщение скрыто. Показать
0   Спам
22    Serge_007   (03.04.2014 20:21) [ Материал]
   Если формула
Код
=ПРОСМОТР(9E+307;1:1)
возвращает ноль, значит последнее значение в указанном диапазоне равно нулю. Так что, с точки зрения логики формулы, всё работает правильно.

Если я правильно понял Вашу задачу, то Вам подойдет такая формула:
Код
=ПРОСМОТР(1;1:1/1:1;1:1)

Спам-сообщение скрыто. Показать
0   Спам
23    OKOPACHE   (09.04.2014 13:13) [ Материал]
   Спасибо. То что нужно.

Эта формула вписывает последнюю запись в строке. В столбце (сверху ячейки - в шапке) я записываю дату. И очень нужно еще и вписать дату, когда была сделана эта последняя запись. Как пример: (20_03_2014 Договорились созвониться в мае) Если это реально, будет вообще космос!

Спасибо

Спам-сообщение скрыто. Показать
0   Спам
24    tosha1arx   (19.10.2014 16:01) [ Материал]
   функции =ИНДЕКС(1:1;СЧЁТЗ(1:1)) не надо максимального числа, но и ваша функция =ПРОСМОТР(9E+307;1:1) работает. Спасибо

Спам-сообщение скрыто. Показать
0   Спам
25    buchlotnik   (26.11.2014 16:51) [ Материал]
    tosha1arx ваша формула не учитывает наличия пустых ячеек

1-25 26-36
Добавлять комментарии могут только зарегистрированные пользователи.
[ Регистрация | Вход ]
Яндекс.Метрика Яндекс цитирования
© 2010-2024 · Дизайн: MichaelCH · Хостинг от uCoz · При использовании материалов сайта, ссылка на www.excelworld.ru обязательна!