☰

Excel: busque los valores más antiguos/últimos para cada fecha en una lista de fechas y horas

tag-icon • tag-icon microsoft-excel microsoft-excel-2016
Excel: busque los valores más antiguos/últimos para cada fecha en una lista de fechas y horas

Tengo una lista de horarios secuenciales de llegada y salida 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  |

De esta lista necesito extraer elprimeroyúltimoentradas para cada día (que luego se utilizarán para calcular los tiempos medios de llegada y salida, duración, etc.).

Por ejemplo, de la tabla anterior me gustaría extraer lo siguiente:

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

Sé que puedo usar BUSCARV para obtener todos los valores 'IN' o 'OUT', pero el problema es que la mayoría de los días de la lista tienen más de uno. Sólo necesito el primero y el último, pero no estoy seguro de cómo hacerlo.

¿Hay alguna manera de lograr esto usando BUSCARV, con o sin una tabla auxiliar? ¿O quizás otro método podría funcionar mejor?

Respuesta1

1. Recuerda buscar antes de preguntar

Usando MAX() en Excel contra una lista de fechas usando múltiples criterios

¿Ver en 'Relacionados'?

CONSULTA RELACIONADA

2. MIN/ MAXReceta CSE

IMAGEN DE MUESTRA

MINpara 'EN':

=MIN(IF($A$2:$A$99=F2,IF($B$2:$B$99=G2,$C$2:$C$99)))

Esto es lo que llaman una fórmula matricial o 'CSE': ingrésala en la barra de fórmulas, luego presiona Ctrl Shift Enterpara ingresarla como una fórmula matricial y luego cópiala.

Esto tiene sentido intuitivo, ¿verdad? Obtenga el valor más bajo de Cdonde Fcoincide Ay Gcoincide B.

Sin embargo, eso solo obtiene los valores IN mínimos... intercambiar MAXen MINcada dos filas obtiene los valores OUT máximos... ¿Pero quién tiene tiempo para eso? Vamos a aplastarlos con un simple 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))))

Nuevamente, ingrese esa fórmula en F2, presione Ctrl Shift Entery luego copie.

NÓTESE BIEN:

  1. Ajuste las referencias de celda según sea necesario
  2. No dijiste mucho explícitamente sobre la forma de tus entradas de datos; Separé cada argumento usando Texto en columnas de Excel para poder operar con fechas y horas de forma independiente.
  3. NO se incluye captura de errores y la solución supone que ya tiene los valores de prueba deseados establecidos como se muestra. Debería ser fácil copiar las columnas de entrada/SALIDA y FECHA y usar 'Eliminar duplicados' para generar una lista única que coincida.
  4. No hay ningún formato especial en lo que tengo en la columna C. Es un texto simple como se muestra.Probablemente tendrás que usar el Copiador de formato.(de esa columna)en su columna de salida final!
  5. Ignore mi columna D, que utilicé como simple verificación de errores al armar esto y olvidé eliminarla antes de tomar capturas de pantalla.

información relacionada