Pesquisando e inserindo dados com base no período

Question

Em outro post , foi dada uma fórmula que significava para ajudar a pesquisar e inserir uma entrada se a data associada à entrada for 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. Eu estou querendo saber se isso pode ser apenas que o meu problema é um pouco diferente. Essencialmente, o que eu gostaria de fazer é, para uma determinada entrada no SmallerSheet, verificar as datas associadas a vários objetos na LargerSheet e retornar a data mais recente da LargerSheet somente se a data mais recente < Data do SmallerSheet. Acho que o que a fórmula acima está fazendo agora é verificar se a data da SmallerSheet é mais recente do que pelo menos uma das datas na LargerSheet, o que resulta na declaração sempre sendo verdadeira porque cada entrada da SmallerSheet tem várias entradas associadas a ela. LargerSheet vai muito longe no tempo. Isso é uma solução fácil?

    

Answer 1

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

Primeiro, faça uma classificação personalizada no LargerSheet; classifique primeiro por Nome (A-Z) e depois por Data (Mais Antiga para Mais Recente). Agora, todas as entradas de mesmo nome são agrupadas e a última entrada em cada grupo é a data mais recente para esse 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 : :

Em seguida, na SmallerSheet, em uma coluna ao lado de cada nome, use a seguinte fórmula (supondo que, como em LargerSheet, o nome esteja na coluna A e a data na coluna B). Retire o espaço em branco e comente.

=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 da LargerSheet se ela for menor que a data da SmallerSheet (para cada nome) ou a data da SmallerSheet, se não.

Se for necessário colocar mais condições na seleção de data, tente usar as funções lógicas "AND" e "OR" ou altere o teste em si. Por exemplo, a seguinte modificação na fórmula retornará a data mais recente de LargerSheet, contanto que esteja dentro de -2 dias da data no 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 pesada, portanto, deve-se tomar cuidado para corresponder parênteses e verificar a sintaxe. Usar várias colunas para dividir a fórmula em estágios 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)

Por fim, você pode abordar esse problema da seguinte maneira: Adicione uma coluna ao LargerSheet que faça uma pesquisa na única entrada do nome em SmallerSheet, faça um teste e retorne um valor VERDADEIRO / FALSO com base no teste.

    

Answer 2

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

Esta fórmula é uma fórmula de matriz que é inserida usando CTRL + 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 na tabela grande, ela faz uma instrução if. Se a célula for menor que E2, ela retornará sua data (que o Excel armazena como um número) se a célula for maior que E2, ela retornará 0. Tomando o máximo de todas essas instruções if, retorna a maior data que é menor que E2.