Pesquisando e inserindo dados com base no intervalo de datas

Pesquisando e inserindo dados com base no intervalo de datas

Emoutra postagem, foi fornecida uma fórmula destinada a ajudar a pesquisar e inserir uma entrada se a data associada à entrada fosse menor que a data em outra planilha. A fórmula foi a seguinte:

=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,"")

No entanto, isso não funcionou como desejado. Estou me perguntando se meu problema pode ser um pouco diferente. Essencialmente, o que eu gostaria de fazer é, para uma determinada entrada no SmallerSheet, verificar as datas associadas a vários objetos no LargerSheet e retornar a data mais recente do LargerSheet somente se Data mais recente <Data do SmallerSheet. Acho que o que a fórmula acima está fazendo agora é verificar se a data do SmallerSheet é mais recente do que pelo menos uma das datas do LargerSheet, o que faz com que a afirmação seja sempre verdadeira porque cada entrada do SmallerSheet tem várias entradas associadas a ela no LargerSheet remontando muito atrás no tempo. isso é fácil de consertar?

Responder1

Supondo que você possa classificar os dados em sua LargerSheet, você pode resolver seu problema assim:

Primeiro faça uma classificação personalizada em LargerSheet; classifique primeiro por Nome (AZ) e depois por Data (do mais antigo para o mais recente). Agora todas as entradas com o mesmo nome são agrupadas e a última entrada em cada grupo é a data mais recente desse nome.

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

Depois, na SmallerSheet, numa coluna ao lado de cada nome, utilize a seguinte fórmula (assumindo que, como na LargerSheet, o nome está na coluna A e a data na coluna B). Retire o espaço em branco e os comentários.

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

Isso deve retornar a data mais recente de LargerSheet se for menor que a data de SmallerSheet (para cada nome) ou a data de SmallerSheet se não for.

Caso seja necessário colocar mais condições na seleção da data, experimente utilizar as funções lógicas "AND" e "OR", ou altere o próprio teste. Por exemplo, a seguinte modificação na fórmula retornará a data mais recente de LargerSheet, desde que esteja dentro de -2 dias da data em SmallerSheet, E também desde que o valor na coluna C de LargerSheet seja maior que zero . Como você pode ver, a fórmula começa a parecer complicada, portanto, deve-se tomar cuidado para combinar parênteses e verificar a sintaxe. Usar várias colunas para dividir a fórmula em etapas pode ajudar.

=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, você pode abordar esse problema de outra maneira: adicione uma coluna a LargerSheet que faça uma pesquisa na entrada única do nome em SmallerSheet, faça um teste e retorne um valor VERDADEIRO/FALSO com base no teste.

Responder2

Vou lhe dar uma fórmula que funciona de maneira diferente daquela que você listou, já que MATCH retorna a primeira função.

Esta fórmula é uma fórmula de matriz, portanto é inserida usandoCTRL+SHIFT+ENTER.

Isso pressupõe que sua tabela grande esteja na coluna A. A data que você deseja comparar está na célula E2.

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

Para cada célula da tabela grande, ele executa uma instrução if. Se a célula for menor que E2, ela retorna sua data (que o Excel armazena como um número). Se a célula for maior que E2, ela retorna 0. Tomando o máximo de todas essas instruções if, ela retorna a maior data que é menor que E2.

informação relacionada