Médias condicionais no Excel

Question

Tenho conjuntos de dados de 5000 datas desde 1995 e o preço de um bem em cada data. Eu quero calcular uma média anual para estes. Eu basicamente quero dizer ao excel para calcular a média de todas as células para o ano xxxx, média de todas as células com o ano xxxy, etc. Eu tenho brincado com if (, left (, average (, em uma nova tabela com todos os anos que eu quero encontrar médias.Eu não sou um especialista em excel.Isto é o que o conjunto de dados parece agora.Eu adoraria saber como fazer isso desde que eu preciso fazer esse tipo de coisa o tempo todo.

insira a descrição da imagem aqui

    

Answer 1

Use uma "fórmula 3D" ...
=SUM(IF(YEAR(A:A)=$D$1,B:B,0))/SUM(IF(YEAR(A:A)=$D$1,1,0))

Digite-o e segure CTRL e SHIFT enquanto pressiona ENTER (funciona tanto no Excel quanto no LibreOffice Calc)

A fórmula recebe {...} chaves nas extremidades, se você fizer isso corretamente.

Agora, insira o ano para a média em D1

Como: A:A e B:B obtém os valores na coluna inteira, mude para um intervalo mais limitado, se é isso que você tem.

Como estes são intervalos de células, CTRL + SHIFT ENTER é necessário, fazendo a fórmula percorrer todas as células do intervalo.
Como o valor do ano em D1 deve ser usado para todas as comparações, ele precisa de $ .

O primeiro IF(...) seleciona as linhas relevantes e retorna os valores correspondentes na coluna B, que são então alimentados para SUM() .

Da mesma forma, o segundo IF(...) seleciona as mesmas linhas novamente, mas retorna 1 para cada uma, compondo o COUNT de linhas, pois SUM(...) fez o trabalho.

    

Answer 2

Isso é bastante simples com uma função AVEAGEIFS implementada assim:

Liste todos os seus anos em D2 e, em seguida, em E2, use esta fórmula

=AVERAGEIFS(B:B,A:A,">="&DATE(D2,1,1),A:A,"<"&DATE(D2+1,1,1))

Copie a fórmula para todos os anos