Procurando um valor, dependendo de qual conjunto de datas outra data fica entre

Question

e desculpas se isso é uma duplicata - se você pudesse me apontar na direção de quaisquer respostas existentes, isso seria ótimo.

Eu tenho um conjunto de intervalos de datas no Excel, cada um deles com algum tipo de rótulo. por exemplo,

LabelA  01/01/10  31/01/10
LabelB  01/02/10  28/02/10
LabelC  01/03/10  31/03/10

Se eu tiver outro encontro, gostaria de procurar o rótulo relacionado ao período dentro do qual essa data se encontra. por exemplo. Para 15/02/10, gostaria de devolver o LabelB. Eu sei que os intervalos de datas não se sobrepõem, embora possa haver um intervalo entre a data final de um e a data de início do próximo.

    

Answer 1

Você pode criar uma fórmula de matriz, que compara cada período com a data exigida. Se a data for o intervalo, ela retornará a data final.

Em seguida, faça uma correspondência nesta data de término para encontrar a linha apropriada.

Isso tudo pode ser feito em uma única célula, conforme necessário.

Se os dados estiverem em A1: C3, e a data requerida estiver em B4, então esta fórmula:

=INDEX(A1:A3,MATCH(SUM(IF(B1:B3<B4,1,0)*IF(C1:C3>=B4,C1:C3,0)),C1:C3,0))

Deve fazer o que quiser. Isso precisa ser inserido como uma fórmula de matriz.

Se a data não estiver coberta em nenhum dos intervalos, ela retornará # N / A

    

Answer 2

Você pode adicionar uma duplicata (talvez oculta) de columnA no lado direito (ou mover a coluna A) e as datas estão em ordem?

Nesse caso, a função VLOOKUP pode ser a resposta. Ele procura um valor na primeira coluna e examina várias colunas. Aqui está um exemplo.

Eu percebo que isso pode ser um pouco desagradável, mas é fácil e, desde que os dados estejam em ordem, você deve ter o que deseja.

    

Answer 3

Você pode fazer uma soma de várias condições usando sumproduct . Você usaria (digamos que Data1 é B: B, Data2 é C: C, E1 é a data que você quer encontrar)

sumproduct(--(B1:B50<=E1),--(C1:C50>=E1),Row(B1:B50))

Isso lhe dará o número da linha, já que essa soma de múltiplas condições é verdadeira somente quando E1 é > = B e < = C e irá somar o número da linha de todas as linhas onde isso é verdade (o que você disse é no máximo 1). Para pesquisar o marcador, você pode:

index(A1:A50,sumproduct(--(B1:B50<=E1),--(C1:C50>=E1),Row(B1:B50)))

Além disso, se você acha que seu número pode estar em uma das lacunas que você poderia fazer:

if(sumproduct(--(B1:B50<=E1),--(C1:C50>=E1),Row(B1:B50))=0,"Date Not Found",index(A1:A50,sumproduct(--(B1:B50<=E1),--(C1:C50>=E1),Row(B1:B50))))

    

Answer 4

Para mim, isso parece exatamente o que Formatação condicional foi criado para. Você pode definir até 3 condições distintas para uma célula ou intervalo de células e, quando uma das condições corresponder à célula, obterá a formatação aplicada por essas condições.

É como muitas planilhas do tipo "semáforo" são criadas.