Soma os valores por ano no Excel
Supondo que você tenha uma gama de dados que contém uma lista de datas e valores de venda, agora você deseja obter o valor total com base em determinados anos, conforme a imagem mostrada abaixo. No Excel, as funções normais de SUMIFS e SUMPRODUCT podem ajudar a resolver essa tarefa de forma rápida e fácil. Este tutorial demonstrará como usar essas duas funções em detalhes.
- Soma os valores por ano no Excel com as funções SUMIFS e DATE
- Soma os valores por ano no Excel com a função SUMPRODUCT
Soma os valores por ano no Excel com as funções SUMIFS e DATE
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 DATE e, em seguida, usar a função SUMIFS para somar todos os valores nas datas especificadas, a sintaxe genérica é:
- sum_range: O intervalo de valores que devem ser somados;
- date_range: O intervalo de datas a serem testados para o ano específico;
- year: O ano específico no 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 essa 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:
=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, "> =" & 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 12/31/2020 - o último dia do ano 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)): Depois de cumprir os critérios, os valores no intervalo C2: C12 serão encontrados e somados.
Soma os valores por ano no Excel com a função SUMPRODUCT
Aqui, outra fórmula simples que é criada pela função SUMPRODUCT também pode ajudá-lo, a sintaxe genérica é:
- date_range: O intervalo de datas a serem testados para o ano específico;
- year: O ano específico em que você deseja somar os valores;
- sum_range: O intervalo de valores a serem somados.
Copie ou insira a fórmula a seguir em uma célula em branco e 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)
- ANO ($ B $ 2: $ B $ 12) = E2: A função YEAR 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 como esta: {TRUE; FALSE; TRUE; TRUE; FALSE; TRUE; TRUE; FALSE; FALSE; TRUE; FALSE}.
- (YEAR($B$2:$B$12)=E2)*$C$2:$C$12={TRUE;FALSE;TRUE;TRUE;FALSE;TRUE;TRUE;FALSE;FALSE;TRUE;FALSE}*$C$2:$C$12: Nesta expressão, os TRUEs e FALSEs serão forçados a 1s e 0s, e esta matriz é multiplicada por cada valor no intervalo C2: C12, e você obterá uma nova matriz como esta: {347; 0; 398; 430; 0; 379; 412; 0; 0; 420; 0}.
- SUMPRODUCT((YEAR($B$2:$B$12)=E2)*$C$2:$C$12)= SUMPRODUCT({347;0;398;430;0;379;412;0;0;420;0}): A função SUMPRODUCT finalmente soma todos os números nesta matriz e retorna o resultado como 2386.
Função relativa usada:
- SUMIFS:
- A função SOMASE no Excel pode ajudar a somar os valores das células com base em vários critérios.
- SUMPRODUCT:
- A função SUMPRODUCT pode ser usada para multiplicar duas ou mais colunas ou matrizes e obter a soma dos produtos.
- INFORMAÇÃO:
- A função DATE pode nos ajudar a combinar números de ano, mês e dia de células separadas para uma data válida.
- ANO:
- A função YEAR retorna o ano com base na data fornecida em um formato de número de série de 4 dígitos.
Mais artigos:
- Soma dos 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 ignorar o ano no Excel.
- Soma dos valores pela semana de uma determinada data
- Para somar valores por semana em que cai uma determinada data, você pode aplicar uma fórmula baseada na função SOMASE.
- Soma dos valores por semana ou dia da semana
- Este tutorial demonstra fórmulas passo a passo para ajudá-lo a somar valores em um determinado intervalo com base no número da semana ou dia da semana especificado no Excel.
As melhores ferramentas de produtividade para escritório
Kutools para Excel - ajuda você a se destacar na multidão
Kutools para Excel possui mais de 300 recursos, Garantindo que o que você precisa está a apenas um clique de distância...
Guia Office - Habilite leitura e edição com guias no Microsoft Office (inclui Excel)
- Um segundo para alternar entre dezenas de documentos abertos!
- Reduza centenas de cliques do 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.
