☰

Indexación de un rango de fechas si la fecha es mayor que otra fecha y está dentro de n días de ella

tag-icon • tag-icon microsoft-excel worksheet-function
Indexación de un rango de fechas si la fecha es mayor que otra fecha y está dentro de n días de ella

en unPublicación anteriorHice una pregunta sobre cómo encontrar la fecha más reciente asociada con una entidad en particular y obtener ese valor devuelto en la Hoja2 siempre que sea mayor que la fecha asociada en la Hoja2. Sin embargo, me di cuenta de que el verdadero problema no es obtener la fecha más reciente si se cumple la condición, sino obtener la fecha si es mayor y dentro de los 2 días (o generalmente n días) de la fecha de referencia.

La fórmula producida antes era la siguiente:

=IF(
     INDIRECT("LargerSheet!$B"&        //Cell starting with "$B" and ending with
     MATCH($A1,LargerSheet!$A:$A,1)    //row of the last date for the name.
     )<$B1,                            //Compare with SmallerSheet date
     INDIRECT("LargerSheet!$B"&        //"Then" return LargerSheet date, 
     MATCH($A1,LargerSheet!$A:$A,1)
     ),$B1)                            //"Else" return SmallerSheet date.

¿No hay una manera de agregar otra condición en INDIRECT() para que pregunte no solo si lo es < $B1sino también cuál es su distancia a la fecha <2?

Probé la siguiente fórmula sin suerte:

=IF(AND(ABS(INDIRECT("'LargerSheet'!$L"&MATCH($K2,'LargerSheet'!$B:$B,1))-$A2)<2,
INDIRECT("'LargerSheet'!$L"&MATCH($K2,'LargerSheet'!$B:$B,1))>$A2,INDIRECT("'LargerSheet'!$L"&MATCH($K2,'LargerSheet'!$B:$B,1))>$A2),INDIRECT("'LargerSheet'!$L"&MATCH($K2,'LargerSheet'!$B:$B,1)),$A2)

Puede encontrar una hoja de muestraaquí.

Respuesta1

Tuve que retroceder en tu serie de preguntas para ver dónde está el problema, pero (con sólo modificaciones menores)@La fórmula de Hannutiene bastante razón. El problema que tiene al implementarlo es que el diseño de su hoja de cálculo no coincide con lo especificado por @jbmorch en surespuesta a tu pregunta anterior.

El diseño se especificó muy claramente en la respuesta de @jbmorch y es importante por varias razones:

  • No especificó muchos detalles sobre el diseño de su hoja de cálculo en su pregunta, excepto las partes de su psuedofórmula que incluían nombres de hojas. Entonces, @jbmorch y otros tuvieron que especular y crear sus propios diseños para poder elaborar una fórmula adecuada.
  • Sin ordenar las columnas de su hoja como se especifica en la respuesta, las referencias a las columnas de la hoja no serán correctas a menos que las ajuste.
  • El orden de clasificación en LargerSheet es importante debido a la forma en que funciona MATCH: si no tiene las filas ordenadas como se especifica en la respuesta, MATCH no producirá resultados precisos.
  • La respuesta de @jbmorch también supuso (porque no se proporcionó información de lo contrario) que sus datos comenzaban en la fila 1. Por lo tanto, su respuesta se escribió para dicha hoja de cálculo y producirá resultados erróneos si no se ajusta al diseño real.

Un par de otros posibles problemas a tener en cuenta:

  • Debe asegurarse de que todas las entradas de fecha y hora estén realmente formateadas como fechas y horas; de lo contrario, Excel no podrá realizar comparaciones adecuadas. Esto se puede verificar en las opciones de formato en las propiedades de la celda y también probando matemáticas con la celda. (por ejemplo: si A2 contiene 1/7/2003y B2 es =A2+2, entonces el valor de B2 debería resolverse en 1/9/2003).
  • Su hoja de muestra contiene algunas entradas en la Hoja1 que realmente coinciden con los criterios que está buscando para devolver un valor de LargerSheet. Esto hace que la resolución de problemas sea un poco complicada. De hecho, la única coincidencia que encontré fue la fila 9. (La fecha de DIARIO, JIM en esa fila en la Hoja 1 en realidad estaba dentro de los dos días de la última fecha para él en LargerSheet).

Dicho esto, aquí tienes la fórmula que necesitas. Suéltelo en C2 en la Hoja1 y cópielo:

=IF(AND(INDIRECT("LargerSheet!$B"&MATCH($A2,LargerSheet!$A:$A,1))>B2,INDIRECT("LargerSheet!$B"&MATCH($A2,LargerSheet!$A:$A,1))<B2+2),INDIRECT("LargerSheet!$B"&MATCH($A2,LargerSheet!$A:$A,1)),$B2)

Nuevamente, preste mucha atención a que su hoja esté formateada.exactamentecomo se muestra a continuación; de lo contrario, la fórmula no funcionará sin ajustes.

  • Todas las fechasdebetener el formato de fechas, no de texto ni de números.
  • ambas hojasdebetener nombres en la columna A y fechas en la columna B, con datos reales comenzando en la fila 2.
  • Hoja más grandedebeordenarse tanto por Nombre (Ascendente) como por Fecha (Ascendente), con prioridad de clasificación establecidaen ese orden.

Otra cosa a tener en cuenta es la distinción entre "dentro de 2 días" y "dentro de 48 horas"; esta fórmula utiliza este último. Es decir, si un tiempo está 5/6/2012 03:00:00en la Hoja1 y el valor correspondiente en Hoja más grande es 5/8/2012 03:00:01entonces la declaración IF se evaluará como FALSO y devolverá el valor de la Hoja1 en lugar del de Hoja más grande. Se necesitarían modificaciones sustanciales a la fórmula si desea hacer coincidir "cualquier momento futuro durante los próximos dos días" en lugar de "cualquier momento dentro de las 48 horas".

Además, debido a que la declaración IF utiliza un mayor que ( >) exclusivo en lugar de un mayor o igual que, los tiempos que coincidan exactamente harán que se evalúe como FALSO. Si desea que evalúe TRUE para coincidencias exactas, reemplácelo >con >=.

Respuesta2

NOTA AQUÍ: Solo estoy viendo su IF() y cambiando la declaración de condición.
Si esto no funciona para usted, al menos podría darle una idea de cómo escribirlo/cambiarlo.

=SI(
     AND(INDIRECT("LargerSheet!$B"& //Celda que comienza con "$B" y termina con
     MATCH($A1,LargerSheet!$A:$A,1) //fila de la última fecha del nombre.
     )<$B1, //Comparar con la fecha de SmallerSheet
     INDIRECT("LargerSheet!$B"& //Celda que comienza con "$B" y termina con
     MATCH($A1,LargerSheet!$A:$A,1) //fila de la última fecha del nombre.
     )<($B1+2)), //Comparar con SmallerSheet (fecha-2)
     INDIRECT("LargerSheet!$B"& //"Entonces" devuelve la fecha de LargerSheet,
     COINCIDIR($A1,Hoja más grande!$A:$A,1)
     ),$B1)

... la incorporación importante está AND( condition1, condition2 )entre IF(y la primera ,para la IF.

información relacionada