David da Flórida faz a pergunta de hoje:
Eu tenho duas pastas de trabalho. Ambos têm os mesmos dados na coluna A, mas as colunas restantes são diferentes. Como posso mesclar essas duas pastas de trabalho?
Perguntei a David se é possível que uma pasta de trabalho tenha mais registros do que a outra. E a resposta é sim. Perguntei a David se o campo-chave só aparece uma vez em cada arquivo. A resposta também é sim. Hoje vou resolver isso com o Power Query. As ferramentas do Power Query são encontradas nas versões do Windows do Excel 2016+ na seção Get & Transform da guia Data. Se você tiver versões do Windows do Excel 2010 ou Excel 2013, poderá baixar o suplemento Power Query para essas versões.
Aqui está a pasta de trabalho de David 1. Ela contém Produto e três colunas de dados.
Aqui está a pasta de trabalho de David 2. Ela contém o Código do Produto e outras colunas. Neste exemplo, há produtos extras na pasta de trabalho2, mas as soluções funcionarão se uma das pastas de trabalho tiver colunas extras.
Aqui estão as etapas:
-
Selecione Dados, Obter Dados, Do Arquivo, Da Pasta de Trabalho:
Carregar dados de um arquivo - Navegue até a primeira pasta de trabalho e clique em OK
- Na caixa de diálogo Navegador, escolha a planilha à esquerda. (Mesmo se houver apenas uma planilha, você deve selecioná-la.) Você verá os dados à direita.
- Na caixa de diálogo Navigator, abra o menu suspenso Load e escolha Load To …
- Escolha apenas criar uma conexão e pressione OK.
-
Repita as etapas 1 a 5 para a segunda pasta de trabalho.
Crie uma conexão com a pasta de trabalho Se você tiver feito as duas pastas de trabalho, deverá ver duas conexões no painel Consultas e conexões à direita da tela do Excel.
Conexões para ambas as pastas de trabalho Continue com as etapas para mesclar as pastas de trabalho:
-
Dados, obter dados, combinar consultas, mesclar.
Mesclar duas consultas com colunas diferentes - Na lista suspensa superior da caixa de diálogo Mesclar, escolha a primeira consulta.
- Na segunda lista suspensa da caixa de diálogo Mesclar, escolha a segunda consulta.
- Clique no título Produto na visualização superior (este é o campo-chave. Observe que você pode selecionar dois ou mais campos-chave com Ctrl + Clique)
- Clique no título Código do Produto na segunda visualização.
-
Abra o tipo de junção e escolha Full Outer (todas as linhas de ambos)
Etapas 8 - 12 ilustradas aqui -
Clique OK. A visualização de dados não mostra as linhas extras e mostra apenas "Tabela" repetidamente na última coluna.
Isso não parece promissor - Observe que há um ícone "Expandir" no cabeçalho de DavidTwo. Clique nesse ícone.
-
Opcional, mas eu sempre desmarco "Usar nome da coluna original como prefixo". Clique OK.
Expanda os campos da pasta de trabalho 2 Os resultados são mostrados nesta visualização:
Todos os registros de qualquer pasta de trabalho - No Power Query, use Home, Close & Load.
Aqui está o belo recurso: se os dados subjacentes em qualquer pasta de trabalho forem alterados, você pode clicar no ícone Atualizar para puxar novos dados para a pasta de trabalho de resultados.
Nota
O ícone para Atualizar geralmente está oculto. Arraste a borda esquerda do painel Consultas e conexões para a esquerda para revelar o ícone.
Assistir vídeo
Transcrição de vídeo
Aprenda Excel com o Podcast, Episódio 2216: Combine duas pastas de trabalho com base em uma coluna comum.
Ei, bem-vindo de volta ao netcast, sou Bill Jelen. A pergunta de hoje é de David, que estava em meu seminário em Melbourne, Flórida, para o Capítulo da Costa Espacial do IIA.
David tem duas pastas de trabalho diferentes em que a coluna A é comum entre as duas. Então, aqui está a pasta de trabalho 1, aqui está a pasta de trabalho 2 - ambas têm o código do produto. Este tem itens que o primeiro não tem, ou vice-versa, e David quer combinar todas as colunas. Portanto, temos três colunas aqui e quatro colunas aqui. Coloquei os dois na mesma pasta de trabalho, caso você esteja baixando a pasta de trabalho para trabalhar junto. Pegue cada um deles, mova-o para sua própria pasta de trabalho e salve-o.
Tudo bem, para combinar esses arquivos, vamos usar o Power Query. O Power Query está integrado ao Excel 2016. Se você estiver na versão do Windows 10 ou 13, pode ir para a Microsoft e baixar o Power Query. Você pode começar a partir de uma nova pasta de trabalho em branco com uma planilha em branco. Você vai salvar este arquivo-- Salvar como, você sabe, talvez pasta de trabalho, para mostrar os resultados dos arquivos combinados .xlsx. Tudo bem? E o que vamos fazer é fazer duas consultas. Iremos para Dados, Obter Dados, Do Arquivo, Da Pasta de Trabalho e então escolheremos o primeiro arquivo. Em uma visualização, selecione a planilha que contém seus dados e não temos que fazer nada com esses dados. Portanto, basta abrir a caixa de carregamento e escolher Carregar para, apenas criar conexão e clicar em OK. Perfeito. Agora, vamos repetir isso para o segundo item - Dados, do arquivo,Em uma pasta de trabalho, escolha DavidTwo, escolha o nome da planilha e abra a carga, Carregar para, apenas criar uma conexão. Você verá aqui neste painel, temos as duas conexões presentes. Tudo bem.
Agora o trabalho real - Dados, Obter Dados, Combinar Consultas, Mesclar e, em seguida, na caixa de diálogo Mesclar, escolha DavidOne, DavidTwo, e esta próxima etapa é completamente não intuitiva. Você tem que fazer isso. Escolha a coluna ou colunas em comum - então Produto e Produto. Tudo bem. E então, tenha muito cuidado aqui com o tipo de junção. Quero todas as linhas de ambos porque um pode ter uma linha extra e preciso ver isso, e então clicamos em OK. Tudo bem. E aqui está o resultado inicial. Não parece que funcionou; parece que não adicionou os itens extras que estavam no arquivo 2. E temos essa coluna 5 - agora é nula. Vou clicar com o botão direito na coluna 5 e dizer: Remova essa coluna. Portanto, abra este ícone de expansão e desmarque esta caixa para Usar o nome da coluna original como prefixo e BAM! funciona. Portanto, os itens extras que estavam no Arquivo 2, que não estão no Arquivo 1,aparecem.
Tudo bem. Agora, no arquivo de hoje, parece que esta coluna Código do Produto é melhor do que esta coluna Produto, porque tem linhas extras. Mas pode haver um dia no futuro em que o Livro 1 tenha coisas que o Livro 2 não tem. Então, vou deixar os dois lá e não vou me livrar de nenhum nulo porque, tipo, embora essa linha na parte inferior pareça ser completamente nula, pode haver no futuro uma situação em que temos alguns nulos aqui porque algo está faltando. Tudo bem? Então, finalmente, Close & Load, e temos nossas dezesseis linhas.
Agora, no futuro, digamos que algo mude. Tudo bem, então vamos voltar para um desses dois arquivos e vou mudar a classe para Apple para 99, e vamos até inserir algo novo e salvar esta pasta de trabalho. Tudo bem. E então, se quisermos que nosso arquivo de mesclagem seja atualizado, venha aqui - agora, cuidado, quando você fizer isso pela primeira vez, você não pode ver o ícone Atualizar - você tem que agarrar esta barra e arrastá-la . E faremos Refresh, e 17 linhas carregadas, a melancia aparece, a Apple muda para 99 - é uma coisa linda. Agora, ei, você quer aprender sobre o Power Query? Compre este livro de Ken Puls e Miguel Escobar, M é para (DATA) MACACO. Vou colocá-lo em dia.
Concluindo hoje: David, da Flórida, tem duas pastas de trabalho que deseja combinar; ambos têm os mesmos campos na coluna A, mas as outras colunas são diferentes; uma pasta de trabalho pode ter itens extras que não estão na outra e David deseja esses; não há duplicatas em nenhum dos arquivos; vamos usar consulta avançada para resolver isso, então comece em uma nova pasta de trabalho em branco em uma planilha em branco; você fará três consultas, primeiro uma-- Data, From File, Workbook e, em seguida, Load to Created Connection; a mesma coisa para a segunda pasta de trabalho e, em seguida, Data, Get Data, Merge, selecione as duas conexões, selecione a coluna que é comum em ambos - no meu caso, Produto - e, em seguida, em Tipo de junção, você deseja uma junção completa tudo a partir do Arquivo 1, tudo a partir do Arquivo 2. E o mais bonito é que, se os dados subjacentes forem alterados,você pode apenas atualizar a consulta.
Para baixar a pasta de trabalho do vídeo de hoje, visite o URL na descrição do YouTube.
Bem, ei, eu quero como David por aparecer no meu seminário, eu quero agradecer por você passar por aqui. Vejo você na próxima vez para outro netcast de.
Baixar arquivo Excel
Para baixar o arquivo excel: combine-based-on-common-column.xlsx
O Power Query é uma ferramenta incrível no Excel.
Excel Pensamento do Dia
Pedi conselhos aos meus amigos do Excel Master sobre o Excel. O pensamento de hoje para ponderar:
"Sempre pressione F4 ao ler intervalo ou matriz em uma função"
Tanja Kuhn
