Um dos muitos recursos do Microsoft Excel é a capacidade de comparar duas listas de dados, identificando correspondências entre as listas e identificando quais itens são encontrados em apenas uma lista. Isso é útil ao comparar registros financeiros ou verificar se um nome específico está em um banco de dados. Você pode usar a função MATCH para identificar e marcar registros correspondentes ou não correspondentes ou pode usar a formatação de condicionamento com a função CONT. SE. As etapas a seguir mostram como usar cada um para combinar seus dados.
Passos
Método 1 de 2: Identificando registros com a função MATCH
Etapa 1. Copie as listas de dados em uma única planilha
O Excel pode trabalhar com várias planilhas em uma única pasta de trabalho ou com várias pastas de trabalho, mas você verá que comparar as listas é mais fácil se copiar suas informações em uma única planilha.
Etapa 2. Dê a cada item da lista um identificador exclusivo
Se as suas duas listas não compartilham uma maneira comum de identificá-los, você pode precisar adicionar uma coluna adicional a cada lista de dados que identifica aquele item no Excel para que ele possa ver se um item em uma determinada lista está relacionado a um item na outra lista. A natureza desse identificador dependerá do tipo de dados que você está tentando corresponder. Você precisará de um identificador para cada lista de colunas.
- Para dados financeiros associados a um determinado período, como registros fiscais, pode ser a descrição de um ativo, a data em que o ativo foi adquirido ou ambos. Em alguns casos, uma entrada pode ser identificada com um número de código; no entanto, se o mesmo sistema não for usado para ambas as listas, esse identificador pode criar correspondências onde não há correspondências ou ignorar correspondências que devem ser feitas.
- Em alguns casos, você pode pegar itens de uma lista e combiná-los com itens de outra lista para criar um identificador, como uma descrição de ativo físico e o ano em que foi comprado. Para criar tal identificador, você concatena (adiciona, combina) dados de duas ou mais células usando o E comercial (&). Para combinar uma descrição de item na célula F3 com uma data na célula G3, separada por um espaço, você deve inserir a fórmula '= F3 & "" & G3' em outra célula dessa linha, como E3. Se você quiser incluir apenas o ano no identificador (porque uma lista usa datas completas e a outra usa apenas anos), inclua a função ANO inserindo '= F3 & "" & ANO (G3)' na célula E3. (Não inclua as aspas simples; elas existem apenas para indicar o exemplo.)
- Depois de criar a fórmula, você pode copiá-la em todas as outras células da coluna do identificador, selecionando a célula com a fórmula e arrastando a alça de preenchimento sobre as outras células da coluna onde deseja copiar a fórmula. Ao soltar o botão do mouse, cada célula que você arrastou será preenchida com a fórmula, com as referências de célula ajustadas às células apropriadas na mesma linha.
Etapa 3. Padronizar os dados sempre que possível
Enquanto a mente reconhece que "Inc." e "Incorporado" significam a mesma coisa, o Excel não, a menos que você re-formate uma palavra ou outra. Da mesma forma, você pode considerar valores como $ 11,950 e $ 11,999,95 como próximos o suficiente para corresponder, mas o Excel não o fará, a menos que você o diga.
- Você pode lidar com algumas abreviações, como "Co" para "Empresa" e "Inc" para "Incorporado usando a função de string LEFT para truncar os caracteres adicionais. Outras abreviações, como" Assn "para" Associação ", podem ser tratado estabelecendo um guia de estilo de entrada de dados e, em seguida, escrevendo um programa para pesquisar e corrigir formatos inadequados.
- Para sequências de números, como códigos postais em que algumas entradas incluem o sufixo ZIP + 4 e outras não, você pode usar novamente a função de sequência LEFT para reconhecer e corresponder apenas aos códigos postais principais. Para que o Excel reconheça valores numéricos próximos, mas não iguais, você pode usar a função ARRED para arredondar valores próximos para o mesmo número e combiná-los.
- Espaços extras, como digitar dois espaços entre palavras em vez de um, podem ser removidos usando a função TRIM.
Etapa 4. Crie colunas para a fórmula de comparação
Assim como você teve que criar colunas para os identificadores da lista, você precisará criar colunas para a fórmula que faz a comparação para você. Você precisará de uma coluna para cada lista.
Você vai querer rotular essas colunas com algo como "Faltando?"
Etapa 5. Insira a fórmula de comparação em cada célula
Para a fórmula de comparação, você usará a função MATCH aninhada dentro de outra função do Excel, ISNA.
- A fórmula assume a forma de "= ISNA (MATCH (G3, $ L $ 3: $ L $ 14, FALSE))", onde uma célula da coluna identificadora da primeira lista é comparada com cada um dos identificadores na segunda lista para veja se corresponde a um deles. Se não corresponder, um registro está faltando e a palavra "TRUE" será exibida nessa célula. Se corresponder, o registro está presente e a palavra "FALSE" será exibida. (Ao inserir a fórmula, não inclua as aspas.)
- Você pode copiar a fórmula nas células restantes da coluna da mesma forma que copiou a fórmula do identificador de célula. Nesse caso, apenas a referência de célula para a célula do identificador muda, visto que colocar o cifrão na frente das referências de linha e coluna para a primeira e a última célula na lista dos identificadores da segunda célula as torna referências absolutas.
- Você pode copiar a fórmula de comparação da primeira lista para a primeira célula da coluna da segunda lista. Em seguida, você terá que editar as referências de célula para que "G3" seja substituído pela referência da primeira célula identificadora da segunda lista e "$ L $ 3: $ L $ 14" seja substituído pela primeira e última célula identificadora do segunda lista. (Deixe os cifrões e dois pontos sozinhos.) Você pode então copiar esta fórmula editada nas células restantes na linha de comparação da segunda lista.
Etapa 6. Classifique as listas para ver os valores não correspondentes com mais facilidade, se necessário
Se suas listas forem grandes, pode ser necessário classificá-las para colocar todos os valores não correspondentes juntos. As instruções nas subetapas abaixo converterão as fórmulas em valores para evitar erros de recálculo e, se suas listas forem grandes, evitarão um longo tempo de recálculo.
- Arraste o mouse sobre todas as células em uma lista para selecioná-lo.
- Selecione Copiar no menu Editar no Excel 2003 ou no grupo Área de transferência da faixa de opções Início no Excel 2007 ou 2010.
- Selecione Colar especial no menu Editar no Excel 2003 ou no botão suspenso Colar no grupo Área de transferência da faixa inicial do Excel 2007 ou 2010.
- Selecione "Valores" na lista Colar como na caixa de diálogo Colar especial. Clique em OK para fechar a caixa de diálogo.
- Selecione Classificar no menu Dados no Excel 2003 ou o grupo Classificar e Filtrar da faixa de opções Dados no Excel 2007 ou 2010.
- Selecione "Linha de cabeçalho" na lista "Meu intervalo de dados tem" na caixa de diálogo Classificar por, selecione "Falta?" (ou o nome que você realmente deu ao cabeçalho da coluna de comparação) e clique em OK.
- Repita essas etapas para a outra lista.
Etapa 7. Compare os itens não correspondentes visualmente para ver por que eles não coincidem
Conforme observado anteriormente, o Excel foi projetado para procurar correspondências exatas de dados, a menos que você o configure para procurar correspondências aproximadas. Sua não correspondência pode ser tão simples quanto uma transposição acidental de letras ou dígitos. Também pode ser algo que requer verificação independente, como verificar se os ativos listados precisam ser relatados em primeiro lugar.
Método 2 de 2: formatação condicional com CONT. SE
Etapa 1. Copie as listas de dados em uma única planilha
Etapa 2. Decida em qual lista você deseja destacar os registros correspondentes ou não correspondentes
Se você deseja destacar os registros em apenas uma lista, provavelmente desejará destacar os registros exclusivos dessa lista; ou seja, registros que não correspondem aos registros da outra lista. Se você deseja destacar os registros em ambas as listas, você deseja destacar os registros que correspondem entre si. Para os fins deste exemplo, assumiremos que a primeira lista ocupa as células G3 a G14 e a segunda lista ocupa as células L3 a L14.
Etapa 3. Selecione os itens na lista em que deseja destacar itens exclusivos ou correspondentes
Se desejar destacar os itens correspondentes em ambas as listas, você terá que selecionar as listas uma de cada vez e aplicar a fórmula de comparação (descrita na próxima etapa) a cada lista.
Etapa 4. Aplique a fórmula de comparação apropriada
Para fazer isso, você terá que acessar a caixa de diálogo Formatação condicional na sua versão do Excel. No Excel 2003, você faz isso selecionando Formatação condicional no menu Formatar, enquanto no Excel 2007 e 2010, você clica no botão Formatação condicional no grupo Estilos da faixa inicial. Selecione o tipo de regra como "Fórmula" e insira sua fórmula no campo Editar a descrição da regra.
- Se você quiser destacar os registros exclusivos da primeira lista, a fórmula seria "= COUNTIF ($ L $ 3: $ L $ 14, G3 = 0)", com o intervalo de células da segunda lista processado como valores absolutos e a referência para a primeira célula da primeira lista como um valor relativo. (Não insira aspas fechadas.)
- Se você quiser destacar os registros exclusivos da segunda lista, a fórmula seria "= COUNTIF ($ G $ 3: $ G $ 14, L3 = 0)", com o intervalo de células da primeira lista processado como valores absolutos e a referência para a primeira célula da segunda lista como um valor relativo. (Não insira aspas fechadas.)
- Se quiser destacar os registros de cada lista que se encontram na outra lista, você precisará de duas fórmulas, uma para a primeira lista e outra para a segunda. A fórmula para a primeira lista é "= CONT. SE ($ L $ 3: $ L $ 14, G3> 0)", enquanto a fórmula para a segunda lista é CONT. SE ($ G $ 3: $ G $ 14, L3> 0) ". observado anteriormente, você seleciona a primeira lista para aplicar sua fórmula e, a seguir, seleciona a segunda lista para aplicar sua fórmula.
- Aplique qualquer formatação que você deseja destacar os registros sendo sinalizados. Clique em OK para fechar a caixa de diálogo.
Vídeo - Ao usar este serviço, algumas informações podem ser compartilhadas com o YouTube
Pontas
- Em vez de usar uma referência de célula com o método de formatação condicional CONT. SE, você pode inserir um valor a ser pesquisado e sinalizar uma ou mais listas para instâncias desse valor.
- Para simplificar os formulários de comparação, você pode criar nomes para sua lista, como "Lista1" e "Lista2". Então, ao escrever as fórmulas, esses nomes de lista podem substituir os intervalos de células absolutos usados nos exemplos acima.
