☰

Indexando um intervalo de datas se a data for maior que e dentro de n dias de outra data

tag-icon • tag-icon microsoft-excel worksheet-function
Indexando um intervalo de datas se a data for maior que e dentro de n dias de outra data

Em umpostagem anteriorFiz uma pergunta sobre como encontrar a data mais recente associada a uma entidade específica e fazer com que esse valor seja retornado na Planilha2, desde que seja maior que a data associada na Planilha2. Percebi, no entanto, que o verdadeiro problema não é retornar a data mais recente se a condição for atendida, mas retornar a data se for maior e dentro de 2 dias (ou geralmente n dias) da data de referência.

A fórmula produzida anteriormente era a seguinte:

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

Não há uma maneira de adicionar outra condição em INDIRECT() para que ele pergunte não apenas se é, < $B1mas também se sua distância até a data é <2?

Tentei a seguinte fórmula sem sorte:

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

Uma folha de amostra pode ser encontradaaqui.

Responder1

Tive que voltar atrás na sua série de perguntas para ver onde está o problema, mas (com apenas pequenas modificações)Fórmula de @Hannuestá bastante certo. O problema que você está enfrentando ao implementá-lo é que o layout da sua planilha não corresponde ao que foi especificado por @jbmorch em seuresponda à sua pergunta anterior.

O layout foi especificado claramente na resposta do @jbmorch e é importante por alguns motivos:

  • Você não especificou muitos detalhes sobre o layout da sua planilha em sua pergunta, exceto as partes de sua psuedo-fórmula que incluíam nomes de planilhas. Então, @jbmorch e outros tiveram que especular e criar seus próprios layouts para montar uma fórmula apropriada.
  • Sem ordenar as colunas da planilha conforme especificado na resposta, as referências às colunas da planilha não estarão corretas, a menos que você as ajuste.
  • A ordem de classificação em LargerSheet é importante devido à maneira como MATCH funciona - se você não tiver as linhas classificadas conforme especificado na resposta, MATCH não produzirá resultados precisos.
  • A resposta de @jbmorch também presumiu (porque nenhuma informação foi fornecida de outra forma) que seus dados começaram na linha 1. Portanto, a resposta deles foi escrita para essa planilha e produzirá resultados errados se não for ajustada para o layout real.

Alguns outros possíveis problemas a serem observados:

  • Você precisa ter certeza de que todas as entradas de data/hora estão realmente formatadas como datas e horas, caso contrário, o Excel não poderá fazer comparações adequadas. Isso pode ser verificado nas opções de formato nas propriedades da célula e também tentando matemática na célula. (por exemplo: se A2 contiver 1/7/2003e B2 for =A2+2, então o valor de B2 deverá ser resolvido 1/9/2003).
  • Sua planilha de amostra contém poucas entradas em Sheet1 que realmente correspondem aos critérios que você está procurando para retornar um valor de LargerSheet. Isso torna a solução de problemas um pouco problemática. Na verdade, a única correspondência que encontrei foi a linha 9. (A data para DAILY, JIM naquela linha na Planilha1 estava na verdade dentro de dois dias da data mais recente para ele na LargerSheet.)

Dito isto, aqui está a fórmula que você precisa. Solte-o em C2 na Planilha1 e copie:

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

Novamente, preste muita atenção para que sua planilha esteja formatadaexatamenteconforme abaixo, caso contrário a fórmula não funcionará sem ajustes.

  • Todas as datasdeveser formatados como datas - não como texto ou números.
  • Ambas as folhasdevetêm nomes na coluna A e datas na coluna B, com os dados reais começando na linha 2.
  • Folha maiordeveser classificados por Nome (Ascendente) e Data (Crescente), com prioridade de classificação definidanaquela ordem.

Outra coisa a ter em conta é a distinção entre “dentro de 2 dias” e “dentro de 48 horas” – esta fórmula utiliza o último. Ou seja, se um horário estiver 5/6/2012 03:00:00em Sheet1 e o valor correspondente em LargerSheet estiver, 5/8/2012 03:00:01a instrução IF será avaliada como FALSE e retornará o valor de Sheet1 em vez daquele de LargerSheet. Seriam necessárias modificações substanciais na fórmula para contabilizar se você deseja corresponder "qualquer horário futuro nos próximos dois dias" em vez de "qualquer horário dentro de 48 horas".

Além disso, como a instrução IF usa um valor exclusivo maior que ( >) em vez de maior que ou igual a, tempos que correspondam exatamente farão com que ela seja avaliada como FALSO. Se você quiser avaliar TRUE para correspondências exatas, substitua >por >=.

Responder2

NOTA AQUI: estou apenas visualizando seu IF() e alterando a instrução de condição.
Se isso não funcionar para você - pode pelo menos dar uma idéia de como escrevê-lo/alterá-lo.

=SE(
     AND(INDIRECT("LargerSheet!$B"& //Célula começando com "$B" e terminando com
     MATCH($A1,LargerSheet!$A:$A,1) //linha da última data do nome.
     )<$B1, //Comparar com data da Folha Menor
     INDIRETO("LargerSheet!$B"& //Célula começando com "$B" e terminando com
     MATCH($A1,LargerSheet!$A:$A,1) //linha da última data do nome.
     )<($B1+2)), //Comparar com SmallerSheet (data-2)
     INDIRETO("LargerSheet!$B"& //"Então" retorna a data da Folha Maior,
     MATCH($A1,Planilha Maior!$A:$A,1)
     ),$B1)

... a adição importante é AND( condition1, condition2 )a IF(primeira ,para o FI.

informação relacionada