Найти последнее вхождение подстановочного значения в списке в Excel

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

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

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

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

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

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

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

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

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

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

Найти последнее появление - Использование функции MAX

Этому способу принадлежит статья Чарли Кида, MVP по Excel.

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

= ИНДЕКС ($ B $ 2: $ B $ 14, СУММПРОИЗВ (МАКС (СТРОКА ($ A $ 2: $ A $ 14) * ($ D $ 3 = $ A $ 2: $ A $ 14)) - 1))

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

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

Найти последнее появление - Использование функции ПРОСМОТР

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

= ПРОСМОТР (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, и в нашем массиве мы получим только единицы или ошибки. Таким образом, он сканирует весь массив и возвращает позицию последней единицы, которая является последним совпадающим значением имени.

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

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

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

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

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

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

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

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

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

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

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

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

'Это код функции, которая находит последнее вхождение значения поиска и возвращает соответствующее значение из указанного столбца' Код, созданный Sumit Bansal (https://trumpexcel.com) Функция LastItemLookup (Lookupvalue As String, LookupRange As Range, ColumnNumber как целое число) Dim i До тех пор, пока i = LookupRange.Columns (1) .Cells.Count To 1 Шаг -1 Если Lookupvalue = LookupRange.Cells (i, 1), то LastItemLookup = LookupRange.Cells (i, ColumnNumber) Выход из функции End If Next i Завершение функции

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

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

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

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