Somar valores por ano no Excel
Suponha que você tenha um intervalo de dados que contém uma lista de datas e valores de vendas, e agora deseja obter o valor total com base em anos específicos, conforme mostrado na captura de tela abaixo. No Excel, as funções normais SOMASES e SOMARPRODUTO podem ajudar a resolver essa tarefa rapidamente e facilmente. Este tutorial demonstrará como usar essas duas funções em detalhes.
- 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 em um determinado ano, você deve especificar a primeira e a última data do ano usando a função DATA, e depois usar a função SOMASES para somar todos os valores dentro das datas especificadas. A sintaxe genérica é:
- intervalo_soma: O intervalo de valores que serão somados;
- intervalo_datas: O intervalo de datas a serem testadas para o ano específico;
- ano: O ano específico para o qual você deseja 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 insira a fórmula abaixo em uma célula em branco e arraste a fórmula para outras células para obter o resultado:
Explicação desta fórmula:
=SOMASES($C$2:$C$12,$B$2:$B$12,">="&DATA(E2,1,1),$B$2:$B$12,"<="&DATA(E2,12,31))
- $C$2:$C$12: é o “intervalo_soma” que contém os valores que queremos somar.
- $B$2:$B$12,">="&DATA(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 na célula E2.
- $B$2:$B$12,"<="&DATA(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 na célula E2.
- SOMASES($C$2:$C$12,$B$2:$B$12,">="&DATA(E2,1,1),$B$2:$B$12,"<="&DATA(E2,12,31)): Após atender aos critérios, os valores no intervalo C2:C12 serão encontrados e somados.
Somar valores por ano no Excel com a função SOMARPRODUTO
Aqui, outra fórmula simples criada pela função SOMARPRODUTO também pode ajudá-lo, a sintaxe genérica é:
- intervalo_datas: O intervalo de datas a serem testadas para o ano específico;
- ano: O ano específico para o qual você deseja somar os valores;
- intervalo_soma: O intervalo de valores a serem somados.
Por favor, copie ou insira a seguinte fórmula em uma célula em branco e arraste a fórmula para outras células para obter o resultado:
Explicação desta fórmula:
=SOMARPRODUTO((ANO($B$2:$B$12)=E2)*$C$2:$C$12)
- ANO($B$2:$B$12)=E2:A função ANO retorna o número do ano de cada célula de data no intervalo B2:B12. Esta expressão verifica se o ano em E2 é igual ao ano no intervalo B2:B12. E você obterá uma matriz assim: {VERDADEIRO;FALSO;VERDADEIRO;VERDADEIRO;FALSO;VERDADEIRO;VERDADEIRO;FALSO;FALSO;VERDADEIRO;FALSO}.
- (ANO($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 VERDADEIROS e FALSOS serão forçados a 1s e 0s, e esta matriz será multiplicada por cada valor no intervalo C2:C12, e você obterá uma nova matriz assim: {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 finalmente soma todos os números nesta matriz e retorna o resultado como 2386.
Função relacionada usada:
- SOMASES:
- A função SOMASES no Excel pode ajudar a somar valores de células com base em múltiplos critérios.
- SOMARPRODUTO:
- A função SOMARPRODUTO pode ser usada para multiplicar duas ou mais colunas ou matrizes juntas e, em seguida, obter a soma dos produtos.
- DATA:
- A função DATA pode nos ajudar a combinar números de ano, mês e dia de células separadas em uma data válida.
- ANO:
- A função ANO retorna o ano com base na data fornecida em um formato de número serial de 4 dígitos.
Mais artigos:
- Somar Valores Por Mês (Com Ou Sem Ano)
- Este tutorial demonstra duas fórmulas em detalhes para ajudar a somar valores por determinado mês e ano ou somar valores por determinado mês ignorando o ano no Excel.
- Somar Valores Pela Semana De Uma Data Específica
- Para somar valores pela semana em que uma data específica cai, você pode aplicar uma fórmula baseada na função SOMASES.
- Somar Valores Por Número Da Semana Ou Dia Da Semana
- Este tutorial demonstra fórmulas passo a passo para ajudá-lo a somar valores em um intervalo específico com base no número da semana ou no dia da semana especificado no Excel.
As Melhores Ferramentas de Produtividade para o Office
Kutools para Excel - Ajuda Você a Se Destacar na Multidão
Kutools para Excel Conta com Mais de 300 Funcionalidades, Garantindo Que O Que Você Precisa Está Apenas Um Clique de Distância...
Office Tab - Ative a Leitura e Edição com Guias no Microsoft Office (inclui Excel)
- Um segundo para alternar entre dezenas de documentos abertos!
- Reduz centenas de cliques de mouse para você todos os dias, diga adeus à mão do mouse.
- Aumenta sua produtividade em 50% ao visualizar e editar vários documentos.
- Traz Guias Eficientes para o Office (inclui Excel), Assim Como Chrome, Edge e Firefox.