Excel para contar o número de sessões simultâneas com base nos horários de início / término

Question

Eu tenho um conjunto enorme de dados com os quais estou tentando trabalhar. Na coluna A, eu tenho um nome de usuário, na coluna B eu tenho uma data / hora de início da sessão, na coluna C eu tenho a data / hora de término da sessão.

Estou tentando contar quantas sessões simultâneas estão acontecendo a qualquer momento com base na conta do usuário. O ponto difícil que estou enfrentando é que um usuário pode ter várias sessões ao mesmo tempo.

Por exemplo:

User     Start Time               End Time            Desired Result (license count)

JW      03/24/2015 14:00:44      03/24/2015 14:09:57     -->    4
TT      03/24/2015 13:58:14      03/24/2015 14:21:08     -->    3
DQ      03/24/2015 13:53:10      03/24/2015 14:15:39     -->    3
BB      03/24/2015 13:50:55      03/24/2015 14:20:42     -->    2
BA      03/24/2015 13:43:02      03/24/2015 13:57:26     -->    2
JW      03/24/2015 13:40:30      03/24/2015 13:48:38     -->    1
BA      03/24/2015 13:18:26      03/24/2015 13:18:44     -->    1
BA      03/24/2015 13:15:18      03/24/2015 13:15:22     -->    1
CT      03/24/2015 11:56:55      03/24/2015 11:58:21     -->    1
CT      03/24/2015 11:53:23      03/24/2015 11:56:55     -->    1
CT      03/24/2015 11:51:50      03/24/2015 11:53:23     -->    1
CT      03/24/2015 11:48:11      03/24/2015 12:16:36     -->    1
CT      03/24/2015 11:36:54      03/24/2015 11:37:50     -->    1
CT      03/24/2015 11:33:52      03/24/2015 11:39:38     -->    1
CT      03/24/2015 11:31:25      03/24/2015 11:34:01     -->    1

A quarta coluna mostra o resultado que eu quero poder computar com uma fórmula. Os dados acima podem ser mostrados graficamente como:

Como você pode ver no final do exemplo (e na parte inferior do gráfico), O usuário CT tem várias sessões ao mesmo tempo. Essas conexões contariam como apenas uma licença.

Deixe-me saber se preciso esclarecer isso.

    

Answer 1

Supondo que seus dados estejam nas colunas A a C , começando na linha 2, você pode usar essa "fórmula de matriz" em D2

=SUM(IF(FREQUENCY(IF(B$2:B$16<=B2,IF(C$2:C$16>=B2,MATCH(A$2:A$16,A$2:A$16,0))),ROW(A$2:A$16)-ROW(A$2)+1),1))

confirmado com CTRL + SHIFT + ENTER e copiado na coluna

Explicação:

Esta é uma técnica comum usada para obter uma contagem de valores diferentes em uma coluna (neste caso, usuários) onde alguns critérios são atendidos em outras colunas (neste caso, a data / hora de início mais recente é entre a hora de início / data e hora final / data em outras colunas).

O "array de dados" para FREQUENCY é o resultado da função MATCH para as linhas nas quais os critérios de tempo são atendidos - e MATCH encontrará o valor de correspondência primeiro , portanto onde você tem usuários repetidos MATCH retorna o mesmo número para cada (e você recebe FALSE para linhas onde as condições não são atendidas)

Os FREQUENCY "bins" consistem em todos os resultados possíveis para MATCH (1 a 15 neste caso), portanto, se as condições (que a banda de tempo contém a hora de início mais recente) forem atendidas e o usuário for o mesmo, o mesmo número é retornado na matriz de dados e vai no mesmo bin ...... , então é suficiente contar o número de categorias que são > 0 para obter uma contagem de diferentes usuários.

Especificamente para a linha 2, por exemplo, a matriz de dados se torna isso:

{1;2;3;4;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE}

e os 4 valores diferentes são retornados para 4 caixas diferentes, então você obtém um resultado de 4

.... mas para a linha 10 a matriz de dados se torna isso:

{FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;9;9;FALSE;9;FALSE;FALSE;FALSE}

onde há 3 linhas que correspondem às condições de tempo ..... mas todas para o mesmo usuário ( CT ), então a função MATCH retorna 9 (a posição da primeira entrada "CT" em A2:A16 ) para todos os três, então FREQUENCY obtém 3 valores no mesmo bin, então a fórmula resolve isso:

=SUM(IF({0;0;0;0;0;0;0;0;3;0;0;0;0;0;0;0},1))

A função IF retorna um 1 para cada valor diferente de zero na matriz retornada por FREQUENCY e SUM soma esses 1s ..... mas há apenas um valor diferente de zero, então o resultado é 1 (representando o número de diferentes usuários com sessões abertas naquele momento)

Veja a captura de tela em anexo

    

Answer 2

Aqui está uma fórmula muito mais curta e simples que produz o resultado desejado, o que parece ser

• o número de linhas abaixo desta para as quais
  • os intervalos de tempo se sobrepõem e
  • o usuário é diferente
• mais um.

O primeiro passo é descobrir que intervalo Iniciar ₁ / End ₁ sobrepõe-se ao intervalo Start ₂ / End ₂ se e somente se Iniciar ₁ < End ₂ e End ₁ > Inicie ₂ . (Isso é fácil de ver se você pensar nisso; mais fácil se você desenhá-lo.)

barry houdini usou ≤ e ≥ , então usarei a mesma convenção. AFAICT, não há instâncias no conjunto de dados de exemplo onde a hora inicial ou final de uma sessão coincide exatamente com o horário de início ou fim de uma sessão pertencente a um usuário diferente, então essa diferença de abordagem não deve produzir resultados diferentes (para o conjunto de dados de exemplo).

Então, para cada linha, queremos contar as linhas abaixo desta no registro de início / fim para o qual o acima é verdadeiro, e o UserID não é igual ao UserID para essa linha. E adicione 1. Isso é simplesmente

=COUNTIFS(B2:B$16, "<="&C2, C2:C$16, ">="&B2, A2:A$16, "<>"&A2) + 1

Observe que eu defini meus intervalos para ir da linha atual (representado como Row2, contendo células A2 , B2 e C2 ) para número de linha absoluta 16 (representado como Row $ 16, contendo as células A16 , B16 e C16 ). Isso faz com que COUNTIF procure apenas a linha atual e as seguintes. E note que isso é não uma fórmula de matriz.

Eu colocaria uma captura de tela mas seria (efetivamente) idêntico ao de barry, e, portanto, um desperdício de largura de banda.