☰

Devolver datos basados ​​en el intervalo de fechas coincidente

tag-icon • tag-icon microsoft-excel worksheet-function
Devolver datos basados ​​en el intervalo de fechas coincidente

Nuestro grupo tiene una hoja de Excel que se utiliza para realizar un seguimiento de las responsabilidades rotativas. Un ejemplo sencillo se presentaría así:

  • Columna A:COMENZARtiene fechas que definen el inicio del período de tiempo representado por cada artículo de línea.
  • Columna B:FINtiene fechas que definen el final del período de tiempo representado por cada partida.
  • Columna C:CESIONARIOtiene cadenas que representan a la persona asignada a la responsabilidad dada durante el período de tiempo definido por INICIO y FIN en la misma línea.

La lista en sí esgeneralmenteen orden ascendente por fecha de INICIO. Sin embargo, es posible que la hoja acabe reordenada.

No debería haber ninguna superposición entre los períodos de tiempo definidos en la lista.

Lo que quiero agregar es una sección informativa con tres valores (en celdas separadas):

  • ANTERIORsería el CESIONARIO correspondiente al período de tiempo inmediatamente anterior al actual.
  • ACTUALsería el CESIONARIO actualmente responsable.
  • PRÓXIMOsería el CESIONARIO correspondiente al periodo de tiempo posterior al que nos encontramos actualmente.

Suponiendo que la hoja de cálculo se mantenga en el orden correcto (ascendente por INICIO), regresar ANTERIOR y SIGUIENTE debería ser fácil después de que se descubra la fórmula para ACTUAL. Pero tampoco estoy seguro de por dónde empezar exactamente.

Respuesta1

Ipensaresto funcionará para encontrar corriente. Lo he probado hasta cierto punto, peroadivinaciónque ignorar una característica no es, en este caso, un problema.

Primero, agregue una columna entre B y C (el cesionario se convierte en D en adelante). En esta columna, escriba esta fórmula:

=IF(NOW()-A2>0,IF(NOW()-B2<0,"Yes","No"),"No")

Eso debería producir una fila con Sí y el resto con No (para el espacio actual).

Ahora, en tu celda del asignado actual, colocas:

=INDEX($C$2:$D$4, MATCH("Yes", $C$2:$C$4,0), 2)

Para el anterior: =INDEX($C$2:$D$4, MATCH("Yes", $C$2:$C$4,0) - 1, 2)
Para el siguiente: =INDEX($C$2:$D$4, MATCH("Yes", $C$2:$C$4,0) + 1, 2)

Normalmente, MATCH quiere que la columna de búsqueda esté ordenada, pero como solo deberíamos tener un Sí en la columna, yopensar(esta es la parte de conjetura) que podemos ignorar esa restricción.

Mi hoja de cálculo de prueba tenía solo 3 filas, por lo que YMMV.

Necesitará verificar algunos errores para el anterior cuando el actual sea la primera línea, etc., y nombrar su rango de origen probablemente sea una buena idea.

Es posible que también desee ocultar la columna adicional.

Respuesta2

Entrada ordenada:

Asignatario actual: currRow=match(now(),A:A,1)a medida que sus datos estén ordenados, encontrará la fila actual, sin necesidad de una columna auxiliar. Establezca el nombre de la celda contando esto en currRow solo para facilitar la referencia.
Asignatario: =indirect("C"& currRow + x)- x: -1, 0, 1 para las filas anterior, actual y siguiente respectivamente.

Entrada no ordenada:

Fila actual: currRow=match(max(if(A:A>now(),"",A:A)),A:A,0)esta es una fórmula matricial, por lo que debe ingresarla con CTRL+MAYÚS+ENTRAR.
Cesionario actual: Misma fórmula que antes.
Anterior: =indirect("C" & match(max(if(A:A>=indirect("A" & currRow),"",A:A)),A:A,0))- también una fórmula matricial.
Siguiente: =indirect("C" & match(min(if(A:A<=indirect("A" & currRow),"",A:A)),A:A,0))- y sigue siendo una fórmula matricial.

información relacionada