Как вычислить дату последнего совпадения текстового значения в Excel

Как-то раз, когда я работал в Excel передо мной стояла задача — вычислить когда последний раз на конференции выступал каждый человек. Мне нужно было сделать это, чтобы при составлении расписания, не допустить повторного выступления одного и того же человека. Такая задача может встретиться вам в разных ситуациях.

Для этого я использовал совокупность функций.

Результат, которого мы добьемся, будет выглядеть так:

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

Итак, начнём!

Используем функцию МАКС

Формула Excel, для нашего примера, будет выглядеть так:

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

Как это работает:

• Функция МАКС находит номер той строки, которая является последней. В конце формулы мы вычитаем один, так как в нашей таблице есть заголовок;
• Функция СУММПРОИЗВ используется только для того, чтобы вы без проблем и лишних кнопок работали с массивом данных;
• А функция ИНДЕКС просто выводит дату той строки, в которой последний раз встречается имя.

Используем функцию ПРОСМОТР

Формула будет выглядеть так, а результат будет точно такой же:

=ПРОСМОТР(2;1/($A$2:$A$14=$D$3);$B$2:$B$14)  

Как это работает:

• Итак, сначала все строки сравниваются с выбранным именем и создается массив данных. Если строка не совпадает — в массив попадает ошибка, а если совпадает, то туда попадает значение «1»;
• Последний аргумент — диапазон ячеек, где содержится то, что нужно вернуть в результате (в нашем случае это даты).

Ну, а функция ПРОСМОТР просто сканирует созданный массив на совпадение и выдает ячейку с датой для той строки, в которой имя встречается последний раз.

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

С помощью Visual Basic

Ну и напоследок, рассмотрим как сделать ту же задачу через Visual Basic.

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

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

Вот она:

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

3 аргумента в функции:

• Первый — текстовое значение, которое мы ищем;
• Второй — диапазон ячеек, среди которых выполняется поиск;
• Третий — диапазон ячеек, значение которых будет возвращаться при совпадении.

Код Visual Basic:

Function 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) Exit Function End If Next i End Function

Но для начала нам нужно добавить эту функцию в Excel

Как это сделать?

Пошаговая инструкция:

• Щелкните на «Разработчик»;

• Откройте Visual Basic;

• Правой кнопкой мышки на любой лист -> «Insert» -> «Module»;

• И просто поместите код в открывшееся окошко.

Готово!

Однако не забудьте, что при использовании макросов Visual Basic нужно сохранять вашу табличку с соответствующим расширением (.XLSM).