Excel: Posso fazer sumários com cálculos?

Question

Estou fazendo alguns recálculos no Excel em uma longa lista para prepará-lo para análise estatística em outros programas e estou tendo problemas com uma coisa que quero fazer.

Eu tenho uma lista de algumas variáveis, entre elas um identificador, uma variável de data e uma variável numérica. Eu preciso adicionar uma variável que soma a variável numérica de todas as instâncias que estão dentro de 365 dias da instância com o mesmo identificador. Isso é relativamente fácil factível com sumifs (). Mas eu também quero adicionar um peso ao valor numérico para instâncias que têm um valor de data próximo ao valor de hoje têm um peso maior do que aqueles mais para trás no tempo (ontem 364/365 de peso, um mês atrás 335/365, amanhã um ano ago 1/365 etc.

Existe alguma maneira de fazer isso com funções?

#     A         B           C         D
1    ID#    datenumber    value    newvar
2    Ken    01-01-2015    1000     somevalue
3    Ida    01-01-2014    1000     somevalue
4    Ida    01-06-2015    1234     somevalue
5    Ida    01-10-2015    5678     somevalue
6    Ida    01-12-2015    1000     y


y = weighted sum of all values of all Ida within one year, line 3 and 4 above.

The newvar sumifs y would be (if "ID#" was in cell A1):

=SUMIFA(C:C,A:A,A6,B:B,"<"&B6,B:B,">"&B6-365) or 1234+5678 = 6912

The "weighted sumifs" would be y = (182/365*1234)+(304/365*5678) = 5344.38

Eu acho que eu poderia fazer uma nova coluna com o valor ponderado em uma data específica, fazer o newvar para todas as instâncias com esse datenumer, copiar os valores no lugar e depois passar um dia para cima e repeti-lo com as instâncias com esse valor de dados etc. através do VBA, mas esse tipo de hackeria realmente limitaria a usabilidade do livro de trabalho em outras áreas. Então, espero que alguém tenha uma solução com funções de célula em vez de VBA.

    

Answer 1

Droga, isso foi muito mais fácil do que eu imaginava. O Sumproduct permite que você faça manipulações de array nos valores individuais antes de adicioná-los

=SUMPRODUCT((A2:A6=A6)*(B2:B6<B6)*(B2:B6>B6-365)*C2:C6*((B2:B6)-(B6-365))/365)

Os (A2: A6 = A6) * (B2: B6 < B6) * (B2: B6 & Bt; B6-365) s as condies

Os C2: C6 são os valores que devem ser considerados (se satisfizerem as condições acima)

O ((B2: B6) - (B6-365)) / 365 é o peso que deve ser adicionado aos valores que satisfizeram as condições