☰

Buscar e insertar datos según el rango de fechas

tag-icon • tag-icon microsoft-excel worksheet-function
Buscar e insertar datos según el rango de fechas

Enotra publicación, se proporcionó una fórmula destinada a ayudar a buscar e insertar una entrada si la fecha asociada con la entrada era menor que la fecha en otra hoja. La fórmula fue la siguiente:

=IF(

    INDEX([LargerSheet!*Range with Dates*],

       MATCH(

         IF([SmallerSheet!*First Date in Range*]<[LargerSheet!*First Date in Range],
         [SmallerSheet!*Range with Dates]),

       [SmallerSheet!*Range with Dates*],0)

    )>0,1,"")

Sin embargo, esto no funcionó como se esperaba. Me pregunto si podría ser que mi problema sea ligeramente diferente. Básicamente, lo que me gustaría hacer es, para una entrada determinada en SmallerSheet, verificar las fechas asociadas con múltiples objetos en LargerSheet y devolver la fecha más reciente de LargerSheet solo si Fecha más reciente <Fecha de SmallerSheet. Creo que lo que la fórmula anterior está haciendo en este momento es verificar si la fecha de SmallerSheet es más reciente que al menos una de las fechas en LargerSheet, lo que da como resultado que la declaración siempre sea verdadera porque cada entrada de SmallerSheet tiene múltiples entradas asociadas en el LargerSheet se remonta muy atrás en el tiempo. ¿Es esta una solución fácil?

Respuesta1

Suponiendo que pueda ordenar los datos en su LargerSheet, puede resolver su problema de esta manera:

Primero haga una clasificación personalizada en LargerSheet; ordenar primero por Nombre (AZ) y segundo por Fecha (De más antiguo a más nuevo). Ahora todas las entradas del mismo nombre están agrupadas y la última entrada de cada grupo es la fecha más reciente de ese nombre.

--A-- --B-- Alice 2003-08-20 Alice 2005-01-01 Alice 2006-05-16 Bob 2001-08-19 Bob 2003-01-01 Bob 2004-05-15 Charlie 2004-08-19 : :

Luego, en SmallerSheet, en una columna al lado de cada nombre, use la siguiente fórmula (asumiendo que, como en LargerSheet, el nombre está en la columna A y la fecha en la columna B). Elimina los espacios en blanco y los comentarios.

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

Esto debería devolver la fecha más reciente de LargerSheet si es menor que la fecha de SmallerSheet (para cada nombre), o la fecha de SmallerSheet si no.

Si es necesario poner más condiciones en la selección de fecha, intente utilizar las funciones lógicas "Y" y "O", o cambie la prueba en sí. Por ejemplo, la siguiente modificación de la fórmula devolverá la fecha más reciente de LargerSheet siempre que esté dentro de -2 días de la fecha en SmallerSheet, Y también siempre que el valor en la columna C de LargerSheet sea mayor que cero. . Como puede ver, la fórmula comienza a parecer difícil de manejar, por lo que se debe tener cuidado de hacer coincidir los paréntesis y verificar la sintaxis. Puede resultar útil utilizar varias columnas para dividir la fórmula en etapas.

=IF(AND(INDIRECT("LargerSheet!$B"&MATCH($A1,LargerSheet!$A:$A,1))>=($B1-2),INDIRECT("LargerSheet!$B"&MATCH($A1,LargerSheet!$A:$A,1))<$B1,INDIRECT("LargerSheet!$C"&MATCH($A1,LargerSheet!$A:$A,1))>0),INDIRECT("LargerSheet!$B"&MATCH($A1,LargerSheet!$A:$A,1)),$B1)

Finalmente, puede abordar este problema de otra manera: agregue una columna a LargerSheet que realice una búsqueda en la entrada única del nombre en SmallerSheet, realice una prueba y devuelva un valor VERDADERO/FALSO basado en la prueba.

Respuesta2

Voy a darte una fórmula que funciona de manera diferente a la que enumeraste, ya que MATCH devuelve la primera función.

Esta fórmula es una fórmula matricial, por lo que se ingresa usandoCTRL+MAYÚS+ENTRAR.

Esto supone que su tabla grande está en la columna A. La fecha que desea comparar está en la celda E2.

=MAX(IF($A$1:$A$33<E2,$A$1:$A$33,0))

Para cada celda de la tabla grande, realiza una declaración if. Si la celda es menor que E2, devuelve su fecha (que Excel almacena como un número) si la celda es mayor que E2, devuelve 0. Al tomar el máximo de todas estas declaraciones if, devuelve la fecha más grande que es menor que E2.

información relacionada