Как найти последнюю дату/время в столбце с помощью макроса

Question

У меня есть данные, сгенерированные системой, которые выглядят как таблица ниже. В последнем столбце мне нужно отобразить пользователя, который был последним, кто обновлял систему из каждого отдела.

Update Time     User    Department  Last update  
-------------------------------------------------------
1/19/12 7:26    John    A
1/19/12 6:26    Yen     A
1/18/12 9:47    Jefta   B
1/18/12 9:47    Jefta   B
1/18/12 9:47    John    A

Answer 1

ЕСЛИ я правильно понял, о чем вы спрашиваете, и это в Excel, вы можете использовать «формулу массива».

Например, если ваш набор данных находится на рабочем листе в диапазоне A1:C5, вы можете использовать это в столбце «D»:

{=INDIRECT("B" & MATCH(MAX(IF(C$1:C$5=C1,(A$1:A$5),)),A$1:A$5,0))}

Эта формула динамически создаст ссылку на ячейку, которая будет указывать на требуемое имя пользователя, возвращая номер строки, в которой находится максимальная дата, но только для тех строк, в которых значение отдела совпадает со значением этой строки в столбце «C» (столбец отдела), и добавит его к текстовому символу «B» (столбец, содержащий имена пользователей), тем самым создавая полную ссылку на ячейку «буква+цифра».

Обратите внимание, что это должна быть функция массива, что означает, что вы должны удерживать Ctrl+ Shift+ Enterпри выходе из ячейки после ввода формулы вместо того, чтобы просто нажать Enter, как это обычно делается. Если вы сделаете все правильно, формула будет заключена в фигурные скобки, как показано выше. Если нет фигурных скобок, оборачивающих формулу, то она не настроена как формула массива и не будет работать правильно.

Возможно, есть более простой или элегантный способ сделать это, но если вам просто нужно быстрое и грубое решение, то этот подойдет.

Если вам нужны дополнительные объяснения, я могу предоставить больше подробностей.

Answer 2

Вернулся и увидел, что никто еще не опубликовал решение VBA. Решил, что стоит его выложить.

'indexes of the values stored as an array in the collection object
  Private Const USERNAME As Integer = 0
  Private Const DATETIME As Integer = 1


'references to where the data is or should be in the workbook
Public Enum DataColumns
    DateTimeStamp = 1
    UName = 2
    Department = 3
    LastUpdater = 4     'The information we will be adding!
End Enum


Sub Main()
    Dim lastUserByDept As Collection
    Set lastUserByDept = GetLastUpdater(2)
    AppendLastUserName 2, lastUserByDept
End Sub


'//Builds a collection of department entries, and stores
'//the last date along with the user tied to that date
Private Function GetLastUpdater(dataStartRow As Long) As Collection
    Dim currRow As Integer: currRow = dataStartRow


Dim maxDatesByDept As Collection
Set maxDatesByDept = New Collection

Dim deptInfo As Variant
Do While Not IsEmpty(Cells(currRow, DataColumns.DateTimeStamp))
    Dim dept As String: dept = Cells(currRow, DataColumns.Department).Value
    If DeptExists(maxDatesByDept, dept) Then
        If Cells(currRow, DataColumns.DateTimeStamp).Value > maxDatesByDept.Item(dept)(DATETIME) Then
            deptInfo = Array(Cells(currRow, DataColumns.UName).Value, Cells(currRow, DataColumns.DateTimeStamp).Value)
            UpdateExistingEntry maxDatesByDept, deptInfo, Cells(currRow, DataColumns.Department)
        End If
    Else
        deptInfo = Array(Cells(currRow, DataColumns.UName).Value, Cells(currRow, DataColumns.DateTimeStamp).Value)
        maxDatesByDept.Add deptInfo, Cells(currRow, DataColumns.Department).Value
    End If

    currRow = currRow + 1
Loop

Set GetLastUpdater = maxDatesByDept
Set maxDatesByDept = Nothing


End Function


'//Since we are using the VBA collection object, there is no true
'//test for if an element exists; the collection will just throw
'//an error if you ask it for something it cannot find, so just
'//trap the error and return false in that case, as it means no
'//item was found in the list with that dept as it's key
Private Function DeptExists(ByRef deptList As Collection, dept As String) As Boolean
    On Error GoTo handler
    deptList.Item dept
    DeptExists = True
    Exit Function
handler:
    Err.Clear
    DeptExists = False
End Function


'//Updates an existing entry in our collection of dept users.
'//Note: this implementation allows for the trapping of failed attempts
'//but is not used in this version to keep it as straight-forward as
'//possible - If it was important to know when such attempts failed, you
'//could trap on the return value of this method and take the appropriate
'//action.
Private Function UpdateExistingEntry(ByRef deptList As Collection, ByVal deptInfo As Variant, ByVal dept As String) As Boolean
    On Error GoTo handler


If DeptExists(deptList, dept) Then
    deptList.Remove dept
    deptList.Add deptInfo, dept
    UpdateExistingEntry = True
Else
    UpdateExistingEntry = False
End If
Exit Function


handler:
    Err.Clear
    UpdateExistingEntry = False
End Function


'//Uses the created collection of dept, username to add the
'//required username to the column
Private Sub AppendLastUserName(dataStartRow As Long, deptListing As Collection)
    Dim currRow As Integer: currRow = dataStartRow
    Do While Not IsEmpty(Cells(currRow, DataColumns.DateTimeStamp))
        Dim currDept As String: currDept = Cells(currRow, DataColumns.Department)
        Cells(currRow, DataColumns.LastUpdater).Value = deptListing(currDept)(USERNAME)
        currRow = currRow + 1
    Loop
End Sub

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

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

Скопируйте и вставьте весь набор кода в один модуль в Excel, и он будет работать отлично.