Identifica o primeiro login e as últimas entradas de logout

Question

Eu tenho uma planilha que precisa determinar a primeira vez que um usuário fez login no sistema e a última vez que o usuário efetuou o logout.

O relatório de login e logout vem de outro sistema do qual copio e colo os dados em minha planilha. Este relatório não é classificado em ordem cronológica.

Basicamente, como os usuários usam o sistema para efetuar login e logout, eles terão vários registros de login e logout diários. Preciso encontrar o primeiro registro de login e o último registro de logout por usuário.

Eu tentei usar INDEX() e MATCH() para fazer isso, mas só consegui retornar a primeira entrada para cada usuário. Como mencionei anteriormente, o relatório extraído não é classificado em ordem cronológica.

    

Answer 1

A solução requer o uso de duas fórmulas de matriz:

Matrizentra(Ctrl+Desloca+Enter)aseguintefórmulaemF2ecopia-cola/fill-downnorestodacolunadatabela(nãoseesqueçaderemovero{e}):

{=MIN(IF($A$2:$A$15=$E2,$B$2:$B$15,MAX($B$2:$B$15)))}

Matriz entra ( Ctrl + Desloca + Enter ) a seguinte fórmula em G2 e copia-cola / fill-down no resto da coluna da tabela (não se esqueça de remover o { e } ):

{=MAX(IF($A$2:$A$15=$E2,$C$2:$C$15,MIN($C$2:$C$15)))}

Ambas as fórmulas funcionam gerando uma matriz dos tempos relevantes quando uma linha corresponde ao usuário apropriado e um valor especial quando isso não ocorre. O truque é escolher um valor especial para que ele não interfira nas funções =MIN(…) e =MAX(…) usadas para extrair o primeiro login e o último logout.

Esses valores especiais são o máximo de todos os tempos de login para o caso MIN e o mínimo de todos os tempos de logout para o caso MAX .

Claro, você poderia usar qualquer número arbitrariamente grande para o MIN case

{=MIN(IF($A$2:$A$15=$E2,$B$2:$B$15,10^9))}

e zero para o caso MAX

{=MAX(IF($A$2:$A$15=$E2,$C$2:$C$15,0))}

EDITAR:

Se algum dos dados do relatório ( User , Login ou Logout ) puder ser omitido, as fórmulas precisarão ser adaptadas. É mais simples modificar as duas últimas fórmulas acima.

A fórmula F2 torna-se

{=IFERROR(1/(1/(1/(1/MIN(IF($A$2:$A$15=$E2,$B$2:$B$15,10^9)))-1/(1/10^9)))+10^9,"")}

e a fórmula G2 muda para

{=IFERROR(1/(1/MAX(IF($A$2:$A$15=$E2,$C$2:$C$15,0))),"")}

Para entender como essas modificações funcionam, é melhor começar com a segunda fórmula.

O 1/(1/MAX(…)) converte um resultado zero em um erro #DIV/0! , mas deixa todos os outros valores sozinhos ^* . Um resultado zero ocorre se não houver tempos de login para um usuário especificado ou se não houver registros para esse usuário.

O IFERROR() converte o erro em um espaço em branco. (Note que você pode substituir qualquer string pelo espaço em branco, se desejado).

A primeira fórmula é um pouco mais complicada, pois quando não existem registros correspondentes, um resultado de 10^9 é retornado em vez de um zero. (Um zero ainda é retornado para o caso de nenhum horário de logout, no entanto.)

Portanto, é necessária uma conversão de 10^9 para um espaço em branco, além de uma conversão zero. Essa segunda conversão é feita subtraindo 10^9 do primeiro resultado de conversão de zero a erro e convertendo isso em erro se for zero. O resultado inicial é então restaurado adicionando um 10^9 . Obviamente, se o resultado parcial for um erro, o erro será retido.

O -1/(1/10^9) em vez de um -10^9 é necessário para considerar os erros de ponto flutuante.

Em seguida, o IFERROR() converte o erro em um espaço em branco, exatamente como na segunda fórmula.