Найти последнее значение в столбце Excel

Обновлено: 21.11.2024

Вы можете задаться вопросом, есть ли способ вернуть последнее (не самое большое) значение в столбце. Например, если есть значения в диапазоне от A1 до A5, вы можете захотеть вернуть значение в A5. Позже, если были добавлены значения в A6-A8, то должно быть возвращено значение в A8.

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

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

В этом случае формула возвращает номер строки любой ячейки в A, которая содержит числовое значение больше нуля. Затем можно использовать следующую формулу для получения последнего значения в столбце A:

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

Если ваш диапазон данных содержит смесь числовых и нечисловых значений (и, возможно, даже несколько пустых ячеек, смешанных в диапазоне), вы можете использовать следующую формулу:

Сначала принцип работы этой формулы может быть непонятен. Часть ISBLANK возвращает массив, содержащий значение True или False для каждой ячейки в столбце A, в зависимости от того, является ли эта ячейка пустой или нет. Это значение True или False (которое на самом деле является 1 или 0) вычитается из 1, так что в итоге вы получаете значения True, равные 0, и значения False, равные 1.

Как вы понимаете, вернуть последнее значение в столбце иногда бывает непросто. Чистый подход состоит в том, чтобы просто разработать собственную функцию VBA, которая возвращает желаемое значение. В этом случае вы можете запрограммировать функцию так, чтобы она возвращала любое значение, а не только числовые значения. Прекрасный пример такой функции доступен на веб-сайте Джона Уокенбаха. Проверьте следующее:

Биография автора

На его счету более 50 научно-популярных книг и множество журнальных статей. Аллен Уайатт является всемирно признанным автором. Он является президентом Sharon Parq Associates, компании, предоставляющей компьютерные и издательские услуги. Узнайте больше об Аллене.

В этой статье демонстрируются формулы, возвращающие последнее значение в заданном диапазоне ячеек или столбце. На изображении выше показана формула в ячейке D3, которая извлекает последнее значение из диапазона ячеек B3:B12.

Диапазон ячеек B3:B12 заполнен значениями и пустыми ячейками в случайном порядке, это показывает, что формула нормально работает с пустыми ячейками.

Оглавление

1. Извлечь последнее значение в заданном столбце

Формула в ячейке D3 позволяет получить последнее значение в столбце B. Она отлично работает с пустыми ячейками в вашем списке.

Формула в ячейке D3:

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

В этой статье демонстрируется формула, которая возвращает последнее совпадающее значение на основе заданного условия. На изображении выше показано […]

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

1.1 Объяснение формулы

Затем он делит 1 на массив, создавая ошибки для всех пустых ячеек.

Функция ПРОСМОТР игнорирует ошибки и пытается найти совпадение. Если каждое совпадение равно 1, а значение поиска равно 2, функция ПРОСМОТР возвращает последнее значение в диапазоне ячеек. Обычно список должен быть отсортирован по возрастанию, чтобы функция ПРОСМОТР работала, однако, поскольку каждое значение равно 1, нет необходимости сортировать список.

Я буду использовать следующую формулу, потому что ее будет легче продемонстрировать:

Шаг 1. Проверьте, не равны ли ячейки в столбце B нулю

Знаки меньше и больше являются логическими операторами, они проверяют, не пусты ли значения ячеек в столбце B. Результатом является массив, содержащий значения TRUE или FALSE.

Шаг 2. Разделите 1 на массив

Шаг 3. Возвращаемое значение

и возвращает XH в ячейке D3.

Находит значение в отсортированном диапазоне ячеек и возвращает значение в той же строке.

1.2 Что насчет ошибок?

Я добавил несколько ошибок в столбец B, см. изображение выше.

Я работаю с Excel 2016, и ошибки в столбце B не представляют проблемы.

2. Вернуть гиперссылку на последнее значение в столбце

Формула в ячейке D3, показанная на изображении выше, создает гиперссылку на последнюю ячейку в столбце B. Нажмите левую кнопку мыши на гиперссылке, чтобы перейти к последнему значению в столбце B.

Формула в ячейке D3:

=ГИПЕРССЫЛКА(MID(ЯЧЕЙКА("имя файла", A1), ПОИСК("[", ЯЧЕЙКА("имя файла", A1)), ДЛСТР(ЯЧЕЙКА("имя файла", A1)))&"!$B $"&ПРОСМОТР(2, 1/(B:B<>""), СТРОКА(B:B)), ПРОСМОТР(2, 1/(B:B<>""), B:B))

2.1 Объяснение формулы

Шаг 1. Получение пути и имени книги

Функция ЯЧЕЙКА получает информацию о форматировании, расположении или содержимом ячейки.

возвращает C:\temp\[Найти последнее значение в listv2.xlsx]Гиперссылка

Шаг 2. Найдите местоположение символа [

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

Функция ПОИСК возвращает число, представляющее позицию символа, в которой найдена определенная текстовая строка, читаемая слева направо.

ПОИСК("[", ЯЧЕЙКА("имя файла", A1))

ПОИСК("[", "C:\temp\[Найти последнее значение в listv2.xlsx] Гиперссылка")

Шаг 3. Извлечение книги и названия листа

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

MID(текст, start_num, num_chars)

СРЕДН(ЯЧЕЙКА("имя файла", A1), ПОИСК("[", ЯЧЕЙКА("имя файла", A1)), ДЛСТР(ЯЧЕЙКА("имя файла", A1)))

СРЕДН(ЯЧЕЙКА("имя файла", A1), 9, ДЛСТР(ЯЧЕЙКА("имя файла", A1)))

Функция ДЛСТР вычисляет количество символов в заданной строке.

СРЕДН(ЯЧЕЙКА("имя файла", A1), 9, ДЛСТР(ЯЧЕЙКА("имя файла", A1)))

MID(ЯЧЕЙКА("имя файла", A1), 9, 49)

"[Найти последнее значение в listv2.xlsx] Гиперссылка"

Шаг 4. Объедините имя книги, имя листа и столбец

Символ амперсанда & позволяет объединять строки в формуле Excel.

СРЕДН(ЯЧЕЙКА("имя файла", A1), ПОИСК("[", ЯЧЕЙКА("имя файла", A1)), ДЛСТР(ЯЧЕЙКА("имя файла", A1)))&"!$B$"< /p>

"[Найти последнее значение в listv2.xlsx]Гиперссылка"&"!$B$"

и возвращает "[Найти последнее значение в listv2.xlsx]Гиперссылка!$B$".

Шаг 5. Вычисление номера строки последнего значения в столбце

Функция СТРОКА позволяет вычислить номер строки последнего значения.

СРЕДН(ЯЧЕЙКА("имя файла", A1), ПОИСК("[", ЯЧЕЙКА("имя файла", A1)), ДЛСТР(ЯЧЕЙКА("имя файла", A1)))&"!$B$"&ПРОСМОТР (2, 1/(B:B<>""), СТРОКА(B:B))

"[Найти последнее значение в listv2.xlsx]Гиперссылка!$B$"&ПРОСМОТР(2, 1/(B:B<>""), ROW(B:B))

"[Найти последнее значение в listv2.xlsx]Гиперссылка!$B$"&11

и возвращает "[Найти последнее значение в listv2.xlsx]Гиперссылка!$B$11"

Шаг 6. Возврат последнего значения

Шаг 7. Создайте гиперссылку

Функция ГИПЕРССЫЛКА позволяет создать в ячейке ссылку, указывающую на что-то еще, например на файл, рабочую книгу, ячейку, диапазон ячеек или веб-страницу.

ГИПЕРССЫЛКА(СРЕДН(ЯЧЕЙКА("имя файла", A1), ПОИСК("[", ЯЧЕЙКА("имя файла", A1)), ДЛСТР(ЯЧЕЙКА("имя файла", A1)))&"!$B$ "&ПРОСМОТР(2, 1/(B:B<>""), СТРОКА(B:B)), ПРОСМОТР(2, 1/(B:B<>""), B:B))

HYPERLINK("[Найти последнее значение в listv2.xlsx]Hyperlink!$B$"&11, "XH")

и возвращает гиперссылку на ячейку B11.

3. Возвращает номер строки последнего значения в столбце

Формула в ячейке D3 возвращает число, представляющее номер строки последнего значения в столбце B.

Формула в ячейке D3:

4. Возвращает смежное значение

В этом наборе данных есть два столбца B и C. Формула в ячейке E3 возвращает соответствующее значение последнего значения в столбце B из столбца C.

Формула в ячейке E3:

Формула возвращает соседнее значение последнего значения. На самом деле, она не обязательно должна быть смежной, вы можете изменить ссылку на ячейку (C:C), если ее начальная и конечная точки совпадают с первой ссылкой на ячейку (B:B).

5.Найдите последнюю непустую ячейку с помощью горячих клавиш

Следующие шаги описывают, как выбрать последнюю непустую ячейку в столбце B с помощью клавиш клавиатуры.

Нажмите левой кнопкой мыши на ячейку A1, чтобы выбрать ее.
Нажмите и удерживайте клавишу CTRL.
Один раз нажмите клавишу со стрелкой вниз. Это приведет вас к последней ячейке в столбце A, если все ячейки в столбце A пусты.
Отпустите все клавиши. Нажмите клавишу со стрелкой вправо, чтобы перейти к последней ячейке в столбце B.
Нажмите и удерживайте клавишу CTRL.
Один раз нажмите клавишу со стрелкой вверх. Это приведет вас к последней непустой ячейке в столбце B. См. изображение выше.

Получить файл Excel

Найти последнее значение в спискеv2.xlsx

Еженедельный блог EMAIL

[newsletter_signup_form Я Оскар и здесь, чтобы помочь вам.

Не стесняйтесь оставлять комментарии и задавать вопросы по Excel.

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

Статьи по теме

Во-первых, позвольте мне объяснить разницу между уникальными значениями и уникальными уникальными значениями, важно, чтобы вы знали разницу […]

В этой статье я продемонстрирую четыре различные формулы, которые позволяют вам искать значение, которое нужно найти […]

В этой статье показана формула, которая выполняет обратный поиск и возвращает соответствующее значение на основе последнего совпадения […]

Содержание Найти ближайшее значение Как ввести формулу массива Найти ближайшее значение - Excel 365 Найти ближайшее […]

Вопрос. Как мне искать данные в таблице перекрестных ссылок. У меня есть строка заголовка (т.е. 24) […]

В этой статье демонстрируется формула, позволяющая выполнять поиск в наборе данных с использованием любого количества условий, однако […]

Чтобы найти значение последней непустой ячейки в строке или столбце, даже если данные могут содержать пустые ячейки, вы можете использовать функцию ПРОСМОТР с операцией массива. Формула в F6:

Результатом является последнее значение в столбце B. Данные в B:B могут содержать пустые ячейки (т. е. пробелы) и не нуждаются в сортировке.

Примечание. Это формула массива. Но поскольку ПРОСМОТР может обрабатывать операции с массивами изначально, формулу не нужно вводить с помощью клавиш Control + Shift + Enter даже в более старых версиях Excel.

В этом примере цель состоит в том, чтобы получить последнее значение в столбце B, даже если данные могут содержать пустые ячейки. Это одна из тех головоломок, которые часто возникают в Excel, потому что ответ не очевиден. Один из способов сделать это — использовать функцию ПРОСМОТР, которая изначально может обрабатывать операции с массивами и всегда предполагает приблизительное совпадение. Это делает формулу удивительно простой и компактной:

Действуя изнутри наружу, мы используем логическое выражение для проверки наличия пустых ячеек в столбце B:

Диапазон B:B представляет собой полную ссылку на каждую ячейку в столбце B. Основное преимущество полной ссылки на столбец заключается в том, что она остается неизменной при удалении или добавлении строк.

Примечание. Excel, как правило, хорошо оценивает формулы, в которых используются полные ссылки на столбцы, но вам следует позаботиться об управлении используемым диапазоном. В этом случае, поскольку мы создаем «виртуальный столбец» из более чем 1 миллиона строк с выражением B:B<>"", используемый диапазон, вероятно, не имеет большого значения, но формула определенно намного медленнее, чем та же формула. с фиксированными диапазонами. Если вы заметили проблемы с производительностью, попробуйте ограничить диапазон. Например: =ПРОСМОТР(2,1/(B1:B100<>""),B1:B100)

Логический оператор <> означает не равно, а "" означает пустую строку, поэтому это выражение означает, что столбец B не пуст. Результатом является массив значений TRUE и FALSE, где TRUE представляет ячейки, которые не пусты, а FALSE представляет ячейки, которые пусты. Этот массив начинается так:

Далее мы делим число 1 на массив. Математическая операция автоматически приводит TRUE к 1, а FALSE к 0, поэтому мы имеем:

Этот массив становится аргументом lookup_array в функции ПРОСМОТР. Обратите внимание, что единицы обозначают непустые ячейки, а ошибки — пустые ячейки.

Искомое_значение задается как число 2. Мы используем 2 в качестве значения поиска, чтобы заставить ПРОСМОТР сканировать до конца данных. ПРОСМОТР автоматически игнорирует ошибки, поэтому ПРОСМОТР просматривает 1 в поисках 2, которые никогда не будут найдены. Когда он достигает конца массива, он "отступает" к последнему 1, что соответствует последней непустой ячейке.

Наконец, ПРОСМОТР возвращает соответствующее значение в result_vector (заданное как B:B), поэтому окончательный результат: 30 июня 2020 г.

Ключом к пониманию этой формулы является признание того, что искомое_значение, равное 2, преднамеренно больше, чем любые значения, которые появятся в искомом_векторе. Когда искомое_значение не может быть найдено, ПРОСМОТР будет соответствовать следующему наименьшему значению, которое не является ошибкой: последнему 1 в массиве. Это работает, потому что LOOKUP предполагает, что значения в lookup_vector сортируются в порядке возрастания, и всегда выполняет приблизительное совпадение. Если функция ПРОСМОТР не может найти совпадение, она будет соответствовать следующему наименьшему значению.

Получить соответствующее значение

Вы можете легко изменить формулу поиска, чтобы она возвращала соответствующее значение. Например, чтобы получить цену, связанную с последним значением в столбце B, используйте формулу F7:

Единственное отличие состоит в том, что аргумент result_vector указан как C:C.

Работа с ошибками

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

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

Чтобы получить последнее числовое значение, вы можете добавить функцию ISNUMBER следующим образом:

Последнее непустое, ненулевое значение

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

Если вы заметили проблемы с производительностью, ограничьте диапазон (например, используйте B1:B100, B1:B1000 вместо B:B).

Позиция последнего значения

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

Мы используем функцию ROW, чтобы передать номера строк для столбца B функции LOOKUP в качестве result_vector, чтобы получить номер строки для последнего совпадения.

Недавно я работал над составлением повестки дня собрания.

У меня был список в Excel, в котором у меня был список людей и даты, когда они выступали в качестве «председателей собрания».

Поскольку в списке были повторы (что означает, что человек был председателем собрания несколько раз), мне также нужно было знать, когда в последний раз человек выступал в качестве «председателя собрания».

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

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

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

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

Но вы находитесь в разделе Formula Hack, и здесь мы делаем волшебство.

В этом уроке я покажу вам три способа сделать это.

Это руководство охватывает:

Найти последнее вхождение — используя функцию MAX

Благодарность этому методу принадлежит статье Чарли Кида, признанного экспертом в области Excel.

Вот формула Excel, которая вернет последнее значение из списка:

Вот как работает эта формула:

Функция MAX используется для поиска номера строки последнего совпадающего имени. Например, если имя — Глен, будет возвращено 11, так как оно находится в 11-й строке.Поскольку наш список начинается со второй строки и далее, из него вычитается 1. Таким образом, позиция последнего появления слова Глен — 10 в нашем списке. используется, чтобы гарантировать, что вам не нужно использовать Control + Shift + Enter, поскольку СУММПРОИЗВ может обрабатывать формулы массива. теперь используется для поиска даты последнего совпадающего имени.

Найти последнее вхождение — с помощью функции ПРОСМОТР

Вот еще одна формула для выполнения той же задачи:

Вот как работает эта формула:

Искомое значение равно 2 (вы поймете, почему... продолжайте читать)
Диапазон поиска: 1/($A$2:$A$14=$D$3). Возвращает 1, когда находит совпадающее имя, и ошибку, если не находит. Таким образом, вы получите массив. Например, если искомое значение — Глен, массив будет .
Третий аргумент ([result_vector]) – это диапазон, из которого выдается результат, в данном случае это даты.

Причина, по которой эта формула работает, заключается в том, что функция ПРОСМОТР использует метод приблизительного соответствия. Это означает, что если он сможет найти точное совпадающее значение, он вернет его, но если нет, он просканирует весь массив до конца и вернет следующее наибольшее значение, которое меньше искомого значения.

В этом случае искомое значение равно 2, и в нашем массиве мы получим только 1 или ошибки. Таким образом, он сканирует весь массив и возвращает позицию последней единицы, которая является последним совпадающим значением имени.

Найти последнее вхождение — с помощью пользовательской функции (VBA)

Позвольте мне также показать вам другой способ сделать это.

Мы можем создать пользовательскую функцию (также называемую пользовательской функцией) с помощью VBA.

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

Я создал простую формулу (во многом похожую на формулу ВПР).

Чтобы создать пользовательскую функцию, вам необходимо иметь код VBA в редакторе VB. Я дам вам код и шаги, чтобы разместить его в редакторе VB через некоторое время, но сначала позвольте мне показать вам, как это работает:

Вот формула, которая даст вам результат:

Формула принимает три аргумента:

Искомое значение (это будет имя в ячейке D3)
Диапазон поиска (это будет диапазон, в котором есть имена и даты — A2:B14)
Номер столбца (это столбец, из которого мы хотим получить результат)

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

Вот код формулы:

Вот шаги для размещения этого кода в редакторе VB:

Теперь формула будет доступна на всех листах рабочей книги.

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

Читайте также: