Transformar intervalos de tempo em etapas discretas

Question

Eu tenho uma planilha excel de alguns trabalhos (ou quaisquer itens / ações) e suas datas de início e término. Agora, para cada minuto do dia, preciso saber quantos trabalhos estavam ativos.

Assim, a entrada é assim:

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

A saída deve ficar assim:

Observação : os nomes de tarefas foram adicionados apenas para fins de clareza. Na saída real, estou interessado apenas no número de tarefas ativas por minuto, não em quais tarefas estavam ativas

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

Alguém pode me ajudar como esse tipo de transformação de dados pode ser feito usando o Excel? Obrigado!

    

Answer 1

Número de trabalhos ativos

Você pode contar o número de linhas usando:

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

A fórmula acima funciona somente quando o intervalo de dados é uma Tabela (converta um intervalo em uma tabela pressionando Ctrl+T ) chamado Table1 (renomeie pressionando Alt, m, n ), e seu faixa de saída desejada também é uma tabela.

Se você não converter intervalos em tabelas, deverá fazer referência a intervalos com A1 para uma célula ou B:B para uma coluna inteira.

Tempo

A coluna contém os minutos. A primeira linha é inserida com o primeiro minuto que você deseja. Nas linhas subsequentes, os minutos podem ser calculados adicionando um minuto às linhas anteriores:

=A2+TIME(0,1,0)

Comentários

1. O uso de COUNTIFS pode ser extremamente lento .
2. Não tenho certeza se adicionar um minuto acumula erros de ponto flutuante em qualquer caso.
3. Se seus dados realmente se parecem com o que você digitou ( delimitado com barras verticais), talvez seja necessário Texto para colunas Alt, A, E .