Как найти последнее вхождение элемента в список с помощью формулы Excel

0 Акции

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

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

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

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

Это потому, что я должен убедиться, что последний председатель не будет переназначен.

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

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

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

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

Содержание:

1 Найдите последнее вхождение — используя функцию МАКС.
2 Найдите последнее вхождение — с помощью функции ПРОСМОТР
3 Найти последнее вхождение — использование пользовательской функции (VBA)
4 О, привет, приятно познакомиться.

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

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

=INDEX($B$2:$B$14,SUMPRODUCT(MAX(行($A$2:$A$14)*($D$3=$A$2:$A$14))-1))

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

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

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

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

Вот еще одна формула, которая выполняет ту же работу:

=LOOKUP(2,1/($A$2:$A$14=$D$3),$B$2:$B$14)

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

Искомое значение равно 2 (вы поймете, почему... продолжайте читать)
Диапазон поиска составляет 1/($A$2:$A$14=$D$3) — он возвращает 1, когда находит совпадающее имя, и ошибку в противном случае.Таким образом, вы получите массив.例如，查找值為 Glen，數組將為 {#DIV/0!;#DIV/0!;1;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/ 0!;#DIV/0!;#DIV/0!;1;#DIV/0!;#DIV/0!;#DIV/0!}。
Третий параметр ([result_vector]) — это диапазон, в котором он дает результат, в данном случае дату.

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

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

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

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

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

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

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

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

связанный вопрос Полное резервное копирование Outlook: экспорт почты в виде файла PST

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

=LastItemLookup($D$3,$A$2:$B$14,2)

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

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

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

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

Функция LastItemLookup(Lookupvalue As String, LookupRange As Range, ColumnNumber As Integer) Dim i As Long For i = LookupRange.Columns(1).Cells.Count To 1 Step -1 If Lookupvalue = LookupRange.Cells(i, 1) Then LastItemLookup = LookupRange.Cells(i, ColumnNumber) Выход из функции End If Next i End Function

Вот шаги, чтобы поместить этот код в редактор VB:

Перейдите на вкладку разработчика.
Щелкните Параметры Visual Basic.Это откроет редактор VB в бэкэнде.
В панели Project Explorer редактора VB щелкните правой кнопкой мыши любой объект в рабочей книге, куда вы хотите вставить код.Если вы не видите Project Explorer, перейдите на вкладку View и щелкните Project Explorer.
Перейдите в «Вставка» и нажмите «Модули».Это вставит объект модуля для вашей книги.
Скопируйте и вставьте код в окно модуля.

связанный вопрос Как перестать синхронизировать Google Фото?

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

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