
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'?
2. MIN
/ MAX
Receta CSE
MIN
para '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 C
donde F
coincide A
y G
coincide B
.
Sin embargo, eso solo obtiene los valores IN mínimos... intercambiar MAX
en MIN
cada 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
=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:
- Ajuste las referencias de celda según sea necesario
- 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.
- 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.
- 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! - Ignore mi columna
D
, que utilicé como simple verificación de errores al armar esto y olvidé eliminarla antes de tomar capturas de pantalla.