Excel - 计算单元格包含特定文本的天数

Question

看起来您想要跟踪单个单元格及其随时间的变化。没有办法在本机执行此操作。但是，您可以使用 VBA 跟踪更改，然后使用公式评估该审计跟踪。

这两个步骤都很复杂，所以这个答案也很复杂。尝试实施，如果出现问题，请先找出原因，然后再提出具体问题。

首先，您需要设置一个工作表来存放审计线索。我将我的工作表命名为“AuditTrail”，中间没有空格。您可能还应该添加一个标题行，以便以后更轻松地阅读。稍后您将在屏幕截图中看到我在第一行中添加的内容。

其次，应将此 VBA 代码粘贴到包含您要监视的单元格的任何工作表的模块中。按Alt+打开 VBA F11（还有其他方法）。双击工作表名称以打开其模块，然后粘贴代码。对我来说，工作表名称是“Sheet1”。您可以看到 Excel 为每个工作表分配了一个代码名称，但也会向您显示您在 Excel 中看到的您为其指定的名称。例如，我命名为“AuditTrail”的工作表的代码名称为“Sheet2”。在括号中查找您为工作表指定的名称。

粘贴此代码后，请务必仔细检查开头的两个值并根据需要更改它们。


    Private Sub Worksheet_Change(ByVal Target As Range)
        
        ' You have to change / check at least these values:
        Const addressOfCellToWatch As String = "A2"
        Const sheetNameForAuditTrail As String = "AuditTrail"
        
        ' The rest of code will work as-is
        Dim rangeToWatch As Range
        Dim newActiveCell As Range
        Dim oldValue
        Dim lastUsedRow As Long
        Dim auditDataRange As Range
    
        ' Use this method if you only want to watch one range
        Set rangeToWatch = Range(addressOfCellToWatch)
        
        ' Check if the Target (the cell that changed) was part of the range to watch
        If Not Intersect(Target, rangeToWatch) Is Nothing Then
            ' Undo the change, get the old value, then redo the change
            ' Not necessary but you may want to keep track of it
            Application.EnableEvents = False
            Set newActiveCell = ActiveCell
            Application.Undo
            oldValue = Target.Value
            Application.Undo   'Yes, this is a redo. I know it seems backwards.
            newActiveCell.Activate
            Application.EnableEvents = True
            
            ' Compile the results
            ' The array order is (time, sheet name, cell address, user name, previous value, new value)
            auditdata = Array(Now, Target.Parent.Name, Target.Address, Environ("username"), oldValue, Target.Value)
            
            ' Find where we're going to paste these results
            With Worksheets(sheetNameForAuditTrail)
                ' Find the last used row
                lastUsedRow = .Cells(.Rows.Count, 1).End(xlUp).Row
                
                ' Define the cells we're going to use for the audit data
                Set auditDataRange = .Range("A1:F1").Offset(lastUsedRow)
                
                ' Store the data
                auditDataRange = auditdata
            End With
        End If
        
    End Sub

当您编辑要跟踪的单元格时，审计跟踪将填充如下数据：

现在您已经拥有了所有这些奇特的数据，您可以使用公式来提取结果。您没有讨论如果值从变为a然后b又变回，您想如何处理它a。我已经编写了公式来提取某个值在最近一次变为该值的时间。如果您想要某个值变为某个值的总天数，那将更加复杂。

=LET(value,E2,firstDate,MAXIFS(AuditTrail!A:A,AuditTrail!F:F,value),lastDate,MAXIFS(AuditTrail!A:A,AuditTrail!E:E,value),IF(firstDate=0,"",IF(lastDate=0,TODAY(),lastDate)-firstDate))

LET() 函数允许您为变量赋值，然后稍后引用变量名称。它可以让杂乱的公式更易于阅读。以下是每个值的细分：

value,E2定义我们在审计线索中寻找的值。例如，可以仅包含将在审计线索中搜索的E2文本a和公式。a
firstDate,MAXIFS(AuditTrail!A:A,AuditTrail!F:F,value)查找单元格更改的最近日期到值。如果从未更改为该值，则值为 0。
lastDate,MAXIFS(AuditTrail!A:A,AuditTrail!E:E,value)查找单元格更改的最近日期从值。如果该值从未改变过，则为 0。firstDate例如，如果a当时是b，现在又是，则可能小于a。它会找到“b→a”日期之前的“a→b”日期。
IF(firstDate=0,"",捕捉该值从未被改变的时间。
IF(lastDate=0,TODAY(),lastDate)捕捉从未改变的时刻从值并替换为今天的日期。即，它当前值已经存在多久了。
-firstDate减去到日期从日期（或今天的日期，取决于上一步）。

这将返回十进制数，但您可以根据需要进行四舍五入。

Answer 1