Como simular uma junção externa completa no Excel?

Question

Vamos supor que eu tenha alguns dados no Excel (e não em um banco de dados real). Em uma planilha, tenho dados, em que uma coluna funciona como a ID, e verifiquei se os valores dessa coluna são exclusivos. Em outra planilha, eu também tenho alguns dados, novamente com uma coluna que pode ser tomada como um ID, e também é única. Se a linha N na Folha 1 tiver algum valor e a linha M na Folha 2 tiver o mesmo valor, tenho certeza de que a linha N e a linha M descrevem o mesmo objeto do mundo real.

O que estou perguntando: como posso obter o equivalente a uma junção externa completa sem escrever macros? Fórmulas e todas as funções acessíveis através da fita são OK.

Um pequeno exemplo de "dados de reprodução":

Folha 1:

Dostoyevski    Russia
Pushkin        Russia
Shelley        England
Flaubert       France
Hugo           France
Eichendorff    Germany
Byron          England
Zola           France

Folha 2:

Shelley        Percy Bysshe
Eichendorff    Josef Freiherr Von
Flaubert       Gustave
Byron          Lord
Keller         Gottfried
Dostoyevski    Fyodor
Zola           Emile
Balzac         Honoré de

Saída desejada (a classificação não é importante):

Dostoyevski    Russia   Fyodor
Pushkin        Russia
Shelley        England  Percy Bysshe
Flaubert       France   Gustave
Hugo           France
Eichendorff    Germany  Josef Freiherr von
Byron          England  Lord
Zola           France   Emile
Keller                  Gottfried
Balzac                  Honoré de

Para todos que estão horrorizados com este cenário: Eu sei que esta é a maneira errada de fazê-lo. Se eu tiver alguma escolha, eu não usaria o Excel para isso. No entanto, há situações suficientes lá fora, onde uma solução pragmática é necessária, stat, e uma solução melhor (do ponto de vista da TI) não pode ser aplicada.

    

Answer 1

Fácil abordagem - operações padrão do Excel

Primeiro, copie / cole as duas colunas-chave de ambas as tabelas em uma única folha nova como uma única coluna.

Use a opção "Remover duplicados" para obter uma lista única de todas as suas chaves exclusivas.

Em seguida, adicione duas colunas (neste caso), uma para cada coluna de dados em cada tabela. Eu recomendo que você use o formato como opção de tabela também, pois faz suas fórmulas parecerem muito mais agradáveis. Usando vlookup, use a seguinte fórmula:

=IFERROR(VLOOKUP([@ID],Sheet4!A:B,2,FALSE),"")

Em que Sheet4!A:B representa qualquer que seja a tabela de dados da tabela de origem para cada valor respectivo. O IFERROR impede os resultados # N / A feios que aparecem quando o vlookup não é bem sucedido e neste caso retorna uma célula em branco.

Isso fornece sua tabela resultante.

Folha3:

Folha4:

Dadosdoresultado:

Fórmulasderesultado(Ctrl+~alternaráisso):

ConstruídoemconsultaSQL

VocêtambémpodefazerissocomaconsultaSQLinterna.Émuitomenosamigável,mastalvezsejaumcasodeusomelhor.Issoprovavelmenteexigiráquevocêtenhaformatadoseusdadosde"origem" como tabelas.

1. Clique em uma célula em uma nova planilha
2. Ir para dados - > De outras fontes - > Do Microsoft Query
3. Selecione Arquivos do Excel * na guia Bancos de Dados e clique em OK
4. Selecione sua pasta de trabalho
5. Selecione os quatro campos a seguir:
   • 
6. Cliqueem"next" e "ok" no aviso formatado dos anos 90 que você vê
7. Siga as estas instruções para criar a primeira associação externa à esquerda. No meu caso, estou usando a tabela "países" como a fonte da esquerda e os "nomes" como a direita.
   • 
   • Issosódáalgumasdaslinhas(desdequevocêentrenoID)

8. Aparte"criar uma subtração de junção e adicioná-la como união" é mais complicada.

   • Aqui estão as configurações de junção de subtração:
   • Copie o SQL dessa junção no botão SQL:

   • SELECT countries$.ID, countries$.Val1, names$.ID, names$.Val2 FROM {oj C:\Users\Username\Desktop\Book2.xlsx.countries$ countries$ LEFT OUTER JOIN C:\Users\Username\Desktop\Book2.xlsx.names$ names$ ON countries$.ID = names$.ID} WHERE (names$.ID Is Null)

9. Volte para a primeira junção externa que você criou. Edite manualmente o SQL e

   • adicione Union ao final
   • Adicione o texto de junção de subtração acima à parte inferior da junção
10. Pressione o botão "Return Data" imediatamente à esquerda do botão SQL
    • Você pode querer editar o SQL para selecionar apenas os dados específicos que deseja neste momento. Eu acho mais fácil esconder colunas no resultado.
11. Coloque a consulta em algum lugar e confirme sua localização
    • 

Não para os fracos de coração. Mas se você quiser uma grande chance de ver algumas partes do Office que não são atualizadas enquanto você pode estar vivo, é uma grande chance.

    

Answer 2

Como uma solução alternativa, posso sugerir Power Query ? É um suplemento Excel gratuito da Microsoft para basicamente executar exatamente esse tipo de coisa. Sua funcionalidade será incluída diretamente no Excel 2016 também, por isso é protegida pelo futuro.

De qualquer forma, com o Power Query, os passos são bem simples:

1. Importe as duas tabelas como consultas no Power Query Editor.
2. Execute uma transformação de mesclagem de consultas, definindo a coluna de união apropriada e definindo o tipo de associação como Outer completo.
3. Carregue sua tabela de resultados em uma nova planilha.

O bom disso é que, depois de configurá-lo, se você fizer alterações em suas tabelas de dados de base, basta acessar Data > Atualizar tudo e sua folha de resultados do Power Query também é atualizada.