Преобразование промежутков времени в дискретные шаги

Question

У меня есть лист Excel с некоторыми заданиями (или какими-либо предметами / действиями) и датами их начала и окончания. Теперь для каждой минуты дня мне нужно знать, сколько рабочих мест было активно.

Таким образом, вход выглядит так:

Jobname |        Start        |         End          
---------------------------------------------------      
JobA    | 04/10/2015 08:00:00 | 04/10/2015 09:00:00
JobB    | 04/10/2015 10:00:00 | 04/10/2015 10:00:59
JobC    | 04/10/2015 10:00:00 | 04/10/2015 11:00:00

Вывод должен выглядеть так:

Примечание: Имена заданий просто добавлены для ясности, в реальном выводе меня интересует только количество активных заданий в минуту, а не то, какие задания были активными

Time                | Number of active jobs
---------------------------------------------------      
04/10/2015 00:00:00 | 0
[..]
04/10/2015 08:00:00 | 1 (JobA)
04/10/2015 08:01:00 | 1 (JobA)
[..]
04/10/2015 09:00:00 | 1 (JobA)
04/10/2015 09:01:00 | 0
[..]
04/10/2015 09:59:00 | 0
04/10/2015 10:00:00 | 2 (JobB, JobC)
04/10/2015 10:01:00 | 1 (JobC)
[..]
04/10/2015 11:00:00 | 1 (JobC)
04/10/2015 11:01:00 | 0
[..]
04/10/2015 23:59:00 | 0

Может кто-нибудь помочь мне, как такого рода преобразование данных может быть сделано с помощью Excel? Спасибо!

Answer 1

Количество активных рабочих мест

Вы можете посчитать количество строк, используя:

=COUNTIFS(Table1[Start],"<="&[@Time],Table1[End],">="&[@Time])

Приведенная выше формула работает только в том случае, если диапазон данных представляет собой таблицу (преобразуйте диапазон в таблицу, нажав Ctrl+T) с именем Table1 (переименуйте, нажав Alt, m, n), а желаемый выходной диапазон также является таблицей.

Если вы не преобразуете диапазоны в таблицы, вы должны сделать ссылку на диапазоны с A1 для ячейки или B:B для всего столбца.

Время

Столбец содержит минуты. Первый ряд вводится с первой минутой, которую вы хотите. В последующих строках минуты можно вычислить, добавив минуту к предыдущим строкам:

=A2+TIME(0,1,0)

замечания

1. Использование COUNTIFS может быть очень медленным.
2. Я не уверен, что добавление минуты накапливает ошибки с плавающей точкой в любом случае.
3. Если ваши данные действительно похожи на то, что вы ввели (разделенные вертикальными чертами), вам также может понадобиться текст для столбцов Alt, A, E