Somar valores por ano no Excel

Suponha que tem um conjunto de dados com uma lista de datas e respetivos montantes de vendas e pretende obter o valor total com base em anos específicos, tal como ilustrado na imagem seguinte. No Excel, as funções **SOMASES** e **SOMARPRODUTO** permitem-lhe resolver esta tarefa de forma rápida e fácil. Este tutorial mostra-lhe, passo a passo, como utilizar estas duas funções.

• Somar valores por ano no Excel com as funções SOMASES e DATA
• Somar valores por ano no Excel com a função SOMARPRODUTO

Somar valores por ano no Excel com as funções SOMASES e DATA

Para somar o valor total com base num determinado ano, especifique a primeira e a última data desse ano utilizando a função DATA e, em seguida, use a função SOMASES para somar todos os valores dentro da data especificada. A sintaxe genérica é:

• sum_range: O intervalo de valores a somar,
• date_range: O intervalo de datas a testar relativamente ao ano específico,
• ano: O ano específico com base no qual pretende somar os valores. ()ano,1,1 indica o primeiro dia do ano e ano,12,31 indica o último dia do ano.)

Para resolver esta tarefa no Excel, copie ou introduza a fórmula seguinte numa célula vazia e, depois, arraste a fórmula para outras células para obter o resultado:

Explicação desta fórmula:

=SUMIFS($C$2:$C$12,$B$2:$B$12,«>,=»&,DATE(E2,1,1),$B$2:$B$12,"<="&,DATE(E2,12,31))

• $C$2:$C$12: é o «intervalo_soma» que contém os valores que queremos somar.
• $B$2:$B$12,«>=»&DATE(E2,1,1): verifica as datas no intervalo B2:B12 que são maiores ou iguais a 01/01/2020 – o primeiro dia do ano indicado na célula E2.
• $B$2:$B$12,«<=»&DATE(E2,12,31): verifica as datas no intervalo B2:B12 que são menores ou iguais a 31/12/2020 – o último dia do ano indicado na célula E2.
• SUMIFS($C$2:$C$12,$B$2:$B$12,«>=»&DATE(E2,1,1),$B$2:$B$12,"<="&DATE(E2,12,31)): Após os critérios serem cumpridos, os valores no intervalo C2:C12 serão identificados e somados.

Somar valores por ano no Excel com a função SOMARPRODUTO

Aqui, outra fórmula simples criada com a função SOMARPRODUTO também o pode ajudar. A sintaxe genérica é:

• date_range: O intervalo de datas a testar relativamente ao ano específico;
• year: O ano específico com base no qual pretende somar os valores;
• sum_rangeO intervalo de valores a somar.

Copie ou introduza a seguinte fórmula numa célula vazia e, depois, arraste a fórmula para outras células para obter o resultado:

Explicação desta fórmula:

=SUMPRODUCT((YEAR($B$2:$B$12)=E2)*$C$2:$C$12)

• YEAR($B$2:$B$12)=E2: A função ANO devolve o ano correspondente a cada célula com data no intervalo B2:B12. Esta expressão compara o ano em E2 com os anos desse intervalo, devolvendo um conjunto como este: {VERDADEIRO;FALSO;VERDADEIRO;VERDADEIRO;FALSO;VERDADEIRO;VERDADEIRO;FALSO;FALSO;VERDADEIRO;FALSO}.
• (YEAR($B$2:$B$12)=E2)*$C$2:$C$12={VERDADEIRO;FALSO;VERDADEIRO;VERDADEIRO;FALSO;VERDADEIRO;VERDADEIRO;FALSO;FALSO;VERDADEIRO;FALSO}*$C$2:$C$12: Nesta expressão, os valores VERDADEIRO e FALSO são convertidos respetivamente em 1 e 0, e este conjunto é multiplicado por cada valor no intervalo C2:C12, resultando num novo conjunto como este: {347;0;398;430;0;379;412;0;0;420;0}.
• SOMARPRODUTO((ANO($B$2:$B$12)=E2)*$C$2:$C$12)= SOMARPRODUTO({347,0,398,430,0,379,412,0,0,420,0}): A função SOMARPRODUTO soma, por fim, todos os números deste conjunto e devolve o resultado 2386.

Função relacionada utilizada:

Mais artigos: