Количество одновременно перекрывающихся дат и времени

У меня есть два столбца даты/времени, и мне нужно узнать максимальное количество из них, которые перекрываются в определенное время.

Пример использования: это время начала и окончания телефонных звонков, и я ищу количество одновременных звонков.

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))

Проблема в том, что он подсчитывает общее количество перекрывающихся элементов, даже если в какой-либо точке они не перекрывают друг друга, что значительно увеличивает желаемый результат.

Я раньше плохо задавал вопрос:

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


excel excel-formula vba date-range
person ajspacemanspiff    schedule 10.09.2015    source источник
comment
Не могли бы вы уточнить это немного подробнее? Я не понимаю ваш желаемый результат по сравнению с вашим вариантом использования.   -  person Tom K.    schedule 10.09.2015
comment
Полезно думать об этом по варианту использования. У меня есть список телефонных звонков, и я пытаюсь определить, сколько телефонных линий мне нужно. Звонки начинаются и заканчиваются в разное время, и когда они пересекаются, мне нужна другая линия.   -  person ajspacemanspiff    schedule 10.09.2015
comment
Позвольте мне сказать прямо: есть телефонный звонок от 1 до 3, затем еще один от 2 до 4 (так что одно перекрывается). следующий будет от 2,5 до 5 (3 перекрытия). следующий от 6 до 7 (больше не перекрываются). Если это так, вторая строка ожидаемых результатов неверна.   -  person Tom K.    schedule 10.09.2015
comment
Пожалуйста объясните далее. Макс. одновременные подсчеты кажутся непоследовательными, даже с учетом варианта использования.   -  person Excel Hero    schedule 10.09.2015
comment
Вторая строка [с 00:20:00 до 00:30:00] пересекается с третьей строкой с 00:25:00 до 00:30:00, оставляя 1 перекрытие. Третья строка [00:25:00 до 00:40:00] пересекается со второй строкой до 00:30:00, а затем снова с четвертой строкой, начиная с 00:35:00, но так как вторая строка не пересекается с четвертой строкой, для третьей строки за раз имеется только 1 перекрытие.   -  person ajspacemanspiff    schedule 10.09.2015
comment
Итак, если я правильно понимаю, вы не хотите знать, сколько вызовов пересекает каждую строку, а в любую секунду, сколько вызовов выполняется. Затем найдите свой максимум в течение определенного временного диапазона, чтобы вы могли знать, сколько строк вам нужно для каждого заданного периода времени, скажем, от часа к часу или от минуты к минуте. Это верно?   -  person Scott Craner    schedule 10.09.2015
comment
да. это правильно.   -  person ajspacemanspiff    schedule 10.09.2015
comment
drive.google.com/file/d/0B6hnsaRAjasBWkN1R2FlN1d2ek0/   -  person ajspacemanspiff    schedule 10.09.2015
comment
Единственный способ, который я могу придумать с помощью формулы, - это создать столбец с каждой минутой этого дня, а затем использовать countifs() для подсчета количества вызовов, происходящих в каждую данную минуту дня. Затем вы можете использовать эту информацию, чтобы получить ответы.   -  person Scott Craner    schedule 10.09.2015
comment
Я думал, что у меня есть формула, но я думаю, что зашел в тупик. Было бы нормально иметь решение UDF?   -  person Excel Hero    schedule 10.09.2015
comment
Я был бы рад любому решению, которое могло бы дать мне ответ!   -  person ajspacemanspiff    schedule 10.09.2015
comment
За какой период времени вы будете составлять отчет? Данные вашего примера показывают временной интервал около трех дней.   -  person Excel Hero    schedule 11.09.2015
comment
Буду заниматься около месяца. Предпочитаю год, но хороший примерный месяц даст мне то, что я хочу. В данном случае за месяц выборки было 35006 отдельных записей.   -  person ajspacemanspiff    schedule 11.09.2015

Ответы (1)


arrow_upward
0
arrow_downward

В итоге я сделал Sub вместо UDF. Вы можете установить диапазон журнала телефонных звонков в квадратных скобках во второй строке подпрограммы. Я проверил это только с вашими шестью строками выборочных данных. Он рассчитает максимальное количество строк и запишет их на один столбец справа от журнала (столбец C в примере).

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
person Excel Hero    schedule 10.09.2015