Подсчет количества одновременно совершаемых телефонных соединений

[VictorK]

Здравствуйте, уважаемые форумчане!
Занимаюсь анализом телефонного трафика и, в частности, возникла задача оценить пропускную способность канала, а для этого нужно по существующим данным за прошлый период (трафик) рассчитать количество одновременно совершаемых вызовов, то есть всех предыдущих, которые на момент начала текущего соединения находятся в состоянии разговора. Использовал для этого формулу с функцией СУММПРОИЗВ(). Но поскольку рабочий трафик за месяц состоит более чем из 170 000 записей, эта формула получается очень ресурсоемкой - требует много памяти и мощного процессора. В прилагаемом файле небольшой фрагмент (2 дня) трафика и описание проблемы. Надеюсь, кто нибудь из "гуру Excel" предложит более рациональное решение.

[Казанский]

Попробуйте формулу массива для 3-й строки

=СУММ(ЕСЛИ($A$2:A3=A3;ЕСЛИ(($B$2:B3+$C$2:C3/86400)>=B3;1))

[Казанский]

А еще лучше ввести доп. столбец END с очевидной формулой и использовать СЧЕТЕСЛИМН (ст. E,F).

[VictorK]

Спасибо. Формулу Вашу вставил, но при заполнении рабочего файла двойным кликом по правому нижнему углу ячейки Excel надолго завис.
Наверное, всё таки не стоит пытаться работать сразу с таким большим объёмом.
Попробую сделать сводную, определю по ней самый загруженный день и уже по этой дате проанализирую на максимум одновременных соединений.
Ещё раз спасибо.
О! Ещё одно решение! Сейчас гляну, только Excel пока "висит"...
System Explorer показывает, что Excel "съел" 7 Гигабайт оперативной памяти и 9 Гигабайт своп-файла

[Hugo121]

Попробуйте макросом.
Но сперва добавить два допстолбца - можно это и макросом считать, но не хотелось время тратить

P.S. Много памяти есть не должно - трачу время на очистку отработанного времени... Хотя посмотрите - может где-то оно и будет висеть, хотя словарь в итоге почти пустой

[IgorGO]

формула длиннее:

=ЕСЛИ(ПОИСКПОЗ(RC[-1];C[-2])-СТРОКА()<2;ПОИСКПОЗ(RC[-1];C[-2])-СТРОКА()+1;МАКС(МУМНОЖ(((RC[-2]:ИНДЕКС(C[-2];ПОИСКПОЗ(RC[-1];C[-2]))>=ТРАНСП(RC[-2]:ИНДЕКС(C[-2];ПОИСКПОЗ(RC[-1];C[-2]))))*(RC[-2]:ИНДЕКС(C[-2];ПОИСКПОЗ(RC[-1];C[-2]))<=ТРАНСП(RC[-1]:ИНДЕКС(C[-1];ПОИСКПОЗ(RC[-1];C[-2])))));СТРОКА(RC[-2]:ИНДЕКС(C[-2];ПОИСКПОЗ(RC[-1];C[-2])))^0)))

но считать должна намного быстрее, потому что не считает каждый раз всю таблицу с данными, а только необходимые ближайшие несколько строк
(на 800 строках данных это работает просто незаметно).
ну, и в данных дата и время начала и окончания звонка обьеденены в отдельные ячейки

[Казанский]

Макрос, на приведенных данных отрабатывает менее чем за 20 мс.
Благодаря тому, что счет идет в целых числах (в секундах от начала массива).
Можно оформить как UDF и выводить массив q на лист.

Sub VictorK()
Dim v(), i&, j&, mx&, ub&, time0#, en&
v = Range("A2", Cells(Rows.Count, "C").End(xlUp)).Value
ub = UBound(v)
ReDim st(1 To ub) As Long, q(1 To ub) As Long
time0 = v(1, 1) + v(1, 2)
For i = 2 To ub
  st(i) = (v(i, 1) + v(i, 2) - time0) * 86400
Next
For i = 1 To ub
  en = st(i) + v(i, 3)
  For j = i + 1 To ub
    If st(j) <= en Then q(j) = q(j) + 1 Else Exit For
  Next
Next
mx = Application.Max(q) + 1
MsgBox "макс. число одновр. соед. " & mx
End Sub

[VictorK]

Всем большое спасибо, буду пробовать по очереди.
Вариант с макросом от Hugo121 задумался на полтора часа, пришлось убивать процесс Excel. Действительно, считать весь вышележащий массив данных до конца месяца (десятки тысяч строк) не имеет смысла. На практике очень редко попадаются соединения продолжительностью более 3 часов.

[Hugo121]

Полтора часа? А внизу в статусбаре время тикало? Там каждая сотая строка пишется.
Но уже есть мнгновенный вариант Казанского
Как оно работает - я пока не дотумкал, времени изучить нет

А мой вариант и не считает весь вышележащий массив. Вернее посчитал и забыл, запомнил только максимум. Но всё равно небыстро...

[VictorK]

Сообщение от Hugo121

Полтора часа? А внизу в статусбаре время тикало? Там каждая сотая строка пишется.

В Windows 7 подвисшее приложение при обращении к нему не отвечает и "мутнеет", в заголовке окна выводится надпись - "приложение не отвечает". В это время никаких признаков работы макроса не наблюдалось, можно было подождать ещё, но решил просто прервать процесс.
Сейчас пробую применить простую формулу СЧЁТЕСЛИ() к двум дополнительным столбцам с объединенными датой/временем, но, похоже, опять Excel повисла...