>
Hay Paula, você pode comparar listas no Excel?
ou
>
>
Qual é a melhor maneira de comparar dois conjuntos de dados no Excel?
Muitas vezes há um requisito no Excel para comparar duas listas, ou dois conjuntos de dados para encontrar itens ausentes ou que coincidam. Como este é o Excel, há sempre mais de uma maneira de fazer as coisas, incluindo a comparação de dados. Desde fórmulas e Formatação Condicional até Power Query. Neste artigo vamos analisar várias maneiras de comparar duas listas no Excel e também vamos analisar a comparação de linhas inteiras de um conjunto de dados.
Talvez a sua maneira preferida não esteja incluída abaixo. Se não, por que não deixar um comentário abaixo e compartilhar conosco como você gosta de comparar duas listas ou conjuntos de dados no Excel. Estou ansioso para ler seus comentários.
Desejamos comparar a Lista 1 com a Lista 2.
Conteúdo
Formatação Condicional Rápida para comparar duas colunas de dados
Limpar a Formatação Condicional
Comparar dados no Excel usando a função MATCH
Comparar 2 listas no Excel 365 com MATCH ou XMATCH como Função Dynamic Array
MATCH e Matrizes Dinâmicas para comparar 2 listas
XMATCH Excel 365 para comparar duas listas
Tabelas – Comparando listas no Excel onde os tamanhos das faixas podem mudar
Diferenças de luz alta em Listas usando Formatação Condicional Personalizada
Cópia fórmula para Formatação Condicional Personalizada
Outras Fórmulas usadas para comparar duas listas no Excel
VLOOKUP para comparar duas listas no Excel
XLOOKUP para comparar duas listas no Excel
COUNTIF para comparar duas listas no Excel
Como comparar dois conjuntos de dados no Excel
Comparando listas ou conjuntos de dados usando Power Query
Formatação rápida condicional para comparar duas colunas de dados
Formatação condicional permitirá destacar uma célula ou intervalo com base em critérios pré-definidos. A maneira mais rápida e simples de comparar visualmente estas duas colunas rapidamente é usar a regra de valor duplicado de destaque predefinido.
Inicie selecionando as duas colunas de dados.
Na guia Home, selecione a drop down Formatação condicional. Em seguida, selecione Highlight Cells Rules (Destacar regras das células). A seguir selecione Duplicar valores.
A caixa de configurações de Duplicar valores será aberta onde você pode definir a formatação e selecionar entre Duplicar ou Valores Únicos.
Selecionando Duplicar valores, todos os lançamentos recorrentes serão definidos para a formatação selecionada. Agora você pode ver rapidamente os itens da Lista 1 que estão na Lista 2, pois estes são os itens formatados. Você também pode ver rapidamente os itens da lista 2 que não estão na lista 1, pois estes não possuem formatação aplicada.
No entanto, você também pode formatar os itens únicos. Isto pode ser conseguido selecionando Unique da caixa de configuração Duplicate Values setup.
Neste caso, nós aplicamos dois formatos condicionais diferentes. O vermelho indicando as duplicatas e o verde indicando os itens únicos.
Nota, eu não apenas peguei as células com dados, eu peguei todas as colunas A a C. A coluna B não tem dados, portanto não pode afetar os resultados. Entretanto, estas células contêm a formatação condicional aplicada, então seria melhor selecionar apenas as células necessárias.
Limpar a formatação condicional
Para limpar toda a formatação condicional, primeiro selecione a célula, ou intervalo. Em seguida, selecione a formatação condicional drop down na fita Home. Em seguida, selecione Limpar Regras. Finalmente selecione Clear Rules from Selected Cells.
Se você tiver mais de uma formatação condicional aplicada de uma vez e quiser remover apenas uma delas, selecione Manage Rules no menu suspenso de formatação condicional. Selecione a regra que você deseja excluir e depois selecione Delete Rule.
Ao pressionar OK, a regra será removida do Gerenciador de Regras e as células não conterão mais a formatação.
> Então essa é a maneira mais básica de comparar duas listas no Excel. É rápido, simples e eficaz. Você também pode aplicar uma formatação condicional baseada em fórmulas, que veremos mais adiante neste artigo.
Combinar dados no Excel usando a função MATCH
Existem muitas fórmulas de pesquisa que você pode usar para comparar dois intervalos ou listas no Excel. A primeira é a função MATCH.
A função MATCH retorna a posição relativa em uma lista. Um número baseado na sua posição, se encontrado, no array de busca.
A sintaxe para MATCH é
=MATCH (valor de busca, array de busca, tipo de correspondência)
Onde o valor de busca é o valor para o qual você quer encontrar uma correspondência. Lookup array é a lista na qual você está procurando por uma correspondência. E Match type permite que você selecione entre uma correspondência exata ou aproximada.
Queremos escrever uma fórmula de correspondência para ver se os itens da Lista 2 estão na Lista 1.
Na célula E3 podemos inserir a fórmula
=MATCH(C2, $A$2:$A$21,0)
Ao preencher esta fórmula, para os valores onde o Excel encontrar uma correspondência, a posição dessa correspondência será retornada. Onde não há correspondência, o valor de retorno será um #N/A.
Muitas vezes, a posição relativa ou o #N/A não tem valor para nós e precisamos converter esses valores em verdadeiros ou falsos. Para fazer isso, podemos facilmente expandir a nossa fórmula Match usando uma função lógica. Como Match retorna um número, podemos usar a função ISNUMBER
=ISNUMBER(MATCH(C2, $A$2:$A$21,0))
Comparar 2 listas no Excel 365 com MATCH ou XMATCH como uma função Dynamic Array
Se você estiver usando o Excel 365, você tem outras alternativas ao usar o MATCH para comparar listas ou dados. Como o Excel 365 pensa em arrays, agora podemos passar um array como o valor de pesquisa do MATCH e nossos resultados irão derramar para nós. Isto elimina a necessidade de copiar a fórmula para baixo e com apenas 1 fórmula, sua planilha será menos propensa a erros e mais compacta.
MATCH e arrays dinâmicos para comparar 2 listas
Se você ainda não está familiarizado com arrays dinâmicos, eu sugeriria que você lesse este artigo: Excel Dynamic Arrays – Uma nova maneira de modelar suas planilhas do Excel: para entender melhor como elas funcionam e os intervalos de vazamento.
A única mudança para a fórmula de correspondência é ao invés de selecionar a célula C2 como nosso valor de pesquisa, vamos selecionar o intervalo C2:12
=NUMBER(MATCH (C2:C12,$A$2:$A$21,0))
Como você pode ver, a única fórmula derrama os resultados na coluna E.
XMATCH Excel 365 para comparar duas listas
Excel 365 também introduz a nova função XMATCH. Assim como a função MATCH, XMATCH retorna uma posição relativa em uma lista. Agora você já conhece o XLOOKUP, que substitui a antiga função VLOOKUP, você sabe que o XLOOKUP vem com potência adicional. Isto vem na forma de novas condições na sintaxe da fórmula, como o modo de pesquisa e tipos de correspondência. Bem, XMATCH também como esta potência extra sobre o seu predecessor MATCH.
A sintaxe para XMATCH é
XMATCH (Lookup Value, Lookup Array, ,)
Where
Lookup Value é o valor que você está procurando para encontrar a posição relativa
Lookup Array é a linha ou coluna que contém o Lookup Value
Match mode é opcional. Ao contrário da antiga função MATCH, o padrão é uma correspondência exata. Você também pode selecionar entre
- Partida exata ou a próxima menor
- Partida exata ou a próxima maior
- Partida com cartão inteligente
O modo de pesquisa também é opcional. O padrão (e única opção na antiga função MATCH) é olhar de cima para baixo. Você também pode selecionar a última para a primeira e as buscas binárias. Se você estiver trabalhando com buscas binárias. A opção wildcard match não funciona.
Com o XMATCH podemos usar Arrays dinâmicos ou referências de células para criar a fórmula, tal como já vimos com o MATCH. Para este exemplo, vamos usar Arrays Dinâmicos. A fórmula é muito similar ao que usamos com MATCH; exceto que não temos que selecionar 0 para uma correspondência exata como no XMATCH esta é a configuração padrão. Vamos misturar um pouco as coisas desta vez um olhar para encontrar itens na lista 2 e não na lista 1.
Neste caso podemos usar a fórmula
=NÃO(ISNUMBER(XMATCH(C2:C12,A2:A21)))
Onde não vai transformar trues em falsas e falsas em trues.
Tabelas – Comparando listas no Excel onde os tamanhos dos intervalos podem mudar
Em cada uma das fórmulas que examinamos até agora, selecionamos um intervalo de células em nossas funções de Partida que não é dinâmico. Isso significa que se adicionarmos novos dados a uma das listas, temos um passo manual para atualizar nossa fórmula e incluir os novos dados.
Para converter as listas em tabelas, selecione uma das listas e pressione CTRL. Este é o atalho do teclado para converter para uma tabela. Se você selecionou o cabeçalho no intervalo de células, certifique-se de marcar a caixa para confirmar que sua tabela tem cabeçalhos.
Tables pela sua natureza usam nomenclatura estruturada. Portanto, quando você estiver escrevendo uma fórmula e selecionar uma coluna de uma tabela, ela não irá mostrar referências de células, mas o nome da coluna.
Localizando nossa fórmula anterior usando XMATCH para encontrar itens na lista 2 e não na lista 1, podemos reescrever esta função agora usando nossas referências de tabela
=NÃO (ISNUMBER(XMATCH(list2,lista1))
Agora, como temos usado tabelas, se adicionarmos uma nova linha a qualquer uma das tabelas, nossa faixa de derramamento também irá aumentar para incluir os novos dados.
Destacar diferenças em Listas usando Formatação Condicional Personalizada
Earlier neste artigo nós olhamos para uma maneira muito rápida de comparar estas duas listas usando uma regra predefinida para duplicatas. No entanto, também podemos usar a Formatação Condicional Personalizada. Se não está familiarizado com a Formatação Condicional Personalizada, sugiro que consulte este artigo: Truques de Formatação Condicional Dinâmica do Excel:
Temos de olhar para duas abordagens diferentes aqui para destacar diferenças. Quando não estivermos usando tabelas e tivermos criado uma fórmula verdadeiro/falso para identificar as diferenças, podemos pegar uma cópia da fórmula e adicioná-la à nossa formatação personalizada. Entretanto, com Tabelas precisamos forçar o uso de referências de células.
Copiar fórmula para Formatação Condicionada Personalizada
Começar pegando uma cópia da fórmula. Como nós testamos a fórmula na planilha, podemos ver que ela funciona antes de usá-la na formatação condicional. Esta é a melhor prática, pois muitas vezes com referências de células relativas e absolutas pode ser difícil obter a fórmula correta.
Selecione as células às quais você deseja aplicar a formatação personalizada. Depois a partir da fita Home selecione a formatação condicional e selecione New Rule
A caixa de configuração New Formatting Rule set up será aberta e selecione Use Formula para determinar quais células devem ser formatadas. Depois cole na fórmula e defina o tipo de formatação
Isto resultará na formatação de todas as células que estão em ambas as listas para o formato escolhido.
Keep, tendo em conta que seleccionámos uma gama de células para aplicar esta formatação condicional e não é dinâmica. Caso utilizássemos tabelas, isto seria atualizado sem a necessidade de alterar nada!
Outras fórmulas usadas para comparar duas listas no Excel
Há muitas fórmulas que você pode usar para comparar duas listas no Excel. Já analisamos MATCH e XMATCH, mas agora vamos analisar mais algumas. Qualquer uma das funções de pesquisa irá realmente funcionar junto com algumas outras!
VLOOKUP para comparar duas listas no Excel
Se você não está familiarizado com VLOOKUP, você pode ler sobre isso aqui. Simplesmente coloque VLOOKUP irá retornar um valor correspondente de uma célula, se não houver um valor correspondente um erro #N/A será retornado. No nosso exemplo, estamos trabalhando com texto. Assim, podemos realizar um VLOOOKUP e testar para ver se ele retorna texto. Se estivéssemos usando números então poderíamos substituir ISTEXT por ISNUMBER.
Podíamos usar a função =ISTEXT(VLOOKUP(C2, $A$2:$A$21,1,FALSE))
Or se estivéssemos usando arrays dinâmicos no Excel 365, poderíamos usar a função
=ISTEXT(VLOOKUP (C2:C12,$A$2:$A$21,1,FALSE))
XLOOKUP para comparar duas listas no Excel
XLOOKUP foi introduzido no Excel 365 e você pode saber mais sobre ele aqui. Muito parecido com o VLOOOKUP, o XLOOKUP irá retornar um valor correspondente de uma célula, e você pode definir um resultado se o valor não for encontrado. Usando arrays dinâmicos, a função seria
=ISTEXT(XLOOKUP (C2:C12,A2:A21,A2:A21))
COUNTIF para comparar duas listas no Excel
A função COUNTIF contará o número de vezes que um valor, ou texto está contido dentro de um intervalo. Se o valor não for encontrado, 0 é retornado. Podemos combinar isso com uma declaração IF para retornar nossos valores verdadeiros e falsos.
=IF(COUNTIF (A2:A21,C2:C12)<>0, “True”, “False”)
Como comparar dois conjuntos de dados no Excel
Comparar duas listas é fácil o suficiente e agora analisamos várias maneiras de fazer isso. Mas comparar dois conjuntos de dados pode ser um pouco mais difícil.
Deixe-nos ver um exemplo. Nós temos duas tabelas de dados, cada uma contendo os mesmos cabeçalhos de coluna. Olhando para a imagem, podemos ver que a correspondência destas duas tabelas exigiria olhar para mais de uma coluna.
Quando você precisa olhar para mais de uma coluna, a solução seria criar uma coluna composta combinando os dados em uma coluna. Isto irá criar uma coluna única para cada linha que podemos então usar como a coluna correspondente
Neste exemplo poderíamos combinar o Nome e o DoB para dar a cada tabela um identificador único
Existem muitas maneiras de juntar o conteúdo de uma célula, neste caso vamos fazer uma simples concatenação. Como estamos a utilizar tabelas, a fórmula irá calçar o formato de nomenclatura da tabela.
= &”-“&
Repetir os passos da segunda tabela.
Agora podemos usar qualquer um dos exemplos acima para combinar estas duas novas colunas de dados. Onde eles correspondem, sabemos que correspondem a toda a linha.
Comparar listas ou conjuntos de dados usando o Power Query
Você também pode comparar listas e conjuntos de dados usando o Excels Power Query. Ao conectar-se às tabelas e depois fundir as tabelas, usando diferentes tipos de join, podemos comparar ambas as listas.
Neste vídeo você aprenderá como comparar ou reconciliar dois conjuntos de dados diferentes usando Excels Power Query
Existe um conjunto de dados para acompanhar este vídeo e praticar ao longo do qual você pode pegar a partir deste artigo.