
Tenho uma lista de horários sequenciais de chegada e partida como esta:
| IN/OUT | DATE/TIME |
|--------|-----------------------|
| IN | 1/2/2019 8:25:59 AM |
| OUT | 1/2/2019 11:51:59 AM |
| IN | 1/2/2019 12:48:59 PM |
| OUT | 1/2/2019 4:45:59 PM |
| IN | 1/3/2019 9:08:59 AM |
| OUT | 1/3/2019 12:38:59 PM |
| IN | 1/3/2019 3:23:59 PM |
| OUT | 1/3/2019 4:59:59 PM |
| IN | 1/4/2019 8:49:59 AM |
| OUT | 1/4/2019 5:11:59 PM |
Desta lista preciso extrair oprimeiroedurarentradas para cada dia (que serão então usadas para calcular os horários médios de chegada e partida, duração, etc.).
Por exemplo, da tabela acima eu gostaria de extrair o seguinte:
| IN/OUT | DATE/TIME |
|--------|-----------------------|
| IN | 1/2/2019 8:25:59 AM |
| OUT | 1/2/2019 4:45:59 PM |
| IN | 1/3/2019 9:08:59 AM |
| OUT | 1/3/2019 4:59:59 PM |
| IN | 1/4/2019 8:49:59 AM |
| OUT | 1/4/2019 5:11:59 PM |
Eu sei que posso usar um VLOOKUP para obter todos os valores 'IN' ou 'OUT', mas o problema é que a maioria dos dias da lista tem mais de um. Preciso apenas do primeiro e do último, mas não sei como fazer isso.
Existe uma maneira de fazer isso usando VLOOKUP, com ou sem uma tabela auxiliar? Ou talvez outro método funcione melhor?
Responder1
1. Lembre-se de pesquisar antes de perguntar
Usando MAX() no Excel em uma lista de datas usando vários critérios
Veja em 'Relacionado'?
2. MIN
/ MAX
Receita CSE
MIN
para 'DENTRO':
=MIN(IF($A$2:$A$99=F2,IF($B$2:$B$99=G2,$C$2:$C$99)))
Isso é o que eles chamam de matriz ou fórmula 'CSE' - insira-a na barra de fórmulas, pressione Ctrl Shift Enterpara inseri-la como uma fórmula de matriz e copie.
Isso faz sentido intuitivamente, certo? Obtenha o valor mais baixo de C
onde F
matches A
e G
matches B
.
Porém, isso obtém apenas os valores mínimos de IN... trocar MAX
em MIN
todas as outras linhas obtém os valores máximos de OUT... Mas quem tem tempo para isso? Vamos esmagá-los com um simples IF
:
3. FÓRMULA FINAL
=IF(F2="IN",MIN(IF($A$2:$A$99=F2,IF($B$2:$B$99=G2,$C$2:$C$99))),MAX(IF($A$2:$A$99=F2,IF($B$2:$B$99=G2,$C$2:$C$99))))
Novamente, insira essa fórmula em F2, pressione Ctrl Shift Entere copie.
Observação:
- Ajuste as referências de células conforme necessário
- Você não disse muito explicitamente sobre o formato de suas entradas de dados; Separei cada argumento usando Text to Columns do Excel para poder operar em relação a datas e horas de forma independente.
- NÃO há detecção de erros incluída e a solução pressupõe que você já tenha os valores de teste desejados definidos conforme mostrado. Deve ser fácil copiar suas colunas de entrada IN/OUT e DATE e usar 'Remover Duplicados' para gerar sua lista exclusiva correspondente.
- Não há formatação especial no que tenho em column
C
. É um texto simples conforme mostrado.Você provavelmente terá que usar o Format Painter(daquela coluna)na sua coluna de saída final! - Ignore minha coluna
D
, que usei como simples verificação de erros ao montar tudo e esqueci de excluir antes de fazer capturas de tela.