☰

使用来自 VBA Audit Trail 输出的数据计算单元格包含特定文本的时间

tag-icon • tag-icon microsoft-excel worksheet-function
使用来自 VBA Audit Trail 输出的数据计算单元格包含特定文本的时间

根据我之前的查询Excel - 计算单元格包含特定文本的天数这个问题由工程师 Toast 回答，我希望利用 VBA 创建的 AuditTrail 来捕获特定范围在某种状态下保持了多长时间。

例如：查看图片 1 - Sheet1在 C3 中，B3 包含文本“a”多长时间了，然后在 D3 中，B3 包含文本“b”多长时间了等等，忽略对 B4 和 B5 所做的更改，因此本质上只查找在图片 2 - 审计追踪包含引用 $B$3 的单元格。

Toast 工程师提到我可以修改 VBA 代码，但这会创建多张 AuditTrail 表，这不是我想要的。

图片 1 - Sheet1

工作表1

图片 2 - 审计追踪

审计追踪

到目前为止，我得到的只是以下代码，但这些代码远非正确。如果 AuditTrail 表的单元格 C2:C1000 包含文本“$B$3”，则完成 Engineer Toast 提供的公式

=LET(value,E2,IF((AuditTrail!C2:C1000="$B$3"),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))

图 3 - 预期结果 最终的 任何帮助是极大的赞赏。

答案1

这是如何运作的？

=SUM((A2:A12)*(C2:C12="$B$3")*(E2:E12="a"))-SUM((A2:A12)*(C2:C12="$B$3")*(F2:F12="a"))

问题是 $B$3 总共等于 a 的值是多少？手动计算如下：

(A6-A2) + (A8-A7) + (A10-A9)

可以写成

(A6+A8+A10) - (A2+A7+A9)

或者

(sum of all $B$3 rows where old value is "a") - (sum of all $B$3 rows where new value is "a")

到目前为止，我看到的唯一问题是，如果我们有不匹配的对，即在开始时我们没有记录单元格变为“a”（没有新值，但有一个旧值）或相反，$B$3 仍然是“a”（一个新值但没有旧值）。在第一种情况下，您的答案将比平常大得多（>40000），在后一种情况下为负~40000。

如果这是个问题，您可以放入 if 语句并添加或减去第一个旧值或最后一个新值。

相关内容