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

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


Конечная функция

«// Поскольку мы используем объект коллекции VBA, нет никакого истинного» // теста на наличие элемента; коллекция просто выдаст // ошибку, если вы попросите ее о чем-то, что она не может найти, поэтому просто «// перехватите ошибку и вернете ложь в этом случае, так как это означает, что ни один элемент // не был найден в списке с этим dept как ключ Закрытая функция DeptExists(ByRef deptList As Collection, dept As String) As Boolean On Ошибка GoTo обработчик deptList.Элемент dept DeptExists = True Выход Обработчик функции: Err.Очистить DeptExists = False End Функция

'// Обновляет существующую запись в нашей коллекции пользователей отдела.
«// Примечание: эта реализация позволяет отлавливать неудачные попытки» // но в этой версии не используется для того, чтобы сделать его таким простым, как «// возможный - если было важно знать, когда такие попытки потерпели неудачу, вы» // может перехватить возвращаемое значение этого метода и выполнить соответствующее действие //.
Закрытая функция UpdateExistingEntry(ByRef deptList As Collection, ByVal deptInfo As Variant, ByVal dept As String) В качестве логического значения при ошибке Обработчик GoTo

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


обработчик: Err.Очистить UpdateExistingEntry = False End Function

'// Использует созданную коллекцию dept, username для добавления // необходимого имени пользователя в столбец Private Sub AppendLastUserName(dataStartRow As Long, deptListing As Collection) Dim currRow As Integer: currRow = dataStartRow Делать пока не IsEmpty(Cells(currRow DataColumns.DateTimeStamp)) Dim currDept As String: currDept = Cells(currRow, DataColumns.Отдел) Ячейки (currRow, DataColumns.LastUpdater).Значение = deptListing (currDept) (USERNAME) currRow = currRow + 1 Loop End Sub


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

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

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