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

Microsoft Excel

трюки • приёмы • решения

Возвращение последней непустой ячейки в столбец или строку таблицы Excel

Предположим, что вы часто обновляете таблицу, добавляя новые данные в ее столбцы. Вам, возможно, понадобится способ ссылаться на последнее значение в определенном столбце (последнее введенное значение).

В таблице на рис. 114.1 отслеживаются значения трех фондов в столбцах B:D. Обратите внимание, что информация поступает не в одно и то же время. Цель состоит в том, чтобы получить сумму самых последних данных по каждому фонду. Эти значения рассчитываются в диапазоне G4:G6.

Рис. 114.1. Таблица, из которой необходимо получить значение последней непустой ячейки в столбцах B:D

Формулы в G4, G5 и G6 следующие:
=ИНДЕКС(B:B;СЧЁТЗ(B:B))
=ИНДЕКС(C:C;СЧЁТЗ(C:C))
=ИНДЕКС(D:D;СЧЁТЗ(D:D))

Формулы применяют функции СЧЁТЗ для подсчета количества непустых ячеек в столбце С. Это значение используется в качестве второго аргумента функции ИНДЕКС. Например, в столбце В последнее значение в строке равно 6, СЧЁТЗ возвращает 6, а функция ИНДЕКС возвращает шестое значение в столбце.

Предыдущие формулы работают в большинстве, но не во всех случаях. Если в столбце одна или несколько пустых ячеек разбросаны, то определение последней непустой ячейки — немного более сложная задача, поскольку функция СЧЁТЗ не подсчитывает пустые ячейки.

Следующая формула массива возвращает содержимое последней непустой ячейки в первых 500 строках из столбца С, даже если он включает пустые ячейки: =ИНДЕКС(C1:C500;МАКС(СТОЛБЕЦ(C1:C500)*(C1:C500<>“”))) .

Нажмите Ctrl+Shift+Enter (а не просто Enter), чтобы ввести формулу массива.

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

Следующая формула массива подобна предыдущей, но возвращает последнюю непустую ячейку в строке (в данном случае в строке 1): =ИНДЕКС(1:1;МАКС(СТОЛБЕЦ(1:1)*(1:1<>“”))) . Чтобы использовать эту формулу для различных строк, измените три ссылки на строки 1:1 так, чтобы она соответствовала правильному количеству строк.

Источник: excelexpert.ru

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

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

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

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

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

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

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

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

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

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

  1. латиница прописные (A-Z)
  2. латиница строчные (a-z)
  3. кириллица прописные (А-Я)
  4. кириллица строчные (а-я)
Читайте также:  Разбить текст в ячейке excel по столбцам

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

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

Источник: www.planetaexcel.ru

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

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

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

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

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

Примечание: Чтобы выделить последнюю ячейку в строке или столбце, нажмите клавишу END, а затем нажмите клавишу Стрелка вправо или стрелка вниз.

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

Выполните одно из следующих действий:

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

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

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

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

На вкладке Главная в группе Редактирование щелкните стрелку рядом с кнопкой ” очистить ” , а затем выберите команду Очистить все.

После повторного открытия книги последняя ячейка данных должна находиться в последней ячейке на листе.

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

Вы всегда можете задать вопрос специалисту Excel Tech Community, попросить помощи в сообществе Answers community, а также предложить новую функцию или улучшение на веб-сайте Excel User Voice.

Примечание: Эта страница переведена автоматически, поэтому ее текст может содержать неточности и грамматические ошибки. Для нас важно, чтобы эта статья была вам полезна. Была ли информация полезной? Для удобства также приводим ссылку на оригинал (на английском языке).

Источник: support.office.com

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

Если вы часто обновляете рабочий лист, записывая новые данные в столбцы, вам пригодится способ, позволяющий ссылаться на последнее значение в том или ином столбце (обычно именно это значение меняется чаще всего). [1] Например (рис. 1), на листе отслеживается размер трех фондов в столбцах B:D. Обратите внимание: обновление информации происходит не в одно и то же время. Цель — получить сумму самых последних данных по каждому из фондов. Эти значения вычисляются в диапазоне G4:G6.

Рис. 1. Формула для возвращения последней непустой ячейки в столбцах B:D на основе подсчета ячеек

Скачать заметку в формате Word или pdf, примеры в формате Excel

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

Приведенные выше формулы работают в большинстве случаев, но не всегда. Если в столбце разбросано несколько пустых ячеек, то определение последней непустой ячейки оказывается более сложной задачей, так как функция СЧЁТЗ не считает пустые ячейки.

Читайте также:  Два столбца в один в excel

Метод с формулой массива. Следующая формула массива возвращает содержимое последней непустой ячейки строк столбца В, даже если в этом столбце есть пустые ячейки (рис. 2): <=ИНДЕКС(B:B;МАКС(СТРОКА(B:B)*(B:B<>” ” )))>. Для ввода формулы массива нажмите Ctrl+Shift+Enter, а не просто Enter.

Рис. 2. Формула массива справится с пустыми значениями в диапазоне

В том виде, в каком эта формула приведена выше, вы не сможете использовать ее в том же столбце, в котором она записана. При попытке сделать это возникнет циклическая ссылка. Чтобы использовать формулу в ячейке В1, измените ссылки так, чтобы они начинались со строки 2, а не охватывали целый столбец. Например, для возврата последней непустой ячейки в диапазоне В2:В1000 используйте запись В2:В1000.

Следующая формула массива напоминает предыдущую, но возвращает последнюю непустую ячейку в строке (в данном случае в строке 2): <=ИНДЕКС(2:2;МАКС(СТОЛБЕЦ(2:2)*(2:2<>” ” )))>.

Метод с применением обычной формулы (не формулой массива). Данная формула возвращает последнюю непустую ячейку в столбце В: =ПРОСМОТР(2;1/(B:B<> ” ” );B:B). Эта формула игнорирует ячейки с ошибками, поэтому, если в последней непустой ячейке содержится ошибка (например, #ДЕЛ/0!), формула возвратит последнюю непустую ячейку, не содержащую ошибку.

Аналогично, следующая формула возвращает последнюю непустую ячейку из строки 2, не содержащую ошибок: =ПРОСМОТР(2;1/(2:2<> ” ” );2:2).

[1] По материалам книги Джон Уокенбах. Excel 2013. Трюки и советы. – СПб.: Питер, 2014. – С. 133, 134.

Источник: baguzin.ru

Последняя заполненная ячейка в 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))

Читайте также:  Excel как посчитать сумму столбца

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

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

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

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

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

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

Источник: excel2.ru

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

Информация о сайте

Инструменты и настройки

Excel Windows
и
Excel Macintosh

Вопросы и решения

Работа и общение

Работа форума и сайта

Функции листа Excel

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

  • 1
  • 2
  • 3
  • Приёмы работы с книгами, листами, диапазонами, ячейками [6]
    Приёмы работы с формулами [13]
    Настройки Excel [3]
    Инструменты Excel [4]
    Интеграция Excel с другими приложениями [4]
    Форматирование [1]
    Выпадающие списки [2]
    Примечания [1]
    Сводные таблицы [1]
    Гиперссылки [1]
    Excel и интернет [1]
    Excel для Windows и Excel для Mac OS [2]

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

    Источник: www.excelworld.ru