Mesclando duas listas sem duplicatas

Uma situação clássica: você tem duas listas que precisam ser mescladas em uma. Além disso, nas listas iniciais pode haver elementos únicos e correspondentes (tanto entre as listas quanto dentro), mas na saída você precisa obter uma lista sem duplicatas (repetições):

Tradicionalmente, vejamos várias maneiras de resolver um problema tão comum – do primitivo “na testa” ao mais complexo, mas elegante.

Método 1: remover duplicatas

Você pode resolver o problema da maneira mais simples – copie manualmente os elementos de ambas as listas em uma e depois aplique a ferramenta ao conjunto resultante. Remover Duplicados da guia Data (Dados — Remover Duplicatas):

Obviamente, esse método não funcionará se os dados nas listas de origem forem alterados com frequência - você terá que repetir todo o procedimento após cada alteração novamente. 

Método 1a. tabela dinâmica

Este método é, de fato, uma continuação lógica do anterior. Se as listas não forem muito grandes e o número máximo de elementos nelas for conhecido com antecedência (por exemplo, não mais que 10), você poderá combinar duas tabelas em uma por links diretos, adicionar uma coluna com as da direita e construir uma tabela de resumo com base na tabela resultante:

Como você sabe, a tabela dinâmica ignora repetições, portanto, na saída, obteremos uma lista combinada sem duplicatas. A coluna auxiliar com 1 é necessária apenas porque o Excel pode construir tabelas de resumo contendo pelo menos duas colunas.

Quando as listas originais forem alteradas, os novos dados irão para a tabela combinada por meio de links diretos, mas a tabela dinâmica terá que ser atualizada manualmente (clique com o botão direito – Atualizar e salvar). Se você não precisar de recálculo imediato, é melhor usar outras opções.

Método 2: Fórmula de matriz

Você pode resolver o problema com fórmulas. Nesse caso, o recálculo e a atualização dos resultados ocorrerão de forma automática e instantânea, imediatamente após as alterações nas listas originais. Por conveniência e brevidade, vamos dar nomes às nossas listas. Lista 1 и Lista 2utilização Gerente de Nomes aba Fórmula (Fórmulas — Gerenciador de Nomes — Criar):

Após a nomeação, a fórmula que precisamos ficará assim:

À primeira vista, parece assustador, mas, na verdade, nem tudo é tão assustador. Deixe-me expandir esta fórmula em várias linhas usando a combinação de teclas Alt+Enter e recuar com espaços, como fizemos, por exemplo aqui:

A lógica aqui é a seguinte:

• A fórmula INDEX(List1;MATCH(0;COUNTIF($E$1:E1;List1); 0) seleciona todos os elementos únicos da primeira lista. Assim que eles acabam, começa a dar um erro #N/A:

  

• A fórmula INDEX(List2;MATCH(0;COUNTIF($E$1:E1;List2); 0)) extrai os elementos únicos da segunda lista da mesma forma.
• Aninhadas uma na outra, duas funções SEERRO implementam a saída primeiro das únicas da lista-1 e, em seguida, da lista-2, uma após a outra.

Observe que esta é uma fórmula de matriz, ou seja, após a digitação, deve ser inserida em uma célula que não seja comum Entrar, mas com um atalho de teclado Ctrl+Shift+Entrar e, em seguida, copie (arraste) para as células filhas com uma margem.

Na versão em inglês do Excel, esta fórmula se parece com:

=SEERRO(SEERRO(ÍNDICE(Lista1, CORRESP(0, CONT.SE($E$1:E1, Lista1), 0)), ÍNDICE(Lista2, CORRESP(0, CONT.SE($E$1:E1, Lista2), 0)) ), “”) 

A desvantagem dessa abordagem é que as fórmulas de matriz reduzem notavelmente o trabalho com o arquivo se as tabelas de origem tiverem um número grande (várias centenas ou mais) de elementos. 

Método 3. Consulta de energia

Se suas listas de origem tiverem um grande número de elementos, por exemplo, várias centenas ou milhares, em vez de uma fórmula de matriz lenta, é melhor usar uma abordagem fundamentalmente diferente, ou seja, as ferramentas de suplemento do Power Query. Este suplemento é incorporado ao Excel 2016 por padrão. Se você tiver o Excel 2010 ou 2013, poderá baixá-lo e instalá-lo separadamente (gratuitamente).

O algoritmo de ações é o seguinte:

1. Abra uma guia separada do complemento instalado Consulta de energia (se você tiver o Excel 2010-2013) ou apenas vá para a guia Data (se você tiver o Excel 2016).
2. Selecione a primeira lista e pressione o botão Da tabela/intervalo (Do intervalo/tabela). Quando perguntados sobre a criação de uma “mesa inteligente” da nossa lista, concordamos:

   

3. A janela do editor de consulta é aberta, onde você pode ver os dados carregados e o nome da consulta tabela 1 (você pode alterá-lo para o seu próprio, se quiser).
4. Dê um duplo clique no cabeçalho da tabela (palavra Lista 1) e renomeie-o para qualquer outro (por exemplo Pessoas). O que exatamente nomear não é importante, mas o nome inventado deve ser lembrado, porque. ele terá que ser usado novamente mais tarde ao importar a segunda tabela. A fusão de duas tabelas no futuro só funcionará se os títulos das colunas corresponderem.
5. Expanda a lista suspensa no canto superior esquerdo fechar e baixar e escolha Fechar e carregar… (Fechar e carregar para…):

   

6. Na próxima caixa de diálogo (pode parecer um pouco diferente - não se assuste), selecione Basta criar uma conexão (Apenas criar conexão):

   

7. Repetimos todo o procedimento (pontos 2-6) para a segunda lista. Ao renomear um cabeçalho de coluna, é importante usar o mesmo nome (Pessoas) da consulta anterior.
8. Na janela do Excel na guia Data ou na aba Consulta de energia Escolha Obter dados – combinar solicitações – adicionar (Obter dados — mesclar consultas — anexar):

   

9. Na caixa de diálogo exibida, selecione nossas solicitações nas listas suspensas:

   

10. Como resultado, obteremos uma nova consulta, onde duas listas serão conectadas uma sob a outra. Resta remover duplicatas com o botão Excluir Linhas - Remover Duplicatas (Excluir linhas — excluir duplicatas):

    

11. A consulta concluída pode ser renomeada no lado direito do painel de opções, dando-lhe um nome sensato (este será o nome da tabela de resultados de fato) e tudo pode ser carregado na planilha com o comando fechar e baixar (Fechar e carregar):

    

No futuro, com quaisquer alterações ou acréscimos nas listas originais, bastará clicar com o botão direito do mouse para atualizar a tabela de resultados.

• Como coletar várias tabelas de arquivos diferentes usando o Power Query
• Extraindo itens exclusivos de uma lista
• Como comparar duas listas entre si para correspondências e diferenças