Это должен быть VBA. Вы можете использовать Worksheet_Change для запуска только при изменении столбца состояния, поэтому количество строк не должно влиять на производительность.
Один из способов решения этой конкретной проблемы — поместить в столбцы для каждой стадии дату начала и дату окончания (например, AV:AZ для даты начала каждой стадии, BA:BE для даты окончания каждой стадии).
Поэтому, когда вы меняете значение в столбце A, код проверяет новое и старое значение статуса, а затем обновляет даты начала и окончания для этого этапа.
Недостатком этого подхода является то, что если вы неправильно выберете этап, вам придется вручную изменить время в каждом столбце этапа, поскольку каждая строка получает только один шанс обновить даты начала и окончания.
Другой заключается в том, что для получения старого значения при изменении ячейки в столбце A мне пришлось использовать SelectionChange, что означает, что вы должны изменить, какая ячейка выбрана между изменениями статуса (это не будет проблемой при обычном использовании , если только вы не меняете одну и ту же ячейку с ЧПУ на ПРЕСС, на ПЛАНИРОВКУ и т. д., не выбирая отдельную ячейку между изменениями).
Я добавил дополнительную строку для хранения сегодняшней даты, чтобы вы могли изменить диапазоны в VBA в соответствии с вашими потребностями.
Лист изменен и выглядит следующим образом: Макет листа
Формула взята из ячейки AQ3, и ее можно перетащить в AU3 — вы можете легко изменить ее, чтобы подсчитать правильное количество дней в тот момент, когда «сегодня — дата начала + 1» для еще активных этапов — вам может потребоваться настроить это .
Вы можете скрыть дополнительные столбцы, BF1 — это просто сегодняшняя дата (=СЕГОДНЯ()), а столбец BG — только для раскрывающегося списка в столбце A.
Dim oldValue
Public Sub Worksheet_SelectionChange(ByVal Target As Range)
oldValue = Target.Value
End Sub
Private Sub Worksheet_Change(ByVal Target As Range)
Dim KeyCells As Range
Dim startCol As Variant
Dim endCol As Variant
Dim oVal As Variant
Dim nVal As Variant
oVal = oldValue
nVal = Cells(Target.Row, 1).Value
'make no changes if anything other than column A is changed
If Not Target.Column = 1 Then
GoTo continue
End If
'add end date if status is changed to finished
If nVal = "FINISHED" Then
Cells(Target.Row, 57) = Date
GoTo continue
End If
'update end of last stage only if new value is blank
If IsEmpty(nVal) Then
GoTo endDate
End If
'find the column of the stage being changed
startCol = Range("AV2:AZ2").Find(nVal).Column
If Not IsEmpty(oVal) Then
endCol = Range("BA2:BE2").Find(oVal).Column
End If
Set KeyCells = Range("A:A")
'run if something in column A is changed
If Not Application.Intersect(KeyCells, Range(Target.Address)) _
Is Nothing Then
'update the start date of new stage
If IsEmpty(Cells(Target.Row, startCol)) Then
Cells(Target.Row, startCol) = Date
End If
'update the date of the stage just ended
If IsEmpty(endCol) Then
GoTo continue
End If
If IsEmpty(Cells(Target.Row, endCol)) Then
If IsEmpty(oVal) Then
GoTo continue
End If
Cells(Target.Row, endCol) = Date
End If
End If
GoTo continue
endDate:
If oVal = "FINISHED" Then
GoTo continue
End If
Cells(Target.Row, Range("BA2:BE2").Find(oVal).Column) = Date
continue:
End Sub
person
ajm_ecosse
schedule
27.04.2019