Consolidação de Dados vs Power Query! Qual a melhor Solução?

O Microsoft Excel fornece-nos sempre várias soluções para um desafio. Hoje vamos considerar 2 ferramentas distintas para compilar dados provenientes de várias origens! São elas a Consolidação de Dados e o PowerQuery!

No cenário que pretendemos exemplificar, temos 3 folhas de Excel distintas, cada uma representando um ano (2016, 2017 e 2018), com a receita mensal de produtos vendidos. O objetivo é agregar numa folha de “Resumo” os valores de venda de cada produto somando os 3 anos.

Solução 1: Consolidação de Dados

A consolidação de dados permite-nos consolidar intervalos provenientes de várias localizações (folhas ou intervalos), utilizando uma função de agregação, como por exemplo a SOMA, MÉDIA ou CONTAR… resultando na compilação desses mesmos dados numa nova única folha. Os passos são muito simples e passamos a explicar!

1 – Começamos por criar uma nova folha “Resumo”.

2 – Na nova folha, acedemos ao separador Data e ao Botão Consolidate.

img_consolidate

3 – Na caixa de diálogo que aparece, devemos escolher as seguintes opções:

  • Function: A função que vai agregar os valores. Neste caso a função SUM vai somar os valores de cada folha.
  • Reference: Nesta opção devemos selecionar os dados de cada folha que vão dar origem à consolidação. Para cada intervalo selecionado confirmamos com o botão Add.

Formas de Consolidação de Dados:

Ainda na caixa de diálogo, seguindo os passos anteriores e fazendo apenas OK para confirmar, estamos a criar uma consolidação denominada por “posição”, que significa que os dados das várias folhas são agregados, neste caso pela função SOMA, através da posição onde estão em cada folha, não sendo feita a distinção do produto ou mesmo do mês.

Use labels in (Consolidação por categoria):

Quando os valores de cada uma das folhas não estão na mesma posição, mas estão identificados por uma etiqueta de linha e de coluna, ou seja, neste caso os valores estão identificados por mês (Top Row) e por produto (Left Column) podemos usar essas etiquetas para somar os produtos da mesma categoria e de acordo com o mês. Este tipo de consolidação é o mais “correto” a utilizar quando os intervalos de origem não partilham a mesma estrutura (Layout). Este método é semelhante a consolidar dados com uma Tabela Dinâmica.

Atualização automática de valores…

Caso pretenda que o resultado da consolidação seja atualizado automaticamente sempre que é alterado algum valor nas referencias de base, deve optar por selecionar a opção Create links to source data.

4 – Selecionando as opções descritas acima terá na folha RESUMO o resultado da consolidação por produto e mês de cada um dos anos: 2016, 2017 e 2018. O cálculo escolhido para a agregação dos valores foi a SOMA. Vamos agora executar o mesmo cenário através do Power Query!

Solução 2: Power Query para compilar dados!

Uma das funcionalidades mais comuns do Power Query é a possibilidade de realizar consultas a intervalos ou tabelas provenientes de várias origens, transformar os seus dados e por fim combinar as pesquisas num resultado final. É esse o processo que vamos demonstrar nos passos abaixo!

Utilizar Tabelas (funcionam bem com o PowerQuery)!

1 – Começamos por converter cada um dos intervalos em tabelas de Excel. Podemos fazê-lo através do Menu Insert e pressionando o botão Table. Repetimos o processo para cada um dos intervalos (2016, 2017 e 2018).

2 – De seguida importamos cada uma das tabelas para o Power Query através do Botão From Table / Range.

3 – Já no Power Query, vamos apenas dar um nome à Query, nomeando-a “2016”.

4 – Terminamos a consulta carregando os dados para o Excel: Acedemos ao Menu Home| opção Close and Load To

4.1 – Surge a janela para importar os dados, na qual devemos escolher a opção Only Create a Connection. Repetimos o processo para cada uma das Tabelas (2017 e 2018).

Combinar as várias Queries…

5 – Depois de criarmos as 3 consultas disponíveis, é altura de fazer a conciliação dos dados! Para o efeito vamos criar uma nova Querie que resulta da adição de linhas das consultas de cada ano: Acedemos ao Menu Data | Get Data | Combine Queries | Append.

6 – Surgindo a caixa de diálogo, podemos optar por combinar de 2 em 2 tabelas ou todas em simultâneo. No exemplo optamos por selecionar todas as tabelas.

7 – Terminamos este processo, carregando a consulta (Close and Load To…) para o Excel, mas desta vez, importamos para uma tabela de Excel.

Consolidar os produtos dos 3 anos…

8 – Depois de criada a ultima consulta que resulta da adição (Append) das linhas das 3 tabelas, é altura de consolidar os dados somando-os!

Uma vez que estamos a usar o produto como referencia, o processo passa por “agrupar” a coluna “ID Produto” efetuando uma soma dos valores de cada mês.

8.1 – Acedemos ao menu Transform e à opção Group by

8.2 – E agruparmos pela coluna “ID Produto”, utilizando a função SOMA, para cada um dos meses…

O processo fica então assim concluído!

Ai está! 2 exemplos para fazer consolidação de dados que pode utilizar: Data Consolidate ou Power Query! Escolha a opção que for mais interessante para si ou que de acordo com o contexto lhe der mais jeito!

Download de Ficheiro!

Download dos Ficheiros Base!

Ficheiros .xlsx do tutorial (base e resolução). Inclui PDF com os passos!
Download de Ficheiro!

Veja o tutorial em video no link em baixo para aprender a usar estas ferramentas!

Aprenda esta e outras funcionalidades nos cursos disponíveis!

Microsoft Excel – Utilização Avançada
Microsoft Excel – Ferramentas Business Intelligence
PowerBi Desktop – Criação de Relatórios Dinâmicos
Workshop – O Power BI do Excel para análise de dados!
2019-07-24T17:45:30+01:00