Como eu uno duas planilhas no Excel como faria em SQL?

Question

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.

    

Answer 1

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

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

Você agora 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 exemplo:

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

    

Answer 2

Aceite 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 provável que você veja o prompt this data source contains no visible tables e as guias e colunas disponíveis não sejam nenhuma. 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 está selecionada por padrão. Portanto, não entre em pânico nesta etapa, basta clicar em "opção" e verificar "Tabelas do sistema" e, em seguida, verá as colunas disponíveis.

    

Answer 3

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

    

Answer 4

Você não pode pré-formações de estilo SQL em tabelas do Excel no Excel. Dito isso, há 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 a fórmula VLOOKUP à primeira lista com ISNA para testar se a pesquisa foi localizada. 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 de 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.)

    

Answer 5

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). O Power Query chama as combinações de junções.

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".

    

Answer 6

No XLTools.net, criamos uma boa alternativa para o MS Query funcionar especialmente com consultas SQL em tabelas do Excel. É chamado de 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 o 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:

    

Answer 7

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 que funcionará.

Para definir sua junção: Diálogo de consulta da Microsoft > Menu de tabela > Junta-se ...

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.

    

Answer 8

Embora eu ache que a resposta do Aprillion usando o Microsoft Query é excelente, ele me inspirou a usar o Microsoft Access para unir as folhas de dados que achei muito mais fáceis.

Você precisa ter o MS Access instalado, é claro.

Etapas:

• Crie um novo banco de dados do Access (ou use um banco de dados de risco).
• Use Get External Data para importar seus dados do Excel como novas tabelas.
• Use Relationships para mostrar como suas tabelas são unidas.
• Defina o tipo de relacionamento para corresponder ao que você deseja (representando a junção à esquerda, etc.)
• Crie uma nova consulta que junte suas tabelas.
• Use External Data->Export to Excel para gerar seus resultados.

Eu realmente não poderia ter feito isso sem a ótima resposta do Aprillion.

    

Answer 9

Se você estiver familiarizado o suficiente com bancos de dados, poderá usar o SQL Server para conectar as duas planilhas como Servidores Vinculados e, em seguida, usar o T-SQL para executar o trabalho de dados de back-end. Em seguida, termine conectando o Excel de volta ao SQL e puxe os dados para uma tabela (regular ou dinâmica). Você também pode considerar o uso do Powerpivot; ele permitirá junções entre quaisquer fontes de banco de dados, incluindo o Excel usado como bancos de dados simples.

    

Answer 10

Olhando para o mesmo problema, deparei-me com RDBMerge , que eu acho que é uma maneira amigável para Mesclar dados de várias pastas de trabalho do Excel, arquivos csv e xml em uma pasta de trabalho de resumo.