Excel para contar o número de sessões simultâneas com base nas horas de início / fim e no nome do PC

Question

Eu sei que existe um tópico muito parecido com este já Excel para contar o número de sessões simultâneas com base nos horários de início / fim - no entanto, por mais que eu tente, Não consigo replicar e manipular a resposta sugerida para atender às minhas necessidades.

Também estou procurando detectar quando um ID de usuário faz login em um sistema ERP de legado unix de vários PCs simultaneamente - possivelmente, eles compartilham suas credenciais com um colega.

Um exemplo dos dados é:

Record #    Time In    Time Out    User      PC Name
1           10:54      10:55       Al        PC1
2           10:55      16:55       Dave      PC42
3           09:11      09:29       Al        PC1
4           11:02      18:03       Al        PC1
5           16:19      17:38       George    PC678
6           16:43      17:41       Al        PC678
7           16:46      17:39       Dave      PC42
8           17:25      17:49       Al        PC42

Os valores de "Entrada de Tempo" e "Tempo de Espera" são, na verdade, data / hora completos, então você não precisa se preocupar em comparar eventos isso aconteceu em dias diferentes. Estou exibindo apenas os tempos aqui de modo a evitar fornecer demasiada informação . Os dados acima podem ser mostrados graficamente como:

No final da lista (e na parte inferior do gráfico) você pode ver que o usuário Al está acessando o sistema a partir de vários PCs ao mesmo tempo, bem como os outros usuários também acessando o sistema dos mesmos computadores. O usuário Al está andando e fazendo login (improvável) ou os outros usuários são capazes de logar como Al para executar tarefas que suas permissões não permitem. Idealmente eu gostaria de ter uma nova coluna para destacar que naquela época havia sessões simultâneas do User ID Al em PCs diferentes. Record # 4, 6 e 8 alertaria - Os registros 2 e 7 são aceitáveis, já que o mesmo usuário fez login no mesmo computador.

    

Answer 1

Você deseja identificar as linhas para o qual há pelo menos uma outra linha com o mesmo UserID para o qual os intervalos de tempo In / Out se sobrepõem. Isto é bastante simples (pelo menos, uma vez que você sabe a resposta. 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.) Então, para cada linha, queremos contar as linhas no registro de login / logout para o qual o acima é verdadeiro, e o UserID é igual ao UserID para essa linha. Isso é simplesmente

=COUNTIFS(A$2:A$9, "<"&B2, B$2:B$9, ">"&A2, C$2:C$9, "="&C2)

para o layout de dados original (onde TimeIn é Coluna A , TimeOut é Coluna B , e Usuário é Coluna C ). Isso irá avaliar no mínimo 1, porque cada linha se sobrepõe a si mesma. Você quer ver quais linhas se sobrepõem a outras linhas, então fazemos

=IF(COUNTIFS(B$2:B$9, "<"&C2, C$2:C$9, ">"&B2, D$2:D$9, "="&D2) > 1,  "Overlap",  "")

Depois que eu mudo o TimeIn para o Registro # 7 (User Dave no PC42) das 16:46 às 16:56 para eliminar a sobreposição com o Registro # 2 ( também User Dave no PC42), que eu presumo não ter sido intencional, recebo os seguintes resultados:

quais,acredito,sãooquevocêquer.

OK,seforpossíveleválidoparaumusuárioestarlogadoemumúnicoPCparaperíodosdetemposobrepostos(comoDavefaznoPC42nosregistros2e7),evocênãoquerqueissosejacontadocomosobreposiçõesentãoprecisamosmodificarnossafórmulaparacontarapenaslinhasondeoPCédiferente:

=IF(COUNTIFS(A$2:A$9,"<"&B2, B$2:B$9, ">"&A2, C$2:C$9, "="&C2, D$2:D$9, "<>"&D2) > 0, "Overlap", "")

Note que temos que alterar o teste de > 1 para > 0 porque as linhas não se sobrepõem mais.