Como mesclar dados de várias pastas de trabalho do Excel. Digamos que você tenha uma pasta cheia de arquivos do Excel. Cada um possui uma planilha com um número diferente de linhas. Você pode importar todos os arquivos da pasta para uma única pasta de trabalho do Excel.
Assistir vídeo
- Revisitando os dados limpos com o podcast # 2037 do Power Query
- O Power Query agora pode combinar todos os arquivos do Excel em uma pasta.
- Aprimorado: Eles excluem automaticamente os cabeçalhos de todos os arquivos, exceto do primeiro.
- Você escolhe qual arquivo usar como arquivo de amostra.
- Escolha qual planilha, tabela, intervalo nomeado importar
- Use o editor de consulta para fazer qualquer transformação
- Fechar e carregar para combinar todos os arquivos
- Mais tarde, atualize a consulta para atualizá-la
Transcrição de vídeo
Aprenda Excel com o Podcast, Episódio 2077: Mesclar Pastas de Trabalho
Ei, bem-vindo de volta ao netcast, sou Bill Jelen. Apenas 40 episódios atrás, falando sobre limpeza de dados com o Power Query, como pegar um monte de arquivos CSV e colocá-los em um único arquivo Excel. Bem, esse recurso acabou de ficar dramaticamente melhor.
Então eu tenho aqui hoje é uma pasta e nesta pasta está toda uma série de arquivos Excel mensais, 22 deles por quase dois anos. Eu preciso combinar todos esses arquivos. Agora, todos eles têm o mesmo layout de coluna, certo? Então, todos eles têm 9 colunas, todos têm os mesmos cabeçalhos e assim por diante, certo. Então, vamos combinar tudo isso sem uma macro.
Se você estiver no Excel 2016, chegará à guia Dados para obter e transformar os dados. Se você estiver no Excel 2010 ou 2013, irá baixar um ótimo produto da Microsoft chamado Power Query. Você pode fazer o download de tudo e instalá-lo em menos de um minuto, certo.
Então, vamos vir aqui para obter dados de um arquivo, mas não de uma única pasta de trabalho. Queremos obtê-lo de uma pasta como esta. Tudo bem, aqui estão eles. Eles estão pedindo que você navegue até a pasta, mas vou apenas clicar lá e colar Ctrl + V porque colei o diretório, clique em OK. Então, eles me mostraram uma prévia dos dados que tenho. Abro esta pequena lista suspensa Combinar aqui e escolho Combinar e Editar. Tudo bem, e a etapa aqui é que vamos escolher um arquivo que seja um bom arquivo representativo, tudo bem, e eles estão apenas escolhendo o primeiro que por acaso esteja em ordem alfabética. Não importa qual você escolha. E então esta consulta de amostra que estamos construindo a partir do primeiro arquivo que escolhemos, neste caso, eu quero a Planilha1.Se você tiver várias planilhas, poderá dizer Planilha 2 ou a planilha denominada Declaração de Renda ou Faixa de Nome ou uma tabela. E eles me mostraram como é e eu cliquei em OK.
Agora, há muita coisa acontecendo nesta tela. O que eles realmente fizeram aqui é criar um Sample Query, ahh Sample Query, e então criar isso como uma função e executar essa função em tudo na pasta. Eles adicionam uma nova coluna que me informa de onde veio o arquivo e, neste caso específico, eu já sei de onde ele veio porque temos uma data preenchida aqui. Então, eu não preciso disso, vou clicar com o botão direito e Remover isso. E talvez haja alguns dados lá que eu não preciso. Talvez esses dados vão para um cliente, então não quero mostrar o Lucro, então posso remover essa coluna e até mesmo remover a coluna Custo, e talvez haja algumas coisas irritantes como, você sabe, eles usaram cabeçalhos ruins e eu deseja renomear esses cabeçalhos. Então, clique com o botão direito e renomear, vamos chamá-lo de QTY para Quantidade.Talvez as coisas estejam na sequência errada, talvez eu queira pegar esse item e movê-lo para outro local, certo? Apenas todas essas pequenas coisas irritantes que você possa ter e, claro, aqui, eles estão executando todas as etapas aplicadas e, em seguida, você pode continuar a fazer qualquer coisa do Power Query que precise fazer. Mas, neste caso, é simplesmente simples. Só quero inserir os dados. Vou fechar e carregar, alguns segundos se passam e aí está. Eu tenho minha prova final. Vamos apenas classificar isso aqui por data; Veja, vamos de janeiro de 2018 até outubro de 2019. Que legal é isso.Ao fazer todas as etapas aplicadas, você pode continuar a fazer todas as coisas do Power Query que precisar. Mas, neste caso, é simplesmente simples. Só quero inserir os dados. Vou fechar e carregar, alguns segundos se passam e aí está. Eu tenho minha prova final. Vamos apenas classificar isso aqui por data; Veja, vamos de janeiro de 2018 até outubro de 2019. Que legal é isso.Ao fazer todas as etapas aplicadas, você pode continuar a fazer todas as coisas do Power Query que precisar. Mas, neste caso, é simplesmente simples. Só quero inserir os dados. Vou fechar e carregar, alguns segundos se passam e aí está. Eu tenho minha prova final. Vamos apenas classificar isso aqui por data; Veja, vamos de janeiro de 2018 até outubro de 2019. Que legal é isso.
Agora, aqui está a coisa incrível sobre isso. Digamos que algo mude em nossa pasta. Então, de volta aqui nos arquivos de Mês, recebo dois novos arquivos, Colar, e agora há mais dois registros aqui, mais dois arquivos do Excel que preciso combinar. Basta voltar ao meu arquivo Excel, que atualmente tem 516 linhas, encontrar os MonthFiles e clicar no botão Atualizar. E rapidamente agora tenho os novos discos de novembro e dezembro. O Power Query é uma nova ferramenta incrível, gratuita para Excel 2010, gratuita para Excel 2013. Era maravilhoso quando eles podiam combinar arquivos CSV, mas agora que podem combinar arquivos Excel também, simplesmente fenomenal.
Então, eu atualizei meu livro, Power Excel com, a edição de 2017, inclui Power Query, Power BI e todas as novas ferramentas. Confira. Clique nesse “i” no canto superior direito.
Tudo bem, resumo deste episódio: Estamos revisitando os dados limpos com o podcast # 2037 do Power Query, onde combinamos vários arquivos CSV. Agora podemos usar essa mesma ferramenta, uma versão aprimorada dessa ferramenta, para combinar todos os arquivos do Excel em uma pasta. Outra melhoria, a mudança de 2037, eles excluem automaticamente os cabeçalhos de todos os arquivos, exceto o primeiro, e promovem esses cabeçalhos no primeiro arquivo. Você escolhe qual arquivo usar como arquivo de amostra. No meu caso, usei janeiro. Trabalho - Escolha qual planilha, tabela ou intervalo nomeado importar e, em seguida, faça quaisquer transformações no editor de consultas. Clicamos em Fechar e Carregar, ele irá combinar todos os arquivos. E então, se você colocar mais arquivos na pasta ou alguns arquivos removidos ou alguns arquivos alterados, apenas atualize a consulta e ela executará todas essas etapas novamente. Power Query,uma ferramenta absolutamente incrível.
Eu quero te agradecer por passar por aqui. Nos vemos na próxima vez para outro netcast de.
⇬ Fazer download do arquivo
Baixe o arquivo de amostra aqui: Podcast2077.xlsm
