매크로를 사용하여 열에서 최신 날짜/시간을 찾는 방법

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올바르게 수행하면 위에 표시된 대로 수식이 중괄호로 묶입니다. 수식을 묶는 중괄호가 없으면 배열 수식으로 설정되지 않은 것이므로 올바르게 작동하지 않습니다.

이 작업을 수행하는 더 간단하고 우아한 방법이 있을 수 있지만 빠르고 지저분한 솔루션이 필요한 경우에는 이 방법이 효과적입니다.

더 자세한 설명이 필요하시면 더 자세한 내용을 제공해 드릴 수 있습니다.

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

이 코드는 부서 내에서 시스템을 업데이트하는 마지막 사람의 사용자 이름을 배치합니다.

열 위치와 같은 몇 가지 사항을 가정합니다. 관련 열을 참조하는 데 사용되는 열거가 있으므로 예제와 다른 경우 적절한 열 인덱스를 가리킬 수 있으며 모두 예상대로 작동해야 합니다. 또한 날짜 스탬프가 포함된 열에 간격이 없으며 항상 날짜라고 가정합니다.

전체 코드 세트를 Excel의 단일 모듈에 복사하여 붙여넣으면 제대로 작동합니다.