Использование Excel VBA для поиска последней подходящей строки

Question

У меня есть электронная таблица с датой и столбцом ActionCode. Я использую событие Worksheet_Change для запуска некоторого кода после того, как пользователь вводит код ActionCode. Я хотел бы иметь процедуру, которая (когда пользователь вводит определенный код) найдет самый последний предыдущий экземпляр этого кода в том же столбце, а затем вернет дату из этого столбца. Например:

Date     |ActionCode
11/4/1999| 2
12/5/1999| 3
1/2/2000 | 2
2/3/2001 | 5
3/1/2001 | 2

Когда пользователь вводит код действия "2" 01.03.2001, я хочу, чтобы код возвращал 1/2/2000 а не 11/4/1999 . Я посмотрел на MATCH , но похоже, что он вернется только 11/4/1999 . Я нашел несколько предложений для этого, введя формулы в таблицу, но я не могу понять, как адаптировать их к VBA.

Answer 1

VBA решение:

Вот решение VBA, использующее событие Worksheet_Change, как вы предложили. Это будет читать пользовательский ввод из F1 и помещать вывод в F2. Предполагается, что данные находятся в столбцах A и B. Чтобы изменить эти местоположения, вам необходимо заменить каждый экземпляр каждого из них на желаемые адреса ввода, вывода и адреса данных.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim userInput As Variant, codeTable() As Variant, maxDate As Long
Application.EnableEvents = False
'Make sure worksheet change affected user input cell
If Not Intersect(Range("F1"), Target) Is Nothing Then
    userInput = Range("F1").Value
    If userInput = "" Then
        Range("F2") = ""
    Else
        'Store table data in an array for fast processing
        codeTable = Range("A2", Range("B1").End(xlDown)).Value
        maxDate = 0
        'Find max date for input code
        For i = LBound(codeTable, 1) To UBound(codeTable, 1)
            If codeTable(i, 2) = userInput Then
                maxDate = Application.WorksheetFunction.Max(maxDate, codeTable(i, 1))
            End If
        Next i
        'Print output to sheet
        If maxDate = 0 Then
            Range("F2") = "No records"
        Else
            Range("F2") = Format(maxDate, "m/d/yyyy")
        End If
    End If
End If
Application.EnableEvents = True
End Sub

Формула решения:

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

В нужной выходной ячейке вставьте следующее в строку формул и нажмите Ctrl+Shift+Enter.

=MAX(($B$2:$B$21=F1)*A2:A21)

В этой формуле B2:B21 содержат ваши коды действий, а A2:A21 - связанные даты.

Пример вывода (оба решения):

Answer 2

Вы можете использовать формулу:

=AGGREGATE(14,6,(Dates<J2)*(ActionCode=J3)*Dates,1)

• J2 = Дата, введенная пользователем, или TODAY() если вы в прямом смысле этого слова.
• J3 = введенный пользователем код действия

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

Если даты отсортированы по возрастанию, вы можете использовать более простую формулу:

=LOOKUP(2,1/((Dates<J2)*(ActionCode=J3)),Dates)