同时重叠日期时间数 [英] Number of Simultaneous Overlapping datetime(s)

问题描述

我有两列日期/时间，需要找出在特定时间重叠的最大数量。

I have two columns of date/time(s) and need to find out the maximum number of them that overlap at a particular time.

用例是这些：这些是电话的开始和结束时间，并且正在寻找同时通话的次数。

Use case is this: These are start and end times for phone calls and am looking to find the number of simultaneous calls.

Column A            Column B                 
8/06/15 00:17:00    8/06/15 00:19:00     
8/09/15 00:20:00    8/09/15 00:30:00     
8/09/15 00:25:00    8/09/15 00:40:00    
8/09/15 00:35:00    8/09/15 00:50:00     
8/09/15 00:45:00    8/09/15 00:55:00     
8/09/15 00:46:00    8/09/15 00:52:00     

预期结果

Column A            Column B             Max Simultaneous    
8/06/15 00:17:00    8/06/15 00:19:00     0
8/09/15 00:20:00    8/09/15 00:30:00     1
8/09/15 00:25:00    8/09/15 00:40:00     1
8/09/15 00:35:00    8/09/15 00:50:00     2
8/09/15 00:45:00    8/09/15 00:55:00     2
8/09/15 00:46:00    8/09/15 00:52:00     2

我正在尝试的公式是：

=SUMPRODUCT((A$2:A$35006<=B2)*(B$2:B$35006>=A2))

这个问题，就是它吧即使在任何一个点，它们都不重叠，大大增加了预期的结果。

Problem with this, is that it counts the total number that overlap, even if at any one point they do not overlap themselves - greatly increasing the desired result.

我以前问过这个问题：

https://stackoverflow.com/posts/32486571/

推荐答案

我最终做了一个Sub而不是UDF。您可以在例程的第二行的方括号中设置电话记录的范围。我只用六行样本数据测试了它。它将计算最大行数，并将它们写入日志右侧的一列（示例中的列C）。

I ended up making a Sub instead of a UDF. You can set the range of the phone call log in the square brackets on the second line of the routine. I've only tested it with your six rows of sample data. It will calculate the max lines and write them one column to the right of the log (column C in the example).

Public Sub MaxLines()
    Dim c&, i&, j&, k&, min_#, max_#, n&, v, w&(), vOut, r As Range
    Set r = [a2:b7]
    v = r
    With Application
        min_ = .Min(.Index(r, 0, 1))
        max_ = .Max(.Index(r, 0, 2))
    End With
    n = (max_ - min_) * 1440
    ReDim vOut(1 To UBound(v), 1 To 1)
    ReDim w(1 To n + 1)
    For i = 1 To UBound(v)
        k = (v(i, 1) - min_) * 1440 + 1
        c = CLng((v(i, 2) - v(i, 1)) * 1440)
        For j = 0 To c
            w(j + k) = w(j + k) + 1
        Next
    Next
    For i = 1 To UBound(v)
        k = (v(i, 1) - min_) * 1440 + 1
        c = CLng((v(i, 2) - v(i, 1)) * 1440)
        max_ = 0
        For j = 0 To c
            If w(j + k) > max_ Then max_ = w(j + k)
        Next
        vOut(i, 1) = max_ - 1
    Next
    r.Resize(, 1).Offset(, 2) = vOut
End Sub

这篇关于同时重叠日期时间数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！