preenche valores no excel de acordo com datas

Question

Eu tenho um desafio no Excel 2010. Eu tenho uma lista de IDs com as seguintes variáveis: issue_date, valor cumulativo, data de vencimento. Cada ID tem data de emissão e data de vencimento diferentes, mas todas estão no intervalo (período de análise) entre 2 de janeiro de 2007 e 23 de dezembro de 2011. Um pequeno exemplo abaixo:

O id tem 3 valores diferentes: um para 2 de janeiro de 2007 (valor: 1000); outro para 5 de maio de 2007 (valor: 1500) e o último para 4 de dezembro de 2007 (valor: 2700). O que eu quero é que esse id tenha os valores cumulativos duplicados pela duração em que estava ativo.

Assim, neste caso, durante o período de 2 de janeiro de 2007 a 4 de maio de 2007, o valor será (1000); então, para o período de 5 de maio de 2007 a 3 de dezembro de 2007, o valor será de 1500; finalmente, para o período entre 4 de dezembro de 2007 até a data de vencimento (digamos 3 de março de 2010), o valor permanecerá em 2700.

Este é um desafio difícil para mim porque tenho 355 ids diferentes. Alguns IDs possuem valores exclusivos, o que significa que seu valor cumulativo será replicado até as datas de vencimento. No entanto, existem outros IDs que possuem valores cumulativos diferentes (em datas diferentes) que devem ser organizados corretamente.

O resultado final será um conjunto de datas consecutivas entre 2 de janeiro de 2007 e 23 de dezembro de 2011 (em colunas) e, em seguida, 355 linhas de dados alocados correspondentemente. Posso enviar um breve exemplo no Excel se for necessário ter uma ideia mais clara da solicitação.

    

Answer 1

Para fazer isso, você precisa massagear seus dados antes de criar sua matriz.

1. Classifique seus dados em ID e issue_date (A a Z) nessa ordem ou prioridade.

2. Adicione uma coluna end_date . O valor de end_date será o mais cedo da data de vencimento desse registro e um dia antes da próxima issue_date para esse ID específico. Assumindo que ID esteja na coluna A, issue_date esteja na coluna B e maturity_date esteja na coluna D, insira a seguinte fórmula para o end_date do primeiro registro. Preencher.

   =TEXT(IF(A3=A2,MIN(B3-1,D2),D2),"mmm d, yyyy")

   

3. Emoutraplanilha,crieaestruturaparasuamatriz.Crierótulosdecolunainserindoaprimeiradatae,emseguida,preenchendotodoocaminhoatéadatafinal.VocêpodeobterseusrótulosdelinhafazendoumfiltroavançadopararegistrosexclusivosnosIDsemsuatabela.

4. Nacélulasuperioresquerdadasuamatriz(B3nesteexemplo),insiraaseguintefórmula.'Raw'éonomedaplanilhacomseusdadosoriginais.

   =SUMPRODUCT(Raw!$C$2:$C$23,--(Raw!$A$2:$A$23=Matrix!$A3),--(DATEVALUE(Raw!$B$2:$B$23)<=Matrix!B$2),--(DATEVALUE(Raw!$E$2:$E$23)>=Matrix!B$2))

   Preenchaporcimaeporbaixoparapreenchersuamatriz.IssoretornaráovalordoIDnessadatae0senãohouverdadosdisponíveisparaessadata.

SUMPRODUCT () é uma das funções menos intuitivas, mas mais úteis no Excel. Basicamente, o que ele faz é multiplicar os valores correspondentes de cada argumento da matriz e, em seguida, resume esses produtos. Aqui, os arrays são os valores cumulativos da tabela e arrays de 0s e 1s que indicam se um critério foi ou não atendido. Portanto, somente o valor pelo qual todos os critérios são atendidos terá um produto diferente de zero. Como apenas um registro deve atender a todos os critérios, a soma será apenas esse valor.

Estou usando datas como texto aqui, mas essas fórmulas também devem funcionar para datas formatadas como datas no Excel. Os TEXT() e DATEVALUE() bits são exagerados nesse caso, mas tudo deve funcionar da mesma forma.