代码之家 › 专栏 › 技术社区 › Frnht451

总重叠时间

overlap time vba excel

Frnht451 · 技术社区 · 7 年前

    Sub intersectHours()
Dim a As Long, i As Long, j As Long, rng As Range, ohrs As Double
With Worksheets("Sheet1")
    For i = 2 To .Cells(Rows.Count, "E").End(xlUp).Row
        ohrs = 0: Set rng = Nothing
        For j = 2 To .Cells(Rows.Count, "E").End(xlUp).Row
            If j <> i And Not Intersect(.Range(Hour(.Cells(i, "D").Value2) & ":" & Hour(.Cells(i, "E").Value2) - 1), _
                 .Range(Hour(.Cells(j, "D").Value2) & ":" & Hour(.Cells(j, "E").Value2) - 1)) Is Nothing Then
                If rng Is Nothing Then
                    Set rng = Intersect(.Range(Hour(.Cells(i, "D").Value2) & ":" & Hour(.Cells(i, "E").Value2) - 1), _
                                    .Range(Hour(.Cells(j, "D").Value2) & ":" & Hour(.Cells(j, "E").Value2) - 1))
                Else
                    Set rng = Union(rng, Intersect(.Range(Hour(.Cells(i, "D").Value2) & ":" & Hour(.Cells(i, "E").Value2) - 1), _
                                                   .Range(Hour(.Cells(j, "D").Value2) & ":" & Hour(.Cells(j, "E").Value2) - 1)))
                End If
            End If
        Next j
        If Not rng Is Nothing Then
            For a = 1 To rng.Areas.Count
                ohrs = ohrs + TimeSerial(rng.Areas(a).Rows.Count, 0, 0)
            Next a
        End If
        '.Cells(i, 6).NumberFormat = "[h]:mm:ss"
        .Cells(i, 6) = ohrs
    Next i
End With
End Sub

1 回复 | 直到 7 年前

thanto_ 7 年前

我自己在WFM工作,我也必须考虑并发性。我已经为这种情况建立了报告。然而,我主要使用excel公式和数据透视表来表示VBA经过少量处理后的数据

我认为有两种方法可以表示数据,但您尚未指定哪种方法,因此我将为每种方法提供解决方案。

假设每行是A是代理ID,B是聊天ID,C是开始,D是停止。您需要将其转换为事件类型报告。下面的代码通过将停止事件添加到数据的底部,并将现有数据转换为开始时间事件来实现这一点。

Dim h As Long: h = ActiveSheet.UsedRange.Rows.Count
Range("A" & h + 1 & ":B" & h + h - 1).Value = Range("A2:B" & h).Value
Range("C" & h + 1 & ":C" & h + h - 1).Value = Range("D2:D" & h).Value
Range("D2:D" & h).Value = 1
Range("D" & h + 1 & ":D" & h + h - 1).Value = -1
Range("C1").Value = "Timestamp"
Range("D1").Value = "Type"

数据存储为基于事件的。

Dim h As Long: h = ActiveSheet.UsedRange.Rows.Count
ActiveSheet.UsedRange.AutoFilter
With ActiveSheet.AutoFilter.Sort
    .SortFields.Clear
    .SortFields.Add _
        Key:=Range("A2:A" & h), _
        SortOn:=xlSortOnValues, _
        Order:=xlAscending, _
        DataOption:=xlSortNormal
    .SortFields.Add _
        Key:=Range("C2:C" & h), _
        SortOn:=xlSortOnValues, _
        Order:=xlAscending, _
        DataOption:=xlSortNormal
    .Header = xlYes
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
End With

Range("E1").Value = "Concurrency"
Range("E2:E" & h).FormulaR1C1 = "=RC4+IF(RC1=R[-1]C1,R[-1]C5)"
Range("F1").Value = "isConcurrent"
Range("F2:F" & h).FormulaR1C1 = "=RC5>0"
Range("G1").Value = "Duration"
Range("G2:G" & h).FormulaR1C1 = "=IF(R[1]C1=RC1,R[1]C3-RC3,0)"

列E获取当前正在进行的聊天次数,在新聊天开始时加一次,在结束时减一次。F列检查是否至少有一个聊天正在进行。G列检查该行和下一行是否是同一代理,如果是,则获取时差。

一旦这一切都完成了,就要以结果为中心。现在,您可以表示每个代理和并发级别(例如:1次聊天和2次聊天)的持续时间,以及代理和组总数。同样,我自己做同样的任务,这就是我做的方式。