Como mapear uma data para um intervalo de datas no excel?

0

Eu tenho 2 folhas no excel.

Um campo tem campos de data.

por exemplo, A-1 Data1

2ª folha Eu tenho um intervalo de datas com IDs.

A-1 StartDate A2 - EndDate ID do intervalo de datas A3

Eu preciso criar um novo campo na primeira planilha que conterá o ID do intervalo da Data1.

Por exemplo, na 2ª folha, tenho faixa de A1 a 1 de maio de 2017 A2 a 30 de maio de 2017 A3-7

Na primeira folha, A1 a 3 de maio de 2017. Por isso, quero ter uma nova coluna na minha primeira planilha que terá o valor 7.

Como posso fazer isso no excel?

    
por Boris The Animal 02.09.2017 / 14:11

1 resposta

2

Como já foi afirmado por @Ron Rosenfeld, se o intervalo na Planilha2 incluir todas as datas da Planilha1 e não houver sobreposição, o simples VLOOKUP deverá funcionar. No entanto, se a Variação de Data na Planilha2 não estiver leste-selvagem em ordem crescente e com intervalos entre e possivelmente se sobreponham. Experimente esta solução. A limitação é que ele retornará o primeiro ID de intervalo para o intervalo de datas em que os dados da Folha1 estão dentro do intervalo.

Neste exemplo, Sheet1 tem datas na coluna A1: A10. Planilha2 tem data de início em A1: A7 Data final em B1: B7 e ID de intervalo em C1: C7.

Na Folha1, a célula B1 coloca a seguinte fórmula de matriz.

=IF(ISERROR(INDEX(Sheet2!$C$1:$C$7,MATCH(1,IF(A1>=Sheet2!$A$1:$A$7,IF(A1<=Sheet2!$B$1:$B$7,1,0)),0))),"Not Found",INDEX(Sheet2!$C$1:$C$7,MATCH(1,IF(A1>=Sheet2!$A$1:$A$7,IF(A1<=Sheet2!$B$1:$B$7,1,0)),0)))

Depois disso, pressione CTRL + SHIFT + ENTER para torná-lo uma fórmula de matriz. Agora você verá a fórmula envolvida em chaves na barra de endereço. Agora arraste-o para as linhas desejadas. Agora, a Coluna B buscará os respectivos IDs de Intervalo da Folha2, onde a data na Coluna A está no intervalo.

A fórmula é baseada na verificação de IFs aninhados para retornar uma matriz de TRUE, FALSE com base no intervalo correspondente e usar um INDEX-MATCH externo para buscar a primeira posição de correspondência 1 e retornar o valor nessa posição da coluna Range ID .

    
por 02.09.2017 / 22:03