☰

Excel: Encontre os valores mais antigos/mais recentes para cada data em uma lista de datas e horas

tag-icon • tag-icon microsoft-excel microsoft-excel-2016
Excel: Encontre os valores mais antigos/mais recentes para cada data em uma lista de datas e horas

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'?

CONSULTA RELACIONADA

2. MIN/ MAXReceita CSE

IMAGEM DE AMOSTRA

MINpara '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 Conde Fmatches Ae Gmatches B.

Porém, isso obtém apenas os valores mínimos de IN... trocar MAXem MINtodas 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

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:

  1. Ajuste as referências de células conforme necessário
  2. 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.
  3. 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.
  4. 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!
  5. 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.

informação relacionada