Show
Como consolidar no Excel? Se você está procurando por maneiras de combinar planilhas, consolidar dados de múltiplas planilhas ou simplesmente combinar várias planilhas em uma só, então você está no lugar certo! Um dos problemas bastante comuns que os usuários de Excel enfrentam, é a possibilidade de mesclar várias planilhas em uma sem precisa copiar e colar. Como consolidar dados de várias planilhas em uma única planilhaProvavelmente, a maneira mais simples e rápida de consolidar dados no Excel (que estão localizados em uma pasta de trabalho ou em várias pastas de trabalho) é usando o recurso de Consolidar, integrado ao Excel. Vamos considerar o seguinte exemplo. Suponha que você tenha vários relatórios dos escritórios regionais de sua empresa e queira consolidar esses números em uma planilha mestre para ter um relatório resumido com os totais de vendas de todos os produtos. Como você pode observar na imagem abaixo, as três planilhas a serem consolidadas possuem uma estrutura de dados semelhante, mas diferentes números de linhas e colunas: Deste modo, para consolidar os dados em uma única planilha, execute as seguintes etapas: 1. Organize os dados de origem corretamente. Para que o recurso que o recurso Consolidar do Excel funcione corretamente. Para isto, certifique-se de que:
2. Na sua planilha mestre, (onde os dados serão consolidados) clique na célula superior esquerda onde deseja que os dados consolidados apareçam. Acesse a guia Dados > Consolidar. Dica extra: Recomenda-se consolidar os dados em uma planilha em branco. Se sua planilha mestre já tiver alguns dados, certifique-se de que haja espaço suficiente (linhas e colunas em branco) para conter os dados mesclados. 3. Ao seguir os passos anteriores, o Excel irá abrir a caixa de diálogo Consolidar. Defina as seguintes configurações de consolidação:
Se uma ou algumas das planilhas estiverem em outra pasta de trabalho, clique no botão Procurar para localizar a pasta de trabalho desejada. 4. Ainda na caixa de diálogo Consolidar, defina as configurações de atualização selecionando qualquer uma das seguintes opções:
Ao expandir um determinado grupo (clicando no símbolo de mais) e, em seguida, clicar na célula com um determinado valor, um link para os dados de origem será exibido na barra de fórmulas. Como você pode observar, o recurso Consolidar do Excel é muito útil para reunir dados de várias planilhas. No entanto, ele tem algumas limitações. Em particular, ele funciona apenas para valores numéricos e sempre resume esses valores de uma forma ou de outra (soma, contagem, média, etc.) Por outro lado, se você deseja mesclar planilhas no Excel copiando seus dados, a opção de consolidação não é a melhor opção. Para combinar apenas algumas folhas, você pode não precisar de mais nada além do bom e velho copiar / colar. Mas se você deseja mesclar dezenas de planilhas, os erros com a cópia e colagem manual podem ser inevitáveis. Neste caso, você poderá usar uma das seguintes opções para automatizar a consolidação dos dados. Como juntar várias planilhas do Excel em umaNo geral, existem outras duas maneiras de mesclar planilhas do Excel em uma, sem copiar e colar manualmente os dados. São elas:
Consolidar no Excel usando um código VBASe você é um usuário avançado do Excel e se sente confortável com macros e VBA, então poderá combinar várias planilhas do Excel em uma usando algum script VBA. Por exemplo, suponha que você tem três planilhas contendo dados que deseja consolidar em uma única Planilha conforme mostra imagem abaixo. Uma opção seria desenvolver uma macro no VBA capaz de copiar os dados de todas as planilhas e colar em uma única planilha, mantendo apenas um único cabeçalho. Existem diferentes maneiras de se fazer esse trabalho usando o VBA. Abaixo temos um código que você poderá adaptar para a sua necessidade. Sub Consolidar_Dados() 'Copia os dados da primeira Planilha Plan1.Select Range("A1").CurrentRegion.Select Selection.Copy 'Cola os dados na Planilha Matriz a partir da célula A1 Plan4.Select Range("A1").Select ActiveSheet.Paste 'Copia os dados da segunda Planilha a partir da célula A2 para ignorar o cabeçalho Plan2.Select Range("A2").Select Range(Selection, Selection.End(xlToRight)).Select Range(Selection, Selection.End(xlDown)).Select Selection.Copy 'Identifica a primeira linha vazia na Planilha Matriz e cola os dados copiados da Segunda Planilha Plan4.Select linha = Sheets("Plan4").Range("A1048576").End(xlUp).Row + 1 Range("A" & linha).Select ActiveSheet.Paste 'Copia os dados da Terceira Planilha a partir da célula A2 para ignorar o cabeçalho Plan3.Select Range("A2").Select Range(Selection, Selection.End(xlToRight)).Select Range(Selection, Selection.End(xlDown)).Select Selection.Copy 'Identifica a primeira linha vazia na Planilha Matriz e cola os dados copiados da Terceira Planilha Plan4.Select linha = Sheets("Plan4").Range("A1048576").End(xlUp).Row + 1 Range("A" & linha).Select ActiveSheet.Paste 'Desenvolvido por exceleasy.com.br End Sub Lembre-se de que, para que o código VBA funcione corretamente, todas as planilhas de origem devem ter a mesma estrutura, os mesmos títulos de coluna e a mesma ordem de coluna. Consolidar no Excel usando o Power QueryO Power Query é uma tecnologia muito poderosa para combinar e refinar dados no Excel. Importante: O Power Query pode ser usado como um suplemento no Excel 2010 e 2013 e é um recurso embutido a partir do Excel 2016. Com base na sua versão, algumas imagens podem parecer diferentes (as imagens usadas neste tutorial são do Excel 2019). Para combinar dados de planilhas diferentes usando o Power Query, é necessário ter os dados em formato de tabela do Excel (ou pelo menos em intervalos nomeados). Se os dados não estiverem em uma tabela do Excel, o método mostrado a seguir, não irá funcionar. Então, suponha que você tenha quatro planilhas diferentes – Janeiro, Fevereiro, Março e Abril. Cada uma dessas planilhas contém os dados estruturados em formato de tabela de modo consistente (ou seja, os cabeçalhos são os mesmos). Agora, siga as etapas abaixo para combinar várias planilhas com tabelas do Excel usando o Power Query: 1. Acesse a guia Dados > Grupo Obter e Transformar Dados > Obter Dados. 2. Escolha a opção De Outras Fontes > Consulta em Branco. 3. No editor de consultas, digite a seguinte fórmula na barra de fórmulas: =Excel.CurrentWorkbook(). Note que as fórmulas do Power Query diferenciam maiúsculas de minúsculas, portanto, é necessário usar a fórmula exata conforme mencionado (caso contrário, você receberá um erro). 4. 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). Opcional: Neste exemplo, desejamos combinar todas as tabelas. Se você deseja combinar apenas tabelas específicas, basta 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. 5. Na célula do cabeçalho Content, clique na seta de duas pontas conforme mostra imagem abaixo: 6. Selecione as colunas que você deseja combinar. Se você deseja combinar todas as colunas, certifique-se de que (Selecionar todas as colunas) esteja marcado. 7. Desmarque a opção ‘Use o nome da coluna original como prefixo’. Para finalizar, clique em OK. Como resultado, você terá as tabelas combinadas em uma única tabela. Observe que a última coluna (mais à direita) tem o nome das tabelas do Excel (Tabela 1, Tabela 2, Tabela 3, Tabela 4). Este é um identificador que nos informa qual registro veio de qual Tabela do Excel. Caso você tenha nomeado suas tabelas para um nome personalizado, esses nomes irão aparecer neste respectivo campo. Exportando os dados do Power Query para a PlanilhaAgora 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, basta seguir as etapas abaixo: 1. Tela caixa de diálogo do Power Query, acesse a guia Arquivo e selecione:
Para este exemplo, optamos por usar a opção Fechar e carregar. Note que o Excel transfere os dados consolidados para uma nova planilha na mesma Pasta de Trabalho. Conforme dito anteriormente, o Power Query é uma ferramenta muito poderosa para combinar e refinar dados no Excel. Com isso, é bastante complexo e requer uma longa curva de aprendizado. Caso tenha interesse em se aprofundar no assunto, recomendamos a leitura do tutorial: Combinar dados de várias fontes de dados (Power Query). Confira também as seguintes dicas de Excel: E você, conhece alguma outra maneira de consolidar dados no Excel? Deixe o seu comentário abaixo e continue acompanhando mais dicas de Excel em nosso site!
|