Pivotando horas de início e de término simultâneas em timeslots com a Tabela Dinâmica do Excel 2010

Question

Eu tenho uma tabela de processos e seus respectivos horários de início e término (calculados a partir do horário de início e duração), listados pelo ID do processo:

Run ID  Duration    Start Time  End Time
200878  464     5/16/11 4:15    5/16/11 11:59
200879  76      5/16/11 4:22    5/16/11 5:38
200880  165     5/16/11 6:29    5/16/11 9:14
200881  44      5/16/11 9:44    5/16/11 10:28

Eu preciso de uma maneira de dinamizar os dados em "intervalos de tempo" para contar o número de processos em execução durante cada hora usando as Tabelas Dinâmicas internas do Excel.

Para o trecho de dados acima, quero este gráfico:

Timeslot Start  Timeslot End    Running Processes
5/16/11 3:00    5/16/11 4:00    0
5/16/11 4:00    5/16/11 5:00    2
5/16/11 5:00    5/16/11 6:00    2
5/16/11 6:00    5/16/11 7:00    2
5/16/11 7:00    5/16/11 8:00    2
5/16/11 8:00    5/16/11 9:00    2
5/16/11 9:00    5/16/11 10:00   3
5/16/11 10:00   5/16/11 11:00   2
5/16/11 11:00   5/16/11 12:00   1

Minha solução atual usa a função SUMIFS para contar as linhas que se encaixam em cada período de atividade. Isso realmente informa as estatísticas exatas que eu quero, mas com as seguintes desvantagens:

• Os horários de "Início de período de atividade" precisam ser inseridos manualmente e estendidos para cobrir o intervalo de vezes representado na tabela original.
• Nenhum dos mecanismos de filtragem das Tabelas Dinâmicas nativas do Excel funciona, como fatiadores, ou organizando por outros parâmetros (meus dados reais possuem outros campos além de "ID de Execução" que foram removidos para brevidade)

Assim, se possível, gostaria de obter uma maneira de obter os mesmos dados com as Tabelas Dinâmicas "reais".

Também adicionei o agrupamento por hora / dia / mês / ano em uma tabela dinâmica, mas, mesmo com a opção "Mostrar itens sem dados", o fato de os processos poderem durar mais de uma hora causa problemas. relatórios (processos que se estendem por um determinado intervalo de tempo, mas não iniciam ou terminam nele, não são contados para esse intervalo de tempo.)

Para sua experiência, aqui está um arquivo xlsx com alguns dados e meu próprio pivô baseado em SUMIFS: link

    

Answer 1

Eu honestamente não vejo uma maneira de lidar com isso com uma tabela dinâmica incorporada do Excel. No entanto, você pode criar sua própria tabela para exibir os dados de que precisa. O único bit manual que você precisa fazer é criar as colunas de horários de início e término do slot para os quais você deseja contar o processo. Depois disso, uma fórmula de matriz pode ser usada para obter as contagens do processo.

=COUNT(IF((H2>=$C$2:$C$50)*(H2<=$D$2:$D$50)+(I2>=$C$2:$C$50)*(I2<=$D$2:$D$50)>0,1,""))

Insira isso como uma fórmula de matriz pressionando Ctrl + Shift + Enter. A coluna H mantém o Timeslot Start, a coluna I mantém o Timeslot End, a coluna C retém o Start Time na sua tabela de processos e a Column D mantém o End Time na sua tabela de processos. Observe que você pode definir a fórmula para ficar bem além da parte inferior da sua tabela de processos, portanto, se a lista de processos estiver em constante mudança, sua fórmula de contagem ainda retornará o resultado correto.