Muitas vezes, queremos calcular alguns valores por mês. Tipo, quantas vendas foram feitas em um mês perticular. Bem, isso pode ser feito facilmente usando tabelas dinâmicas, mas se você estiver tentando ter um relatório dinâmico, podemos usar uma fórmula SUMPRODUCT ou SUMIFS para somar por mês.
Vamos começar com a solução SUMPRODUCT.
Aqui está a fórmula genérica para obter a soma por mês no Excel
= SUMPRODUCT (intervalo_soma, - (TEXTO (intervalo_de_data, "MMM") = texto_mês))
Intervalo_soma : É o intervalo que você deseja somar por mês.
Intervalo_de_data : É o período que você observará por meses.
Month_text: É o mês em formato de texto do qual você deseja somar os valores.
Agora vamos ver um exemplo:
Exemplo: Soma de Valores por Mês no Excel
Aqui temos alguns valores associados às datas. Essas datas são nos meses de janeiro, fevereiro e março de 2019.
Como você pode ver na imagem acima, todas as datas são do ano 2019. Agora só precisamos somar os valores em E2: G2 por meses em E1: G1.
Agora, para somar os valores de acordo com os meses, escreva esta fórmula em E2:
= SUMPRODUCT (B2: B9, - (TEXT (A2: A9, "MMM") = E1)))
Se você deseja copiá-lo em células adjacentes, use referências absolutas ou intervalos nomeados como você na imagem.
Isso nos dá a soma exata de cada mês.
Como funciona?
Começando de dentro, vamos olhar para o TEXTO (A2: A9, "MMM") papel. Aqui, a função TEXT extrai o mês de cada data no intervalo A2: A9 em formato de texto em uma matriz. Traduzindo para fórmula = SUMPRODUCT (B2: B9, - ({"Jan"; "Jan"; "Fev"; "Jan"; "Fev"; "Mar"; "Jan"; "Fev"} = E1) )
A seguir, TEXTO (A2: A9, "MMM")= E1: Aqui, cada mês na matriz é comparado com o texto em E1. Como E1 contém “Jan”, cada “Jan” na matriz é convertido em TRUE e outro em FALSE. Isso converte a fórmula em = SUMPRODUCT ($ B $ 2: $ B $ 9, - {TRUE; TRUE; FALSE; TRUE; FALSE; FALSE; TRUE; FALSE})
Em seguida - (TEXT (A2: A9, "MMM") = E1), converte VERDADEIRO FALSO em valores binários 1 e 0. A fórmula se traduz em = SUMPRODUCT ($ B $ 2: $ B $ 9, {1; 1; 0; 1; 0; 0; 1; 0}).
Finalmente SUMPRODUTO($ B $ 2: $ B $ 9, {1; 1; 0; 1; 0; 0; 1; 0}): a função SUMPRODUCT multiplica os valores correspondentes em $ B $ 2: $ B $ 9 para a matriz {1; 1; 0; 1; 0; 0; 1; 0} e os soma. Portanto, obtemos soma por valor como 20052 em E1.
SUM IF meses de ano diferente
No exemplo acima, todas as datas eram do mesmo ano. E se eles fossem de anos diferentes? A fórmula acima somará os valores por mês, independentemente do ano. Por exemplo, janeiro de 2018 e janeiro de 2019 serão adicionados, se usarmos a fórmula acima. O que está errado na maioria dos casos.
Isso acontecerá porque não temos nenhum critério para o ano no exemplo acima. Se adicionarmos critérios de ano também, funcionará.
A fórmula genérica para obter a soma por mês e ano no Excel
= SUMPRODUCT (intervalo_soma, - (TEXT (intervalo_de_data, "MMM") = texto_mês), - (TEXTO (intervalo_de_data, "aaaa") = TEXTO (ano, 0)))
Aqui, adicionamos mais um critério que verifica o ano. Tudo o resto é o mesmo.
Vamos resolver o exemplo acima, escreva esta fórmula na célula E1 para obter a soma de janeiro do ano de 2017.
= SUMPRODUCT (B2: B9, - (TEXTO (A2: A9, "MMM") = E1), - (TEXTO (A2: A9, "aaaa") = TEXTO (D2,0)))
Antes de copiar nas células abaixo, use intervalos nomeados ou referências absolutas. Na imagem, usei intervalos nomeados para copiar nas células adjacentes.
Agora podemos ver a soma do valor por meses e anos também.
Como funciona?
A primeira parte da fórmula é igual ao exemplo anterior. Vamos entender a parte adicional que adiciona os critérios do ano.
- (TEXT (A2: A9, "aaaa") = TEXT (D2,0)): TEXT (A2: A9, "aaaa") converte a data em A2: A9 como anos no formato de texto em uma matriz. {"2018"; "2019"; "2017"; "2017"; "2019"; "2017"; "2019"; "2017"}.
Na maioria das vezes, o ano é escrito em formato de número. Para comparar um número com o texto nós, converti o texto do ano int usando TEXTO (D2,0). Em seguida, comparamos este ano de texto com a matriz de anos como TEXT (A2: A9, "aaaa") = TEXT (D2,0). Isso retorna uma matriz de verdadeiro-falso {FALSO; FALSO; VERDADEIRO; VERDADEIRO; FALSO; VERDADEIRO; FALSO; VERDADEIRO}. Em seguida, convertemos verdadeiro falso em número usando o operador -. Isso nos dá {0; 0; 1; 1; 0; 1; 0; 1}.
Então, finalmente, a fórmula será traduzida como = SUCESSO (B2: B9, {1; 1; 0; 1; 0; 0; 1; 0}, {0; 0; 1; 1; 0; 1; 0; 1 }). Onde a primeira matriz são os valores. O próximo é o mês combinado e o terceiro é o ano. Por fim, obtemos nossa soma de valores como 2160.
Usando a função SUMIFS para somar por mês
Fórmula Genérica
= SUMIFS (intervalo_soma, intervalo_de_data, ”> =” & data de início, intervalo_de_data, ”<=” & EOMONTH (data_de_início, 0))
Aqui,Intervalo_soma : É o intervalo que você deseja somar por mês.
Intervalo_de_data : É o período que você observará por meses.
Data de início : É a data de início a partir da qual você deseja somar. Para este exemplo, será o primeiro dia do mês especificado.
Exemplo: Soma de Valores por Mês no Excel
Aqui temos alguns valores associados às datas. Essas datas são nos meses de janeiro, fevereiro e março de 2019.
Só precisamos somar esses valores por mês. Agora era fácil se tivéssemos meses e anos separados. Mas eles não são. Não podemos usar nenhuma coluna de ajuda aqui.
Portanto, para preparar o relatório, preparei um formato de relatório que contém o mês e a soma dos valores. Na coluna do mês, na verdade, tenho uma data de início do mês. Para ver apenas o mês, selecione a data de início e pressione CTRL + 1.
No formato personalizado, escreva “mmm”.
Agora temos nossos dados prontos. Vamos somar os valores por mês.
Escreva esta fórmula em E3 para somar por mês.
= SUMIFS (B3: B10, A3: A10, "> =" & D3, A3: A10, "<=" & EOMONTH (D3,0))
Use referências absolutas ou intervalos nomeados antes de copiar a fórmula.
Então, finalmente chegamos ao resultado.
Então, como funciona?
Como sabemos, a função SUMIFS pode somar valores em vários critérios.
No exemplo acima, o primeiro critério é a soma de todos os valores em B3: B10 onde, data em A3: A10 é maior ou igual à data em D3. D3 contém 1 de janeiro. Isso se traduz também.
= SUMIFS (B3: B10, A3: A10, "> =" & “1-jan-2019”, A3: A10, "<=" EOMONTH (D3,0))
Os próximos critérios são somados apenas se a data em A3: A10 é menor ou igual a EOMONTH (D3,0). A função EOMONTH retorna apenas o número de série da última data do mês fornecida. Finalmente a fórmula também se traduz.
= SUMIFS (B3: B10, A3: A10, "> = 1-jan-2019”, A3: A10, "<= 31-jan-2019”)
Portanto, obtemos a soma por mês no Excel.
A vantagem deste método é que você pode ajustar a data de início para somar os valores.
Se as datas tiverem anos diferentes, é melhor usar tabelas dinâmicas. As tabelas dinâmicas podem ajudá-lo a segregar os dados em formato anual, trimestral e mensal com facilidade.
Então sim pessoal, é assim que você pode somar os valores por mês. Ambas as formas têm suas próprias especialidades. Escolha como você gosta.
Se você tiver alguma dúvida sobre este artigo ou qualquer outra consulta relacionada ao Excel e VBA, a seção de comentários está aberta para você.
Artigos relacionados:
Como usar a função SUMIF no Excel
SOMASE com datas no Excel
SUMIF com células não em branco
Como usar a função SUMIFS no Excel
SUMIFS usando lógica AND-OR
Artigos populares
50 Atalho do Excel para aumentar sua produtividade: Torne sua tarefa mais rápida. Esses 50 atalhos farão você trabalhar ainda mais rápido no Excel.
Como usar tFunção VLOOKUP no Excel: Esta é uma das funções mais usadas e populares do Excel, usada para pesquisar valores em diferentes intervalos e planilhas.
Como usar a função CONT.SE no Excel: Conte valores com condições usando esta função incrível. Você não precisa filtrar seus dados para contar valores específicos. A função Countif é essencial para preparar seu painel.
Como usar a função SUMIF no Excel: Esta é outra função essencial do painel. Isso ajuda você a somar valores em condições específicas.
