Recentemente, recebi uma pergunta de um leitor sobre a combinação de várias planilhas na mesma pasta de trabalho em uma única planilha.
Pedi a ele para usar o Power Query para combinar diferentes planilhas, mas então percebi que, para alguém novo no Power Query, fazer isso pode ser difícil.
Portanto, decidi escrever este tutorial e mostrar as etapas exatas para combinar várias planilhas em uma única tabela usando o Power Query.
Abaixo um vídeo onde mostro como combinar dados de várias planilhas / tabelas usando o Power Query:
Abaixo estão as instruções escritas sobre como combinar várias folhas (no caso de você preferir texto escrito em vez de vídeo).
Observação: o Power Query pode ser usado como um suplemento no Excel 2010 e 2013 e é um recurso embutido do Excel 2016 em diante. Com base na sua versão, algumas imagens podem parecer diferentes (as capturas de imagens usadas neste tutorial são do Excel 2016).
Combine dados de várias planilhas usando o Power Query
Ao combinar dados de planilhas diferentes usando o Power Query, é necessário ter os dados em uma tabela do Excel (ou pelo menos em intervalos nomeados). Se os dados não estiverem em uma tabela do Excel, o método mostrado aqui não funcionaria.
Suponha que você tenha quatro folhas diferentes - Leste, Oeste, Norte e Sul.
Cada uma dessas planilhas possui os dados em uma Tabela do Excel e a estrutura da tabela é consistente (ou seja, os cabeçalhos são os mesmos).
Clique aqui para baixar os dados e acompanhar.
Este tipo de dados é extremamente fácil de combinar usando o Power Query (que funciona muito bem com dados na Tabela do Excel).
Para que essa técnica funcione melhor, é melhor ter nomes para suas tabelas do Excel (funcione sem eles também, mas é mais fácil de usar quando as tabelas são nomeadas).
Eu dei às tabelas os seguintes nomes: East_Data, West_Data, North_Data e South_Data.
Aqui estão as etapas para combinar várias planilhas com tabelas do Excel usando o Power Query:
- Vá para a guia Dados.
- No grupo Get & Transform Data, clique na opção ‘Get Data’.
- Vá para a opção ‘De outras fontes’.
- Clique na opção ‘Consulta em branco’. Isso abrirá o editor do Power Query.
- No editor de consultas, digite a seguinte fórmula na barra de fórmulas: = Excel.CurrentWorkbook(). Observe que as fórmulas do Power Query diferenciam maiúsculas de minúsculas, então você precisa usar a fórmula exata conforme mencionado (caso contrário, você receberá um erro).
- Pressione a tecla Enter. Isso mostrará todos os nomes das tabelas em toda a pasta de trabalho (também mostrará os intervalos nomeados e / ou conexões, caso existam na pasta de trabalho).
- [Etapa Opcional] Neste exemplo, desejo combinar todas as tabelas. Se você deseja combinar apenas tabelas específicas do Excel, pode clicar no ícone suspenso no cabeçalho do nome e selecionar aquelas que deseja combinar. Da mesma forma, se você nomeou intervalos ou conexões, e deseja apenas combinar tabelas, você pode remover esses intervalos nomeados também.
- Na célula de cabeçalho de conteúdo, clique na seta de duas pontas.
- Selecione as colunas que você deseja combinar. Se você deseja combinar todas as colunas, certifique-se de que (Selecionar Todas as Colunas) esteja marcado.
- Desmarque a opção ‘Usar o nome da coluna original como prefixo’.
- Clique OK.
As etapas acima combinariam os dados de todas as planilhas em uma única tabela.
Se você olhar com atenção, verá que a última coluna (mais à direita) tem o nome das tabelas do Excel (East_Data, West_Data, North_Data e South_Data). Este é um identificador que nos informa qual registro veio de qual Tabela do Excel. Este também é o motivo pelo qual eu disse que é melhor ter nomes descritivos para as tabelas do Excel.
Aqui estão algumas modificações que você pode fazer nos dados combinados no próprio Power Query:
- Arraste e coloque a coluna Nome no início.
- Remova o “_Data” da coluna de nome (então você fica com Leste, Oeste, Norte e Sul na coluna de nome). Para fazer isso, clique com o botão direito no cabeçalho Nome e clique em Substituir Valores. Na caixa de diálogo Substituir Valores, substitua _Data por um espaço em branco.
- Altere a coluna Dados para mostrar apenas as datas (e não a hora). Para fazer isso, clique no cabeçalho da coluna Data, vá para a guia ‘Transformar’ e altere o tipo de dados para Data.
- Renomeie a consulta para ConsolidatedData.
Agora que você tem os dados combinados de todas as planilhas no Power Query, pode carregá-los no Excel - como uma nova tabela em uma nova planilha.
Para fazer isso. siga as etapas abaixo:
- Clique na guia ‘Arquivo’.
- Clique em Fechar e carregar para.
- Na caixa de diálogo Importar dados, selecione as opções Tabela e Nova planilha.
- Clique OK.
As etapas acima combinariam os dados de todas as planilhas e forneceriam esses dados combinados em uma nova planilha.
Um problema que você deve resolver ao usar este método
Caso você tenha usado o método acima para combinar todas as tabelas na pasta de trabalho, é provável que você enfrente um problema.
Veja o número de linhas dos dados combinados - 1304 (que está certo).
Agora, se eu atualizar a consulta, o número de linhas muda para 2607. Atualize novamente e ele mudará para 3910.
Aqui está o problema.
Sempre que você atualiza a consulta, todos os registros dos dados originais são adicionados aos dados combinados.
Observação: você enfrentará esse problema apenas se tiver usado o Power Query para combinar TODAS AS TABELAS EXCEL na pasta de trabalho. Caso você tenha selecionado tabelas específicas para serem combinadas, você não enfrentará esse problema.Vamos entender a causa deste problema e como corrigi-lo.
Quando você atualiza uma consulta, ela volta e segue todas as etapas que realizamos para combinar os dados.
Na etapa em que usamos a fórmula = Excel.CurrentWorkbook (), nos deu uma lista de todas as tabelas. Isso funcionou bem na primeira vez, pois havia apenas quatro tabelas.
Mas quando você atualiza, há cinco tabelas na pasta de trabalho - incluindo a nova tabela que o Power Query inseriu onde temos os dados combinados.
Portanto, toda vez que você atualiza a consulta, além das quatro tabelas do Excel que queremos combinar, ele também adiciona a tabela de consulta existente aos dados resultantes.
Isso é chamado de recursão.
Aqui está como resolver esse problema.
Depois de inserir = Excel.CurrentWorkbook () na barra de fórmulas do Power Query e pressionar Enter, você obtém uma lista de tabelas do Excel. Para ter certeza de que conseguirá apenas combinar as tabelas da planilha, você precisa filtrar de alguma forma apenas essas tabelas que deseja combinar e remover todo o resto.
Aqui estão as etapas para garantir que você tenha apenas as tabelas necessárias:
- Clique no menu suspenso e passe o cursor sobre Filtros de texto.
- Clique na opção Contém.
- Na caixa de diálogo Filtrar Linhas, insira _Dados no campo próximo à opção ‘contém’.
- Clique OK.
Você pode não ver nenhuma alteração nos dados, mas fazer isso impedirá que a tabela resultante seja adicionada novamente quando a consulta for atualizada.
Observe que nas etapas acima, usamos “_Dados”Para filtrar conforme nomeamos as tabelas dessa forma. Mas e se suas tabelas não forem nomeadas de forma consistente. E se todos os nomes de tabelas forem aleatórios e não tiverem nada em comum?
Aqui está a maneira de resolver isso - use o filtro "não é igual" e insira o nome da Consulta (que seria ConsolidatedData em nosso exemplo). Isso garantirá que tudo permaneça o mesmo e que a tabela de consulta resultante criada seja filtrada.
Tirando o fato de que o Power Query torna todo esse processo de combinação de dados de planilhas diferentes (ou mesmo da mesma planilha) bastante fácil, outro benefício de usá-lo é que o torna dinâmico. Se você adicionar mais registros a qualquer uma das tabelas e atualizar o Power Query, ele fornecerá automaticamente os dados combinados.Nota importante: No exemplo usado neste tutorial, os cabeçalhos eram os mesmos. Caso os cabeçalhos sejam diferentes, o Power Query combinará e criará todas as colunas na nova tabela. Se os dados estiverem disponíveis para aquela coluna, eles serão mostrados, caso contrário, serão nulos.
Você também pode gostar dos seguintes tutoriais do Power Query:
- Combine dados de várias pastas de trabalho no Excel (usando o Power Query).
- Como remover dados dinâmicos no Excel usando o Power Query (também conhecido como Get & Transform)
- Obtenha uma lista de nomes de arquivo de pastas e subpastas (usando o Power Query)
- Mesclar tabelas no Excel usando o Power Query.
- Como comparar dois arquivos / planilhas do Excel
