Questão Como faço para juntar duas planilhas no Excel, como faria no SQL?


Eu tenho duas planilhas em dois arquivos diferentes do Excel. Ambos contêm uma lista de nomes, números de identificação e dados associados. Uma é uma lista principal que inclui campos demográficos gerais e a outra é uma lista que inclui apenas nome e id e um endereço. Esta lista foi reduzida da lista principal por outro escritório.

Eu quero usar a segunda lista para filtrar o primeiro. Além disso, quero que os resultados incluam outros campos da planilha mestre junto com os campos de endereço da segunda planilha. Eu sei como eu poderia fazer isso muito facilmente com uma junção interna do banco de dados, mas estou menos claro sobre como fazer isso de forma eficiente no Excel. Como pode juntar duas planilhas no Excel? Pontos de bônus para mostrar como fazer junções externas também, e eu preferiria muito saber como fazer isso sem precisar de uma macro.


105


origem


Eu acho a seguinte página muito útil: randomwok.com/excel/how-to-use-index-match - Tommy Bravo
randomwok.com/excel/how-to-use-index-match => especialmente o "lembrete" fácil no final: =INDEX ( Column_I_want_a_return_value_from , ( MATCH ( My_Lookup_Value , Column_I_want_to_Lookup_against , 0 )) - Tommy Bravo


Respostas:


Para 2007+ use Data > From Other Sources > From Microsoft Query:

  1. escolher Excel File e selecione seu primeiro excel
  2. escolha colunas
     (se você não vir nenhuma lista de colunas, verifique Options > System Tables)
  3. vamos para Data > Connections > [escolha a conexão que acabou de criar]> Properties > Definition > Command text

Agora você pode editar este Command text como SQL. Não sei qual é a sintaxe suportada, mas tentei uniões implícitas, "inner join", "left join" e uniões que funcionam. Aqui está uma consulta de amostra:

SELECT *
FROM `C:\Users\Peter\Documents\Excel-to-excel\Source_1.xlsx`.`Sheet1$` a
LEFT JOIN `C:\Users\Peter\Documents\Excel-to-excel\Source_2.xlsx`.`Sheet1$` b
ON a.col2 = b.col2

146



Existe uma maneira de evitar codificar o caminho? O caminho relativo funcionaria? - Rekin
Como um bônus adicional, o Microsoft Query permite que você experimente controles comuns de estilo de 16 bits para abrir o arquivo do Excel e para caixas de mensagem. Dessa forma, você pode se lembrar de quando era jovem. :-) - Edward Brey
Isso funciona com arquivos CSV? Estou usando o MS Office Professional Plus 2010 e não vejo como seguir a etapa 3 - deixo a caixa de diálogo na etapa 2 suspensa? Não há "escolha sua nova conexão" na caixa de diálogo Conexões. - John Freeman
@ JohnFreeman porque você tem que escolher uma das opções disponíveis de acordo com suas necessidades (por exemplo, "Adicionar ...") - Aprillion
Observe que você pode obter uma caixa de diálogo dizendo 'Esta fonte de dados não contém tabelas visíveis', seguida por um diálogo 'Assistente de consulta - Escolher colunas'. É de esta caixa de diálogo que você deseja clicar no botão 'Opções', marque a caixa de seleção 'Tabelas do sistema' para ver os dados que deseja consultar. - Tola Odejayi


Apoie a resposta aceita. Eu só quero enfatizar em "escolher colunas (se você não vir nenhuma lista de colunas, certifique-se de verificar Opções> Tabelas do sistema)"

Depois de selecionar o arquivo do Excel, muito provavelmente você verá this data source contains no visible tables prompt, e as guias e colunas disponíveis são nenhum. Microsoft admitiu que é um bug  que as guias nos arquivos do Excel são tratadas como "Tabelas do sistema" e a opção para "Tabelas do sistema" não é selecionada por padrão. Portanto, não entre em pânico nesta etapa, basta clicar em "opção" e marcar "Tabelas do sistema" e, em seguida, você verá as colunas disponíveis.


9





VLOOKUP e HLOOKUP podem ser usados ​​para procurar chaves primárias correspondentes (armazenadas vertical ou horizontalmente) e retornar valores de colunas / linhas de "atributo".


8



muito útil para folhas na mesma pasta de trabalho (mas acho que as funções INDEX + MATCH são ainda mais úteis), um pouco mais complicado ao atualizar dados de pastas de trabalho externas fechadas ... - Aprillion


Você não pode pré-formações de estilo SQL em tabelas do Excel no Excel. Dito isto, existem várias maneiras de realizar o que você está tentando fazer.

No Excel, como Reuben diz, as fórmulas que provavelmente funcionarão melhor são VLOOKUP e HLOOKUP. Em ambos os casos, você combina em uma linha única e retorna o valor da coluna \ row à esquerda / abaixo do ID encontrado.

Se você quiser adicionar apenas alguns campos extras à segunda lista, adicione as fórmulas à segunda lista. Se você quiser uma tabela de estilo "outer join", adicione o VLOOKUP fórmula para a primeira lista com ISNA para testar se a pesquisa foi encontrada. Se a Ajuda do Excel não fornecer detalhes suficientes sobre como usá-los em sua instância específica, informe-nos.

Se você preferir usar o SQL, vincule os dados ao programa do banco de dados, crie sua consulta e exporte os resultados de volta para o Excel. (No Access, você pode importar planilhas do Excel ou intervalos nomeados como uma tabela vinculada.)


4



na verdade, você pode - é chamado de Microsoft Query - Aprillion


Você pode usar o Microsoft Power Query, disponível para versões mais recentes do Excel (semelhante à resposta aceita, mas muito mais simples e fácil). Power Query chama junções 'mescla'.

A maneira mais fácil é ter suas 2 planilhas do Excel como tabelas do Excel. Em seguida, no Excel, vá para a guia da faixa de opções Power Query e clique no botão "Do Excel". Depois de importar as duas tabelas para o Power Query, selecione uma e clique em "Mesclar".


4



Muito mais fácil do que qualquer uma das outras opções propostas. Esta deve ser a resposta! Consulta de energia está incluído agora no Excel 2016 sob o Dados aba. - SliverNinja - MSFT
Outro recurso que parece não existir no Mac - juandesant


No XLTools.net, criamos uma boa alternativa para o MS Query funcionar especialmente com as Consultas SQL em tabelas do Excel. É chamado Consultas SQL do XLTools. É muito mais fácil de usar do que o MS Query e funciona muito bem se você precisa apenas criar e executar SQL - sem VBA, sem manipulações complexas com o MS Query ...

Com essa ferramenta, você pode criar qualquer consulta SQL em tabelas em pastas de trabalho do Excel usando o editor de SQL incorporado e executá-la imediatamente com a opção de colocar o resultado em uma nova ou qualquer planilha existente.

Você pode usar quase qualquer tipo de junção, incluindo LEFT OUTER JOIN (somente RIGHT OUTER JOIN e FULL OUTER JOIN não são suportados).

Aqui está um exemplo:

XLTools SQL Queries - Query Builder


3





Para o usuário do Excel 2007: Dados> De Outras Fontes> Do Microsoft Query> navegue até o arquivo do Excel

De acordo com Este artigo, a consulta do XLS versão 2003 pode resultar em "Esta fonte de dados não contém tabelas visíveis". erro porque suas planilhas são tratadas como tabela SYSTEM. Portanto, verifique as "Tabelas do sistema" nas opções do diálogo "Assistente de consulta - Escolher colunas" ao criar a consulta.

Para definir sua junção: Caixa de diálogo Microsoft Query> Menu Tabela> Junções ...

Para retornar dados para a planilha original do Excel, escolha "Retornar dados para a planilha do Excel" na caixa de diálogo Microsoft Query> menu Arquivo.


2