Como comparar mês e ano em tabela dinâmica no Excel.
1. Comparar Mês a Mês em Tabela Dinâmica Excel
As análises MoM – Month over Month (mês a mês) e YoY – Year Over Year (ano a ano) estão entre as análises de dados temporais mais importantes. São formas comparativas que permitem observar o comportamento de indicadores ao passar dos anos ou dos meses, tendo sempre uma base de comparação.
Podemos analisar um negócio e expressar a evolução de sua receita anual, indicando o percentual de crescimento ou queda. Da mesma forma, podemos analisar mensalmente, comparando percentualmente os meses. Especialmente para os meses, podemos fazer o comparativo entre o mesmo mês de diferentes anos (como março de 2023 vs março de 2022), ou mesmo entre meses seguidos de um mesmo ano (como março de 2023 vs fevereiro de 2023).
Dada a explicação genérica destes conceitos, objetivamos neste artigo ilustrar como proceder para obter estas análises com tabelas dinâmicas no Excel.
Para isto, tomaremos como base a fonte de dados amostrada na próxima imagem.
2. Passo-a-passo para a obtenção das análises YoY e MoM
Preparação – Inserir a tabela dinâmica
Nosso ponto de partida será a tabela dinâmica a seguir. Ela foi montada ao inserirmos a coluna de Datas de nossa fonte de dados na área de linhas da tabela dinâmica e a coluna de Valor na área de valores da tabela dinâmica. Note a imagem a seguir para visualizar a tabela dinâmica resultante e as colunas devidamente colocadas nas áreas apropriadas.
NOTA: ao arrastar a coluna de Data para área de linhas, são exibidos os agrupamentos de anos, trimestres e meses. Podemos remover um ou mais agrupamentos para obter apenas os anos ou os meses, por exemplo.
2.1 Análise YoY
Após a tabela dinâmica obtida, deixamos apenas os anos, removendo os outros agrupamentos, podendo arrastar diretamente da área de linhas da tabela dinâmica.
Teremos o seguinte resultado (aproveitamos para renomear a coluna dos anos):
Daí, basta apenas alterar a forma como visualizamos o valor. Devemos clicar com o botão direito do mouse em algum valor e escolher as opções Mostar Valores como > % Diferença de:
Será apresentada uma caixa de diálogo com diversas opções, conforme imagem a seguir:
O Campo Base, em nosso caso, é apenas 1: o campo dos anos, único disponível. Já o Item Base apresenta todos os anos presentes em nossas datas e as opções anterior e próximo. Podemos fazer maravilhas com essas opções. Se escolhermos 2018, por exemplo, todos os anos terão uma comparação fixa, sempre se referindo a 2018. Este ano ficaria então congelado e os nossos resultados seriam valores acumulados, sempre com base em 2018.
Em nosso caso o Item Base será anterior. Com isso, cada ano será comparado com o ano anterior, ou seja: 2024 vs 2023, 2023 vs 2022, 2022 vs 2021, 2021 vs 2020 etc.
Ao escolher esta opção, teremos os seguintes resultados, já com a tabela dinâmica devidamente formatada (renomeamos o campo dos percentuais – Var. %).
Esta tabela dinâmica informa que o ano de 2019 caiu 0,36% quando comparado a 2018. Informa também que 2020 cresceu 1,42% em relação a 2019.
2.2 Análise MoM
Após a tabela dinâmica obtida, deixamos apenas os meses, removendo os outros agrupamentos, podendo arrastar diretamente da área de linhas da tabela dinâmica.
Teremos o seguinte resultado (aproveitamos para renomear a coluna dos anos):
Daí, basta apenas alterar a forma como visualizamos o valor. Devemos clicar com o botão direito do mouse em algum valor e escolher a opções Mostar Valores como > % Diferença de:
Será apresentada uma caixa de diálogo com diversas opções, conforme imagem a seguir:
O Campo Base, em nosso caso, é apenas 1: o campo dos meses, único disponível. Já o Item Base apresenta todos os meses presentes em nossas datas e as opções anterior e próximo. Se escolhermos jan, por exemplo, todos os meses terão uma comparação fixa, sempre se referindo a jan. Este mês ficaria então congelado e os nossos resultados seriam valores acumulados, sempre com base em jan. Isto faz mais sentido se tivermos sempre um ano em questão, o que pode ser implementado por filtro direto na tabela dinâmica ou ainda segmentações de dados.
Em nosso caso o Item Base será anterior. Com isso, cada mês será comparado com o mês anterior, ou seja: fev vs jan, mai vs abr, set vs ago, dez vs nov etc.
Ao escolher esta opção, teremos os seguintes resultados, já com a tabela dinâmica devidamente formatada (renomeamos o campo dos percentuais – Var. %).
Esta tabela dinâmica informa que o mês de agosto cresceu 2,86% quando comparado com julho, mês anterior. Além disso, outra constatação é que dezembro caiu 16,38% quando comparado com novembro.
NOTA: Como nos dados temos diversos anos e nossa tabela dinâmica não está com algum ano filtrado, reunindo vários, temos que lembrar que os meses elencados são resultados da soma de todos os anos. Isso implica dizer que o resultado de julho vs junho, por exemplo, é a soma do mês de julho de todos os anos comparada com a soma de junho de todos os anos. Isto não faz muito sentido, sendo o ideal “isolar” um certo ano para comparar os meses.
A análise MoM apresentada ficará mais interessante, conforme exposto, caso tenhamos os anos elencados. Por isso, vamos partir da tabela dinâmica a seguir que apresenta os anos e seus meses estratificados:
Com base nesta tabela dinâmica teremos 2 análises distintas: comparar um certo mês com o mês anterior (dentro do mesmo ano) e comparar um certo mês com o mesmo mês no ano anterior.
Cenário 1 – Mês vs mês anterior no mesmo ano
Para isso, exploramos as opções Mostar Valores como > % Diferença de, sendo exibida a seguinte caixa de diálogo:
O Campo Base apresenta a opção de Meses e Anos. Devemos escolher Meses, visto que nossa comparação será realizada com base em meses de um mesmo ano.
O Item Base a ser escolhido será Anterior.
Ao confirmarmos as alterações em Ok, teremos a nossa tabela dinâmica finalizada (com as colunas devidamente renomeadas):
Esta tabela dinâmica informa que o mês de ano outubro de 2022 cresceu 28,72% quando comparado com setembro de 2022. Outra constatação apontada por esta tabela dinâmica é que maio de 2019 caiu 16,27% quando comparado com abril do mesmo ano.
NOTA: percebe-se que os meses de janeiro não são comparados com dezembro do ano anterior. Esta comparação pode ser implementada utilizando expressões DAX. Uma alternativa é ter agrupamentos de datas do tipo mmm/aa (dez/2023), por exemplo, que este processo será facilitado (conforme exibe a imagem a seguir)..
Cenário 2 – Mês vs mesmo mês no ano anterior
Tomando como base a mesma tabela dinâmica do Cenário 1, exploramos a opções Mostar Valores como > % Diferença de, sendo exibida a seguinte caixa de diálogo:
O Campo Base apresenta a opção de Meses e Anos. Devemos escolher Anos, visto que nossa comparação será realizada com base em meses de anos distintos (o mês é o mesmo, mas de anos consecutivos).
O Item Base a ser escolhido será Anterior.
Ao confirmarmos as alterações em Ok, teremos a nossa tabela dinâmica finalizada (com as colunas devidamente renomeadas):
Esta tabela dinâmica informa que o mês de ano outubro de 2022 cresceu 30,51% quando comparado com outubro de 2021. Outra constatação apontada por esta tabela dinâmica é que maio de 2019 cresceu 5,31% quando comparado com o mesmo mês do ano anterior.
NOTA: observa-se que os meses do primeiro ano, 2018, estão em branco. Isso ocorre porque não há dados referentes ao ano anterior, 2017, impossibilitando, portanto, a comparação.
Download Planilha Comparar Mês e Ano em Tabela Dinâmica
Clique no botão abaixo para realizar o download do arquivo texto do exemplo:
